Glassbox · data quality scorecard

What the published data can and cannot support

Each row below is a documented property of the source datasets as published — duplicate rows, cumulative amendment values, missing identifiers, roll-up records. These are characteristics of the data, not findings about any recipient, department or program. Every canonical query in Glassbox applies the listed guard; the scorecard records how much of the corpus each guard touches.

Scorecard computed Thu, 24 Sep 2026 22:49:53 GMT · 35 issues · 18 active · 12 mitigated · 5 resolved

F · Federal grants & contributions

fed.grants_contributions · 11 issues

ID	Issue	Rows affected	Dollars affected	Status	Guard applied
F-1	ref_number collisions across distinct recipients The same ref_number covers multiple unrelated recipients, so ref_number alone cannot identify an agreement.	46,817	$79.5B	Mitigated	Every agreement key is (ref_number, COALESCE(bn, legal_name, _id)) — the F-1 key — in the CTE path, app.agreement_current and every detector.
F-2	Duplicate (ref_number, amendment_number) rows Within one ref_number the same amendment_number appears more than once — unambiguous publisher duplicates.	31,415	$14.9B	Active	Chains are ordered by (amendment_number, _id) so the winner is deterministic; app.agreement_current.has_duplicate_rows and the detectors' duplicateRows flag discount evidence strength.
F-3	agreement_value is cumulative — naive SUM over-counts Every amendment row restates the running total; summing raw rows over-counts by the amendment rows' values.	336,347	$126.6B	Mitigated	All totals use the current row per F-1 key (highest amendment_number): the CTE path or app.agreement_current. Dollars = naive SUM − current commitment.
F-4	Negative agreement_value TBS requires value > 0; publishers use negatives as termination / reversal markers, almost always on amendment rows.	5,135	$-5.0B	Active	Base filter agreement_value > 0 everywhere; app.agreement_current.has_negative_rows and the negativeValues flag discount evidence strength.
F-5	Zero agreement_value Rows carrying exactly 0 violate the TBS validation rule.	11,609	$0	Active	Base filter agreement_value > 0 (and ≥ 1 in overview totals).
F-6	recipient_business_number format polyglot Besides 9- and 15-character BNs the column carries placeholders ('0', '-', all-zeros) and malformed strings.	28,880	$17.4B	Mitigated	identity.ts normalizeBn() maps placeholder tokens to NULL; app.agreement_current.is_placeholder_bn; detectors carry placeholderBn / missingBn flags.
F-7	Missing BN where one is expected (recipient_type N / F) Not-for-profit and for-profit recipients should carry a BN; many rows do not.	49,545	$35.7B	Active	Ghost-capacity detector surfaces the largest cases; name-only identity is discounted in every other detector.
F-8	Missing agreement_end_date agreement_end_date is mandatory per TBS but NULL on ~15% of rows.	190,081	$148.1B	Active	No window is anchored on end dates; rolling windows use agreement_start_date, which is populated on every row.
F-9	agreement_end_date before agreement_start_date End date precedes the start date.	951	$498M	Active	End dates are displayed but never used in a calculation.
F-10	agreement_number reused as a program code Free-text agreement_number is reused across thousands of unrelated grants and cannot be a join key.	24	$1.5B	Mitigated	agreement_number is never used as a key; count = agreement_number values shared by ≥ 100 distinct recipients.
F-11	Amendments can reduce agreement value About 8% of amendments with a prior value decrease it; analysts assuming monotone growth mis-read them.	11,897	$7.2B	Mitigated	Current value is always the highest-amendment row regardless of direction; dollars = total reduction across decreasing amendments.

C · CRA registered charities

cra.* · 11 issues

