writing-idempotent-transformationslisted

# Writing Idempotent Transformations ## When to use - A task may be retried (orchestrator retries, manual re-runs, replays). - A backfill must not change already-correct numbers or create duplicates. - You are writing an incremental load, upsert, or partition refresh. - Do NOT use for one-off exploratory queries with no persisted side effects. ## Why it matters Failures are normal. Retries, backfills, and replays run the same logic again. If a job is idempotent, running it once or five times yields the same final state — so recovery is safe and boring. If not, retries duplicate rows and backfills silently change history. This is the single most reported source of data incidents. ## Workflow ``` - [ ] Define the unit of work and its natural key - [ ] Make writes overwrite-by-key or overwrite-by-partition (never blind append) - [ ] Remove dependence on wall-clock time / autoincrement / random order - [ ] Verify: running twice == running once (row counts and checksums match) ``` 1. **Pick a deterministic key** for each output row (business key, or a hash of the identifying columns). 2. **Replace `INSERT`-append with `MERGE`/upsert** keyed on that key, or with a full **partition overwrite** for the window being processed. 3. **Remove non-determinism**: don't key on `now()`, sequence values, or unordered `row_number()`; derive timestamps from the data/event, not the run. 4. **Test it**: run the transform twice on the same input and assert identical output (count