Data Engineering interview question · Most asked
What is change data capture (CDC), and why is log-based CDC preferred over polling?
Summary
CDC captures every insert, update and delete from a source database. Log-based CDC reads the database's replication log (PostgreSQL WAL via logical decoding, MySQL binlog) and emits changes in commit order, including deletes, with little load on the source. Polling queries see only current rows, miss deletes and intermediate updates, and repeatedly scan the primary.
Longer Answer
Tools such as Debezium or managed connectors take an initial snapshot, then stream change events (usually to a broker), tracking the log position to resume exactly. Consumers apply changes idempotently and in order per key. Risks: replication slots retaining WAL and filling the source disk if consumers stop, and schema changes flowing through the stream.
Key Points
- Reads the database's own change log.
- Captures deletes and every update.
- Low source load, commit order.
- Monitor slot lag and WAL retention.
Deep dive: ETL and Data Pipelines: Change Data Capture for Pipelines
Explanation
What interviewers look for
Delete handling, ordering, initial snapshot + log position, and the slot/WAL operational risk.
More most-asked Data Engineering questions
- What is the difference between ETL and ELT, and when would you choose each?
- How do you implement incremental extraction, and what can go wrong?
- What does it mean for a data pipeline to be idempotent, and how do you achieve it?
- Batch or streaming: how do you decide?
- How do you deduplicate events, and what does 'exactly-once' really mean?
- What is a star schema, and why is it used in data warehouses?
- What is the grain of a table, and what is join fan-out?
- Explain slowly changing dimensions, especially Type 1 and Type 2.