Change data capture explained: methods, tools and pitfalls

Change data capture (CDC) is a way of recording every insert, update and delete in a database as it happens, and delivering those changes to other systems. Instead of copying whole tables on a schedule, CDC sends only what changed, usually within seconds. It is how data warehouses, lakehouses, search indexes, caches, microservices and AI systems stay current with the operational databases they depend on. This guide covers the four ways to capture changes, the main tools, a working setup, and the problems that show up in production. It is part of our Private AI & Data Architecture series.

The four CDC methods, from most to least common in new builds:

1. Log-based: read changes from the database's transaction log.
2. Trigger-based: database triggers write each change to a side table.
3. Query-based: poll tables for rows with a newer timestamp or version.
4. Snapshot comparison: copy whole tables and compare them with the last copy.

What change data capture is

Every operational database already knows what changed. It records each committed insert, update and delete in a transaction log so it can recover after a crash. Change data capture turns that record, or something like it, into a stream of change events that other systems can consume.

A change event usually carries the operation (create, update or delete), the row's key, the values before and after the change, the time it was committed, and its position in the log. Consumers apply those events in order to keep their own copy in step with the source.

The alternative is batch extraction: copying tables every night, or every hour. Batch is simpler, but it is slow, it loads the source heavily at extraction time, and it misses intermediate changes. A row updated three times between extracts appears once. A row inserted and deleted between extracts never appears at all.

Four ways to capture changes

Log-based CDC is the default for good reasons. It adds little load, because the database writes the log anyway. It captures deletes and every intermediate change. It preserves the order in which changes were committed. And it needs no changes to application tables.

The other methods still have a place. Trigger-based CDC works where you cannot get log access, such as some managed or vendor-hosted databases. Query-based CDC is fine for small tables where missing an intermediate change does not matter. Snapshot comparison is useful for reconciliation: checking that a CDC pipeline has not drifted from the source.

Change data capture tools compared

These are common choices, not a ranking.

On Cloudera, the Debezium source connectors run in Kafka Connect and write changes to Kafka topics, and SQL Stream Builder can read the same databases directly through Flink without Kafka in between. Both keep CDC inside the same governed platform as the data it feeds, alongside Kafka and Flink on Cloudera.

How to set up log-based CDC

The steps below use PostgreSQL and SQL Server as the sources and Debezium with Kafka Connect as the capture tool. The same sequence applies to other databases and tools.

Step 1: Choose the tables and confirm their keys

List the tables that consumers actually need. Every captured table should have a primary key, because the key decides how changes are ordered and how consumers apply them. Tables without a key produce changes that are hard to apply correctly downstream.

Step 2: Prepare the database

Each database exposes changes differently. PostgreSQL needs logical decoding turned on and a publication that names the tables. SQL Server needs CDC enabled on the database and on each table, with SQL Server Agent running.

-- PostgreSQL: set wal_level = logical in postgresql.conf, then restart
CREATE PUBLICATION orders_publication
  FOR TABLE public.orders, public.order_lines;

-- SQL Server: enable CDC for the database, then for each table
EXEC sys.sp_cdc_enable_db;
EXEC sys.sp_cdc_enable_table
  @source_schema = N'dbo',
  @source_name   = N'Orders',
  @role_name     = NULL;

Create a dedicated database user for capture with only the privileges it needs, and store its password in a secrets store, not in the connector configuration.

Step 3: Configure the connector

The connector reads the log and writes one Kafka topic per table. This is a minimal Debezium configuration for the PostgreSQL source, submitted to Kafka Connect:

{
  "name": "orders-postgres-cdc",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "database.hostname": "orders-db.internal",
    "database.port": "5432",
    "database.user": "cdc_reader",
    "database.password": "${file:/secrets/cdc.properties:password}",
    "database.dbname": "orders",
    "topic.prefix": "orders",
    "plugin.name": "pgoutput",
    "slot.name": "orders_cdc",
    "publication.name": "orders_publication",
    "table.include.list": "public.orders,public.order_lines",
    "snapshot.mode": "initial"
  }
}

Changes to public.orders arrive on the topic orders.public.orders. The password is read from a file at runtime, so it never sits in the configuration itself.

Step 4: Run the initial snapshot

A new connector first copies the current contents of each table, then switches to streaming changes from the log position it recorded at the start. For large tables, schedule the snapshot for a quiet period, or use an incremental snapshot so it runs in chunks alongside streaming.

Step 5: Plan for schema changes

Columns get added, renamed and dropped. Register change event schemas in a schema registry, set compatibility rules, and agree with application teams how schema changes are announced. A renamed column that reaches consumers unannounced is the most common cause of a broken pipeline.

Step 6: Monitor lag and log retention

Watch how far the connector is behind the source, and how much log the database is holding for it. Alert on both. A connector that stops is not just late: on some databases it causes the log to grow until the disk fills.

Common pitfalls in production

• Log growth from stopped connectors. In PostgreSQL, a replication slot keeps the write-ahead log it has not consumed, even with no connection. A stalled connector can fill the disk. Drop slots that are no longer used and alert on retained log size.
• Retention windows that are too short. SQL Server keeps CDC change table rows for three days by default. A connector down longer than the retention window loses changes and needs a new snapshot.
• Duplicates. Most CDC pipelines deliver at least once, so a consumer can see the same change twice after a restart. Make consumers idempotent. Our guide to event-driven architecture shows the pattern, and how the transactional outbox uses CDC to publish events reliably.
• Ordering across tables. Changes are ordered per key and per table, not across the whole database. Consumers that join tables need to tolerate one table running slightly ahead of another.
• Deletes downstream. A delete arrives as an event, often followed by a tombstone that tells compacted Kafka topics to drop the key. Make sure every consumer handles both.
• Sensitive data copied everywhere. CDC copies every column you capture to every consumer. Exclude or mask columns that consumers do not need before the data leaves the source.
• Large transactions. A bulk update of millions of rows becomes millions of change events. Plan for the spike, or run bulk jobs outside capture.

CDC for analytics and AI

CDC is how analytical and AI systems get current data without loading the systems of record. Changes stream into lakehouse tables, such as Apache Iceberg tables, so analytics runs on data minutes old instead of a day old. Feature stores stay current for machine learning models. Retrieval systems re-index documents and records when they change, so an AI assistant does not answer from a superseded version.

Every one of those uses raises the same question from reviewers: where did this data come from, and when? A CDC pipeline is a lineage source in its own right. Record which source tables feed which topics and tables downstream, as part of your data lineage.

If you work in a regulated industry

Banks and insurers often hold their most important data on mainframes: Db2 for z/OS, IMS and VSAM files behind core banking, policy and claims systems. These are the sources where CDC delivers the most value and needs the most care. Commercial tools such as IBM Data Replication read these sources and can deliver to Kafka, so modern platforms can use mainframe data without changing the core system.

Three controls matter more in regulated settings. Mask or exclude personal and account data at capture time, so it does not spread to every consumer. Treat the CDC pipeline as part of the system of record for lineage and reconciliation, with regular checks that the target matches the source. And put connector configuration, credentials and table lists under change control, with an audit trail of who changed what.

When to bring in a partner

Turning on CDC for one table takes an afternoon. Running it across core systems, with snapshots, schema changes, masking, monitoring and reconciliation, is a production system in its own right. Duczer East designs and runs Debezium CDC on Cloudera and on other platforms, from mainframe and relational sources into Kafka, lakehouse tables and AI systems.