Change Data Capture (CDC) and Why It's Important

Key Takeaways

• Change Data Capture (CDC) propagates database changes so downstream systems can refresh incrementally instead of repeatedly comparing whole tables.
• Trigger-based capture adds work to database transactions; log-based capture reads transaction logs but still requires source resources and operational care.
• A coordinated initial snapshot establishes the baseline. Checkpoints and retained changes make recovery possible.
• CDC doesn't independently guarantee a complete history or a correct warehouse: consumers must handle duplicates, deletes, ordering, and schema changes.

Change Data Capture (CDC) identifies database inserts, updates, and deletes and makes those changes available to downstream consumers. Its central advantage is incremental processing: a data warehouse can apply changes rather than repeatedly reload or compare the entire source table.

Think of CDC as change propagation, not a consistency guarantee. The capture mechanism, initial load, transport, and destination application strategy together determine whether your downstream data is current and correct.

Why Change Data Capture Matters for Data Warehouses

A transactional database serves application reads and writes; a warehouse supports analytical queries. CDC connects those workloads by supplying changes that an extraction, transformation, and loading pipeline can apply to an analytical representation. For background on the workload distinction, see OLTP vs OLAP.

For example, an orders pipeline could update warehouse records as orders are created, paid, or removed. You can maintain current-state tables, or deliberately preserve successive versions for historical comparisons. Those are different storage designs: overwriting a row doesn't preserve its history, and a compacted event topic isn't a permanent archive of every mutation.

The benefit is avoiding repeated work on unchanged data—not eliminating all source work. Initial loads, log generation, decoding, network transfer, and destination writes still consume resources. Measure freshness and source impact rather than assuming CDC means “real-time with no overhead.”

CDC vs Polling and Full-Table Comparisons

“Traditional methods” covers several different approaches. Full-table differencing compares complete reads; timestamp polling selects rows newer than a saved watermark, meaning the last processed timestamp or other progress value. These have different costs and failure modes.

These are mechanism comparisons, not performance rankings. Trigger behavior, incremental-query limitations, and log access requirements depend on the database and connector.

If a row changes from A to B to C between polls, a query might retrieve C successfully while never observing B. That's acceptable for some current-state copies, but not for a requirement to preserve every mutation. Hard-deleted rows no longer exist for an ordinary timestamp query to return; you need soft deletes, a deletion ledger, or another capture mechanism.

Polling also needs careful handling of equal timestamps, concurrent transactions, retries, and indexes. Adding a timestamp column alone isn't a complete synchronization protocol.

How Change Data Capture Works

Trigger-Based Capture

A trigger-based design typically writes a change record into a capture table when an application modifies a source row. A separate process then delivers those records downstream. In PostgreSQL, a trigger executes in the same transaction as the triggering statement, so an error can roll back both the application change and the trigger's work.

This places capture work on the write path: extra writes and contention can increase transaction cost. It doesn't mean the warehouse receives the event immediately. Keep external network calls out of the trigger; record changes transactionally and deliver them separately.

Transaction-Log-Based Capture

Log-based CDC reads the database's transaction log and converts relevant changes into consumable records. PostgreSQL calls its log the write-ahead log (WAL), and logical decoding translates its storage-level records into application-readable changes.

This avoids installing a capture trigger on every tracked table, but it isn't free. You still need suitable database configuration, permissions, retained logs, and enough connector and destination capacity. Latency depends on the entire pipeline, including workload, network conditions, and target ingestion speed.

Start With a Coordinated Initial Snapshot

A change stream doesn't automatically supply rows that existed before capture began. An initial snapshot reads existing data to establish a baseline; the handoff to streaming must account for writes made while that read is running. PostgreSQL's native logical replication, for example, coordinates its initial copy with subsequent change application.

A sound bootstrap strategy must:

1. Establish a snapshot and a corresponding change-stream boundary.
2. Preserve changes that occur while the baseline is copied.
3. Apply the baseline and concurrent changes in a coordinated order, resolving overlap.
4. Record durable progress so an interruption has a defined recovery path.

