Run the same input twice, change only the write policy
The lab loads one row for branch HZ-01 on 2026-02-01. Run the job once, then rerun it with the same input and switch between append, overwrite and merge-upsert. Watch the partition row count and SUM(balance) after each execution.
No rows yet. Run the job to load the partition.
No executions recorded yet.
What “idempotent” means precisely
Idempotency is a property of the write, not of the scheduler. Three conditions define it:
- Same logical input. The same source rows for the same business date, even if the execution timestamp or attempt number differs.
- Same target. The same partition, key or row set is addressed each time.
- Same business state. After the second run, the target holds one correct version of the result — not two.
Both executions can return success while the second one appends a duplicate row. Check the target state, not the run status.
Why reruns create duplicates
- Append writes. The job inserts a new row every time and never removes the previous result.
- Retry after a partial failure. The first attempt wrote some rows before failing; the retry writes them again.
- Overlapping backfills. Two runs process the same business date range at the same time.
- Non-deterministic input. The query reads
NOW(), a moving window or an unpinned source snapshot, so the “same” run is not the same input.
The failure is observable: one business date has more than one row in the target partition, and any measure summed from that partition is too high.
Write policies compared
| Policy | Second run with the same input | Trade-off |
|---|---|---|
INSERT APPEND | Two rows for the same business date; sums inflated | Simple and fast, but not idempotent |
OVERWRITE PARTITION | One row for the business date | Needs an atomic replace; briefly exposes an empty partition if it is not atomic |
MERGE / UPSERT | One row for the business date, updated if the value changed | Requires a reliable unique key and correct update semantics |
Overwrite and merge both make the partition addressable by business date. The choice depends on the table format, the cost of a full partition rewrite and whether deletes must be propagated. In all cases the key that defines “the same row” must be explicit.
Retry, rerun and backfill are different scopes
The same run, another attempt
- Business date and target do not change.
- Should be safe even if the first attempt partially wrote.
- Depends on the write policy, not on the retry count.
Re-execute one existing business date
- Used for corrections and recovery.
- Must replace the previous result, not add to it.
- Keep the run history so the correction is auditable.
Re-execute a range of business dates
- Idempotency must hold per partition, not just for the range.
- Overlapping ranges must not write the same date twice.
- Consider concurrency limits and source read load.
“Just rerun it”
- Reruns an append job and doubles the partition.
- Treats a duplicate as a scheduler problem.
- Hides the failure until an aggregate is visibly wrong.
What SQL idempotency does not cover
Making the table write idempotent does not automatically make the whole pipeline idempotent. Side effects still run once per execution unless they are made idempotent too:
- Files written to object storage, and their partitions or manifests.
- Notifications, emails and webhook calls.
- Rows inserted into audit or event tables.
- External systems updated through an API.
For those, use a deterministic output path or an idempotency key. The task graph view of the pipeline is a separate concern — seedata lineage vs task dependency.
Edge cases and trade-offs
- Failure in the middle of an overwrite. If delete and insert are not in one transaction, a failed run can leave an empty or partial partition. Prefer an atomic replace or write to a staging table first.
- Corrected input. With overwrite or merge, a rerun with corrected input replaces the old value. With append, both versions remain and the sum is wrong.
- Late-arriving data. The business date of the row does not change; the target partition must be recomputed for that date.
- Unique constraints. A database unique key can reject duplicates, but it turns a silent duplication into a hard failure — useful, but not a substitute for an idempotent write.
- Cost. A full partition overwrite can be more expensive than a merge, while a merge needs a reliable key. Measure both against the real table format.