Change Data Capture: How It Works and When to Use It

Decorative database change event title card

Change data capture (CDC) is a way of moving only what changed in a database, the inserts, updates and deletes, to other systems as it happens, instead of copying whole tables on a schedule. It tracks record-level modifications in a source system and propagates them downstream in near real time. This guide covers the methods, how CDC differs from ETL, what setup looks like on MySQL and SQL Server, and the costs teams tend to discover too late.

Nullbit

Build Data Systems That Scale

Nullbit develops custom software and AI solutions to help businesses optimize processes and support growth through digitalization.

Explore Nullbit

TL;DR

CDC captures row-level inserts, updates and deletes from a source database and delivers them to downstream systems in near real time.
Log-based CDC reads the database’s transaction log, so it adds little query load and catches deletes that batch extracts miss.
CDC and ETL are not rivals. A common pattern is CDC to land raw changes, then ELT to transform them in the warehouse.
CDC is not free: it raises log and storage usage, and schema changes and delivery guarantees need design work.
Start with one high-value table and a proof of concept before you commit to a platform-wide rollout.

What change data capture actually does
The main CDC methods compared
CDC patterns you will meet in real systems
CDC vs ETL: complements, not competitors
Change data capture in MySQL
Change data capture in SQL Server
What can go wrong
Where CDC pays off in business terms
Our perspective
A practical way to start
How we can help
FAQ
Sources

What change data capture actually does

A transactional database holds the current state of your business: orders, accounts, inventory. Other systems need that data too: a warehouse for reporting, a search index, a cache, a fraud model, another service. The old way to feed them is to run a query every hour or night and copy what you find.

CDC flips that. It records each change as an event, typically with the operation type (insert, update or delete) and the affected row, and sends it on. The Wikipedia entry describes it as a set of software design patterns for tracking the “deltas” so action can be taken on the changed data. That wording matters: CDC is a family of patterns, not one product.

Database changes recorded and sent as events

The practical result is a delta-driven dataset. Downstream systems stay current with a fraction of the data volume a full reload would move, and the source database is not hammered by large scans.

The main CDC methods compared

There are three common ways to detect changes. They trade off accuracy, load and effort.

Method	How it detects changes	Captures deletes	Load on source	Typical drawback
Log-based	Reads the transaction log (binlog, WAL, redo log)	Yes	Low	Needs log access and careful configuration
Trigger-based	Database triggers write changes to a shadow table	Yes	Medium to high, inside every transaction	Slows writes; triggers are hard to maintain
Query-based	Polls for rows with a newer timestamp or version column	Usually no	Grows with table size	Misses hard deletes and intermediate updates

Log-based CDC

Every serious database already writes changes to a log for replication and crash recovery. Log-based CDC reads that log instead of querying your tables. This is why it can deliver fresh data with near-zero impact on source performance, and why it sees deletes that a polling query never will. For most production workloads this is the method to start with.

Trigger-based CDC

Triggers fire inside the same transaction as the change and write a record to an audit or shadow table. It works on databases with no usable log access, but every write pays the price, and the trigger code becomes something your team must maintain through every schema change.

Query-based CDC

This is the simplest approach: select rows where an updated_at column is newer than the last run. It is fine for small, append-mostly tables. It breaks on hard deletes, and if a row changes twice between polls you only see the last state.

CDC patterns you will meet in real systems

The method is only half the design. The patterns around it decide whether the pipeline is trustworthy.

Log to event stream. Changes are published to a message broker, one topic per table, and many consumers read them independently. This is the most flexible shape.
Log to warehouse. Changes land in analytics storage and are merged into current-state tables. Platforms such as Databricks document this replication from Oracle, Postgres and SQL Server for analytics, reporting and machine learning.
Outbox pattern. The application writes a business event to an outbox table in the same transaction as its data change, and CDC ships the outbox. You get clean, intentional events instead of raw row changes.
Initial snapshot, then stream. A consistent snapshot loads existing data, then the log takes over from the exact position where the snapshot ended. Getting this handoff right is where many homegrown pipelines lose or duplicate rows.

If you have ever wired up blockchain event notifications via webhooks, the idea will feel familiar: a system emits a signed event when something changes, and consumers react without polling. CDC applies the same push model to your own databases.

CDC vs ETL: complements, not competitors

Traditional ETL relies on periodic batch intervals, hourly or nightly, and the heavy scans can degrade source performance. CDC streams changes as they commit. That does not make ETL obsolete. The transformation work, joining, cleaning and modeling, still has to happen somewhere.

The pattern that works for most teams is described in dbt Labs’ overview of data movement patterns: use CDC where data freshness is essential, such as personalization, then transform change events into incremental models inside the warehouse. To make those pipelines reliable, teams must design for exactly-once delivery and handle schema changes proactively.

Question	Batch ETL	CDC
Data freshness	Minutes to hours or a day	Seconds to near real time
Source load	Spikes during extraction	Steady and low (log-based)
Deletes	Often missed without full reload	Captured as events
Complexity	Lower to start	Higher: ordering, schema drift, replays
Best for	Nightly reporting, small tables	Live dashboards, sync between systems, migrations

A simple rule: if nobody would notice data being a day old, batch is cheaper and simpler. If a stale record causes a wrong price, a missed alert or a bad customer experience, CDC earns its complexity.

Change data capture in MySQL

MySQL exposes changes through its binary log. A log-based connector reads the binlog, parses the DDL statements to learn each table’s structure, and maps each insert, update and delete event to a message. The open source Debezium project explains this design in its write-up on capturing changes from MySQL, including how one team wrote events to a separate Kafka topic per table.

