1. The Problem
Nightly ETL job performed full `SELECT *` table scans over 20M historical records. Pipeline execution time reached 45 minutes and ballooned cloud egress costs.
2. What I Initially Thought
3. What I Learned
Incremental extractions track a high-watermark column (`updated_at`). By storing the timestamp of the last processed record, extractions scan only rows modified since the last run.
4. What I Built
Watermark state persistence module in PostgreSQL storing last extracted timestamp per pipeline, filtering source tables with `WHERE updated_at > last_watermark`.
SELECT * FROM tasks WHERE updated_at > :last_watermark ORDER BY updated_at ASC LIMIT :batch_size;
5. The Experiment
Full table extraction scanning 20 million rows every night. Execution duration: 45 minutes.
Implemented incremental extraction querying only records updated after high-watermark timestamp.
Execution time reduced from 45 minutes to 35 seconds per batch; scanned row volume cut by 99.2%.
6. What Went Wrong
Encountered late-arriving data race conditions when source transactions committed with timestamps earlier than the watermark execution window.
7. Engineering Decision & Trade-offs
Added a 5-minute overlap lookback window (`last_watermark - INTERVAL '5 minutes'`) to guarantee late-committing transactions are captured.
8. What I Would Do Differently in Production
Ensure source tables have a composite index on `(updated_at, id)` to prevent full table scans during incremental `WHERE updated_at > ?` filter queries.
Questions I Can Now Answer Confidently in an Interview:
- How do you implement incremental ETL using high-watermarks?
- How do you handle late-arriving data or out-of-order transaction commits in incremental extractions?
- Why is ordering by watermark timestamp critical when fetching incremental batches?