Home / Data Platform / Lakehouse

Data PlatformRunbookIntermediate

Sev1: duplicate records after a pipeline re-run — clean up and make loads idempotent

Revenue doubled overnight because a failed load was re-run on top of a partial one. Find the duplicates, remove them safely in Delta Lake, Snowflake or SQL, and redesign the load with MERGE or partition overwrite so re-runs are always safe.

Deepak Bhardwaj3 min read

Applies toDelta Lake / Databricks, Snowflake, Azure SQL / Synapse, Spark and ADF pipelines

Symptoms

  • Totals jump on a dashboard right after an incident or a manual re-run.
  • Primary-key-like columns have duplicates; downstream joins fan out.

1. Measure the damage

SELECT order_id, COUNT(*) AS copies
FROM silver.orders
WHERE load_date = '2026-10-24'
GROUP BY order_id HAVING COUNT(*) > 1
ORDER BY copies DESC LIMIT 20;

Confirm they're true duplicates (all columns equal) or different versions of the same key (need "latest wins").

2. Pause downstream

Stop gold-layer jobs and extracts that would spread the duplicates further.

3. Remove duplicates

Delta Lake — simplest if the table was clean before the bad run:

DESCRIBE HISTORY silver.orders LIMIT 10;
RESTORE TABLE silver.orders TO VERSION AS OF 1287;    -- version before the duplicate append

Then re-run the load once, correctly.

Delta Lake — dedupe in place, keeping the latest per key:

CREATE OR REPLACE TEMP VIEW orders_dedup AS
SELECT * EXCEPT (rn) FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY ingest_ts DESC) AS rn
  FROM silver.orders WHERE load_date = '2026-10-24') WHERE rn = 1;

INSERT OVERWRITE silver.orders REPLACE WHERE load_date = '2026-10-24'
SELECT * FROM orders_dedup;

Snowflake:

CREATE OR REPLACE TABLE orders_fix AS
SELECT * FROM orders QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY ingest_ts DESC) = 1;
ALTER TABLE orders SWAP WITH orders_fix;

SQL Server / Azure SQL:

WITH d AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY IngestTs DESC) rn FROM dbo.Orders WHERE LoadDate = '2026-10-24')
DELETE FROM d WHERE rn > 1;

Warning

Take a copy before deleting (Delta/Snowflake history covers you; on SQL, SELECT INTO a backup table). Verify row counts against the source system before and after.

4. Rebuild downstream

Recompute gold tables for affected dates, refresh semantic models, and tell report owners which numbers were wrong and for how long.

5. Make the load idempotent

Pattern Use for
MERGE on a business key Upserts, CDC feeds
Partition overwrite (REPLACE WHERE, dynamic partition overwrite) Daily/hourly batch loads
Insert-only with dedupe key + unique constraint (SQL) Event logs
Write-audit-publish (stage, validate, then swap) Critical financial tables 
MERGE INTO silver.orders t
USING staged_orders s ON t.order_id = s.order_id
WHEN MATCHED AND s.ingest_ts > t.ingest_ts THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

Make sure the source batch itself is deduplicated before MERGE — MERGE fails when several source rows match one target row.

Prevent it next time

  • A uniqueness test on key columns after each load (dbt unique, DLT expectation, a simple SQL check) that blocks publishing.
  • Re-run procedures in the runbook: "always re-run the whole partition", never "append the missing part".
  • Record batch IDs on every row so a bad batch can be deleted precisely.

Related fixes