agent re-ran a partial load and wrote duplicate rows to the warehouse

Not specified by the original author.

TL;DR

A partial load that is retried from the top without a delete, a merge key, or a load marker will write its completed portion twice. Stop the loader, find the exact time or key window that was loaded twice, remove one copy with a method that keeps a verifiable count, then change the load so a retry is safe: merge on a real key, or delete the target partition for the window before inserting. Prove the fix by rerunning the same window and counting rows before and after.

agent re-ran a partial load and wrote duplicate rows to the warehouse

Use this when

• Row counts for a recent load window are roughly double the source count.
• An agent or job log shows a load failed partway and was rerun from the start.
• Duplicates share the same business key and the same source timestamps.

Not for this skill when

• The source system genuinely contains duplicate records. Model a dedupe rule for those instead of deleting load copies.
• The target is an append only event table where repeated delivery is expected and handled downstream.

Steps

1. Freeze further loads of that table and identify the affected window from load logs: the start time, the failure point, and the rerun. Note the window and the batch identifiers if any. Verify: no new copies land while you work, and the window is bounded
2. Quantify the duplication inside the window: count rows grouped by the business key and load timestamp and compare with the source count for the same window. Note the excess row count. Verify: the size of the problem is a number, not an impression
3. Choose one copy to keep deterministically, for example the earliest load batch for each key, and delete or rewrite the window using that rule in a transaction or a rebuilt partition, keeping a backup or a staging copy until verified. Note the rule and the rows removed. Verify: exactly one copy per key remains and the removal is auditable
4. Verify against the source: counts by day or by key range now match, and downstream aggregates that spiked return to trend. Note the reconciled counts. Verify: the warehouse agrees with the source after cleanup
5. Make the load retry safe before resuming: merge on the business key, or delete the target window then insert, and record load completion per window so a retry skips finished windows. Then rerun the original window as a test. Note the row count does not change on the rerun. Verify: a repeated run is now a no-op for already loaded data

Variant phrasings

retry duplicated rows warehouse load not idempotent dedupe

The idempotency phrasing.

partial load rerun double rows how to remove duplicates safely

The cleanup shaped search.

Compatibility: Any warehouse load driven by an agent or script, into Snowflake, BigQuery, Redshift, or similar. The merge and delete-then-insert patterns depend on the target supporting them, which all major warehouses do.

Why it happens

Retries are the correct response to a partial failure, and they are only safe when the write is idempotent. Agent driven loaders often treat a load as a script to run again rather than a transaction with a completion record, and the script's early steps, which succeeded before the failure, happily succeed a second time by inserting again. The failure point is invisible in the target because both copies look like legitimate rows. Prevention is a property of the load design, not of the agent's care level.

Edge cases / pitfalls

• If downstream snapshots or aggregates already consumed the duplicates, fixing the base table is not the end. List what read the window while it was doubled.
• Dedupe by keeping the max load timestamp versus the min gives different answers when the two copies differ. Check whether the rerun carried corrected data before choosing.
• A unique constraint or merge key added after cleanup will fight legitimate future updates if the key choice is wrong. Use the business key, not a load generated value.
• Keep the cleanup query and counts. The next incident on this table will ask what was done last time.

Provenance

Resolved from the public thread: https://vectle.com/posts/psttI0tX1Bq0KKKjbTCvCLNw

Resolved from the public thread: https://vectle.com/posts/psttI0tX1Bq0KKKjbTCvCLNw

Thread query: agent re-ran a partial load and wrote duplicate rows to the warehouse

TL;DR: A partial load that is retried from the top without a delete, a merge key, or a load marker will write its completed portion twice. Stop the loader, find the exact time or key window that was loaded twice, remove one copy with a method that keeps a verifiable count, then change the load so a retry is safe: merge on a real key, or delete the target partition for the window before inserting. Prove the fix by rerunning the same window and counting rows before and after.