8 Jul 2026 · 8 min read
What Happens if We Run It Twice?: Idempotency as a Design Discipline
Every pipeline gets re-run — by a scheduler, a retry policy, or a colleague at 2am. Idempotency is the property that makes that boring. The principle, the four families of technique, and the one question that should be in every design review.
At 2:47am, an orchestrator noticed that last night’s sales load hadn’t acknowledged in time, and did exactly what it was configured to do: it ran the job again. The job had, in fact, succeeded — the acknowledgement was lost, not the work. By breakfast, revenue was double, three dashboards agreed with each other (they always agree; they share the table), and a finance director was composing an email with the word “urgent” in it twice.
Nothing malfunctioned. Every component did its job. The system was simply missing one property, and it’s the property this post is about: idempotency — running a thing twice leaves the world exactly as running it once did. Mathematicians write it f(f(x)) = f(x). Data engineers write it at 3am, with less notation and more feeling.
Why “exactly once” is a bedtime story
The reason idempotency is a discipline rather than a nice-to-have is that at-least-once delivery is the resting state of the universe. Schedulers re-run jobs on lost acknowledgements. Retry policies re-send batches on timeouts. Change streams replay from checkpoints. Fanned-out chunks get re-processed after partial failures. Humans re-trigger backfills because the first attempt “looked weird”. You cannot prevent the second execution — distributed systems have spent fifty years failing to. What you control is whether the second execution matters.
So the design question is never “will this run twice?” It will. The question is: what will be true afterwards?
Watch the failure, then the cures
Same job, same input, three different write strategies. Scroll:
marts.daily_sales · partition 2026-07-07
run #1 · committed clean
Run #1: all is well
Three transactions land in the daily partition. The job exits, the dashboard is right, everyone sleeps. This is also the moment the design flaw exists — it just hasn't been invited to express itself yet.
Run #2: the naive append
The retry fires and the job does what it did before: INSERT. Every row lands again. Note the worst part — nothing failed. No error, no alert, just a total that's quietly doubled. Duplicate data doesn't crash; it lies, which is far more expensive.
Cure one: overwrite the unit of work
The rerun replaces the partition instead of appending to it — delete-then-write, or an atomic partition swap. Brutally simple, and it works because the write unit equals the run unit: the job for 7 July owns the partition for 7 July, entirely. Run it fifty times; the fiftieth is identical to the first.
Cure two: merge on the key
When you can't rewrite the whole unit — late data, big partitions, updates trickling in — you make each row idempotent instead: merge on the business key, so a replayed row updates itself into exactly itself. Costs you a reliable key and a heavier write; buys you replays that touch only what changed.
The four families
Those two cures are members of a small taxonomy. Nearly every idempotency technique in data engineering belongs to one of four families, and which family you reach for follows directly from how you write:
| You write like… | Make it idempotent by… | The fine print |
|---|---|---|
| Full or partitioned batch | Overwriting — replace the partition/table, or reset its metadata and rewrite | Align run unit with write unit; watch readers mid-swap |
| Keyed, incremental updates | Merging — upsert on the business key; keep merge state for late data and deletes | No trustworthy key, no merge. Fight for the key first |
| Row-at-a-time into a database | Keyed writes + transactions — an idempotency key checked in-store, and data + checkpoint committed in one transaction | The check and the write must be atomic, or you’ve built a race |
| Curated, published datasets | Immutability + a proxy — never mutate; write version N+1 and flip a pointer/view | Storage is the fee; instant rollback and time travel are the prize |
The fourth family deserves a highlight, because it’s the one application developers find strangest and data people find obvious once shown: stop updating things. Write every run’s output as a new immutable version, and put a thin proxy — a view, a pointer, a manifest — in front of it. The rerun writes version 42; the pointer flip is the only mutation in the system, and a pointer flip is naturally idempotent. If that sounds familiar, it’s the same move as the warehouse migration’s cutover: stage everything, then commit by changing one small thing atomically.
The database family has one rule worth tattooing somewhere: state and data travel together. If your job writes rows and then records its checkpoint, there is a crash window between the two where the world believes the work is undone. The fix is boring and absolute:
begin;
insert into marts.daily_sales
select /* … the actual work … */;
update etl.checkpoints
set high_water_mark = :run_watermark
where job = 'daily_sales';
commit; -- both facts, or neither. No third state exists.One transaction, two facts, zero crash windows. Every “exactly-once” claim a vendor has ever sold you is at-least-once delivery plus some flavour of this, wearing marketing.
How you actually test it
Idempotency has the rare virtue of being mechanically checkable: run the job twice in CI against the same input, and assert the target is byte-identical — a checksum of the output after run one equals the checksum after run two. That’s the whole test. It’s cheap, it’s brutal, and it catches the naive append on the first pull request instead of the first incident. If your pipeline framework makes this test hard to write, that is itself a finding.
The coaching note
I’ve stopped asking teams “is this idempotent?” in design reviews — it invites a confident yes and a philosophical discussion. I ask instead: “walk me through what’s in the table after this runs twice.” It’s concrete, it has a right answer, and watching an engineer trace it teaches them more than my explanation would.
The habit spreads on its own, because it keeps being right. Within a few months the question shows up in reviews I’m not in — asked by the engineer who got burned, of the engineer about to be. That’s the whole player-coach loop in miniature: take the 2:47am page, turn it into a question, and give the question away.
The finance director’s email used “urgent” three times, not two. I under-reported for dignity — theirs and mine.