Plan a backfill

Lay out a project, plan code changes and data restatements, get SQL for your warehouse, and cap what a change may cost.

Lay out a project

A project is a directory. The examples on this page use three models: raw events, a daily rollup, and a user dimension.

File	Required	What it holds
`*.sql`	yes	One model per file; the file name is the model name. Plain SQL: models refer to each other by name.
`sources.json`	no	Columns of tables you read but don't build, e.g. `{"raw_events": ["dt", "user_id", "n", "last_seen"]}`. Without it, unresolved columns are assumed changed.
`materializations.json`	no	`table`, `view` or `incremental` per model; `table` by default. Only incremental models can be partition-scoped.
`incremental.json`	no	How incremental models are partitioned and rewritten (below).

models/

$ cat models/events.sql
select dt, user_id, n, last_seen from raw_events
$ cat models/daily_rollup.sql
select dt, count(*) as users, sum(n) as n from events group by dt
$ cat models/user_dim.sql
select user_id, max(last_seen) as last_seen from events group by user_id

models/materializations.json

{"events": "incremental", "daily_rollup": "incremental"}

Partitions and how they're rewritten

grain says how partition keys are spelled (Hour, Day, Week, Month, or an integer step). replace says what a partition rebuild does to the table: insert_overwrite, delete_insert, merge, replace_where or full_refresh. There is no default: warehouses disagree about what it should be.

models/incremental.json

{"models": {
  "events":       {"grain": "Day",
                   "replace": {"strategy": "insert_overwrite", "partition_column": "dt", "partition_type": "date"}},
  "daily_rollup": {"grain": "Day",
                   "replace": {"strategy": "merge", "unique_key": ["dt"]}}
}}

Lookback windows (a 7-day rolling model reads 7 upstream days) and every key are in the configuration reference.

Plan code changes

freshet plan compares every model with the fingerprints in your state. Fingerprints come from the parsed SQL, not the text, so reformatting, re-casing or commenting a model changes nothing:

a cosmetic edit rebuilds nothing

$ printf -- '-- tidied up\nSELECT dt,\n       user_id,\n       n,\n       last_seen\nFROM raw_events\n' > models/events.sql
$ freshet plan --project models --state state.json
nothing to backfill — state is up to date.

A real change rebuilds the model and everything that reads it, with the columns that carry it:

a real change

$ echo 'select dt, user_id, n * 2 as n, last_seen from raw_events' > models/events.sql
$ freshet plan --project models --state state.json
backfill plan (3 model(s), topological order):
  events                   full                         (directly changed)
  daily_rollup             full                         (downstream of events via [dt,n,users])
  user_dim                 full                         (downstream of events via [last_seen,user_id])
rebuild work: 3.000 CU planned, 0.000 CU (0%) avoided — rebuilding everything downstream of the change whole would be 3.000 CU

A changed model's definition changed, so every partition of it is stale: code changes rebuild whole models. In 0.1.0, plan also treats a code change as touching every column of the changed model, so every downstream reader rebuilds. Column-by-column pruning is in freshet explain (below) and the library API.

Restate data

When the code is fine but the data for some partitions was wrong or late, restate plans only those partitions, and scopes every incremental model downstream to the same keys:

restate one day of events

$ freshet restate --project models --model events --partitions 2026-06-20
backfill plan (3 model(s), topological order):
  events                   partitions[2026-06-20]       (directly changed)
                           └─ writes: insert_overwrite over 1 partition(s) on `dt`
  daily_rollup             partitions[2026-06-20]       (downstream of events via [dt,n,users])
                           └─ writes: merge over 1 partition(s) on key [dt]
  user_dim                 full                         (downstream of events via [last_seen,user_id])
rebuild work: 1.200 CU planned, 1.800 CU (60%) avoided — rebuilding everything downstream of the change whole would be 3.000 CU

To make a restatement survive a failed run, record it in state first. It is then owed: every plan includes it until you settle it after the run succeeds.

