#65 fix(silver): derive fiscal period from accounting_date when calendar join misses

[Anonymous]

Originally created by: pyy3

Problem

silver_gl_entries had fiscal_year = 0 / fiscal_period = 0 on all 51,901 GL rows, collapsing the period dimension platform-wide (trial balance, balance sheet, bs_movement, P&L, quarterly/YTD/variance, consolidation — every period-based model).

Root cause (regression)

Commit f969c3f ("scope fiscal calendar join to entity's calendar, eliminating 12x row fan-out") correctly scoped the fiscal_dates join by adding and fp.calendar_id = coalesce(efc.fiscal_calendar_id, 'Fiscal'). But entity_fiscal_calendars maps entities to Fiscal/Fiscal_*, while the only calendar actually loaded (bronze_fiscal_calendar_years → silver_fiscal_periods) is Standard. So Fiscal ≠ Standard → the join matches zero rows.

A ClickHouse LEFT JOIN miss fills fp.fiscal_year/fp.fiscal_period with the column default 0, not NULL, so coalesce(fp.fiscal_year, extract_year(accounting_date)) returns 0 and never reaches the accounting_date-derived fallback. The period collapse then failed silently (no NULLs, just 0).

Fix

Replace the two coalesce(fp.x, fallback) with if(fp.x != 0, fp.x, fallback) so the accounting_date fallback fires on a join miss:

if(fp.fiscal_year  != 0, fp.fiscal_year,  {{ extract_year('gae.accounting_date') }})  as fiscal_year,
if(fp.fiscal_period != 0, fp.fiscal_period, {{ extract_month('gae.accounting_date') }}) as fiscal_period,

When a real, matching fiscal calendar is loaded, fp.* is non-zero and used as before; otherwise the period is derived from the posting date. No fan-out is reintroduced (the calendar_id scoping stays).

Verification (live epm/epm_gold)

• silver_gl_entries: periods now populate — 2023 (25,510 rows) / 2024 (26,391).
• gold_bs_movement: real periods 1–12 (was period-0-only).
• Rebuilt silver_gl_entries+ (27 models) cleanly; assert_trial_balance_balances and assert_silver_gl_debit_credit_balance pass.

Follow-ups (separate)

• #64 — period_net_amount sums magnitudes (debit+credit), not signed net (debit−credit); surfaced once periods were real. Pre-existing, platform-wide; tracked separately.

🤖 Generated with Claude Code

[Anonymous]

Ticket changed by: grynn-in

• status: open --> closed