Backfill

What is a backfill?

A backfill is the rerun of a data pipeline over a historical period. The pipeline normally processes today's data, or only the rows that changed since the last run. A backfill points that same logic at the past: last week, last month, the previous three years, or the exact period affected by a bug.

Example: you launch a sales pipeline on 1 July 2026. From that day forward, it loads yesterday's orders every night. The business still wants three years of history in the dashboard. Running the pipeline over 2023, 2024, 2025, and the first half of 2026 is the backfill.

A backfill is not just a big import. A good backfill uses the same business logic as the regular pipeline, so historical data and future data are shaped in the same way.

When do you need a backfill?

• A new pipeline goes live. The daily job starts now, but the warehouse needs older history.

• A new column or calculation is added. Future rows get the value automatically. Old rows need to be recalculated.

• A bug is fixed. If two weeks of revenue used the wrong exchange rate, those two weeks must be processed again.

• A source was unavailable. Once the missing files or API responses arrive, the skipped window needs to be filled.

• A model changes grain. If a table moves from order-level to order-line-level, the historical data must be rebuilt at the new grain.

How a backfill works

Most backfills run the pipeline in batches. A three-year history might be processed by day, week, month, or partition. Smaller batches are easier to retry and easier on the source system.

The pipeline must be parameterised. Code that always reads today is hard to backfill. Code that accepts a start date and end date can run for any period. Apache Airflow calls this creating runs for past dates of a DAG. dbt incremental models can be rebuilt with a full refresh, and dbt's microbatch strategy can split large time-series work into time-based batches.

The safest backfill is boring: choose a period, run it, validate counts and totals, move to the next period. The faster version runs several periods in parallel, but only if the source and target can handle it.

Why idempotency matters

A backfill almost always touches data that might already exist. It may also fail halfway and need to be restarted. That makes idempotency essential: running the same period twice should leave the target in the same state as running it once.

Common patterns:

Partition overwrite
Replace the whole day, month, or partition instead of appending to it. If March is backfilled twice, March is still present once.

Merge by key
Use a stable business key or event key. Existing rows are updated, new rows are inserted, and deleted rows are handled deliberately.

Staging then swap
Build the backfilled period in a temporary table, validate it, then swap or replace the target slice in one controlled step.

Without one of these patterns, a backfill creates duplicate rows, partial aggregates, or mismatched history.

Backfill versus full refresh

A full refresh rebuilds the whole target from scratch. A backfill usually rebuilds a selected historical range. The two can overlap: if an incremental model's logic changes everywhere, a full refresh may be the cleanest backfill. If only two weeks are wrong, rebuilding the full table wastes time and money.

Choose the smallest range that fixes the problem, unless the model logic changed in a way that affects all history.

What to watch out for with backfills

Source-system load
A backfill can read years of data in one run. That may stress an operational database or hit API rate limits. Schedule heavy backfills outside business hours and throttle them when needed.

Overlap with scheduled jobs
If the normal daily run and the backfill write the same partitions at the same time, one can overwrite or duplicate the other. Pause the schedule or isolate the target range.

Historical business rules
Today's logic may not match the past. Tax rates, product categories, currencies, and organisation structures change. Decide whether the backfill should use today's rules or the rules that were valid at the time.

Validation before and after
Record row counts, totals, date ranges, and sample records. A backfill that silently changes history is worse than no backfill.

Cost
Backfills can burn compute quickly in a warehouse or lakehouse. Estimate the range, batch size, and retry cost before launching a large one.