Change Data Capture (CDC): How It Works, Types & Tools

Keeping analytical data current requires more than moving new rows. Orders change status, customers update their details, and records disappear. A pipeline that copies only inserts gradually diverges from its source. Change data capture addresses that problem by tracking mutations and giving downstream systems the information needed to apply them.

This article explains how CDC works, what different capture methods preserve, and how to build a pipeline that handles updates, deletes, and recovery. It also covers warehouses, data lakes, Apache Iceberg, and the tools used to connect them.

What is change data capture (CDC)?

Change data capture (CDC) is the process of identifying changes in a source dataset and making those changes available to downstream consumers. For databases, the central operations are inserts, updates, and deletes. The output might be a stream of change events or a change table that another process reads periodically.

CDC supplies the extraction mechanism within a larger integration pipeline. Transformation still determines how source records map to the destination, and loading still determines how changes become visible there. CDC can therefore support either ETL or ELT, depending on where transformation happens.

A full refresh replaces or rebuilds a dataset from a new extract. CDC instead lets a consumer apply changes incrementally after establishing its starting state. That distinction matters when a large table changes only slightly between refreshes, or when consumers need to observe intermediate changes rather than just the latest snapshot.

How does change data capture work?

A typical replication pipeline establishes a baseline, captures subsequent changes, and applies them to a destination. The difficult part is coordinating those phases while the source continues accepting writes.

Establish a baseline. An initial snapshot supplies existing rows. The pipeline coordinates that snapshot with a position in the change history so writes during the scan are accounted for. The Debezium PostgreSQL connector, for example, supports a consistent initial snapshot followed by streaming from the associated write-ahead log position.

Represent changes. A useful event identifies the source table, row key, operation, and source position. It may include previous and new column values. The available row images depend on the database and capture configuration; consumers should not assume every update contains a complete previous row.

Apply and checkpoint. A destination writer inserts new records, updates existing ones, and processes deletions. It also records progress so processing can resume after failure. Capturing an event and making its effect durable at the destination are separate milestones.

Consider an order changing from pending to paid. A current-state destination should replace the status for that order's key. A history destination should retain the transition. If the same event arrives again after a restart, neither destination should create an unintended second effect.

Types of change data capture

CDC terminology varies across products. A useful distinction is what the consumer receives: individual changes or the net result of changes over an interval. Delivery frequency is a separate choice.

All-changes capture. The feed exposes individual recorded changes in the captured scope. An order that moves from pending to paid to refunded can produce distinct transitions. This supports consumers that need intermediate states, provided the capture and retention configuration preserves them.

Net-changes capture. The consumer receives the net effect of recorded changes over a selected interval. This can reduce destination work when only current state matters, but it loses intermediate transitions. SQL Server CDC provides query functions for all changes and, for appropriately configured capture instances, net changes.

Continuous and scheduled delivery. Either representation may be consumed continuously or in batches. A log reader can run continuously while the destination applies changes periodically. Calling a pipeline streaming says little about which transitions survive or when they become queryable.

Choose the representation from the consumer's question: “What is true now?” and “How did this record change?” require different retained information.

Change data capture methods

Capture methods differ in how they observe mutations and what they require from the source.

‍Log-based capture reuses records the database maintains for recovery or replication. PostgreSQL logical decoding converts changes from its write-ahead log into a consumable representation. This avoids repeated whole-table polling for ongoing changes, but decoding, retention, and initial snapshots still consume resources.

‍Trigger-based capture can record selected values in a change table as part of the transaction that changes the source row. It offers control over the captured fields, at the cost of maintaining database objects and adding work to writes. Test bulk operations and schema changes against the actual trigger design.

‍Polling is useful when log access is unavailable. Confluent's JDBC source connector supports timestamp and incrementing-column modes. A monotonically increasing ID alone finds new rows, not updates to existing IDs. Timestamp polling needs reliable update timestamps and a policy for transaction timing; a hard-deleted row leaves nothing for that query to return.

‍Snapshot comparison can detect that a key disappeared between consistent extracts. It cannot reconstruct a row inserted and deleted entirely between them. Its cost also follows the data compared, even when few rows changed.

