Change Data Capture (CDC)

Overview

Change Data Capture (CDC) tracks changes to data in a source system—typically a transactional database—and makes those changes available as a stream of events, one per inserted, updated, or deleted row. Instead of periodically querying an entire table and comparing snapshots, a CDC system captures each change close to where it happens, which keeps downstream systems up to date with much lower latency and far less load on the source database.

CDC is the backbone of most modern data replication and streaming pipelines: it's how a data warehouse stays in near-real-time sync with an operational Postgres or MySQL database without full-table re-scans, and it's how event-driven architectures propagate state changes between services.

How CDC typically works

The most common and least invasive approach is log-based CDC, which reads a database's internal write-ahead log or binary log rather than querying tables directly:

• PostgreSQL: logical replication slots decode the write-ahead log (WAL) into a stream of row-level changes.
• MySQL: the binary log (binlog) records every committed change and can be tailed by tools like Debezium.
• SQL Server: has native CDC support that captures changes into dedicated change tables.

Other, less common approaches include trigger-based CDC (database triggers write changes to a separate table) and query-based CDC (periodically diffing based on a timestamp or version column), but both add load to the source or lose deletes, which is why log-based CDC is generally preferred at scale.

Captured changes are usually published to a message broker like Kafka or a cloud pub/sub system, from which downstream consumers—a warehouse loader, a search index, a cache—apply them.

Applying CDC changes downstream

A downstream table needs to apply CDC events in a way that reflects the final state of each row—typically an upsert (insert-or-update) keyed by primary key, plus deletion handling for delete events:

Copy code
-- conceptual example: apply a batch of CDC events to a target table
INSERT INTO customers_snapshot
SELECT id, name, email, updated_at
FROM cdc_events
WHERE op IN ('insert', 'update')
ON CONFLICT (id) DO UPDATE SET
    name = excluded.name,
    email = excluded.email,
    updated_at = excluded.updated_at;

DELETE FROM customers_snapshot
WHERE id IN (SELECT id FROM cdc_events WHERE op = 'delete');

DuckDB isn't typically the CDC capture mechanism itself, but it's a natural place to land and query CDC output: a batch of CDC events written to Parquet or JSON files by a connector can be read directly with read_parquet() or read_json() and merged into a DuckDB table using the same upsert pattern.