Schema Drift: Advanced
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
- AdditiveEvolve the target, never stopCompatible 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-breakingStop at the first incompatible eventCompatible changes flow through. The first breaking event halts that table's stream and pages its owner, and nothing after it applies until someone decides.
- QuarantineDivert the bad, keep the rest movingCompatible 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 |
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 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 |
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?
Profile the source before picking a strategy
| Source | Lateness profile | Idleness profile | Strategy that fits |
|---|---|---|---|
| Order fills from the trading engine | p99 under 5 minutes, steady | Quiet overnight | Fixed delay plus idle timeout |
| Mobile app events | Long tail: offline phones replay hours later | Rarely idle | Percentile delay plus late-event handling |
| Clearing-house settlement messages | Arrive in batches per cycle | Silent for hours between cycles | Producer markers |
| Field sensors | Small, but some clocks drift ahead | Devices drop off for days | Future-time cap plus idle timeout |
The 4 strategies and the late data each one misses
- Fixed delayW = max event time - DPredictable and cheap. Fails on a tail longer than D: those events arrive behind the watermark and miss their window.
- Percentile delayD tracks observed latenessD is recomputed from a trailing percentile, such as p99 lateness over the last hour. A watermark may never move backward, so a sudden rise in D freezes it, and a sudden fall lets it race ahead of stragglers.
- Idle timeoutIgnore quiet partitionsA partition with no events for T is dropped from the minimum. When it wakes, its first events may sit behind a watermark that moved on without it.
- Producer markersThe source says when it is doneThe producer sends a marker meaning nothing older than time X is coming. Exact for sparse batch sources, and useless when the producer cannot make that promise.
Settlement messages arrive in batches up to 3 hours long, with hours of silence between cycles. Which watermark strategy fits?
One quiet partition stalls the whole job
| Partition | Newest event time | Last event received | Partition watermark |
|---|---|---|---|
| p0 | 10:04:58 | 10:05:00 | 10:04:28 |
| p1 | 10:04:51 | 10:04:59 | 10:04:21 |
| p2 | 09:58:10 | 09:58:12 | 09:57:40 |
A fast clock is worse than a slow partition
Start from the partition table above at 10:05:00 with a 2-minute idle timeout. p2 sends nothing more, and p1 goes silent after 10:04:59. At 10:07:30, p0's newest event time is 10:07:28. What is the job watermark?
Coverage comes from the lateness distribution
| Allowed lateness L | Counted if lateness up to | Events covered |
|---|---|---|
| 0 | 30 s | 96.0% |
| 5 min | 5 min 30 s | 99.2% |
| 1 hour | 1 h 0 min 30 s | 99.93% |
| 24 hours | 24 h 0 min 30 s | 99.995% |
State grows with the budget
| L | Open windows per key | Engine state | Events covered |
|---|---|---|---|
| 0 | 2 | 400 MB | 96.0% |
| 5 min | 7 | 1.4 GB | 99.2% |
| 1 hour | 62 | 12.4 GB | 99.93% |
| 24 hours | 1,442 | 288.4 GB | 99.995% |
Count coverage in the unit the consumer uses
Choosing a percentile target
A dashboard needs 99% of events inside its live number, and the job has a 2 GB state budget. Using the tables above, which L fits?
- Live number closer to final
- State grows with every key and window
- Restarts reload more state
- More re-emissions downstream
- Live number labeled provisional
- State sized to the knee of the curve
- Tail corrected in batch from raw events
- Exactness arrives on the pass's schedule
Each re-emission replaces the window's earlier result, so a consumer that appends results counts that window twice.
A team raises L from 1 hour to 24 hours so finance stops reporting missing late trades. Using the tables above, what changes?
Both paths read the same feed. The stream serves provisional totals within 2 minutes; the pass recomputes from every landed event and replaces the final partition.
Decisions that make the pass trustworthy
- Source of truthRecompute from the full recordRead raw landed events, including those the stream dropped as too late, or an authoritative outside record. The streaming output can only be compared against, never recomputed from.
- Settle horizonRun after the tail arrivesFor the trades feed a 24-hour budget already reaches 99.995%, so a pass a day after the window closes sees nearly everything.
- Same grain, same rulesMatch the stream exactlyCompute at the stream's keys, account and window start, and dedupe on event_id. A pass that counts duplicates the stream removed reports a delta that does not exist.
- Idempotent writeReplace, never appendReplace the partition for each recomputed day, so a rerun or the 7-day trailing backfill leaves one correct copy.
- Explicit authorityProvisional or finalLabel every row. Consumers that need audit-grade numbers read only final rows.
Computing the delta
| Events counted only by the pass | Reading | Likely cause |
|---|---|---|
| 0.06% to 0.09% | Healthy | The expected tail past 1 hour |
| 2.4%, all from 1 partition | Incident | An idle partition resumed more than L behind |
| 2.4%, starting at a migration | Incident | Key-sticky quarantine parked keys and was never replayed |
| 0.00% | Suspicious | The pass reads the streaming output instead of raw events |
- Recompute from raw landed events, kept in every schema version
- Match the stream's keys and dedupe rules exactly
- Give the delta an expected band and alert on both sides of it
- Replace whole partitions so reruns leave one copy
- Diff the stream against a copy of its own output
- Let consumers mix provisional and final rows without a status column
- Raise L to shrink the delta when the pass already corrects it
- Treat a zero delta as proof of health
The daily pass reports 2.4% of events counted only by the pass, all stamped 09:58 to 10:26 and all from p2, which went quiet at 09:58 and resumed at 11:28. Other days sit near 0.07%. What happened?
| Input | Shape | Lateness and idleness |
|---|---|---|
| `trades` table, via CDC | Insert per fill, update per correction or cancel | p99 4 minutes; quiet overnight |
| `instruments` table, via CDC | Reference data, rare changes | Changes a few times a day |
| Clearing-house settlement messages | A batch per cycle, owned by an outside firm | Silent for hours, then up to 3 hours of messages |
Decisions, piece by piece
| Piece | Choice | Failure it prevents |
|---|---|---|
| Schema: `trades` | Quarantine, key-sticky, version-guarded writes | A single bad migration stalling every account, or replays reverting state |
| Schema: `instruments` | Additive | Stalls over new descriptive columns no money depends on |
| Schema: settlement messages | Block-breaking | A changed amount format posting wrong cash to the ledger |
| Watermark: `trades` | D = 30 s, 2-minute idle timeout, 5 s future cap | Overnight quiet stalling results; a fast clock leaping the watermark |
| Watermark: settlements | End-of-cycle marker from the producer | Guessing completeness for a source that knows it |
| Allowed lateness | L = 1 hour on 1-minute account windows | 288.4 GB of state for 0.065 points |
| Reconciliation | 06:00 the morning after settlement date, trailing 7 days | Audit numbers that depend on the streaming tail |
Raw landing takes every payload before any schema check, so a fixed parser can rerun over it later. Quarantined events replay into the aggregator in source order.
The clearing house starts sending amount in cents instead of dollars, with the same field name and type. Which piece catches it, and when?
Walking the 14:12 rename through the system
| Time | What happens | What each consumer sees |
|---|---|---|
| Thu 14:12 | A migration renames `qty` to `quantity` in `trades` | Nothing yet |
| 14:12 to 15:40 | Every `trades` event after the rename lacks `qty` and is quarantined, parked by account, the aggregator's state key | The view marks the 1,900 accounts with a fill since 14:12 stale; 498,100 without one stay correct |
| Thu 15:40 | The parser now maps `quantity`; quarantine replays in source order through the version guard | Flagged accounts catch up, and no state reverts |
| Thu 15:40 | Replayed events stamped 14:12 to 14:38 are past L: the watermark kept advancing on events read before the schema check | Live totals for those minutes stay short |
| Sat 06:00 | The pass recomputes 2026-10-22 from raw landing with the fixed parser | The ledger gets exact final totals; delta reads 0.10% |
In the rename incident, the 15:40 replay runs through the version guard, yet live totals for 14:12 to 14:38 stay short until the Saturday pass. Why?
> Picture a ride-hailing company like Uber showing drivers their earnings minutes after each trip, while the weekly payout statement must match every fare, tip and adjustment exactly. Trips arrive by CDC from the trips database, tips trail trips by hours as riders add them later, and support-team fare adjustments land days afterward.
Keep live numbers fast and audit numbers exact when schemas drift, partitions go quiet and events arrive late.
- Category
- Pipeline Architecture
- Difficulty
- advanced
- Duration
- 40 minutes
- Challenges
- 11 hands-on challenges
Topics covered: Schema Enforcement in CDC, Watermarks for Irregular Streams, Allowed Lateness Versus Accuracy, Reconciliation Passes, Late Trade Settlements
Lesson Sections
- 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 un
- Watermarks for Irregular Streams (concepts: Stream Processing)
Every watermark strategy assumes something about how late events arrive. The common default, bounded out-of-orderness, sets the watermark to the newest event time seen minus a fixed delay D. It works when lateness is steady and capped, as on a feed of order fills from a trading engine. Real sources break that assumption in 3 ways: a long tail of stragglers, partitions that fall silent, and clocks that run fast. Profile the source before picking a strategy The 4 strategies and the late data each
- Allowed Lateness Versus Accuracy (concepts: Late-Arriving Data)
Allowed lateness L keeps a window's state alive after the watermark passes its end. An event that arrives within L still updates the window, and the engine emits a corrected result; one that arrives later is dropped or sent to a side output. Together, D and L decide 3 things at once: how complete the streaming result is, how much state the engine holds, and how often a consumer sees a number change. Coverage comes from the lateness distribution An event updates its window while the watermark, ne
- Reconciliation Passes
Every allowed-lateness budget leaves a tail, and some consumers cannot accept one: a ledger, a regulatory report, an invoice. A reconciliation pass is a batch job that recomputes the streaming output from the complete record after the tail has arrived, compares the 2, writes the corrected version as the version of record, and publishes how far apart they were. Decisions that make the pass trustworthy Computing the delta The pass and the comparison fit in one query: dedupe the raw events, aggrega
- Late Trade Settlements (concepts: Late-Arriving Data)
Here the 4 pieces run as one system. A brokerage serves 2 consumers from the same trades. The live buying-power view shows each account's net unsettled amount and must reflect a fill within 2 minutes. The settlement ledger needs exact totals per account per trade date. US equity trades settle on the next business day (T+1), so trades from Thursday 2026-10-22 settle Friday 2026-10-23. Decisions, piece by piece The settlement feed gets the strictest policy. Its volume is small, its consumer tolera