Idempotent task design and completion verification

What makes re-execution safe, and how do you know a task really finished?

The one requirement

Retry is this mechanism's only recovery mechanism. A retry after a failure of unknown state means re-running work that may have partly finished. So the whole layer rests on one requirement:

That is not a nice property. It is the precondition for everything else being safe.

Which write patterns are safe

This forces the write pattern.

• Appends are never idempotent. A retry after a partial append leaves duplicates with no key to remove them by.
• Overwrite by key: drop and rewrite the partition.
• Merge by key: a MERGE on a business key (update rows that exist, insert those that do not).
• Write to a temporary location, then rename atomically.

-- Safe to run twice: the second run replaces the first run's rows for the same day.
DELETE FROM daily_sales WHERE sales_date = '2025-03-05';
INSERT INTO daily_sales
SELECT '2025-03-05' AS sales_date, store_id, SUM(amount)
FROM   orders_clean
WHERE  order_date = '2025-03-05'
GROUP  BY store_id;
-- (In real systems, run both statements in one transaction.)

All three safe patterns require the output to be addressable by the interval key. Idempotency is impossible without identity. That is why the partition scheme and the schedule interval must agree. When people say "just make the task idempotent", what they require is that the output layout matches the run key. That is a data-modeling decision, not a coding style.

The price

The price is write amplification: more bytes written than the logical change needs. Rewriting a whole day's partition to correct one row is a granularity mismatch measured by the partition-to-row ratio. Example: a day partition of 5 GB rewritten to fix one 200-byte row writes 5 GB ÷ 200 B = 25 million times more than the change. Idempotency is bought with I/O, and the price is the partition size.

Determinism

The supporting discipline: ban nondeterminism from task bodies (determinism; the run's logical time is an input, never an ambient read). Idempotency without determinism is worthless: a retry that overwrites the partition with different correct-looking data is arguably worse than duplicates, because nothing detects it.

Completion verification

Exit code 0 is the task's claim about itself, produced by the same code that may have failed. A task that catches an exception and returns 0, or that writes half its output and exits cleanly after an empty final batch, reports success exactly like one that worked. The orchestrator has no independent channel. The responses, from weakest to strongest:

1. Marker files. The producer writes _SUCCESS after the data. It is still a self-report. But it is written after the data landed, in the same store, so it is at least weakly ordered with respect to the output. It is cheap and nearly universal, and a crash between data and marker defeats it.
2. Assertion on output. A separate step checks row counts, null rates and key uniqueness. This is independent evidence: data-quality checks used as a gate rather than a monitor.
3. Write-audit-publish (WAP). Write to a staging location, run the assertions against it, then swap it into the visible name with one pointer compare-and-swap. The visible transition is atomic even though the work was not, and orchestration inherits that atomicity from the storage below. It costs a staging copy plus the assertions, and most pipelines skip it because the cost is visible and the failure it prevents is not.

Retry safety requires re-execution to converge, which requires an addressable output and a deterministic body; exit code 0 is the task's opinion of itself, and only an independent assertion on the output is evidence.