OrchestrationGENERALFORMAT-SPECIFICWAREHOUSE-SPECIFIC

Idempotent Data Pipelines

Re-running the same logical input must not corrupt or duplicate the result. A pipeline that cannot be re-run is a pipeline whose every bug is permanent.

What actually happens How to build it Can I trust it?

Who needs this, what one row is, and why the obvious build breaks

Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.

The question

If I run this task twice for the same interval, is the result identical to running it once — and if not, what exactly do I do the next time I find a bug in it?

Who needs this

Every consumer, on the day a bug is found. Idempotency is invisible while everything works and is the entire difference between "we fixed March" and "March is wrong forever" (Backfills).

What one row is

The unit is the (input range, code version) pair. A task is idempotent when its output is a pure function of that pair, so any number of executions of the same pair converge to the same output — not merely succeed.

The obvious build

The task inserts its results into the target table. It ran, the rows are there, the dashboard is right. Insert is the simplest write there is, and on the happy path it is indistinguishable from a correct design.

Why it breaks

The task is retried after a transient failure and inserts a second time. Every sum for the interval is now larger and nothing raised an error (Duplicate Rows).

How it breaks with real data

The task is retried after a transient failure and inserts a second time. Every sum for the interval is now larger and nothing raised an error (Duplicate Rows).
A bug is found in March's logic. Re-running March inserts a corrected copy alongside the original, so the fix doubles the data it was supposed to repair (What Backfills Break).
The task computes its window with now(), so re-running it in June processes June. There is no way to address March at all without editing the code (Airflow Concepts).
The task reads the table it also writes — a running total, a "insert rows not already present" pattern — so the second run sees the first run's output and produces something different again (Reasoning About Races: A Method, Not an Instinct).
The task calls an external API as a side effect. The data portion is now repeatable and the effect is not, so nobody dares re-run any of it (Idempotency Keys: The Mechanism).
Two backfill runs for adjacent intervals execute concurrently and both rewrite an overlapping slice, and the final state depends on which finished last (Ordering Guarantees: Four Levels, Four Prices).

Source→Ingestion→Raw→Transformation→Validation→Storage Model→Serving→Consumer→Observability

What is actually happening

Idempotence here is not the mathematical minimum — "applying twice equals applying once" — it is the practical requirement that the output for an interval depends only on that interval's input and the code, so re-execution converges rather than accumulates.
The mechanism that delivers it is almost always replacement scoped to a key you control. Overwrite the partition for the interval; merge on the business key; write to a new location and swap. Each replaces a well-defined slice rather than adding to an undefined one (Upserts and Merges).
The scope of the replacement must match the scope of the run. A task responsible for one day must replace exactly one day — replacing more destroys neighbouring intervals, and replacing less leaves stale rows behind (Partitioning).
Reading a table you also write breaks the property at its root, because the input is no longer the interval's data — it is the interval's data plus the previous run's output. This is the single most common way an otherwise careful pipeline becomes unrepeatable.
Determinism matters as much as write semantics. A transformation that uses now(), a random sample, or an unstable ordering for a "pick one per key" step produces different output from identical input, so a re-run diverges even when the write is a clean replacement (Deduplication).
External side effects cannot be made idempotent by the data platform. They are made idempotent by the receiving system honouring a key you send — which is a backend contract, not an orchestration setting (Idempotency in Backends).

Two writes, one of which you can fix later

Every transformation ends in a write, and the choice made there decides whether the pipeline has a future. Append is the obvious write: it is fast, it needs no knowledge of what is already there, and it is exactly what you want for an immutable log. For a derived table keyed by an interval it is a decision to never be able to re-run.

State the consequence plainly, because it is the thing to remember from this module: a pipeline that cannot be re-run is a pipeline whose every bug is permanent. Not "hard to fix" — permanent. Once a wrong transformation has appended six months of rows, correcting it requires either deleting rows you cannot precisely identify or living with them.

The replacement pattern is not more sophisticated, it is more disciplined. It requires that the task knows which slice it owns, that the slice is addressable, and that the write replaces the whole slice. Those three requirements are what all the specific techniques below have in common.

The write at the end of the task

Append to the target

`INSERT INTO fct_orders SELECT ... FROM stg_orders WHERE order_ts >= :start AND order_ts < :end`. The task adds rows. A retry adds them again. A backfill of March adds a second March. Nothing in the table records which run produced which row, so removing one run's output later requires guessing.

Replace the slice the task owns

The task overwrites the partition for its interval as a single atomic operation, or merges on the business key. Running it once, twice or fifty times leaves the same rows. A backfill of March replaces March, touches nothing else, and needs no cleanup step.

