#157 Every D365 voucher must net to zero at staging; drop the demo BU hack (#155)

[Anonymous]

Originally created by: grynn-in

Closes [#155]. The comment on the issue explains why this detects instead of re-deriving the sign.

What was wrong

The demo generator shipped GL amounts unsigned, with the sign only in IsCredit. Silver derives debit/credit from the sign (#112), so every credit was booked as a debit and every trial balance went one-sided. The only signal was assert_silver_gl_debit_credit_balance, three layers down and by entity-year, which was carried for weeks as a "demo-data tension".

What changes

• tests/assert_d365_gl_vouchers_balance.sql: fails the build when a D365 voucher doesn't net to zero in stg_d365_fo__gl_entries, and names it. It groups by voucher alone, so entity-less headers are included.
• It does not re-derive the sign from IsCredit. On real D365 that flag can disagree with the sign on storno lines (#112/#118, assert_is_credit_retired), and a correctly signed voucher nets to zero either way.
• stg_d365_fo__gl_entries: the with_demo_bu CTE is deleted. It hard-coded AMUS business-unit splits "so MGMT_DEMO rollups are testable", which was demo code in a production adapter; the demo is gone (#156, konsol#127).
• generate_demo_data.py: its docstring now warns that its GL and budget amounts are unsigned. Its budget 'No' column is IncludeInCashFlowForecast, not a sign.

Verification

• Live run on the local stack, which still holds the unsigned demo raw data until the planned wipe: to follow. The new test is expected to fail there, and that failure is the proof that it detects the problem.
• CI: dbt parse.

Open question for the owner

Delete generate_demo_data.py outright? It produces only data this project no longer loads, and its output is wrong.

🤖 Generated with Claude Code

https://claude.ai/code/session_013WewQKFQgG7o2M3mUDPRR5

[Anonymous]

Originally posted by: grynn-in

Live proof, and a bug the proof caught (c8d262b)

The first push didn't build. Deleting with_demo_bu left joined as (…), in front of the final SELECT, which ClickHouse rejects with a SYNTAX_ERROR. CI passed because dbt parse doesn't validate SQL. Merging would have broken the D365 staging view and everything downstream. c8d262b removes the comma.

Run on the local stack, from a copy of this branch's dbt project outside the deploy checkout's bind mount, against the unsigned demo raw data that stays until the wipe:

1 of 4 FAIL 927 assert_d365_gl_vouchers_balance
2 of 4 PASS assert_is_credit_retired
3 of 4 PASS test_canonical_gl_entries_not_null
4 of 4 PASS test_canonical_gl_entries_schema

• stg_d365_fo__gl_entries rebuilt: OK. The live view no longer mentions AMUS (0 mentions).
• AMUS business units now come straight from D365: SERVICES 290; (the raw JSON has 290 × SERVICES; the hack had invented the CORP / MANUFACTURING splits).
• FAIL 927 is the intended result. All 927 demo vouchers are unsigned, confirmed independently (927 of 927 don't net to zero). On real, signed data, or on an empty stack after the wipe, the test passes.

🤖 Generated with Claude Code

[Anonymous]

Ticket changed by: grynn-in

• status: open --> closed