Change Data Capture (CDC)
How modern pipelines keep a warehouse fresh to the second — by streaming every insert, update, and delete out of a source database the instant it commits.
What you'll learn
- What a change event is — operation, before/after image, and commit order
- Query-based capture (polling) versus log-based capture (reading the transaction log)
- Why polling is blind to deletes — and the log isn't
- Where CDC feeds: warehouse replication, SCD Type 2, and event-driven systems
Before you start
A nightly batch job answers “what did the business look like yesterday?” For a growing list of jobs — fraud checks, live dashboards, keeping a search index in sync — yesterday is far too old.
Change Data Capture closes that gap. Instead of re-copying a whole table on a schedule, it captures every individual change — each insert, update, and delete — the moment it commits, and streams it onward. The warehouse stops being a stale snapshot and becomes a near-real-time mirror. The mental shift is that a row change is now data: each CDC event carries the operation (create / update / delete), the new row, usually the old row too, and a position that fixes its place in commit order. Replay that stream in order and you reconstruct the source table exactly.
Two ways to capture, and one decisive difference
There are two families of CDC, and the gap between them is the whole lesson. Query-based (polling) adds an updated_at column and periodically runs SELECT * WHERE updated_at > :last_seen. Dead simple, no special access — but it has a hole you cannot patch: a DELETE removes the row entirely, leaving no updated_at for the next query to find, so deletes are invisible. It also only sees the latest state between polls, missing intermediate changes. Log-based capture instead reads the database’s write-ahead log (Postgres WAL, MySQL binlog) — the ordered, durable record every transactional database already writes for crash recovery — and turns it into a change stream. It sees everything, deletes included, in exact commit order, at minimal cost, because the database wrote that log anyway.
The canonical tool is Debezium: it reads Postgres logical decoding or MySQL binlog, turns the log into structured change events, and publishes them (usually to Kafka), where sink connectors land them in a warehouse or index. A real pipeline also takes an initial snapshot of the table’s current contents once, then tails the log from the exact position where the snapshot ended — so nothing is missed or double-counted at the boundary. From there CDC feeds warehouse replication (the low-latency face of ELT), drives SCD Type 2 (the change stream is the input to the dimension-history MERGE), and powers event-driven systems (cache invalidation, search indexing) off the same committed changes.
Practice
Quick check
Questions about this lesson
What is Change Data Capture (CDC)?
CDC captures every individual change — insert, update, and delete — made to a source database and streams it onward the moment it commits, instead of re-copying whole tables on a schedule. Each change event carries the operation, the new (and usually old) row image, and a position in commit order, so a consumer can keep a warehouse or other system continuously in sync.
What is the difference between log-based and query-based CDC?
Query-based CDC polls the source with something like WHERE updated_at > last_seen — simple, but blind to deletes and to intermediate states between polls. Log-based CDC reads the database's transaction log (Postgres WAL, MySQL binlog), so it captures every insert, update, and delete in exact commit order with minimal load. Log-based is the robust choice; Debezium is the common tool.
Why can't query-based CDC capture deletes?
Query-based polling finds rows whose updated_at timestamp advanced. A DELETE removes the row entirely and leaves no timestamp behind, so the next poll has nothing to match and never sees the row disappear. Only reading the transaction log (or using soft-delete tombstones) captures deletions — which is why deleted rows linger as stale ghosts under polling.
Practice this in an interview
All questionsCDC continuously captures row-level inserts, updates, and deletes from a source database and streams them downstream — enabling near-real-time replication to a warehouse or data lake without full table scans. The most robust implementation reads the database's write-ahead log (WAL), making it low-impact on the source and capable of capturing deletes that polling-based approaches miss entirely.
Batch pipelines process data in bounded chunks on a schedule — simple to build and test, but latency is measured in hours or days. Streaming pipelines process records continuously as they arrive — latency drops to seconds or milliseconds, but correctness requires handling late arrivals, watermarks, and stateful aggregations. Choose streaming when business decisions need fresh data; choose batch when daily freshness is acceptable and operational simplicity matters.
Schema evolution covers adding, renaming, removing, or retyping columns in a data stream or table over time. Safe strategies include: only adding nullable columns (backwards-compatible), using schema registries to enforce compatibility rules before a producer publishes, and open table formats like Iceberg that track schema history and allow column renames and reorders without rewriting data.
An idempotent pipeline produces the same output no matter how many times it runs for the same logical window — rerunning it on an already-processed date partition yields identical results rather than duplicated rows. Achieving idempotency typically means using INSERT OVERWRITE (or MERGE) instead of plain INSERT, keying every record with a deterministic ID, and deleting-then-inserting the target partition before writing.