Use your connector's documented snapshot procedure rather than independently dumping a table and starting capture afterward. That gap can miss writes; unmanaged overlap can also let an older snapshot row overwrite a newer streamed value. Snapshotting itself can create substantial source load, so plan its timing and concurrency.

A Database Change, End to End

One implementation uses PostgreSQL as the source, Debezium running in Kafka Connect for capture, Kafka for transport, and a consumer that writes to a warehouse. These products illustrate the pattern; they aren't required components of CDC. See the Debezium architecture documentation for the product-specific arrangement.

PostgreSQL
  |-- coordinated snapshot --+
  |-- WAL changes -----------+--> Debezium --> Kafka --> Consumer --> Warehouse
                                   |                      |
                             source checkpoint      consumer checkpoint
                                   ^                      ^
                           replay retained WAL    replay retained events

The source checkpoint records capture progress; the consumer checkpoint records downstream processing progress. They're different recovery boundaries. A connector can be caught up while the warehouse consumer is still behind. For the transport layer, see Kafka architecture explained.

What Change Events Contain

The following is an invented, conceptual JSON format—not a Debezium payload or executable connector configuration. It shows one order being inserted, updated, and deleted. Positions are illustrative sequence values within the named stream.

[
  {
    "key": {"order_id": 42},
    "operation": "insert",
    "before": null,
    "after": {"status": "placed"},
    "source": {"stream": "orders", "position": 101},
    "metadata": {"transaction_id": "tx-a", "schema_version": 1}
  },
  {
    "key": {"order_id": 42},
    "operation": "update",
    "before": {"status": "placed"},
    "after": {"status": "paid"},
    "source": {"stream": "orders", "position": 102},
    "metadata": {"transaction_id": "tx-b", "schema_version": 1}
  },
  {
    "key": {"order_id": 42},
    "operation": "delete",
    "before": {"status": "paid"},
    "after": null,
    "source": {"stream": "orders", "position": 103},
    "metadata": {"transaction_id": "tx-c", "schema_version": 1}
  }
]

The key identifies the affected row. A before image describes its previous values; an after image describes its new values. Don't assume either image contains every column: availability depends on source logging and connector configuration. PostgreSQL's replica identity settings, for example, affect the old-row information available for updates and deletes.

Also distinguish individual mutations from net changes over an interval. SQL Server CDC exposes all-changes and net-changes query functions; choosing the latter isn't equivalent to retaining every intermediate edit.

A delete event is also different from a Kafka tombstone. In a compacted Kafka topic, a tombstone has a key and a null value and marks earlier records for that key for removal. A connector may emit both a descriptive delete event and a tombstone; verify how your sink handles each.

Delivery, Ordering, and Recovery

Make Retries Safe

At-least-once delivery means events can be delivered again. For example, a connector or consumer can crash after completing work but before saving its progress, then repeat that work after restarting. Design for duplicates even when normal operation appears duplicate-free.

For a current-state warehouse table, use this application pattern:

1. Identify the source row and the event's stable identity or ordering metadata.
2. Insert/update the row or apply its deletion, while recording deduplication or version information atomically where supported.
3. Advance the consumer checkpoint only after the destination write is durable.

An idempotent write has the same effect when repeated. Replacing a row with the same after image can be idempotent; incrementing a revenue counter again isn't. An upsert alone also doesn't prevent an older event from overwriting newer state—preserve ordering or reject stale versions. “Exactly once” within a broker doesn't automatically extend to an external warehouse.

Preserve the Ordering You Actually Need

Kafka preserves record order within a partition, not globally across all partitions. Route related row changes consistently and preserve their application order; parallel processing can otherwise change the order in which writes finish.

A source transaction can also change several rows or tables. If downstream readers must never observe half of that transaction, you need transaction metadata and a destination application strategy that preserves that boundary. Debezium can expose transaction-boundary metadata, but consumers must use it deliberately.

Plan for Missing Logs, Not Just Restarts

Checkpoints enable replay only while the required changes remain available. PostgreSQL replication slots can retain WAL while a consumer is behind, consuming source storage; configured retention limits can also leave a lagging reader without the WAL it needs. Monitor both lag and retained bytes.