Append makes the output a function of the execution history rather than of the input, so there is no operation that returns the table to a known state. Replacement makes the output a function of the interval and the code, which is what allows any bug found later to be corrected by re-execution — the only repair mechanism a data platform actually has.

Four techniques, in the order you should reach for them

WAREHOUSE-SPECIFICThe delete-then-insert transaction assumes multi-statement transactions over DML, which most analytical warehouses provide and some engines over object storage do not. On Iceberg or Delta the same intent is a single partition-overwrite commit; on an engine without transactions it must become write-to-a-new-location-then-swap, or the interval is briefly empty for readers.

These are not alternatives to be chosen between so much as a ladder. Partition overwrite is the strongest and simplest and applies whenever the output is naturally sliced by time. Merge applies when the grain is a mutable entity. Purity and the no-self-read rule are not write strategies at all — they are the preconditions that make the first two mean anything.

The SQL below shows the first two against a plain warehouse. Read the third and fourth blocks carefully: the self-read example is the one that looks most reasonable in review and is the most reliable way to build a table that can never be rebuilt.

One detail that decides whether any of this works: the replacement and the write must be atomic together. On a table format that is one commit. On a plain warehouse it is a transaction or a staging-table swap. A delete followed by an insert with a failure in between leaves the interval empty, which is a different bug with the same cause (Atomic Publish).

Interval overwrite, merge, and the two ways to lose repeatability

1-- 1. PARTITION OVERWRITE — the default. The task owns one interval and
2--    replaces it entirely. Re-runnable any number of times.
3BEGIN;
4  DELETE FROM fct_orders
5   WHERE order_date = DATE :interval_start;
6 
7  INSERT INTO fct_orders (order_id, order_date, customer_id, amount_minor, produced_by)
8  SELECT o.order_id, o.order_date, o.customer_id, o.amount_minor, :code_version
9    FROM stg_orders o
10   WHERE o.order_ts >= :interval_start
11     AND o.order_ts <  :interval_end;
12COMMIT;
13 
14-- 2. MERGE ON THE BUSINESS KEY — when the grain is a mutable entity rather
15--    than a time slice. The predicate IS the idempotency mechanism.
16MERGE INTO dim_customer AS t
17USING (
18  SELECT customer_id, name, tier, updated_at
19    FROM stg_customer
20   WHERE updated_at >= :interval_start
21     AND updated_at <  :interval_end
22) AS s
23   ON t.customer_id = s.customer_id          -- the real key, not a per-run surrogate
24 WHEN MATCHED AND s.updated_at > t.updated_at
25      THEN UPDATE SET name = s.name, tier = s.tier, updated_at = s.updated_at
26 WHEN NOT MATCHED
27      THEN INSERT (customer_id, name, tier, updated_at)
28           VALUES (s.customer_id, s.name, s.tier, s.updated_at);
29 
30-- 3. NOT A PURE FUNCTION OF (input range, code version).
31--    now() makes the output depend on when it ran; a re-run in June
32--    processes June and the March bug stays unfixed forever.
33INSERT INTO fct_orders
34SELECT * FROM stg_orders
35 WHERE order_ts >= current_date - INTERVAL '1 day';
36 
37-- 4. READING THE TABLE YOU ALSO WRITE.
38--    Looks defensive. The input now includes the previous run's output,
39--    so run two produces something run one could not have produced, and
40--    no execution ever converges.
41INSERT INTO fct_orders
42SELECT s.*
43  FROM stg_orders s
44 WHERE NOT EXISTS (SELECT 1 FROM fct_orders f WHERE f.order_id = s.order_id);

Block 4 deserves the attention. It is idempotent in the narrow sense — running it twice adds nothing the second time — and it is still unrepeatable, because a corrected row can never replace an existing one. Every backfill of fixed logic silently does nothing, and the pipeline reports success while refusing the repair.

Where idempotency quietly stops holding

A task can use every technique above and still fail to converge, because idempotency is a property of the whole (input, code, write) triple and the techniques only address the write. The recurring cause is a mismatch of grain: the scope the task replaces is not the scope its input actually covers.

The table below tracks the unit through a typical order pipeline and names the mismatch at each stage. Read the breaksIf column as the list of ways a correct-looking overwrite replaces the wrong amount of data — too much, too little, or a slice defined by a different clock than the one the input uses.

The last two rows are the ones that catch experienced teams. A merge is only as good as the uniqueness of its key, and an interval overwrite defined on ingestion time will not replace the rows the source assigned to that interval by event time. Both produce a table that is internally consistent, passes its tests, and disagrees with the source (Event Time).

