#176 Translate from the governed group rates only (#93, konsol#103)

[Anonymous]

Originally created by: grynn-in

Closes [#93].
Closes [#177].

Decisions implemented (user, 13 Sep 2026)

• 5. Translation uses one governed group rate table in konsol, owned by group finance. The ERP rates only pre-fill drafts there.
• 6. Rates are held per fiscal period, one Closing and one Average, into the group's reporting currency. Cross rates are derived and never stored. Historical equity rates stay as they were.
• 7. The presentation currency stays on each Consolidation Group node, which is what this model already reads (grp.reporting_currency where data_area_id = ''). konsol#172 makes it a validated Link. The direct translation method is unchanged.

Companion PRs: konsol#172 (currency Link) and konsol#174 (Group Exchange Rate, write-through, pre-fill, close gate, adoption).

What changed

• gold_consolidated_trial_balance reads its Closing and Average rates from epm_staging.group_exchange_rates, keyed on (from, to, fiscal year, fiscal period).
• Gone from the model: rate_keys, all_rates, the three silver_exchange_rates as-of lookups, the ERP rate-type literals 'Closing' / 'Average' / 'Default', and the coalesce(..., 1.0) parity fallback.
• The ERP's rate-type names now live only in konsol's pre-fill adapter (group_rates.ERP_RATE_TYPES). The two strings left in the model are the governed table's own Select values.
• A missing rate fails loudly. A translated currency without an approved Closing and Average rate gets a NULL translation rate, and consolidated stops the build with throwIf(translation_rate is null, 'konsolidat#93: …'). The condition is per row: a constant would be folded and raise on every build. The model never produces a 0 or a 1.0.
• Same-currency entities stay at 1, and their closing and average rates show 1 by definition. The historical equity rate and its closing fallback are unchanged. No output column was added or retyped.
• clickhouse/init-db.sql adds epm_staging.group_exchange_rates, with the column list, engine and ORDER BY identical to konsol's _REFERENCE_TABLE_DDL:
  (to_currency String, from_currency String, fiscal_year UInt16, fiscal_period UInt8, rate_type String, rate Float64, document String) ENGINE = MergeTree ORDER BY (to_currency, from_currency, fiscal_year, fiscal_period, rate_type). It is declared as an epm_staging source.
• Tests:
• assert_every_translated_currency_has_a_governed_rate names each missing (from, to, year, period). Its keys are recomputed from the model's inputs, with the model's filters.
• assert_governed_rate_grain_unique checks one approved row per grain.
• assert_governed_rate_sane checks the rate is positive, of a governed type, not a currency into itself, and within the [#138] bands.
• The bands move to macros/fx_magnitude.sql, now shared with assert_exchange_rate_sane_magnitude. konsol's entry guard carries the same bands.
• docs/developer-guide/design/fx-translation.md is marked superseded for the rate source.

Unchanged on purpose: silver_exchange_rates and its tests stay; they are the ERP feed the pre-fill reads. The convert_currency macro still reads silver, but nothing calls it.

Deploy order (checked by deploy.sh)

1. Merge and deploy konsol#174. Its migrate creates Group Exchange Rate and runs the one-time adoption: the rates this model translates each period at today become approved rows labelled "Adoption". reconcile_all creates and fills epm_staging.group_exchange_rates.
2. Confirm that the adoption summary in the migrate log shows 0 refused, and that dbt compile --select fx_governed_rate_gaps, run against the stack, returns no rows.
3. Then merge this PR and build.

Before step 5, deploy.sh runs the guard's own macros through the dbt_init service (dbt show --inline "{{ fx_precheck() }}"):

• The table does not exist: the deploy aborts, whatever is translated, with "Run the konsol migration (konsol [#174]) first", naming the count of foreign-currency keys. The guard and the model read the table on every build. A fresh stack has the table from init-db.sql, so this fires only on an old volume without [#174].
• The table exists but keys have no usable rate: it warns with the key list and continues. Aborting there would deadlock a fix made in konsol that only this build can bring in. At step 5, every model not downstream of gold_consolidated_trial_balance refreshes; the consolidated TB and the models that read it keep their last figures, and step 5 fails the deploy.
• Nothing is built yet (a fresh stack): it passes.
• The check itself fails: it warns with dbt's error and continues.

epm_gold.currencies.usd_log10 comes from konsol (#174 adds it). Until then, assert_governed_rate_sane and assert_exchange_rate_sane_magnitude return one warn row each, and the guard's magnitude check is not applied.

Verification (live, konsolidat.local)

Built from copies of each dbt_project inside konsolidat_backend, outside the bind mount (/home/frappe/dbt_project_fx103 for this branch, …_fx103_main for main), with chown -R frappe:frappe. The command was dbt run --select +gold_consolidated_trial_balance+ --exclude gold_spread_budget+, scoped with --vars so that only the test slice was deleted and rebuilt.

ZZ data: entity ZZFX (EUR) under AMG (CHF), 100 % owned from 2099-01-01. FY2099 P12 trial balance: 1100 +1000, 2010 −300, 4010 −1200, 5010 +500. The governed rates went in through konsol#174's Group Exchange Rate: submit, then the after-commit write-through.

A — main, ERP feed (scope {fiscal_year: 2099, fiscal_period: 12, entity_scope: ZZFX}; OK created, PASS=41):

│ AMG │ 1100 │ 1 │  1000 │ EUR │ CHF │ 0.9528 │ 0.9471 │ 0.9528 │   952.8  │
│ AMG │ 2010 │ 1 │  -300 │ EUR │ CHF │ 0.9528 │ 0.9471 │ 0.9528 │  -285.84 │
│ AMG │ 4010 │ 0 │ -1200 │ EUR │ CHF │ 0.9528 │ 0.9471 │ 0.9471 │ -1136.52 │
│ AMG │ 5010 │ 0 │   500 │ EUR │ CHF │ 0.9528 │ 0.9471 │ 0.9471 │   473.55 │
ERP feed (silver_exchange_rates) EUR->CHF as of 2099-12-01: Average 0.9471 | Closing 0.9528 | Default 0.95  (2024-12-01 quotes)

B0: this branch, no governed rate:

23 of 36 ERROR creating sql incremental model `epm_gold`.`gold_consolidated_trial_balance`
Code: 395. DB::Exception: konsolidat#93: a translated currency has no approved governed Closing and Average rate for its period (konsol Group Exchange Rate). See assert_every_translated_currency_has_a_governed_rate.

B: this branch, governed EUR→CHF FY2099 P12 Closing 0.90 / Average 0.88 (OK created, PASS=37). The ERP's 0.9528 and 0.9471 no longer reach it:

│ AMG │ 1100 │ 1 │  1000 │ EUR │ CHF │ 0.9 │ 0.88 │ 0.9  │   900 │
│ AMG │ 2010 │ 1 │  -300 │ EUR │ CHF │ 0.9 │ 0.88 │ 0.9  │  -270 │
│ AMG │ 4010 │ 0 │ -1200 │ EUR │ CHF │ 0.9 │ 0.88 │ 0.88 │ -1056 │
│ AMG │ 5010 │ 0 │   500 │ EUR │ CHF │ 0.9 │ 0.88 │ 0.88 │   440 │

B2: the Average rate cancelled in konsol (staging keeps only the Closing row) → ERROR … Code: 395 … konsolidat#93 …. B3: amended back (GER-2099-12-EUR-CHF-Average-01-1, with a reason) → OK created, same figures as B.

Tests (this branch):

FAIL 60 assert_every_translated_currency_has_a_governed_rate
PASS    assert_exchange_rate_sane_magnitude   (now via the shared macro)
PASS    assert_governed_rate_grain_unique
PASS    assert_governed_rate_sane

The 60 failures are exactly the FY2024 keys the live site translates (5 pairs × 12 periods: EUR→CHF, EUR→USD, GBP→USD, JPY→USD, USD→CHF). The live site has no governed rates, because konsol#174's adoption has not run there. The test names each key. ZZFX's key had its rates and is not among them.

FY2024 P3 identity (what adoption guarantees). P3's rates, exactly as main translated them, were written to staging. The P3 slice was rebuilt with this branch ({fiscal_year: 2024, fiscal_period: 3}, OK created). The result is identical to the main-built slice for every group and entity: row counts, local, translated and group sums. The only diff was one displayed rate, 1.268458 against 1.2684579999999999, a float round-trip of the test's direct insert.

Cleanup:

• ZZ entity, node, ownership period, rates and raw TB rows removed; reconcile_all run; governed staging resynced (0 rows).
• Rebuilt with main's model for FY2099 P12 and FY2024 P3. Zero ZZFX rows remain anywhere, and P3 is back to the main-built figures.
• Build Approvals 237 → 237.
• The Group Exchange Rate doctype, its files and the staging table were then removed from the live stack. Nothing was deployed, no image was built, and the deploy checkout was not touched.

Review fixes

f800326, docs: docs/developer-guide/decisions/konsolidat-93-consolidation-currency.md opens with the outcome decided on 13 Sep 2026: the currency lives on the Consolidation Group node (a validated Link to ISO Currency), the EPM Settings field is removed, and rates are governed per period (konsol#172, konsol#174, this PR). The decisions index row follows.

aacae96:

1. A missing rate fails before the delete. The model's first pre_hook is governed_rate_guard() (macros/governed_rates.sql). It runs a throwIf over the same missing-key set, scoped by the run's period_filter and scope_filter, ahead of the scope's DELETE. The in-model throwIf stays as the second line of defence. assert_every_translated_currency_has_a_governed_rate reuses the same governed_rate_gaps(), so the test and the guard cannot drift.
2. The tests no longer skip the model under dbt build. The coverage, grain and sanity tests are severity: warn; they name the problem, and the model's guard decides.
3. deploy.sh's abort check adds ERROR creating (a model errored) and SKIP=[1-9] (a model or test was skipped because something upstream failed).
4. I did not add a bare ERROR=[1-9]. That count includes every error-severity data test, so it would turn today's "tolerated test failures" branch into an abort for any failing test, including ones that skip nothing.
5. Deploy order is enforced, as described above.
6. CLS (period 13): build_date_from_year_period clamps the period with least(greatest(p, 1), 12), so OPN is January and CLS December.
7. Cleanup: macros/currency_conversion.sql is deleted: nothing called convert_currency, and it still carried 'Default' and a 1.0 fallback. The consolidation guide, the macro reference and build_consolidation_report.py's hints now point at Group Exchange Rate.
8. Magnitude rule (decided for both PRs):
9. epm_gold.currencies gains usd_log10 Float64 DEFAULT nan; the exact line is pending confirmation against konsol's DDL.
10. A rate is flagged when abs(log10(rate) − (usd_log10(to) − usd_log10(from))) > 1, or when either currency has no reference value.
11. The formula lives once, in macros/fx_magnitude.sql, used by assert_governed_rate_sane and assert_exchange_rate_sane_magnitude. The literal bands and the currency list are gone. The konsol side is konsol.group_rates.magnitude_problem.

Verification. All runs used scratch copies inside konsolidat_backend with --project-dir pointing at the copy. The pre-fix copy is f800326; the fixed copy is this commit.

• Each copy's profiles.yml targets schema zzfx103, so every built model went to zzfx103_* databases and live epm_* tables were never written.
• The governed-rate and currency sources were redirected to a scratch zzfx103_src, holding the 120 rates the live model translates today (60 keys) and reference magnitudes for USD, EUR, CHF, GBP, JPY and IDR.
• The removed rate was GBP→USD FY2024 P5 Average. All zzfx103* databases and copies were dropped afterwards.
• dbt parse and a full dbt compile exit 0, bash -n deploy.sh passes, and mkdocs build --strict exits 0.

Finding 2, the dbt run path (dbt run --select gold_consolidated_trial_balance, unscoped):

setup (every rate present): OK created, rows 1517 (live epm_gold: 1517)
A (f800326): ERROR ... Code: 395 ... konsolidat#93: a translated currency has no approved governed ... rate
   rows after the failed run: 0          <- the DELETE ran, then the insert failed
B (fixed):   ERROR ... Code: 395 ... konsolidat#93: ... Nothing was deleted. ...
   rows after the failed run: 1517

Finding 1, the dbt build path (dbt build --select +gold_consolidated_trial_balance,resource_type:model plus the three governed tests, --indirect-selection empty):

A (f800326): FAIL 1 assert_every_translated_currency_has_a_governed_rate
             SKIP relation zzfx103_gold.gold_consolidated_trial_balance
             Done. PASS=24 WARN=0 ERROR=1 SKIP=1
             deploy.sh old abort check: tolerated (reports "models were built")   new: ABORTS
B (fixed):   WARN 1 assert_every_translated_currency_has_a_governed_rate
             ERROR creating sql incremental model `zzfx103_gold`.`gold_consolidated_trial_balance`
             Code: 395 ... konsolidat#93 ... Nothing was deleted.
             Done. PASS=24 WARN=1 ERROR=1 SKIP=0
             deploy.sh old: ABORTS   new: ABORTS
rate restored: PASS assert_every_...; OK created ...gold_consolidated_trial_balance; Done. PASS=26 ERROR=0

Pre-existing, not from this PR (superseded by the re-review fixes below): a plain dbt build --select +gold_consolidated_trial_balance on the live data skips the model anyway, because three error-severity upstream tests fail on the data: assert_d365_gl_vouchers_balance (927), assert_equity_rate_coverage (7) and assert_silver_gl_debit_credit_balance (7). They fail identically before and after this PR. With the new SKIP=[1-9] abort, a deploy on today's live data would stop at step 5 until they are fixed or downgraded. That is the intended "stale is not success", but it is new behaviour.

CLS clamp. Every model that uses the macro was compiled with and without the clamp, and each compiled SELECT was run against the same scratch upstream (read-only):

toDate('2024-13-01') = 1970-01-01 (unclamped)   P13 clamped = 2024-12-01   P0 clamped = 2024-01-01
periods outside 1..12: live gold_trial_balance 0, gold_entity_ownership 0
silver_gl_entries             before 1910 rows, sum(cityHash64) 2360735465453505165   after identical
gold_entity_ownership         before  108 rows, sum(cityHash64) 14586162045804961862  after identical
gold_acquisition_adjustments  before    0 rows                                         after identical

(gold_consolidated_trial_balance uses the macro only for the historical-rate as-of date, and the prorate_period_amount macro and four tests use it too. Every one of them is unchanged for P1–P12 by construction.)

Magnitude rule, assert_governed_rate_sane on scratch rows:

flagged:     ZZ-MAG-JPY-x100   JPY->USD 0.66    more than 10x from the reference magnitudes
             ZZ-MAG-SEK-noref  SEK->CHF 0.085   no reference magnitude for SEK (ISO Currency.usd_log10)
not flagged: ZZ-MAG-IDR-6e-5   IDR->USD 0.0000625   (the old [1e-4, 1e4] band refused it)
             ZZ-MAG-JPY-ok     JPY->USD 0.0066
assert_exchange_rate_sane_magnitude over the ERP feed: PASS (0 flagged)

Re-review fixes (ebbe226)

1. The magnitude tests can no longer error on a missing column. CREATE TABLE IF NOT EXISTS never adds a column, so existing volumes have no epm_gold.currencies.usd_log10 until konsol [#174]'s migration adds it; severity: warn covers test failures, not errors. Both magnitude tests now check first, with adapter.get_columns_in_relation. If the column is missing, they return one warn row: "usd_log10 not present yet in epm_gold.currencies: run the konsol migration from [#174]". fx_magnitude_problem also flags a non-finite rate first.
2. Zero, negative, NaN and duplicate rates are refused before the DELETE. governed_rate_gaps() names three reasons per translated key:
3. missing: no Closing and Average rate;
4. duplicate: more than one approved rate of a type;
5. invalid: a rate that is not isFinite(rate) and rate > 0.

The pre_hook raises one message per reason. The model's second line of defence NULLs the same cases (n_closing != 1, n_average != 1, n_invalid > 0). The magnitude check stays at warn.

1. assert_equity_rate_coverage is severity warn. Its 7 rows are konsol master data (entity nodes with no Historical Equity Rate), and the model has a defined fallback: equity translates at the closing rate. The one-sided-ledger checks assert_d365_gl_vouchers_balance and assert_silver_gl_debit_credit_balance stay at error. Deploy on the current stack will abort on those two until the demo ledger is replaced, and that is correct: a one-sided trial balance must not be consolidated.
2. The pre-step-5 check no longer fails open.
3. It uses EXISTS TABLE, and any failed curl is reported as "governed group exchange rates ... were NOT checked", with a warning.
4. It warns and continues rather than aborting. dbt connects inside the compose network, so a host-side curl failure (wrong password, port not published on localhost, server down) says nothing about the warehouse, and aborting would block every deploy on such a host.
5. The build still cannot mistranslate: the model's guard stops it before replacing anything.
6. (Since superseded: a missing table aborts; an existing table with missing keys warns with the list and continues. See the deploy order above.)
7. P12 prorating. prorate_period_amount ends a period at addMonths(period start, 1). The old end, least(p + 1, 13), was 1970-01-01 before the [#177] clamp and December after it, so a mid-December acquisition prorated P12 to 0.

Verification. Scratch copies ran in konsolidat_backend with --project-dir pointing at the copy; aacae96 is "A" and this commit is "B". They targeted the zzfx103_* databases, with the governed-rate and currency sources redirected to zzfx103_src, as before. The scratch currencies table was built without usd_log10, as on today's volumes. Afterwards every zzfx103* database and copy was dropped. dbt parse and a full dbt compile exit 0, and bash -n deploy.sh passes. No docs changed in this commit.

H1, currencies without usd_log10 (dbt build, upstream models plus the three governed tests, --indirect-selection empty):

A: ERROR assert_governed_rate_sane: Code: 47 Unknown expression or function identifier 'usd_log10' ...
   Done. PASS=25 WARN=0 ERROR=1 SKIP=0   (exit 1; deploy aborts on "Database Error")
B: WARN 1 assert_governed_rate_sane; OK created gold_consolidated_trial_balance
   Done. PASS=25 WARN=1 ERROR=0 SKIP=0   (exit 0)
   assert_exchange_rate_sane_magnitude: WARN 1 -> "usd_log10 not present yet in epm_gold.currencies: run the konsol migration from #174"

(In this narrow selection, A's erroring test did not skip the model: it hangs off sources only. The build still failed, and deploy aborted on it. B passes.)

M2, unusable rates on GBP→USD FY2024 P5 Average (dbt run --select gold_consolidated_trial_balance, unscoped; 1517 rows; P5 P&L translated at 1.265464 = 2,366,248.11):

zero       B: Code: 395 ... a governed rate a translated currency needs is zero, negative or not a finite number. Nothing was deleted.  rows 1517, P5 [1.265464] 2366248.11
           A: OK created -> P5 P&L translated at [0], 0
duplicate  (rates [1.5, 1.265464])
           B: Code: 395 ... more than one approved governed rate of one type for its period. Nothing was deleted.  rows 1517, P5 unchanged
           A: OK created -> anyIf picked one of the two (here 1.265464; which one is arbitrary)
nan        B: Code: 395 ... zero, negative or not a finite number. Nothing was deleted.  rows 1517, P5 unchanged
           A: OK created -> P5 P&L translated at [nan], nan

The fx_magnitude_problem NaN branch, with the column present: ZZ-MAG-NAN nan → not a finite number; ZZ-MAG-JPYx100 0.66 → more than 10x; IDR 6.25e-5 not flagged.

M3, full dbt build --select +gold_consolidated_trial_balance (B):

WARN 7   assert_equity_rate_coverage
FAIL 927 assert_d365_gl_vouchers_balance          (error: the one-sided demo ledger)
SKIP     assert_silver_gl_debit_credit_balance, relation gold_consolidated_trial_balance

L4, the pre-step-5 block, extracted from deploy.sh and run in the backend container. The ClickHouse URL and table names were substituted; the credentials came from the container's environment and were never printed.

live stack (table exists, 0 rows; 1513 foreign-currency TB rows): [ERROR] ... group_exchange_rates is empty. Run the konsol migration that adopts group exchange rates (konsol #174) first ...  exit 1
scratch table, empty:            [ERROR] ... is empty ...                                         exit 1
scratch table, 1 row:            [INFO] Governed group exchange rates: 1 rows ... (continued)      exit 0
ClickHouse unreachable:          [WARN] ... were NOT checked. [WARN] Continuing: ... (continued)   exit 0
wrong password:                  [WARN] ... were NOT checked. ...                  (continued)      exit 0

L5, prorating 1000.0 (inline compile, run read-only):

          P12, acquired 15 Dec   P11, acquired 15 Nov   P12, acquired 1 Dec
before    0                      533.33                 0
after     548.39 (17/31)         533.33                 1000

The FX contract (2cf5a2e)

Decided 13 Sep 2026: one source of truth for FX rates, and it comes via konsol.

epm_staging.group_exchange_rates.rate is the true rate: units of to_currency per 1 from_currency, as a Float64, published by konsol.

• Finance enters any "quoted per" factor (1/10/100/1,000/10,000) in konsol, and konsol divides it out before publishing.
• The warehouse never scales or inverts the rate. It reads neither inverse_quote nor quoted_per.
• silver_exchange_rates holds the ERP quotes, which feed only konsol's pre-fill.

The contract is stated in the model header, fx-translation.md, the source description and init-db.sql.

• The magnitude check is now the safety net against konsol ever publishing an unscaled or double-scaled rate. The pre-hook guard refuses, before the DELETE, any rate the translation will use that is more than 10× from what the two currencies' usd_log10 references imply (abs(log10(rate) − (usd_log10(to) − usd_log10(from))) > 1).
• It applies only when both references are known; a missing reference is a warning from the tests, not a refusal.
• The guard's error now names the first key and its reason. It uses a CAST of the message, because ClickHouse's throwIf only accepts a constant message.
• Unchanged from the earlier fixes:
• rates must be finite, above zero, and exactly one Closing and one Average per key;
• the checks that usd_log10 exists;
• severity: warn on the tests;
• the deploy checks.
• Lows:
• the model's fallback throwIf message now covers missing, duplicate and invalid rates;
• the stale comment saying the grain test "fails the build" is fixed (it warns, and the guard is what stops a collision).
• DDL in init-db.sql is written as the single statements konsol executes. It stays provisional until konsol [#174]'s final lines are copied verbatim:
• group_exchange_rates: (to_currency String, from_currency String, fiscal_year UInt16, fiscal_period UInt8, rate_type String, rate Float64, document String) ENGINE = MergeTree ORDER BY (to_currency, from_currency, fiscal_year, fiscal_period, rate_type), with no inverse_quote;
• currencies: (currency_code String, currency_name String, symbol String, minor_unit UInt8, usd_log10 Float64 DEFAULT nan) ENGINE = MergeTree ORDER BY currency_code.
• Deploy order is unchanged: konsol [#174] first; the adoption summary must show 0 refused and a non-zero group_exchange_rates count; then this PR. A konsol that publishes a mis-scaled rate is refused by the build, not translated.

Verification (scratch copies in konsolidat_backend, zzfx103_* databases, all dropped afterwards; dbt parse and a full dbt compile exit 0; mkdocs --strict exit 0; bash -n deploy.sh passes):

1. true JPY->USD FY2024 P3 (Average 0.00659678, Closing 0.00664305):
   OK created; JPY P3 translated at [0.00659678, 0.00664305] = 3,079,446.47   (live ERP-rate build: identical)
2. the same Closing published 100x wrong (0.664305):
   B (2cf5a2e): refused: "konsolidat#93 refused before anything was deleted: a governed rate more than 10x from the
                reference magnitudes (check its scale: the quoted-per factor) for JPY->USD FY2024 P3 (1 key(s) in all; ...)"
                rows unchanged (1533), JPY P3 unchanged
   A (ebbe226): OK created; JPY P3 translated at [0.00659678, 0.664305] = 208,722,763.29   (~68x the true figure)
3. an IDR entity (a scratch entity's currency set to IDR), true IDR->USD 6.25e-5 / 6.3e-5, scoped to FY2024:
   OK created; IDR P3 translated at [0.000063, 0.0000625]: 2,699,340 IDR -> 169.15 USD, in GROUP_CORP and GROUP_EMEA
   (the old [1e-4, 1e4] bands refused 6.25e-5)
3b. the IDR P3 Closing published 100x wrong (6.25e-3): refused "... more than 10x ... for IDR->USD FY2024 P3 ..."; rows unchanged
4. a volume konsol has not migrated (currencies without usd_log10): dbt build -> PASS=25 WARN=1 ERROR=0 SKIP=0,
   gold_consolidated_trial_balance OK created (the magnitude refusal simply isn't applied until the references exist)

Aligned to konsol [#174]'s frozen FX tables (konsol bccc675)

• Table definitions: init-db.sql's two statements are byte-identical to konsol [#174]'s _REFERENCE_TABLE_DDL (checked by diff against origin/feat/103-group-exchange-rate):
• CREATE TABLE IF NOT EXISTS epm_staging.group_exchange_rates (to_currency String, from_currency String, fiscal_year UInt16, fiscal_period UInt8, rate_type String, rate Float64, document String) ENGINE = MergeTree ORDER BY (to_currency, from_currency, fiscal_year, fiscal_period, rate_type);
• CREATE TABLE IF NOT EXISTS epm_gold.currencies (currency_code String, currency_name String, symbol String, minor_unit UInt8, usd_log10 Float64 DEFAULT nan) ENGINE = MergeTree ORDER BY currency_code;
• Contract: rate is the true rate, in units of to_currency per 1 from_currency. Only approved rows are published, and konsol divides out the quoted-per factor in decimal (it publishes 0.006607 for JPY). Existing stacks get the column from konsol with ALTER TABLE epm_gold.currencies ADD COLUMN IF NOT EXISTS usd_log10 Float64 DEFAULT nan.
• The "no reference" rule is konsol's: isNaN(usd_log10) OR (usd_log10 = 0 AND currency_code != 'USD'), because USD is 0 by definition. fx_reference_magnitudes() applies it, and the pre-hook guard and both warn tests read only that. The magnitude rule is abs(log10(rate) − (usd_log10(to) − usd_log10(from))) <= 1.
• assert_governed_rate_sane lets a currency into itself pass at exactly 1; any other self-rate is named.
• docker/configurator/init.sh now stops before bench migrate if ClickHouse doesn't answer /ping within 120 s. It used to warn after 60 s and carry on. The migrate runs konsol's rate adoption, which reads ClickHouse.

Live A/B against the real references on konsolidat.local: epm_gold.currencies with usd_log10 populated (66 rows, none NaN or unset; USD 0, JPY 2.17, IDR 4.21, GBP −0.1, EUR −0.03, CHF −0.05).

• Only the governed-rate source was redirected, to a scratch table holding the true rates the live model translates today (120 rows). The built models went to zzfx103_* databases, all dropped afterwards.
• Live gold_consolidated_trial_balance (1517 rows), epm_staging.group_exchange_rates (0 rows) and epm_gold.currencies were never written.

• dbt parse and a full dbt compile exit 0.

  1. true JPY->USD FY2024 P3 (Average 0.00659678, Closing 0.00664305)
     OK created; JPY P3: 464,617,340 JPY -> 3,079,446.47 USD (live: identical)
     USD entities in the USD group: translation_rate [1]; USD->CHF at the governed 0.8762 / 0.8824
  2. JPY P3 Closing published x100 (0.664305)
     refused: "konsolidat#93 refused before anything was deleted: a governed rate more than 10x from the reference
     magnitudes (check its scale: the quoted-per factor) for JPY->USD FY2024 P3 (1 key(s) in all; ...)"
     rows unchanged (1517), JPY P3 unchanged
  3. IDR (a scratch entity switched to IDR), true IDR->USD 6.17e-5 / 6.2e-5, FY2024
     OK created; IDR P3: 5,398,680 IDR -> 333.63 USD
     IDR P3 Closing published x100 (0.00617): refused "... more than 10x ... for IDR->USD FY2024 P3 ..."; rows unchanged
  4. warn tests on the real references, plus a USD->USD 1.0 governed row:
     PASS assert_every_translated_currency_has_a_governed_rate, assert_governed_rate_grain_unique,
     assert_governed_rate_sane (the USD->USD 1.0 row is not flagged), assert_exchange_rate_sane_magnitude
     (0 ERP-feed rows flagged)

Joint-review follow-ups (superseded in part by the re-review fixes below: no scripts/sql, and the pre-check warns on gaps)

• (4) The deploy pre-check lists missing keys. It used to pass as soon as the governed table had rows. It now runs scripts/sql/fx_governed_rate_gaps.sql, standalone SQL that also runs in clickhouse-client, and stops with the (from, to, year, period, reason) list when any translated key has no usable rate. The reasons are missing, duplicate and invalid, as in the guard. The guard's >10× check needs usd_log10, so it stays in the guard only. Keys the adoption skips are therefore caught before step 5.
• (5) Every key is listed. The guard's refusal names every unusable key, not only the first. By contract, a missing rate still refuses the whole run. fx-translation.md documents that a newly submitted foreign-currency trial balance blocks full builds until its Closing and Average rates are approved, and that konsol's home and the SQL above show which rates are missing.
• (7) The model's ownership filter. The translated-key set in deploy.sh now uses gold_entity_ownership with the model's filters (complete chain, not equity or none) instead of the whole group tree (scripts/sql/fx_translated_keys.sql).
• (10) Shared cases. assert_fx_magnitude_cases runs one case table through fx_magnitude, in a single file that is easy to align with konsol's magnitude_problem cases. It covers JPY and IDR true and ×100, USD→USD 1.0, a NaN reference, 0 for a non-USD currency, a currency pegged at 0.001, zero and NaN. fx_has_reference() is now the one "no reference" rule.
• (12) The "provisional" comments in init-db.sql are gone.

Verification. dbt parse and a full dbt compile exit 0. bash -n passes on deploy.sh and init.sh, and mkdocs --strict exits 0.

Deploy pre-check, dry-run of the block only (not deploy.sh), read-only against live konsolidat.local:

[ERROR] 60 translated key(s) have no usable governed rate (from, to, year, period, reason):
    EUR CHF 2024    1   missing   ...   USD CHF 2024    12  missing
[ERROR] Approve their Closing and Average rates in konsol (Group Exchange Rate), or run the konsol migration that adopts them (konsol #174), then re-run this deploy.
exit 1        (EUR->CHF, EUR->USD, GBP->USD, JPY->USD, USD->CHF: 12 periods each, all "missing")
unreachable ClickHouse: [WARN] ... were NOT checked ... (continued to step 5), exit 0

Scratch build (real epm_gold.currencies; only the governed-rate source redirected; zzfx103_* databases, dropped afterwards):

assert_fx_magnitude_cases: PASS
clean (every rate present): OK created, 1517 rows
three keys removed: refused "konsolidat#93 refused before anything was deleted: 3 translated key(s) without a usable
  governed rate (konsol Group Exchange Rate): EUR->CHF FY2024 P7 missing; GBP->USD FY2024 P11 missing; JPY->USD FY2024 P3 missing. Reasons: ..."
  rows after: 1517 (unchanged)

Re-review fixes (after 7fb4a6a)

• M1: one magnitude rule. fx_is_implausible(rate, from_log10, to_log10) (more than one decade off) is called by both fx_magnitude_problem (the warn tests) and the guard's r_implausible, so assert_fx_magnitude_cases now covers the rule that refuses builds. Added the exact-10× boundary case: ('XPG','USD',0.01,3.0,0.0,'ok').
• M2: the pre-check can no longer deadlock a deploy.
• deploy.sh aborts whenever epm_staging.group_exchange_rates does not exist. That is the migration-ordering case; see the follow-up below.
• When the table exists but keys have no usable rate, it warns with the list and continues. It reads the last build's gold tables, so aborting would deadlock a fix made in konsol that only this build brings in.
• At step 5 the guard still refuses the consolidated TB before any DELETE. Every model not downstream of it refreshes; the consolidated TB and the models that read it keep their last figures, and step 5 fails the deploy.
• fx-translation.md says the same.
• L1: one source for the pre-check. It runs the guard's own macros through the dbt_init service, inside the compose network: dbt show --inline "{{ fx_precheck() }}" --output json. So it includes "implausible", and there is no second copy of the SQL to keep in step.
• scripts/sql is gone. dbt compile --select fx_governed_rate_gaps (an analysis) renders the same query as standalone SQL for clickhouse-client.
• governed_translated_keys() is the one definition of the translated-key set.
• I chose this over a CI job comparing the macro with a standalone file on a fixture: it leaves less to maintain.
• L2: the message is capped. The guard lists the first 50 sorted keys, then "... and N more (assert_every_translated_currency_has_a_governed_rate lists all)". With 60 missing keys the message is 1,886 bytes.
• L3: no hidden failures. The check no longer uses curl. If it fails, it prints dbt's own error lines ("FX pre-check failed ... dbt said: ...") and continues.

Verification. Scratch copies ran in konsolidat_backend and were dropped afterwards; nothing live was written.

• dbt parse, a full dbt compile, and compiling the analysis all exit 0. The compiled analysis is 89 lines with no Jinja left. bash -n passes on deploy.sh and init.sh.

• The deploy pre-check block was dry-run with the dbt_init command swapped for the container's dbt. It never ran deploy.sh.

  live, read-only: [WARN] 60 translated key(s) have no usable governed rate (from->to, year, period, reason):
  EUR->CHF FY2024 P1 missing ... USD->CHF FY2024 P9 missing (60 listed, all "missing")
  [WARN] Continuing: step 5 refreshes every other model, but gold_consolidated_trial_balance refuses
  to rebuild (nothing is deleted) until these rates are approved ... exit 0
  table missing: [ERROR] 60 foreign-currency key(s) are translated, but epm_staging.group_exchange_rates does not exist.
  [ERROR] Run the konsol migration that adopts group exchange rates (konsol #174) first ... exit 1
  check fails: [WARN] FX pre-check failed ... dbt said:
  Error: Invalid value for '--profiles-dir': Path '...' does not exist.
  [WARN] Continuing ... exit 0
  Scratch build and mutation checks. Live epm_gold.currencies currently has usd_log10 NaN in all 66 rows: it was re-synced at 16:17 today, which is on the konsol [#174] side. So these runs used a scratch currencies table with the real reference values.

  assert_fx_magnitude_cases (incl. exactly 10x): PASS
  guard, JPY P3 Closing x100 (0.664305): refused "... 1 translated key(s) without a usable governed rate ...: JPY->USD FY2024 P3 implausible ..."; rows unchanged (1517)
  MUTATION fx_is_implausible '> 1' -> '> 2':
  assert_fx_magnitude_cases: FAIL 1 ("JPY->USD 0.6607 expected implausible got ok") <- CI-visible
  guard: lets JPY x100 through (translated at 0.664305): the same macro, so the case test is what guards it
  cap, all 60 keys missing: 1,886-byte message, 50 keys, "... and 10 more (assert_every_translated_currency_has_a_governed_rate lists all)"; rows unchanged

Re-review follow-ups (after 7457f5b)

• The deploy order section above now matches deploy.sh: a missing table aborts; an existing table with missing keys warns with the list and continues; the check is a dbt show through dbt_init.
• The wording on what refreshes is now exact (in deploy.sh, fx-translation.md and here): every model not downstream of gold_consolidated_trial_balance refreshes; the consolidated TB and the models that read it keep their last figures, and step 5 fails the deploy.
• A missing table always aborts, whatever is translated, because the guard and the model read it on every build. The message keeps the count of foreign-currency keys. fx_precheck() checks the table first, and reports a fresh stack (table present, nothing built) as FX_NOTHING_BUILT, which passes.

Dry run of the pre-check block (read-only; dbt_init swapped for the container's dbt; deploy.sh not run):

live konsolidat.local:        [INFO] Governed group exchange rates: every translated foreign-currency key has a usable rate.   exit 0
                              (group_exchange_rates now holds 120 adopted rows on the live stack)
table simulated missing:      [ERROR] epm_staging.group_exchange_rates does not exist (60 foreign-currency key(s) are translated today);
                                      gold_consolidated_trial_balance reads it on every build.
                              [ERROR] Run the konsol migration (konsol #174) first, then re-run this deploy.                    exit 1
table missing, nothing built: [ERROR] ... does not exist (0 foreign-currency key(s) are translated today) ... Run the konsol migration (konsol #174) first ...   exit 1
fresh stack (table present, nothing built):
                              [INFO] Governed group exchange rates: no trial balance or ownership built yet, so nothing to check.   exit 0

The "missing" cases point the source at a table that doesn't exist; the live table was never dropped. The fresh-stack case points dbt at a target schema that was never built. bash -n deploy.sh passes.

🤖 Generated with Claude Code

https://claude.ai/code/session_013WewQKFQgG7o2M3mUDPRR5

[Anonymous]

Ticket changed by: grynn-in

• status: open --> closed