Idempotent pipelines: safe to re-run

Every data pipeline eventually runs twice. A job is retried after a network error, an operator re-runs yesterday to fix a bug, a scheduler fires late, or two workers pick up the same task. If running it twice produces a different result from running it once, you get duplicate rows, double-counted revenue and reports nobody trusts. Idempotent pipelines are designed so that running the same step again, with the same input, leaves the world in the same state.

What idempotent means

An operation is idempotent when doing it once and doing it many times give the same outcome. Setting a value is idempotent (x = 5). Adding to it is not (x += 5). Most bugs come from writing the second kind where the first was needed.

The practical promise: you can safely retry, backfill and re-run without cleaning up first.

The usual ways pipelines break it

• Blind appends. INSERT of all of yesterday's rows every run doubles them on re-run.
• Processing "new since last run". State stored somewhere else drifts; a partial failure skips or repeats a window.
• Non-deterministic steps. Using now(), random values or unordered results inside the transformation, so the same input produces different output.
• Side effects. Sending an email or charging a card each time the step runs.

Patterns that make writes safe

1. Overwrite a partition. Write each run's output to a location keyed by its date or batch (for example sales/date=2026-09-01/) and replace it wholesale. Re-running replaces the same partition rather than adding to it.
2. Upsert on a key. Insert if the key is new, update if it exists. A natural or derived unique key is essential.

INSERT INTO daily_sales (sale_date, store_id, total)
SELECT sale_date, store_id, SUM(amount)
FROM staging_sales
WHERE sale_date = :run_date
GROUP BY sale_date, store_id
ON CONFLICT (sale_date, store_id)
DO UPDATE SET total = EXCLUDED.total;

1. Delete-then-insert in one transaction. For the window you are loading, remove existing rows and insert the new ones atomically, so readers never see half a load.
2. Write to a temporary location, then swap. Build the full result off to the side and rename or swap it in as one step.
3. Deduplicate by key when reading, keeping the latest version, if the source can send the same record twice.

Make time an input, not a side effect

Never let a step read "the current time" to decide what to process. Pass the logical date or batch ID in as a parameter. Then run(2026-09-01) always means the same thing, whether it runs at noon or three days late.

def load_day(run_date: str) -> None:
    rows = extract(run_date)          # deterministic for that date
    cleaned = transform(rows)         # no random, no now()
    replace_partition("sales", run_date, cleaned)

Handle side effects deliberately

For actions that cannot be undone (emails, payments, external API calls), record that the action happened, keyed by a unique ID, and check before repeating. Many systems accept an idempotency key so a repeated request is recognised and ignored.

Design for failure in the middle

Ask: if this step dies after half its work, what does a re-run do? Good answers:

• The half-written output is invisible to readers until the step completes (atomic swap or transaction).
• The re-run overwrites or upserts the same keys.
• Nothing depends on a marker that was set before the work finished.

Test it

The simplest test is powerful: run the pipeline twice on the same input and compare the outputs. Then run a step, kill it partway, re-run, and compare again. Add these as automated tests, and add a data-quality check that flags duplicate keys.

Common mistakes

1. Appending without a key or partition.
2. Using wall-clock time inside transformations.
3. Marking a batch "done" before it is fully written.
4. Assuming the scheduler never runs a job twice.
5. Backfills that need manual cleanup first.

A checklist

• Does a re-run of any step replace or upsert rather than add?
• Is the logical date a parameter?
• Is the output invisible until complete?
• Are external side effects protected by an idempotency key?
• Is there a test that runs the pipeline twice and compares?