Idempotent and backfill-safe jobs

A job is idempotent when running it twice leaves the same result as running it once. Jobs get rerun all the time (a retry after a timeout, a fix after a bug, a backfill of last month), so this is the property that keeps a warehouse right.

The classic mistake is a job that just appends. Run it twice and every row is there twice; totals double and nobody notices for a week.

One fix is to replace the run's slice of data: delete the partition (say, one day) and insert it again, in one transaction, or overwrite the partition where the warehouse supports it. The other is to upsert on a key: insert new rows and update the ones that already exist, with MERGE in most warehouses or INSERT … ON CONFLICT in PostgreSQL and SQLite. Here a daily revenue job runs twice and the table still has one row for the day:

CREATE TABLE daily_revenue (day TEXT PRIMARY KEY, revenue REAL);

-- the job for 2024-09-15, run twice
INSERT INTO daily_revenue (day, revenue)
SELECT o.ordered_on, SUM(i.qty * p.price)
FROM orders o JOIN order_items i ON i.order_id = o.id JOIN products p ON p.id = i.product_id
WHERE o.ordered_on = '2024-09-15' GROUP BY o.ordered_on
ON CONFLICT(day) DO UPDATE SET revenue = excluded.revenue;

INSERT INTO daily_revenue (day, revenue)
SELECT o.ordered_on, SUM(i.qty * p.price)
FROM orders o JOIN order_items i ON i.order_id = o.id JOIN products p ON p.id = i.product_id
WHERE o.ordered_on = '2024-09-15' GROUP BY o.ordered_on
ON CONFLICT(day) DO UPDATE SET revenue = excluded.revenue;

SELECT * FROM daily_revenue;

Use the run's logical date, never "now". A job that processes WHERE day = today() does the wrong day when it's rerun tomorrow. Pass the date in, and write each run so it touches only its own slice.

A backfill reruns a job for past dates: to fill history for a new table, or to repair a bug. With idempotent, date-sliced jobs it's just "run each day from September to now", and the runs can go in parallel because they don't touch each other's data. Without them it's a careful manual cleanup.

Late data needs a plan too: if payments can arrive up to three days late, have each run reprocess the last few days, which is only safe because the job is idempotent.