A typical setup checklist:

Enable the binary log in row format, so events carry the full row data rather than the SQL statement.
Create a dedicated database user with only the replication privileges the connector needs.
Set binlog retention long enough to survive a connector outage and a replay.
Run an initial snapshot, then switch to streaming from the recorded binlog position.
Monitor connector lag and disk usage on the source server.

Check the exact variable names and permissions against the documentation for your MySQL version and your managed provider, since hosted services often restrict binlog settings.

Change data capture in SQL Server

SQL Server has CDC built in. Once it is enabled on a database, you enable it per table with the sys.sp_cdc_enable_table stored procedure, after which a record of each data manipulation operation on that table is written to a change table. Capture runs through SQL Server Agent jobs, so the Agent must be running.

Two cautions from Microsoft’s documentation apply in production:

Do not hand-edit CDC metadata. Microsoft’s known issues page says not to modify the CDC schema, change tables, system stored procedures or default permissions, and not to rename the cdc user.
Plan for log growth. Enabling CDC on high-transaction databases, especially alongside Accelerated Database Recovery, can significantly raise transaction log utilization and storage overhead, so test the I/O impact before switching it on in production.

What can go wrong

Most CDC failures are design problems, not tool problems.

Schema changes. Adding a column upstream can break a consumer that expects the old shape. Decide up front how changes propagate: versioned schemas, a registry, or a contract with the owning team.

Ordering and duplicates. Streams can redeliver after a failure. Consumers must be idempotent, or you need delivery guarantees that prevent double application.

Log retention. If the connector is down longer than the log is kept, you lose your place and must re-snapshot.

Sensitive data. CDC copies everything in the row, including personal data. Decide which columns are masked or excluded, and where the copies may legally live. If your data crosses borders, our guide to data residency requirements walks from the legal text to technical proof.

Silent drift. A pipeline can run green while quietly dropping events. Reconcile row counts or checksums between source and target on a schedule.

What can go wrong — overview diagram

Where CDC pays off in business terms

The cases we see worth the effort are the ones where a delay costs money or trust:

Live operational reporting. Finance and operations dashboards that show current numbers, not yesterday’s.
System synchronization. Keeping a CRM, billing system and customer portal consistent without nightly jobs. Our write-up on quote-to-cash automation shows how connected finance data shortens collection time.
Zero-downtime migrations. Stream changes into the new platform until cutover.
Monitoring and anomaly detection. Feeding fresh data into models that must react quickly, as in our infrastructure anomaly detection case study.
Healthcare and regulated data. Consolidating records from several sources into one trustworthy view, as in our diabetes data platform case study.

Our perspective

We build custom software and AI systems, and CDC comes up whenever a client says their reports are always behind or their systems disagree. Our experience is that the tooling is rarely the hard part. The hard part is deciding which data really needs to be fresh, who owns the schema, and what happens when a consumer fails at 3 a.m.

We lean toward starting small. Pick one table that drives a visible business decision, stream it, measure the lag and the cost, and only then widen the scope. We stay technology-agnostic, so the right answer might be a managed database feature, an open source connector or a lighter webhook, depending on what you already run.

We also treat AI as part of the core design rather than an add-on. Fresh change events are what let decision-support and automation systems act on what is true now, not on last night’s extract. That is usually the real reason to invest in CDC.

— Matija

A practical way to start

Plan a first CDC project in this order:

Name the decision. Write down which report, alert or sync is hurting because of stale data, and what freshness it needs.
Pick the source and method. Log-based for production OLTP databases in most cases; query-based only for small, append-only tables.
Size the overhead. Estimate log volume, retention and storage before enabling anything on the primary.
Design for failure. Define the snapshot-to-stream handoff, idempotent consumers, schema change rules and reconciliation checks.
Prove it on one table. Run it for a few weeks, compare against the batch output, and track lag, cost and incidents.
Expand deliberately. Add tables and consumers in order of business value.

How we can help

If you want to test CDC without committing to a large build, a proof of concept is the right first step. Our proof-of-concept development starts from €5,000 one-off. For narrower pipeline work, such as syncing one system to another or automating what happens when a record changes, smaller, targeted automations start from €3,000 one-off. Larger data platform work can run as a fixed-price project or an agile engagement, published at €5,000 to €60,000 one-off and €5,000 to €40,000 per month respectively.

FAQ

What does change data capture mean?

It means detecting and recording the inserts, updates and deletes made to a data source, then delivering them to other systems. Downstream systems receive only the changes rather than a full copy of the data. The goal is to keep systems synchronized with low delay and low load on the source.

What are the disadvantages of change data capture?

CDC adds operational complexity: you must handle schema changes, ordering, duplicates and log retention. It also consumes resources on the source, since enabling it on busy databases can raise transaction log use and storage. For data that can be a day old, batch loading is usually simpler and cheaper.

How do I enable change data capture?

The steps depend on the database. In SQL Server you enable CDC on the database, then run the sp_cdc_enable_table procedure for each table, with SQL Server Agent running. In MySQL you enable row-format binary logging and connect a log reader with replication privileges. Always test log and storage impact first.

Is CDC a replacement for ETL?

No. CDC moves changes quickly and cheaply, while ETL or ELT handles transformation and modeling. Many teams land raw changes with CDC and transform them in the warehouse with incremental models.

Change Data Capture: How It Works and When to Use It