The full answer
The changes were applied in the order they arrived, not the order they happened. CDC events can arrive late, be redelivered, or come in the same batch as later changes to the same row. A MERGE that simply takes "the row in this batch" lets an old update overwrite a newer one, and lets an update that arrives after a delete bring the row back. Apply each key's changes by a sequence number from the source and ignore anything older than what the table already holds.
Three bugs that cause this
| Bug | Result |
|---|---|
| Several changes for one key in a batch, MERGE picks one | Fails with "multiple source rows matched", or someone "fixed" that with dropDuplicates and kept an arbitrary row |
| An older change arrives in a later batch | It overwrites the newer state: the old address returns |
| An update arrives after the delete | The delete removed the row, the update re-inserts it |
The fix with plain MERGE
Keep the source's ordering column (a log sequence number, or commit timestamp plus an ID) on the target, and use it both within a batch and against the table:
from pyspark.sql import functions as F, Window
w = Window.partitionBy("customer_id").orderBy(F.col("lsn").desc())
latest = (changes.withColumn("rn", F.row_number().over(w))
.filter("rn = 1").drop("rn"))
(target.alias("t")
.merge(latest.alias("s"), "t.customer_id = s.customer_id")
.whenMatchedDelete(condition="s.op = 'DELETE' AND s.lsn > t.lsn")
.whenMatchedUpdateAll(condition="s.op <> 'DELETE' AND s.lsn > t.lsn")
.whenNotMatchedInsertAll(condition="s.op <> 'DELETE'")
.execute())
This handles duplicates and late updates. For a delete followed by a late update, the row is already gone, so whenNotMatchedInsertAll would bring it back. Keep a soft-delete flag (is_deleted = true with its lsn) instead of physically deleting, and filter it out in views; then the late update loses on sequence.
The managed option on Databricks
In a Lakeflow pipeline, AUTO CDC does this bookkeeping for you: give it the keys and SEQUENCE BY lsn (or a STRUCT of columns to break ties), mark which rows are deletes, and choose SCD type 1 or type 2. Late events with a lower sequence than the current state are ignored.
Repair the damaged table
Rebuild the affected keys from the CDC history in order, or take a fresh snapshot from the source and reconcile. Then add a daily check that compares row counts and a sample of rows with the source.
What interviewers want: that arrival order is not event order, a sequence column from the source, deduplicating per key within a batch, conditional MERGE on the sequence, the late-update-after-delete case, and knowing AUTO CDC.
Last updated 16 October 2026
Sources