#151 Resolve entity currency from konsol's registry first (konsol#110)

[Anonymous]

Originally created by: grynn-in

Warehouse half of grynn-in/konsol#110. Konsol half: https://github.com/grynn-in/konsol/pull/123

What was wrong

gold_consolidated_trial_balance INNER JOINed silver_legal_entities — ERP-sourced — for each entity's accounting currency. A subsidiary submitting its trial balance as a file (F8), with no connector, had no row, and its numbers vanished at consolidation.

What this does

• silver_entity_currencies — the one answer to which currency an entity keeps its books in: konsol's Entity master (epm_staging.entities) first, silver_legal_entities second. UNION + GROUP BY, not a join: under join_use_nulls=0 an unmatched LEFT JOIN yields '' and coalesce() never falls through to the ERP value. The resolution sits one CTE out, because aliasing it accounting_currency beside its own anyIf raises CYCLIC_ALIASES (verified live).
• gold_consolidated_trial_balance joins it instead of silver_legal_entities.
• assert_entity_currency_sources_agree — fails when both sources are set and differ, instead of konsol silently overriding the ERP.
• assert_every_tb_entity_has_a_currency — every entity with TB rows (gold and submitted batches) must resolve a currency. Replaces the interim F8 guard assert_tb_submission_entities_consolidatable, which could only check against the ERP.
• epm_staging.entities DDL in init-db.sql (matches konsol's _REFERENCE_TABLE_DDL), declared as a source; data dictionary updated.

Verification (live stack)

• dbt run --select +gold_consolidated_trial_balance+ --exclude gold_spread_budget+: PASS=40 ERROR=0.
• dbt test on the new model, both new tests and assert_translation_rate_resolved: PASS=6.
• All 9 entity×group slices of gold_consolidated_trial_balance byte-identical before/after.
• Deployed main baseline unchanged: 2 errors, assert_equity_rate_coverage and assert_silver_gl_debit_credit_balance.
• Connector-less end-to-end proof: to follow.

Merge order

Merge after the konsol half. Existing volumes only get epm_staging.entities from konsol's ensure_reference_tables().

🤖 Generated with Claude Code

https://claude.ai/code/session_013WewQKFQgG7o2M3mUDPRR5

[Anonymous]

Originally posted by: grynn-in

Live end-to-end with a connector-less entity

With konsol#123 hot-copied, a new entity AMIT (EUR, no ERP row) was created in konsol and submitted a trial balance for 2024 P12.

check	result
silver_entity_currencies for AMIT	EUR, currency_source = konsol, in_erp = 0
gold_consolidated_trial_balance for AMIT	under AMG, EUR→CHF at 0.9528
main's INNER JOIN silver_legal_entities	0 AMIT rows (the bug)
other entity×group slices	unchanged — diff is the AMIT line only
chain rebuild	PASS=40 ERROR=0; tests PASS=6

Both new tests fail when they should:

• AMIT's functional currency blanked → assert_every_tb_entity_has_a_currency FAIL 1 (AMIT resolves '')
• AMDE set to CHF in konsol while the ERP says EUR → assert_entity_currency_sources_agree FAIL 1

Restored afterwards: chain and tests green, consolidated state identical to before the negative runs.

🤖 Generated with Claude Code

[Anonymous]

Originally posted by: grynn-in

Review round 2: 1d8be9c

The subquery comment gave a false reason. ClickHouse 24.8 does accept a non-equality AND (and an OR) in JOIN … ON, verified live. Only x IN (col, …) is refused. The subquery stays, for readability.

The re-review verified live that the resolved-currency join resolves AMG, GROUP_EMEA and GROUP_CORP, and that no trial-balance entity is left without a currency.

🤖 Generated with Claude Code

[Anonymous]

Ticket changed by: grynn-in

• status: open --> closed