Backfills: Manifest-Driven Reruns and the Downstream Trap
Reprocess a date range from a backfill manifest, then rebuild the downstream aggregate for exactly the affected weeks so the mart stops lying.
Backfillable means parameterized
A job is backfillable when it accepts the date it is responsible for as a parameter and hardcodes nothing about "today". That single property is what lets a scheduler point the same code at last Tuesday. The moment a transformation calls the wall clock, or filters on a literal date someone typed during an incident, the job can only ever produce today's answer and a backfill is impossible without editing code.
You already know how to rebuild one partition: delete the date, reinsert the date, which is idempotent because the delete runs first. sql-l5-incremental-watermark-backfill taught that. This lesson starts one step past it.
The manifest drives the loop
A real backfill is not one date, it is a set of them, and the set lives in a table. A manifest is one row per date the scheduler was asked to rerun, and the job joins to it rather than repeating a statement per date:
DELETE FROM fct_daily_sales WHERE dt IN (SELECT run_date FROM backfill_manifest);
INSERT INTO fct_daily_sales (dt, store_id, sales, revenue_usd)
SELECT s.dt, s.store_id, COUNT(*), SUM(s.amount_usd)
FROM raw_sales s
JOIN backfill_manifest m ON m.run_date = s.dt
GROUP BY s.dt, s.store_id
Two statements handle five dates or five hundred. Nothing about the SQL changes when the manifest grows, which is the whole point: the dates are data, not code. Notice also that the manifest here holds the deploy window rather than the dates you believe broke. That is how incident tickets are actually written, and it is the safer instinct: rerun the range the bad code was live, and accept that some of those dates were fine.
- backfill_manifestone row per date to rerun
- delete partitionsonly the dates in the manifest
- insert from rawjoined to the manifest, not hardcoded
- recompute martthe weeks those dates fall in
The trap: the fact is repaired and the mart still lies
Here is the part that earns this lesson its slot. You rerun the fact table, every assertion passes, the numbers reconcile against raw. And the weekly mart built on top of that fact still holds the old totals, because nothing downstream knows it is stale. There is no error, no failed run, no alert. Somebody opens the dashboard next Monday and the revenue is wrong, four days after you closed the incident.
Anything derived from a partition you rewrote is stale until you rebuild it. That means the backfill script has one more step than people expect, and the step is bounded the same way: recompute exactly the weeks that contain a manifest date, not the whole mart. Cost is a design constraint. Rebuilding three broken days plus the two weeks they fall in is a minute of compute; a full-history recompute on every incident is how a backfill turns into its own outage.
Cost, and why unbounded catchup blows up
The other half of the cost story is the scheduler. If you enable catchup on a daily pipeline whose start date is two years back, the scheduler will happily queue seven hundred runs and try to execute them with whatever parallelism it has. That is the classic self-inflicted outage: your own backfill saturates the warehouse and takes production down with it. Bound the range, and bound the concurrency.
Common mistake: repairing the fact and stopping there. The fact is the table you were paged about; the mart is the table the business actually reads.
Interview nuance: "your transformation logic had a bug for two weeks, walk me through the fix" is a standard scenario, and the expected answer has an order: build a manifest of the affected dates, rerun the job per date with a partition-scoped idempotent write, then recompute the downstream aggregates for the affected windows, all bounded to the broken range. Candidates who stop after the fact table get the follow-up "and what about everything built on top of it", which is a question you would rather answer before it is asked.
On a real platform this differs. Airflow 3 made backfills scheduler-managed and first class, so the manifest is the set of logical dates you hand the scheduler rather than a table you maintain, and dbt would express the downstream recompute as a selector over the model graph (
dbt run --select fct_daily_sales+) because the lineage already knows what depends on what. The reasoning is identical: bounded set of partitions, idempotent write, downstream refreshed.
CREATE TABLE fct_daily_sales (
dt TEXT,
store_id INTEGER,
sales INTEGER,
revenue_usd REAL -- already repaired by the fact backfill; the mart has not caught up
);
INSERT INTO fct_daily_sales (dt, store_id, sales, revenue_usd) VALUES
('2026-02-08', 1, 1, 120.00),
('2026-02-08', 2, 1, 95.50),
('2026-02-09', 1, 1, 210.00),
('2026-02-09', 2, 2, 45.50),
('2026-02-10', 1, 2, 135.00),
('2026-02-10', 2, 1, 140.00),
('2026-02-11', 1, 1, 260.50),
('2026-02-11', 2, 2, 57.75),
('2026-02-12', 1, 2, 90.00),
('2026-02-12', 2, 1, 175.75),
('2026-02-13', 1, 1, 199.00),
('2026-02-13', 2, 1, 120.40),
('2026-02-14', 1, 2, 388.25),
('2026-02-14', 2, 1, 45.60);
CREATE TABLE backfill_manifest (
run_date TEXT
);
INSERT INTO backfill_manifest (run_date) VALUES
('2026-02-08'),
('2026-02-09'),
('2026-02-10'),
('2026-02-11'),
('2026-02-12');
CREATE TABLE mart_weekly_sales (
week_start TEXT, -- the Monday of the ISO week
revenue_usd REAL -- still holds the pre-backfill numbers for the weeks the manifest touches
);
INSERT INTO mart_weekly_sales (week_start, revenue_usd) VALUES
('2026-01-26', 980.00),
('2026-02-02', 215.50),
('2026-02-09', 2021.75),
('2026-02-16', 1450.00);-- The trap, live. The fact table has ALREADY been repaired by the backfill. This is what the
-- weekly mart built on top of it still reports, and nothing anywhere raised an error about it.
SELECT w.week_start,
ROUND(w.revenue_usd, 2) AS mart_revenue,
(SELECT ROUND(SUM(f.revenue_usd), 2) FROM fct_daily_sales f
WHERE date(f.dt, 'weekday 0', '-6 days') = w.week_start) AS recomputed_revenue,
CASE
WHEN (SELECT COUNT(*) FROM fct_daily_sales f
WHERE date(f.dt, 'weekday 0', '-6 days') = w.week_start) = 0
THEN 'no fact rows for this week'
WHEN ROUND(w.revenue_usd, 2) <> (SELECT ROUND(SUM(f.revenue_usd), 2) FROM fct_daily_sales f
WHERE date(f.dt, 'weekday 0', '-6 days') = w.week_start)
THEN 'STALE: still the pre-backfill total'
ELSE 'agrees with the repaired fact'
END AS mart_status
FROM mart_weekly_sales w
ORDER BY w.week_start;Apply
Your turn
The task this lesson builds to.
Write a script that rebuilds fct_daily_sales for every date in backfill_manifest from raw_sales, leaves all other dates untouched, and changes nothing when it is rerun.
raw_sales(sale_id, dt, store_id, amount_usd) is the truth, including the negative refund rows the buggy job excluded. fct_daily_sales(dt, store_id, sales, revenue_usd) is at the date and store grain, where sales is the row count and revenue_usd is the summed amount. Drive both the delete and the insert from backfill_manifest rather than naming dates in your SQL, so the same script still works when the manifest grows.
2 hints and 7 automated checks are waiting in the workspace.
Practice
Make it stick
A second problem on the same idea, plus 3 bonus drills.
Write a script that recomputes mart_weekly_sales for exactly the weeks containing the backfill_manifest dates, leaving other weeks untouched, and that is safe to rerun.
fct_daily_sales has already been repaired, so it is your source of truth. mart_weekly_sales(week_start, revenue_usd) still holds the pre-backfill totals, and week_start is the Monday of the ISO week, which SQLite gives you as date(dt, 'weekday 0', '-6 days'). The manifest straddles two weeks, so the affected set is not one row, and the weeks it does not touch must keep their current values exactly.
1 hint and 5 automated checks are waiting in the workspace.