Skip to content

Backfill and replay

Re-running a pipeline for past periods, to fill a new table with history, to correct data after a bug, or to apply changed logic, without disturbing the current runs or the people reading the results.

Before reading

Deduplication and merge · dbt

Why it matters

Every platform eventually needs to recompute the past: a bug is found in a metric, a new column must be filled for last year, a source resends a month of corrected data. A pipeline that can only process "today" turns each of these into a one-off project. A pipeline designed for backfills turns them into a routine run with different dates.

How it works

Two properties make backfills safe.

Idempotency: running the same period twice gives the same result. A model that appends new rows on each run doubles the data when re-run; a model that replaces a period, or merges on a key, does not.

Partition scoping: each run processes a defined period, such as one day, and writes only that period's output. The period is a parameter of the run, not "now". A backfill is then a series of ordinary runs for past periods, which can be split, parallelised and retried one at a time.

flowchart LR
  BUG[Bug fixed in<br/>discount logic] --> PLAN[Backfill plan:<br/>1 Aug to 28 Oct, 89 days]
  PLAN --> R1[Run 1 Aug]
  PLAN --> R2[Run 2 Aug]
  PLAN --> RN[... Run 28 Oct]
  R1 --> AUD[Audit: compare<br/>old and new]
  R2 --> AUD
  RN --> AUD
  AUD -->|accepted| PUB[Publish]

The data has to come from somewhere. Replaying from the raw layer is the normal case, which is why raw data is kept as delivered: the corrected logic runs over the same raw rows. Replaying from Kafka works only within the topic's retention, usually days, and means resetting a consumer group's offsets. Reloading from the source is the last resort, for data that never reached raw or was wrong in raw.

Downstream tables must follow. Rebuilding clean_orders for three months means fct_daily_sales and anything else built from it must be rebuilt for the same period, in dependency order. Tools that know the dependency graph, such as dbt or an orchestrator, can run a backfill for a model and everything downstream.

Large backfills compete with normal work. Running three months in one query can exhaust a cluster; running 89 one-day runs at once has the same effect. Backfills are usually run in batches, on separate compute or outside busy hours, with a budget estimated from a single day's run first.

Consumers notice backfills. Numbers for past periods change, sometimes by a lot. Write the backfilled data unseen, compare it with the old version, and publish it once the differences are understood, using the write-audit-publish pattern. Tell consumers which periods changed and why.

Some changes do not need a backfill. A new column that is only meaningful from now on can start today, if that is documented. Backfilling is a decision about cost and value, not a reflex.

Key terms

  • Backfill: running a pipeline for past periods.
  • Replay: reprocessing data from the raw layer or from a log.
  • Idempotent model: a model that gives the same result when re-run for the same period.
  • Partition-scoped run: a run that processes and writes exactly one period.
  • Run parameter: the period a run processes, passed in instead of taken from the clock.

Example

A bug in the discount logic of fct_daily_sales has overstated revenue since 1 August. After the fix, the shop rebuilds the period up to yesterday.

fct_daily_sales is a dbt microbatch model (incremental_strategy='microbatch', event_time='sales_date', batch_size='day', begin='2026-01-01'), so each day is one batch and the period is passed in. Its inputs, clean_orders and clean_order_items, set event_time='ordered_at', so each batch reads only its own day; without event_time on upstream models, every batch would scan them completely:

dbt run --select fct_daily_sales+ \
  --event-time-start "2026-08-01" --event-time-end "2026-10-29"

The plus sign includes everything downstream of fct_daily_sales. One day was run first and took four minutes; the 89 days ran overnight, ten batches at a time, with concurrent batches enabled and ten threads. The run used a separate audit target (schema audit), so the new figures were written beside the published ones, compared with them (revenue 2.1% lower, as expected from the bug), and then swapped in, with a note to finance.

Pitfalls

  • Models that append. A backfill doubles their data; make them replace or merge by period or key.
  • Logic that reads the clock. A run for August that uses current_date produces August data with today's rules or today's filter.
  • Forgetting downstream tables. The fixed table is right and the tables built from it still show the bug.
  • Raw data already expired. Check retention before promising a backfill.
  • Silent changes to the past. Consumers lose trust when last quarter's figures change without notice.

Learn more

  • dbt documentation, microbatch incremental models: https://docs.getdbt.com/docs/build/incremental-microbatch
  • Maxime Beauchemin, Functional Data Engineering: https://maximebeauchemin.medium.com/functional-data-engineering-a-modern-paradigm-for-batch-data-processing-2327ec32c42a

Orchestration · Write-audit-publish · Late and out-of-order data · Cost and FinOps