ID	Issue	Rows affected	Dollars affected	Status	Guard applied
C-1	T3010 arithmetic impossibilities Filings that fail one of ten identities printed on the T3010 form (e.g. line 5100 = 4950 + 5045 + 5050).	54,010	$111.8B	Active	Surfaced from cra.t3010_impossibilities; dollars = sum of the per-row severity ($ impact).
C-2	T3010 plausibility flags (unit-error candidates) Money fields that are very likely unit errors (dollars vs thousands) rather than form violations.	1,075	—	Active	Loop and gift evidence carries the unitErrorSuspect discount when a flagged filing is involved.
C-3	Qualified-donee BN→name mismatches Donee BN and donee name disagree with the register (rebrands, acronyms, DAF platforms, typos, malformed or unregistered BNs).	127,725	$9.0B	Mitigated	Gift loops use only well-formed donee BNs (^\d{9}RR\d{4}$); dollars = gifts on rows other than MINOR_VARIANT that cannot be joined programmatically.
C-4	Qualified-donee rows broken on their face NULL or malformed donee BN, NULL/zero/negative amount, or empty donee name.	200,475	$9.8B	Active	Gift-loop edges require a well-formed donee BN and total_gifts ≥ $5,000; dollars = gifts on rows with NULL or malformed donee BN.
C-5	Four cra_identification columns are 100% NULL Schema should drop or document them.	—	—	Active	Not computable here: the upstream note does not name the columns and Glassbox reads only bn, legal_name, province, fiscal_year, designation from this table.
C-6	cra_directors NULL rates at_arms_length (5%), start_date (10%) and first_name (0.1%) are missing; shared-director detection silently drops cycles.	372,095	—	Active	Director overlap is not used by any live detector; count = director rows missing at_arms_length, start_date or a name.
C-7	Historical legal names not preserved CRA backfills the current legal name onto historical years; only ~1.4% of BNs show any name change.	1,170	—	Active	cra_identification.legal_name is treated as current-state, never as a historical name; count = BNs with more than one recorded name.
C-8	2024 T3010 form revision (v24 → v27 field migration) Some fields exist only pre-2024, others only from 2023 onward, producing confusing NULL patterns by year.	—	—	Active	Not a countable defect — a schema-era boundary. Glassbox reads no version-specific T3010 field.
C-10	cra_political_activity_funding is empty The table has no rows; investigation needed upstream.	0	—	Active	Table is not read by Glassbox; count = rows present (0 = defect persists).
C-11	Well-formed donee BNs absent from the charity register Qualified donees include municipalities, universities and First Nations councils that are not charities, plus revoked or pre-2020 registrations.	20,192	$2.3B	Mitigated	Loop nodes not in cra_identification keep their donor-written name; the unregisteredDonee flag discounts strength. Dollars = gifts to such BNs.
C-12	Johnson cycles were non-simple (resolved upstream) 06-johnson-cycles.js emitted 158 cycles with a repeated vertex; fixed 2026-04-19.	0	—	Resolved	Verification: count = cycles whose path repeats a vertex (expected 0). Glassbox recomputes its own simple cycles in app.gift_loops regardless.

A · Alberta grants, contracts & sole-source

ab.* · 13 issues

