#206 Variance analysis filters scenario_id = 'BUDGET', so konsol-authored budgets never reach it

[Anonymous]

Originally created by: grynn-in

What happened

gold_variance_analysis selects its two sides by literal scenario code:

-- models/gold/gold_variance_analysis.sql:23, :37
from {{ ref('gold_scenario_trial_balance') }} where scenario_id = 'ACTUAL'
from {{ ref('gold_scenario_trial_balance') }} where scenario_id = 'BUDGET'

gold_scenario_trial_balance assigns 'BUDGET' only to the D365 branch (silver_budget_entries). The branch fed by konsol's own budgeting module — Budget Cycle → Budget Sheet → gold_spread_budget — carries its own scenario ids, and the model says so in its own comment:

-- gold_spread_budget scenario_ids (BUDGET_2024/2025, FORECAST_*) are disjoint
-- from the 'ACTUAL' and D365 'BUDGET' branches above, so no double-count.

The disjointness is deliberate and correct for avoiding a double-count in the scenario fact. Its unintended consequence is that scenario_id = 'BUDGET' never matches a konsol-authored budget.

So variance analysis works for a customer whose budgets come from D365, and silently returns nothing for a customer who uses the product's own budgeting feature. Same for gold_variance_ytd, gold_variance_quarterly and gold_variance_at_hierarchy_node, which follow the same pattern.

Nothing fails; the models build and come back empty, which is why it has not surfaced.

Root cause

A scenario's identity is being used where its kind is meant. A customer is free to name scenarios anything — PLAN, BUD26, FY26_FORECAST_V2 — and the literal only ever matches one particular naming.

Suggested fix

Filter on the semantic column, not the code. Two candidates already exist:

• gold_scenario_trial_balance already carries data_source ('budget' on both budget branches) — the smallest change.
• konsol's Scenario doctype carries scenario_type (actual / budget / forecast) and syncs it to epm_gold.scenario_definitions. Joining that is the more complete answer, and it is the same "declare it, don't infer it" pattern the chart and the Consolidation Policy already follow.

Prefer the second if the variance models should distinguish budget from forecast; the first if not.

Add a singular test on a fixture with a non-default budget scenario id, failing on main, per the dbt rule-change convention.

Repro

Load a budget through konsol (Budget Cycle → Budget Sheet), build, and query gold_variance_analysis: no rows, while gold_spread_budget holds the figures.

🤖 Generated with Claude Code

https://claude.ai/code/session_01P3Pf9835FeLeXjRrYTTZ1M

[Anonymous]

Originally posted by: grynn-in

Fixed by PR [#211].

[Anonymous]

Ticket changed by: grynn-in

• status: open --> closed