Change Data Capture Pipeline

Overview

A change data capture pipeline turns a database's commit log into a stream of row-level change events and delivers them to analytical systems. Compared with polling for updated timestamps, log reading sees every change, including deletes, in commit order.

How it works

A connector attaches to the log, performs an initial snapshot, then tails changes. Each event holds the key, before and after images, operation type and source position.

OLTP DB --WAL--> connector --> topic db.orders (key = order_id)
                                   |--> sink: MERGE into warehouse.orders

Applying events downstream usually follows this merge, with the latest event per key winning:

MERGE INTO dw.orders t USING staged_changes s ON t.order_id = s.order_id
WHEN MATCHED AND s.op = 'd' THEN DELETE
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED AND s.op <> 'd' THEN INSERT *;

Delivery is at least once, so applying by key with the source log position as a tiebreaker makes replays harmless. A history table of all events supports audit and slowly changing dimensions. See the cdc pattern for the general technique.

Pitfalls

• Stuck replication slot: if the consumer stops, PostgreSQL retains WAL indefinitely. Alert on slot lag and set limits.
• Schema evolution: adding or renaming columns requires a registry and compatible sinks.
• Snapshot cutover errors cause gaps or duplicates; rely on connector-managed handoff.
• Leaking raw table internals as an API; consider contracts or an outbox for domain events.
• Large transactions arrive as bursts and stress consumers.

Key takeaways

• Read the log, not the tables, for low-impact complete capture.
• Make sinks idempotent because delivery repeats.
• Monitor connector lag and replication slot size.
• Treat captured schemas as a coupling risk.