What breaks in a CDC sink when the source updates the column used as the row's key?

## Why the key is load-bearing Two separate mechanisms in a CDC pipeline hang off the key of a change event. Routing hashes it to decide which lane carries the event, which is what preserves the row's ordering. The sink's merge matches on it to decide whether to update an existing row or insert a new one. Both assume the key identifies the same real-world row for that row's entire lifetime. A key that can change violates the assumption in both places at once, and the failure is quiet. ## What actually happens ```text source: UPDATE products SET sku = 'A2' WHERE sku = 'A1' event keyed by sku=A2 -> hashes to a different lane than A1's history sink merge on sku -> no row matches A2, so it INSERTS a new row sink row sku=A1 -> never updated, never deleted: an orphan ``` The target now holds two rows where the source holds one. Counts are inflated, a join against another table can match the stale row, and every subsequent change to that product updates only the new row, so the ghost freezes at the values it had at the moment of the rename. Because nothing errors, this typically surfaces weeks later as a reconciliation mismatch that nobody can reproduce. ## Fix 1: key on something immutable The clean answer is that the routing and merge key should be a surrogate identifier the source guarantees is stable for the row's lifetime — a generated numeric or UUID primary key — and the business key should be an ordinary column. A rename is then just another column update: same key, same lane, same target row, no orphan, no ordering break. When you can influence the source schema, this is the fix; the rest are mitigations. ## Fix 2: translate the change into a delete plus an insert If the key must be the mutable column, the change has to be expressed as the removal of the old identity and the arrival of the new one. Some engines make this easy because their log records the whole previous row for an update, so the capture can see the old key value and emit a delete for it alongside the upsert of the new one. Others log only the key columns for an update, in which case the old value may simply not be available and there is nothing the connector can reconstruct — a limitation worth checking on the specific source before promising anything. Even when the pair of events is produced, they are keyed differently and therefore travel different lanes, so they can be applied in either order. Applying the insert first and the delete second is fine; applying the delete first and the insert second is also fine; but a *replay* that re-applies the delete after the new row exists under the same key would remove live data. This is another reason to persist the source log position on the sink row and refuse any event whose position is not newer, and to model deletion as a flag rather than a physical removal so the position survives. ## Fix 3: forbid key updates The cheapest fix of all, when you own the source application: make the identifier column immutable by policy and enforce it with a constraint or a trigger. Most business keys are mutable only by accident — nobody actually intended email addresses to be primary keys — and the change is often smaller than the downstream mitigations. ## Detecting the damage you already have Orphans do not announce themselves, so add reconciliation: compare the target's row count and per-key-range checksums against the source on a schedule. A count that exceeds the source by a slowly growing margin is the signature of both dropped deletes and key-update orphans. To distinguish them, look for target rows whose last-updated position is far behind everything around them — a row that stopped receiving changes while its neighbours kept moving is almost always an orphan. Repairing is usually a full re-load of the affected table rather than a surgical fix, because you generally cannot tell from the target alone which old rows were renamed into which new ones. That expense is the argument for fixing the key choice rather than patching around it. ## Related trap: composite and nullable keys Two cousins of this problem show up in the same conversation. A composite key that includes a mutable column has exactly the same failure for the same reason. And a target keyed on a column that is nullable in the source will merge every null-keyed row into one, or fail the match entirely, depending on the engine's null semantics — so check that the chosen key is both immutable and non-nullable before building on it. ## What an interviewer is looking for Recognition that the key is used for two things (routing and matching) and that a change breaks both, a concrete description of the orphan, and a preference for the immutable-surrogate fix over clever downstream repair. Mentioning that the delete-plus-insert pair needs a position guard because the two events race is the detail that shows real exposure.