How do I reduce duplicate rows when syncing databases to a warehouse?

To reduce duplicate rows when syncing databases to a warehouse, focus on how you extract, identify, and load changes. The best approach depends on whether you need full refreshes or incremental syncs.

Common causes of duplicates

• Re-running the same extract/load job without deduping
• Source tables containing multiple versions of the same business record
• CDC/events arriving more than once
• Late-arriving updates or retries in the pipeline
• Lack of a stable primary key or unique record identifier

Best practices

1. Use a stable unique key

Make sure each row has a natural key or surrogate key that uniquely identifies the record in the warehouse.

Examples:

• customer_id
• order_id
• (account_id, effective_date) for composite uniqueness

If the source doesn’t have a good key, generate one from a deterministic hash of business fields.

---

2. Load into a staging table first

Instead of writing directly to the final table:

1. Load raw data into a staging table
2. Deduplicate there
3. Merge into the target table

This gives you a safe place to compare incoming rows with existing records.

---

3. Use MERGE / upsert logic

For incremental syncs, prefer upserts over append-only inserts.

Typical pattern:

MERGE INTO target t
USING staging s
ON t.id = s.id
WHEN MATCHED THEN
  UPDATE SET ...
WHEN NOT MATCHED THEN
  INSERT (...);

This prevents inserting the same key multiple times.

---

4. Deduplicate with window functions

If multiple rows arrive for the same key, keep only the latest one using ROW_NUMBER():

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY id
           ORDER BY updated_at DESC
         ) AS rn
  FROM staging
)
SELECT * FROM ranked WHERE rn = 1;

You can then merge only the rn = 1 rows.

---

5. Track source change timestamps or sequence numbers

If the source provides:

• updated_at
• created_at
• CDC LSN / binlog position
• monotonically increasing version number

Use these to determine the newest row and avoid loading older duplicates.

---

6. Make loads idempotent

A job is idempotent if running it twice produces the same result.

Ways to achieve this:

• Use MERGE
• Use batch IDs / load IDs
• Delete and reinsert only the affected partition
• Record processed offsets/checkpoints

---

7. Enforce uniqueness in the warehouse

If your warehouse supports it, add:

• primary key constraints
• unique indexes/constraints
• dedupe checks during ingestion

Even if the warehouse doesn’t fully enforce constraints, documenting them helps catch issues early.

---

8. Handle retries carefully

If your ingestion tool retries failed loads, make sure retries don’t append the same records again.

Good patterns:

• Load files with deterministic names and checksum checks
• Store processed file/batch metadata
• Use exactly-once semantics when available

---

Recommended pattern

For most setups:

1. Extract changes
2. Load to staging
3. Deduplicate by key + latest timestamp
4. MERGE into final table
5. Track load state/checkpoints

---

Example workflow

If syncing a users table:

• Key: user_id
• Latest record by: updated_at

Process:

WITH deduped AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY user_id
           ORDER BY updated_at DESC
         ) AS rn
  FROM users_staging
)
MERGE INTO users_target t
USING (SELECT * FROM deduped WHERE rn = 1) s
ON t.user_id = s.user_id
WHEN MATCHED THEN UPDATE SET
  name = s.name,
  email = s.email,
  updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT (...)
VALUES (...);

---

If you’re doing full refreshes

If you rebuild the table entirely:

• Truncate + reload, or
• Build a new table and swap it in

This avoids duplicates, but can be expensive for large datasets.

---

Quick checklist

• Identify a unique key
• Stage data before final load
• Deduplicate with ROW_NUMBER() or DISTINCT ON
• Use MERGE for incremental syncs
• Track checkpoints / offsets
• Make retries idempotent
• Validate row counts and uniqueness after load

If you want, I can also show:

1. a generic SQL dedupe/merge template, or
2. a tool-specific approach for Airbyte, Fivetran, dbt, Snowflake, BigQuery, Postgres, etc.