ID	Issue	Rows affected	Dollars affected	Status	Guard applied
A-1	ab_grants.fiscal_year non-canonical (resolved upstream) Three formats coexisted before the 2026-04-19 normalisation; now identical to display_fiscal_year.	0	—	Resolved	Verification: count = rows where fiscal_year ≠ display_fiscal_year (expected 0). Glassbox groups by display_fiscal_year.
A-2	ab_grants.lottery is boolean-as-text without a CHECK constraint Only 'True'/'False' observed, but nothing enforces the value set.	166,256	—	Active	Glassbox never filters on lottery; count = rows whose lottery value is outside {'True','False',NULL} (drift detector).
A-3	ab_sole_source.special semantics undocumented The publisher does not document what the flag means.	—	—	Active	Not computable: a documentation gap, not a data defect. The column is displayed as opaque.
A-4	ab_sole_source.permitted_situations letter codes not publicly keyed Codes a–l, z are not mapped to Alberta's numbered list by the publisher.	—	—	Active	Not computable: mapping is a positional inference; codes are shown verbatim.
A-5	AB aggregate tables mix by_fiscal_year and all_years rows ab_grants_ministries / _programs double-count unless aggregation_type is filtered.	6,436	$479.3B	Mitigated	Glassbox aggregates from ab_grants directly and never reads the rollup tables; count = all_years rows, dollars = their total_amount.
A-6	ab_grants negative amounts (reversals / corrections) Alberta's convention: negative rows are reversals, not errors; naive sums double-count them.	50,381	$-13.1B	Mitigated	AB search and comparables filter amount > 0 (A-6 landmine in retrieval.ts).
A-7	AB per-period aggregate tables stale (resolved upstream) Rollups were rebuilt from ab_grants on 2026-04-19.	0	—	Resolved	Verification: count = fiscal years whose ab_grants_fiscal_years total differs from SUM(ab_grants.amount) (expected 0).
A-8	ab_grants.mongo_id broke idempotent loads (resolved upstream) Column dropped 2026-04-19.	0	—	Resolved	Verification: count = mongo_id columns still present on ab.ab_grants (expected 0).
A-9	CSV-sourced AB rows have NULL lottery / version / timestamps FY 2024-25 and 2025-26 rows carry NULL for five columns; lottery filtering is unusable for FY ≥ 2023-24.	320,284	—	Active	Glassbox never filters on lottery or version.
A-10	Publisher roll-up rows (recipient IS NULL) Programme-level aggregates published as single rows with no recipient — ~$25B in the two newest years.	1,409	$63.3B	Mitigated	Recipient-level AB analysis filters recipient IS NOT NULL; the omitted aggregate is surfaced separately.
A-11	Ministry rename / comma drift (partially resolved upstream) Commas stripped and JOBS variants canonicalised; genuine cabinet renames remain.	0	—	Mitigated	Verification: count = rows with a comma in ministry or business_unit_name (expected 0); longitudinal joins use ministries_history.
A-12	ASHC ministry / business-unit swap (resolved upstream) Every ALBERTA SOCIAL HOUSING CORPORATION row folded into ASSISTED LIVING AND SOCIAL SERVICES.	0	—	Resolved	Verification: count = rows still naming ALBERTA SOCIAL HOUSING CORPORATION (expected 0).
A-13	Exact-duplicate rows and perfect reversal pairs Repeated identical tuples and ±offsetting pairs make COUNT(*) unreliable as a payment count.	131,890	$52.3B	Mitigated	AB comparables DISTINCT ON the full (ministry, business_unit_name, recipient, program, amount, payment_date) tuple and drop amount ≤ 0. Count = excess duplicate rows + reversal pairs; dollars = excess dollars + one-side reversal magnitude.

Counts and dollars are the portion of each source table touched by the guard, measured at refresh time. “Active” means the property is present in the published data and guarded at query time; “mitigated” means a canonical table removes it upstream of every query; “resolved” means the publisher corrected it. None of these rows describe the conduct of any entity named in the corpus.

Loading the transparency dashboard

What the published data can and cannot support

Scorecard computed Thu, 24 Sep 2026 22:49:53 GMT · 35 issues · 18 active · 12 mitigated · 5 resolved

Issue

Rows affected

Dollars affected

Status

Guard applied

F-1

ref_number collisions across distinct recipients

The same ref_number covers multiple unrelated recipients, so ref_number alone cannot identify an agreement.

46,817

$79.5B

Mitigated

Every agreement key is (ref_number, COALESCE(bn, legal_name, _id)) — the F-1 key — in the CTE path, app.agreement_current and every detector.

F-2

Duplicate (ref_number, amendment_number) rows

Within one ref_number the same amendment_number appears more than once — unambiguous publisher duplicates.

31,415

$14.9B

Active

Chains are ordered by (amendment_number, _id) so the winner is deterministic; app.agreement_current.has_duplicate_rows and the detectors' duplicateRows flag discount evidence strength.

F-3

agreement_value is cumulative — naive SUM over-counts

Every amendment row restates the running total; summing raw rows over-counts by the amendment rows' values.

336,347

$126.6B

Mitigated

All totals use the current row per F-1 key (highest amendment_number): the CTE path or app.agreement_current. Dollars = naive SUM − current commitment.

F-4

Negative agreement_value

TBS requires value > 0; publishers use negatives as termination / reversal markers, almost always on amendment rows.

5,135

$-5.0B

Active

Base filter agreement_value > 0 everywhere; app.agreement_current.has_negative_rows and the negativeValues flag discount evidence strength.

F-5

Zero agreement_value

Rows carrying exactly 0 violate the TBS validation rule.

11,609

Active

Base filter agreement_value > 0 (and ≥ 1 in overview totals).

