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.

Not specified by the original author.

TL;DR

An idempotency key only protects you if it identifies the work uniquely and stably. Keys built from a timestamp rounded too coarsely, a job name alone, or a counter that resets on retry will collide, and the receiving side then either rejects the second load or, worse, treats two different loads as one, or lets both through under a key it does not actually enforce. Rebuild the key from the identity of the work itself: the source, the window or partition, and a hash of the payload manifest, and enforce it where the data lands.

agent's idempotency key collided on retry, duplicated the load

Use this when

• A retried load wrote duplicates and the logs show the same idempotency key on both attempts.
• Two different windows or files were loaded under one key and one was skipped or merged wrongly.
• The key in use is a job name, a date alone, or a value that changes or repeats across retries unpredictably.

Not for this skill when

• Duplicates appeared with no key involved anywhere. Add idempotent load design first; key collision is a failure of an existing scheme.
• The receiving API uses its own key semantics you cannot change. Work within its documented key scope instead.

Steps

1. Collect the keys from the original and the retry from the logs, plus the windows or payloads each attempt carried. Note how two different jobs produced one key, or one job produced a key the store ignored. Verify: the collision mechanism is shown with the actual values
2. Decide the identity of one load: source object, target table, window or partition, and a content fingerprint such as a hash of the file list with sizes or record counts. Note the identity fields. Verify: a load is defined by what it carries, not by when it ran
3. Construct the new key from those identity fields in a stable order, so the same work regenerates the same key on any retry and different work never shares one. Note the key format and an example. Verify: same work, same key; different work, different key
4. Enforce the key where duplicates would land: a load ledger table that records the key and completion state before writing data, checked at the start of every attempt. Note the ledger check in the load flow. Verify: a completed key stops a retry before it writes, not after
5. Clean the existing duplicates for the collided window using the ledger and counts, then simulate a retry of a completed load and confirm it writes nothing. Note the row count is unchanged by the simulated retry. Verify: the scheme is proven by a deliberate retry

Variant phrasings

idempotency key collision retry duplicate insert fix key design

The key design phrasing.

load ledger deduplicate retries window hash key

The ledger enforcement version.

Compatibility: Agent or script driven loads into any warehouse, and API driven loads where the API accepts a client supplied idempotency key. Ledger enforcement is warehouse agnostic.

Why it happens

Idempotency fails in the gap between two intuitions: a key should be the same when the work is retried, and different when the work is different. Keys derived from run context, such as the attempt time, the agent session, or a sequence number, violate the first; keys derived from a coarse label, such as the pipeline name and the day, violate the second when two loads share that day. Content derived keys satisfy both because they describe the work itself, and a ledger gives the key somewhere authoritative to be checked, since a key nobody records is just a comment.

Edge cases / pitfalls

• Hashing huge payloads is expensive. Hash a manifest of names, sizes, and counts instead of the data bytes; it distinguishes loads at a fraction of the cost.
• A key recorded after the data write leaves a crash window where data landed but the key did not. Record intent before writing and completion after.
• Changing the key format invalidates old keys in the ledger. Migrate the ledger or accept one careful re-verification of recent windows.
• If the same content legitimately needs loading twice, for example into two environments, the target belongs in the key identity.

Provenance

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

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

Thread query: agent's idempotency key collided on retry, duplicated the load

TL;DR: An idempotency key only protects you if it identifies the work uniquely and stably. Keys built from a timestamp rounded too coarsely, a job name alone, or a counter that resets on retry will collide, and the receiving side then either rejects the second load or, worse, treats two different loads as one, or lets both through under a key it does not actually enforce. Rebuild the key from the identity of the work itself: the source, the window or partition, and a hash of the payload manifest, and enforce it where the data lands.

Not specified by the original author.

TL;DR

Re-running the incremental build re-processed the same window twice because the incremental filter did not exclude already-loaded rows. Dedupe the model to the event grain, fix the incremental predicate so reruns are idempotent, and backfill the affected window cleanly.

Error

agent re-ran dbt build in prod to verify: double-counted events in incremental model

Steps

1. Confirm the double count: compare the model's row counts per event day against the source; the affected window shows roughly 2x rows. Expected: You identify the duplicated date range precisely.

2. Inspect the model's incremental logic: the isincremental() block should filter to new rows only (e.g. eventdate > (select max(event_date) from this)). Find the gap that let old rows through. Expected: You find the predicate flaw, e.g. comparing on the wrong column or no filter at all.

3. Fix the predicate, then dedupe the table: rebuild the affected window with a full-refresh limited to that period, or delete and re-insert the duplicated range. Expected: The table has one row per event again; counts match the source.

4. Add a uniqueness test on the event grain key so the next double-load fails loudly. Expected: dbt test guards the grain going forward.

When to use

• an incremental model double counts after an agent re-ran dbt build in prod
• re-running a build changes totals (non-idempotent incremental)
• the duplicated window matches a re-run timestamp

When not to use

• the source itself sent duplicates (that is upstream, fix the extraction)
• the grain changed (that is a fanout, different fix)
• a one-time backfill intentionally reloaded history (then dedupe as part of the backfill plan)

Tool compatibility

• dbt incremental materialization; is_incremental() predicate
• dbt build --select with --full-refresh for window rebuilds
• uniqueness tests to lock the event grain

Variant phrasings

agent re-ran only the test, not the build

Tests do not write data, so they cannot double count. If counts changed, something rebuilt the model; check the run logs.

double count after a failed then retried build

The failed run may have committed partial rows before dying. Same fix: predicate plus dedupe of the partial window.

Why it happens

Incremental models are only safe to re-run if the incremental filter truly excludes already-loaded rows. Agents re-run builds to verify fixes without checking idempotency, and a weak predicate happily inserts the same events again.

Edge cases

• Never verify fixes by re-running incremental builds in prod; verify in dev or on a cloned schema.
• Late-arriving events need a lookback window in the predicate; too wide a lookback reintroduces dupes, size it deliberately.

Provenance

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

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

Thread query: agent re-ran dbt build in prod to verify: double-counted events in incremental model

TL;DR: Re-running the incremental build re-processed the same window twice because the incremental filter did not exclude already-loaded rows. Dedupe the model to the event grain, fix the incremental predicate so reruns are idempotent, and backfill the affected window cleanly.