What the task replaces versus what its input covers

Stage	One row is	Breaks if
Raw landing for the interval	One delivered record, possibly delivered more than once.	The overwrite is keyed by arrival and the downstream is keyed by event time, so the two disagree about which rows belong to the interval (Ingestion Time).
Staging model for the interval	One entity, reconstructed as the latest change within the interval.	The "latest" tie-break is unstable, so two runs over identical input pick different winners and neither is wrong (CDC Ordering and Transaction Boundaries).
Fact partition for the interval	One order that occurred inside the interval.	The partition column is the load date rather than the order date, so re-running an interval replaces rows belonging to several different days.
Dimension merged on a key	One customer, current state.	The source contains two rows for a customer in the interval and the merge has no deterministic ordering, so the surviving row depends on physical layout (Grain: What Does One Row Represent?).
History-tracking dimension	One customer-version, with validity dates.	A re-run appends a new version identical to the current one, because the task compares against the wrong version rather than the latest (SCD Type 2 in Practice).
Aggregate mart for the interval	One country-day with revenue pre-summed.	The mart is incrementally added to instead of recomputed for the interval, so a corrected fact partition never propagates and the mart drifts from the fact table it claims to summarise.

Every row is a case where the write is a clean replacement and the pipeline is still not idempotent, because the slice replaced is not the slice the input defines. Matching those two is the actual work.

Proving it rather than believing it

Idempotency is a claim about behaviour under repetition, which means it can be tested directly — and almost never is. The double-run test is cheap: run the task twice against the same fixed input and compare. Teams that add it find non-idempotent tasks immediately, because a graph of any size normally has two or three.

In production the standing detectors are different. You cannot re-run to test, so you assert the invariants the property implies: keys are unique within an interval, an interval's row count does not change without a code change, and duplicate rates do not correlate with retry counts.

As always, read the misses column as the reason none of these is sufficient alone. The gap that matters most is the last row: every check here operates inside your platform, and none of them can see the effect of a re-run on an external system that already received a message (At-Least-Once Delivery).

Checks that a pipeline is genuinely re-runnable

Check	Expresses	Catches	Still misses
Double-run test on a fixed input in a test environment	Executing twice converges to the same output.	Append semantics, self-reads, unstable tie-breaks, and any dependence on the wall clock.	Non-determinism that only appears under concurrency, and anything depending on external state that happened not to move during the test.
Uniqueness on the business key within an interval	Each real-world entity appears once in the slice the task owns.	Accumulation from retries and re-run backfills; a merge whose predicate does not match the real key.	Duplicates that differ in the key — a re-emitted event with a fresh event id is two rows and one order (Deduplication).
Row count per interval compared against the previous run of the same interval	Re-running does not change the size of the slice.	Silent inflation from partial writes and overlapping backfill runs.	A re-run that replaces rows with different but equally numerous wrong rows, and any change legitimately caused by late-arriving data (Late-Arriving Data).
Retry count joined against duplicate rate, per task per day	The reliability mechanism is not damaging the data.	A non-idempotent task whose automatic retries have been quietly duplicating rows for months.	Non-idempotent tasks that simply never fail, which are latent rather than safe.
Produced-by code version present on every partition	Provenance — which logic built this slice.	Intervals still holding output from a version you have since fixed.	Everything about whether the current version is correct; it records identity, not quality (Metadata: Technical, Operational and Business).
Reconciliation of an interval against the source after re-run	The repaired slice matches the system of record.	Over-wide and under-wide overwrites, and re-runs against a source that has moved.	Errors present in both source and target, and anything about external effects the re-run may have repeated (Reconciliation).

The first row is the only one that tests the property directly; the rest infer it from consequences. A team that adds only one of these should add the first, and should expect it to fail the day it is introduced.

How to build it

Most important first.

Make the task a pure function of (input range, code version). Every parameter it needs comes in from the orchestrator; nothing comes from the clock, the environment or the current contents of its own output.
Write to a partition keyed by the data interval and overwrite it wholesale, and make that replacement atomic — a partition swap, a table-format commit, or a staging-table exchange — so a failure mid-write never leaves a half-replaced interval. This is the strongest and simplest form: the write replaces a slice the task owns, so any number of re-runs converge (Partitioning, Atomic Publish, Open Table Formats).
Merge on a business key rather than inserting, wherever the grain is a mutable entity rather than a time slice. The merge predicate is the idempotency mechanism, and it must be the real key, not a surrogate generated per run (Surrogate Keys).
Never `INSERT` into a table you also read. If a computation genuinely needs prior state, read it from a separate snapshot or from the source of truth, so the input is fixed before the write begins (Snapshot Tables).
Stamp each row or partition with the interval and the code version that produced it. Idempotence lets you re-run; provenance lets you know which rows still need it (Reprocessing vs Retrying).
Push side effects to the edges of the graph and give them idempotency keys, so the data portion of the pipeline is freely re-runnable and only one task requires care (Webhook Idempotency).

