Why must a sink applying CDC change events use idempotent upserts rather than replaying source statements?

## Why a CDC stream redelivers A change-data-capture pipeline reads the source database's transaction log and emits one change event per row change. To resume after an interruption it periodically records the log position it has processed — every few seconds or every N events, never once per event, because a durable write per event would destroy throughput. If the process dies between emitting an event and recording that position, everything after the last recorded position is emitted again when it restarts. The same thing happens on a task rebalance, on a connector restart, when the sink retries a failed batch, and whenever an operator deliberately rewinds to re-load a target. So a CDC feed is **at-least-once** in practice: every change appears at least once, and after any interruption a window of recent changes appears twice. This is not a defect you can configure away in the general case. Even where a stack offers transactional or exactly-once delivery into a specific sink, the moment you rebuild a target, add a second consumer, or replay a backfill you re-introduce duplicates on purpose. A pipeline whose correctness depends on nothing ever being delivered twice is a pipeline that cannot be operated. ## What an idempotent apply looks like An operation is idempotent if applying it repeatedly leaves the same state as applying it once. Change events make this unusually easy, because a well-formed event carries the **after-image**: the complete state of the row after the change, not a description of the edit. That lets the sink's write mean "make the row with this key look exactly like this", which is inherently repeat-safe: ```sql MERGE INTO customers t USING (SELECT :id AS id, :email AS email) s ON t.id = s.id WHEN MATCHED THEN UPDATE SET email = s.email WHEN NOT MATCHED THEN INSERT (id, email) VALUES (s.id, s.email); ``` Deletes follow the same rule: `DELETE FROM customers WHERE id = :id` affects one row the first time and zero rows every time after, which is exactly what you want. An insert event is handled by the same merge as an update, which is why sink code usually does not branch on the operation type at all except to separate deletes. ## Operations that are not idempotent Four shapes break under redelivery: - **Relative updates.** `SET qty = qty - 1` reads the current value, so each replay subtracts again and the sink silently drifts from the source. Take the absolute value from the after-image instead. - **Appends without a dedup identity.** Loading change events into a history or fact table with a plain `INSERT` writes the duplicate as a real row. The fix is a unique identity per change — the pair of primary key and source log position is unique for every change the source ever made — and either a merge that only inserts when not matched, or a dedup step in staging. - **Aggregates computed by counting events.** "Orders today" derived by counting change events overcounts after a replay; derive it from the deduplicated current-state or history table instead. - **External side effects.** An email, a webhook, a payment call or a search-index write are not undone by the sink's idempotency. Each needs its own idempotency key carried in the event and checked at the far end. ## Idempotency is not ordering A common half-answer stops here. Idempotent writes make *repetition* harmless; they do nothing about *reordering*. If two changes to the same row are processed by two workers, the older after-image can be written last and the sink holds a stale value indefinitely — and unlike a duplicate, nothing later corrects it. The two properties have to be built together: route or partition change events by primary key so one row's history stays on one ordered path, collapse a batch to the newest event per key before merging, and where reordering is still possible, guard the merge with the source log position so an older image cannot overwrite a newer one. ## What this buys you operationally An idempotent sink turns a class of incidents into non-events. A connector that fell over at 3am and re-emitted an hour of changes needs no intervention. A target table that was corrupted by a bad deploy can be dropped and rebuilt by replaying the stream. A second sink can be added and caught up from an earlier position without coordinating with the first. That operational freedom, not just crash safety, is the real reason experienced engineers insist on it before they discuss delivery guarantees at all. ## What interviewers listen for The strong answer names at-least-once as the practical guarantee, explains *why* (periodic position commits), shows the after-image upsert, names at least one non-idempotent shape and its dedup key, and then volunteers that ordering is a separate problem. The weak answer claims the pipeline is exactly-once and stops.