Assessment 2 - Ground-Truth Audit

Cross-checks every measured count published in this assessment's deliverables against data/mock/issue-log.csv (gitignored, generated locally by the seed run - not a repo path), organized by assignment task and subtask.

Sources

Task 1 - GL Integrity and Reconciliation

task ref check expected (issue-log) measured match
01.01 arithmetic integrity arithmetic_integrity_violation=5 5 yes
01.02-08 recomputation/dim recon n/a [01] 30 keys, 297,137.40 n/a
  1. 01.02-08 has no single injected-issue tag of its own; five implementation issues were found and fixed while developing this check (docs/assessments/10-as02-financial-accounting-gl.md, issues 10.IS.02-10.IS.06, have the full diagnostic trail for each). First, the recomputation used the wrong basis for the then-available Ledger fields; second, it grouped by each transaction's actual gl_account/cost_center/legal_entity, making the dimensional check tautological. Grouping instead by the expected classification surfaced a real variance, and correcting the mapping lookup for wrong-indicator transactions settled the native-currency diagnostic at 30 keys. 10.IS.05 added GL local-SGD fields so the published aggregate no longer sums mixed native currencies, landing the SGD basis at 30 keys, 297,137.40. 10.IS.06 then closed the bridge to that figure fully - see the Task 3 bridge check below.

Task 2 - Accounting Mapping Validation

