Idempotent Pipeline

Mia opened the dashboard on a Monday and Friday's revenue was double what it should have been. Sales hadn't suddenly doubled, and nothing about the chart looked broken. The 3 a.m. batch had died partway through, somebody reran it in the morning, and the second run's output landed on top of the numbers already there. The person who reran it believed the outage was fixed. The person reading the chart spent half a day looking for whatever had happened on Friday.

Pipelines failing isn't the interesting part — pipelines fail all the time. A source system goes down for maintenance, a file arrives late, a schema shifts. The trouble starts afterward. Some jobs can be rerun and some can't, and that distinction lived only inside the code, invisible from the outside.

So whoever was on call couldn't act on a failure alert. The only person who knew whether the job could simply be rerun, or whether something had to be cleared out first, was the person who wrote it. Recovery regularly waited until morning for a reason that had nothing to do with the failure itself.

An idempotent pipeline removes the question rather than answering it. Instead of checking each job to see whether rerunning is safe, every job is built so that rerunning is safe. Recovery then collapses to a single action: run it again.

Results overwrite the window they belong to rather than adding to what's already there. If a rerun replaces the earlier output, the number of runs stops mattering.

Four things have to hold.

1. Split output into explicit windows, usually by date. The result table carries a processing-date column and the data is partitioned on it. To rewrite May 1, you first have to be able to identify which rows belong to May 1 — and to do it cheaply enough that clearing a window isn't a full table scan.
2. Clear the window before writing it. Delete every May 1 row and insert the freshly calculated ones. When a primary key — the column that uniquely identifies a row — is in place, you can overwrite matching rows instead of deleting first.
3. Read inputs by window, not by clock. Pulling "the last 24 hours" relative to execution time means the same job grabs a completely different range when it runs two days later. Pin it to May 1, 00:00 through 24:00, and any run gets the same input.
4. Record the runs. Logging which window ran when, and for the how-manyeth time, is what tells you whether to suspect the calculation or the run count when a number looks wrong.

The code that breaks this property has recognizable shapes: adding this run's result to a running-total column, appending rows with a fresh sequence number each run, or feeding execution time and random values into a calculation. The first two turn up constantly in batch processing, where work is collected and handled on a fixed schedule. Write a job assuming it runs once a day and the day it runs twice becomes an incident.

A workflow orchestrator — the tool that manages job order and dependencies — makes this easier to live with, since it knows which window failed and can rerun only that one. The tool doesn't supply the property, though. A job that wasn't written to overwrite doubles just the same when an orchestrator reruns it, only now it does so on a schedule nobody is watching.

The most common place is a backfill, the job of reprocessing past windows to fill them in — recalculating a year of history against a new definition, for instance. Without idempotency a backfill is an incident rather than a repair, because the moment the year runs, every day in it inflates.

Next is a change in how a metric is defined. If internal accounts now come out of the active-user count, past numbers need the same treatment or the comparison is meaningless. A large share of data quality work — keeping data in a state people can trust — is exactly this kind of clear-and-rewrite.

Stream processing, which handles records as they arrive rather than in scheduled batches, needs the property too. When a record's handling is interrupted, most tools send it through again, and a consumer that adds the same record twice quietly skews the live figure.

The same idea applied to a single request is idempotency: a server that receives a request twice but acts on it once. An idempotent pipeline widens that from one call to a whole window of data.

"Can I just rerun this job? Is it idempotent?"

Asking whether running a failed window again will throw the numbers off.

"The overnight batch died halfway through, but it's idempotent so I just reran it."

Reporting that recovery finished without any manual clearing step.

"This one isn't idempotent yet. Clear the window before you run it."

Asking a person to do by hand what the code doesn't do for itself, which is also a quiet note that the job needs fixing.

When Theo builds a new aggregation job at Pagedeck, Mia asks the same question every time: what happens if you run May 1 three times? If the answer isn't "the same number," the job isn't finished. Whether the calculation is correct comes after that. It's a cheap question to ask during review and an expensive one to answer six months later, once a year of history has been written by a job nobody trusts to run twice.

What changed at Pagedeck wasn't that pipelines fail less often. It's that a failure no longer requires finding the person who wrote the job.

The most common belief is that building things well enough makes the property unnecessary. Rerunning looks like incident response, so avoiding incidents seems to cover it. But reruns are routine, not exceptional. Metric definitions change, source data lands a day late, a new column needs past values filled in — every month. Skip the property and you accumulate written procedures for clearing each window by hand, and when the person who knows those procedures takes a week off, nobody can correct a number.

The other one reads "idempotent" as "safe to run whenever." What's guaranteed is that the same input gives the same result, not that any moment is a good moment. Rerun a window whose source data has only half arrived and the half-complete result overwrites a complete one, with no error anywhere to mark the moment. The rerun meant as a repair erases numbers instead, and since the cause is timing rather than logic, staring at the code shows nothing.