How do you make a data pipeline safe to rerun?

02:59, run 1 inserts the orders, ok. 03:00, a timeout, retrying. 03:04, run 2 inserts the orders, ok. The table went from 1,204 rows to 2,408: every order loaded twice, and if anything downstream charges customers, so did the charges.

A retry should be boring. Here is why this one was not, and the three patterns that fix it.

The crime scene

Nothing here is broken. That is the problem: it works twice.

-- runs every night at 03:00
INSERT INTO orders
SELECT * FROM staging_orders;

-- timeout after commit, before ack.
-- the scheduler does what
-- schedulers do: it retries.

The retry is not the bug. Retries are normal, networks time out, schedulers reschedule. A pipeline that cannot survive one retry is the bug.

Idempotent, in one picture

Run it once, twice, or five times: the table ends up identical.

Formally: f(f(x)) = f(x). Practically: reruns are free, and 3am pages stop.

Fix 1: MERGE on a key

Let the natural key decide: update if it exists, insert if it does not.

MERGE INTO orders o
USING staging_orders s
  ON o.order_id = s.order_id
WHEN MATCHED THEN UPDATE
  SET amount = s.amount
WHEN NOT MATCHED THEN
  INSERT VALUES (s.*);

A rerun matches every row and changes nothing. Postgres spells it INSERT ... ON CONFLICT DO UPDATE. Same idea, same safety.

Fix 2: overwrite the slice

Delete the day, reload the day. The rerun rewrites the same slice.

BEGIN;
DELETE FROM orders
 WHERE order_date = '2026-08-18';
INSERT INTO orders
SELECT * FROM staging_orders
 WHERE order_date = '2026-08-18';
COMMIT;

The warehouse version is INSERT OVERWRITE PARTITION. One transaction, or you traded one bug for another: a crash between the DELETE and the INSERT would leave the day empty.

A third variant for streams and event tables: stamp every load with a run id and dedupe on it downstream. Same property, different layer.

The rerun checklist

Before you ship a pipeline, ask it these four questions:

• Natural key? Something stable to merge on. Not the auto-increment id.
• Scoped runs? A run owns a slice (a date, a batch id) it can rewrite.
• Safe retries? Run it twice in staging. Diff the row counts.
• Late data? Yesterday reruns when stragglers arrive. The same property saves you.

If you cannot rerun yesterday without fear, you do not have a pipeline. You have a ritual.

The takeaway

Plain INSERT plus a retry is how 1,204 orders become 2,408 at 3am. MERGE on a natural key, overwrite the partition, or dedupe on a run id: any of the three makes reruns boring, which is the whole point. All three patterns and the rerun checklist fit on the print-ready PDF above. Pin it near the pager.