#187 gold_variance_analysis: FULL OUTER JOIN + coalesce never fall through under join_use_nulls=0 (budget-only rows lose keys, actual-only rows look budgeted)

[Anonymous]

Originally created by: grynn-in

Found while reviewing grynn-in/konsol#182 (PR [#186]). Not fixed there, on purpose.

What happened

gold_variance_analysis joins actuals and budgets with a FULL OUTER JOIN, then fills each side with coalesce(a.x, b.x) and tests b.budget_amount is not null. This project runs with join_use_nulls = 0, the ClickHouse server default here. Under that setting the unmatched side of an outer join is filled with the column's default ('', 0), not NULL. None of the coalesces or NULL tests ever fall through:

• Budget-only rows lose their keys. coalesce(a.data_area_id, b.data_area_id) and coalesce(a.main_account, b.main_account) return a's '', and the fiscal year and period come back 0. The budget lands on a blank entity and account.
• Budget-only rows never get an account type from the chart. coalesce(a.account_type_name, am.account_type_name, '') returns '' (the plan's original finding, :58-59, :74, :76), so favourability is always false for them.
• Actual-only rows look budgeted. b.budget_amount is 0, not NULL, so budget_amount is not null is true. variance_favorable is computed against a zero budget instead of being false, and variance_pct falls to its != 0 guard only by luck.

Root cause

dbt_project/models/gold/gold_variance_analysis.sql:54-59 (the key and attribute coalesces), :62-68, :73-78 (b.budget_amount is null / is not null and the favourability case), and :82-86 (full outer join budgets as b). The model assumes NULL-filling outer joins. Neither the model nor profiles.yml sets join_use_nulls = 1.

Suggested fix

Do what silver_entity_currencies does (konsol#110): replace the full outer join with a UNION ALL of actual and budget rows keyed on (entity, year, period, account, dims), then GROUP BY with sumIf / countIf(side = 'budget') > 0 as "has a budget". Take the account attributes from silver_main_accounts by NOT IN-safe lookups, not from a. Alternatively, set join_use_nulls = 1 on this model only (SETTINGS join_use_nulls = 1), but that changes the column types to Nullable, and downstream Cube schemas read them. The UNION form keeps the contract.

The tests assert_favorable_revenue and assert_favorable_expense read this model and inherit the defect: a budget-only row can never be checked.

Repro

The same join shape on the test stack's ClickHouse (24.8, join_use_nulls = 0):

select coalesce(a.main_account, b.main_account) as main_account,
       coalesce(a.account_type_name, am.account_type_name, '') as account_type_name,
       b.budget_amount, b.budget_amount is not null as has_budget
from (select 'ZZ1' as main_account, 'Revenue' as account_type_name, 100.0 as actual_amount) as a
full outer join (select 'ZZ2' as main_account, 50.0 as budget_amount) as b on a.main_account = b.main_account
left join (select 'ZZ2' as main_account, 'Expense' as account_type_name) as am
  on coalesce(a.main_account, b.main_account) = am.main_account
order by main_account;

Expected: ZZ1 Revenue NULL 0 and ZZ2 Expense 50 1. Actual:

main_account  account_type_name  budget_amount  has_budget
                                 50             1          <- budget-only row: account '' and type '' (should be ZZ2, Expense)
ZZ1           Revenue            0              1          <- actual-only row: budget 0, "has a budget"

🤖 Generated with Claude Code

[Anonymous]

Originally posted by: grynn-in

Verdict: LIVE, confirmed. The FULL OUTER JOIN is still in gold_variance_analysis, so the join_use_nulls = 0 behaviour this issue describes is unchanged.

This is one of three open issues on the variance path, and they are cheaper as one piece of work than three:

• this one — join semantics under join_use_nulls = 0
• konsolidat#206 — the model filters scenario_id = 'BUDGET', so budgets authored in konsol never arrive at all
• konsol#214 — consumers sum across budget scenarios

It also shares a root cause with konsolidat#190 (coalesce() never falling back across a join under the same setting).

🤖 Triage against main — Claude Code · https://claude.ai/code/session_01P3Pf9835FeLeXjRrYTTZ1M