The choice depends on the mutations consumers must observe. A simple poller may suit a periodically refreshed directory; a financial status history needs stronger coverage of intermediate changes.

Benefits of change data capture

Less repeated extraction. After initialization, incremental capture can avoid repeatedly transferring unchanged rows. The benefit depends on the change rate and method: a frequently rewritten table or snapshot-comparison pipeline may still generate substantial work.

‍Fresher downstream state. Consumers can process changes throughout the day instead of waiting for a full refresh. The useful outcome is shorter time from a source commit to a queryable result, which also depends on destination processing.

‍Independent analytical workloads. Replicating changes into an analytical store lets dashboards and transformations run against that destination. Source-side capture still needs capacity planning, but large analytical scans can move off the transactional system.

‍Reusable change history. A retained event stream can feed several consumers and support reprocessing after a transformation error. Replay is available only for the retained interval, with enough schema context to interpret old records.

These benefits come with ongoing responsibilities: monitoring lag, retaining recoverable history, and proving that the destination remains correct after failures.

Common change data capture use cases

Operational reporting. Capture changes to orders, payments, and fulfillment records so reports reflect corrections and cancellations as well as new sales. Report freshness should include transformation and dashboard refresh time.

‍Search and cache synchronization. Propagate product edits, account changes, and deletions into derived serving systems. Consumers need stable keys and retry-safe writes so replay does not duplicate documents or restore stale values.

‍Database migration. Load the existing dataset, then capture ongoing changes while preparing the new system. Before switching application traffic, reconcile data and define how writes are paused or coordinated during the final handoff. CDC reduces the amount left to transfer at cutover; it does not eliminate cutover planning.

‍Historical analysis. Preserve changes to investigate when an account's status or an order's value changed. An ordinary CDC feed is not automatically a complete audit trail: business reasons, actor identity, retention controls, and tamper protection require separate design.

‍Application integration. Row changes can initiate downstream processing, but database mutations do not always express business intent. With a transactional outbox, the application writes a business event alongside its state change in the same database transaction. CDC then transports the outbox record; Debezium's outbox event router supports routing those records to consumers.

Change data capture for data warehouses and data lakes

A warehouse pipeline commonly lands events in staging tables before applying them to modeled tables. The destination needs explicit rules for inserts, updates, and deletes. If the desired output is current state, several updates to one key may be reduced to the latest applicable version before a merge. Historical models need to preserve the transitions their analysis depends on.

A data lake can retain the original change records in object storage for replay and investigation. Those files are an event history, not necessarily a current-state table. An analytical table requires a writer or transformation that interprets operations and resolves record versions.

Keep ingestion history and query-ready state conceptually separate. Appending a deletion event to a file does not by itself remove the corresponding row from a downstream table. Similarly, removing a row from the current-state table does not erase its earlier values from retained events or snapshots.

Once records are queryable, some questions depend on relationships across tables: which accounts share devices, or which suppliers connect to delayed orders? PuppyGraph defines a graph schema over existing tables and queries that model with openCypher and Gremlin. Its source connections cover SQL databases, warehouses, and lakes or lakehouses, including direct reads of Iceberg and Delta Lake. The default direct-query path requires no graph-specific ETL or persistent duplicate dataset. The CDC pipeline remains responsible for updating the underlying tables; graph queries inherit the freshness of those inputs.

Change data capture for Apache Iceberg

Apache Iceberg supplies a table format over data files, with metadata and snapshots that describe table state. An upstream capture system detects source database changes, while an Iceberg-capable writer applies them. Iceberg's reliability model uses atomic metadata changes to publish table updates, so readers can work against a consistent table snapshot.

Two common application paths are streaming upserts and scheduled merges. An upsert inserts a missing key or updates an existing one. Iceberg's Flink writer documentation describes primary-key-based upserts with requirements for table format and equality fields. Confirm those requirements, including their interaction with partitioning, for the writer you deploy.

For batches, Spark with Iceberg supports MERGE INTO to apply matched updates and deletes and insert unmatched rows. Prepare the batch so multiple source records do not attempt to update the same target row. Reducing to one change per key is appropriate for a current-state projection, but would discard history if used indiscriminately.

