Databricks Data Engineer Professional: A MERGE that fails on duplicates, goes stale on retry, and silently keeps deleted orders

A MERGE that fails on duplicates, goes stale on retry, and silently keeps deleted orders

silver.orders is fed from bronze.orders_cdc, an append-only stream of CDC events from Kafka (about 9 million rows a day, roughly 40 events per second across about 2 million order keys). A Structured Streaming foreachBatch job merges each micro-batch into silver.orders (SCD type 1, 300 million rows) with MERGE INTO silver.orders t USING batch b ON t.order_id = b.order_id WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT *. Twice this week the job failed with Cannot perform Merge as multiple source rows matched and attempted to modify the same target row. After a manual retry, about 4,000 orders show totals older than the events sitting in bronze. An order hard-deleted upstream still appears in silver. A CHECK (order_total >= 0) constraint added last sprint is rejected by 1,100 historic rows. Nobody will approve a full nightly reprocess of 300 million rows.

1. Fix the merge failure and the stale totals, without reprocessing the whole table.

Read the worked answer

Duplicate match The same order_id can appear more than once in a micro-batch - Kafka delivery is at-least-once and CDC emits several events per key. Delta requires at most one source row per target row.

Deduplicate inside foreachBatch before merging:

SELECT * FROM (
  SELECT *, row_number() OVER (PARTITION BY order_id ORDER BY sequence_num DESC) rn
  FROM batch_view
) WHERE rn = 1

or aggregate with max_by(struct(*), sequence_num) grouped by order_id. Write the deduped frame to a temp view and merge from that.

Stale totals UPDATE SET * applies whatever the batch contains with no ordering guard, so a replayed or out-of-order batch can overwrite newer values. Persist sequence_num (or updated_at) in silver and make the merge order-aware:

WHEN MATCHED AND s.sequence_num > t.sequence_num THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *

This makes retries idempotent: replaying an old batch is a no-op for rows that have moved on. Repair the 4,000 affected orders with a targeted merge from bronze for just those keys.

If you move this to a Lakeflow/DLT pipeline, APPLY CHANGES INTO ... KEYS (order_id) SEQUENCE BY sequence_num handles deduplication and ordering for you.

2. The hard-deleted order still shows up. Why, and how do you handle deletes in this design?

Read the worked answer

A MERGE can only delete when the source tells it to. If the CDC producer emits a delete event, the fix is a branch in the merge:

WHEN MATCHED AND s.op = 'DELETE' THEN DELETE

plus the same sequence_num guard so an old insert cannot resurrect the row after a newer delete. Also confirm bronze actually receives and retains the delete events - several connectors drop them or convert them to null-filled updates unless configured otherwise.

If the source hard-deletes without emitting an event, nothing arrives and no merge logic can help. Options, in order of preference: ask for soft deletes plus tombstones from the source; otherwise schedule a periodic reconciliation that compares a source-of-truth extract of keys against silver and marks or removes the missing ones; or keep a _deleted_at soft-delete column in silver and filter in the gold layer so you never lose referential history.

3. A `CHECK (order_total >= 0)` constraint exists but 1,100 historic rows violate it. Explain what that means and how to resolve it.

Read the worked answer

Databricks enforces CHECK constraints on new writes and on updates to a row, but it does not validate rows already in the table when you add the constraint. So the constraint was accepted, existing bad rows survive, and the practical effect is that any future MERGE or UPDATE touching one of those rows will fail or be rejected - a slow-burning incident rather than a clean one.

Resolution without a full reprocess:

Identify the 1,100 offending rows with a SELECT ... WHERE order_total < 0 and decide per row whether the source value is wrong (fix from bronze with a targeted merge on those keys) or the rule is wrong (such as refunds legitimately stored as negatives - then the constraint itself is the bug and should be narrowed to exclude refund order types).
Apply the targeted fix, re-run the violating-row count until it returns zero.
Keep the constraint in place; it is now a genuine guard against new bad data.
Add a scheduled quality check that counts violations, so a future rule change cannot silently accumulate debt again.