#76 fix(dbt): map demo entities to the Standard fiscal calendar so the fiscal-calendar join matches

[Anonymous]

Originally created by: grynn-in

Root cause

In silver_gl_entries, fiscal_year/period are derived by joining the GL accounting date to a date-expanded fiscal calendar (silver_fiscal_periods), keyed on calendar_id = coalesce(efc.fiscal_calendar_id, 'Fiscal') from the entity_fiscal_calendars seed.

The two sides of that join used different calendar identifiers, so it never matched for the demo entities (AMUS / AMDE / AMHQ):

• entity_fiscal_calendars.fiscal_calendar_id for the demo entities = Fiscal
• The only fiscal calendar actually loaded from raw D365 (epm_raw.FiscalCalendarYears.Calendar) = Standard (one calendar, FY2024 Jan–Dec). This flows through stg_d365_fo__fiscal_calendar_years → bronze_fiscal_calendar_years → silver_fiscal_periods.calendar_id, which only ever contains Standard.

Because silver_fiscal_periods had zero Fiscal rows, fp.fiscal_year / fp.fiscal_period were always NULL and the values fell through to the posting-date fallback (toYear/toMonth). That fallback is coincidentally correct for calendar fiscal years (the demo), but wrong for offset fiscal years because the calendar-derived path was effectively dead.

This is the follow-up noted in PR [#73], which fixed the FY0/P0 symptom with nullIf(fp.fiscal_year,0) but left the calendar join itself dead.

The fix

Determined the authoritative side from the raw D365 data before editing: epm_raw.FiscalCalendarYears contains exactly one calendar, Standard. The raw/bronze/silver chain faithfully carries Standard; the seed was the incorrect side, referencing a calendar (Fiscal) that does not exist in the loaded data.

Minimal change at the correct (seed) layer: point the three demo entities at the calendar that actually exists.

-AMHQ,Fiscal      +AMHQ,Standard
-AMUS,Fiscal      +AMUS,Standard
-AMDE,Fiscal      +AMDE,Standard

No change to silver_gl_entries.sql (does not depend on / conflict with PR [#73]'s nullIf). Branched off main.

Verification (D365-only build)

Before: silver_fiscal_periods had 0 rows with calendar_id='Fiscal'; replicating the join gave 0 demo GL rows matched via calendar → all values came from the date fallback.

After:

• silver_fiscal_periods now has 12 Standard periods that the join keys against.
• Replicating the production join: 862 / 862 demo GL rows now get a real calendar match (fp.fiscal_year populated), up from 0.
• silver_gl_entries: all 862 rows → fiscal_year=2024, fiscal_period 1–12, now calendar-derived rather than via toYear/toMonth.
• No regression: gold_trial_balance still spans fiscal_year=2024, periods 1–12 (distinct periods [1..12]).
• Excel API still returns a value: epm_batch for AMHQ / 2024 / P1 / acct 1010 → {"values":[3045306.0]}.
• No new dbt ERRORs: the build still reports the same 4 pre-existing test failures (assert_dynamic_step_count, assert_reciprocal_converges, assert_tier_total_equals_pool, assert_ownership_uses_effective_date) — all in the allocation/consolidation area, none referencing the fiscal calendar, and confirmed to fail identically with the original seed.

🤖 Generated with Claude Code

[Anonymous]

Originally posted by: grynn-in

Independent review — APPROVE

Root cause is correct and the fix is at the right layer. The raw D365 demo loads exactly one fiscal calendar (epm_raw.FiscalCalendarYears.Calendar = 'Standard'), which flows faithfully to silver_fiscal_periods.calendar_id. The entity_fiscal_calendars seed pointed the demo entities at a 'Fiscal' calendar that doesn't exist in the demo data, so the join could never match. Aligning the seed to the actual data (not the reverse) is the right call. Diff is minimal — only the 3 entities that have GL data (AMHQ/AMUS/AMDE).

Verified independently: calendar join keys 0→12, demo GL rows match 862/862 via the calendar (not the date fallback), gold spans 2024 P1–12, Excel API returns 3045306, no new dbt errors.

Interaction with [#73]: complementary and both worth keeping. With this PR the join matches and fp.fiscal_year is a real 2024 (non-zero), so [#73]'s nullIf(fp.fiscal_year,0) passes it straight through; [#73] still protects entities/dates where the calendar join legitimately misses. No conflict (different files: seed vs silver model).

Observation (non-blocking, data hygiene): ~60 other entities in the seed still reference calendars that aren't in the demo raw data (Fiscal ×52, Fiscal_CN, Fisscal_IN [sic], etc.). They're inert today (no GL rows), but any of them gaining data would hit the same FY0 path. Worth a follow-up to either seed those calendars or reconcile the names — not needed for this fix.

[Anonymous]

Ticket changed by: grynn-in

• status: open --> closed