How do I keep incremental loads from creating duplicates?

To prevent duplicates in incremental loads, you usually need to make the load idempotent and keyed by a stable unique identifier. The exact approach depends on your source and target, but these are the most common patterns:

1. Use a business key or primary key

Identify a column or set of columns that uniquely identifies a row, such as:

• customer_id
• order_id
• composite key like (account_id, transaction_id)

Use that key to decide whether a row is new, changed, or already loaded.

2. Do an upsert instead of plain insert

Instead of inserting every incremental row, use:

• MERGE
• UPSERT
• INSERT ... ON CONFLICT DO UPDATE (Postgres)
• MERGE INTO ... (SQL Server, Snowflake, BigQuery, Delta Lake, etc.)

This way:

• new keys get inserted
• existing keys get updated
• duplicates are not created

3. Track a watermark / high-water mark

Store the last successfully processed:

• timestamp
• monotonically increasing ID
• sequence number

Then only extract rows newer than that value.
Example:

• WHERE updated_at > last_loaded_at

Important: timestamps can be tricky if multiple rows share the same timestamp or if late-arriving data exists.

4. Deduplicate before loading

If the source can emit duplicates within a batch, dedupe the incoming data before writing:

• ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC) and keep row_number = 1
• DISTINCT if appropriate, though it’s often less precise

5. Make the load process retry-safe

Duplicates often happen when a job fails after partially loading data and then reruns the same batch. To avoid that:

• write to a staging table first
• only promote/merge after validation
• checkpoint progress only after a successful commit

6. Use hash checks for change detection

If you want to update only changed rows:

• compute a hash of the row’s non-key fields
• compare source hash to target hash
• update only when the hash differs

7. Handle late-arriving and updated records

If source rows can change after initial load, incremental logic should use:

• updated_at, not created_at
• a lookback window, like reprocessing the last 1–3 days
• merge logic to avoid duplicates during reprocessing

---

Common safe pattern

1. Extract rows changed since last watermark
2. Deduplicate in staging using the business key
3. MERGE into target on the business key
4. Update watermark only after successful merge

---

Example

MERGE INTO target t
USING staging s
ON t.order_id = s.order_id
WHEN MATCHED THEN
  UPDATE SET
    t.amount = s.amount,
    t.updated_at = s.updated_at
WHEN NOT MATCHED THEN
  INSERT (order_id, amount, updated_at)
  VALUES (s.order_id, s.amount, s.updated_at);

---

If you want, I can give you:

• a SQL example
• a dbt incremental model example
• or a Spark/Databricks pattern for duplicate-safe incremental loads.