How would you implement Change Data Capture (CDC)?

WHAT THIS TESTS: This question tests your practical knowledge of data engineering patterns for moving data from a live transactional system (OLTP) to an analytical one (Data Warehouse). The interviewer is evaluating your ability to weigh the trade-offs between performance impact, data completeness, latency, and operational complexity. They want to see if you prioritize protecting the production source system.

A GOOD ANSWER COVERS: A strong answer outlines a clear strategy. First, acknowledge the primary constraint: minimal impact on the production OLTP database. Second, present and compare at least two distinct CDC methods. The best two to compare are log-based CDC and query-based CDC. Third, analyze the pros and cons of each. Log-based CDC reads the database's internal transaction log (e.g., Postgres WAL, MySQL binlog), offering low source DB impact and capturing all changes including deletes. Query-based CDC involves polling tables for a timestamp column like updated_at, which is simpler to implement but puts significant load on the DB and misses deletes. Fourth, make a clear recommendation for log-based CDC for this use case, citing its low impact and completeness, and mention a common tool like Debezium.

COMMON WRONG ANSWERS: A major red flag is suggesting periodic full table dumps. This is not CDC and causes massive load. Another weak answer is proposing only query-based polling (e.g., SELECT * FROM table WHERE updated_at > last_sync) without acknowledging its two critical flaws: it misses deletes and the polling queries can overwhelm a busy production database. Mentioning triggers is better, but you must also mention their performance overhead on write operations and maintenance burden. Failing to mention the impact on the source database at all is a failure to understand the core constraint of the problem.

LIKELY FOLLOW-UPS: Be ready for questions about the initial data load (snapshotting), handling schema evolution (e.g., Avro schemas and a schema registry), ensuring data ordering and exactly-once processing semantics (often using Kafka), and how you would monitor the replication pipeline for lag.

ONE CONCRETE EXAMPLE: For a production Postgres database, I would enable logical replication and use Debezium's Postgres connector. This connector runs within a Kafka Connect cluster. It performs an initial consistent snapshot of the tables, then seamlessly transitions to streaming changes from the Postgres Write-Ahead Log (WAL). Each change event (insert, update, delete) is published as a structured message to a Kafka topic. A downstream consumer, like a Snowflake Kafka connector or a custom Flink job, then reads from Kafka and applies these changes idempotently to the target data warehouse. This provides low latency (seconds) replication with minimal impact on the production database.