data-pipeline-idempotency

Use one of two safe write patterns: overwrite-by-partition, where the job replaces an entire partition such as a date wholesale, or merge/upsert on a natural key with a deterministic tie-break. Then verify with a double-run test that running the job twice yields identical output.

Store the high-water mark in a state table updated in the same transaction as the load itself, so crashes cannot skip or duplicate batches. Subtract a late-arrival lag, such as one hour, and rely on merge semantics so overlapping reprocessing is harmless.

Wall-clock time makes results depend on when the job runs, so re-runs and backfills produce different output than the original run. Always derive the processing date from the scheduler's logical or execution date instead.

Run the exact same code path as the daily job, parameterized by date range, with bounded parallelism to avoid overloading the warehouse. Finish with a reconciliation query comparing counts and totals per day against the source as the exit criterion.

Side effects like emails, webhooks, and counters fire again on every re-run unless made idempotent. Use idempotency keys on external calls, a sent-log checked before sending, or move side effects downstream of the data write.

Genuinely one-off exploratory transforms do not require idempotency guarantees. However, the moment such a job gets a schedule or trigger, retries and re-runs become inevitable and the idempotency workflow applies.