Backfill
A backfill is the process of re-running a pipeline over a range of historical data—typically to populate a new table, fix a bug, or apply changed logic to past periods.
Overview
A backfill re-processes historical data through a pipeline, either because the pipeline is new and needs to populate history, or because the transformation logic changed and past results need to be recomputed to stay consistent with the new logic. If a metric definition changes—say, how "active user" is calculated—a backfill re-runs that calculation over every historical day so the whole time series reflects the new definition consistently, rather than having a discontinuity at the point the code changed.
When backfills happen
- New pipeline, existing history: a table is added to track something that already has historical source data, and the initial load needs to cover all of it, not just data going forward.
- Bug fixes: a transformation had incorrect logic for some period, and the fix needs to be applied retroactively to correct past output.
- Schema or logic changes: a new column or business rule needs to be computed for historical partitions too, not just new ones.
- Recovery from an outage: a pipeline failed to run for several days, and the missed partitions need to be filled in once it's fixed.
Backfilling incremental models
Incremental pipelines are built to process only new data on each run, which makes backfills trickier than for a pipeline that always reprocesses everything: the incremental logic has to be temporarily overridden to target a historical date range.
dbt, for example, supports this via the --full-refresh flag (rebuild the whole table from scratch) or by looping over date ranges with --vars to target specific partitions:
Copy code
dbt run --select fct_orders --full-refresh
Backfilling with DuckDB
A common backfill pattern is looping over a date range and overwriting each partition idempotently, so the backfill is safe to re-run if it's interrupted:
Copy code
-- backfill a single historical partition
DELETE FROM fct_orders WHERE order_date = '2026-06-01';
INSERT INTO fct_orders
SELECT *
FROM read_parquet('s3://bucket/raw_orders/dt=2026-06-01/*.parquet');
For a full date-range backfill, this statement is typically wrapped in a loop (in Python, a shell script, or an orchestrator) that iterates over each date and applies the same overwrite. Because DuckDB starts up instantly and can query files directly from object storage with read_parquet(), it's well suited to running many small backfill jobs in parallel rather than requiring a long-lived cluster.
Related terms
An incremental model is a transformation that processes only new or changed source data on each run and merges it into an existing table, instead of rebuilding the whole table from scratch every time.
Kappa architecture →Kappa architecture is a data processing design that uses a single stream-processing pipeline for both real-time and historical data, avoiding the separate batch layer required by Lambda architecture.
Data transformation →Data transformation is the process of converting data from its raw, source format into a structured form suited to analysis — through operations like cleaning, joining, aggregating, and reshaping.
Slowly changing dimensions (SCD) →Slowly changing dimensions (SCD) are techniques for handling changes to dimension attributes over time — such as a customer moving to a new region — while preserving or overwriting historical values as needed.
Data orchestration →Data orchestration is the automated coordination, scheduling, and monitoring of interdependent data tasks—such as extracts, transformations, and loads—so they run in the correct order and recover cleanly from failure.
Batch processing →Batch processing is the execution of data processing jobs on accumulated groups (batches) of records at scheduled intervals, rather than processing each record as it arrives.
FAQS
A regular run processes the newest data on a schedule. A backfill targets a specific historical range—often re-running the same logic that normally only looks at new data, but pointed at past partitions instead.
They often touch large volumes of historical data at once, can be expensive to compute, and if not idempotent can create duplicates or inconsistent state if interrupted partway through. Partition-level overwrites are the safest pattern.
