#99 fix(dbt): unify budget stores into one canonical monthly fact (#94)

[Anonymous]

Originally created by: grynn-in

Resolves the budget-storage fragmentation in [#94]. Manual monthly budgets (budget_save → Budget Sheet → epm_gold.budget_monthly_input) were invisible to K.EPM, which reads gold_spread_budget (annual+profile path only) — same scenario, different table → K.EPM returned 0 for anything entered by hand.

Fix — unify via "identity spread"

A manual monthly entry is a spread whose 12 weights are the typed values.

• Declare epm_gold.budget_monthly_input as a dbt source.
• gold_spread_budget = UNION of (a) annual × profile (PRD-6) and (b) manual monthly as an identity spread (profile_id='manual', base layer). A manual grain overrides annual-spread on the same (scenario, entity, year, account, dims) via a tuple NOT IN, so nothing double-counts. main_account cast to String to align both branches.
• gold_scenario_trial_balance now reads the canonical gold_spread_budget instead of the empty epm_staging.budget_input placeholder, so budgets reach consolidation/variance too (amount cast to Decimal128 to match the ACTUAL/BUDGET branches; fiscal_period promotes UInt8→UInt16).
• assert_budget_fact_grain_unique test guards the precedence (one row per grain).

K.EPM's budget_input fact already points at gold_spread_budget, so no fact-registry change is needed — extending the model in place fixes the read path.

Validated against live ClickHouse 24.8

• BUDGET_2024 (324) + every FORECAST scenario now present in the canonical fact (was BUDGET_2025-only).
• AMUS BUDGET_2024 / 4010 / p1 = −734,400 — exactly the entered value, so K.EPM(...,2024,...,"budget",...,"BUDGET_2024") now returns it instead of 0.
• 0 duplicate-grain rows (precedence dedup verified).

Acceptance criteria (#94)

• [x] Manual-monthly and annual+profile budgets land in one canonical table at monthly grain, identical semantics.
• [x] K.EPM(...) returns values for both entry modes (no zeros for manual budgets).
• [x] gold_scenario_trial_balance includes both for the requested scenario.
• [x] Manual entry representable as a spread (profile_id='manual', weights sum to 1.0/grain; FY = Σ12).
• [ ] epm_staging.budget_input no longer a silent dead source — now unread; removing the source decl + the init-db.sql table is a small follow-up (left to keep this PR dbt-parse-safe without CI).
• [x] Existing demo BUDGET_2025 (profile-spread) unchanged after unification (144 rows, untouched by the NOT IN).

⚠️ Logic validated by running the compiled-equivalent SQL on live CH; full dbt build/test pending CI (isolated worktree ≠ mounted project).

Closes the K.EPM-can't-see-manual-budgets issue from the budget-process session.

🤖 Generated with Claude Code

[Anonymous]

Originally posted by: grynn-in

Code review (×2 independent passes) — both SHIP, all high-risk items verified on live CH 24.8

Review A (ClickHouse/dbt correctness):

• NOT IN + NULL pitfall — SAFE: all grain columns are non-Nullable; simulated predicate keeps 144/144 spread rows, correctly drops a synthetic overlap, keeps a disjoint one.
• Nullable period_weight UNION — SAFE: spread UNION ALL manual runs (4356 rows), widens to Nullable(Float64); 0 zero-sum grains today.
• 3-branch TB UNION — SAFE: amount→Decimal(38,2), fiscal_period→UInt16, main_account→String; 42,597 rows, no NO_COMMON_TYPE.
• No circular dep; no downstream Int32 expectation broken (variance joins main_account String=String).

Review B (semantics/integration):

• Scenario disjointness — VERIFIED: ACTUAL='ACTUAL', D365='BUDGET', canonical=BUDGET_2024/2025/FORECAST_* — zero overlap, no double-count.
• BUDGET_2025 bit-for-bit unchanged (144 rows, sum 39,780,000).
• layer='base' is the semantically-correct canonical layer; nothing filters on the old api_input label; source-as-app-table is the right dbt pattern.

Fixes applied (scope 12-period test): excluded spread_profile_id='manual' from assert_spread_has_12_periods (partial-year manual budgets are legitimate).

Noted follow-ups (low / out of core scope):

• Variance (medium): gold_variance_analysis filters scenario_id='BUDGET', so app budgets reach the scenario TB but not variance — broadening needs a deliberate "which budget scenario drives variance" decision.
• Optional: a warn-test so a non-base-only sheet isn't silently dropped; a source freshness note for the app-owned table.
• epm_staging.budget_input is now unread (dead) — removing its source decl + the init-db.sql table is a small follow-up.

[Anonymous]

Ticket changed by: grynn-in

• status: open --> closed