Agent Retries Turn One Write Into Two: Why Duplicate Writes Happen and How to Prevent Them
Why agent retries create duplicate writes, how idempotency keys, table-level MERGE deduplication, saga compensation and reconciliation prevent them.
An agent calls a write tool to record a refund. The call times out. The agent retries, the retry succeeds, and the business table now holds two refund rows for one customer request. Nothing in the retry policy is broken. The retry did exactly what it was configured to do.
The duplicate exists because the first attempt probably succeeded and the agent never learned that. A client that receives no acknowledgment cannot tell whether the server never processed the request or processed it and lost the response. When the second case happens, the retry re-executes an action that already landed. Suppressing retries is the wrong fix: it trades duplicate writes for missing ones. The durable fix is to make the write path recognize a repeat and to keep enough evidence to prove afterward that no duplicates survived.
This article is written for engineers and data architects operating agents that write into a lakehouse or transactional store. It works through the mechanism behind the duplicate, what delivery guarantees actually promise, how to design an idempotency key that survives retries, how to deduplicate at the table, how to treat a multi-step action as one unit, and how to reconcile afterward. The closing test checklist is a reasoning construct assembled from the mechanisms cited here, not a validated production procedure or an observed result.
Why a retry creates a second row when the first request appeared to fail
The failure that produces a duplicate is a specific one, and it is worth separating from its lookalikes.
A write request can fail in two distinct ways. In the first, the request never reaches the server: the connection is refused, the packet is dropped, or the request is rejected before any state changes. Retrying is safe here because nothing happened the first time. In the second, the server receives the request, commits the write, and then the response is lost — the connection drops, the client times out waiting, or a proxy in between fails to relay the reply. The client sees the same symptom in both cases: no acknowledgment. It cannot distinguish them from the outside.
That ambiguity is why HTTP defines idempotent methods. RFC 9110 §9.2.2 describes idempotent methods as those where the intended effect on the server of multiple identical requests is the same as the effect of a single request. The definition exists precisely because a client may repeat a request when it cannot confirm the outcome. It is a property of the method's semantics, not a promise that the server will detect the repeat for you — a PUT that appends a row on every call is not idempotent no matter what the method is named.
For an agent, the practical consequence is that retry policy and duplicate prevention are different problems. Backoff, jitter, and retry caps decide how often the agent tries; they say nothing about whether a second attempt is harmless. Only the write target can answer that, and only if it has something stable to compare against. A retry loop with perfect backoff will still produce two rows if the table has no way to recognize the second write as the same intended action.
What at-least-once actually guarantees, and why exactly-once is not an end-to-end property
Delivery semantics are usually invoked at this point, and usually invoked too loosely.
At-least-once delivery means every message is delivered one or more times. That is the guarantee most brokers actually provide, and it is the honest description of what an agent's tool call sees: the request may be processed more than once. Exactly-once, where a broker advertises it, is narrower than it sounds. The Azure Architecture Center's idempotent consumer guidance states that it is impractical to guarantee exactly-once delivery across a distributed system, and that even brokers using exactly-once semantics can guarantee only operations they directly control — delivering messages to consumers, or writing data back to the broker. They cannot control the side effects a consumer implements in external systems.
That boundary is the whole argument. If the agent's write lands in a business table the broker does not own, the broker's guarantee stops at the handoff. The row is written by the consumer side, and the consumer side is where the duplicate appears.
The source names the achievable target: combine at-least-once delivery with a consumer that ignores duplicates, and the result is effectively-once processing. The duplicate still arrives; it is simply not acted on twice. This reframes the engineering task. Instead of searching for a stronger delivery guarantee, the team builds a writer that can recognize a repeat and decline to re-apply it. That is a design decision inside the agent's write path, and it is testable in a way that a broker feature claim is not.
Designing the idempotency key: what to derive it from and what breaks it
An idempotency key is the identifier the writer uses to decide whether an incoming action has already been applied. Its only hard requirement is stability: the same intended action must produce the same key on every retry.
Illustrative configuration only. Each layer is assumed to retry independently, with no shared deadline or retry budget across layers; actual attempt counts depend on error classification and backoff. The product shows how independent retry owners multiply the worst-case number of attempts reaching the write target, which is why a stable idempotency key — not a retry cap — is what prevents a second row.
The idempotent consumer guidance is explicit about what breaks that stability. Do not key on transport-level identifiers that the broker regenerates on redelivery, or on values derived from delivery attempts — those change between deliveries and defeat duplicate detection. Avoid deriving the key from volatile fields such as receive timestamps for the same reason. Each of these choices looks convenient at send time and fails at exactly the moment it is needed, because the retry produces a different key and the writer concludes it is looking at a new action.
For an agent, the rule translates into a placement decision. The key has to be generated when the agent decides to act, and then carried with the action through every attempt. If it is computed inside the send function, it is regenerated on each call and the deduplication layer never sees a repeat. If it is derived from the model's output at send time, it inherits the model's variability.
That last point connects to a failure mode developed later in this article. An agent that re-plans after a timeout may produce a slightly different tool call — a different parameter ordering, a rephrased description, a different intermediate value — and a key derived from that output will differ too. The write target then correctly concludes it has never seen this action, and inserts a second row. The key must be pinned to the original decision, not recomputed from whatever the model says on the second pass.
A workable pattern is to have the agent emit a decision record before it calls any write tool: a decision identifier, the intended business key (for example, the order and the action type), and the parameters. The idempotency key is derived from that record and stored with it. Retries reuse the record; a genuine new decision gets a new one. The distinction between those two cases is the subject of the next two sections.
Deduplicating agent writes at the table level with MERGE and merge keys
Request-level idempotency handles a single write. Storage-level deduplication handles the case where a duplicate reaches the table anyway, and it is where the table format's semantics start to matter.
The common pattern is MERGE: match incoming records against the target on a stable business key, update when matched, insert when not. Delta Lake documents this upsert form directly, using a source table of updates against a target and matching on an id column so that existing rows are updated and new rows inserted (Delta Lake merge). The same page includes a worked example for a duplicate-log use case: collecting change records into a table, ordering them by a timestamp column, and keeping only the latest change per key so that re-sent records do not accumulate as duplicate rows.
The formats are not interchangeable, and a design that assumes they are will fail in production.
Apache Iceberg supports MERGE INTO by rewriting the data files that contain rows needing updates in an overwrite commit (Iceberg Spark writes). Its constraint is stricter than Delta's: a merge statement in which more than one source record matches a given target row is an error condition rather than a silent last-writer-wins. That is useful — it surfaces a broken merge key at write time instead of hiding it — but it means a batch containing two records for the same business key will fail rather than deduplicate. The pipeline has to reduce to one record per key before the merge, which is exactly what the Delta example does with its groupBy and max step.
Apache Hudi takes a different approach again. Its upsert path merges multiple versions of a record identified by the record key into a final record, and for records sharing a key within the same batch, the ORDERING_FIELDS configuration selects the record with the largest ordering-field value (Hudi writing data). Hudi's configuration reference also documents insert-time duplicate policies — none, drop, and fail — that determine what happens when records being ingested already exist in storage (Hudi configurations). The default is none, meaning no action is taken, which is worth checking before assuming a Hudi table rejects re-ingested records.
The design consequence is that the merge key and the idempotency key must agree. If the agent's idempotency key is a decision identifier but the table merges on (order_id, action_type), the two layers are answering different questions. A re-decide that produces a new decision identifier but the same business key will pass the request-level check and be caught at the table; a retry that preserves the decision identifier but somehow alters the business key will pass the table check and be caught at the request level. Neither layer is redundant. Both need to be specified together, and the merge key should be the business identity of the action, not a surrogate the agent generates.
Treating a multi-step agent action as one unit: saga, outbox, and replay versus re-decide
Single-write idempotency and table-level merge both assume one action maps to one write. Real agent tasks often do not. A refund may require a payment reversal, an inventory adjustment, and a customer notification, each in a different system. A partial failure in the middle leaves the business in a state no single retry can repair.
The saga pattern addresses this. The Azure Architecture Center's saga guidance describes coordinating a sequence of local transactions across multiple services, where each service performs its operation and triggers the next step through events or messages, and where a failed step triggers a series of compensating transactions that undo the changes completed steps made. The key property is that each step is a local transaction with a defined compensation, not a distributed transaction held open across services.
The transactional outbox complements it. The microservices.io description frames the pattern as each transaction publishing a corresponding event — T1 -> E1, T2 -> E2 — with the state change and the event recorded together so that a service cannot commit one without the other. The AWS Prescriptive Guidance version covers the same intent for event-driven systems. For an agent, the outbox is what makes "the write happened" and "the next step was told about it" a single atomic fact rather than two operations that can diverge.
Neither pattern solves the agent-specific problem on its own, which is the distinction between replay and re-decide. This distinction is a reasoning construct defined here, not a sourced standard, but it is the one that most often explains why an idempotency design that looks correct still produces duplicates.
Replay re-executes a recorded action with the same parameters and the same key. It is what a retry mechanism should do, and it is safe because the key is unchanged. Re-decide asks the model to plan again from the current context. The new plan may use a different tool, a different parameter value, or a different decomposition of the task — and because the plan is new, any key derived from it is new. The write target sees a first-time action and applies it.
Re-decide is tempting because it is what the agent is good at. Given a timeout, the model can reason about what might have gone wrong and try a different route. That flexibility is valuable for read paths and for tasks with no side effects. On a write path it is dangerous, because the model has no reliable knowledge of whether the first attempt landed. The safe design is to pin the decision: once an action with side effects has been dispatched, retries replay it, and any change of plan requires an explicit reconciliation step that first checks whether the original action took effect. The saga's compensation logic is the natural place to put that check.
Proving after the fact that no duplicates were written
Prevention is necessary and insufficient. Teams also need a way to answer, after the fact, whether duplicates landed — because the mechanisms above can be misconfigured, and because an agent's behavior changes as prompts and tools evolve.
The verification method is reconciliation: compare what the agent intended to do against what the target table contains. Three query patterns cover most cases.
Group by the intended business key and count occurrences. If the business key is (order_id, action_type) and the expected count per key is one, any key with a count above one is a duplicate. This is the simplest check and the one most likely to be run continuously rather than once.
Compare expected action counts against row counts. If the agent's decision ledger records that it dispatched 1,000 refund actions in a window, the target table should contain 1,000 refund rows for that window. A discrepancy in either direction is a signal: more rows than actions means duplicates, fewer means lost writes.
Join the audit ledger to the target table. This is the most informative check because it attributes each row to a decision. The ledger needs five fields to make the join possible: the idempotency key, the decision identifier, the attempt number, the recorded outcome, and a timestamp. With those, a query can find rows whose idempotency key appears more than once, rows with no matching ledger entry, and ledger entries marked successful with no corresponding row.
The attempt number is what separates replay from re-decide in the logs. A replay shows the same decision identifier with increasing attempt numbers. A re-decide shows a new decision identifier for what the ledger's business key says is the same intended action — which is exactly the pattern that produces duplicates, and exactly what the reconciliation query should flag.
This reconciliation approach is a design construct assembled from the mechanisms described above, not a validated production procedure. The query shapes are ordinary SQL; the value is in defining the ledger fields before the agent goes live, because retrofitting them after duplicates appear means reconstructing intent from logs that were never designed to record it.
What to test before rolling an agent's write path into production
The tests below convert each mechanism into an observable expectation. They are acceptance checks for a write path, not benchmarks, and none of them requires a specific pass threshold beyond the stated expectation.
Lost acknowledgment, then retry. Simulate a write that commits on the server and then drops the response. Let the agent retry. Expect exactly one row in the target table. This is the test that reproduces the mechanism in the first section, and it is the highest-value one to run first because it exercises the whole chain: key generation, key stability, and the write target's duplicate check.
Re-decide after a timeout. Simulate the same lost acknowledgment, but configure the agent to re-plan rather than replay. Expect either that the pinned key still deduplicates — because the key was attached to the original decision and the new plan reuses it — or a documented failure that surfaces the divergence. An undocumented second row is the failure this test exists to catch.
MERGE with duplicate source records. Feed a merge statement two source records for the same business key. Expect the format's documented behavior: Delta's ordered reduction to one record per key, Iceberg's error on multiple matches, or Hudi's ordering-field selection and configured insert duplicate policy. The point is to confirm which behavior the team's actual table format exhibits, rather than assuming the formats are equivalent.
Mid-saga failure. Fail a step in the middle of a multi-write action. Expect compensation of the completed steps, not a partial write left in place. Verify by querying the target systems for the state the compensation should have restored, not by reading the saga's own success log.
Reconciliation against a seeded duplicate. Deliberately insert a duplicate row and run the reconciliation queries. Expect each query to catch it. A reconciliation check that passes on a known-bad dataset is not a check.
Two further checks are worth adding as the agent matures. First, verify that the idempotency key is not derived from a receive timestamp or a transport identifier by inspecting the key-generation code path directly — this is a code review, not a runtime test, and it catches the failure mode that the runtime tests may miss if the retry happens to arrive within the same clock tick. Second, confirm that the merge key and the idempotency key are documented together, so that a future change to one does not silently break the other.
Where Singdata fits
The mechanisms above depend on the storage layer supporting them. Singdata Lakehouse documents Append and Upsert write support with analysis immediately after writing, alongside high-throughput real-time and batch writing and schema evolution ([kb://51795]). That upsert surface is the storage-level deduplication layer the MERGE section depends on: a write path that can match on a business key and update rather than insert is what makes table-level deduplication possible at all.
Singdata's observability materials also treat agent execution traces as queryable enterprise data rather than logs confined to a viewer ([kb://53358]). For the reconciliation approach described above, that matters: the audit ledger and the target table can live in the same queryable environment, so the join between intended actions and written rows is a SQL query rather than an export-and-compare exercise.
These are vendor-documented capabilities, and they should be validated against a team's own write path rather than taken as proof that duplicates are eliminated. The tests in the previous section are the way to do that validation: run the lost-acknowledgment test and the seeded-duplicate reconciliation against the actual configuration, and confirm the behavior rather than the documentation.
FAQ
Does retrying with the same idempotency key always prevent a duplicate row?
No. It prevents a duplicate only if the write target checks the key and the key is stable across retries. A key regenerated at send time, derived from a transport identifier, or computed from volatile fields such as a receive timestamp will differ between attempts and will not deduplicate. The key must be generated at decision time and carried with the action.
Is exactly-once delivery the right thing to ask a broker for?
It is the wrong question for an end-to-end write path. Broker-level exactly-once covers only operations the broker directly controls, such as delivering messages to consumers or writing back to the broker. It cannot control side effects a consumer performs in an external system. The achievable target is effectively-once processing: at-least-once delivery combined with a consumer that ignores duplicates.
Can MERGE alone solve duplicate agent writes?
MERGE deduplicates at the table given a correct merge key, and it is a necessary layer. It does not address multi-step actions that span several systems, and it does not address re-decide, where the agent produces a new plan with new parameters after a timeout. Those need saga compensation and a pinned decision identifier in addition to the merge key.
How do I tell replay from re-decide in my logs?
The audit ledger fields make the distinction visible. A replay shows the same decision identifier with increasing attempt numbers. A re-decide shows a new decision identifier for what the business key identifies as the same intended action. If the ledger does not record the decision identifier and the attempt number, the two are indistinguishable after the fact.
What is the single highest-value test to run first?
The lost-acknowledgment-then-retry test. It reproduces the exact mechanism that creates the duplicate — a committed write whose response never reached the client — and it exercises key generation, key stability, and the write target's duplicate check in one pass. If that test produces two rows, no amount of retry tuning will fix the write path.