What is a backfill? Rerunning the past without doubling your rows

You fixed the bug. The past is still wrong. The deploy only changes what the pipeline builds from today, and every day between the first bad run and now still holds the old numbers. One page on scoping a backfill, running it safely, and the three ways it doubles your rows. The print-ready A4 PDF is at the bottom.

What it is

• A backfill recomputes past periods of a table.
• The trigger is a bug fix, a new column, or late data.
• It is not a rerun: a rerun is one period, a backfill is every affected one.

Before you start

• Measure the blast radius: from the first bad day to today.
• Freeze the code: one version for all days, or you backfill twice.
• Tell downstream: dashboards will move, and people should know why.

Run it right

• Per partition: one day at a time, delete then insert.
• Oldest first, so late data lands in order.
• Idempotent: run it twice, get the same rows.

Airflow / dbt

• airflow backfill -s start -e end schedules one run per interval.
• dbt --full-refresh rebuilds incremental models from zero.
• Cap max_active_runs, or the warehouse melts.

The cost

• 6 months x 2 TB: price it before you launch it.
• Use an off-peak window: nights, weekends.
• Sample one day, verify it, then run the rest.

One partition, safely, forever

-- one day of the blast radius, oldest first
DELETE FROM fct_orders
 WHERE order_date = '2026-03-14';

INSERT INTO fct_orders
SELECT ... FROM stg_orders
 WHERE order_date = '2026-03-14';

-- run it twice: same rows, never double rows

Pass the day in as a parameter and loop from the first bad day to today. Never compute the date with CURRENT_DATE inside the query: March rows would be built as if it were today.

Gotchas

• Append-only sink: every rerun doubles the rows.
• today() in SQL: past days get today's date.
• Downstream tables need the rerun too.

The trap: the three ways a backfill lies

The quiz

Which one is safe to run twice?

A) INSERT INTO fct SELECT ...
   WHERE day = :d

B) DELETE FROM fct WHERE day = :d;
   INSERT INTO fct SELECT ...
   WHERE day = :d

B. A appends a second copy of the day on every rerun. B rebuilds the partition, so running it twice leaves the same rows.

Interview phrasing worth memorizing: a backfill is a rerun of every affected partition with frozen code, and it is only safe if the job is idempotent.