Change Data Capture & Incremental Processing
What does it cost to know what changed, and is that cheaper than not needing to know?
Who this mechanism is for: a developer who writes code, calls APIs and knows basic SQL, but has never built a pipeline that copies data out of a live database. You do not need to know what a write-ahead log, a message broker or a table format is. Every technical term is explained the first time it appears, and again in the glossary at the end.
The story we will follow
Tariq is a data engineer at Quillpost, a small newsletter-delivery company (it prints newsletters and delivers them to subscribers with its own drivers). The main application stores orders, customers, drivers and deliveries in a PostgreSQL database. This database is run by Lena, the database administrator (DBA). Tariq's job is to copy this data into an analytics warehouse every day, so that managers can see dashboards: orders per city, revenue per customer, late deliveries.
Until now Tariq rebuilt every warehouse table from scratch each night. The tables are growing, and the rebuild is taking hours. So Tariq decides to move to incremental processing: copy only the rows that changed. Over six weeks he will meet six problems, one at the start of each article below. Each problem looks like a bug. Each is actually a consequence of how change detection, ordering and arithmetic work. At the end of each article we go back and solve it.
The main idea of this mechanism:
How this mechanism is organized
- 1
The break-even
Why is incremental processing different from everything before it, and when is it worth building at all?
CaseIncremental, and slower than the full rebuild
- 2
Detect
How do we find out what changed in a database we do not control, and what does each method lose?
CaseCancelled orders still on the dashboard, and a full disk
- 3
Coarse incrementality
When can we avoid change capture completely?
CaseA late batch the watermark never saw
- 4
Bootstrap and represent
How do we start from nothing, and what must a change event contain so it can be applied safely?
Case4,000 orders with an old status after the copy
- 5
Apply
How do we apply changes to a target, which computations can absorb a change cheaply, and what does a history table demand of the pipeline?
CaseDuplicate orders and a 4,000-file rewrite
- 6
Repair
How do we backfill, replay and avoid double writes?
CaseA 60-day bug and 30 days of retention
- 7
What change data capture forbids
What can this whole area never do, and what does each escape cost?
Case0.3% short, and every pipeline green
After that: a summary, hands-on labs, interview questions and a glossary.
What do I do, and what does each option cost?
Can I see it myself?
Find your own break-even point
Time a full rebuild against an incremental merge as the changed fraction grows, and find where they cross.
MeasurePython with DuckDBTrigger overhead
Time single-row and bulk inserts with and without a shadow-table trigger that records every change.
Real systemDocker, PythonWatch the log retention problem happen
Create a logical replication slot, never read it, and watch the database keep its write-ahead log until the slot is dropped.
Real systemDocker, PythonAliasing in a polling pipeline
Poll a fast-changing row every few seconds and count how many of its changes, and its delete, the poller ever sees.
Break itPythonWhat query-based CDC misses
A poller on updated_at against three changes it cannot see: intermediate updates, a delete, and a slow commit.
Real system · Break itDocker, PythonSum versus max under deletes
Maintain SUM and MAX per group under inserts and deletes, and count the rescans and memory each needs.
SimulatePythonInject loss, then catch it
Drop 0.1% of the changes in a toy CDC pipeline, catch it with a count + checksum, and crash it under three cursor-commit orders.
Break itPython
Can I reason about it under questioning?
Interview questions
Short questions on change data capture and incremental processing, such as when incremental pays, ways to detect changes, snapshots and change events, applying changes and repairing pipelines, each with an answer you could give aloud and a link to the full explanation.
Scenarios
Longer problems on change data capture and incremental pipelines, of the kind asked in senior interviews: each one estimated with numbers, diagnosed, and answered with its trade-offs.
The tensions this mechanism takes a position on. Each recurs across mechanisms.
Precompute vs. compute-on-read
For example: materialized views, ELT vs. ETL, schema-on-read.
Exactness vs. approximation
For example: sketches, sampling, ANN.
Early binding vs. late binding
When structure is committed; determines where the failure surfaces, at write time or read time.
Consistency vs. availability vs. latency
For example: quorums, replication modes.
Coupling vs. coordination cost
For example: 2PC vs. sagas, schema contracts vs. schema-on-read.
Ideas this mechanism meets that keep coming back, mechanism after mechanism.
Retention is paid by the live path
Costs are conserved
Costs are conserved, never removed.
Granularity mismatch
Granularity mismatch creates amplification.
The algebra decides
What can be skipped, pushed down, parallelized, merged or precomputed is determined by the algebra of the function, not the layout of the data.
Absence is not evidence
Absence of an event is not absence of a failure.
Show all 10 principlesShow fewer
Fixed cost per operation
Fixed cost per operation implies a natural batch size.
Immutable storage grows indirection
An immutable substrate grows an indirection layer plus a collector.
Designate one write
Atomicity over n items is atomicity over one designated item.
Masking moves failure
Masking a failure moves it from a channel with a schema to a channel without one.
Guarantees have scope
Atomicity is bounded by the shared commit substrate.