F-6

recipient_business_number format polyglot

Besides 9- and 15-character BNs the column carries placeholders ('0', '-', all-zeros) and malformed strings.

28,880

$17.4B

Mitigated

identity.ts normalizeBn() maps placeholder tokens to NULL; app.agreement_current.is_placeholder_bn; detectors carry placeholderBn / missingBn flags.

F-7

Missing BN where one is expected (recipient_type N / F)

Not-for-profit and for-profit recipients should carry a BN; many rows do not.

49,545

$35.7B

Active

Ghost-capacity detector surfaces the largest cases; name-only identity is discounted in every other detector.

F-8

Missing agreement_end_date

agreement_end_date is mandatory per TBS but NULL on ~15% of rows.

190,081

$148.1B

Active

No window is anchored on end dates; rolling windows use agreement_start_date, which is populated on every row.

F-9

agreement_end_date before agreement_start_date

End date precedes the start date.

951

$498M

Active

End dates are displayed but never used in a calculation.

F-10

agreement_number reused as a program code

Free-text agreement_number is reused across thousands of unrelated grants and cannot be a join key.

$1.5B

Mitigated

agreement_number is never used as a key; count = agreement_number values shared by ≥ 100 distinct recipients.

F-11

Amendments can reduce agreement value

About 8% of amendments with a prior value decrease it; analysts assuming monotone growth mis-read them.

11,897

$7.2B

Mitigated

Current value is always the highest-amendment row regardless of direction; dollars = total reduction across decreasing amendments.

Issue

Rows affected

Dollars affected

Status

Guard applied

C-1

T3010 arithmetic impossibilities

Filings that fail one of ten identities printed on the T3010 form (e.g. line 5100 = 4950 + 5045 + 5050).

54,010

$111.8B

Active

Surfaced from cra.t3010_impossibilities; dollars = sum of the per-row severity ($ impact).

C-2

T3010 plausibility flags (unit-error candidates)

Money fields that are very likely unit errors (dollars vs thousands) rather than form violations.

1,075

—

Active

Loop and gift evidence carries the unitErrorSuspect discount when a flagged filing is involved.

C-3

Qualified-donee BN→name mismatches

Donee BN and donee name disagree with the register (rebrands, acronyms, DAF platforms, typos, malformed or unregistered BNs).

127,725

$9.0B

Mitigated

Gift loops use only well-formed donee BNs (^\d{9}RR\d{4}$); dollars = gifts on rows other than MINOR_VARIANT that cannot be joined programmatically.

C-4

Qualified-donee rows broken on their face

NULL or malformed donee BN, NULL/zero/negative amount, or empty donee name.

200,475

$9.8B

Active

Gift-loop edges require a well-formed donee BN and total_gifts ≥ $5,000; dollars = gifts on rows with NULL or malformed donee BN.

C-5

Four cra_identification columns are 100% NULL

Schema should drop or document them.

—

Active

Not computable here: the upstream note does not name the columns and Glassbox reads only bn, legal_name, province, fiscal_year, designation from this table.

C-6

cra_directors NULL rates

at_arms_length (5%), start_date (10%) and first_name (0.1%) are missing; shared-director detection silently drops cycles.

372,095

—

Active

Director overlap is not used by any live detector; count = director rows missing at_arms_length, start_date or a name.

C-7

Historical legal names not preserved

CRA backfills the current legal name onto historical years; only ~1.4% of BNs show any name change.

1,170

—

Active

cra_identification.legal_name is treated as current-state, never as a historical name; count = BNs with more than one recorded name.

C-8

2024 T3010 form revision (v24 → v27 field migration)

Some fields exist only pre-2024, others only from 2023 onward, producing confusing NULL patterns by year.

—

Active

Not a countable defect — a schema-era boundary. Glassbox reads no version-specific T3010 field.

C-10

cra_political_activity_funding is empty

The table has no rows; investigation needed upstream.

—

Active

Table is not read by Glassbox; count = rows present (0 = defect persists).

C-11

Well-formed donee BNs absent from the charity register

Qualified donees include municipalities, universities and First Nations councils that are not charities, plus revoked or pre-2020 registrations.

20,192

$2.3B

