Backfills and Pitfalls
Rewriting history without breaking the present
Backfilling is re-running history safely: partition-scoped, idempotent, and validated against new vs old. The pitfalls — watermarks skidding, non-idempotency, interleaved runs — turn 'backfill day' into data incidents.
What you will learn
- Scope a backfill to partitions, not whole tables
- Make backfills idempotent and isolated from live runs
- Validate after backfill with before/after comparisons
What is this?
A backfill re-runs transformation over past data to rebuild outputs after a bug or schema change. Safe backfills are partition-scoped, idempotent, run with the same DAG code path, and validated against both the old output and source expectations — not a 'bigger delete' or a hand-tuned re-run.
Why do you need it?
- Backfills are the highest-risk table operations most teams perform.
- A careless backfill corrupts metrics for a whole quarter before anyone looks.
- The same discipline makes every replay (recovery, retries) boringly safe.
Mental model
Re-printing old pages of a ledger instead of rewriting the whole book
Partition-scoped backfill
You authorize reprints for the damaged pages (partitions) only, print them to identical format (same DAG code), and check a few totals against the accountant's source record before calling it done.
How it works
- Scope: choose the exact partitions (days/months) changed by the bug or shipper.
- Code path: use the SAME DAG/task with a backfill interval, not a separate 'repair' script.
- Idempotency: delete-then-insert within the partition so re-runs are safe.
- Isolation: run out-of-hours or with a lock so live reads don't see mixed state.
- Validation: compare old vs new totals and spot-check against source before publishing.
Backfill flow: scope, replay, validate.
Examples
A safe backfill plan in prose
# SAFE1. Freeze consumers for window 2026-03-10..14 (or accept short staleness).2. dbt run --select fct_order_lines --full-refresh --vars '{start: 2026-03-10, end: 2026-03-14}' # same model, partition-bounded.3. Validate: compare sums of new vs old per day; diff = expected delta only.4. Re-open reads. # UNSAFE"Just re-run everything and fix the numbers after."Scope first, replay with the real code path, validate, then publish.
In production
- A deduced bug in a dimension SCD makes the team backfill the 3 affected months and validate per-month totals.
- A format change (json->parquet) triggers a raw-layer backfill that feeds downstream automatically.
- A post-incident review mandates 'backfill drill' so the next one is rehearsed.
Best practices
- Scope to the minimum partitions touched by root cause.
- Run backfills through the scheduled code path with parameters, not ad-hoc SQL at midnight.
- Freeze or mark consumers of the window; mixed-state reads are the classic silent bug.
- Write the before/after diff as a test you run BEFORE publishing.
Common mistakes
- Cloud backup of a whole table for a 3-day bug, doubling compute and risking collateral damage.
- Non-idempotent backfill that multiplies rows on the second run.
- Backfilling while live runs interleave, so the window mixes versions permanently.
Debugging tips
- After a backfill, spot-check the boundary partitions (first/last day) hardest to get right.
- A 'numbers changed but shouldn't have' diff usually means the bug was wider than the scoped window.
- Log old vs new per partition so the diff review is a table, not a feeling.
Interview checkpoint
Hands-on lab
- Create a bug, scope the affected date partitions
- Backfill only those partitions idempotently
- Validate old vs new totals before 'publishing'
Scoped, Idempotent, Validated Backfill
40 min · advanced
Quick quiz
The first sentence of any backfill is…
Backfilling with a separate ad-hoc 'repair' script instead of the scheduled code path risks launching different logic.