#155 Every GL credit is booked as a debit: D365 staging trusts a sign the raw data doesn't carry (regression from #118, 29 Jun)

[Anonymous]

Originally created by: grynn-in

What happened

I measured the local stack's data directly (12 Sep 2026) instead of trusting the known-baseline framing. epm_gold.gold_trial_balance has 0 rows with a credit across all 7 entities and all 12 periods of 2024. For each entity, total debits equal the whole trial balance: AMDE 54,212,086 debits and 0 credits, AMHQ 48,690,354 and 0, JPMF 7,600,888,746 and 0, and so on.

So every revenue, liability and equity balance is booked as a debit. Every trial balance is one-sided, and every consolidated number built on them is meaningless. assert_silver_gl_debit_credit_balance fails 7 (one row per entity-year) and has been carried as a "pre-existing demo-data tension".

Root cause

The raw data is correct. The credit sign lives only in IsCredit.

On epm_raw.general_journal_account_entry_bi_entities, the table d365_raw points at:

• 1,910 lines, 0 with a negative AccountingCurrencyAmount; IsCredit = 'Yes' on 962
• 927 vouchers (GeneralJournalEntry): 0 net to zero read as signed; 927/927 net to zero with IsCredit applied (residual 0)
• The older CamelCase landing GeneralJournalAccountEntryBiEntities behaves identically: 0 negatives, 419/419 vouchers balance only with the flag

The demo generator writes magnitudes plus a flag, and has since 6523605 (26 Jun). scripts/generate_demo_data.py:511-512:

is_credit = "Yes" if bal < 0 else "No"
amt = abs(bal)

The staging adapter passes the amount through unsigned. dbt_project/models/staging/d365_fo/stg_d365_fo__gl_entries.sql:38:

entries.AccountingCurrencyAmount as amount,

and silver creates a credit only from a negative amount (silver_gl_entries.sql:44-47), so credit_amount is always 0.

4caf9aa (29 Jun, [#118] "retire dead is_credit") removed the only code that applied the flag:

-            when lower(trim(trim(both '"' from trim(toString(coalesce(entries.IsCredit, '')))))) in ('yes', 'true', '1') then 1
-            else 0
-        end as is_credit,

What the pipeline does NOT do: apply IsCredit to the amount anywhere.

Correction to an earlier finding

• 4caf9aa states "VERIFIED PASS against live data 2026-06-29". That can't have held for this data, because the abs-plus-flag generator was already in place from 26 Jun. The likeliest explanation is that the build was silently producing no models until 6e7413e / [#139] (10 Sep), so the test passed on empty tables. That is inferred, not proven.
• konsol's HANDOFF lists this failure as a demo-data tension, with "AMHQ's local GL out of balance by 17.4m". It is this bug. All 7 entities are unbalanced in every period, and 17.4m is just AMHQ's worst single month. The CTA test failing at 24 when the chain runs directly follows from it too.

Suggested fix

1. In stg_d365_fo__gl_entries.sql, derive the sign from the flag, idempotently:
   if(<quote-stripped IsCredit> = 'yes', -abs(AccountingCurrencyAmount), abs(AccountingCurrencyAmount)) as amount, using the exact parser 4caf9aa removed. It is correct for this data and for real D365, where the amount is already signed. Apply the same to ReportingCurrencyAmount and TransactionCurrencyAmount.
2. Test at the source. Add a staging test that every GeneralJournalEntry voucher nets to 0 in stg_gl_entries, so a sign regression fails where it's introduced instead of three layers down.
3. Rebuild from bronze, which is incremental: dbt run --select bronze_general_journal_account_entries+ --full-refresh.
4. Adjacent, same shape. stg_d365_fo__budget_entries.sql:22 takes AccountingCurrencyAmount the same way, and the generator writes every budget line as abs(amount), 'No' (generate_demo_data.py:918, :1453). A revenue budget (-int(...), line 896) loses its sign at the source, so the generator needs fixing for budgets as well.

Repro

SELECT count() AS vouchers,
       countIf(abs(signed) < 0.01)  AS balanced_as_signed,
       countIf(abs(flagged) < 0.01) AS balanced_with_flag
FROM (SELECT GeneralJournalEntry,
             sum(AccountingCurrencyAmount) AS signed,
             sum(if(lower(trim(both '"' from trim(coalesce(IsCredit,'')))) = 'yes',
                    -abs(AccountingCurrencyAmount), abs(AccountingCurrencyAmount))) AS flagged
      FROM epm_raw.general_journal_account_entry_bi_entities GROUP BY 1);
-- 927 | 0 | 927
SELECT countIf(period_credit > 0) FROM epm_gold.gold_trial_balance;   -- 0

🤖 Generated with Claude Code

[Anonymous]

Originally posted by: grynn-in

Correction to an earlier finding: the unsigned source is the demo generator, not necessarily D365

The fix proposed above, deriving the sign from IsCredit in stg_d365_fo__gl_entries, could make things worse for real D365.

• The staging code follows [#112]'s premise. Real D365 OData carries a signed AccountingCurrencyAmount, and IsCredit can disagree with the sign on storno/reversal lines. That's why [#118] retired is_credit, and tests/assert_is_credit_retired.sql pins that down. if(IsCredit, -abs(x), abs(x)) would flip real storno debits. I have no real D365 data on this stack to confirm the storno semantics either way.
• The only unsigned producer is scripts/generate_demo_data.py. For the GL it writes amt = abs(bal) with the sign only in IsCredit (:515-516, :1229-1230). For budgets it writes abs(amount) with no sign information at all: its 'No' column is IncludeInCashFlowForecast, and all 672 raw budget rows are non-negative.
• #156 stopped loading the generator's output, so a fresh stack no longer ingests it.

Revised fix: detect, don't guess

1. A staging test that every D365 voucher (general_journal_entry_recid) nets to zero. An unsigned feed from any source then fails at staging, naming the voucher, instead of silently booking every credit as a debit three layers down. It holds whatever real D365 does with storno lines, because a correctly signed voucher always nets to zero.
2. Delete the with_demo_bu CTE in the same model. It hard-codes AMUS business-unit splits "so MGMT_DEMO rollups are testable", which is demo code in a production adapter.
3. Warn in the generator's docstring that its GL and budget amounts are unsigned and fail (1). Deleting the generator is a separate call for the owner.

On the current local stack, which still holds the demo raw rows until the planned wipe, (1) is expected to fail. That failure is the proof that it detects the problem.

🤖 Generated with Claude Code

[Anonymous]

Ticket changed by: grynn-in

• status: open --> closed