#141 fix(fx): scale exchange rates once — every foreign subsidiary was consolidating at 1% of its value

[Anonymous]

Originally created by: grynn-in

Closes [#138].

What happened

Consolidated statements for every non-functional-currency entity were wrong by two orders of magnitude. A 41.9M USD subsidiary contributed 365K CHF; the consolidated group was effectively the CHF parent alone.

Root cause

Scaling lived in two layers with contradictory assumptions:

• stg_d365_fo__exchange_rates.sql normalised to a ×100 representation: 'One' → Rate*100, 'Hundred' pass through
• silver_exchange_rates.sql then divided by 100 unconditionally

Any row not matching that private contract came out 100× wrong, silently. The demo's 'Hundred'-tagged European pairs held true rates, so they were divided once too many. 'One' rows survived (×100 then ÷100), which is why some rates were right — EUR→USD at 1.08 sat next to EUR→CHF at 0.00942 in the same table. The ERPNext adapter emits true rates and never fit the contract at all.

The JPY rows, encoded per-100 correctly, were the only 'Hundred' pair the old pipeline got right — part of why this survived.

The fix

Scale exactly once, in the D365 adapter, because ConversionFactor is a D365 concept: 'Hundred' rows (quoted per 100 units) are divided by 100, 'One' rows pass through. Staging emits true rates whatever the ERP; silver does no scaling. This also makes the ERPNext path correct for free.

Demo data made self-consistent with real D365 encoding: the 72 mistagged European rows rescaled to per-100 quotes; the 36 JPY rows untouched.

Guard

assert_exchange_rate_sane_magnitude — a non-identity rate outside [0.05, 20] fails for unit-class pairs; [0.0001, 10000] for cent-class (JPY, KRW…). The wide cent band is a conscious trade against false alarms on real JPY rates, documented in the test header. Against the pre-fix data this flags EUR→CHF (0.00942), USD→CHF (0.00877), CHF→EUR (106.1), CHF→USD (114.1). The existing assert_exchange_rate_positive passed throughout, because 0.00935 is positive.

Verified on the running stack (FY2024)

	before	after
silver EUR→CHF	0.00942	0.94233
silver USD→CHF	0.00877	0.87679
silver JPY→USD	0.00658 ✓	0.00658 ✓
AMUS (67.7M USD) translated	365K	59.2M CHF
AMDE (54.2M EUR) translated	328K	51.1M CHF
JPMF (7.6B JPY) translated	—	50.4M USD

Reciprocity rate(A→B) × rate(B→A) within 0.14% of 1 for every pair. Full dbt build: error count unchanged from baseline (3 pre-existing, unrelated).

Not included, noted in [#138]: a consolidated-TB tie-out test with a CTA tolerance — worth doing once CTA posting exists to assert against.

🤖 Generated with Claude Code

[Anonymous]

Ticket changed by: grynn-in

• status: open --> closed