Change Data Capture Explained: Methods, Tools and When Batch Still Wins

Change data capture (CDC) copies every insert, update and delete from a source database as it is committed, usually by reading the database's transaction log rather than querying its tables. Log-based CDC gives low latency and low source load; query-based and trigger-based methods are simpler but miss deletes or slow the source. Batch still wins when hourly freshness is enough.

CDC is now the default way to feed lakehouses, search indexes and event streams from operational systems. It is also one of the most commonly under-engineered parts of a data platform, because the first demo works in an afternoon and the production failure modes take months to appear.

#Why Change Data Capture Matters Now

What this means for you: if your nightly extracts are the reason a dashboard is always a day behind, CDC is the usual fix, but it changes what your team has to operate.

Three pressures have pushed CDC from a niche replication technique into a mainstream platform pattern. Operational analytics expects minutes rather than days. AI systems answering questions about orders, stock or claims need current data, not last night's copy. And full-table extracts grow with table size, so a job that ran in twenty minutes five years ago now competes with the business day.

The open-source tooling matured over the same period. PostgreSQL 9.4 introduced logical decoding in 2014, giving external tools a supported way to read row changes from the write-ahead log. Kafka 3.3, released in 2022, added exactly-once delivery for source connectors under KIP-618, and the Debezium project's 3.x line, starting with 3.0 Final in October 2024, built on it.

CDC is easy to demonstrate and hard to operate, and the gap between the two is where most programmes lose a quarter.

None of that makes CDC free. It moves work from a scheduled job that either succeeds or fails into a long-running process that can fall behind, lose its place or silently skip a schema change. The rest of this article is about choosing the method and designing for those failures.

#What Change Data Capture Actually Is

What this means for you: CDC is a specific technique for capturing row-level change, not a synonym for "real-time" or "streaming".

Change data capture is the identification and delivery of individual row changes (inserts, updates and deletes) from a source system to one or more targets, in commit order, with enough metadata to apply them correctly. That metadata typically includes the operation type, the before and after values, the transaction identifier and the log position.

It is distinct from streaming in general. A Kafka topic of clickstream events is streaming but not CDC. A nightly job that compares two snapshots and emits differences is CDC in the loose sense, but it carries none of the ordering or latency properties people usually mean. The architecture that sits downstream, where CDC events land on a backbone and fan out to hot, warm and cold paths, is covered in our guide to Kafka-to-lakehouse streaming patterns.

#The Three Capture Methods

#Log-Based, Query-Based or Trigger-Based: How to Choose

What this means for you: the method is usually decided by what access you can get to the source database, so ask that question first.

Log-based capture is the usual target. Each database exposes its log differently: PostgreSQL through logical replication slots, MySQL through the row-format binary log, SQL Server through its built-in CDC feature (available since SQL Server 2008), and Oracle through LogMiner or the redo log directly. Each needs database configuration and privileges that a DBA team must agree to, and that conversation is often the critical path.

Query-based capture is the fallback when log access is refused. It works acceptably for append-mostly tables with a trustworthy updated_at column. It fails on hard deletes, on rows updated twice between polls, and on clocks or application code that do not update the timestamp consistently.

Trigger-based capture has a specific place in SAP estates. SAP Landscape Transformation Replication Server (SLT) creates database triggers and logging tables on the source to capture changes, which is why its load on the source system has to be sized with the Basis team. For SAP sources the decision often sits inside a wider SAP data integration choice rather than a pure CDC one.

#The Tooling Landscape

What this means for you: choose tools on the sources you must support and the team that will run them, not on demo speed.

The open-source reference point is Debezium, a set of log-based connectors for PostgreSQL, MySQL, SQL Server, Oracle, MongoDB and others, most often run on Kafka Connect. It gives you control of every setting and no licence fee, in exchange for operating Apache Kafka, Kafka Connect, a schema registry and the connectors themselves.

Commercial CDC tools trade that control for a managed or packaged service. Oracle GoldenGate is long established for Oracle sources. Qlik Replicate and Fivetran offer log-based capture across many databases with less engineering. Cloud providers offer their own replication services, and lakehouse platforms increasingly bundle CDC ingestion. Capabilities and source coverage change often, so verify them against your exact source versions.

The second tooling decision is the transport. Some tools write directly to a warehouse or lakehouse; others publish to Kafka first. Publishing to a durable log adds an operating burden but gives you replay, multiple consumers and a buffer when a target is down. Writing directly is simpler when there is exactly one consumer and replay is not a requirement.

#What CDC Has to Get Right

What this means for you: these five properties separate a CDC pipeline you can trust from one that quietly drifts from the source.

#Initial Snapshots

A CDC connector starting against an existing table has to load the rows already there before it can stream changes. The snapshot must line up exactly with the log position where streaming begins, or rows are lost or duplicated at the seam. Large tables make this the slowest and riskiest step; incremental snapshot features that load in chunks while streaming continues reduce the blast radius of a restart.

#Ordering and Delivery Semantics

Changes to the same row must be applied in commit order, or an older value overwrites a newer one. Most pipelines achieve this by partitioning events by primary key, so all changes to a row share one ordered partition. Delivery is commonly at-least-once, so targets must apply changes idempotently, typically by merging on key and log position. Exactly-once delivery became possible for Kafka source connectors with KIP-618 in Kafka 3.3, but it still has to hold end to end, including the sink.

At-least-once delivery with idempotent targets is a design; exactly-once is a property you have to prove on every hop.

