#138 FX rates are scaled by 100 twice: every foreign subsidiary consolidates at 1% of its value

[Anonymous]

Originally created by: grynn-in

Found while running a real consolidation on the demo stack. Consolidated statements for any non-functional-currency entity are wrong by two orders of magnitude.

Evidence

Raw D365 data holds correct, plausible rates:

FromCurrency  ToCurrency  RateTypeName  Rate    ConversionFactor
EUR           CHF         Average       0.9322  Hundred
EUR           CHF         Default       0.9350  Hundred
EUR           CHF         Closing       0.9378  Hundred

All 72 seeded rows carry ConversionFactor = 'Hundred'; rates range 0.8533–0.9579 — i.e. already true rates, not ×100 representations.

What silver ends up with:

EUR -> CHF  0.00935      (should be ~0.935)
USD -> CHF  0.00876      (should be ~0.876)

Cause: the scaling is handled twice, with contradictory assumptions

models/staging/d365_fo/stg_d365_fo__exchange_rates.sql scales conditionally:

case when coalesce(toString(ConversionFactor), 'One') = 'One'
     then coalesce(Rate, 0) * 100
     else coalesce(Rate, 0) end as exchange_rate

models/silver/silver_exchange_rates.sql:13-14 then scales unconditionally:

-- D365 stores exchange rates multiplied by 100
exchange_rate / 100.0 as exchange_rate,

Rows tagged 'Hundred' pass through staging untouched, then silver divides them by 100 regardless. The silver comment asserts something the staging model has already dealt with — and which the seed data does not do in the first place.

Impact, measured

epm_gold.gold_consolidated_trial_balance, FY2024, group AMG:

entity	currency	Σ abs local	Σ abs translated	avg rate
AMHQ	CHF	32,305,156	32,305,156	1.00000
AMUS	USD	41,865,608	365,216	0.00876
AMDE	EUR	34,891,698	328,142	0.00941

A 41.9M USD subsidiary contributes 365K CHF instead of ~36.8M. The consolidated group is effectively the CHF parent alone; the two foreign subsidiaries are ~1% of their true weight.

Knock-on: the consolidated trial balance no longer ties. Local sums to exactly 0 (correct), but translated sums to 192.21 and group to 192.98, split as BS +1,251,865.62 against P&L −1,251,672.64. Part of that residual is legitimate CTA — translating a balanced ledger at mixed rates always leaves one — but it cannot be assessed while the rates are 100× out. Note also that no CTA appears to be posted back, so the statement does not balance.

Fix

Decide where the scaling belongs and do it once.

1. Preferred — make it the source adapter's job, since ConversionFactor is a D365 concept. Handle 'Hundred' there (divide by 100) alongside the existing 'One' branch, and delete the unconditional /100.0 from silver. Silver then holds true rates whatever the ERP.
2. Or keep silver's division and fix staging to normalise everything to ×100 first — but that pushes a D365 quirk into a source-agnostic layer.

Either way the seed data should be self-consistent: clickhouse/demo-data.sql currently pairs true rates with 'Hundred', which is not what a D365 export looks like.

Guard it

dbt_project/tests/assert_exchange_rate_positive.sql passes today — 0.00935 is positive. A range assertion would have caught this: a non-identity FX rate outside roughly 0.01–100 is almost certainly a scaling error. Worth adding alongside a test that the consolidated trial balance ties to within a CTA tolerance.

Found on konsolidat main with the seeded demo dataset, verified end to end from epm_raw.ExchangeRates through epm_silver.silver_exchange_rates to epm_gold.gold_consolidated_trial_balance.

[Anonymous]

Ticket changed by: grynn-in

• status: open --> closed