SQL, data modeling, data quality, EDA, visualization · insight-0048
Make every load and backfill idempotent so a rerun cannot double-count
- Evidence type
- documented fact
- Difficulty
- medium
- Roles
- Data analyst, ML engineer
- Industries
- cross-industry
Summary: situation, action, reason, boundary
Situation
A failed nightly job is rerun manually, or a month of history is backfilled after a logic fix.
Action
Design each load as "replace this partition" or "merge on a unique key", never plain append. Test by running the same job twice on the same input and asserting identical output row counts and totals.
Reason
Idempotent writes make retries and backfills safe, which is required because failures and logic changes are routine.
Boundary
Batch ETL, incremental models, feature tables and label tables.
Symptom or risk
Append-only loads insert the same rows again, so revenue for the rerun days doubles and nobody notices until finance reconciles.
Exceptions
Immutable event logs where appending with a unique event id and deduplicating downstream is the design.
Trade-offs
Merges and partition overwrites cost more compute than appends.
Pitfalls
A unique key with NULLs or duplicates in the source, which breaks merge semantics.
Lessons and concepts
Claims and their sources
Without a unique_key, dbt incremental models on most adapters append every returned row, so re-runs can duplicate data; with a unique_key existing rows are updated or replaced.