#104 feat(demo-data): IAS 21 acquisition-date FX + historical equity rates for Contoso

[Anonymous]

Originally created by: grynn-in

Summary

Adds IAS 21 acquisition-date FX translation for equity to the Contoso demo, on top of [#103].

• Acquisition-date (2020-01-01) historical FX rows added to epm_raw.exchange_rates for EUR/GBP/JPY/USD→USD (2020 levels). Equity is translated at the rate on the date the subsidiary was acquired (group inception, matching the ownership_periods effective_date), not the period/closing rate.
• epm_staging.historical_equity_rates emission — equity accounts 3010/3100 × 4 entities @ 2020-01-01 (8 rows): USMF→USD 1.0, DEMF→EUR 1.1213, GBMF→GBP 1.3257, JPMF→JPY 0.009201.

Exercises the historical-equity-rate path documented in [#102] and consumed by gold_consolidated_trial_balance (PRD-10).

Notes

• demo-data.sql is regenerated from generate_demo_data.py; the large line count is sequential recid (uid) reflow from inserting rows mid-stream — not a data rewrite. Regeneration is deterministic.
• Snake-case d365_raw table names from [#103] are preserved (0 PascalCase refs); historical_equity_rates is epm_staging (unaffected by the d365 rename).

🤖 Generated with Claude Code

[Anonymous]

Originally posted by: grynn-in

Review — feat(demo-data): IAS 21 acquisition-date FX + historical equity rates for Contoso

Adversarial review (correctness/data-integrity + nits). Traced the new seed data through gold_consolidated_trial_balance.sql, stg_d365_fo__exchange_rates.sql, silver_exchange_rates.sql, consolidation_groups.csv and init-db.sql. dbt parse run; no run/build/seed (prod-mutating).

Blocking issues

1. Historical equity rates for DEMF/GBMF use GROUP_EMEA, which never joins → IAS 21 translation silently does NOT apply to the two foreign EMEA subs (the PR's whole purpose).

• scripts/generate_demo_data.py:1456-1457 (CX_HER) and generated clickhouse/demo-data.sql:4381-4384 emit equity rates keyed to consolidation_group = 'GROUP_EMEA' for DEMF and GBMF.
• But gold_consolidated_trial_balance.sql:256 joins the historical-rate CTE on eo.consolidation_group = hr.consolidation_group, and eo.consolidation_group is sourced from the consolidation_groups seed (gold_consolidated_trial_balance.sql:71), which flat-maps all Contoso entities to GROUP_CORP (dbt_project/seeds/consolidation_groups.csv:2-5). GROUP_EMEA never appears in that seed, so the consolidated TB never emits a GROUP_EMEA row.
• Net effect: DEMF (EUR) and GBMF (GBP) equity accounts 3010/3100 miss the historical rate → historical_equity_rate is NULL → the case at gold_consolidated_trial_balance.sql:214-220 falls back to closing_rate. So the acquisition-date equity translation works only for USMF (USD, rate 1.0, a no-op) and JPMF (GROUP_CORP, matches). The two foreign EMEA subs that actually need IAS 21 get the closing rate.
• This passes CI silently: assert_equity_historical_rate_is_asof.sql:22 filters to historical_equity_rate is not null, so the join-miss rows are excluded and the test is vacuously green.
• Fix: set consolidation_group = 'GROUP_CORP' for the DEMF/GBMF rows in CX_HER (match the consolidation_groups seed), then regenerate demo-data.sql.
• Note: CX_OWNERSHIP (generate_demo_data.py:1435-1439, pre-existing) uses the same GROUP_EMEA convention, but there it's masked because ownership has a seed/hierarchy fallback (gold_consolidated_trial_balance.sql:208). The historical-rate path has no fallback to the right group, so the same convention is a real regression here.

2. JPY acquisition-date exchange_rates rows skip the ×100 store-scale → 100× too small after the silver round-trip.

• scripts/generate_demo_data.py:1123-1130 / demo-data.sql:2316-2318 store JPY at raw 0.009201 with ConversionFactor='Hundred'. The monthly loop 10 lines above (generate_demo_data.py:1106-1112) applies store_scale=100 for JPY (stored value 0.678, see demo-data.sql:2172); the acq loop does not.
• stg_d365_fo__exchange_rates.sql:14-16 passes 'Hundred' rows through unscaled and silver_exchange_rates.sql:14 divides everything by 100, so this acq row round-trips to 0.00009201 — 100× too small.
• Currently inert: these rows carry a 2020 period and there is no 2020 GL, while rate_lookup matches period_date exactly, so gold_consolidated_trial_balance never reads them. But it is inconsistent with the convention 10 lines above and would be 100× wrong if anything ever looks up FX as-of acquisition. Fix: either store JPY as 0.9201 (0.009201 × 100), or drop these exchange_rates acq rows entirely (see nit below).

Non-blocking nits

• The 12 acquisition-date exchange_rates rows (demo-data.sql:2307-2318) appear to have no consumer: equity translation reads historical_equity_rates, not exchange_rates, and rate_lookup only matches 2024 periods. Consider dropping them or documenting their intended use. (This is also what makes finding [#2] currently inert.)
• USMF historical-equity row (USD, rate 1.0) is redundant — the model short-circuits same-currency to 1.0 at gold_consolidated_trial_balance.sql:215. Harmless.
• AMG entities (AMHQ/AMUS/AMDE, CHF) get no historical equity rates, so AMG equity translates at closing rate (no IAS 21). Out of this PR's scope (Contoso-titled) but a coverage gap in the AMG demo.
• FX is provided one-directional (functional→USD only); consistent with the existing monthly convention and silver inverse-pair handling (konsolidat#106), so not a regression — noting for coverage only.

Verification

• dbt parse --no-partial-parse --profiles-dir . in dbt_project/: PASS (only pre-existing MissingArgumentsPropertyInGenericTestDeprecation warnings; no errors).
• Magnitudes sanity-checked: EUR 1.1213, GBP 1.3257, JPY 0.009201 (≈108.7 JPY/USD) are realistic Jan-2020 levels; rate_date 2020-01-01 aligns with ownership_periods effective_date; Decimal(18,6) DDL (init-db.sql:58) holds all values.
• Did NOT run dbt run/build/seed (mutates live prod).

MERGE RECOMMENDATION: BLOCKING — finding [#1] defeats the PR's stated purpose for DEMF/GBMF and is silent in CI.

[Anonymous]

Ticket changed by: grynn-in

• status: open --> closed