Quick answer:
Change data capture (CDC) is a technique for copying a database by watching its changes instead of re-reading its tables. Every insert, update, and delete gets picked up from the database’s internal transaction log and replayed into the destination, usually within seconds. It’s how you keep a warehouse copy of a production database fresh without hammering the database itself.
If replication is photocopying the whole book every night, CDC is subscribing to the edits.
What is change data capture?
Every serious database keeps a write-ahead log: an append-only record of every change, written before the change itself is applied. Postgres calls it the WAL, MySQL the binlog, SQL Server and Oracle have their own. The log exists for crash recovery and replication between database servers.
CDC tools tap that same log for analytics. A connector reads the stream of changes, translates them into rows, and applies them to your warehouse copy. Because it’s reading a log rather than querying tables, the production database barely notices, and deletes (which a normal query-based sync can’t see, since the row is simply gone) come through explicitly.
How does CDC compare to the alternatives?
Three ways to sync a database, in ascending order of quality:
- Full re-sync: copy every table on a schedule. Simple, brutal on large tables, and your data is only as fresh as the schedule.
- Incremental queries: “give me rows where
updated_atchanged since last time”. Lighter, but it trusts the app to maintain that column, and it never sees deletes. - Log-based CDC: reads the transaction log. Near real-time, catches deletes, minimal source load. The catch is setup: you need replication privileges and the right database config, which means a conversation with whoever guards production.
There’s also trigger-based CDC (the database fires a trigger on every change and writes it to a side table). It works, but it adds write overhead to production, so log-based has mostly won.
Where did CDC come from?
Database replication is ancient, but CDC as an analytics commodity is recent. The turning point was Debezium, the open-source project (out of Red Hat) that standardized log reading for Postgres, MySQL, MongoDB, and friends, streaming changes into Kafka. Commercial tools then packaged the whole thing: Fivetran bought HVR in 2021 largely for its enterprise CDC engine, and Airbyte ships Debezium-based CDC for its database sources.
What are the gotchas?
CDC is the most reliable sync method and also the most operationally spiky. Things that bite in practice:
- Log retention. If your connector goes down longer than the database keeps its log, you’ve lost the thread and must full-resync. Size the retention window generously.
- Schema changes. Someone adds a column upstream and your pipeline either adapts or breaks. Good connectors handle common cases; test the ugly ones.
- Cost surprises. Usage-priced tools charge on rows changed, and CDC faithfully delivers every change, including bulk updates that touch millions of rows nobody needed synced. A single backfill upstream can blow a monthly quota.
- Initial snapshot. Before streaming changes, the tool has to copy the existing data once. On multi-terabyte tables, plan that snapshot like a small project.
What are the benefits and drawbacks of CDC?
Benefits of CDC
Freshness measured in seconds
Changes stream as they commit, so warehouse copies trail production by moments instead of a nightly schedule. Operational dashboards and fraud checks become possible at all.
The production database barely notices
Reading the transaction log sidesteps the query load that batch re-syncs inflict. Your DBA stops flinching when analytics asks for another table.
Deletes finally exist
Query-based syncs can’t see a vanished row; the log records the deletion explicitly. For anything where correctness matters (billing, compliance), that alone settles the argument.
Every intermediate state, if you want it
The log shows each version a row passed through, which enables audit trails and slowly-changing-dimension history that snapshot syncs simply never saw.
Drawbacks of CDC
It’s a production dependency now
Replication slots, log retention, and database privileges tie your pipeline to production’s configuration. Setup requires the keeper of that database to trust you, and mistakes are visible to everyone.
Fall behind and you start over
If the connector outage outlasts log retention, the stream is broken and a full re-snapshot is the only cure. On big tables that’s a planned incident.
Usage pricing amplifies upstream noise
A bulk update touching 50 million rows syncs 50 million changes, whether anyone downstream needed them or not. Row-priced tools turn other teams’ migrations into your bill.
Complexity beyond the happy path
Schema evolution, type mismatches, and out-of-order edge cases are where CDC engineering time actually goes. The demos never show week 6.
When do you actually need it?
When freshness matters (operational dashboards, fraud checks, anything a human acts on within the hour), when tables are too big to re-copy, or when deletes matter for correctness. If you’re syncing a 2 GB database into a warehouse for weekly reporting, nightly batch is fine and nobody will ever know the difference.
The pattern we’d push against is CDC as a default because it sounds rigorous. It’s a production dependency with failure modes. Buy the freshness when something downstream actually consumes it.