Idempotent Pipeline

The single property that makes data engineering tractable, because everything else depends on it: retries become safe, backfills become routine, and a failed run can simply be re-executed rather than investigated for partial effects.

How it is achieved: write by overwrite-partition rather than append, so a re-run replaces the window's output instead of adding to it. Derive every input from the execution window, never from wall-clock time. Make loads deterministic — the same input produces the same output, with no dependence on run order or on the target's prior contents. Where an upsert is required, key it so a repeated row updates rather than duplicates.

The anti-patterns to recognise: INSERT without a delete of the target window; transformations using CURRENT_DATE or NOW(); sequences and auto-increment IDs that differ between runs; and any step whose result depends on how many times it has run.

The check that catches most violations in seconds: run the task twice and diff the output. If the two differ, the pipeline is not idempotent, and every retry it has ever performed is suspect.