Data Duplication: Understanding and Resolving Common Issues

Data duplication, or data redundancy, is a common challenge within databases and storage systems, characterized by the presence of identical copies of data across different locations. This can occur both intentionally, for backup and redundancy purposes, and unintentionally, through errors in data entry or integration processes. However, the implications of unchecked data duplication are far-reaching, affecting various aspects of data management and utilization.

Here are some potential issues which might cause data duplicates:

• Duplicate records in source
• Incorrect snapshotting policy
• Conflicting date range
• For data storages:
  • Incompatible write mode
  • Wrong unique columns when using upsert

Duplicate Records in Source

Duplicate records often stem from source data anomalies.

1. The source table has duplicit records.
2. Multiple values for typically singular fields get flagged as duplicates. For example, when the same person registers twice, each time with a different email/phone number.

Incorrect Snapshotting Policy

Incorrect snapshot keeping policy can inadvertently cause data duplicities. Ensure the right combination of date range and sync frequency.

Example: Then the date range is set to the last 7 days with a daily sync frequency, the system will insert the last 7 days' data every day. This results in data being replicated sevenfold.

To optimize snapshotting:

• For storages, databases, warehouses: Select Replace as the snapshotting policy.
• For dashboarding apps: Select Append snapshotting policy.

Conflicting Date Range

Date range during data source configuration is by default set to Yesterday to extract the pervious day's data every day.

If you want to load historical data, the best practice is to create a dedicated additional data source.

Data Storages: Write Modes and Unique Columns

Incompatible Write Mode

If your destination is a data storage, selecting the insert write mode may lead to duplicates.

Instead, use the upsert write mode, which updates existing records and inserts new ones.

Wrong Unique Columns for Upsert

Make sure to select the right unique column(s) for upsert write mode.

Example: You have a database of customer interactions. Customer ID would be a good choice for a unique column because each customer can have only one ID. If you use Login Time, there will be duplicates.

1. Customer ID as unique column
Customer ID is a stable and unique identifier for each customer. When new data comes in, the upsert process checks if a record with the same Customer ID already exists. If it does, the system updates that existing record with the new information. If not, it inserts a new record. This approach effectively avoids duplication because Customer ID uniquely identifies each customer.

2. Login Time as unique column
Login Time is used as the unique identifier. However, Login Time varies with each login, meaning it's unlikely to be the same across multiple records. As a result, the upsert process may not find a matching Login Time in the existing records. This leads to the system treating most incoming data as new information. As new records will be created each time, it will result in data duplication. This unique column fails to recognize and update existing customer records because Login Time does not serve as a consistent and unique identifier like Customer ID.

Choosing a unique column like Customer ID, which consistently and uniquely identifies each record, is crucial for upsert to ensure data is accurately updated without duplication.