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 source | What the event stream shows | Safe for readers? |
|---|---|---|
| Add nullable `promo_code` | New field, absent on older events | Yes |
| Widen `qty` INT to BIGINT | Same field, larger values | Yes, if the target widens |
| Rename `qty` to `quantity` | `qty` vanishes, `quantity` appears | No: a drop plus an add |
| Drop `fee` | Field missing from new events | No, for readers of `fee` |
| `price` VARCHAR to DECIMAL(18,6) | Same name, new type | No: 2 types in one column |
| New `status` value `PARTIAL_FILL` | Same type, unseen value | Schema 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
- Additive: Compatible changes are applied to the target automatically, and breaking ones are absorbed as additions: a renamed column becomes a new column beside an old one that fills with nulls.
- Block-breaking: Compatible changes flow through. The first breaking event halts that table's stream and pages its owner, and nothing after it applies until someone decides.
- Quarantine: Compatible events flow. Breaking ones land in a quarantine table with their raw payload and schema version, and the rest of the stream keeps applying.
| Property | Additive | Block-breaking | Quarantine |
|---|---|---|---|
| Freshness during a breaking change | Unaffected | Whole table stalls | Affected keys stall |
| How it fails | Silent nulls and mixed types | Source log expires before restart | Keys applied out of order |
| Who notices | A consumer, days later | On-call, within minutes | A quarantine-volume alert |
| Fits | Exploration and sandbox tables | Ledgers, billing, audited tables | High-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?
- invoices additive, user_sandbox block-breaking, positions quarantine
- All 3 block-breaking, so no table can ever hold wrong data
- invoices quarantine, user_sandbox additive, positions block-breaking
- invoices block-breaking, user_sandbox additive, positions quarantine
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 position | order_id | status | Outcome |
|---|---|---|---|
| 1001 | 42 | NEW | Applied |
| 1002 | 42 | PARTIAL_FILL | Quarantined |
| 1003 | 42 | FILLED | Applied: target says FILLED |
| 1002 (replay) | 42 | PARTIAL_FILL | Upsert: 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 = 1A 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?
- FILLED, since an upsert keeps the first row it wrote; no change is needed
- PARTIAL_FILL, since the replay wrote last; a source-position check on write fixes it
- PARTIAL_FILL, since the replay wrote last; running the replay a second time fixes it
- FILLED, since quarantined events are discarded; a full re-read of the table is needed
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.