dbt models on DuckDB that decompose a budget-to-actual revenue variance into price, volume, and mix effects that sum to the total exactly, turning a small net variance into an explained walk.
- A small net variance hides big offsetting moves. The demo's net revenue variance is only -7,800 on 1.82M of budget, but inside it sit a -21,764 volume drag and a +13,684 mix benefit that a single number never shows.
- Bridges that do not tie out cannot be trusted. Every walk here is checked so the three effects sum to the total variance exactly; the tie-out is a test, not a hope.
- Finance teams need the walk, not a query. The output is a tidy waterfall table plus a ranked driver list, both shaped to drop straight into a Power BI or Tableau report.
Month-end variance review stalls when the budget-to-actual number is a single figure with no explanation. A revenue variance of a few thousand dollars can hide a large volume shortfall offset by a favorable product mix and a small price gain, and the FP&A team spends the review reconstructing that story by hand in spreadsheets. In the demo dataset, budget revenue of 1,822,700 lands at actual revenue of 1,814,900, a net variance of only 7,800 that on its own tells a manager nothing about what actually happened.
This project decomposes that variance into price, volume, and mix using dbt models on DuckDB. Staging derives scenario revenue, an intermediate model computes per-cell price and quantity effects, and a bridge model splits the quantity effect into pure volume and mix. The result is a five-step walk from budget to actual where each step is an explainable driver, and a driver-detail mart ranks which product and region cells moved the number most.
On the demo the walk is Budget 1,822,700, then Price +280, Volume -21,763.58, Mix +13,683.58, landing on Actual 1,814,900 with a residual of exactly 0.00. The largest single driver is product P06 in NA at +35,760. The mid-period re-forecast lands within a revenue-weighted 5.0 percent of actual. All figures come from dbt build against the committed models and are guarded by 16 tests.
flowchart TD
RAW[raw_plan_actual: deterministic budget, forecast, actual] --> STG[stg_plan_actual: derive scenario revenue]
STG --> PVM[int_pvm_components: per-cell price and quantity effects]
PVM --> BR[int_bridge: split quantity into volume and mix, check tie-out]
BR --> M1[mart_variance_bridge: tidy waterfall]
PVM --> M2[mart_variance_detail: ranked drivers]
STG --> M3[mart_forecast_accuracy: forecast error and MAPE]
BR -. residual must be 0 .-> T[assert_bridge_ties_out]
The tie-out boundary is int_bridge: it is where the decomposition is reconciled against the total variance, and the assert_bridge_ties_out test fails the build if the residual is not zero.
| Technology | Role in this project | Why chosen here |
|---|---|---|
| dbt | Model lineage, tests, and docs for the transformation | The decomposition must be trusted and traceable; dbt makes the tie-out a first-class test and the lineage explicit (see ADR-0001) |
| DuckDB | Executes the dbt models with no server | Runs the whole project on a laptop and in CI; swap the profile to run the same models on Snowflake |
| SQL (dbt models) | All variance math | The price, volume, and mix logic is readable SQL a finance reviewer can audit |
| dbt tests (schema and singular) | Guardrails on grain, accepted values, and tie-out | Catches the exact decomposition bug fixed in this repo before it can ship |
Prerequisites: Python 3.11 or later.
pip install dbt-duckdb
git clone https://github.com/Vanithanallamothu/variance-bridge.git
cd variance-bridge
# profiles.yml is committed in the project, so point dbt at it
export DBT_PROFILES_DIR=.
# build every model and run every test
dbt build
# inspect the waterfall
duckdb variance_bridge.duckdb -c "select * from mart_variance_bridge order by step_order;"This is an analytical modeling project, so the meaningful performance metric is the developer and CI loop, not request throughput. A full dbt build compiles and runs 7 models and 16 tests against DuckDB in about 3.6 seconds on a laptop, so the tie-out is verified on every change.
The decomposition is linear in the number of cells (product and region combinations), and the tie-out identity holds at any grain, so the same models run unchanged whether the grid is the 18-cell demo or a full product hierarchy. The demo walk:
| Step | Amount | Running total |
|---|---|---|
| Budget revenue | 1,822,700.00 | 1,822,700.00 |
| Price | 280.00 | 1,822,980.00 |
| Volume | -21,763.58 | 1,801,216.42 |
| Mix | 13,683.58 | 1,814,900.00 |
| Actual revenue | 1,814,900.00 | 1,814,900.00 |
The three effects that make up the walk, drawn to the same scale, show why the net is misleading: the volume drag and the mix benefit are each an order of magnitude larger than the net variance they nearly cancel into.
xychart-beta
title "Revenue variance decomposition, budget to actual"
x-axis [Price, Volume, Mix]
y-axis "Effect on revenue (USD)" -25000 --> 15000
bar [280, -21763.58, 13683.58]
The point of the walk is that the visible net (-7,800) is the small residue of much larger volume and mix moves; the decomposition is what makes those moves visible and actionable.
- ADR-0001: dbt on DuckDB, not raw SQL scripts or a cloud warehouse
- ADR-0002: Price variance measured at actual quantity so the bridge ties out
The bridge covers a single period, a single currency, and revenue only. There is deliberately no FX effect bar and no cost or margin walk in this release, because adding them without a real multi-currency, multi-period dataset would be decoration rather than demonstration. The trigger to add an FX bar is a dataset with more than one transaction currency; the trigger to add a margin walk is cost data at the same grain. The decomposition method extends to both without changing shape.
- No secrets and no credentials: the project runs against a local DuckDB file. Moving to a warehouse is a profile change, and warehouse access there is via the platform's managed credentials, not values in the repo.
- The dataset is fully synthetic and deterministic, so there is no financial PII in the repo or in CI logs.
- CI pins
dbt-duckdbto a minor version so a build is reproducible.
- The decomposition stops tying out. If a model change strands a residual,
assert_bridge_ties_outfails anddbt buildblocks the merge. This is the primary guardrail and it is tested to fire (the bug was reintroduced once to confirm the test catches it). - A zero-revenue cell. The forecast error percentage uses
nullifon the denominator, so a zero-actual cell yields a null percentage rather than a divide-by-zero. - A grain that is not unique.
assert_grain_uniquefails if any product and region cell repeats, which would otherwise silently double-count a driver. - Bad input domain. The
accepted_valuestest on region fails the build if an unexpected region code enters staging.
The first version of the bridge did not tie out: the three effects summed to 480.00 less than the total variance. The symptom was a nonzero residual column in int_bridge, which I had added specifically as a tie-out check because I did not trust the decomposition until it reconciled.
The root cause was the price effect. I had measured price variance at budget quantity, the intuitive "hold volume, move price" version. That convention strands the price-times-volume interaction: when both price and quantity move, the rectangle formed by both changes belongs to neither the pure price effect nor the pure quantity effect, so it falls out of the walk. The fix was to measure price variance at actual quantity, which folds that interaction into the price bar. The quantity effect at budget price then splits cleanly into volume and mix, and the residual went to exactly 0.00. Fixed in commit d509ce7 and guarded permanently by assert_bridge_ties_out.
- Add an FX effect bar once a multi-currency dataset is available, decomposing the currency translation separately from price.
- Extend to a Budget to Forecast to Actual double walk so re-forecast value is visible alongside plan variance.
- Add a cost and contribution-margin walk at the same grain, reusing the decomposition method.
- Publish the marts as partitioned Parquet for direct load into a BI waterfall visual.
- Track the price, volume, and mix bars period over period; a mix bar that keeps growing is an early signal that the product strategy and the plan have drifted apart.