learning-it by example

#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.

1. What is the watermark in an incremental load, as the video uses the word?
2. Over three loads, how many rows did the full reload, the plain watermark load and the watermark load with a 60 minute lookback copy?
3. Why did the plain watermark load miss the update to order 99?
4. Why are the repeated rows harmless when the lookback makes the load read some rows twice?
5. How far back does a lookback of one hour reach, and what is that choice?

Practice only: nothing is saved and no score counts towards anything. Each answer comes with the reason.

  1. 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.

  2. 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.

  3. 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.

  4. 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

All Data engineering and pipelines videos

Related videos