Data Engineering interview question · Most asked
How do you implement incremental extraction, and what can go wrong?
Summary
Track a watermark (for example the highest updated_at loaded) and extract rows newer than it. It can miss rows from transactions that commit after the extract with an older timestamp, rows with equal timestamps, clock-skewed writes and hard deletes. Use an overlap window with idempotent upserts, log-based CDC, and regular reconciliation.
Longer Answer
updated_at is set when a row is written but visible only when its transaction commits, so a slow transaction can land behind the watermark. Re-reading a lookback window longer than the longest transaction fixes most gaps as long as the load upserts by primary key. CDC avoids the problem by reading changes in commit order.
Key Points
- Watermark on updated_at or an increasing ID.
- Late commits and deletes are missed.
- Overlap window + upsert.
- Reconcile with the source regularly.
Deep dive: ETL and Data Pipelines: Incremental Extraction and Watermarks
Explanation
What interviewers look for
Knowing the late-commit problem and the overlap + idempotent-load fix.
Follow-up questions
- How would you detect rows the pipeline missed?
More most-asked Data Engineering questions
- What is the difference between ETL and ELT, and when would you choose each?
- What is change data capture (CDC), and why is log-based CDC preferred over polling?
- 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.