Mitigated

Loop nodes not in cra_identification keep their donor-written name; the unregisteredDonee flag discounts strength. Dollars = gifts to such BNs.

C-12

Johnson cycles were non-simple (resolved upstream)

06-johnson-cycles.js emitted 158 cycles with a repeated vertex; fixed 2026-04-19.

—

Resolved

Verification: count = cycles whose path repeats a vertex (expected 0). Glassbox recomputes its own simple cycles in app.gift_loops regardless.

Issue

Rows affected

Dollars affected

Status

Guard applied

A-1

ab_grants.fiscal_year non-canonical (resolved upstream)

Three formats coexisted before the 2026-04-19 normalisation; now identical to display_fiscal_year.

—

Resolved

Verification: count = rows where fiscal_year ≠ display_fiscal_year (expected 0). Glassbox groups by display_fiscal_year.

A-2

ab_grants.lottery is boolean-as-text without a CHECK constraint

Only 'True'/'False' observed, but nothing enforces the value set.

166,256

—

Active

Glassbox never filters on lottery; count = rows whose lottery value is outside {'True','False',NULL} (drift detector).

A-3

ab_sole_source.special semantics undocumented

The publisher does not document what the flag means.

—

Active

Not computable: a documentation gap, not a data defect. The column is displayed as opaque.

A-4

ab_sole_source.permitted_situations letter codes not publicly keyed

Codes a–l, z are not mapped to Alberta's numbered list by the publisher.

—

Active

Not computable: mapping is a positional inference; codes are shown verbatim.

A-5

AB aggregate tables mix by_fiscal_year and all_years rows

ab_grants_ministries / _programs double-count unless aggregation_type is filtered.

6,436

$479.3B

Mitigated

Glassbox aggregates from ab_grants directly and never reads the rollup tables; count = all_years rows, dollars = their total_amount.

A-6

ab_grants negative amounts (reversals / corrections)

Alberta's convention: negative rows are reversals, not errors; naive sums double-count them.

50,381

$-13.1B

Mitigated

AB search and comparables filter amount > 0 (A-6 landmine in retrieval.ts).

A-7

AB per-period aggregate tables stale (resolved upstream)

Rollups were rebuilt from ab_grants on 2026-04-19.

—

Resolved

Verification: count = fiscal years whose ab_grants_fiscal_years total differs from SUM(ab_grants.amount) (expected 0).

A-8

ab_grants.mongo_id broke idempotent loads (resolved upstream)

Column dropped 2026-04-19.

—

Resolved

Verification: count = mongo_id columns still present on ab.ab_grants (expected 0).

A-9

CSV-sourced AB rows have NULL lottery / version / timestamps

FY 2024-25 and 2025-26 rows carry NULL for five columns; lottery filtering is unusable for FY ≥ 2023-24.

320,284

—

Active

Glassbox never filters on lottery or version.

A-10

Publisher roll-up rows (recipient IS NULL)

Programme-level aggregates published as single rows with no recipient — ~$25B in the two newest years.

1,409

$63.3B

Mitigated

Recipient-level AB analysis filters recipient IS NOT NULL; the omitted aggregate is surfaced separately.

A-11

Ministry rename / comma drift (partially resolved upstream)

Commas stripped and JOBS variants canonicalised; genuine cabinet renames remain.

—

Mitigated

Verification: count = rows with a comma in ministry or business_unit_name (expected 0); longitudinal joins use ministries_history.

A-12

ASHC ministry / business-unit swap (resolved upstream)

Every ALBERTA SOCIAL HOUSING CORPORATION row folded into ASSISTED LIVING AND SOCIAL SERVICES.

—

Resolved

Verification: count = rows still naming ALBERTA SOCIAL HOUSING CORPORATION (expected 0).

A-13

Exact-duplicate rows and perfect reversal pairs

Repeated identical tuples and ±offsetting pairs make COUNT(*) unreliable as a payment count.

131,890

$52.3B

Mitigated

AB comparables DISTINCT ON the full (ministry, business_unit_name, recipient, program, amount, payment_date) tuple and drop amount ≤ 0. Count = excess duplicate rows + reversal pairs; dollars = excess dollars + one-side reversal magnitude.