owe, run, settle

$ freshet restate --project models --model events --partitions 2026-06-20 --state state.json
recorded as owed in state.json — the next `freshet plan` re-plans it until you settle it with:
  freshet restate --project models --model events --partitions ... --state state.json --commit
# … run the plan …
$ freshet restate --project models --model events --partitions 2026-06-20 --state state.json --commit
settled 3 model(s) in the ledger -> state.json

Get SQL for your warehouse

Add --sql with postgres, snowflake, bigquery, databricks or redshift, and each model carries the statements that rewrite exactly the planned partitions. Freshet never runs them.

--sql postgres (excerpt)

$ freshet restate --project models --model events --partitions 2026-06-20 --sql postgres
…
-- events (postgres)
WITH freshet_deleted AS (
  DELETE FROM events WHERE dt IN (DATE '2026-06-20')
)
INSERT INTO events (dt, user_id, n, last_seen)
SELECT dt, user_id, n, last_seen FROM (
select dt, user_id, n, last_seen from raw_events
) AS freshet_model
WHERE freshet_model.dt IN (DATE '2026-06-20');

-- daily_rollup (postgres)
MERGE INTO daily_rollup AS freshet_tgt
USING (
select dt, count(*) as users, sum(n) as n from events group by dt
) AS freshet_src
ON freshet_tgt.dt = freshet_src.dt
WHEN MATCHED THEN UPDATE SET users = freshet_src.users, n = freshet_src.n
WHEN NOT MATCHED THEN INSERT (dt, users, n) VALUES (freshet_src.dt, freshet_src.users, freshet_src.n);

--schema analytics qualifies every table; --recreate m1,m2 rebuilds models someone altered by hand. A model whose definition changed is always recreated rather than written into. Per-dialect details are in the reference.

Read it from a program

--json prints the plan in the wire format every Freshet surface shares. With --sql, each model also carries sql.statements and sql.atomic.

the shape, via jq

$ freshet restate --project models --model events --partitions 2026-06-20 --sql postgres --json \
    | jq -c '.models[] | {name, unit, statements: (.sql.statements | length)}'
{"name":"events","unit":{"partitions":["2026-06-20"]},"statements":1}
{"name":"daily_rollup","unit":{"partitions":["2026-06-20"]},"statements":1}
{"name":"user_dim","unit":"full_model","statements":1}

Cap what a change may cost

--budget N caps a plan at N compute units. Over budget, Freshet still prints the plan (so you can see what to scope down), exits 1, and never commits. It is a guardrail you set, typically in CI.

over budget

$ freshet plan --project models --state state.json --budget 2
…
budget: OVER by 1.000 CU (1000 mCU) — used 3.000 CU (3000 mCU) of 2.000 CU (2000 mCU)
error: plan needs 3.000 CU (3000 mCU) but the budget is 2.000 CU (2000 mCU) — scope the change down, restate fewer partitions, or raise --budget
$ echo $?
1

Coming from dbt

Point freshet explain at a dbt project that has been built, and at the production manifest it's compared with. It reads target/, never runs dbt, and reports what Freshet would have skipped, column by column:

freshet explain (excerpt)

$ freshet explain --against ./jaffle_shop --state ./prod-manifest
freshet explain: 9 dbt model(s), 9 built by dbt
  changed (2):
    customer_regions        new model
    stg_orders              sql — only [amount]
Freshet would rebuild 7 model(s):
    stg_orders              directly changed
    customer_regions        directly changed
    orders_enriched         downstream of stg_orders via [amount]
    …
  vs the last `dbt build` (9 model(s)): 2 fewer — 22.2% avoided
      it builds, Freshet does not: mart_customers, stg_customers

Exit codes

Code	Meaning
`0`	Success, including "nothing to backfill".
`1`	An error, or a plan refused by `--budget`.
`2`	A usage error.
`3`	Hosted state moved: another commit landed after this plan was computed. Re-plan and try again; nothing is broken.