DT3: Building reliable pipelines
Incremental loads, backfills and late data
🎯You'll be able to
Load only what's new, re-load past days on purpose (a backfill), and handle files that arrive late or are corrected.
🏠The everyday picture
A newspaper's archive adds today's paper each morning, without reprinting the last ten years. If yesterday's edition had a mistake, they replace that one edition.
📘What it really is
- A full load rebuilds everything every run. Simple and safe while the data is small; slow and costly when it grows.
-
An incremental load processes only new data: today's file, or rows newer than a watermark (the newest timestamp already loaded).
-
A backfill re-runs past periods on purpose: after a bug fix, a new rule or a new column. Idempotent loads (Lesson 2) make backfills safe:
python -m pipeline.run --allis SabziSathi's backfill. -
Late and corrected data: a file can arrive a day late, or be resent with corrections. Load each file by its own slice (its date), and allow a look-back window: re-process the last few days on every run. SabziSathi's dbt fact table re-processes the last 3 days on each incremental run (DT7).
-
Deletes are the hard part: an incremental merge adds and updates rows, but rows removed from a corrected file stay in the target unless the slice is replaced. Choose replace-the-slice when sources can remove rows.
-
Partitions: splitting a big table by date means a load or a query touches one day, not the whole table (DT5).
📝A worked example
Input: load all 30 days, then edit a copy of 2026-10-12.csv so one line has qty 3 instead of 2, and load that date again.
Expected output: the row count stays 10,181 and only that line's amount changes, because the solution replaces the rows from that file.
(Keep the edited file outside raw/, or regenerate the raw data afterwards with python tools/generate.py.)
🌍Where it's used
- Clickstream and app events: loaded every hour by watermark.
- Finance: month-end corrections that need the month re-loaded.
- Data platforms: orchestrators such as Airflow with "catch up" runs for missed days.
📝Case scenario
A telecom's usage report loaded only rows newer than the last run's newest timestamp. One tower's data arrived six hours late with older timestamps, and was skipped for good. A look-back window of 24 hours, with idempotent merging, picked up late rows on the next run.
🖱️Try it
Delete data/sabzisathi.duckdb, then load only 1 to 10 October with ten --date runs, then the whole month with --all. Success: the final
counts match a single --all run exactly.
⚠️Common mistakes
- A watermark with no look-back. Sign: late rows are skipped for good. Fix: re-process a window, idempotently.
- Merging when the source can delete. Sign: removed lines still count. Fix: replace the slice.
- Backfills that append. Sign: re-running a month doubles it. Fix: make every load idempotent first.
🛠️For your project
Project 2 test 9 re-loads a day and rebuilds the warehouse, and expects the same totals.
✅Quick check
What is a backfill?
Show the answer
Re-running past periods on purpose, for example after fixing a bug.
Why does a look-back window help?
Show the answer
It picks up rows that arrived late with older timestamps.