A refresh planner for dbt shaped warehouses that works out which partitions of which models are actually stale, then proves the result row for row identical to a full rebuild: 84.1% fewer rows written and 41.8% less wall clock on a 33 model warehouse.
- The nightly job rebuilds partitions where nothing changed. The solver propagates each changed source partition through the dependency graph using a declared partition map per edge, so only genuinely affected partitions are refreshed. Measured on the 120 day warehouse: 700 of 3,147 model partitions stale, 105,823 rows written instead of 667,402, 41.8% less wall clock.
- Late arriving data makes "refresh yesterday" quietly wrong. In the seeded load batch 41 of 48 changed source partitions are older than the batch day, the worst backdated 110 days. The plan reads the actual change log, so those partitions are in it, and the reason is printed next to each one.
- Nobody can tell whether the incremental result is correct.
refresh-planner proverebuilds every model into a shadow schema and compares both directions withEXCEPT ALL. Against the naive window rule it reports 2 of 31 models diverged, 84 partitions and 168 rows each; against the declared maps, 31 of 31 match row for row.
A warehouse team runs a 40 minute nightly rebuild because deciding what actually needs refreshing is harder than refreshing everything. The cost shows up twice. It shows up on the bill, since most of that compute recomputes partitions where no row changed. And it shows up on the SLA, because the exec dashboard cannot be ready before the whole graph finishes. As a representative scenario, a 40 minute nightly job on a mid sized Snowflake warehouse at roughly 4 credits an hour is about 2.7 credits a night, near 1,000 credits a year, and if 80% of that work rebuilds unchanged partitions then 800 of those credits bought nothing. The bigger cost is usually the SLA: an 06:00 dashboard that depends on the tail of a 40 minute job has no slack when the job runs long.
This repository plans the minimum sufficient refresh and then proves it. Every dependency edge declares how a parent partition maps onto the child's partitions: one to one, lagged by up to n days, a trailing n day window, a rollup from days into weeks or months, a fan out from an unpartitioned dimension, or a full read of all history. The solver walks the graph in topological order and unions those maps, so a return that backdates 45 days onto its original order partition lands where it belongs, and one changed input day of a rolling 28 day metric invalidates 28 output days rather than one. A critical path schedule then orders the work by the earliest SLA it feeds, and an equivalence prover rebuilds everything into a shadow schema and asserts row for row equality. It runs entirely on DuckDB with no cloud account. Snowflake and Power BI appear only as emission targets: the mart models get a Power BI incremental refresh policy whose window is derived from the same partition maps, and widened with a warning when the configured window would leave backdated rows unreprocessed, and the incremental models get Snowflake MERGE statements generated with sqlglot and parsed back before they ship.
The numbers in this README came from make bench and make demo on a 2 vCPU Linux container running DuckDB 1.5.5, against a seeded 120 day warehouse of 33 models over 10 sources and 544,255 source rows; raw output is in benchmark/results/. The planned refresh wrote 84.1% fewer rows and finished 41.8% faster than a full rebuild, planning included, and the plan itself took 12.2 ms. The prover caught the deliberately wrong window rule on 84 partitions of fct_rolling_28d_revenue and the 84 partitions of mart_exec_summary that inherited it, then confirmed equivalence once the declared map was used. Savings fall as more of the source changes and go negative above about 25% of source partitions changed, which is reported rather than hidden. The benchmark container was shared with other builds and variance was not controlled for, so treat single measurements as indicative and the repeated medians as the reliable figures.
flowchart TB
subgraph load[Load]
SRC[(10 partitioned sources<br/>DuckDB)]
CL[src_change_log<br/>partition, rows, lateness]
SRC -- every write records its partition --> CL
end
subgraph plan[Plan]
CAT[Model catalogue<br/>33 models, declared partition map per edge]
SOL[Staleness solver<br/>topological forward map union]
CL --> SOL
CAT --> SOL
SOL --> P[Plan: stale partitions + reason each]
end
subgraph sched[Schedule]
COST[(wh.model_cost<br/>measured seconds per partition)]
PRI[Critical path priority<br/>earliest SLA, then longest chain]
RUN[List schedule on N workers]
P --> PRI --> RUN
COST --> PRI
end
subgraph exec[Execute]
INC[DELETE stale partitions<br/>INSERT the same compiled SQL]
RUN --> INC --> WH[(target schema wh)]
end
subgraph verify[Verify]
SH[Full rebuild into shadow schema<br/>predicate set to TRUE]
CMP{EXCEPT ALL<br/>both directions}
WH --> CMP
SRC --> SH --> CMP
CMP -- equal --> OK[EQUIVALENT, exit 0]
CMP -- differs --> BAD[DIVERGED: model, partitions, why<br/>exit 1]
end
subgraph emit[Emit]
PBI[Power BI refresh policy<br/>window derived from the same maps]
SFK[Snowflake MERGE via sqlglot<br/>parsed back before it ships]
CAT --> PBI
CAT --> SFK
end
WH --> DASH[12 consumers with freshness SLAs]
RUN -.SLA slack per consumer.-> RISK[[SLA risk list]]
Failures are handled at three boundaries. Configuration is validated in Config.__post_init__ before anything touches the database, so a bad environment variable fails with an exit code of 2 and a named variable rather than a stack trace mid refresh. Every model build is wrapped so a SQL error becomes an ExecutionError carrying the model name, which is what makes a partial refresh resumable. And the EXCEPT ALL comparison is the correctness boundary: prove exits 1 and names the diverged model and partitions, so a wrong partition map cannot reach a dashboard unnoticed.
| Technology | Role here | Why chosen for this problem |
|---|---|---|
| DuckDB | The warehouse: 10 seeded sources, 33 models, target and shadow schemas in one file | The equivalence proof needs a full rebuild of every model alongside the incremental result in the same transaction scope. Two schemas in one embedded file makes that a single EXCEPT ALL with no data movement, and it runs with zero cloud credentials |
SQL with DECIMAL money columns |
Every model body, compiled once and rendered twice | An equivalence proof on DOUBLE sums would fail on aggregation order alone, since incremental and full rebuild group the same rows in different orders. Exact decimal arithmetic makes row for row equality a real assertion rather than an approximate one |
| sqlglot | Parses model SQL to read its output columns, transpiles to Snowflake, and parses the emitted MERGE back |
The MERGE column list has to match what the model actually selects. Reading it off the parse tree keeps the emitted DDL in step with the model automatically, and re-parsing the output means unparseable SQL fails in tests, not in a Snowflake worksheet |
Python dataclasses |
Model, Edge, Consumer, Plan, Comparison, frozen where they are declarations |
The partition map is the load bearing declaration in this repo. Frozen dataclasses with validation in __post_init__ mean an impossible edge, such as a window with no length, cannot be constructed at all |
| jinja2 + playwright | The self contained HTML refresh plan report, rendered headless at 1440x900 | A refresh plan is reviewed by an analyst, not read from JSON. Rendering the same numbers the CLI prints into one page makes the plan, the SLA risk and the proof result reviewable in a pull request |
| matplotlib | The savings curve and the benchmark chart | The most useful output of this project is the shape of the savings curve and where it crosses zero. That is a chart, and it has to be generated from results.json so it cannot drift from the measurement |
| pandas | Bulk loading the generated rows into DuckDB | Row at a time executemany measured about 2.7k rows/s against a file backed database because each statement commits separately. One registered frame inserts 300k rows in 0.26s, which is what makes re-seeding at nine change rates practical |
| rich | CLI tables for the plan, the schedule, the SLA check and the comparison | The plan's value is the reason column next to each stale model. A table that keeps that readable at 33 rows is worth the dependency |
Prerequisites: Python 3.11 or newer, git, and about 200 MB of free disk for the DuckDB file. No cloud account, no Snowflake, no Power BI.
git clone https://github.com/Sandeep0430/incremental-refresh-planner.git
cd incremental-refresh-planner
python3 -m venv .venv && source .venv/bin/activate
pip install -e ".[dev]"
# 1. build the warehouse: 10 sources, 33 models, then play one night's load batch
refresh-planner seed
# 2. what the loader actually touched, including the backdated partitions
refresh-planner changes
# 3. which partitions of which models are stale, and why each one is
refresh-planner plan
# 4. the naive rule treats the rolling window as one to one. The prover catches it
# and exits 1, naming the model and the partitions
refresh-planner prove --strategy naive
# 5. the declared partition maps. Row for row equal to a full rebuild, exits 0
refresh-planner prove
# 6. run under the scheduler and see the SLA slack per consumer
refresh-planner run
# 7. measured planned versus full, and critical path versus alphabetical ordering
refresh-planner compare --orderings
# 8. emit the Power BI incremental refresh policy and the Snowflake MERGE statements
refresh-planner policy
ls artifacts/
# tests and benchmark
pytest --cov=src/refresh_planner --cov-report=term
python benchmark/run_benchmark.py --quick
# regenerating the committed screenshots additionally needs playwright chromium
pip install -e ".[shots]" && playwright install chromium
python docs/make_screenshots.pyEverything above is also wired into the Makefile: make setup, make demo, make test, make bench, make screenshots. To run it in Docker instead, docker compose run --rm demo.
Every tunable is an environment variable documented in .env.example, including the warehouse size, the change rate, the rolling window length, the concurrency limit and the Power BI archive and incremental windows.
The refresh-planner report output rendered headless with playwright chromium at 1440x900. The model DAG is coloured by how much of each model is stale (red where more than half its partitions are), the middle table gives the reason for every stale model in terms of its declared partition maps, the right hand column shows per consumer SLA slack, the measured planned versus full comparison, and both equivalence proofs: the naive window rule DIVERGED on 2 models, the declared maps EQUIVALENT on all 31.
Measured savings against how much of the source the load batch changed, one independently re-seeded warehouse per point, 120 day warehouse with 33 models. Rows written saved (green) falls smoothly from 95% to 1%. Wall clock saved (blue) crosses zero at about 25% of source partitions changed: past that point the delete plus insert path costs more than a create or replace, and planning stops paying for itself. Grey shows how much of the model graph the solver marked stale, which is always more than the source change rate because of lag, window and rollup edges.
Real terminal output. changes shows 48 changed source partitions of which 41 are backdated behind the batch day, the worst by 110 days. prove --strategy naive catches the rolling window mistake: 84 partitions of fct_rolling_28d_revenue and 84 of mart_exec_summary, with the explanation and the first and last diverged partition. prove with the declared maps confirms all 31 models match row for row. Then the full test suite: 113 passed.
Measured with python benchmark/run_benchmark.py on a 2 vCPU Linux container, Python 3.11.15, DuckDB 1.5.5, one process, SET threads TO 2. Each point seeds a warehouse from scratch, materialises all 33 models, plays one load batch, then runs the planned refresh and a full rebuild for real in that order; scale figures are the median of three repeats. Raw output is in benchmark/results/results.json and benchmark/results/README.md.
| scale | days x orders/day | model partitions stale | full rebuild p50 | planned p50 | wall clock saved | rows written saved | planning p50 |
|---|---|---|---|---|---|---|---|
| small | 60 x 150 | 449 / 1,577 | 0.412s | 0.223s | 45.8% | 76.2% | 6.3 ms |
| medium | 120 x 400 | 700 / 3,147 | 1.077s | 0.627s | 41.8% | 84.1% | 12.2 ms |
| large | 180 x 1,200 | 1,040 / 4,718 | 2.449s | 1.760s | 28.1% | 83.1% | 22.2 ms |
Measured makespan and SLA risk by concurrency on the medium warehouse:
| workers | planned makespan | SLAs at risk | full rebuild makespan | SLAs at risk |
|---|---|---|---|---|
| 1 | 0.670s | none | 1.808s | 9 of 12 |
| 2 | 0.398s | none | 0.935s | 1 of 12 |
| 4 | 0.545s | none | 0.610s | none |
Where it degrades, and why: wall clock saving falls from 45.8% to 28.1% as the warehouse grows, while rows saved stays near 84%. The gap is the four models that cannot be partition refreshed at all (two dimension snapshots, the cohort table and the non deterministic snapshot); they are rebuilt whole on every run regardless of the plan, so their cost grows with the warehouse while the planned work does not shrink with it. The same effect is what puts the crossover at 25% rather than somewhere near 100%.
- ADR 0001: Partition level staleness propagation, verified by an equivalence proof. Why declared partition maps checked by a shadow rebuild beat both trusting
is_incremental()and refreshing everything, argued from the 84.1% row reduction and the 84 partition divergence the prover actually caught. - ADR 0002: A heuristic critical path schedule rather than an optimal solver. The boring choice: resource constrained scheduling is NP-hard, so this uses greedy list scheduling on an SLA aware priority, with the optimality gap and the myopia stated plainly.
- Reading a real dbt project. The catalogue is declared in
models.pyrather than parsed frommanifest.json. Add the parser when the partition map can be expressed as a dbt modelconfig()block, so the declaration lives next to the SQL it describes instead of in a second place. - Running against Snowflake. Snowflake is a dialect target only. Add a runtime adapter when a warehouse exists whose query history can supply real per partition cost, since the calibration is currently a local DuckDB measurement.
- Column level lineage. Staleness is tracked per partition, not per column, so a change to one column marks the whole partition stale. Add column level tracking when a single model is measured to spend more than about a quarter of the refresh budget on columns that did not change.
- Incremental equivalence proving. The prover does a full rebuild, so it costs what it is trying to avoid. Add sampled or partition scoped proving when the full proof stops fitting in the maintenance window.
- Retry and resume of a partial refresh.
ExecutionErrorcarries the model name so the plan could be resumed from it, but no state is persisted between runs. Add a run journal when the refresh gets long enough that restarting from the top is expensive.
No credentials are read, stored or needed: the only external resource is a local DuckDB file whose path comes from REFRESH_PLANNER_DB. .env.example documents every variable and contains no secrets; .env and *.duckdb are gitignored.
The structured JSON logger emits model names, partition counts, timings and a run id. It never emits row values, so a diverged partition is logged as a model name, a partition key and a row count, and reading the actual differing rows requires database access. The generated Power BI policy and Snowflake MERGE statements contain schema and column identifiers only, no connection strings and no account identifiers; the M expression refers to SnowflakeServer and Warehouse as Power BI parameters to be supplied at deploy time.
Least privilege: the planner needs SELECT on the source tables and write access to exactly two schemas, the target and the shadow. The shadow schema exists so the proof never writes into the schema serving dashboards. The Docker image runs as an unprivileged user (uid 10001) and the only writable paths are the data volume and the artifacts directory.
| Failure | Detection | Behaviour | Recovery |
|---|---|---|---|
| A declared partition map is wrong, so the incremental result differs from a full rebuild | prove compares both directions with EXCEPT ALL per model |
Exits 1, names each diverged model, its diverged partitions, the first and last of them, and explains the divergence in terms of the declared edge | Fix the edge declaration and re-run the plan. The corrected stale set is a superset of the wrong one, so re-running repairs the affected partitions |
| Late arriving rows backdate into a partition the plan did not cover | The change log records the partition each write touched, and changes reports the lateness distribution |
The partition is in the plan, because the plan reads the change log rather than assuming yesterday | None needed. If a loader stops recording its writes, the equivalence proof is the backstop |
A model reads CURRENT_TIMESTAMP and can never be proved equal |
deterministic=False on the model declaration |
Marked stale on every run, a warning is printed and logged, and the prover compares only the deterministic columns, listing the model under volatile_models |
Either freeze the timestamp as a batch parameter or accept the full refresh, which is what the warning says |
| A model's SQL fails to build mid refresh | Every build is wrapped, DuckDB errors are converted | Raises ExecutionError carrying the model name, so the failure identifies the model rather than the batch |
Fix the model and re-run. Downstream models were never built, so they are still marked stale by the next plan |
| An environment variable is missing or out of range | Config.__post_init__ validates types, ranges and the target and shadow schemas differing |
Exits 2 with the variable name and the accepted range, before opening the database | Correct the variable. .env.example lists every one with its default |
| The load batch changed almost everything, so planning costs more than it saves | compare computes the reduction with planning time included |
Prints that planning did not pay for itself at this change rate rather than reporting a negative saving as a win | Run a full rebuild for that batch. The crossover measured here is about 25% of source partitions changed |
The prove command is asked to reuse a shadow rebuild that was never built |
The shadow schema is checked in information_schema.schemata |
Raises ShadowMissingError naming the schema, instead of a catalog error naming a table |
Run prove once without rebuild_shadow=False, which is the CLI default |
The equivalence prover is the whole point of this repository, so its test asserts that the deliberately naive plan diverges. That test failed with assert not True: the prover reported every one of the 31 models equal, on a warehouse where the naive plan had definitely left the rolling window wrong. Fix commit 6f09311.
The root cause was not in the prover. It was that the test shared a session scoped warehouse fixture with an earlier test that had already run a full rebuild on it. A warehouse that is already correct cannot diverge: the naive plan rebuilt a subset of partitions from correct inputs and produced correct output, so the proof passed truthfully and told me nothing. The uncomfortable part is what would have happened if I had written the assertion the other way round. A test that says assert proof.equivalent passes vacuously under exactly these conditions, and the suite would have stayed green while the prover verified nothing at all. This generalises straight to production: an equivalence proof scheduled after any full rebuild always passes, so the proof has to be pinned to the incremental output specifically, on a warehouse that is genuinely stale. The fix was a function scoped stale_warehouse fixture that seeds its own copy and hands it over in the post batch, pre refresh state, with a docstring saying why.
The same lesson bit a second time, on measurement rather than correctness. The HTML report was showing the planned refresh 17% slower than a full rebuild while the benchmark on the same warehouse showed it 42% faster. The report ran its comparison after the proof, so the full rebuild it timed had a page cache warmed by two earlier refreshes and a database file the shadow schema had doubled; whichever refresh runs second wins, and the report's order was the opposite of the benchmark's. The fix was to snapshot the models before measuring, run planned then full in the same order the benchmark uses, then restore the snapshot so the proof still has a stale warehouse to work on. Both bugs are the same bug in different clothes: the state you measure against is part of the measurement, and if you do not control it you are reporting the fixture, not the system.
- Parse the partition map out of a dbt
manifest.json. The declaration belongs next to the model SQL, not in a second file that can drift from it. The first thing to check after that lands is how many models have no declared map, since those silently fall back to identity. - Persist a run journal so a failed refresh resumes.
ExecutionErroralready names the failing model; what is missing is durable state saying which partitions were committed before it failed. - Sampled equivalence proving. A full shadow rebuild costs what the planner saves. Proving a stratified sample of partitions per model, weighted towards recently changed ones, would make the proof affordable on every batch rather than on every code change.
- Feed the Power BI policy warnings into CI. The generator already computes the incremental window each mart actually needs from its lag, window and rollup edges. Failing the build when a deployed policy is narrower than that would catch the silent staleness before a dashboard serves it.
- The first metric to watch after deploying: the divergence rate per 100 proofs, split by model. Savings percentage is the headline but it is not the risk. A model that starts diverging is a partition map that has drifted from its SQL, and that number moving off zero is the signal to stop trusting the plan for that model until it is fixed.