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.

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