Writer semantics matter as much as capture. Preserve keys, interpret deletions explicitly, and prevent an older replayed update from replacing newer state. A table commit also does not by itself reproduce a source transaction that spans several destination tables.

Frequent small commits can accumulate small files and metadata. Plan file compaction and snapshot expiration alongside the ingestion cadence. Snapshot retention should support the intended recovery window without being mistaken for a permanent business change log.

Real-time change data capture

Real-time CDC generally means continuous or near-real-time propagation, not a universal latency guarantee. Define freshness as the elapsed time between a source transaction committing and its effects becoming visible to the intended consumer.

That interval includes capture delay, transport backlog, processing, destination commit, and any query or dashboard refresh. A connector can be caught up while a destination writer is still behind. Track source-to-destination lag as well as each stage's progress.

Set a measurable target for the workload, then test it during bursts, large transactions, restarts, and backfills. For example, an inventory dashboard may tolerate a delay that an operational reservation workflow cannot. Use the transactional source for decisions that require its consistency guarantees.

Low latency also does not imply global ordering. Parallel consumers can advance at different rates. Specify whether correctness requires order per row key, per source transaction, or across several tables, and verify that the whole path preserves that scope.

CDC architecture

A CDC architecture typically contains a capture connector, transport or buffer, optional processing, and a destination writer. Debezium's architecture documentation illustrates deployments using Kafka Connect and Kafka, as well as alternatives using Debezium Server. A broker is an architectural choice, not a requirement of CDC itself.

A durable buffer lets consumers progress independently and provides retained history for replay. Direct replication can reduce component count when only one destination is needed. In both designs, record where progress becomes durable and how a consumer resumes after failure.

‍Recovery and idempotency. Assume replay is possible unless the complete system proves otherwise. PostgreSQL documents that logical decoding can resend recent changes after a crash. An idempotent destination operation produces the same result when repeated; version checks also stop older events from overwriting newer state. Deletions need an equivalent policy so replay cannot resurrect removed records.

‍Schema and transaction boundaries. Define how added columns, changed types, renamed fields, and multi-table transactions reach consumers. A row-level feed does not automatically provide compatible schema evolution or atomic visibility across destination tables.

‍Retention and access. Monitor retained logs and consumer checkpoints. PostgreSQL replication slots can retain WAL needed by a stalled consumer, increasing storage pressure. Limit captured tables and columns to the required scope, and protect before-images as carefully as current values because they can contain data since removed from the source.

The architecture should explain both normal flow and recovery: what survives a crash, what gets replayed, and how correct state is reconstructed.

How to implement change data capture

1. Define the destination contract. Decide whether consumers need current state, complete captured history, or business events. Record row keys, delete behavior, ordering scope, acceptable lag, and recovery expectations before selecting a connector.

‍2. Check source prerequisites. Verify supported database interfaces, capture permissions, logging settings, and retention. Inspect tables without stable keys and data types that need conversion. Estimate initialization load as well as steady-state change volume.

‍3. Select tools for the complete path. Common options occupy different operational roles:

Evaluate a representative workload through the destination. Source connectivity alone does not establish correct delete handling, ordering, or restart behavior.

4. Coordinate initialization. Use the tool's documented snapshot or full-load procedure. Identify the change position associated with that baseline and verify coverage of writes made during initialization. Add tables through a similarly controlled backfill process.

5. Test failure and recovery. Exercise updates, deletes, repeated events, key changes, schema changes, and connector restarts. Stop the destination long enough to create backlog, then confirm it catches up correctly. Test recovery when retained source history is no longer available.

6. Reconcile and operate. Compare source and destination at a coordinated point or after controlled catch-up. Counts alone cannot detect incorrect values, so include key-level or content comparisons. Assign ownership for lag alerts, retention capacity, failed records, and periodic reconciliation before broadening coverage.

Conclusion

Change data capture makes database mutations available for incremental processing. Its reliability depends on a coordinated baseline, sufficient change history, stable keys, and destination rules that survive replay. Choose capture methods and tools around those requirements, then measure freshness where users actually query the data.

Try the forever-free PuppyGraph Developer Edition and book a demo with the team to see how openCypher and Gremlin queries connect entities across warehouse and lakehouse tables, with no graph-specific ETL, after your CDC pipeline applies source changes.