Idempotent Task Boundaries and Atomic Output Publication

A retry is only meaningful if running the task twice leaves the same result as running it once. That property is idempotence, and it is not a property of the scheduler. No dependency graph, trigger rule, or retry policy can make a task safe to repeat; the task must be constructed so that repetition is harmless. Since nearly every real workflow repeats tasks (retries after transient failure, backfills over old dates, manual re-runs after a fix), the construction is not an optimisation but a precondition for the scheduler’s other mechanisms to mean anything.

The two failure windows

Consider a task that appends rows to a table. It reads its input, produces rows, and writes them. Two windows exist in which it can be interrupted: before the write is durable, and after.

In the first window the task failed cleanly and nothing was written. Repeating it is safe by accident, because the first attempt left no trace. In the second window the task wrote rows and then failed for an unrelated reason: the process was killed while reporting completion, the network dropped the acknowledgement, the scheduler’s heartbeat was missed. Repeating it appends the rows again. The output now contains duplicates that no downstream stage will remove, because downstream stages typically aggregate or join and duplicates change the aggregate.

The boundary between the windows is the moment the write becomes visible to other readers. Everything before that moment is invisible and repeatable; everything after is committed and must not be repeated blindly. The design goal is to move that boundary to a single point and make the interval between “invisible” and “committed” as short and as controllable as possible, which is what atomic publication means.

Note that the second window is not exotic. Any failure after the data is durable, whether a crash during logging, a timeout on a status call, or an operator restarting the scheduler, produces it. Systems that treat “the task reported failure” as “the task did nothing” will duplicate on exactly these cases.

Staging plus swap

The standard construction writes to a location that readers do not consult, then makes the result visible with one operation that either happens or does not.

For a table, the staging form is a temporary table or a separate partition prefix: the task writes _staging/<run_id> and, on completion, either renames it into place or inserts from it into the target. For a filesystem or object store, the pattern is a directory per attempt with a final rename or a manifest update that points at the new directory. For a table format with a transactional log, such as Delta Lake or Iceberg, the staging is implicit: the writer produces new data files and a commit record, and the commit is a single atomic metadata operation that either appears or does not.

The properties that make this work are worth separating. First, the write phase is invisible, so an interrupted attempt can be discarded without affecting the target. Second, the swap is a metadata operation whose cost is independent of the volume of data, so the commit window is short. Third, the swap is conditional in some implementations, failing if the target already exists, which converts a concurrent double-run into one winner and one error rather than two appends.

-- Staging table holds the attempt's output; the swap is one statement.
CREATE TABLE orders_staging_20250324 (LIKE orders INCLUDING DEFAULTS);

INSERT INTO orders_staging_20250324
SELECT * FROM raw_orders
WHERE order_date = DATE '2025-03-24';

BEGIN;
DELETE FROM orders WHERE order_date = DATE '2025-03-24';
INSERT INTO orders SELECT * FROM orders_staging_20250324;
COMMIT;

The DELETE then INSERT inside one transaction is the simplest swap and it is not free. It rewrites every row for the partition, it holds locks for the duration, and on a columnar warehouse it may rewrite far more data than the insert itself. The alternative, partition replacement, is a metadata operation when the storage layout supports it, and it is why partitioned layouts with a per-partition commit are the usual arrangement for large tables.

The same shape applies at smaller scale. A task that produces a report file can write report.csv.tmp and rename it to report.csv; rename within a filesystem is atomic with respect to other observers, so a reader sees either the old file or the new one. Across filesystems, or on object stores without a rename primitive, the equivalent is a copy followed by a delete, which is not atomic and must be replaced by a manifest or a versioned key that readers resolve.

Deduplication as the alternative

Where staging is impossible, as with an append-only sink or an interface that offers no atomic swap, the remaining option is to make the write itself detect repetition. That requires a key.

A natural key derived from the data (an order id, a composite of business columns) lets the writer use an upsert or a merge, replacing an existing row rather than adding a second copy. This works when the key exists and is stable, and fails when the same logical row can be produced with different values on two attempts, such as a timestamp column defaulted to the write time or a surrogate id generated per attempt. Those columns must be excluded from the key or made deterministic.

A synthetic key derived from the run (the logical date, the attempt id, a hash of the input) lets the writer record which runs have been applied and skip the rest. This is a ledger: a small table of applied run identifiers, checked and updated in the same transaction as the data write. Its advantage is that it works for append-only sinks with no natural key. Its cost is an extra write per attempt and a transaction that spans two tables, plus the need to bound the ledger’s growth or it becomes a scan of every run ever performed.

Both forms of deduplication put the correctness burden on a comparison performed at write time, inside the same atomic operation as the write. If the check and the write are separate operations, an interruption between them reintroduces the original problem at a smaller scale.

Where idempotence fails anyway

Three cases survive both constructions.

The first is a side effect outside the task’s control. A task that sends a notification, calls an external billing API, or increments a counter in another system has effects that no staging table can roll back. Repeating it sends the notification twice. The remedy is to push the identifier of the effect to the external system so that it can deduplicate, or to make the effect a separate task whose repetition is acceptable, which is a decision about the business meaning of the effect, not a scheduling detail.

The second is a task that reads its own output. A task that appends to the table it also reads, or that updates rows in place based on a scan of the same rows, is not repeatable even if each individual write is atomic: the second attempt reads the first attempt’s result and produces something different. The fix is to make the input an immutable snapshot, read from a fixed version or a fixed partition set, so the second attempt sees the same input as the first.

The third is a non-deterministic transformation. Sampling, now(), random identifiers, and ordering without an explicit sort all make two executions differ. A retry then produces a different result, and whichever attempt commits last wins, so the output depends on retry timing. Making the transformation deterministic, with a fixed seed, the logical date instead of the wall clock, and an explicit ordering, restores repeatability at the cost of the flexibility those constructs provide.

The interaction with concurrency

Atomic publication makes a single task repeatable. It does not make two concurrent attempts of the same task safe unless the swap is conditional. If both attempts stage their output and both then replace the partition, the second swap overwrites the first with an identical result and nothing is lost; if the two attempts read different input snapshots, the second swap overwrites the first with a different result and the outcome depends on ordering.

The usual defence is a lock or a lease keyed on the task and logical date, held for the duration of the attempt. That is the mechanism behind worker leases and zombie tasks, and it is also why a scheduler that guarantees one attempt at a time per task is easier to reason about than one that merely intends to. Where the scheduler cannot provide that guarantee, the storage layer’s conditional commit is the last line: a commit that asserts the expected prior version fails for the loser instead of silently overwriting.

Idempotence is therefore a joint property of the task and the sink. A task can be written to be repeatable; whether repeating it is harmless depends on what the sink does with two identical writes. The next layer up is deciding when a repetition should happen at all, which is retry policy design: distinguishing the failures worth repeating from those where a second attempt only wastes time and delays the alert.