Idempotent Writes Are Not Replay Safety

Imagine replaying the orders slice for 9 August. The first attempt writes three rows, emits an orders.published event, then dies before recording that the event was handled. The retry runs the same MERGE. The table still contains three rows, so the job looks idempotent.

The downstream billing service has now handled the event twice.

That is the gap I care about in this article. The first part of this series gave a replay a bounded data interval and an owned output. This part starts when that same intent reaches a write operation—and keeps following it until every visible effect has settled.

Four ideas that are easy to mix up

These words often appear next to each other in design reviews, but they make different promises:

• Deduplication identifies records that represent the same fact and keeps one according to an explicit rule.
• Upsert or MERGE describes a write operation. Its result depends on the match key, source rows and actions for matched and unmatched records.
• An idempotent write lets the same logical write identifier arrive again without applying an additional table mutation.
• Replay safety means repeating the whole intent reaches a semantically equivalent outcome, including calls, messages and checkpoints outside the table.

The scope grows with every line. A transaction ID can make one append idempotent without making the transform deterministic. A deterministic transform can converge in one table while an anonymous event fires twice. Saying “the pipeline is idempotent” hides those boundaries exactly when they matter.

The write verb is not the contract

Take the same bounded input—three orders for 9 August—and write it twice.

Write convergenceThe verb does not define the replay contract

Same interval × 2

StrategyAttempt 01Replay 02Visible result

Append

+3 rowsA · B · C

+3 rowsA · B · C again

6 rowsAccumulates

Merge

3 keyed rowsone row per key

same keyssame source

3 rows*Needs rules

Replace

slice Nowned boundary

slice Nsame boundary

slice NConverges

A plain append has no memory of the first attempt, so the replay adds another three rows. That does not make append a bad operation. It means append needs an identity contract somewhere else: a transaction token, an immutable source offset or a downstream deduplication key.

Replacing the owned slice is easier to reason about. If the run owns exactly the 9 August partition, then replacing that partition with the same deterministic result converges. The important word is exactly. An unbounded overwrite can remove unrelated data, while a dynamic partition overwrite can change meaning when the table’s partitioning evolves.

MERGE sits between those two. It can converge, but the SQL verb does not provide the proof. The proof comes from the rules around it.

MERGE is conditional safety

Before I call a merge replay-safe, I want four things fixed:

• Stable identity. The match condition uses a business key, not an attempt-time row number, generated ID or wall-clock timestamp.
• One source winner. Multiple candidates for one key are resolved deterministically before the merge; file order is not a rule.
• Stable source meaning. Random values, current_timestamp and unpinned lookups cannot change between evaluations of the same replay intent.
• Explicit absence. Missing source rows mean deletion only when that rule is declared and scoped to the owned boundary.

Notice what is deliberately missing here: the policy for an older event arriving after a newer one. That deserves its own treatment in part 3. For this article, the requirement is only that the winner rule exists and produces the same answer for the same reviewed inputs.

The side effect you forgot to count

Even a perfectly convergent table is only one participant in a workflow. A task may also:

• publish a message;
• call an external API;
• send a notification;
• advance a checkpoint; or
• trigger another dataset.

Those effects do not join the table transaction merely because they happen in the same task function.

Side-effect boundaryA converged table can still repeat the effect

One applied

Effect intentorders · 09 Aug · input-42 · rev-17 · published

1. Attempt identity
   Writetable converges

   Emitanonymous event

   Retryemit again

   2 appliedduplicate effect
2. Stable effect ID
   Writetable converges

   Deliversame effect ID

   Dedupedurable receipt

   1 appliedone business effect

The safer lane gives the effect a stable identity derived from intent:

dataset + owned interval + input version + contract revision + effect type

An attempt ID is the wrong key because it changes on retry. With a stable effect ID, delivery may still repeat, but the receiver can record the ID with its business change and apply the effect once.

This is an important distinction: one delivery and one applied effect are not the same promise.

Choose the boundary you can actually make atomic

When state and the event record live in the same transactional database, an outbox is a clean boundary. The business change and an outbox row commit together; a separate relay may publish the row more than once, and consumers use the event ID to remove duplicate effects.

A lakehouse table and a remote API usually do not share that transaction. Pretending otherwise creates a dual-write problem with better naming. In that case I prefer an explicit protocol:

1. derive the effect ID from the reviewed replay intent;
2. make the data write converge under its own table contract;
3. deliver the effect with that stable ID;
4. let the receiver record the ID atomically with the change it owns; and
5. reconcile intents that have no durable receipt.

Every valid protocol must name where atomicity ends and how uncertainty is repaired. An “exactly-once” guarantee inside a log or transaction does not automatically include an email provider, REST API or second store. Ask which effect is protected, inside which boundary and under which identity.

Pick a write contract by what the dataset owns

I use this as a starting point, not a universal lookup table:

Output	Usually simplest contract	What still needs proof
Immutable landing batch	Append with a stable transaction ID	One logical batch cannot append twice
Correctable bounded slice	Replace the owned slice	Overwrite scope cannot reach unrelated data
Curated entity or fact table	MERGE on a stable business key	One source winner and explicit delete semantics
Aggregate derived entirely from one boundary	Recompute and replace that aggregate boundary	Dependencies do not read beyond the claimed scope
Message, API call or notification	Stable effect ID plus receiver deduplication	Receipt and business change share a durable boundary

The best contract is often the least clever one that matches ownership. Replacing one small, well-defined slice is easier to explain than a merge with six branches. A merge is better when the table genuinely owns entities across many intervals. An append is appropriate when every input is new and the transaction identity is first-class.

What I want to hear in a review

Before approving a replayable write path, I want concise answers to these questions:

1. What stable identity represents this logical write?
2. Which target rows or partitions may it change?
3. Will the same inputs and revision converge to the same logical data?
4. What makes one source row win when keys collide?
5. Which effects happen outside the table commit?
6. Does each external effect keep the same identity across retries?
7. Where is that identity recorded with the business change?
8. How do we reconcile an effect whose final state is unknown?

Part 5 turns claims like these into failure-injection tests and executable invariants. Here they serve a narrower purpose: exposing whether “idempotent” describes one line of code or the complete replay path.

That distinction is the takeaway. Idempotency is a property of an operation inside a boundary. Replay safety is a contract for the intent as it crosses boundaries.

Further reading

• Making retries safe with idempotent APIs — Amazon Builders’ Library.
• Table deletes, updates and merges — Delta Lake documentation.
• Spark writes for Iceberg tables — Apache Iceberg documentation.
• Outbox Event Router — Debezium documentation.
• Kafka design and delivery semantics — Apache Kafka documentation.