What this actually promises

Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.

Interval-scoped overwrite guarantees convergence for that interval: after any number of executions, the partition contains exactly the output of the last complete one. It guarantees nothing about neighbouring intervals or about late data that arrived between runs (Late-Arriving Data).
Merge on a business key guarantees at most one row per key. It guarantees nothing if the key is not actually unique in the source, and duplicate keys in the source turn a merge into a non-deterministic choice (Grain: What Does One Row Represent?).
These techniques give once-only effect on the output write — not exactly-once processing end to end. Input consumption remains at-least-once and state updates are separate; the phrase only means something when you name which of the three it applies to (Exactly-Once: Input Consumption, State Update, Output Write).
Nothing here guarantees that two re-runs read the same input. Reproducibility across time additionally requires an immutable source, which is what a raw landing zone is for (The Raw Landing Zone).
Atomic publish guarantees readers never see a partial state. It does not guarantee that what they see is complete for the interval — that is a completeness check, not a write property.

Can I trust it?

A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.

The check that would catch this

The check that proves idempotency is a double-run test in a non-production environment: run the task twice for the same interval against the same input, and assert that the output is byte-identical, or at minimum that row counts, key uniqueness and summed measures are unchanged.
It misses non-determinism that only appears with concurrency — two intervals racing on an overlapping slice — and it misses anything that depends on external state that happens not to change during the test.
In production, a uniqueness assertion on the business key per interval is the standing detector. It catches accumulation from retries and backfills, and it misses duplicates that differ in the key, which is exactly what a re-emitted event with a fresh event id looks like (Deduplication).

Freshness

Overwrite-by-interval costs nothing in freshness for the current interval and makes late-arriving data cheap: the fix is to re-run an old interval, not to invent a correction record.
Merge is slower than append on most engines because it must locate existing rows, so the strongest correctness property is also the one that adds per-run latency (Upserts and Merges).
Staging-then-swap adds a write of the full slice before publication. That is latency spent buying the guarantee that consumers never read a partial interval (Atomic Publish).

When the schema or meaning changes

A code change means the same interval now has two possible outputs. That is fine and expected — it is why the code version belongs on the row — but it means "re-running produces the same result" is a claim about a fixed version, not about the task forever (Reprocessing vs Retrying).
A schema change to the output makes old partitions structurally different from new ones. Re-running an old interval with new code repairs that for the intervals you re-run and leaves a boundary at the ones you do not (Schema Evolution).
Changing the partition key changes what a re-run replaces. A pipeline that was idempotent under a daily partition is not automatically idempotent under an hourly one, because the scope of the overwrite no longer matches the scope of the run (Partition Cardinality).

How to re-run this safely

This lesson *is* the recovery story for the whole module. Every repair described anywhere else — retry, closure clear, backfill, replay — is safe if and only if the tasks involved are idempotent, and is a fresh incident if they are not.
The operational form: clear the state for an explicit interval range, re-run with concurrency bounded, verify the interval against the source, and publish. Four steps, and they are the same four steps every time, which is the benefit (Planning a Backfill).
Where a task is not yet idempotent, make it so *before* the repair rather than during it. Repairing with a non-idempotent task is how the second incident begins (Validating a Backfill Before You Publish).
Keep the raw landing immutable so the input to a re-run is genuinely the same input. Idempotent code over a mutated source is reproducible in form and not in fact (Keeping Raw History: The Recovery Position and the Liability).
For external effects, recovery is compensation rather than replay — a correcting message, a reversal, an apology — and it should be designed as a task rather than improvised (A Dead-Letter Queue Is a Workflow, Not a Bin).

What can go wrong

Failure modes

Append semantics surviving in one task of an otherwise idempotent graph, so full re-runs are unsafe for a reason nobody remembers.
An overwrite whose scope is wider than the run's interval, destroying adjacent days during a backfill (What Backfills Break).
A merge on a key that is not unique in the source, so which row wins depends on physical ordering.
A "pick the latest per key" step whose tie-break is unstable, producing different winners on each run (CDC Ordering and Transaction Boundaries).
Concurrent runs of adjacent intervals overlapping on a shared slice, with the last writer deciding the result.
The mitigation failing: a double-run test that passes because the test environment's source is static, while production's source is mutable and moves between runs.

Misreads

