CDC Into Delta: Idempotency, Deletes and Schema Drift
MetroMart's point-of-sale platform runs on SQL Server 2019 with change data capture enabled on 38 tables. The largest table holds 1.8 billion sales lines, and the platform emits 3-5 million change rows on a normal trading day. Business wants bronze data in Delta within ten minutes of a change, and a silver table that always reflects current state, including deletes. The current IDMC mapping performs a nightly full reload followed by hourly "update else insert" runs. The nightly job takes 3.5 hours, duplicate order lines appear in silver, deleted orders never disappear, and two weeks ago a new column added to the source broke the pipeline with a schema mismatch. The Databricks job cluster is set to terminate after 20 minutes of inactivity, adding six to eight minutes of cold start to every hourly run. Nobody can say whether re-running a failed hour is safe.
1. Redesign the bronze-to-silver flow so that it is idempotent and correctly ordered. Specify the metadata you must carry and why each item is needed.
Read the worked answer
Separate ingestion from merge. Bronze should be an append-only log of change rows exactly as CDC emitted them, plus metadata. Silver is current state, produced by a deterministic MERGE. That split is what makes replay safe.
Metadata to carry into bronze:
- Primary key, for example order_id plus line_id.
- CDC sequence - the LSN or commit sequence number, not the event timestamp. Timestamps collide and arrive out of order.
- Operation flag: insert, update, delete.
- Ingestion timestamp, source table, and file or partition-offset for traceability.
Idempotency. Re-running an hour must be a no-op. Two sound options:
- Append-only bronze with dedupe at read time:
ROW_NUMBER() OVER (PARTITION BY pk ORDER BY cdc_seq DESC) = 1. - MERGE with a guard:
WHEN MATCHED AND source.cdc_seq > target.cdc_seq THEN UPDATE. A replayed older row fails the guard and changes nothing.
Why the current job duplicates rows. "Update else insert" against a table with no key and no sequence guard inserts whenever the match fails - including when it fails because of case, whitespace or a null key. Deduplicate each micro-batch before the merge and enforce a primary key on the Delta table.
Decoupling. None of this should depend on a cluster staying warm. The bronze append is a file write and the MERGE is a transaction; both are re-runnable, which is precisely how you answer the open question about failed hours.
2. How do you handle deletes and late-arriving or late-committed changes without corrupting current state?
Read the worked answer
Deletes. CDC emits them; do not filter them out at ingestion. Carry op = 'D' into bronze, then in the MERGE use WHEN MATCHED AND src.op = 'D' AND src.cdc_seq > tgt.cdc_seq THEN DELETE for hard deletes, or set a soft-delete flag and deleted_at when downstream consumers need history.
Late-arriving changes break any design keyed on event time. Ordering must come from the CDC sequence, and the merge guard must compare sequence numbers in both directions:
- An older change arriving after a newer one fails the guard and is ignored - correct, because the newer state already won.
- An out-of-order delete followed by a higher-sequence insert resurrects the row - correct if that is what the source did.
Tombstones. If the source purges a row and only the delete survives, the merge still needs a target row to act on. Keep the row in silver as a soft-deleted tombstone rather than physically removing it when late updates may still arrive.
Batch-level collapse. Before merging a micro-batch, reduce it to one row per key ordered by cdc_seq DESC. That cuts MERGE volume and prevents two changes to the same key in one batch from racing.
Verification. Reconcile row counts against the source CDC table per table per hour, and keep a set of deliberately replayed batches in test as proof that idempotency actually holds rather than being asserted.
3. The 6-8 minute cold start and the schema-drift break both come from the platform, not the data. What do you change, and what process control prevents the drift from reaching production again?
Read the worked answer
Cold start. Six to eight minutes per hourly run wastes roughly 12-13% of the cycle. Options, cheapest first:
- An instance pool of pre-warmed idle instances, so clusters start in tens of seconds.
- A small always-on job cluster if the cadence and SLA justify it - compare pool cost against six minutes times twenty-four.
- Move the MERGE to a serverless SQL warehouse, removing cluster start from the path entirely.
- Run all 38 tables on one cluster rather than paying 38 cold starts.
Schema drift. A new source column should not break the pipeline. In Databricks, Auto Loader with schema evolution, or mergeSchema on the write, adds new columns to bronze; keep a rescued-data column so a type conflict quarantines the row instead of dropping it. Silver transforms should project explicitly named columns, so a new bronze column stays inert until someone models it.
On the IDMC side, enable schema drift handling on the target and eliminate SELECT * from mappings. The mapping should tolerate an added column and fail only on a removed or retyped column it actually reads.
Process control. The real failure is that a source DDL change reached production undetected. Add a contract check at the start of each run comparing the source information schema to an expected definition, and alert before the load rather than after. Couple it with a column-level lineage check so the blast radius of an upstream change can be answered in minutes.