Transport retention matters too. Kafka compaction can remove older versions, and tombstones eventually expire. A consumer that falls too far behind can miss deletion markers, so retention must match your recovery requirements.

Schema Changes Need Their Own Plan

Data definition language (DDL) changes—adding, renaming, or removing columns and changing types—need coordination beyond row replication. PostgreSQL's native logical replication doesn't replicate DDL commands; an incompatible destination schema can stop replication.

Test migrations through the whole pipeline. Prefer compatible additions first, update consumers and destination schemas, and retire old fields afterward. Verify connector behavior during snapshots as well as streaming; don't equate “the connector detected a schema change” with “every downstream consumer can process it.”

Production Checklist

• Define freshness: measure source commit to queryable destination data, not just connector capture time.
• Monitor each stage: track capture lag, consumer backlog, throughput, snapshot progress, failures, and source storage pressure.
• Budget recovery: retain source and transport changes long enough for outages plus catch-up time.
• Handle backpressure: if the sink is persistently slower than the source, fix its capacity or processing design rather than merely increasing buffers.
• Test correctness: exercise inserts, repeated updates, deletes, crash recovery, stale-event replay, and schema migrations.
• Protect replicated data: grant only required permissions, encrypt transport, and restrict sensitive columns and before images.
• Reconcile: periodically compare destination results with a source-consistent reference; a running connector isn't proof of correct data.

Separate source-side and destination-side bottlenecks when investigating lag. AWS DMS documents how changes can accumulate when capture outpaces target application; PostgreSQL documents the storage consequences of retained WAL. The same distinction is useful when designing your own streaming data architecture.

Beyond Warehouses: Search, Caches, and RAG

You can apply the same change-propagation pattern to derived stores such as search indexes, caches, and vector indexes used for retrieval-augmented generation (RAG). Treat this as an architectural application of CDC: the change event starts downstream work; it doesn't automatically regenerate embeddings or update an index.

For a document-based RAG pipeline, use source IDs and versions to replace outdated chunks, regenerate embeddings when relevant content changes, and remove every derived chunk when its source is deleted. Guard asynchronous indexing results against stale versions so a delayed job can't resurrect deleted content. Microsoft's index projection documentation illustrates why parent documents, derived chunks, change tracking, and deletion detection must be coordinated.

FAQ

What is change data capture (CDC)?

Change Data Capture identifies database inserts, updates, and deletes and makes them available to downstream systems. It supports incremental synchronization instead of repeatedly comparing or copying whole tables.

What is the difference between log-based and trigger-based CDC?

Trigger-based CDC typically records changes inside the transaction that modifies a row. Log-based CDC reads transaction logs, avoiding per-table capture triggers but requiring appropriate database configuration, permissions, and retention.

How is CDC different from polling a database with timestamps?

Timestamp polling queries rows newer than a saved watermark and can retrieve the latest row state without observing intermediate edits. Ordinary timestamp queries don't discover hard-deleted rows, while a suitably configured change-capture mechanism can report deletions.

Does CDC capture deletes and every intermediate update?

It can, but you must verify the source, connector, selected operations, and output mode. Net-change interfaces, filtering, retention, and compaction can prevent a downstream consumer from receiving every intermediate mutation.

How do CDC systems handle snapshots, duplicates, and recovery?

A coordinated snapshot establishes existing state, and saved checkpoints support replay after interruption. Consumers need duplicate-safe writes, and recovery requires retained changes; if those changes are unavailable, a re-snapshot or rebuild may be necessary.

Further Reading

• Microsoft: Change Data Capture in SQL Server — warehouse use cases, change tables, and all versus net changes.
• PostgreSQL: Trigger behavior — trigger execution and transaction semantics.
• PostgreSQL: Logical decoding concepts — WAL decoding, replication slots, and recovery considerations.
• Debezium: PostgreSQL connector — snapshots, event payloads, deletes, and transaction metadata.
• Confluent: JDBC source connector — incremental polling modes and their limitations.
• Apache Kafka: Design — delivery semantics, external destinations, compaction, and tombstones.
• PostgreSQL: Logical replication restrictions — DDL and destination-schema constraints.
• Microsoft: Search index projections — maintaining derived chunks as source documents change.