how to backfill dbt models for a date range

TL;DR

Make your incremental models accept a date range via dbt vars, then run the range in small slices instead of one giant job. One big run for a year of history will time out or blow up the warehouse, ten small runs will not. Keep each slice idempotent so a failed day can be rerun alone without double-counting.

how to backfill dbt models for a date range

Use this when

• you need to load historical data into a new incremental model
• a source backfill means dbt must reprocess past dates
• a bug corrupted a date range and you must rebuild it

Not for this skill when

• the model is a full table rebuild, just run it once normally
• you need to backfill an Airflow DAG, that is a different skill
• the date range is a single day, run it normally

Steps

1. Define the date range as vars with sane defaults so normal runs keep working untouched:

# dbt_project.yml
vars:
  start_date: '2024-01-01'
  end_date: '2024-01-31'

Expected output: every model in the project can read the range with var(). Defaults mean developers who ignore the vars get normal behavior.

1. Filter the incremental model on the vars so each run processes exactly one slice:

-- models/marts/fct_orders.sql
SELECT *
FROM {{ source('raw', 'orders') }}
WHERE order_date >= '{{ var("start_date") }}'::date
  AND order_date < '{{ var("end_date") }}'::date
{% if is_incremental() %}
  AND order_date >= (SELECT MAX(order_date) FROM {{ this }})
{% endif %}

Expected output: the model only scans the requested window. The is_incremental guard keeps daily runs incremental while backfill runs stay bounded.

1. Run the range in small slices from the command line, overriding the vars per slice:

dbt run --select fct_orders --vars '{start_date: "2024-01-01", end_date: "2024-02-01"}'
dbt run --select fct_orders --vars '{start_date: "2024-02-01", end_date: "2024-03-01"}'
dbt run --select fct_orders --vars '{start_date: "2024-03-01", end_date: "2024-04-01"}'

Expected output: three monthly slices build in sequence. Each run is small enough to finish reliably, and a failed slice reruns alone.

1. Verify row counts per slice before moving on, since silent gaps are the classic backfill failure:

SELECT DATE_TRUNC('month', order_date) AS m, COUNT(*)
FROM analytics.fct_orders
GROUP BY 1 ORDER BY 1;

Expected output: one row per month with plausible counts. A gap means a slice failed silently, rerun just that slice with its vars.

1. Run once more with defaults after the backfill so normal incremental behavior resumes:

dbt run --select fct_orders

Expected output: the model runs with default vars and normal incremental logic. Backfill mode is over and tomorrow's scheduled run behaves normally.

Variant phrasings

dbt backfill incremental model historical data

The vars pattern in steps 1 and 2 is the standard approach. Size slices so each run finishes in minutes, not hours, and verify as you go.

dbt rerun model for specific dates

Override the vars on the command line for a one-off rebuild. No project changes needed when it is just one slice.

dbt backfill without full refresh

Incremental plus date vars avoids the full table rebuild. Full refresh on a huge table is the slow path, slicing is the controlled one.

Why it happens

Incremental models only process new data by design, so history from before the model existed simply is not there. Backfilling means convincing the incremental logic to accept old dates, which the var-filtered WHERE clause does cleanly. Small slices keep each transaction bounded, which is why the loop beats one giant run that risks timeouts and partial writes.

Edge cases

• Overlapping slices double-count unless the model deletes or merges on the date key. Make the logic idempotent before you start.
• Late-arriving data inside a backfilled range needs a second pass. Freshness checks catch it after the fact.
• Unique tests on the full table can fail mid-backfill until all slices land. Run tests after the last slice, not during.
• Very old source data may be archived or purged. Confirm the raw history exists before promising anyone a backfill.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_pv7w6Kzy2sVnCO0NqHQKUw