#111 Data engineering and pipelines 6.7 min
Incremental Loads: Watermarks and the Late Update Trap
Full reloads copied 3,011 rows over three loads; a watermark copied 1,010 and missed one late update.
Shorten the lookback to 20 minutes
Copy the code folder first. In run.py change the lookback value in MODES from 60 to 20, then run python run.py lookback. Predict whether order 99 is fixed. When I ran it, the three loads copied 1000, 7 and 5 rows, 1012 in total, and the check still failed for order 99, because its stamp was 20 minutes behind the watermark and a lookback of 20 minutes starts reading just after that.
A hint
Order 99 is stamped 17:30 and the watermark after load 2 is 17:50. Only stamps after the watermark minus the lookback are read.
Find the smallest lookback that works
Try the lookback value 30 in MODES in run.py, and run python run.py lookback. Predict the result. When I ran it, the check said ok, with 1013 rows copied in total. Then try values below 30 and find where the check starts to fail. In my tries 10 and 20 both failed.
A hint
Compare the lookback with the gap between the stamp of order 99 and the watermark.
Give order 99 a later stamp
In source.py change the stamp of order 99 from 2026-03-01 17:30 to 2026-03-01 17:55 and run python run.py incremental. Predict whether the late update is still missed. When I ran it, the check said ok, with 1011 rows copied in total.
A hint
It is only late if its stamp is behind the watermark when the next load starts.
Predict a deleted order
This is a prediction and I did not run it. Think about what happens if the source deletes order 5 before load 3. Predict what each of the three modes, full, incremental and lookback, does with the warehouse copy of order 5, and what the check reports. The video says a deleted row leaves no new stamp to find.
A hint
Look at what the full reload does to the warehouse table before copying.
These tasks are for you to try on your own machine. Nothing is checked or saved.
What this does not show
One made-up table in SQLite with no deletes, no time zones and no timings; the one hour lookback is a choice, not a rule.
Up next in Data engineering and pipelines
More in Data engineering and pipelines
Related videos
- #112 Idempotent Loads: Append, Upsert, Replace, Delete Insert Data engineering and pipelines
- #266 Event Data Modelling: Events, Sessions, Late Arrivals Data modelling
- #115 Schema Evolution Without Breaking Pipelines Data engineering and pipelines
- #263 Medallion Architecture: Bronze, Silver, Gold in Python Data modelling
