Idempotent Loads
At 02:10 PedalCity's nightly load failed halfway through writing yesterday's rides. The scheduler retried it automatically, and the retry succeeded. The dashboard then showed 61,000 rides for a day that had 40,000. Pipelines will be rerun, whether by retries, by backfills or by a tired engineer. They must be safe to rerun.
The rule
Running a load twice must leave the same result as running it once.
This property is called idempotency. A plain INSERT is not idempotent: every rerun appends another copy. Two patterns make loads safe.
1. Replace the whole partition. Delete the window's rows and insert them again, inside one transaction so nobody ever sees a half-finished state:
BEGIN;
DELETE FROM fact_rides WHERE ride_date = '2026-09-24';
INSERT INTO fact_rides
SELECT ... FROM stg_rides WHERE ride_date = '2026-09-24';
COMMIT;Run it once or five times, and 24 September always ends with exactly one copy of each ride.
2. Merge on a key. Match incoming rows to existing rows on a unique key such as ride_id. Update the rows that match and insert the ones that do not:
MERGE INTO fact_rides AS t
USING stg_rides AS s ON t.ride_id = s.ride_id
WHEN MATCHED THEN UPDATE SET fare = s.fare, ended_at = s.ended_at
WHEN NOT MATCHED THEN INSERT (ride_id, fare, ended_at) VALUES (s.ride_id, s.fare, s.ended_at);One more rule: a load must depend only on its window, never on the clock. A job that filters on "yesterday" computed from now() loads a different day if it is rerun a day later. Pass the run's logical date (the day being processed) into the job instead.
The shortcut
Before shipping any load, run this thought experiment: "If this runs twice in a row, what changes?" If the answer is anything other than "nothing", make it replace a partition or merge on a key, and pin it to its logical date.
Spot the danger
The job does a plain
INSERTof yesterday's rides. Running it twice doubles them.
Drills
Answer fast — trust the rule.
A plain INSERT of 40,000 rides is run twice by mistake. How many rows does that day end up with?
Why wrap the DELETE and INSERT of a partition in one transaction?
Which load is idempotent?
A job loads "yesterday" using now(). It fails on 25 September and is rerun on 27 September. Which day does the rerun load?
Remember
- Running a load twice must leave the same result as running it once.
- Replace a partition in one transaction, or merge on a unique key.
- Pin every run to its logical date, never to
now().