Implement CDC from OLTP to warehouse

What's really being asked

The question checks whether you understand the mechanics of capturing inserts, updates, and deletes from a live OLTP system while keeping its performance untouched, and whether you grasp the real trade-offs between approaches.

The full answer

Describe at least two methods. Query-based CDC polls the source, either by selecting rows where an updated_at timestamp exceeds the last run or by diffing full snapshots. It is easy to set up and database-agnostic, but it adds read load, has latency tied to poll frequency, and crucially cannot see hard deletes because a deleted row simply vanishes. Log-based CDC reads the database transaction log such as the Postgres WAL or MySQL binlog, often via Debezium and a stream like Kafka. It captures every committed change including deletes, imposes almost no extra query load on the source, and gives low latency, at the cost of operational complexity, log-retention management, and per-engine connectors. For minimal source impact, recommend log-based, landing a change stream that the warehouse merges.

The mistakes people make

Proposing only timestamp polling and forgetting it misses physical deletes, running heavy full-table scans on the production database, or ignoring how out-of-order or duplicate change events are handled downstream.

What usually comes next

How do you handle schema changes in the source? How do you guarantee exactly-once or at-least-once delivery and idempotent merges? How do you bootstrap an initial snapshot before streaming incremental changes?

A concrete example

For a Postgres orders table, you enable logical replication, point Debezium at the WAL, and stream change events to Kafka. Each event carries the operation type, so a delete arrives as an explicit tombstone. The warehouse applies a merge that upserts inserts and updates and removes deleted keys, keeping the warehouse copy accurate without ever scanning the live table.