task ref check expected (issue-log) measured match
02.02 mapping effective-date expired_mapping_still_used=2 [01] 0 no [01]
02.04 overlapping mapping overlapping_effective_dates=3 8 pairs / 6 combos [02] yes
02.06 multi-GL mapping product_multiple_gl_accounts=2 4 combos [02] yes
02.01 GL_MISMATCH n/a [03] 403 rows / 319 txns n/a
02.03 MAPPING_NOT_FOUND missing_accounting_mapping=5 combos [04] 385 rows / 5 combos yes
  1. 02.02 EXPIRED_MAPPING=0 despite issue-log.csv naming 2 expired_mapping_still_used rows - both expired-row references land on a (product_code, transaction_type) pair that also carries a currently-valid mapping row, so per the design's own NOT EXISTS clause they classify as GL_MISMATCH (posted against the wrong, expired account while a valid one existed) - confirmed by direct SQL: both injected rows appear in the GL_MISMATCH set, matching what the design section already specifies for this combination.
  2. 02.04/02.06 issue-log.csv's counts (3 overlapping-date rows, 2 multi-GL rows) undercount the pairs/combos these two checks report, because a single injected conflict (e.g. one extra mapping row for P1/DEBIT) can produce more than one overlapping pair or push a combo's distinct-GL count above the multi-GL threshold - all 6 distinct (product, type) combos across both checks (P1/CREDIT, P1/DEBIT, P4/DEBIT, P5/DEBIT, P8/CREDIT, P9/CREDIT) are traceable to the 7 ref.accounting_mapping issue-log rows (2 expired_mapping_still_used + 3 overlapping_effective_dates + 2 product_multiple_gl_accounts).
  3. 02.01 has no single injected-issue tag of its own: GL_MISMATCH is 99.5% explained by the 6 mapping-conflict combos above (401/403 rows - 4 from task ref 02.06's multi-GL combos, 2 more from task ref 02.04's overlapping-window combos) plus a 2-row residual the mapping-validation deliverable reports as unexplained. Both of those 2 rows (FTX-0000158, FTX-0000660) are among Task 3's 03.03 incorrect-debit/credit-indicator population (see below): each transaction's posted indicator is wrong, its GL account correct; the mapping-validation deliverable stays as first written, since that connection only emerges from Task 3's later check.
  4. 02.03 deliberately injected (gen_assessment2()'s missing_combos, catalog issue 03 in docs/features/04-seed-mock-data.md). The 5 tagged combos (P2/DEBIT, P4/CREDIT, P6/CREDIT, P7/CREDIT, P10/CREDIT) match the 5 combos the notebook's MAPPING_NOT_FOUND query independently finds exactly

Task 3 - Investigate a Finance Variance

task ref check expected (issue-log) [01] measured match
03.01/02 duplicate / re-posted entry 15 + 8 [02] 21 rows close
03.03 incorrect debit/credit indicator 10 [03] 9 close
03.04 incorrect FX conversion 3 3 yes
03.06 posted one accounting day late 12 [04] 12 / 0 [04] partial
03.07 incorrect legal-entity allocation 6 6 yes
03.08 incorrect cost-center assignment 7 5 distinct [05] partial
n/a incorrect GL-account assignment n/a [06] 7 + 4 [06][07] n/a
  1. expected column values are issue-log row counts for duplicate_accounting_entry, posted_twice_different_id, incorrect_dr_cr_indicator, incorrect_fx_conversion, posted_one_day_late, incorrect_legal_entity, incorrect_cost_center respectively.
  2. 03.01/02 both scenarios manifest identically in the data (same business fields hashed, different transaction id) and are detected by one query per the design; issue-log.csv tags 23 rows across the two categories combined, the notebook's single detection finds 21 - a 2-row shortfall not further isolated by this run.
  3. 03.03 the swap-and-exact-match method (does a transaction's actual classification exactly match a valid mapping row under the opposite indicator, having failed to match one under its own) finds 9 of 10 tagged rows with zero false positives. The one miss, FTX-0001358, has no active mapping row at all for the opposite indicator to match against - genuinely nothing to swap-match. That same transaction is picked up by 03.08's cost-center check below (its actual cost center disagrees with the mapping row its own, uncorrected indicator points to) - a reasonable fallback attribution given the swap method can't confirm it.
  4. 03.06 the raw day-shift candidate count (12) matches the tagged count exactly - the detection step itself is accurate - but the confirmation step (requiring a candidate's amount to closely match its prior day's own shortfall) rejected all 12, for the same structural reason as 03.03's original indirect method: the Ledger is built from each transaction's own (possibly late) posting date, so the Ledger and the recomputation already agree on where a late-posted transaction lands - there is no shortfall left to match against. The root-cause deliverable reports the raw 12 as the finding, with no confirmation attempted.
  5. 03.08 of the 5 transactions flagged, 4 (FTX-0000068/0080/1341/1452) are genuinely incorrect_cost_center-tagged; the 5th (FTX-0001358) is 03.03's one unconfirmed indicator miss, picked up here instead (see above). 3 tagged incorrect_cost_center rows (FTX-0000028/0915/1427) are not found by this check at all, before or after 03.03's addition - a pre-existing gap this investigation did not further diagnose. Of the 5, only 3 (FTX-0000068/1341/1452, 40,036.00) and FTX-0001358 (9,706.73, both GL account and cost center wrong, counted separately) actually move Task 1's recomputation: FTX-0000080 (5,773.16) is a genuine incorrect_cost_center tag whose product/type combination also carries two currently-active, conflicting mapping rows, so it is never substituted by Task 1's own single_match join and moves zero dollars of the variance - the same structural exclusion already applied to FTX-0001297 below.
  6. incorrect GL-account assignment has no dedicated Task 3 check of its own (not one of the assignment's eight named categories) and no single injected-issue tag - scripts/utils/data-generators.py's gen_assessment2() posts 6 transactions to an expired mapping row's GL account (a currently-active row also covers their date) without a matching log_issue() call. 02.01's GL_MISMATCH check already reports these as part of its 319-transaction population; cross-referencing that population against Task 1's own single_match join (the ones with exactly one unambiguous current mapping row) isolates the 6 that actually move the recomputation, 53,687.44, plus FTX-0001358 above wrong on both GL account and cost center at once, 9,706.73. The exception dataset's own WRONG_GL_ACCOUNT (7 = 6 + FTX-0001358) and WRONG_COST_CENTER (4 = 3 + FTX-0001358) issue types report this same split, one row per issue type per transaction.
  7. exception dataset consistency - the exception dataset's WRONG_GL_ACCOUNT/WRONG_COST_CENTER queries were first written independently of Task 1/Task 3's own indicator-corrected mapping lookup, so 2 of 03.03's 9 flip-candidate transactions (FTX-0000158, FTX-0000660) were double-counted there as GL-account/cost-center mismatches on top of WRONG_DR_CR_INDICATOR (WRONG_GL_ACCOUNT=9, WRONG_COST_CENTER=6, exception dataset total 1227) - caught by cross-checking the exception dataset's printed counts against classification_movers' 6/3/1 split in the same notebook run, diagnosed and fixed as 10.IS.07, re-executed clean at the counts above (total 1223).

bridge check - Task 1's SGD recomputation (30 keys, 297,137.40) substitutes an expected value for exactly 16 transactions across three dimensions: 03.07's 6 legal-entity transactions (60,697.50), 6 GL-account-only transactions (53,687.44, footnote 06), 3 cost-center-only transactions (40,036.00, the corrected 03.08 population per footnote 05), and 1 transaction wrong on both GL account and cost center (FTX-0001358, 9,706.73). Confirmed zero overlap between all four groups and 03.03's indicator population. Re-running the recomputation with exactly these 16 transactions' classification reverted to actual and every other transaction unchanged returns 0 keys exceeding tolerance, 0.00 total variance - the full 297,137.40 is exactly and completely explained by this set, verified directly. Summing each group's face value and doubling it totals 328,255.34 across all four groups - 31,117.94 more than the true figure, because two groups' transactions can share every other dimension of the same five-key bucket and net against each other there. Two further real misclassifications - FTX-0001297 (03.03, 11,453.83) and FTX-0000080 (03.08, footnote 05, 5,773.16) - post under product/transaction-type combinations with more than one currently-active, conflicting mapping row, so single_match never substitutes either and they move zero dollars of Task 1's variance regardless; both are correctly excluded from the 16-transaction bridge above.

Task 4 - Create a Reconciliation Framework

task ref check expected (issue-log) measured match
04.01 total-layer metrics n/a [01] 16999151.01 / 16999151.01, PASS [02] n/a
04.01 dimensional layer n/a [03] 30 keys, 297,137.40 yes [03]
04.01 category layer n/a [03] 1,223 rows, 16-txn bridge to 0.00 yes [03]
  1. 04.01 is a design/metrics deliverable with no injected-issue tag of its own - it reuses Task 1's already-verified gl_amount expression (SUM(local_sgd_debit_movement + local_sgd_credit_movement)), the same movement-flow basis Task 1's own write-back and Task 3's bridge use. A first-pass check against the design's originally-specified gl_amount expression (SUM(local_sgd_closing_balance)) returned 14,826,335.19 against a source amount of 16,999,151.01 - the same chained-ledger stock-vs-flow mismatch 01.02-08's footnote already diagnosed once for Task 1, reproduced here because the design's gl_amount definition had not been corrected to match. Corrected before any notebook cell was written; the published metric reconciles exactly.
  2. measured column reads source amount / GL amount, both SGD, then the resulting status.
  3. dimensional/category layer rows have no injected-issue tag of their own - they reuse Task 1's own dimension_summaries and Task 3's own findings/bridge outputs directly, so "match" here means these two rows reproduce 01.02-08 and the Task 3 bridge check below exactly.

All measured values are read live via Spark SQL/PySpark against postgres in the notebook section cited above.