#Schema Evolution

Columns are added, renamed and retyped on operational databases without warning to the data team. A CDC pipeline needs a schema registry or equivalent, compatibility rules, and an agreed process for breaking changes. Treating the source schema as a data contract, checked in CI as described in our article on governance as code, turns a silent breakage into an early, visible failure.

#Deletes and Updates

Log-based CDC emits deletes as events, often followed by a tombstone record for compaction. Targets must decide whether to hard-delete, soft-delete or keep history. Updates may carry only changed columns unless the source is configured to log full row images, as with PostgreSQL's replica identity setting, so check this before designing merges.

#Monitoring and Reconciliation

The three numbers that matter are source log lag, pipeline lag and row-count or checksum reconciliation between source and target. Pipeline health dashboards that only show "connector running" miss the most expensive failures. The wider practice of data observability and quality monitoring applies directly here.

#What CDC Costs to Run

What this means for you: the licence or cloud bill is the visible cost; the operating effort is the larger one.

Open-source CDC moves cost from licences to people. Someone has to run Kafka and Kafka Connect, patch connectors, manage offsets during upgrades, re-snapshot tables after incidents and handle schema changes. Managed tools reduce that effort but price by volume, rows or connectors, which grows with every table added.

Both routes share hidden costs. Source databases need extra log retention and sometimes higher-spec storage. Targets that merge changes continuously pay more compute than targets that load once a night, because merges are expensive in most table formats. On a lakehouse, frequent small merges also create small files that need compaction, a problem covered in our notes on lakehouse performance tuning.

The realistic question is therefore not "how much does CDC cost?" but "which feeds are worth the operating cost of continuous capture?". For most estates, that is a minority of tables carrying most of the business value.

#When Batch Is Still the Right Answer

What this means for you: CDC is the wrong tool for a large share of feeds, and saying so early saves months.

Batch remains the better choice when the consumer only needs daily or hourly freshness, when the table is small enough to extract in full cheaply, or when no one can grant log access and the table lacks a reliable change column. It is also the better choice for teams that cannot yet staff on-call support for a long-running pipeline; a failed batch job waits politely for the morning, while a stalled CDC connector can affect the source database.

CDC is also a poor fit for one-off migrations where a single bulk load and a short cut-over window will do, although it is often used to keep source and target in step during a phased cut-over. For SAP specifically, the trade-offs in our guide to S/4HANA data migration with the Migration Cockpit apply before any CDC decision.

#Where to Start

What this means for you: a staged rollout on a few high-value tables proves the operating model before it is scaled.

Start with an inventory of candidate feeds: source, table, size, change rate, freshness needed by the most demanding consumer, and whether log access is possible. Rank them by business value and pick two or three tables that justify continuous capture.

Agree log access and configuration with the DBA team early, including retention, privileges and monitoring of slot or log lag on the source. Then build one pipeline end to end: snapshot, streaming, idempotent merge, schema-change handling and reconciliation. Run it through a planned failure, such as stopping the connector for an hour, before calling it production.

Only then widen the scope. A first production wave of a few high-value tables typically moves in weeks; extending CDC across an estate of hundreds of tables, with ownership and on-call, is multi-quarter work best run as part of a wider data engineering programme. The target architecture those feeds land in is an enterprise data architecture decision, and the streaming backbone itself is part of real-time streaming design.

#Frequently Asked Questions

#What is change data capture in simple terms?

Change data capture is a technique that copies each insert, update and delete from a source database to other systems as it happens. Most modern CDC reads the database's transaction log, which records every committed change in order, so the source tables are not repeatedly queried. The result is a near-real-time copy or event stream that downstream systems can use for analytics, search or integration.

#What is the difference between log-based and query-based CDC?

Log-based CDC reads committed changes from the database's transaction log, so it captures deletes, preserves order and puts little load on the source. Query-based CDC repeatedly queries tables for rows with a newer timestamp or version, which needs no log access but misses hard deletes and any intermediate updates between polls. Log-based is preferred wherever database administrators can grant log access.

#Is Debezium a good choice for enterprise CDC?

Debezium is a widely used open-source set of log-based CDC connectors for databases including PostgreSQL, MySQL, SQL Server, Oracle and MongoDB, usually run on Kafka Connect. It suits teams that can operate Kafka, Kafka Connect and a schema registry and want full control without licence fees. Teams without that operating capacity often prefer a managed or commercial CDC service.

#Does CDC guarantee exactly-once delivery?

Not by default. Most CDC pipelines deliver at least once, so targets must apply changes idempotently, typically by merging on primary key and log position. Kafka 3.3 added exactly-once support for source connectors under KIP-618, which tools such as Debezium build on, but exactly-once only holds if every hop, including the sink writing to the target, also supports it.

#When should you use batch instead of change data capture?

Use batch when consumers only need daily or hourly freshness, when tables are small enough to extract in full cheaply, or when log access cannot be granted and no reliable change column exists. Batch is also safer for teams that cannot yet support a continuously running pipeline. Reserve CDC for the minority of feeds where fresher data carries clear business value.

Unolabs is a Data and AI first engineering consultancy, headquartered in the United Kingdom with engineering operations in Pune and active engagements across the UK, Australia, and Hong Kong. We help enterprises build the architectural foundation for autonomous AI execution — governed data platforms, semantic intelligence, and agentic systems that enterprises can stand behind.

If you are weighing which feeds justify change data capture and which should stay batch, book a discovery call and we will work through it with you.