How do you stop a replayed CDC event from overwriting a newer value already in the sink?

## The failure this solves A sink that applies change events as idempotent upserts survives duplicates: the same after-image written twice leaves the same row. It does not survive *reordering*. If the pipeline re-delivers a batch after a restart and one of those older after-images lands after a newer change to the same row has already been applied, the sink now holds a value the source abandoned — and unlike a duplicate, nothing downstream converges it back. Every later read is wrong until a human notices. This asymmetry is why senior candidates are expected to reach for a guard rather than to trust routing alone. ## The monotonic source position Every log-based capture stamps each event with the position in the source log at which the change was recorded. The names differ by engine — log sequence number, system change number, binlog file plus offset, a global transaction id — but the property is the same: within one source and one capture lineage it increases monotonically with commit order. That is exactly the comparator the sink needs. Persist it as a column on the target row and make the write conditional: ```sql MERGE INTO customers t USING cdc_batch s ON t.id = s.id WHEN MATCHED AND s.src_pos > t.src_pos THEN UPDATE SET email = s.email, src_pos = s.src_pos WHEN NOT MATCHED THEN INSERT (id, email, src_pos) VALUES (s.id, s.email, s.src_pos); ``` A replayed event now fails the `>` test and is dropped. A genuinely newer event passes. The guard costs one extra column and makes the sink robust to *any* arrival order, which in turn means you can be far more relaxed about parallelism and about the replays you cause yourself. ## Why the source's own updated_at is a weaker key The tempting shortcut is to compare the source table's `updated_at` column instead. It is weaker for several reasons: two changes inside the same millisecond compare equal, application code may forget to set it on some write paths, a batch job may set it to a constant, it can be moved backwards by a correction, and it is a wall-clock value on a machine whose clock can step. The log position has none of these problems because it is the storage engine's own bookkeeping, not application data. Use `updated_at` only when the source genuinely offers nothing better, and then treat ties as "apply", accepting the risk. ## Deletes need the guard too The subtle case is deletion. If the sink physically removes the row, it forgets the position it had reached for that key. A replayed pre-delete update then finds no row, takes the not-matched branch, and inserts a row the source deleted — a resurrected ghost, and one of the most common CDC data-quality bugs in the wild. Two workable answers: keep the row with a soft-delete flag and its position, so the guard still sees a position to compare; or maintain a small tombstone table of deleted keys with their positions and check it in the merge, pruning it after a retention window comfortably longer than any replay you would perform. ## When positions are not comparable Positions are only ordered inside one capture lineage. Three events break that assumption: - **Source failover or restore.** After promoting a replica or restoring from a backup, the log position space can restart or diverge. The safe pattern is to store a monotonically increasing epoch or generation alongside the position — bumped by the operator or supplied by the capture — and compare the pair `(epoch, position)` lexicographically. - **Re-snapshotting.** Snapshot rows are read from the table, not the log, so they usually carry the position at which the snapshot was taken, not the position at which each row last changed. A snapshot row can therefore look *older* than a streamed change already applied, which is correct behaviour for the guard, or *newer* than everything, which will mask real streamed changes if the snapshot is stale. Decide deliberately whether a re-snapshot wins or loses against streamed events. - **Multiple sources into one table.** If two databases feed one target, their positions live in different spaces. Include the source identifier in the key or keep a position column per source. ## Alternative: a dedup table keyed by change identity Instead of comparing positions in place, you can keep a table of already-applied `(primary key, position)` pairs and reject anything present in it. This detects duplicates precisely, but it does not by itself reject an *older* event that was never applied, and it grows without bound unless pruned. In practice the in-row position guard is cheaper and stronger; the dedup table earns its place mainly for append-only history targets, where there is no single current row to hang a position on. ## What a strong answer includes Name the position and where it comes from, show the conditional merge, raise the delete-resurrection case unprompted, and note that positions are comparable only within one lineage. Mentioning that this makes reordering a non-issue — and therefore buys you freedom to parallelise and replay — turns a mechanism answer into a design answer.