Implementing CDC with Debezium

When to use

• Replicating an operational database (Postgres/MySQL/SQL Server) to a warehouse/lake in near real time.
• You need deletes and every intermediate change (watermark extraction can't see deletes).
• Consuming or applying a Debezium change stream idempotently.
• Do NOT use for simple periodic batch pulls (use building-ingestion-pipelines).

Workflow

- [ ] Enable the DB log (Postgres logical replication / MySQL binlog / MSSQL CDC)
- [ ] Configure the Debezium connector (tables, snapshot mode, keys)
- [ ] Handle the initial snapshot, then streaming changes
- [ ] Apply changes idempotently: MERGE keyed on PK, ordered by log position
- [ ] Handle deletes (tombstones) and schema changes

1. Enable the log. Debezium reads the DB transaction log: Postgres logical replication (wal_level=logical + a publication/slot), MySQL binlog (ROW format), or SQL Server CDC. Grant the connector the needed privileges.
2. Configure the connector with the tables to capture, the snapshot mode, and the primary key. It emits an initial snapshot, then live change events.
3. Apply idempotently. Each event carries before/after/op and a log position (LSN/GTID). MERGE on the primary key and order by the position so out-of-order or replayed events converge to the correct state.
4. Deletes arrive as op=d (plus a null-value tombstone for log compaction); apply as a delete or soft-delete flag.
5. Schema changes flow through; pair with handling-schema-evolution.

Patterns

Apply a change event with MERGE (soft delete):

MERGE INTO dwh.customers t
USING cdc_batch s ON t.id = s.id
WHEN MATCHED AND s.op = 'd' THEN UPDATE SET t.is_deleted = TRUE, t.updated_lsn = s.lsn
WHEN MATCHED AND s.lsn > t.updated_lsn THEN UPDATE SET t.name = s.name, t.updated_lsn = s.lsn
WHEN NOT MATCHED AND s.op <> 'd' THEN INSERT (id, name, is_deleted, updated_lsn)
  VALUES (s.id, s.name, FALSE, s.lsn);

The lsn guard makes application idempotent and order-safe: replayed or older events are ignored.

Snapshot then stream — the snapshot backfills current state; streaming keeps it fresh. Deduplicate the overlap by log position.

Common pitfalls

• Ignoring log position ordering — applying events out of order corrupts state; guard updates with the LSN/GTID.
• Not handling deletes/tombstones — target diverges from source over time.
• Non-idempotent apply — connector restarts replay events and duplicate rows; MERGE by PK.
• Replication slot not consumed (Postgres) — WAL accumulates and fills the disk; monitor slot lag and keep the consumer running.
• Forgetting schema-change handling — a source DDL breaks the sink; plan for additive evolution.
• Snapshot on a huge table with no throttling — hammers the source; use incremental snapshotting.