Backfill a week of reports

What a backfill promises

The bakery’s report counts each day’s orders. A job runs once per day of data: it waits for that day’s export file, extracts it, converts it, loads it into the report and checks the report’s count against the source. A run belongs to its day, not to the day it happens to run on. That is what makes a backfill possible: switch the job on at the end of the week, and it runs all seven days, three at a time.

It also means every day can run more than once. A load fails half way and retries, someone clears a day to rerun it after a fix, a backfill overlaps a day that already ran. A run that adds its rows each time counts that day twice. A run that replaces its day’s rows in one step can run any number of times and leave the same report. That second kind is idempotent, and it is the only kind a backfill is safe with.

Three things to try

Watch Wednesday count twice. The first run does it: Wednesday’s first load fails after writing 96 of its 179 rows, the retry appends all 179, and Wednesday’s check finds 275 orders against the source’s 179. Switch the load to overwrite the day and start over. Every day matches: 2,012 orders.

Write to today. Make each load write to the day the clock says instead of its run’s own day, and start over. Every day’s orders land on the same date, and every check fails. A job that asks the clock for its date cannot be backfilled.

Rerun a finished day. Clear Tuesday after its run succeeds. With append, Tuesday’s total doubles. With overwrite, nothing changes.

Then make Monday’s file five hours late with one run at a time, and watch the week wait behind it.

What the lines and lamps mean

Each row is a day and each lamp a step of its run, from waiting for the file to the check. A lit lamp is running, a filled lamp has succeeded, a hollow lamp has not started, and a cross has failed; a step waiting to retry carries its next try’s time. The column on the right is the day’s total in the report against the source. The three properties under the board are checked after every event.

Credits

Runs that belong to their day of data, and loads that replace a day’s rows instead of adding to them, are the core of Maxime Beauchemin’s essay “Functional Data Engineering: a modern paradigm for batch data processing” (2018).

What it costs

Nothing to run. Every file is static, and your browser runs the simulation.

How it is built

The job graph is one TypeScript module: five steps for each day, from a sensor that waits for the export file to the check, a scheduler that lets runs in oldest day first, 3 at a time unless you choose otherwise, and 2 warehouse slots that every load shares. An extract or a load fails on its own 8% of the time, and each gets 3 attempts, 5 and then 10 minutes apart. Time, the export files and every failure belong to a simulator around it: virtual time in minutes, one seeded random stream, and your presses recorded where they happened. The same seed and the same presses give the same week. That is how the page opens with a picture the build drew, 44 events after the job was switched on, and how your browser then takes over at the same minute.

After every event, a checker tests the three properties, and that no row appears or vanishes without a write. The same simulator runs 3,000 seeded weeks of failed loads, late files, reruns and changed limits in the site's tests. With overwrite and the run's own day, every property holds. With append, a retry or a rerun counts a day twice, and with the clock's day a week's rows land on one date. Three deliberately broken schedulers prove those runs can find a real bug: one that starts a step before the one it needs has succeeded, one that runs a third load beside the warehouse's 2, and one whose overwrite deletes a day in one step and inserts its rows in another.