"Our tasks are idempotent because they use INSERT ... ON CONFLICT DO NOTHING." That is idempotent only against re-delivery of identical keys. It also silently discards genuine updates, so a corrected row never lands (Upserts and Merges).
"Idempotency is about retries." Retries are the smallest case. The real payoff is that any bug in any transformation is repairable for all of history, which is the difference between a platform and a museum.
"The orchestrator handles it." No orchestrator can make a write idempotent. It can only re-run a task; what that does is decided by the code (Job Idempotency).
"We are exactly-once because we use a transactional sink." A transactional sink gives once-only effect on the output write, assuming a deterministic recomputation of the same input range. Consumption and state are separate, and the phrase is meaningless without saying which (Exactly-Once: Input Consumption, State Update, Output Write).
"Deduplicating downstream is equivalent." It repairs the symptom at one consumer and leaves every other consumer, every export and every trained model with the duplicates (Deduplication).

Operating it

How you see it in production

Duplicate rate per business key per interval, tracked over time. A non-zero rate that correlates with retry counts is a non-idempotent task confessing (The Data Quality Dashboard).
Row counts per interval across re-runs. An interval whose count changes without a code change is not converging.
Retry counts joined against duplicate rates, which is the query that attributes silent inflation to the reliability feature that caused it (Pipeline Metrics).
A produced-by column showing the code version per partition, so "which intervals still need re-running" is a query and not an archaeology project (Metadata: Technical, Operational and Business).

What changes at 10x and 100x

At 10x volume the overwrite unit becomes the constraint: a daily partition that was cheap to rewrite becomes expensive, and the answer is finer partitions rather than incremental appends (Partitioning).
At 100x, merge on a huge target needs the engine to prune candidate files by partition and sort key, or every small change reads everything (Clustering and Sort Order).
More parallel backfill runs increase the chance of overlapping writes. Idempotency of a single task does not imply safety of many tasks writing concurrently, and that gap widens with scale (Ordering Guarantees: Four Levels, Four Prices).

What drives cost here

Overwrite rewrites the whole interval even when one row changed. That is the cost of the guarantee, and it is bounded by the interval size — which is one more reason interval granularity is an important decision (File Size and the Small-Files Problem).
Merge costs a read of the target to locate matching keys, so it scales with the target rather than with the change set unless the engine can prune by partition (Partition Pruning).
The cost of *not* having idempotency is paid in incidents: manual deduplication, forensic reconciliation, and periods of history that are simply written off (What Actually Drives Data Platform Cost).

What this approach costs

Overwrite costs rewriting unchanged data; append costs correctness. That is the whole trade, and it is not close: the write amplification is bounded and predictable, while the duplication is unbounded and discovered later.
Merge buys entity-level correctness and costs read work against the target on every run, plus a dependency on a key that is genuinely unique — which is a data-quality assumption dressed as a write strategy.
Purity costs convenience. A task that cannot read now(), cannot read its own output and must receive every parameter is more tedious to write, and it is the only kind of task you can fix later.

Where this applies

Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.

GENERALThe property — output determined by input range and code version — is universal. What varies is the mechanism available to express it: a partition overwrite on a lake, a MERGE on a warehouse, a table-format transaction, or a staging table swap where none of those exist.
FORMAT-SPECIFICTable formats such as Iceberg, Delta and Hudi give an atomic, snapshot-isolated replacement of a partition, so a failed overwrite leaves the previous snapshot intact. Plain Parquet directories on object storage give you no such boundary: a partial delete-then-write leaves readers with whatever files happen to exist at that moment.
WAREHOUSE-SPECIFICMERGE semantics, whether DDL such as partition exchange is transactional, and whether a multi-statement transaction is available at all differ by warehouse. Where transactional DDL is missing, the atomic-publish pattern becomes write-to-staging-then-swap rather than delete-then-insert.

Where the depth lives

This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.

Databasetransactions-and-acid isolation-levels update-delete-under-mvcc

Backendjob-idempotency idempotency-keys idempotency-in-backends idempotency-storage duplicate-detection at-least-once-delivery webhook-idempotency

Architectureidempotency event-sourcing

Concurrencyordering-guarantees reasoning-about-races

Distributed Systemsidempotent-operations idempotency-scope

Domains that do not exist yet

— Distributed Systems owns why at-least-once is the delivery guarantee you are given rather than one you choose, and therefore why idempotent effects are the only mechanism that turns repeated delivery into a single outcome.
— DevOps / Production Engineering owns the code-version half of "a pure function of (input range, code version)": pinning, tagging and recording which build produced which artefact is deployment discipline, and without it the provenance column has nothing true to record.