Schema Enforcement in CDC

Change data capture (CDC) reads a database's own change log and turns each insert, update and delete into an event. An event carries the row's columns after the change, the operation, and the change's position in the source log, a number that only grows. Downstream, the pipeline rebuilds the table by applying those events in position order.

That makes a CDC feed the most drift-prone source a pipeline reads. Its producer is an application team running migrations against its own database, often unaware a feed exists. Each migration lands mid-stream: rows written before and after it sit side by side in the same partition, and the pipeline meets the new shape on the first row committed afterward.

Each DDL change as it arrives downstream

Change at the sourceWhat the event stream showsSafe for readers?
Add nullable `promo_code`New field, absent on older eventsYes
Widen `qty` INT to BIGINTSame field, larger valuesYes, if the target widens
Rename `qty` to `quantity``qty` vanishes, `quantity` appearsNo: a drop plus an add
Drop `fee`Field missing from new eventsNo, for readers of `fee`
`price` VARCHAR to DECIMAL(18,6)Same name, new typeNo: 2 types in one column
New `status` value `PARTIAL_FILL`Same type, unseen valueSchema yes, meaning no

The rename row causes the worst incidents. Row events carry no rename marker: the old name stops appearing and a new one starts. A pipeline that accepts every change writes nulls into qty from the migration onward, and every report summing qty loses the volume traded after it, with no error anywhere.

Additive, block-breaking and quarantine policies

PropertyAdditiveBlock-breakingQuarantine
Freshness during a breaking changeUnaffectedWhole table stallsAffected keys stall
How it failsSilent nulls and mixed typesSource log expires before restartKeys applied out of order
Who noticesA consumer, days laterOn-call, within minutesA quarantine-volume alert
FitsExploration and sandbox tablesLedgers, billing, audited tablesHigh-volume operational tables

Block-breaking carries a deadline. The source keeps its change log for a window its operators size in hours or days to cap disk use. A table blocked past that window loses its place in the log, and recovery turns from a resume at the last applied position into a full re-read of the table.

One CDC feed carries invoices (monthly billing), user_sandbox (an analyst playground) and positions (40,000 changes a minute behind a live dashboard). Which assignment fits?

Quarantine must respect order within a key

For a CDC table, quarantine can corrupt state, because events for one key are only correct when applied in source order. Below, the event at position 1002 is quarantined because price arrived as a DECIMAL, and 1003 applies. When the fix ships and 1002 is replayed as an upsert by order_id, the older state lands on top.

Source positionorder_idstatusOutcome
100142NEWApplied
100242PARTIAL_FILLQuarantined
100342FILLEDApplied: target says FILLED
1002 (replay)42PARTIAL_FILLUpsert: target says PARTIAL_FILL

Key-sticky quarantine parks every later event for a key once one of its events is parked, so the key goes stale instead of wrong. A version guard makes the write order-proof: the target stores the source position of the state it holds, and an incoming event applies only when its position is higher. Replaying 1002 against a row already at 1003 then changes nothing, and a rebuild from history uses the same rule. Careful pipelines run both.

SELECT
  order_id,
  status,
  price,
  source_position
FROM (
  SELECT
    order_id,
    status,
    price,
    source_position,
    row_number() OVER (
      PARTITION BY order_id
      ORDER BY source_position DESC
    ) AS rn
  FROM order_changes
) AS ranked
WHERE rn = 1

A trades table runs quarantine with no key-sticky parking. Event 1002 for order 42 is quarantined and 1003 (FILLED) applies. After the fix, the replay upserts by order_id with no position check. What does order 42 show, and what makes the replay safe?

About this lesson

This section is part of the Schema Drift: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.