In a CDC pipeline, what guarantees that two updates to the same row reach the sink in source order?

## Ordering is a per-key property The useful guarantee in a change-data-capture pipeline is not "events arrive in the order the database committed them" — that is unobtainable at any throughput worth having — but "for any single row, its changes are applied in the order the source made them." That is enough for a current-state sink to converge on the right value, and it is what every design decision below is protecting. ## Capture: one ordered reader Log-based capture reads the source's transaction log sequentially, so the events it emits for a table are already in commit order and each carries a monotonically increasing source position. This is why splitting capture for one table across two readers — two separate replication streams, or a range-partitioned "parallel CDC" scheme someone invented to go faster — is a correctness bug and not just an optimisation. Two readers have no shared position space, and a row whose key moves between the two ranges has its history split across two independent streams with no way to interleave them. ## Transport: key by the primary key Between capture and sink, the stream is almost always parallelised, and the parallelisation is what puts ordering at risk. The rule is to derive the routing decision from the row's primary key — hash the key, send it to the corresponding lane — so every change to that row follows the same lane and keeps its relative order. Any scheme that distributes by arrival, by batch size, or round-robin across workers will, sooner or later, let the newer of two changes to the same row be written first. That failure is worse than a duplicate: a duplicate converges, a lost ordering leaves a permanently stale value in the sink that nothing later repairs. ```text source order for id=7: UPDATE tier='silver' (pos 100) UPDATE tier='gold' (pos 101) round-robin: worker A takes 100, worker B takes 101 B commits first -> tier='gold' A commits second -> tier='silver' <- stale value wins, forever ``` A useful corollary: the number of lanes is a capacity decision, but changing it rehashes keys, so a key's old and new lanes can be drained concurrently during the change. Pausing the sink, or draining before repartitioning, is the usual mitigation. ## Apply: one writer per key, newest per batch The sink is the last place ordering can be lost. Two common mistakes: - **Concurrent writers on the same key.** If the sink loader hands rows to a thread pool without partitioning by key, two updates to the same row race in the database and the loser's value can land last. Partition sink work by key, or serialise per key. - **A micro-batch containing several events for one key.** A `MERGE` whose source side contains two rows matching the same target row is an error on some engines and nondeterministic on others. Collapse first: rank the batch's events per key by source position and keep the highest. ```sql WITH latest AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY src_pos DESC) rn FROM cdc_batch ) MERGE INTO customers t USING (SELECT * FROM latest WHERE rn = 1) s ON t.id = s.id WHEN MATCHED 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); ``` Collapsing is also a large performance win: a row updated a thousand times inside one batch is written once. ## What per-key ordering does not give you It is worth being explicit about the limits, because interviewers probe them: - **No cross-key ordering.** Two different customers' updates can be applied in either order. Usually fine. - **No cross-table atomicity.** A source transaction that touched two tables becomes independent events on independent lanes, so a consumer can briefly see one table updated and not the other. - **No protection against a *replay* landing out of order.** After a restart, re-delivered events can interleave with newly captured ones depending on how the sink batches. Where that is possible, the durable fix is to store the source position on the sink row and refuse to apply an older one. - **Nothing when the key itself changes.** If the routing key is a mutable business key, the change that alters it is routed by a different value than the row's earlier history, and ordering with that history is gone. Key on an immutable identifier. ## What a good answer sounds like Name the three layers — capture, routing, apply — say that keying by primary key is what buys the guarantee, and immediately state the scope: per key, not global. Then mention the two sink-side traps (concurrent writers on a key, multiple events for a key in one batch). Candidates who only say "CDC preserves order" have not operated one.