What is change data capture?
Also known as
CDC
Change data capture (CDC) is a technique for detecting row-level changes in a source database — inserts, updates, and deletes — and streaming just those changes to another system, usually a data warehouse. Instead of asking “what does this table look like right now?”, CDC asks “what happened to this table since the last time I looked?”
Log-based CDC
Every serious transactional database already writes a durable record of every change it makes, because it needs one to recover from a crash and to feed replicas: Postgres has the write-ahead log (WAL), MySQL has the binlog, SQL Server has its transaction log. Log-based CDC reads that log directly, decoding it into a stream of change events.
This is the good version. It catches every change including deletes, it captures intermediate states rather than just the final value, it doesn’t require touching the table schema, and it puts almost no query load on the source database — the reader looks like just another replica. The costs are operational: you need replication-level access, you need to keep the log from being recycled before your reader consumes it, and schema changes upstream have to be handled deliberately.
Query-based CDC
The simpler approach is to poll. You add an updated_at column, index it, and repeatedly ask the database for anything newer than your last high-water mark:
SELECT *
FROM orders
WHERE updated_at > '2024-05-01 03:00:00'
ORDER BY updated_at;
Query-based CDC works on any database and takes an afternoon to set up. Its weaknesses are real, though. Hard deletes are invisible — the row is simply gone, and no query can tell you it left. Any code path that updates a row without touching updated_at silently drops that change. And rows that change several times between polls only ever show you the last state.
Why it beats full-table snapshots
The naive alternative is to re-copy the whole table on every run. That’s fine for a 50,000-row reference table and untenable for a 500-million-row events table: the snapshot is slow, expensive, hammers the source database, and forces you to run it rarely — which is exactly why “the dashboard is a day behind” is such a familiar complaint.
CDC decouples freshness from table size. The work per run is proportional to the number of changes, not the number of rows, so you can run it every few minutes on a table that would take hours to snapshot. Most managed pipeline tools do a one-time historical snapshot to seed the destination and then switch to CDC for everything after, which is a sensible default for any table large enough that you’d notice a full copy.
Key article
Related terms
Put it to work
- Build a data pipeline — Integration
- Stripe — Integration
- Ecommerce analytics — Overview
