Change data capture: keeping systems in sync without nightly batch

Two systems need the same data and they drift apart. The classic fix, a nightly batch export and reload, means the target is always up to a day stale, the job gets slower as volumes grow, and a single failed run leaves everything inconsistent. Change data capture replaces that pattern with a continuous stream of every change. This article covers how CDC works, which method to use, and the production pitfalls that are not on the happy path.

What change data capture does

Change data capture is a technique for detecting and streaming every row-level change (insert, update, delete) in a source database, as it happens, to one or more downstream consumers. Instead of periodically copying a whole table and diffing it, CDC emits a fine-grained event for each change, in order.

The immediate benefit is freshness: the target system reflects the source within seconds instead of hours. The deeper benefit is decoupling. The source database keeps doing its transactional job, and any number of consumers (a data lake, a search index, a cache, another service) subscribe to its change stream without the source knowing or caring who is listening.

This is why CDC sits at the center of modern data integration. It is the mechanism that feeds real-time pipelines, keeps microservices in sync, and lands operational data in analytical stores without nightly batch windows.

Query-based versus log-based CDC

There are two fundamentally different ways to capture changes, and the choice determines how robust the pipeline is.

Query-based (polling) CDC repeatedly queries the source for rows changed since the last check, usually via an updated_at timestamp or a version column. It is simple to set up and needs no special database access. It also has three serious limitations: it cannot see deletes (a deleted row simply stops appearing, with no event), it adds query load to the operational database, and it misses intermediate states if a row changes twice between polls. For anything beyond a low-stakes sync, these are disqualifying.

Log-based CDC reads the database transaction log directly: the write-ahead log (WAL) in PostgreSQL, the binlog in MySQL, redo logs in Oracle, the transaction log in SQL Server. Every committed change is already recorded there for durability and replication, so CDC consumes that stream instead of querying tables. It captures deletes, imposes almost no load on the source, preserves order, and never misses an intermediate state. It is the correct default for production.

The tradeoff is access and setup: log-based CDC needs replication privileges and correct log-retention configuration on the source. That is a one-time cost worth paying.

Debezium and Kafka in practice

The de facto open-source CDC stack is Debezium running on Kafka Connect. Debezium provides log-based connectors for PostgreSQL, MySQL, Oracle, SQL Server, MongoDB and others. Each connector reads the source transaction log and publishes an ordered stream of change events to Kafka topics, typically one topic per table.

From there the pattern is standard event streaming. Consumers read the change topics and apply them: a sink connector lands them in a data lake or warehouse, a service updates a search index, a cache invalidates the affected keys. Kafka's retention and replay mean a consumer can be added later and catch up from a chosen point, which is a property batch exports never had.

For teams already on a managed cloud, equivalent managed CDC exists (AWS Database Migration Service, Google Datastream, and the CDC features of platforms like Fivetran), trading some control for less operational burden. The architectural shape is the same: transaction log in, ordered change stream out.

The parts that actually break

The happy path of CDC is straightforward. Production reliability lives in four problems that are easy to underestimate.

• Initial snapshot. Before streaming changes, the consumer needs the current state of the table. Debezium takes a consistent snapshot first, then switches to streaming. For a large table this snapshot is heavy and must not lock the source or fall behind the log. Incremental snapshotting exists precisely because naive snapshots stall on big tables.
• Schema changes. The source adds a column, changes a type, renames a field. The change stream has to carry that schema evolution, and downstream consumers have to handle it. A schema registry (Avro or Protobuf on Kafka) is how this stays manageable instead of breaking every consumer at once.
• Delete handling. Log-based CDC emits deletes as events, often as a record with the old value and a tombstone. Consumers that were built assuming inserts and updates only will silently keep deleted rows unless they are designed to process tombstones. Soft-delete versus hard-delete semantics have to be decided explicitly.
• Delivery semantics. CDC generally guarantees at-least-once delivery, which means duplicates are possible after a restart. Consumers must be idempotent, keyed on the primary key, so that reprocessing the same change is harmless. Ordering per key must be preserved, which is why partitioning by primary key matters.

Get these four right and CDC is dependable. Ignore them and the pipeline works in the demo and drifts in production, which is the worst failure mode because it is silent.

Failure patterns

From the field, the patterns that undermine CDC pipelines:

1. Polling in production. Query-based CDC chosen for its simplicity, then quietly missing deletes and double updates until the target diverges from the source.

2. No schema registry. Schema changes propagate as raw shape changes and break consumers with no warning or versioning.

3. Non-idempotent consumers. Duplicates from at-least-once delivery corrupt aggregates because a consumer applied the same change twice.

4. Snapshot that stalls. A naive full snapshot on a large table that locks or lags, blocking the switch to streaming.

5. Ignoring source log retention. Transaction logs recycled before the consumer reads them, causing gaps that force a full re-snapshot.

Talk through your CDC pipeline

DNA Solutions helps European enterprises replace batch synchronization with log-based change data capture: Debezium and Kafka or managed equivalents, with the snapshot, schema evolution, delete handling and idempotency designed in from the start. Whether you are feeding a data lake, keeping services in sync or retiring a fragile nightly job, we build the stream so it stays correct under real load. Talk to us.