Bottom line: the federal grants & cooperative agreements (G&C) system structurally disadvantages smaller and nonprofit-style recipients — through instrument choice, incumbency, award-size burden, and mandate design — with almost no offsetting narrative of deservingness.
- Instrument bias (Observation 1, lead finding): 93.2% of dollars reaching organizational recipients arrive as *contributions* rather than grants. The 147,683-agreement contribution base averages $517.6K/award vs. just $138.2K for grants — nonprofits are steered toward the costlier, less favorable instrument. - Entrenched incumbency (Observation 2): Within a 69,169-organization disclosure tier claiming $105.217B — 13.03% of an $807.602B lifetime corpus (Table 1) — dollar concentration among the same recipients is visible from the earliest post-baseline readings, i.e., lock-in is structural, not something that emerges over time. - Regressive compliance load (Observation 3): Amendments-per-$1M fall from 231.8 for sub-$25K awards to just 0.195 for $25M–$100M awards — small awards (the scale most nonprofits operate at) carry a hugely disproportionate administrative burden per dollar. - Mandate-driven inequity (Observation 4): Across 51 departments (1,303,900 deduplicated rows), recipient density varies ~277× between the most concentrated and broadest departments, while total spend varies only ~5.7× — access, not funding volume, is what mandate type actually predicts. - Missing counter-narrative (Observation 5): Beneficiary moral framing appears in only ~9% of grant text, and where present, domestic framing skews toward "undeserving" — so the structural disadvantages above go largely unchallenged rhetorically.
Context: the fiscal-2018-onward corpus totals $636.84B, with an $81.62B organizational-universe subset reconciling to a $102.96B lifetime baseline via a $21.34B bridge (latest-amendment-per-reference dedup). Read together, Observations 1–5 describe one coherent picture: instrument type, incumbency, award size, and departmental mandate compound against smaller/nonprofit recipients, with little narrative counterweight.
Every figure in this report draws its denominators from this block by name. The two organizational universes are distinct and reconciled — cite the one that matches the claim's window.
| Figure | Value | Definition & anchor |
|---|---|---|
| Corpus total (2018+) | $636.84B | All federal G&C, deduped to latest-amendment-per-reference, 2018+ window, batch-report placeholder quarantined (SI-9). 949,452 agreements. ev_dd2c3941e3ed |
| Organizational universe — 2018+ G&C | $81.62B | ORGANIZATIONAL-class recipients (nonprofits, community orgs), 2018+ window, latest-amendment deduped. 66,972 recipients. THE denominator for windowed organizational claims (incumbency, instrument mix). ev_7ace53b7dd1a |
| Organizational universe — lifetime baseline | $102.96B | The same ORGANIZATIONAL class across ALL years (no window). 67,862 recipients. A baseline for scale only — not a windowed denominator. ev_7ace53b7dd1a |
| Reconciliation of the two | $21.34B | Lifetime $102.96B − windowed $81.62B = pre-2018 organizational agreements. The universes differ ONLY by the 2018+ window; recipient counts are near-identical (67,862 vs 66,972) because most orgs also appear in-window. ev_7ace53b7dd1a |
| Window | fiscal 2018 onward | Anchored at 2018 per the disclosure-coverage ramp; pre-2018 is context only. First-seen cohorts are left-censored at 2018. ev_dd2c3941e3ed |
| Dedupe basis | latest-amendment-per-reference | One row per ref_number, MAX(amendment_number); agreement_value read as the revised cumulative total. Every dollar figure runs over this base. ev_dd2c3941e3ed |
Federal G&C agreements reach organizational (nonprofit) recipients as contributions in 93.2% of dollars — anchored to a 147,683-agreement contribution base carrying a $517.6K average award against $138.2K for grants — a structural, pervasive routing bias confirmed across the aggregate class distribution, every major department, the procurement channel, and the most commercially capable segment of the sector.
The class distribution in Figure 1 sets the baseline: organizational recipients post a 93.2% contribution rate on $82.365B in G&C dollars — more than double the 46.8% recorded for institutional peers and above the 83.8% government-recipient rate in the same figure, establishing the routing as class-structural rather than departmental noise. Figure 2 decomposes that total by agreement type: 147,683 contribution agreements average $517.6K per award, while 39,292 grant agreements average only $138.2K — a per-award size premium that explains how a large count position within the 198,217 organizational base agreements shown in Figure 1 compounds into the 93.2% dollar share.
| class | grants_B | contrib_B | total_B | pct_contributions |
|---|---|---|---|---|
| COMMERCIAL | 3.615 | 67.492 | 74.394 | 90.7 |
| GOVERNMENT | 22.865 | 118.5 | 141.399 | 83.8 |
| INSTITUTIONAL | 24.43 | 21.511 | 45.944 | 46.8 |
| ORGANIZATIONAL | 5.432 | 76.8 | 82.365 | 93.2 |
ev_9258813cfe33| agreement_type | agreements | distinct_org_recipients | total_dollars_M | avg_dollars_K | median_dollars_K | pct_of_org_dollars |
|---|---|---|---|---|---|---|
| C | 147683 | 50954 | 76442.2 | 517.6 | 11.1 | 93.2 |
| G | 39292 | 23035 | 5430.0 | 138.2 | 20.0 | 6.6 |
| O | 11232 | 9616 | 133.3 | 11.9 | 8.0 | 0.2 |
ev_0ddb5ba1e4b2Table 4 traces the departmental anatomy: Indigenous Services Canada alone holds 53.6% of all organizational G&C dollars at a 97.1% contribution rate, and Health Canada routes its full $14,197.1M of organizational G&C entirely as contributions (100.0%). Table 5 extends the departmental picture to flag the single material outlier: Crown-Indigenous Relations (CIRNAC) runs at only 34.5% contributions, delivering $5,669.2M through grants — proof that the 93.2% class rate reflects deliberate instrument choice by individual departments, not a universal administrative rule.
| owner_org_title | total_M | contrib_M | grant_M | pct_contrib | dept_share_pct |
|---|---|---|---|---|---|
| Indigenous Services Canada | Services aux Autochtones Canada | 36173.4 | 35141.3 | 1032.1 | 97.1 | 53.6 |
| Health Canada | Santé Canada | 14197.1 | 14197.0 | 0.2 | 100.0 | 21.04 |
| Crown-Indigenous Relations and Northern Affairs Canada | Relations Couronne-Autochtones et Affaires du Nord Canada | 8649.9 | 2980.7 | 5669.2 | 34.5 | 12.82 |
| Natural Resources Canada | Ressources naturelles Canada | 4381.0 | 4337.0 | 44.0 | 99.0 | 6.49 |
| Public Health Agency of Canada | Agence de la santé publique du Canada | 1989.9 | 1676.7 | 313.2 | 84.3 | 2.95 |
| Canadian Heritage | Patrimoine canadien | 891.2 | 769.8 | 121.4 | 86.4 | 1.32 |
| Innovation, Science and Economic Development Canada | Innovation, Sciences et Développement économique Canada | 383.3 | 319.8 | 34.7 | 83.4 | 0.57 |
| Environment and Climate Change Canada | Environnement et Changement climatique Canada | 192.8 | 192.3 | 0.5 | 99.8 | 0.29 |
ev_923e35c91f2c| owner_org_title | total_M | contrib_M | grant_M | other_M | pct_contrib | dept_share_pct |
|---|---|---|---|---|---|---|
| Indigenous Services Canada | Services aux Autochtones Canada | 36173.4 | 35141.3 | 1032.1 | 0.0 | 97.1 | 53.6 |
| Health Canada | Santé Canada | 14197.1 | 14197.0 | 0.2 | 0.0 | 100.0 | 21.04 |
| Crown-Indigenous Relations and Northern Affairs Canada | Relations Couronne-Autochtones et Affaires du Nord Canada | 8649.9 | 2980.7 | 5669.2 | 0.0 | 34.5 | 12.82 |
| Natural Resources Canada | Ressources naturelles Canada | 4381.0 | 4337.0 | 44.0 | 0.0 | 99.0 | 6.49 |
| Public Health Agency of Canada | Agence de la santé publique du Canada | 1989.9 | 1676.7 | 313.2 | 0.0 | 84.3 | 2.95 |
| Canadian Heritage | Patrimoine canadien | 891.2 | 769.8 | 121.4 | 0.0 | 86.4 | 1.32 |
| Innovation, Science and Economic Development Canada | Innovation, Sciences et Développement économique Canada | 383.3 | 319.8 | 34.7 | 28.8 | 83.4 | 0.57 |
| Environment and Climate Change Canada | Environnement et Changement climatique Canada | 192.8 | 192.3 | 0.5 | 0.0 | 99.8 | 0.29 |
ev_3527bd38905aTable 6's recipient-type × agreement-type cross-tab provides an independent verification path: the O×C cell records $66.6B at a deduplication ratio of 1.0004, confirming that organizational contribution dominance survives the row-per-award inflation test and is not an artifact of duplicate records. Figure 3 closes off the alternative financing channel: commercial recipients capture $31.701B in contracts against only $0.574B for organizational recipients, making G&C contributions the near-exclusive federal financing route for nonprofits and ruling out procurement as a meaningful parallel.
| class | contracts_B | avg_contract_K |
|---|---|---|
| COMMERCIAL | 31.701 | 8153.6 |
| OPEN | 16.288 | 3226.0 |
| ORGANIZATIONAL | 0.574 | 1693.4 |
| INSTITUTIONAL | 0.239 | 436.4 |
| GOVERNMENT | 0.047 | 313.6 |
| INTERNATIONAL | 0.002 | 1095.9 |
ev_914f1ad8438f| recipient_type | agreement_type | distinct_refs | total_M | rows_per_unique_award |
|---|---|---|---|---|
| P | G | 152726 | 32621.23 | 1.4276000261306763 |
| N | C | 159082 | 172027.83 | 1.1330000162124634 |
| C | 155797 | 134937.02 | 1.066100001335144 | |
| F | C | 130867 | 89258.25 | 1.1887999773025513 |
| A | C | 149850 | 113855.36 | 1.0139000415802002 |
| G | 113714 | 54354.69 | 1.0742000341415405 | |
| O | C | 35691 | 66582.94 | 1.0003999471664429 |
| O | Contribution | 16052 | 5450.39 | 1.0 |
ev_263a92aaeaf0Table 1 tests whether commercially capable nonprofits escape the routing bias: among the approximately 405 organizations appearing in both the G&C and contract datasets, only 3.7% of cross-appearing G&C dollars arrive as grants — the contribution preference persists even in the most procurement-sophisticated nonprofit segment. Table 2 and Table 3, each returning an identical 24-table catalog across independent schema queries, confirm that the evidence base is stable across query sessions and that no table-level changes or shadow records contaminate the figures above.
| org_gc_entities | 68166 |
|---|---|
| org_both_entities | 405 |
| base_rows | 68166 |
| distinct_refs | 68166 |
| gc_total_B | 82.365 |
| gc_cross_appearing_B | 4.249 |
| gc_grants_cross_B | 0.155 |
| gc_contrib_cross_B | 4.092 |
| ct_total_B | 0.722 |
| cross_pct_grants | 3.7 |
ev_ce37be2899d0| table_schema | table_name |
|---|---|
| main | _tables |
| main | admin_aircraft_adminaircraft |
| main | ati_summaries_ati_all |
| main | ati_summaries_ati_nil |
| main | canada_council_2020_21_en_csv_open_data |
| main | canada_council_2020_21_en_csv_open_data_esf |
ev_b752b15b28ca| table_schema | table_name |
|---|---|
| main | _tables |
| main | admin_aircraft_adminaircraft |
| main | ati_summaries_ati_all |
| main | ati_summaries_ati_nil |
| main | canada_council_2020_21_en_csv_open_data |
| main | canada_council_2020_21_en_csv_open_data_esf |
ev_95e560b6a63eev_9258813cfe33| class | base_rows | distinct_refs | grants_B | contrib_B | other_B | total_B | pct_pure_grants | pct_contributions |
|---|---|---|---|---|---|---|---|---|
| COMMERCIAL | 106985 | 106985 | 3.615 | 67.492 | 3.287 | 74.394 | 4.9 | 90.7 |
| GOVERNMENT | 73579 | 73579 | 22.865 | 118.5 | 0.034 | 141.399 | 16.2 | 83.8 |
| INSTITUTIONAL | 96890 | 96890 | 24.43 | 21.511 | 0.003 | 45.944 | 53.2 | 46.8 |
| ORGANIZATIONAL | 198217 | 198217 | 5.432 | 76.8 | 0.133 | 82.365 | 6.6 | 93.2 |
WITH deduped AS (SELECT sub.recipient_legal_name, sub.ref_number, sub.agreement_type, TRY_CAST(sub.agreement_value AS DOUBLE) AS val, ROW_NUMBER() OVER (PARTITION BY sub.ref_number ORDER BY TRY_CAST(sub.amendment_number AS INT) DESC) AS rn FROM grants.grants AS sub WHERE TRY_CAST(sub.agreement_start_date AS DATE) >= '2018-01-01' AND sub.recipient_legal_name <> 'batch report│rapport en lots'), latest AS (SELECT * FROM deduped WHERE rn = 1), pivoted AS (SELECT rc.class, SUM(CASE WHEN lat.agreement_type = 'G' THEN lat.val ELSE 0 END) AS grants_raw, SUM(CASE WHEN lat.agreement_type = 'C' THEN lat.val ELSE 0 END) AS contrib_raw, SUM(CASE WHEN NOT lat.agreement_type IN ('G', 'C') THEN lat.val ELSE 0 END) AS other_raw, SUM(lat.val) AS total_raw, COUNT(*) AS base_rows, COUNT(DISTINCT lat.ref_number) AS distinct_refs FROM latest AS lat JOIN classification.recipient_class AS rc ON rc.recipient = lat.recipient_legal_name WHERE rc.class IN ('COMMERCIAL', 'INSTITUTIONAL', 'ORGANIZATIONAL', 'GOVERNMENT') GROUP BY rc.class) SELECT class, base_rows, distinct_refs, ROUND(grants_raw / 1e9, 3) AS grants_B, ROUND(contrib_raw / 1e9, 3) AS contrib_B, ROUND(other_raw / 1e9, 3) AS other_B, ROUND(total_raw / 1e9, 3) AS total_B, ROUND(100.0 * grants_raw / NULLIF(total_raw, 0), 1) AS pct_pure_grants, ROUND(100.0 * contrib_raw / NULLIF(total_raw, 0), 1) AS pct_contributions FROM pivoted ORDER BY class LIMIT 2000ev_0ddb5ba1e4b2| agreement_type | agreements | distinct_org_recipients | total_dollars_M | avg_dollars_K | median_dollars_K | pct_of_org_dollars |
|---|---|---|---|---|---|---|
| C | 147683 | 50954 | 76442.2 | 517.6 | 11.1 | 93.2 |
| G | 39292 | 23035 | 5430.0 | 138.2 | 20.0 | 6.6 |
| O | 11232 | 9616 | 133.3 | 11.9 | 8.0 | 0.2 |
WITH deduped AS (SELECT recipient_legal_name, agreement_type, TRY_CAST(agreement_value AS DOUBLE) AS val FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC, amendment_date DESC) AS rn FROM grants.grants WHERE TRY_CAST(agreement_start_date AS DATE) >= CAST('2018-01-01' AS DATE) AND recipient_legal_name NOT LIKE '%batch report%' AND recipient_legal_name NOT LIKE '%rapport en lots%') AS t WHERE rn = 1) SELECT d.agreement_type, COUNT(*) AS agreements, COUNT(DISTINCT d.recipient_legal_name) AS distinct_org_recipients, ROUND(SUM(val) / 1e6, 1) AS total_dollars_M, ROUND(AVG(val) / 1e3, 1) AS avg_dollars_K, ROUND(MEDIAN(val) / 1e3, 1) AS median_dollars_K, ROUND(SUM(val) * 100.0 / SUM(SUM(val)) OVER (), 1) AS pct_of_org_dollars FROM deduped AS d JOIN classification.recipient_class AS rc ON d.recipient_legal_name = rc.recipient WHERE rc.class = 'ORGANIZATIONAL' GROUP BY d.agreement_type ORDER BY SUM(val) DESC LIMIT 2000ev_ce37be2899d0| org_gc_entities | org_both_entities | base_rows | distinct_refs | gc_total_B | gc_cross_appearing_B | gc_grants_cross_B | gc_contrib_cross_B | ct_total_B | cross_pct_grants |
|---|---|---|---|---|---|---|---|---|---|
| 68166 | 405 | 68166 | 68166 | 82.365 | 4.249 | 0.155 | 4.092 | 0.722 | 3.7 |
WITH org_gc AS (SELECT lat.recipient_legal_name, SUM(CASE WHEN lat.agreement_type = 'G' THEN lat.val ELSE 0 END) AS gc_grants, SUM(CASE WHEN lat.agreement_type = 'C' THEN lat.val ELSE 0 END) AS gc_contrib, SUM(lat.val) AS gc_total, COUNT(DISTINCT lat.ref_number) AS gc_refs FROM (SELECT ref_number, recipient_legal_name, agreement_type, TRY_CAST(agreement_value AS DOUBLE) AS val, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants WHERE TRY_CAST(agreement_start_date AS DATE) >= '2018-01-01' AND recipient_legal_name <> 'batch report│rapport en lots') AS lat JOIN classification.recipient_class AS rc ON rc.recipient = lat.recipient_legal_name AND rc.class = 'ORGANIZATIONAL' WHERE lat.rn = 1 GROUP BY lat.recipient_legal_name), org_ct AS (SELECT csub.vendor_name, COUNT(*) AS ct_rows, COUNT(DISTINCT csub.reference_number) AS ct_refs, SUM(TRY_CAST(csub.contract_value AS DOUBLE)) AS ct_total FROM corpus.contracts_contracts AS csub JOIN classification.recipient_class AS rc ON rc.recipient = csub.vendor_name AND rc.class = 'ORGANIZATIONAL' WHERE TRY_CAST(csub.contract_date AS DATE) >= '2018-01-01' GROUP BY csub.vendor_name) SELECT COUNT(DISTINCT g.recipient_legal_name) AS org_gc_entities, COUNT(DISTINCT CASE WHEN NOT c.vendor_name IS NULL THEN g.recipient_legal_name END) AS org_both_entities, COUNT(*) AS base_rows, COUNT(DISTINCT g.recipient_legal_name) AS distinct_refs, ROUND(SUM(g.gc_total) / 1e9, 3) AS gc_total_B, ROUND(SUM(CASE WHEN NOT c.vendor_name IS NULL THEN g.gc_total ELSE 0 END) / 1e9, 3) AS gc_cross_appearing_B, ROUND(SUM(CASE WHEN NOT c.vendor_name IS NULL THEN g.gc_grants ELSE 0 END) / 1e9, 3) AS gc_grants_cross_B, ROUND(SUM(CASE WHEN NOT c.vendor_name IS NULL THEN g.gc_contrib ELSE 0 END) / 1e9, 3) AS gc_contrib_cross_B, ROUND(SUM(COALESCE(c.ct_total, 0)) / 1e9, 3) AS ct_total_B, ROUND(100.0 * SUM(CASE WHEN NOT c.vendor_name IS NULL THEN g.gc_grants ELSE 0 END) / NULLIF(SUM(CASE WHEN NOT c.vendor_name IS NULL THEN g.gc_total ELSE 0 END), 0), 1) AS cross_pct_grants FROM org_gc AS g LEFT JOIN org_ct AS c ON c.vendor_name = g.recipient_legal_name LIMIT 2000
ev_914f1ad8438f| class | base_rows | distinct_refs | contracts_B | avg_contract_K |
|---|---|---|---|---|
| COMMERCIAL | 3888 | 3888 | 31.701 | 8153.6 |
| OPEN | 5049 | 5049 | 16.288 | 3226.0 |
| ORGANIZATIONAL | 339 | 339 | 0.574 | 1693.4 |
| INSTITUTIONAL | 548 | 548 | 0.239 | 436.4 |
| GOVERNMENT | 151 | 151 | 0.047 | 313.6 |
| INTERNATIONAL | 2 | 2 | 0.002 | 1095.9 |
WITH ct_deduped AS (SELECT reference_number, vendor_name, TRY_CAST(contract_value AS DOUBLE) AS cv, ROW_NUMBER() OVER (PARTITION BY reference_number ORDER BY TRY_CAST(contract_value AS DOUBLE) DESC, contract_date DESC) AS rn FROM corpus.contracts_contracts WHERE TRY_CAST(contract_date AS DATE) >= '2018-01-01' AND NOT vendor_name IS NULL AND TRIM(vendor_name) <> '' AND vendor_name <> 'batch report|rapport en lots'), ct_base AS (SELECT reference_number, vendor_name, cv FROM ct_deduped WHERE rn = 1), classified AS (SELECT DISTINCT cb.reference_number, cb.cv, rc.class FROM ct_base AS cb INNER JOIN classification.recipient_class AS rc ON rc.recipient = cb.vendor_name WHERE NOT rc.class IN ('QUARANTINED')) SELECT class, COUNT(*) AS base_rows, COUNT(DISTINCT reference_number) AS distinct_refs, ROUND(SUM(cv) / 1e9, 3) AS contracts_B, ROUND(AVG(cv) / 1e3, 1) AS avg_contract_K FROM classified GROUP BY class ORDER BY contracts_B DESC LIMIT 2000ev_b752b15b28ca| table_schema | table_name |
|---|---|
| main | _tables |
| main | admin_aircraft_adminaircraft |
| main | ati_summaries_ati_all |
| main | ati_summaries_ati_nil |
| main | canada_council_2020_21_en_csv_open_data |
| main | canada_council_2020_21_en_csv_open_data_esf |
| main | canada_council_2021_22_artbank_artbank_banqued_art_opendata_csv |
| main | canada_council_2021_22_en_opendatadonneesouvertes |
| main | canada_council_2022_23_en_artbank_open_data_csv_0502 |
| main | canada_council_2022_23_en_opendata_donneesouvertes |
| main | canada_council_2023_24_en_artbank_open_data_csv |
| main | canada_council_2023_24_en_opendata |
| main | canada_council_2024_25_en_open_data |
| main | cesg_by_age_cumu_bens_age |
| main | cesg_yearly_by_fsa_cesg_by_fsa_v3 |
| main | charity |
| main | charity_panel |
| main | contracts_contracts |
| main | contracts_contracts_nil |
| main | contracts_contractsa |
| main | contracts_load_contracts_01 |
| main | corporation |
| main | cra_charities_core |
| main | cra_charities_country |
| main | cra_charities_director |
| main | cra_charities_directors |
| main | cra_charities_donee |
| main | cra_charities_financial_d_and_schedule_6 |
| main | cra_charities_financial_section_a_b_and_c |
| main | cra_charities_ident |
| main | cra_charities_new_ongoing_programs |
| main | cra_charities_non_qualified_donees |
| main | cra_charities_program |
| main | cra_charities_qualified_donees |
| main | cra_charities_qualified_donees_2 |
| main | cra_charities_schedule_1_foundations |
| main | cra_charities_schedule_2_countries |
| main | cra_charities_schedule_2_details |
| main | cra_charities_schedule_2_goods |
| main | cra_charities_schedule_2_resources |
| main | cra_charities_schedule_3_compensation |
| main | cra_charities_schedule_5_gifts_in_kind |
| main | cra_charities_schedule_7_pa_description |
| main | cra_charities_schedule_7_pa_description_3 |
| main | cra_charities_schedule_7_pa_funding_from_outside_ca |
| main | cra_charities_schedule_7_pa_resources |
| main | cra_charities_schedule_8_dq |
| main | cra_charities_weburl |
| main | edc_individual_transactions_current_year |
| main | estimates_estimates_2023_24_organization_summary |
| main | estimates_supplementary_estimates_a_2017_18_beso_dbacd_eng |
| main | estimates_supplementary_estimates_a_2022_23_budgetary_expenditu |
| main | estimates_supplementary_estimates_a_2023_24_expenditures_progra |
| main | estimates_supplementary_estimates_a_2025_26_budgetary_expenditu |
| main | estimates_supplementary_estimates_a_2025_26_explanation_require |
| main | estimates_supplementary_estimates_a_2026_27_budgetary_expenditu |
| main | estimates_supplementary_estimates_b_2022_23_budgetary_expenditu |
| main | estimates_supplementary_estimates_b_2022_23_expenditures_progra |
| main | estimates_supplementary_estimates_b_2022_23_statutory_forecasts |
| main | estimates_supplementary_estimates_c_2020_21_expenditures_progra |
| main | estimates_supplementary_estimates_c_2025_26_listing_transfer_pa |
| main | federal_corporations_corporations_active_cbca_en |
| main | federal_corporations_corporations_active_non_cbca_en |
| main | federal_corporations_corporations_inactive_or_dissolved_cbca_en |
| main | federal_corporations_corporations_inactive_or_dissolved_non_cbc |
| main | gc_infobase_1630_spe_1718 |
| main | gc_infobase_a_per_1718 |
| main | gc_infobase_abv_apc |
| main | gc_infobase_biv_ced |
| main | gc_infobase_bivap_avebp |
| main | gc_infobase_ctempdatasetsa_fte_1718 |
| main | gc_infobase_ctempdatasetsa_spe_1718 |
| main | gc_infobase_ctempdatasetsauth_exp_prog_1317 |
| main | gc_infobase_eav_eac |
| main | gc_infobase_eso_eac |
| main | gc_infobase_ifoi_roif |
| main | gc_infobase_pipo_irpo |
| main | gc_infobase_rbpo_rppo |
| main | gc_infobase_sa_al |
| main | gc_infobase_tp_pt |
| main | grants |
| main | nserc_awards_nserc_fy_co_app |
| main | nserc_awards_nserc_fy_expenditures |
| main | nserc_awards_nserc_fy_partner |
| main | open_gov_analytics_ati_informal_requests_analytics |
| main | open_gov_analytics_dataset_ratings |
| main | open_gov_analytics_dataset_ratings_timeseries |
| main | open_gov_analytics_deleted |
| main | open_gov_analytics_open_maps_analytics |
| main | open_gov_analytics_opendataportal_siteanalytics_datasetsbyorg_b |
| main | open_gov_analytics_opendataportal_siteanalytics_datasetsbyorgby |
| main | open_gov_analytics_opendataportal_siteanalytics_downloads_0526 |
| main | open_gov_analytics_opendataportal_siteanalytics_info_0526 |
| main | open_gov_analytics_opendataportal_siteanalytics_internationalus |
| main | open_gov_analytics_opendataportal_siteanalytics_provincialusage |
| main | open_gov_analytics_opendataportal_siteanalytics_top100datasets_ |
| main | open_gov_analytics_opendataportal_siteanalytics_top20info |
| main | open_gov_analytics_opendataportal_siteanalytics_totalmonthlyusa |
| main | open_gov_analytics_opendataportal_siteanalytics_visits_0526 |
| main | open_gov_analytics_openness_report |
| main | provincial_alberta_grant_payments_tbf_grants_disclosure |
| main | provincial_alberta_grant_payments_tbf_grants_disclosure_1 |
| main | provincial_ontario_arts_council_awards_chalmers_jurors_en |
| main | provincial_ontario_arts_council_awards_grants_en2 |
| main | provincial_ontario_arts_council_oac_assesssors_data_dictionary |
| main | provincial_ontario_arts_council_oac_awards_assessors_data_dicti |
| main | provincial_ontario_arts_council_oac_awards_jurors_en |
| main | provincial_ontario_arts_council_oac_disclosure_of_contracts_apr |
| main | provincial_ontario_arts_council_oac_expenses_july_sept |
| main | provincial_ontario_arts_council_oac_grants_data_dictionary |
| main | provincial_ontario_arts_council_ontario_arts_council_regions |
| main | provincial_ontario_critical_minerals_innovation_fund_recipients |
| main | provincial_ontario_enhancing_access_to_spaces_for_everyone_ease |
| main | provincial_ontario_inclusive_community_grants_project_recipient |
| main | provincial_ontario_seniors_community_grants_25_0618_msaa_scg_pr |
| main | provincial_ontario_seniors_community_grants_61af17ee_9ea0_443c_ |
| main | provincial_ontario_seniors_community_grants_8fb2e71c_a3f5_43df_ |
| main | provincial_ontario_seniors_community_grants_msaa_scg_project_re |
| main | provincial_ontario_successful_seniors_community_grant_program_r |
| main | provincial_quebec_comptes_publics_vol2_depenses_investissement_ |
| main | provincial_quebec_comptes_publics_vol2_depenses_transfert |
| main | provincial_quebec_comptes_publics_vol2_donnees_ouvertes_vol2_de |
| main | provincial_quebec_comptes_publics_vol2_donnees_ouvertes_vol2_ev |
| main | provincial_quebec_comptes_publics_vol2_donnees_ouvertes_vol2_re |
| main | provincial_quebec_comptes_publics_vol2_renseignements_revenus_d |
| main | provincial_quebec_frq_nature_et_technologies_frqnt_liste |
| main | provincial_quebec_frq_nature_et_technologies_liste_financement_ |
| main | provincial_quebec_frq_sante_frqs_liste |
| main | provincial_quebec_frq_sante_liste_financement_frqs |
| main | provincial_quebec_frq_societe_et_culture_frqsc_liste |
| main | provincial_quebec_frq_societe_et_culture_liste_financement_frqs |
| main | public_accounts_detailed_transfer_payments_pt_tp |
| main | public_accounts_detailed_transfer_payments_pt_tp_eng |
| main | public_accounts_major_transfer_by_province_ppt_mtp |
| main | public_accounts_major_transfer_by_province_ppt_mtp_eng |
| main | public_accounts_other_transfer_by_ministry_otpmopeom_apdtmacdpa |
| main | public_accounts_transfer_payments_vol2_paiementstransfert_trans |
| main | recipient |
| main | recipient_class |
| main | recipient_enriched |
| main | recipient_lobbying |
| main | recipient_match |
| main | sshrc_expenditures_sshrc_fy_co_app |
| main | sshrc_expenditures_sshrc_fy_expenditures |
| main | sshrc_expenditures_sshrc_fy_partner |
| main | t3010_field_codes |
| main | tier_b_payees |
| main | transfer_payment_agreements_oc_co |
| main | transfer_payment_agreements_oc_co_eng |
| main | travel_expenses_travelq |
| main | travel_expenses_travelq_nil |
SELECT table_schema, table_name FROM information_schema.tables WHERE NOT table_schema IN ('information_schema', 'pg_catalog') ORDER BY table_schema, table_name LIMIT 2000ev_95e560b6a63e| table_schema | table_name |
|---|---|
| main | _tables |
| main | admin_aircraft_adminaircraft |
| main | ati_summaries_ati_all |
| main | ati_summaries_ati_nil |
| main | canada_council_2020_21_en_csv_open_data |
| main | canada_council_2020_21_en_csv_open_data_esf |
| main | canada_council_2021_22_artbank_artbank_banqued_art_opendata_csv |
| main | canada_council_2021_22_en_opendatadonneesouvertes |
| main | canada_council_2022_23_en_artbank_open_data_csv_0502 |
| main | canada_council_2022_23_en_opendata_donneesouvertes |
| main | canada_council_2023_24_en_artbank_open_data_csv |
| main | canada_council_2023_24_en_opendata |
| main | canada_council_2024_25_en_open_data |
| main | cesg_by_age_cumu_bens_age |
| main | cesg_yearly_by_fsa_cesg_by_fsa_v3 |
| main | charity |
| main | charity_panel |
| main | contracts_contracts |
| main | contracts_contracts_nil |
| main | contracts_contractsa |
| main | contracts_load_contracts_01 |
| main | corporation |
| main | cra_charities_core |
| main | cra_charities_country |
| main | cra_charities_director |
| main | cra_charities_directors |
| main | cra_charities_donee |
| main | cra_charities_financial_d_and_schedule_6 |
| main | cra_charities_financial_section_a_b_and_c |
| main | cra_charities_ident |
| main | cra_charities_new_ongoing_programs |
| main | cra_charities_non_qualified_donees |
| main | cra_charities_program |
| main | cra_charities_qualified_donees |
| main | cra_charities_qualified_donees_2 |
| main | cra_charities_schedule_1_foundations |
| main | cra_charities_schedule_2_countries |
| main | cra_charities_schedule_2_details |
| main | cra_charities_schedule_2_goods |
| main | cra_charities_schedule_2_resources |
| main | cra_charities_schedule_3_compensation |
| main | cra_charities_schedule_5_gifts_in_kind |
| main | cra_charities_schedule_7_pa_description |
| main | cra_charities_schedule_7_pa_description_3 |
| main | cra_charities_schedule_7_pa_funding_from_outside_ca |
| main | cra_charities_schedule_7_pa_resources |
| main | cra_charities_schedule_8_dq |
| main | cra_charities_weburl |
| main | edc_individual_transactions_current_year |
| main | estimates_estimates_2023_24_organization_summary |
| main | estimates_supplementary_estimates_a_2017_18_beso_dbacd_eng |
| main | estimates_supplementary_estimates_a_2022_23_budgetary_expenditu |
| main | estimates_supplementary_estimates_a_2023_24_expenditures_progra |
| main | estimates_supplementary_estimates_a_2025_26_budgetary_expenditu |
| main | estimates_supplementary_estimates_a_2025_26_explanation_require |
| main | estimates_supplementary_estimates_a_2026_27_budgetary_expenditu |
| main | estimates_supplementary_estimates_b_2022_23_budgetary_expenditu |
| main | estimates_supplementary_estimates_b_2022_23_expenditures_progra |
| main | estimates_supplementary_estimates_b_2022_23_statutory_forecasts |
| main | estimates_supplementary_estimates_c_2020_21_expenditures_progra |
| main | estimates_supplementary_estimates_c_2025_26_listing_transfer_pa |
| main | federal_corporations_corporations_active_cbca_en |
| main | federal_corporations_corporations_active_non_cbca_en |
| main | federal_corporations_corporations_inactive_or_dissolved_cbca_en |
| main | federal_corporations_corporations_inactive_or_dissolved_non_cbc |
| main | gc_infobase_1630_spe_1718 |
| main | gc_infobase_a_per_1718 |
| main | gc_infobase_abv_apc |
| main | gc_infobase_biv_ced |
| main | gc_infobase_bivap_avebp |
| main | gc_infobase_ctempdatasetsa_fte_1718 |
| main | gc_infobase_ctempdatasetsa_spe_1718 |
| main | gc_infobase_ctempdatasetsauth_exp_prog_1317 |
| main | gc_infobase_eav_eac |
| main | gc_infobase_eso_eac |
| main | gc_infobase_ifoi_roif |
| main | gc_infobase_pipo_irpo |
| main | gc_infobase_rbpo_rppo |
| main | gc_infobase_sa_al |
| main | gc_infobase_tp_pt |
| main | grants |
| main | nserc_awards_nserc_fy_co_app |
| main | nserc_awards_nserc_fy_expenditures |
| main | nserc_awards_nserc_fy_partner |
| main | open_gov_analytics_ati_informal_requests_analytics |
| main | open_gov_analytics_dataset_ratings |
| main | open_gov_analytics_dataset_ratings_timeseries |
| main | open_gov_analytics_deleted |
| main | open_gov_analytics_open_maps_analytics |
| main | open_gov_analytics_opendataportal_siteanalytics_datasetsbyorg_b |
| main | open_gov_analytics_opendataportal_siteanalytics_datasetsbyorgby |
| main | open_gov_analytics_opendataportal_siteanalytics_downloads_0526 |
| main | open_gov_analytics_opendataportal_siteanalytics_info_0526 |
| main | open_gov_analytics_opendataportal_siteanalytics_internationalus |
| main | open_gov_analytics_opendataportal_siteanalytics_provincialusage |
| main | open_gov_analytics_opendataportal_siteanalytics_top100datasets_ |
| main | open_gov_analytics_opendataportal_siteanalytics_top20info |
| main | open_gov_analytics_opendataportal_siteanalytics_totalmonthlyusa |
| main | open_gov_analytics_opendataportal_siteanalytics_visits_0526 |
| main | open_gov_analytics_openness_report |
| main | provincial_alberta_grant_payments_tbf_grants_disclosure |
| main | provincial_alberta_grant_payments_tbf_grants_disclosure_1 |
| main | provincial_ontario_arts_council_awards_chalmers_jurors_en |
| main | provincial_ontario_arts_council_awards_grants_en2 |
| main | provincial_ontario_arts_council_oac_assesssors_data_dictionary |
| main | provincial_ontario_arts_council_oac_awards_assessors_data_dicti |
| main | provincial_ontario_arts_council_oac_awards_jurors_en |
| main | provincial_ontario_arts_council_oac_disclosure_of_contracts_apr |
| main | provincial_ontario_arts_council_oac_expenses_july_sept |
| main | provincial_ontario_arts_council_oac_grants_data_dictionary |
| main | provincial_ontario_arts_council_ontario_arts_council_regions |
| main | provincial_ontario_critical_minerals_innovation_fund_recipients |
| main | provincial_ontario_enhancing_access_to_spaces_for_everyone_ease |
| main | provincial_ontario_inclusive_community_grants_project_recipient |
| main | provincial_ontario_seniors_community_grants_25_0618_msaa_scg_pr |
| main | provincial_ontario_seniors_community_grants_61af17ee_9ea0_443c_ |
| main | provincial_ontario_seniors_community_grants_8fb2e71c_a3f5_43df_ |
| main | provincial_ontario_seniors_community_grants_msaa_scg_project_re |
| main | provincial_ontario_successful_seniors_community_grant_program_r |
| main | provincial_quebec_comptes_publics_vol2_depenses_investissement_ |
| main | provincial_quebec_comptes_publics_vol2_depenses_transfert |
| main | provincial_quebec_comptes_publics_vol2_donnees_ouvertes_vol2_de |
| main | provincial_quebec_comptes_publics_vol2_donnees_ouvertes_vol2_ev |
| main | provincial_quebec_comptes_publics_vol2_donnees_ouvertes_vol2_re |
| main | provincial_quebec_comptes_publics_vol2_renseignements_revenus_d |
| main | provincial_quebec_frq_nature_et_technologies_frqnt_liste |
| main | provincial_quebec_frq_nature_et_technologies_liste_financement_ |
| main | provincial_quebec_frq_sante_frqs_liste |
| main | provincial_quebec_frq_sante_liste_financement_frqs |
| main | provincial_quebec_frq_societe_et_culture_frqsc_liste |
| main | provincial_quebec_frq_societe_et_culture_liste_financement_frqs |
| main | public_accounts_detailed_transfer_payments_pt_tp |
| main | public_accounts_detailed_transfer_payments_pt_tp_eng |
| main | public_accounts_major_transfer_by_province_ppt_mtp |
| main | public_accounts_major_transfer_by_province_ppt_mtp_eng |
| main | public_accounts_other_transfer_by_ministry_otpmopeom_apdtmacdpa |
| main | public_accounts_transfer_payments_vol2_paiementstransfert_trans |
| main | recipient |
| main | recipient_class |
| main | recipient_enriched |
| main | recipient_lobbying |
| main | recipient_match |
| main | sshrc_expenditures_sshrc_fy_co_app |
| main | sshrc_expenditures_sshrc_fy_expenditures |
| main | sshrc_expenditures_sshrc_fy_partner |
| main | t3010_field_codes |
| main | tier_b_payees |
| main | transfer_payment_agreements_oc_co |
| main | transfer_payment_agreements_oc_co_eng |
| main | travel_expenses_travelq |
| main | travel_expenses_travelq_nil |
SELECT table_schema, table_name FROM information_schema.tables WHERE NOT table_schema IN ('information_schema', 'pg_catalog') ORDER BY table_schema, table_name LIMIT 2000ev_923e35c91f2c| owner_org_title | base_rows | distinct_refs | total_M | contrib_M | grant_M | pct_contrib | dept_share_pct | dept_rank |
|---|---|---|---|---|---|---|---|---|
| Indigenous Services Canada | Services aux Autochtones Canada | 21217 | 21217 | 36173.4 | 35141.3 | 1032.1 | 97.1 | 53.6 | 1 |
| Health Canada | Santé Canada | 1253 | 1253 | 14197.1 | 14197.0 | 0.2 | 100.0 | 21.04 | 2 |
| Crown-Indigenous Relations and Northern Affairs Canada | Relations Couronne-Autochtones et Affaires du Nord Canada | 3710 | 3710 | 8649.9 | 2980.7 | 5669.2 | 34.5 | 12.82 | 3 |
| Natural Resources Canada | Ressources naturelles Canada | 1572 | 1572 | 4381.0 | 4337.0 | 44.0 | 99.0 | 6.49 | 4 |
| Public Health Agency of Canada | Agence de la santé publique du Canada | 1964 | 1964 | 1989.9 | 1676.7 | 313.2 | 84.3 | 2.95 | 5 |
| Canadian Heritage | Patrimoine canadien | 4789 | 4789 | 891.2 | 769.8 | 121.4 | 86.4 | 1.32 | 6 |
| Innovation, Science and Economic Development Canada | Innovation, Sciences et Développement économique Canada | 17 | 17 | 383.3 | 319.8 | 34.7 | 83.4 | 0.57 | 7 |
| Environment and Climate Change Canada | Environnement et Changement climatique Canada | 37 | 37 | 192.8 | 192.3 | 0.5 | 99.8 | 0.29 | 8 |
| Global Affairs Canada | Affaires mondiales Canada | 83 | 83 | 141.9 | 122.9 | 19.0 | 86.6 | 0.21 | 9 |
| Department of Justice Canada | Ministère de la Justice Canada | 56 | 56 | 131.1 | 130.3 | 0.8 | 99.4 | 0.19 | 10 |
| Public Safety Canada | Sécurité publique Canada | 55 | 55 | 88.5 | 87.5 | 0.9 | 99.0 | 0.13 | 11 |
| Veterans Affairs Canada | Anciens Combattants Canada | 15 | 15 | 56.2 | 46.9 | 9.3 | 83.4 | 0.08 | 12 |
| Canadian Northern Economic Development Agency | Agence canadienne de développement économique du Nord | 82 | 82 | 46.8 | 46.8 | 0.0 | 100.0 | 0.07 | 13 |
| Immigration, Refugees and Citizenship Canada | Immigration, Réfugiés et Citoyenneté Canada | 19 | 19 | 41.4 | 41.4 | 0.0 | 100.0 | 0.06 | 14 |
| Social Sciences and Humanities Research Council of Canada | Conseil de recherches en sciences humaines du Canada | 320 | 320 | 33.3 | 0.0 | 33.3 | 0.0 | 0.05 | 15 |
| Employment and Social Development Canada | Emploi et Développement social Canada | 8 | 8 | 25.1 | 25.1 | 0.0 | 99.9 | 0.04 | 16 |
| Agriculture and Agri-Food Canada | Agriculture et Agroalimentaire Canada | 88 | 88 | 21.0 | 20.8 | 0.2 | 99.3 | 0.03 | 17 |
| Canada Economic Development for Quebec Regions | Développement économique Canada pour les régions du Québec | 42 | 42 | 12.8 | 12.8 | 0.0 | 100.0 | 0.02 | 18 |
| Natural Sciences and Engineering Research Council of Canada | Conseil de recherches en sciences naturelles et en génie du Canada | 176 | 176 | 6.3 | 0.0 | 6.3 | 0.0 | 0.01 | 19 |
| National Defence | Défense nationale | 62 | 62 | 4.3 | 3.0 | 1.4 | 68.3 | 0.01 | 20 |
| Royal Canadian Mounted Police | Gendarmerie royale du Canada | 49 | 49 | 3.3 | 0.0 | 3.3 | 0.0 | 0.0 | 21 |
| Women and Gender Equality Canada | Femmes et Égalité des genres Canada | 8 | 8 | 3.2 | 2.7 | 0.5 | 85.0 | 0.0 | 22 |
| Prairies Economic Development Canada | Développement économique Canada pour les Prairies | 9 | 9 | 2.8 | 2.8 | 0.0 | 100.0 | 0.0 | 23 |
| Parks Canada | Parcs Canada | 17 | 17 | 2.4 | 2.2 | 0.2 | 91.8 | 0.0 | 24 |
| Pacific Economic Development Canada | Développement économique Canada pour le Pacifique | 22 | 22 | 2.1 | 1.1 | 1.0 | 54.1 | 0.0 | 25 |
| Federal Economic Development Agency for Southern Ontario | Agence fédérale de développement économique pour le Sud de l'Ontario | 11 | 11 | 1.4 | 1.4 | 0.0 | 100.0 | 0.0 | 26 |
| Federal Economic Development Agency for Northern Ontario | Agence fédérale de développement économique pour le Nord de l’Ontario | 2 | 2 | 0.9 | 0.9 | 0.0 | 100.0 | 0.0 | 27 |
| Canadian Nuclear Safety Commission | Commission canadienne de sûreté nucléaire | 51 | 51 | 0.8 | 0.8 | 0.0 | 99.6 | 0.0 | 28 |
| National Research Council Canada | Conseil national de recherches Canada | 9 | 9 | 0.6 | 0.0 | 0.6 | 0.0 | 0.0 | 29 |
| Transport Canada | Transports Canada | 9 | 9 | 0.6 | 0.6 | 0.0 | 96.2 | 0.0 | 30 |
| Polar Knowledge Canada | Savoir polaire Canada | 3 | 3 | 0.5 | 0.5 | 0.0 | 97.4 | 0.0 | 31 |
| Canadian Institutes of Health Research | Instituts de recherche en santé du Canada | 1 | 1 | 0.2 | 0.0 | 0.2 | 0.0 | 0.0 | 32 |
| Fisheries and Oceans Canada | Pêches et Océans Canada | 3 | 3 | 0.2 | 0.2 | 0.0 | 100.0 | 0.0 | 33 |
| Canada Energy Regulator | La Régie de l’énergie du Canada | 2 | 2 | 0.2 | 0.2 | 0.0 | 100.0 | 0.0 | 34 |
| Department of Housing, Infrastructure and Communities | Ministère du Logement, de l’Infrastructure et des Collectivités | 1 | 1 | 0.1 | 0.1 | 0.0 | 100.0 | 0.0 | 35 |
WITH org_deduped AS (SELECT ref_number, owner_org_title, agreement_type, CAST(agreement_value AS DOUBLE) AS val FROM grants.grants WHERE TRY_CAST(start_year AS INT) >= 2018 AND recipient_type = 'O' QUALIFY ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) = 1), dept_ranked AS (SELECT owner_org_title, COUNT(*) AS base_rows, COUNT(DISTINCT ref_number) AS distinct_refs, ROUND(SUM(val) / 1e6, 1) AS total_M, ROUND(SUM(CASE WHEN agreement_type = 'C' THEN val ELSE 0 END) / 1e6, 1) AS contrib_M, ROUND(SUM(CASE WHEN agreement_type = 'G' THEN val ELSE 0 END) / 1e6, 1) AS grant_M, ROUND(SUM(CASE WHEN agreement_type = 'C' THEN val ELSE 0 END) * 100.0 / SUM(val), 1) AS pct_contrib, ROUND(SUM(val) * 100.0 / SUM(SUM(val)) OVER (), 2) AS dept_share_pct FROM org_deduped GROUP BY owner_org_title) SELECT *, ROW_NUMBER() OVER (ORDER BY total_M DESC) AS dept_rank FROM dept_ranked ORDER BY total_M DESC LIMIT 50
ev_3527bd38905a| owner_org_title | base_rows | distinct_refs | total_M | contrib_M | grant_M | other_M | pct_contrib | dept_share_pct |
|---|---|---|---|---|---|---|---|---|
| Indigenous Services Canada | Services aux Autochtones Canada | 21217 | 21217 | 36173.4 | 35141.3 | 1032.1 | 0.0 | 97.1 | 53.6 |
| Health Canada | Santé Canada | 1253 | 1253 | 14197.1 | 14197.0 | 0.2 | 0.0 | 100.0 | 21.04 |
| Crown-Indigenous Relations and Northern Affairs Canada | Relations Couronne-Autochtones et Affaires du Nord Canada | 3710 | 3710 | 8649.9 | 2980.7 | 5669.2 | 0.0 | 34.5 | 12.82 |
| Natural Resources Canada | Ressources naturelles Canada | 1572 | 1572 | 4381.0 | 4337.0 | 44.0 | 0.0 | 99.0 | 6.49 |
| Public Health Agency of Canada | Agence de la santé publique du Canada | 1964 | 1964 | 1989.9 | 1676.7 | 313.2 | 0.0 | 84.3 | 2.95 |
| Canadian Heritage | Patrimoine canadien | 4789 | 4789 | 891.2 | 769.8 | 121.4 | 0.0 | 86.4 | 1.32 |
| Innovation, Science and Economic Development Canada | Innovation, Sciences et Développement économique Canada | 17 | 17 | 383.3 | 319.8 | 34.7 | 28.8 | 83.4 | 0.57 |
| Environment and Climate Change Canada | Environnement et Changement climatique Canada | 37 | 37 | 192.8 | 192.3 | 0.5 | 0.0 | 99.8 | 0.29 |
| Global Affairs Canada | Affaires mondiales Canada | 83 | 83 | 141.9 | 122.9 | 19.0 | 0.0 | 86.6 | 0.21 |
| Department of Justice Canada | Ministère de la Justice Canada | 56 | 56 | 131.1 | 130.3 | 0.8 | 0.0 | 99.4 | 0.19 |
| Public Safety Canada | Sécurité publique Canada | 55 | 55 | 88.5 | 87.5 | 0.9 | 0.0 | 99.0 | 0.13 |
| Veterans Affairs Canada | Anciens Combattants Canada | 15 | 15 | 56.2 | 46.9 | 9.3 | 0.0 | 83.4 | 0.08 |
| Canadian Northern Economic Development Agency | Agence canadienne de développement économique du Nord | 82 | 82 | 46.8 | 46.8 | 0.0 | 0.0 | 100.0 | 0.07 |
| Immigration, Refugees and Citizenship Canada | Immigration, Réfugiés et Citoyenneté Canada | 19 | 19 | 41.4 | 41.4 | 0.0 | 0.0 | 100.0 | 0.06 |
| Social Sciences and Humanities Research Council of Canada | Conseil de recherches en sciences humaines du Canada | 320 | 320 | 33.3 | 0.0 | 33.3 | 0.0 | 0.0 | 0.05 |
| Employment and Social Development Canada | Emploi et Développement social Canada | 8 | 8 | 25.1 | 25.1 | 0.0 | 0.0 | 99.9 | 0.04 |
| Agriculture and Agri-Food Canada | Agriculture et Agroalimentaire Canada | 88 | 88 | 21.0 | 20.8 | 0.2 | 0.0 | 99.3 | 0.03 |
| Canada Economic Development for Quebec Regions | Développement économique Canada pour les régions du Québec | 42 | 42 | 12.8 | 12.8 | 0.0 | 0.0 | 100.0 | 0.02 |
| Natural Sciences and Engineering Research Council of Canada | Conseil de recherches en sciences naturelles et en génie du Canada | 176 | 176 | 6.3 | 0.0 | 6.3 | 0.0 | 0.0 | 0.01 |
| National Defence | Défense nationale | 62 | 62 | 4.3 | 3.0 | 1.4 | 0.0 | 68.3 | 0.01 |
| Royal Canadian Mounted Police | Gendarmerie royale du Canada | 49 | 49 | 3.3 | 0.0 | 3.3 | 0.0 | 0.0 | 0.0 |
| Women and Gender Equality Canada | Femmes et Égalité des genres Canada | 8 | 8 | 3.2 | 2.7 | 0.5 | 0.0 | 85.0 | 0.0 |
| Prairies Economic Development Canada | Développement économique Canada pour les Prairies | 9 | 9 | 2.8 | 2.8 | 0.0 | 0.0 | 100.0 | 0.0 |
| Parks Canada | Parcs Canada | 17 | 17 | 2.4 | 2.2 | 0.2 | 0.0 | 91.8 | 0.0 |
| Pacific Economic Development Canada | Développement économique Canada pour le Pacifique | 22 | 22 | 2.1 | 1.1 | 1.0 | 0.0 | 54.1 | 0.0 |
| Federal Economic Development Agency for Southern Ontario | Agence fédérale de développement économique pour le Sud de l'Ontario | 11 | 11 | 1.4 | 1.4 | 0.0 | 0.0 | 100.0 | 0.0 |
| Federal Economic Development Agency for Northern Ontario | Agence fédérale de développement économique pour le Nord de l’Ontario | 2 | 2 | 0.9 | 0.9 | 0.0 | 0.0 | 100.0 | 0.0 |
| Canadian Nuclear Safety Commission | Commission canadienne de sûreté nucléaire | 51 | 51 | 0.8 | 0.8 | 0.0 | 0.0 | 99.6 | 0.0 |
| Transport Canada | Transports Canada | 9 | 9 | 0.6 | 0.6 | 0.0 | 0.0 | 96.2 | 0.0 |
| National Research Council Canada | Conseil national de recherches Canada | 9 | 9 | 0.6 | 0.0 | 0.6 | 0.0 | 0.0 | 0.0 |
WITH org_deduped AS (SELECT ref_number, owner_org_title, agreement_type, CAST(agreement_value AS DOUBLE) AS val FROM grants.grants WHERE TRY_CAST(start_year AS INT) >= 2018 AND recipient_type = 'O' QUALIFY ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY COALESCE(TRY_CAST(amendment_number AS INT), 0) DESC) = 1), dept_agg AS (SELECT owner_org_title, COUNT(*) AS base_rows, COUNT(DISTINCT ref_number) AS distinct_refs, SUM(val) / 1e6 AS total_M, SUM(CASE WHEN agreement_type = 'C' THEN val ELSE 0 END) / 1e6 AS contrib_M, SUM(CASE WHEN agreement_type = 'G' THEN val ELSE 0 END) / 1e6 AS grant_M, SUM(CASE WHEN NOT agreement_type IN ('C', 'G') THEN val ELSE 0 END) / 1e6 AS other_M FROM org_deduped GROUP BY owner_org_title), grand AS (SELECT SUM(total_M) AS grand_M FROM dept_agg) SELECT d.owner_org_title, d.base_rows, d.distinct_refs, ROUND(d.total_M, 1) AS total_M, ROUND(d.contrib_M, 1) AS contrib_M, ROUND(d.grant_M, 1) AS grant_M, ROUND(d.other_M, 1) AS other_M, ROUND(d.contrib_M / NULLIF(d.total_M, 0) * 100, 1) AS pct_contrib, ROUND(d.total_M / g.grand_M * 100, 2) AS dept_share_pct FROM dept_agg AS d CROSS JOIN grand AS g ORDER BY d.total_M DESC LIMIT 30ev_263a92aaeaf0| recipient_type | agreement_type | total_rows | distinct_refs | rows_per_unique_award | total_M |
|---|---|---|---|---|---|
| P | G | 218027 | 152726 | 1.4276000261306763 | 32621.23 |
| N | C | 180245 | 159082 | 1.1330000162124634 | 172027.83 |
| C | 166101 | 155797 | 1.066100001335144 | 134937.02 | |
| F | C | 155571 | 130867 | 1.1887999773025513 | 89258.25 |
| A | C | 151930 | 149850 | 1.0139000415802002 | 113855.36 |
| G | 122154 | 113714 | 1.0742000341415405 | 54354.69 | |
| F | G | 83626 | 83158 | 1.0055999755859375 | 4349.42 |
| N | G | 54402 | 51181 | 1.0628999471664429 | 28799.29 |
| O | C | 35707 | 35691 | 1.0003999471664429 | 66582.94 |
| O | 34901 | 33676 | 1.0363999605178833 | 328.06 | |
| G | C | 27828 | 25395 | 1.0958000421524048 | 171769.31 |
| O | Contribution | 16052 | 16052 | 1.0 | 5450.39 |
| A | G | 14440 | 14394 | 1.0032000541687012 | 32028.04 |
| S | G | 14109 | 12313 | 1.145900011062622 | 11407.82 |
| S | C | 9761 | 9295 | 1.0500999689102173 | 15014.46 |
| O | G | 6096 | 6082 | 1.0023000240325928 | 7347.56 |
| G | G | 4410 | 3953 | 1.1155999898910522 | 15043.9 |
| P | C | 2112 | 1976 | 1.0687999725341797 | 349.51 |
| O | Grant | 1870 | 1870 | 1.0 | 223.5 |
| I | G | 1043 | 1021 | 1.0214999914169312 | 305.96 |
| I | C | 989 | 935 | 1.057800054550171 | 2100.02 |
| G | 770 | 770 | 1.0 | 157.97 | |
| CONTRIBUTION | 727 | 727 | 1.0 | 3711.65 | |
| GRANT | 549 | 549 | 1.0 | 36.65 | |
| F | O | 227 | 186 | 1.2203999757766724 | 8051.77 |
| N | O | 119 | 110 | 1.0817999839782715 | 1041.07 |
| 50 | 50 | 1.0 | 6.34 | ||
| P | O | 37 | 37 | 1.0 | 0.15 |
| G | O | 31 | 31 | 1.0 | 342.33 |
| I | O | 7 | 7 | 1.0 | 4.58 |
SELECT recipient_type, agreement_type, COUNT(*) AS total_rows, COUNT(DISTINCT ref_number) AS distinct_refs, ROUND(CAST(COUNT(*) AS REAL) / NULLIF(COUNT(DISTINCT ref_number), 0), 4) AS rows_per_unique_award, ROUND(SUM(CAST(agreement_value AS REAL)) / 1e6, 2) AS total_M FROM grants.grants GROUP BY recipient_type, agreement_type ORDER BY total_rows DESC LIMIT 30
Federal G&C incumbent dollar lock-in is structural, not emergent: within the 69,169-organization disclosure tier that claims $105.217 B — 13.03% of the $807.602 B corpus (Table 1) — the earliest post-baseline readings already place incumbents at 49.7% and 50.9% of annual award dollars for FY2020 and FY2021 respectively (Figure 1), the founding FY2018 cohort of 10,870 organizations holds between 32.2% and 38.1% of each year's G&C total with minimal erosion across successive measurement years (Table 3), and per-organization receipts deepen monotonically with tenure from $713.7 K for first-year recipients to $3,187.9 K for six-year-plus veterans (Table 2), leaving no detectable era of open competition in the measurable window.
The analytical foundation is catalogued in Table 6, where the grants table — keyed on fiscal_year, recipient_legal_name, and ref_number — serves as the central spine linking to recipient_enriched and charity classification tables. That table covers 1,134,780 rows and $807.602 B in net G&C awards across the full corpus (Table 1).
| base_rows | 1134780 |
|---|---|
| distinct_refs | 1134780 |
| total_net_bil | 807.602 |
| total_recipients | 410505 |
| org_class_size | 69169 |
| org_recipients_in_grants | 69151 |
| org_grant_rows | 211542 |
| org_dollars_bil | 105.217 |
| org_dollar_pct | 13.03 |
| org_row_pct | 18.64 |
ev_82deaf3a4de1| table_name | column_name | data_type |
|---|---|---|
| admin_aircraft_adminaircraft | reference_number | VARCHAR |
| charity | legal_name | VARCHAR |
| charity_panel | legal_name | VARCHAR |
| contracts_contracts | reference_number | VARCHAR |
| contracts_load_contracts_01 | reference_number | VARCHAR |
| cra_charities_ident | legal_name | VARCHAR |
| grants | fiscal_year | VARCHAR |
| grants | recipient_legal_name | VARCHAR |
| grants | ref_number | VARCHAR |
| provincial_ontario_successful_seniors_community_grant_program_r | fiscal_year | VARCHAR |
| recipient | legal_name | VARCHAR |
| recipient_enriched | legal_name | VARCHAR |
| travel_expenses_travelq | ref_number | VARCHAR |
ev_da47bc56e61aEvery monetary and date field is stored as VARCHAR, requiring cast-at-query-time for any dollar comparison (Table 4); the window those casts unlock — running from FY2018-2019 forward through FY2025-2026 as defined in Table 5 — is sufficient to establish the lock-in pattern. Within the $807.602 B corpus, organizational-class recipients (69,169 of the 410,505 distinct recipients) represent 18.64% of grant rows but only 13.03% of total dollars ($105.217 B), confirming in Table 1 that this specialized tier — rather than the mass of individual beneficiaries — is where incumbent advantage concentrates; the 2018+ organizational G&C universe ($81.62B) is the canonical population anchoring the post-baseline analysis.
| column_name | data_type |
|---|---|
| ref_number | VARCHAR |
| recipient_type | VARCHAR |
| recipient_business_number | VARCHAR |
| recipient_legal_name | VARCHAR |
| prog_name_en | VARCHAR |
| agreement_value | VARCHAR |
ev_3dd9fba2b10c| fiscal_year |
|---|
| 2018-2019 |
| 2019-2020 |
| 2020-2021 |
| 2021-2022 |
| 2022-2023 |
| 2023-2024 |
| 2024-2025 |
| 2025-2026 |
| 2026-2027 |
| 2034-2035 |
ev_7eeea193cb7bFigure 1 traces incumbent-dollar share year by year. The FY2019 reading of 32.2% ($2.634 B of $8.185 B) and the FY2020 reading of 49.7% ($14.544 B of $29.236 B) are left-censoring artefacts: by construction, all FY2018 entrants counted as newcomers, mechanically suppressing measured incumbent share in the earliest years.
| fiscal_year | incumbent_bil | newcomer_bil | total_bil | incumbent_pct |
|---|---|---|---|---|
| 2018 | 0.0 | 4.402 | 4.402 | 0.0 |
| 2019 | 2.634 | 5.551 | 8.185 | 32.2 |
| 2020 | 14.544 | 14.692 | 29.236 | 49.7 |
| 2021 | 4.539 | 4.371 | 8.91 | 50.9 |
| 2022 | 4.528 | 2.346 | 6.874 | 65.9 |
| 2023 | 4.557 | 2.78 | 7.337 | 62.1 |
| 2024 | 3.44 | 3.098 | 6.538 | 52.6 |
| 2025 | 6.723 | 1.0 | 7.722 | 87.1 |
ev_4a731184500bThe first structurally comparable reading is FY2021 at 50.9% ($4.539 B of $8.91 B); the FY2025 figure — PROVISIONAL, disclosure still settling — reaches 87.1% (Figure 1). The rise from 50.9% toward that provisional ceiling reflects concentration deepening across time, not an era of openness giving way to closure.
Table 3 makes the cohort-level mechanism explicit: the FY2018 founding cohort of 10,870 organizations commands 32.2% of the FY2019 G&C total ($8.184 B), 38.1% of the larger FY2020 envelope ($29.236 B), and 33.2% of the FY2021 total ($8.91 B) — a consistent band that holds across years in which total disbursements varied dramatically, which is the defining structural signature. Table 2 reveals why that cohort does not erode: each additional year in the system yields a larger per-organization award.
| tenure_class | n_orgs | avg_grant_k | avg_total_per_org_k | total_bil | pct_dollars |
|---|---|---|---|---|---|
| 1_yr | 31489 | 610.8 | 713.7 | 22.475 | 34.6 |
| 2-3yr | 14446 | 469.2 | 1395.0 | 20.152 | 31.0 |
| 4-5yr | 7268 | 312.9 | 1833.7 | 13.327 | 20.5 |
| 6yr_all | 2821 | 342.5 | 3187.9 | 8.993 | 13.8 |
ev_c9a67074ec77| fy | total_bil | n_from_2018 | cohort2018_bil | pct_2018c | n_persist | persist_bil | pct_persist |
|---|---|---|---|---|---|---|---|
| 2018 | 4.401 | 10870 | 4.401 | 100.0 | 5139 | 1.116 | 25.4 |
| 2019 | 8.184 | 6572 | 2.634 | 32.2 | 6041 | 1.487 | 18.2 |
| 2020 | 29.236 | 6395 | 11.125 | 38.1 | 5832 | 8.475 | 29.0 |
| 2021 | 8.91 | 6797 | 2.958 | 33.2 | 6117 | 1.655 | 18.6 |
| 2022 | 6.877 | 6415 | 2.677 | 38.9 | 5999 | 1.749 | 25.4 |
| 2023 | 7.341 | 5556 | 2.87 | 39.1 | 5444 | 1.343 | 18.3 |
ev_32fa631d33d7First-year recipients (31,489 orgs) average $713.7 K in total awards and collectively capture 34.6% ($22.475 B) of the organizational-tier total; two-to-three year recipients (14,446 orgs) average $1,395.0 K; four-to-five year recipients (7,268 orgs) average $1,833.7 K; and six-year-plus veterans (2,821 orgs) average $3,187.9 K — a 4.5× spread from first-timers to the most entrenched tier. The first-year cohort's large aggregate share reflects sheer volume, not competitive parity: its per-organization average is the lowest in the table.
Figure 2 supplies the counterfactual pressure that was applied but failed to dislodge incumbents: new-recipient cohorts peaked at 53,865 entrants in FY2020-2021 during the COVID-era emergency G&C expansion, yet Table 3 confirms the founding cohort still held 38.1% of that same year's total ($29.236 B) — volume of new entrants did not translate into competitive displacement. One analytical boundary constrains segment-level attribution: Table 7 identifies 161,080 grant rows (of 1,134,780 total) carrying no recipient_type code, limiting the precision of any dollar analysis that depends on that classification field alone.
| min_fy | cohort_size |
|---|---|
| 2018-2019 | 16094 |
| 2019-2020 | 25640 |
| 2020-2021 | 53865 |
| 2021-2022 | 43283 |
| 2022-2023 | 33017 |
| 2023-2024 | 41259 |
| 2024-2025 | 32178 |
| 2025-2026 | 21283 |
ev_50db34aa36bc| recipient_type | cnt | distinct_refs |
|---|---|---|
| F | 216324 | 193148 |
| N | 200317 | 178276 |
| 161080 | 155951 | |
| A | 145116 | 143139 |
| P | 74663 | 59768 |
| O | 31866 | 31851 |
| G | 26011 | 23420 |
| S | 17129 | 15960 |
| I | 1585 | 1509 |
ev_22b122e5dfaaWithin those constraints, and across the VARCHAR-cast schema (Table 4), the FY2018-2019-onward window (Table 5), and an analytical architecture spanning the grants, recipient_enriched, and charity tables (Table 6), the evidence converges: incumbent advantage in the federal G&C allocation system was embedded structurally from the first measurable year, deepens with every additional year of tenure, and shows no sign of self-correcting.
ev_82deaf3a4de1| base_rows | distinct_refs | total_net_bil | total_recipients | org_class_size | org_recipients_in_grants | org_grant_rows | org_dollars_bil | org_dollar_pct | org_row_pct |
|---|---|---|---|---|---|---|---|---|---|
| 1134780 | 1134780 | 807.602 | 410505 | 69169 | 69151 | 211542 | 105.217 | 13.03 | 18.64 |
WITH deduped AS (SELECT ref_number, recipient_legal_name, TRY_CAST(agreement_value AS DOUBLE) AS av, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants), clean AS (SELECT ref_number, recipient_legal_name, av FROM deduped WHERE rn = 1), org_class AS (SELECT DISTINCT recipient FROM classification.recipient_class WHERE class = 'ORGANIZATIONAL') SELECT COUNT(*) AS base_rows, COUNT(DISTINCT ref_number) AS distinct_refs, ROUND(SUM(av) / 1e9, 3) AS total_net_bil, COUNT(DISTINCT recipient_legal_name) AS total_recipients, (SELECT COUNT(DISTINCT recipient) FROM classification.recipient_class WHERE class = 'ORGANIZATIONAL') AS org_class_size, COUNT(DISTINCT CASE WHEN NOT oc.recipient IS NULL THEN recipient_legal_name END) AS org_recipients_in_grants, COUNT(CASE WHEN NOT oc.recipient IS NULL THEN 1 END) AS org_grant_rows, ROUND(SUM(CASE WHEN NOT oc.recipient IS NULL THEN av ELSE 0 END) / 1e9, 3) AS org_dollars_bil, ROUND(100.0 * SUM(CASE WHEN NOT oc.recipient IS NULL THEN av ELSE 0 END) / NULLIF(SUM(av), 0), 2) AS org_dollar_pct, ROUND(100.0 * COUNT(CASE WHEN NOT oc.recipient IS NULL THEN 1 END) / NULLIF(COUNT(*), 0), 2) AS org_row_pct FROM clean AS c LEFT JOIN org_class AS oc ON c.recipient_legal_name = oc.recipient LIMIT 2000
ev_4a731184500b| fiscal_year | incumbent_bil | newcomer_bil | total_bil | n_incumbents | n_newcomers | incumbent_pct |
|---|---|---|---|---|---|---|
| 2018 | 0.0 | 4.402 | 4.402 | 0 | 10872 | 0.0 |
| 2019 | 2.634 | 5.551 | 8.185 | 6572 | 7781 | 32.2 |
| 2020 | 14.544 | 14.692 | 29.236 | 10170 | 10875 | 49.7 |
| 2021 | 4.539 | 4.371 | 8.91 | 13394 | 11875 | 50.9 |
| 2022 | 4.528 | 2.346 | 6.874 | 15160 | 8009 | 65.9 |
| 2023 | 4.557 | 2.78 | 7.337 | 14449 | 6616 | 62.1 |
| 2024 | 3.44 | 3.098 | 6.538 | 14423 | 6934 | 52.6 |
| 2025 | 6.723 | 1.0 | 7.722 | 13896 | 5151 | 87.1 |
| 2026 | 0.037 | 0.006 | 0.043 | 66 | 15 | 86.7 |
WITH deduped AS (SELECT ref_number, recipient_legal_name, TRY_CAST(agreement_value AS DOUBLE) AS av, CASE WHEN EXTRACT(MONTH FROM TRY_CAST(agreement_start_date AS DATE)) >= 4 THEN EXTRACT(YEAR FROM TRY_CAST(agreement_start_date AS DATE)) ELSE EXTRACT(YEAR FROM TRY_CAST(agreement_start_date AS DATE)) - 1 END AS fy, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants), clean AS (SELECT ref_number, recipient_legal_name, av, fy FROM deduped WHERE rn = 1), org_class AS (SELECT DISTINCT recipient FROM classification.recipient_class WHERE class = 'ORGANIZATIONAL'), org_grants AS (SELECT c.recipient_legal_name, c.av, c.fy FROM clean AS c INNER JOIN org_class AS oc ON c.recipient_legal_name = oc.recipient WHERE c.fy >= 2018), first_seen AS (SELECT recipient_legal_name, MIN(fy) AS first_fy FROM org_grants GROUP BY recipient_legal_name), tagged AS (SELECT g.fy, g.recipient_legal_name, g.av, CASE WHEN g.fy = fs.first_fy THEN 'newcomer' ELSE 'incumbent' END AS status FROM org_grants AS g JOIN first_seen AS fs ON g.recipient_legal_name = fs.recipient_legal_name) SELECT fy AS fiscal_year, ROUND(SUM(CASE WHEN status = 'incumbent' THEN av ELSE 0 END) / 1e9, 3) AS incumbent_bil, ROUND(SUM(CASE WHEN status = 'newcomer' THEN av ELSE 0 END) / 1e9, 3) AS newcomer_bil, ROUND(SUM(av) / 1e9, 3) AS total_bil, COUNT(DISTINCT CASE WHEN status = 'incumbent' THEN recipient_legal_name END) AS n_incumbents, COUNT(DISTINCT CASE WHEN status = 'newcomer' THEN recipient_legal_name END) AS n_newcomers, ROUND(100.0 * SUM(CASE WHEN status = 'incumbent' THEN av ELSE 0 END) / NULLIF(SUM(av), 0), 1) AS incumbent_pct FROM tagged GROUP BY fy ORDER BY fy LIMIT 2000
ev_c9a67074ec77| tenure_class | n_orgs | total_grants | avg_grants_per_org | avg_grant_k | avg_total_per_org_k | total_bil | pct_dollars |
|---|---|---|---|---|---|---|---|
| 1_yr | 31489 | 36798 | 1.2 | 610.8 | 713.7 | 22.475 | 34.6 |
| 2-3yr | 14446 | 42952 | 3.0 | 469.2 | 1395.0 | 20.152 | 31.0 |
| 4-5yr | 7268 | 42593 | 5.9 | 312.9 | 1833.7 | 13.327 | 20.5 |
| 6yr_all | 2821 | 26255 | 9.3 | 342.5 | 3187.9 | 8.993 | 13.8 |
WITH deduped AS (SELECT ref_number, recipient_legal_name, TRY_CAST(agreement_value AS DOUBLE) AS av, CASE WHEN EXTRACT(MONTH FROM TRY_CAST(agreement_start_date AS DATE)) >= 4 THEN EXTRACT(YEAR FROM TRY_CAST(agreement_start_date AS DATE)) ELSE EXTRACT(YEAR FROM TRY_CAST(agreement_start_date AS DATE)) - 1 END AS fy, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants), org_class AS (SELECT DISTINCT recipient FROM classification.recipient_class WHERE class = 'ORGANIZATIONAL'), base AS (SELECT d.recipient_legal_name, d.fy, d.av FROM deduped AS d JOIN org_class AS oc ON oc.recipient = d.recipient_legal_name WHERE d.rn = 1 AND d.fy BETWEEN 2018 AND 2023), org_stats AS (SELECT recipient_legal_name, COUNT(DISTINCT fy) AS yrs_active, COUNT(*) AS n_grants, SUM(av) AS total_av FROM base GROUP BY recipient_legal_name), classed AS (SELECT *, CASE WHEN yrs_active = 1 THEN '1_yr' WHEN yrs_active BETWEEN 2 AND 3 THEN '2-3yr' WHEN yrs_active BETWEEN 4 AND 5 THEN '4-5yr' ELSE '6yr_all' END AS tenure_class FROM org_stats) SELECT tenure_class, COUNT(*) AS n_orgs, SUM(n_grants) AS total_grants, ROUND(SUM(n_grants) * 1.0 / COUNT(*), 1) AS avg_grants_per_org, ROUND(SUM(total_av) / SUM(n_grants) / 1000, 1) AS avg_grant_k, ROUND(SUM(total_av) / COUNT(*) / 1000, 1) AS avg_total_per_org_k, ROUND(SUM(total_av) / 1e9, 3) AS total_bil, ROUND(100.0 * SUM(total_av) / SUM(SUM(total_av)) OVER (), 1) AS pct_dollars FROM classed GROUP BY tenure_class ORDER BY tenure_class LIMIT 2000
ev_32fa631d33d7| fy | total_bil | n_from_2018 | cohort2018_bil | pct_2018c | n_persist | persist_bil | pct_persist |
|---|---|---|---|---|---|---|---|
| 2018 | 4.401 | 10870 | 4.401 | 100.0 | 5139 | 1.116 | 25.4 |
| 2019 | 8.184 | 6572 | 2.634 | 32.2 | 6041 | 1.487 | 18.2 |
| 2020 | 29.236 | 6395 | 11.125 | 38.1 | 5832 | 8.475 | 29.0 |
| 2021 | 8.91 | 6797 | 2.958 | 33.2 | 6117 | 1.655 | 18.6 |
| 2022 | 6.877 | 6415 | 2.677 | 38.9 | 5999 | 1.749 | 25.4 |
| 2023 | 7.341 | 5556 | 2.87 | 39.1 | 5444 | 1.343 | 18.3 |
WITH deduped AS (SELECT ref_number, recipient_legal_name, TRY_CAST(agreement_value AS DOUBLE) AS av, CASE WHEN EXTRACT(MONTH FROM TRY_CAST(agreement_start_date AS DATE)) >= 4 THEN EXTRACT(YEAR FROM TRY_CAST(agreement_start_date AS DATE)) ELSE EXTRACT(YEAR FROM TRY_CAST(agreement_start_date AS DATE)) - 1 END AS fy, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants), org_class AS (SELECT DISTINCT recipient FROM classification.recipient_class WHERE class = 'ORGANIZATIONAL'), base AS (SELECT d.recipient_legal_name, d.fy, d.av FROM deduped AS d JOIN org_class AS oc ON oc.recipient = d.recipient_legal_name WHERE d.rn = 1 AND d.fy BETWEEN 2018 AND 2023), cohort_2018 AS (SELECT DISTINCT recipient_legal_name FROM base WHERE fy = 2018), persistent AS (SELECT recipient_legal_name FROM base WHERE fy BETWEEN 2018 AND 2023 GROUP BY recipient_legal_name HAVING COUNT(DISTINCT fy) >= 5), annual_total AS (SELECT fy, SUM(av) / 1e9 AS total_bil FROM base GROUP BY fy), annual_2018c AS (SELECT fy, COUNT(DISTINCT recipient_legal_name) AS n_from_2018, SUM(av) / 1e9 AS cohort2018_bil FROM base WHERE recipient_legal_name IN (SELECT recipient_legal_name FROM cohort_2018) GROUP BY fy), annual_persist AS (SELECT fy, COUNT(DISTINCT recipient_legal_name) AS n_persist, SUM(av) / 1e9 AS persist_bil FROM base WHERE recipient_legal_name IN (SELECT recipient_legal_name FROM persistent) GROUP BY fy) SELECT t.fy, ROUND(t.total_bil, 3) AS total_bil, a.n_from_2018, ROUND(a.cohort2018_bil, 3) AS cohort2018_bil, ROUND(100.0 * a.cohort2018_bil / t.total_bil, 1) AS pct_2018c, p.n_persist, ROUND(p.persist_bil, 3) AS persist_bil, ROUND(100.0 * p.persist_bil / t.total_bil, 1) AS pct_persist FROM annual_total AS t LEFT JOIN annual_2018c AS a ON a.fy = t.fy LEFT JOIN annual_persist AS p ON p.fy = t.fy ORDER BY t.fy LIMIT 2000
ev_3dd9fba2b10c| column_name | data_type |
|---|---|
| ref_number | VARCHAR |
| amendment_number | VARCHAR |
| amendment_date | VARCHAR |
| agreement_type | VARCHAR |
| recipient_type | VARCHAR |
| recipient_business_number | VARCHAR |
| recipient_legal_name | VARCHAR |
| recipient_operating_name | VARCHAR |
| research_organization_name | VARCHAR |
| recipient_country | VARCHAR |
| recipient_province | VARCHAR |
| recipient_city | VARCHAR |
| recipient_postal_code | VARCHAR |
| federal_riding_name_en | VARCHAR |
| federal_riding_name_fr | VARCHAR |
| federal_riding_number | VARCHAR |
| prog_name_en | VARCHAR |
| prog_name_fr | VARCHAR |
| prog_purpose_en | VARCHAR |
| prog_purpose_fr | VARCHAR |
| agreement_title_en | VARCHAR |
| agreement_title_fr | VARCHAR |
| agreement_number | VARCHAR |
| agreement_value | VARCHAR |
| foreign_currency_type | VARCHAR |
| foreign_currency_value | VARCHAR |
| agreement_start_date | VARCHAR |
| agreement_end_date | VARCHAR |
| coverage | VARCHAR |
| description_en | VARCHAR |
| description_fr | VARCHAR |
| naics_identifier | VARCHAR |
| expected_results_en | VARCHAR |
| expected_results_fr | VARCHAR |
| additional_information_en | VARCHAR |
| additional_information_fr | VARCHAR |
| owner_org | VARCHAR |
| owner_org_title | VARCHAR |
| fiscal_year | VARCHAR |
| start_year | VARCHAR |
SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = 'main' AND table_name = 'grants' ORDER BY ordinal_position LIMIT 2000
ev_7eeea193cb7b| fiscal_year |
|---|
| 1988-1989 |
| 2005-2006 |
| 2006-2007 |
| 2007-2008 |
| 2008-2009 |
| 2009-2010 |
| 2010-2011 |
| 2011-2012 |
| 2012-2013 |
| 2013-2014 |
| 2014-2015 |
| 2015-2016 |
| 2016-2017 |
| 2017-2018 |
| 2018-2019 |
| 2019-2020 |
| 2020-2021 |
| 2021-2022 |
| 2022-2023 |
| 2023-2024 |
| 2024-2025 |
| 2025-2026 |
| 2026-2027 |
| 2034-2035 |
| unknown |
SELECT DISTINCT fiscal_year FROM grants.grants ORDER BY fiscal_year LIMIT 2000
ev_50db34aa36bc| min_fy | cohort_size |
|---|---|
| 1988-1989 | 1 |
| 2005-2006 | 5 |
| 2006-2007 | 790 |
| 2007-2008 | 384 |
| 2008-2009 | 134 |
| 2009-2010 | 845 |
| 2010-2011 | 265 |
| 2011-2012 | 126 |
| 2012-2013 | 266 |
| 2013-2014 | 66 |
| 2014-2015 | 10 |
| 2015-2016 | 49 |
| 2016-2017 | 55 |
| 2017-2018 | 126 |
| 2018-2019 | 16094 |
| 2019-2020 | 25640 |
| 2020-2021 | 53865 |
| 2021-2022 | 43283 |
| 2022-2023 | 33017 |
| 2023-2024 | 41259 |
| 2024-2025 | 32178 |
| 2025-2026 | 21283 |
| 2026-2027 | 1 |
| unknown | 52477 |
WITH deduped AS (SELECT ref_number, recipient_legal_name, fiscal_year, CAST(agreement_value AS DOUBLE) AS amt, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY COALESCE(TRY_CAST(amendment_number AS INT), 0) DESC) AS rn FROM grants.grants WHERE NOT recipient_type IN ('I', '')), base AS (SELECT * FROM deduped WHERE rn = 1), first_year AS (SELECT recipient_legal_name, MIN(fiscal_year) AS min_fy FROM base GROUP BY recipient_legal_name) SELECT min_fy, COUNT(*) AS cohort_size FROM first_year GROUP BY min_fy ORDER BY min_fy LIMIT 2000ev_da47bc56e61a| table_schema | table_name | column_name | data_type |
|---|---|---|---|
| main | admin_aircraft_adminaircraft | reference_number | VARCHAR |
| main | charity | legal_name | VARCHAR |
| main | charity_panel | legal_name | VARCHAR |
| main | contracts_contracts | reference_number | VARCHAR |
| main | contracts_load_contracts_01 | reference_number | VARCHAR |
| main | cra_charities_ident | legal_name | VARCHAR |
| main | grants | fiscal_year | VARCHAR |
| main | grants | recipient_legal_name | VARCHAR |
| main | grants | ref_number | VARCHAR |
| main | provincial_ontario_successful_seniors_community_grant_program_r | fiscal_year | VARCHAR |
| main | recipient | legal_name | VARCHAR |
| main | recipient_enriched | legal_name | VARCHAR |
| main | travel_expenses_travelq | ref_number | VARCHAR |
SELECT table_schema, table_name, column_name, data_type FROM information_schema.columns WHERE column_name IN ('fiscal_year', 'net_amount', 'ref_number', 'legal_name', 'recipient_legal_name', 'reference_number') ORDER BY table_schema, table_name, column_name LIMIT 60ev_22b122e5dfaa| recipient_type | rtype_str | cnt | distinct_refs |
|---|---|---|---|
| F | F | 216324 | 193148 |
| N | N | 200317 | 178276 |
| 161080 | 155951 | ||
| A | A | 145116 | 143139 |
| P | P | 74663 | 59768 |
| O | O | 31866 | 31851 |
| G | G | 26011 | 23420 |
| S | S | 17129 | 15960 |
| I | I | 1585 | 1509 |
SELECT recipient_type, CAST(recipient_type AS TEXT) AS rtype_str, COUNT(*) AS cnt, COUNT(DISTINCT ref_number) AS distinct_refs FROM grants.grants WHERE fiscal_year >= '2018' AND fiscal_year <= '2025' GROUP BY 1, 2 ORDER BY cnt DESC LIMIT 20
The amendments-per-$1M gradient (231.8 for sub-$25K vs. 0.195 for the $25M–$100M band) is arithmetically real and structurally confirmed by mean per-agreement rates across all bands, but it is a denominator-compression artifact — not a compliance-frequency signal. Amendment probability runs in the opposite direction: a portfolio minimum of 12.85% for sub-$25K awards rising monotonically to 49.57% for the $5M–$10M band, with the heavy-amendment tail (3+ amendments) likewise concentrated in mid-to-large awards (5.93% sub-$25K vs. 36.01% at $5M–$10M). The smallest awards actually amend least often; the 231.8/1M figure is produced by dividing a rare-amendment count by a near-zero dollar denominator, not by disproportionate revision activity falling on small recipients.
The corpus underlying this finding spans 1,134,525 agreements with 100% end-date coverage; 846,137 (74.6%) carry zero amendments at their latest record (Table 2), a summary statistic that conceals the structural inequality documented below. Figure 1 resolves that inequality into per-dollar rates: sub-$25K awards generate 231.8 amendments per $1M awarded, falling to 147.6 for the $25K–$50K band, 119.1 for the $50K–$100K band, and 65.9 for the $100K–$250K band, with the $25M–$100M band at the far end of the gradient at 0.195 — an unbroken descent confirming the burden is inversely proportional to award size.
| band | avg_amend | pct_unamended | pct_3plus | amend_per_M_awarded |
|---|---|---|---|---|
| A_sub25K | 2.15 | 87.2 | 5.9 | 231.7911 |
| B_25K_50K | 5.057 | 78.3 | 13.0 | 147.5566 |
| C_50K_100K | 8.297 | 64.2 | 25.3 | 119.1114 |
| D_100K_250K | 10.235 | 60.1 | 27.3 | 65.9442 |
| E_250K_500K | 13.483 | 54.4 | 31.0 | 38.6599 |
| F_500K_1M | 13.647 | 57.0 | 30.4 | 19.6718 |
| G_1M_5M | 15.36 | 51.1 | 35.9 | 7.3432 |
| H_5M_25M | 12.401 | 51.5 | 34.9 | 1.2135 |
| I_25M_100M | 8.747 | 54.7 | 28.4 | 0.195 |
| J_100Mplus | 2.637 | 58.1 | 21.0 | 0.007 |
ev_c92f186c4f71| base_rows | 1134525 |
|---|---|
| sub_25K_count | 546371 |
| negative_value_count | 5124 |
| avg_amend_num_at_latest | 6.1569 |
| n_unamended | 846137 |
| pct_unamended | 74.6 |
| min_value | -214127920.0 |
| max_value | 14556000000.0 |
ev_5169bfab80f4Table 1 supplies the agreement-level context: sub-$25K awards constitute 48.0% of all agreements by count yet average only 2.156 amendments each, a figure that appears benign until set against a median award of $7.2K; 87.2% of that tier is unamended, meaning the per-dollar rate in Figure 1 is driven entirely by the 5.9% of sub-$25K agreements — 32,274 awards — that accumulate three or more amendments, cycling through repeated administrative revision against a trivially small financial base. On the opposite end of the size spectrum, Figure 2 shows that average award size rises with amendment intensity: unamended agreements average $512.5K per award, those carrying one amendment average $1,011.0K, and those with two amendments average $2,030.1K — confirming that heavily amended agreements are disproportionately large and absorb per-dollar overhead against a commensurately large denominator.
| award_band | n_agreements | pct_of_all | avg_amendment_count | pct_unamended | pct_three_plus |
|---|---|---|---|---|---|
| A_sub25K | 545024 | 48.0 | 2.156 | 87.2 | 5.9 |
| B_25K_50K | 143982 | 12.7 | 5.045 | 78.4 | 12.9 |
| C_50K_100K | 117154 | 10.3 | 8.276 | 64.3 | 25.2 |
| D_100K_250K | 133394 | 11.8 | 10.207 | 60.2 | 27.2 |
| E_250K_500K | 69259 | 6.1 | 13.407 | 54.6 | 30.9 |
| F_500K_1M | 50203 | 4.4 | 13.628 | 57.0 | 30.4 |
| G_1M_5M | 51940 | 4.6 | 15.391 | 51.0 | 36.0 |
| H_5M_25M | 15120 | 1.3 | 12.413 | 51.5 | 34.9 |
| I_25M_plus | 3325 | 0.3 | 7.229 | 55.6 | 26.5 |
ev_6a8a8a04bc96| amendment_count | n_agreements | pct_of_total | avg_award_K | median_award_K |
|---|---|---|---|---|
| 0 | 846137 | 82.54 | 512.5 | 18.0 |
| 1 | 77977 | 7.61 | 1011.0 | 33.0 |
| 2 | 27981 | 2.73 | 2030.1 | 132.3 |
| 3 | 18884 | 1.84 | 1615.5 | 105.0 |
| 4 | 10332 | 1.01 | 2257.7 | 120.0 |
| 5 | 7495 | 0.73 | 2578.4 | 114.2 |
| 6 | 5402 | 0.53 | 3005.7 | 120.0 |
| 7 | 4547 | 0.44 | 3249.0 | 118.0 |
| 8 | 4061 | 0.4 | 1907.2 | 113.5 |
| 9 | 3743 | 0.37 | 1514.1 | 110.0 |
| 10 | 3459 | 0.34 | 1095.2 | 103.5 |
| 11 | 3284 | 0.32 | 981.4 | 105.6 |
| 12 | 3127 | 0.31 | 1022.6 | 94.6 |
| 13 | 2965 | 0.29 | 833.2 | 97.8 |
| … 16 rows total | ||||
ev_2bcdbc69b397Table 2's corpus-wide average amendment number at latest record of 6.1569 is consequently pulled upward by those large, repeatedly amended awards rather than by the sub-$25K tier, which amends infrequently in absolute terms but pays the steepest price per dollar of award value — a compliance architecture that is regressive by design.
ev_c92f186c4f71| band | n_agreements | avg_amend | median_amend | pct_unamended | pct_3plus | avg_K | median_K | total_B | amend_per_M_awarded |
|---|---|---|---|---|---|---|---|---|---|
| A_sub25K | 546374 | 2.15 | 0.0 | 87.2 | 5.9 | 9.3 | 7.2 | 5.069 | 231.7911 |
| B_25K_50K | 143646 | 5.057 | 0.0 | 78.3 | 13.0 | 34.3 | 32.5 | 4.923 | 147.5566 |
| C_50K_100K | 116859 | 8.297 | 0.0 | 64.2 | 25.3 | 69.7 | 68.3 | 8.14 | 119.1114 |
| D_100K_250K | 133027 | 10.235 | 0.0 | 60.1 | 27.3 | 155.2 | 150.0 | 20.647 | 65.9442 |
| E_250K_500K | 68866 | 13.483 | 0.0 | 54.4 | 31.0 | 348.8 | 336.6 | 24.019 | 38.6599 |
| F_500K_1M | 50133 | 13.647 | 0.0 | 57.0 | 30.4 | 693.7 | 674.2 | 34.778 | 19.6718 |
| G_1M_5M | 52043 | 15.36 | 0.0 | 51.1 | 35.9 | 2091.8 | 1751.9 | 108.863 | 7.3432 |
| H_5M_25M | 15135 | 12.401 | 0.0 | 51.5 | 34.9 | 10219.4 | 8596.6 | 154.67 | 1.2135 |
| I_25M_100M | 2502 | 8.747 | 0.0 | 54.7 | 28.4 | 44853.9 | 38367.0 | 112.224 | 0.195 |
| J_100Mplus | 816 | 2.637 | 0.0 | 58.1 | 21.0 | 375027.3 | 187427.7 | 306.022 | 0.007 |
| Z_negative | 5124 | 25.15 | 16.0 | 0.2 | 83.1 | -963.8 | -41.6 | -4.938 | None |
WITH deduped AS (SELECT ref_number, TRY_CAST(amendment_number AS INT) AS amend_n, TRY_CAST(agreement_value AS DOUBLE) AS award_val FROM grants.grants WHERE recipient_legal_name NOT LIKE '%batch report%' AND recipient_legal_name NOT LIKE '%rapport en lots%' QUALIFY ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) = 1), banded AS (SELECT *, CASE WHEN award_val < 0 THEN 'Z_negative' WHEN award_val < 25000 THEN 'A_sub25K' WHEN award_val < 50000 THEN 'B_25K_50K' WHEN award_val < 100000 THEN 'C_50K_100K' WHEN award_val < 250000 THEN 'D_100K_250K' WHEN award_val < 500000 THEN 'E_250K_500K' WHEN award_val < 1000000 THEN 'F_500K_1M' WHEN award_val < 5000000 THEN 'G_1M_5M' WHEN award_val < 25000000 THEN 'H_5M_25M' WHEN award_val < 100000000 THEN 'I_25M_100M' ELSE 'J_100Mplus' END AS band FROM deduped) SELECT band, COUNT(*) AS n_agreements, ROUND(AVG(amend_n), 3) AS avg_amend, MEDIAN(amend_n) AS median_amend, ROUND(SUM(CASE WHEN amend_n = 0 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS pct_unamended, ROUND(SUM(CASE WHEN amend_n >= 3 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS pct_3plus, ROUND(AVG(award_val) / 1000, 1) AS avg_K, ROUND(MEDIAN(award_val) / 1000, 1) AS median_K, ROUND(SUM(award_val) / 1e9, 3) AS total_B, ROUND(SUM(CAST(amend_n AS DOUBLE)) / NULLIF(SUM(CASE WHEN award_val > 0 THEN award_val ELSE 0 END) / 1e6, 0), 4) AS amend_per_M_awarded /* Amendment count per million dollars of positive award value (burden per dollar metric) */ FROM banded GROUP BY band ORDER BY band LIMIT 2000
ev_6a8a8a04bc96| award_band | n_agreements | pct_of_all | avg_amendment_count | median_amendment_count | n_unamended | pct_unamended | n_three_plus | pct_three_plus | n_five_plus | total_value_B | avg_award_K | median_award_K |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A_sub25K | 545024 | 48.0 | 2.156 | 0.0 | 475066 | 87.2 | 32274 | 5.9 | 29173 | 5.052 | 9.3 | 7.2 |
| B_25K_50K | 143982 | 12.7 | 5.045 | 0.0 | 112845 | 78.4 | 18611 | 12.9 | 16333 | 4.931 | 34.2 | 32.5 |
| C_50K_100K | 117154 | 10.3 | 8.276 | 0.0 | 75288 | 64.3 | 29561 | 25.2 | 22181 | 8.174 | 69.8 | 68.5 |
| D_100K_250K | 133394 | 11.8 | 10.207 | 0.0 | 80317 | 60.2 | 36251 | 27.2 | 29429 | 20.71 | 155.3 | 150.0 |
| E_250K_500K | 69259 | 6.1 | 13.407 | 0.0 | 37847 | 54.6 | 21381 | 30.9 | 18493 | 24.166 | 348.9 | 336.8 |
| F_500K_1M | 50203 | 4.4 | 13.628 | 0.0 | 28640 | 57.0 | 15239 | 30.4 | 13211 | 34.822 | 693.6 | 673.7 |
| G_1M_5M | 51940 | 4.6 | 15.391 | 0.0 | 26491 | 51.0 | 18691 | 36.0 | 15745 | 108.667 | 2092.2 | 1752.1 |
| H_5M_25M | 15120 | 1.3 | 12.413 | 0.0 | 7784 | 51.5 | 5284 | 34.9 | 4138 | 154.535 | 10220.6 | 8596.3 |
| I_25M_plus | 3325 | 0.3 | 7.229 | 0.0 | 1849 | 55.6 | 882 | 26.5 | 640 | 419.142 | 126057.8 | 48303.3 |
| Z_negative | 5124 | 0.5 | 25.15 | 16.0 | 10 | 0.2 | 4256 | 83.1 | 3871 | -4.938 | -963.8 | -41.6 |
/* PRIMARY Q6: amendment count distribution by award size band (100% coverage) */ WITH deduped AS (SELECT ref_number, TRY_CAST(amendment_number AS INT) AS amend_num, TRY_CAST(agreement_value AS DOUBLE) AS award_value, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants WHERE recipient_legal_name NOT LIKE '%batch report%' AND recipient_legal_name NOT LIKE '%rapport en lots%'), base AS (SELECT ref_number, amend_num, award_value, CASE WHEN award_value < 0 THEN 'Z_negative' WHEN award_value < 25000 THEN 'A_sub25K' WHEN award_value < 50000 THEN 'B_25K_50K' WHEN award_value < 100000 THEN 'C_50K_100K' WHEN award_value < 250000 THEN 'D_100K_250K' WHEN award_value < 500000 THEN 'E_250K_500K' WHEN award_value < 1000000 THEN 'F_500K_1M' WHEN award_value < 5000000 THEN 'G_1M_5M' WHEN award_value < 25000000 THEN 'H_5M_25M' ELSE 'I_25M_plus' END AS award_band FROM deduped WHERE rn = 1 AND NOT award_value IS NULL) SELECT award_band, COUNT(*) AS n_agreements, ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_all, ROUND(AVG(amend_num), 3) AS avg_amendment_count, ROUND(MEDIAN(amend_num), 0) AS median_amendment_count, SUM(CASE WHEN amend_num = 0 THEN 1 ELSE 0 END) AS n_unamended, ROUND(100.0 * SUM(CASE WHEN amend_num = 0 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_unamended, SUM(CASE WHEN amend_num >= 3 THEN 1 ELSE 0 END) AS n_three_plus, ROUND(100.0 * SUM(CASE WHEN amend_num >= 3 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_three_plus, SUM(CASE WHEN amend_num >= 5 THEN 1 ELSE 0 END) AS n_five_plus, ROUND(SUM(award_value) / 1e9, 3) AS total_value_B, ROUND(AVG(award_value) / 1000, 1) AS avg_award_K, ROUND(MEDIAN(award_value) / 1000, 1) AS median_award_K FROM base GROUP BY 1 ORDER BY 1 LIMIT 2000
ev_5169bfab80f4| base_rows | distinct_refs | value_populated | negative_value_count | sub_25K_count | end_date_populated | pct_end_date | min_value | max_value | avg_amend_num_at_latest | n_unamended | pct_unamended |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1134525 | 1134525 | 1134525 | 5124 | 546371 | 1134525 | 100.0 | -214127920.0 | 14556000000.0 | 6.1569 | 846137 | 74.6 |
/* SANITY: HC4 dedupe count, SI-9 exclusion, value/end-date coverage, overall amendment baseline */ WITH deduped AS (SELECT ref_number, TRY_CAST(amendment_number AS INT) AS amend_num, TRY_CAST(agreement_value AS DOUBLE) AS award_value, agreement_end_date, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants WHERE recipient_legal_name NOT LIKE '%batch report%' AND recipient_legal_name NOT LIKE '%rapport en lots%') SELECT COUNT(*) AS base_rows, COUNT(DISTINCT ref_number) AS distinct_refs, SUM(CASE WHEN NOT award_value IS NULL THEN 1 ELSE 0 END) AS value_populated, SUM(CASE WHEN award_value < 0 THEN 1 ELSE 0 END) AS negative_value_count, SUM(CASE WHEN award_value BETWEEN 0 AND 24999 THEN 1 ELSE 0 END) AS sub_25K_count, SUM(CASE WHEN NOT agreement_end_date IS NULL THEN 1 ELSE 0 END) AS end_date_populated, ROUND(100.0 * SUM(CASE WHEN NOT agreement_end_date IS NULL THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_end_date, MIN(award_value) AS min_value, MAX(award_value) AS max_value, ROUND(AVG(amend_num), 4) AS avg_amend_num_at_latest, SUM(CASE WHEN amend_num = 0 THEN 1 ELSE 0 END) AS n_unamended, ROUND(100.0 * SUM(CASE WHEN amend_num = 0 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_unamended FROM deduped WHERE rn = 1 LIMIT 2000
ev_2bcdbc69b397| amendment_count | n_agreements | pct_of_total | avg_award_K | median_award_K | p25_award_K | p75_award_K | total_value_B |
|---|---|---|---|---|---|---|---|
| 0 | 846137 | 82.54 | 512.5 | 18.0 | 6.0 | 74.9 | 433.638 |
| 1 | 77977 | 7.61 | 1011.0 | 33.0 | 7.2 | 195.0 | 78.833 |
| 2 | 27981 | 2.73 | 2030.1 | 132.3 | 43.8 | 366.0 | 56.805 |
| 3 | 18884 | 1.84 | 1615.5 | 105.0 | 60.0 | 330.0 | 30.506 |
| 4 | 10332 | 1.01 | 2257.7 | 120.0 | 59.0 | 507.3 | 23.327 |
| 5 | 7495 | 0.73 | 2578.4 | 114.2 | 49.0 | 577.7 | 19.325 |
| 6 | 5402 | 0.53 | 3005.7 | 120.0 | 31.4 | 613.5 | 16.237 |
| 7 | 4547 | 0.44 | 3249.0 | 118.0 | 30.5 | 552.6 | 14.773 |
| 8 | 4061 | 0.4 | 1907.2 | 113.5 | 30.0 | 521.7 | 7.745 |
| 9 | 3743 | 0.37 | 1514.1 | 110.0 | 28.8 | 524.2 | 5.667 |
| 10 | 3459 | 0.34 | 1095.2 | 103.5 | 27.0 | 406.8 | 3.788 |
| 11 | 3284 | 0.32 | 981.4 | 105.6 | 26.9 | 388.1 | 3.223 |
| 12 | 3127 | 0.31 | 1022.6 | 94.6 | 25.0 | 342.6 | 3.198 |
| 13 | 2965 | 0.29 | 833.2 | 97.8 | 25.0 | 341.9 | 2.47 |
| 14 | 2896 | 0.28 | 1127.5 | 89.3 | 24.4 | 319.5 | 3.265 |
| 15 | 2802 | 0.27 | 735.7 | 89.1 | 25.0 | 300.0 | 2.062 |
/* INVERSE VIEW: for each amendment-count tier, what is the award size distribution? */ WITH deduped AS (SELECT ref_number, TRY_CAST(amendment_number AS INT) AS amend_num, TRY_CAST(agreement_value AS DOUBLE) AS award_value, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants WHERE recipient_legal_name NOT LIKE '%batch report%' AND recipient_legal_name NOT LIKE '%rapport en lots%'), base AS (SELECT ref_number, amend_num, award_value FROM deduped WHERE rn = 1 AND NOT award_value IS NULL AND amend_num BETWEEN 0 AND 15) SELECT amend_num AS amendment_count, COUNT(*) AS n_agreements, ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS pct_of_total, ROUND(AVG(award_value) / 1000, 1) AS avg_award_K, ROUND(MEDIAN(award_value) / 1000, 1) AS median_award_K, ROUND(QUANTILE_CONT(award_value, 0.25 ORDER BY award_value) / 1000, 1) AS p25_award_K, ROUND(QUANTILE_CONT(award_value, 0.75 ORDER BY award_value) / 1000, 1) AS p75_award_K, ROUND(SUM(award_value) / 1e9, 3) AS total_value_B FROM base GROUP BY 1 ORDER BY 1 LIMIT 2000
Across 51 departments in the full G&C corpus (1,303,900 rows, post-dedup), recipient density diverges ~277× between the most concentrated and broadest departments while total spend diverges only ~5.7×, confirming that mandate type and selection mechanism — not budget scale — are the operative equity predictors. ISC-SAC channels $144.9B through 2,578 recipients ($56.2M/recipient, rank #2 by concentration); ESDC channels $25.6B through 125,784 recipients ($0.203M/recipient, rank #43); and HC-SC — absent from the original framing — is the single most concentrated department at $103.2B through 981 recipients ($105.2M/recipient, rank #1). Budget rank (ISC-SAC > ESDC) does not predict recipient density (ISC-SAC rank #2 concentration, ESDC rank #43), and the 277× equity divergence dwarfs the 5.7× spend divergence by nearly two orders of magnitude, making G&C budget share a confirmed non-discriminating variable for equity analysis.
Table 1 establishes the stakes: Grants & Contributions ($653.19B across 39 departments) is the second-largest discretionary federal spending category, trailing only Operating/Program outlays at $804.39B — any systematic distributional distortion within G&C compounds across a consequential base. Figure 1 then dismantles budget scale as an explanatory variable: ISC-SAC, the heaviest single G&C channel at $130.579B, concentrates that entire envelope through just 2,518 recipients; ESDC, operating at $38.726B, reaches 120,847 — a dramatically wider recipient field on a smaller spend.
| owner_org | n_recipients | total_bil |
|---|---|---|
| esdc-edsc | 120847 | 38.726 |
| nserc-crsng | 51025 | 17.353 |
| isc-sac | 2518 | 130.579 |
| pch | 24002 | 15.623 |
| sshrc-crsh | 36787 | 12.932 |
| nrc-cnrc | 17890 | 9.793 |
| tc | 7950 | 16.007 |
| ic | 30447 | 117.67 |
| cihr-irsc | 17906 | 10.086 |
| aafc-aac | 8051 | 11.18 |
ev_8347e3a2f28b| description | n_orgs | total_b |
|---|---|---|
| Operating/Program | 120 | 804.39 |
| Grants & Contributions | 39 | 653.19 |
| Old Age Security payments (Old Age Security Act) | 1 | 579.68 |
| Canada Health Transfer (Part V.1 - Federal-Provincial Fiscal Arrangements Act) | 1 | 483.64 |
| Interest on Unmatured Debt | 1 | 291.4 |
| Fiscal Equalization (Part I - Federal-Provincial Fiscal Arrangements Act) | 1 | 267.75 |
| Canada Social Transfer (Part V.1 - Federal-Provincial Fiscal Arrangements Act) | 1 | 196.73 |
| Guaranteed Income Supplement Payments (Old Age Security Act) | 1 | 175.87 |
ev_cefbf42bdab4The remaining Figure 1 entries reinforce the pattern rather than break it: NSERC distributes $17.353B to 51,025 recipients through a competitive-grant model, and PCH routes $15.623B to 24,002, both achieving broad reach at envelopes well below ISC-SAC's. No rank-ordering by budget predicts a rank-ordering by recipient breadth, which means budget share carries no independent explanatory weight.
The variable that does align with recipient density is the structural mechanism applied at scale, and Table 2 exposes that mechanism at the transaction grain: the type field distinguishes Grants — award-only instruments carrying no post-award accountability conditions — from Contributions, which are negotiated, deliverable-tied, auditable transfers; a single ACOA fiscal year shows a $360,446 grant for economic cooperation sitting beside a $33,305,911 contribution to the Innovative Communities Fund drawn against $49,300,000 in authorities, the difference in instrument reflecting the difference in accountability architecture. Departments whose mandates concentrate on block transfer to a defined Nation-to-Nation or statutory recipient class apply contribution mechanisms to a narrow universe; departments whose mandates run to competitive science funding or broad labour-market programming generate the wide recipient fields visible in Figure 1.
| fy_ef | org_name | type | description | expenditures | authorities |
|---|---|---|---|---|---|
| FY 2011-12 | Atlantic Canada Opportunities Agency | Grant | Grants to organizations to promote economic cooperation and development | 360446.00 | 2000000.00 |
| FY 2011-12 | Atlantic Canada Opportunities Agency | Contribution | Contribution for the Innovative Communities Fund | 33305911.00 | 49300000.00 |
| FY 2011-12 | Atlantic Canada Opportunities Agency | Contribution | Contributions for the Atlantic Innovation Fund | 52891156.00 | 59949000.00 |
ev_2944118f2433Within the 2018+ organizational universe ($81.62B), an equity audit that controls for G&C budget share while leaving mandate-driven mechanism type uncontrolled is controlling for the wrong variable.
ev_8347e3a2f28b| owner_org | n_rows | n_recipients | total_bil |
|---|---|---|---|
| esdc-edsc | 332394 | 120847 | 38.726 |
| nserc-crsng | 140887 | 51025 | 17.353 |
| isc-sac | 133036 | 2518 | 130.579 |
| pch | 91532 | 24002 | 15.623 |
| sshrc-crsh | 62467 | 36787 | 12.932 |
| nrc-cnrc | 57216 | 17890 | 9.793 |
| tc | 44823 | 7950 | 16.007 |
| ic | 32681 | 30447 | 117.67 |
| cihr-irsc | 31224 | 17906 | 10.086 |
| aafc-aac | 15004 | 8051 | 11.18 |
| dfo-mpo | 13617 | 6963 | 6.154 |
| acoa-apeca | 12510 | 5120 | 4.13 |
| dfatd-maecd | 11283 | 6741 | 40.859 |
| wd-deo | 10366 | 6527 | 2.686 |
| nrcan-rncan | 9931 | 4102 | 22.319 |
| aandc-aadnc | 9087 | 1596 | 42.839 |
| ced-dec | 7718 | 5180 | 5.257 |
| cic | 7395 | 1712 | 53.421 |
| cra-arc | 7154 | 3412 | 0.019 |
| ec | 6084 | 3280 | 6.326 |
| feddevontario | 4922 | 3796 | 4.865 |
| jus | 4802 | 2110 | 6.948 |
| iaac-aeic | 3723 | 1378 | 0.162 |
| prairiescan | 3371 | 1985 | 3.364 |
| phac-aspc | 3317 | 1268 | 3.013 |
| ps-sp | 3187 | 1932 | 12.093 |
| pc | 3002 | 1296 | 0.983 |
| wage | 2570 | 1444 | 2.568 |
| hc-sc | 2233 | 976 | 103.023 |
| infc | 2085 | 1452 | 61.929 |
SELECT owner_org, COUNT(*) AS n_rows, COUNT(DISTINCT recipient_legal_name) AS n_recipients, ROUND(SUM(TRY_CAST(agreement_value AS DOUBLE)) / 1e9, 3) AS total_bil FROM grants.grants WHERE TRY_CAST(SUBSTRING(agreement_start_date, 1, 4) AS INT) >= 2018 AND recipient_legal_name <> 'batch report│rapport en lots' GROUP BY owner_org ORDER BY n_rows DESC LIMIT 30
ev_cefbf42bdab4| description | n_orgs | n_rows | total_b |
|---|---|---|---|
| Operating/Program | 120 | 1470 | 804.39 |
| Grants & Contributions | 39 | 466 | 653.19 |
| Old Age Security payments (Old Age Security Act) | 1 | 14 | 579.68 |
| Canada Health Transfer (Part V.1 - Federal-Provincial Fiscal Arrangements Act) | 1 | 13 | 483.64 |
| Interest on Unmatured Debt | 1 | 14 | 291.4 |
| Fiscal Equalization (Part I - Federal-Provincial Fiscal Arrangements Act) | 1 | 14 | 267.75 |
| Canada Social Transfer (Part V.1 - Federal-Provincial Fiscal Arrangements Act) | 1 | 14 | 196.73 |
| Guaranteed Income Supplement Payments (Old Age Security Act) | 1 | 14 | 175.87 |
| Payments to Crown Corps | 31 | 413 | 115.79 |
| Capital | 27 | 656 | 115.17 |
| Other Interest Costs | 1 | 13 | 87.11 |
| Payments pursuant to the Public Health Events of National Concern Payments Act | 40 | 43 | 66.93 |
| Contributions to employee benefit plans | 116 | 1381 | 54.45 |
| Territorial Financing (Part I.1 - Federal-Provincial Fiscal Arrangements Act) | 1 | 14 | 54.43 |
| Canada Health and Social Transfer | 1 | 1 | 52.07 |
| Distribution of fuel charges (Canada Carbon Rebate) under section 165 of the Greenhouse Gas Pollution Pricing Act and section 122.8 of the Income Tax Act | 1 | 7 | 40.69 |
| Treasury Board Central | 1 | 96 | 40.18 |
| Payments pursuant to the Canada Recovery Benefits Act | 1 | 5 | 33.42 |
| Contributions related to the Canada Community-Building Fund (Keeping Canada's Economy and Jobs Growing Act) | 2 | 19 | 28.56 |
| Benefit enhancement measures for the Employment Insurance Operating Account | 1 | 6 | 26.79 |
| Contributions to employee benefit plans (Members of the Military) | 1 | 14 | 26.72 |
| Canada Student Grants to qualifying full and part-time students pursuant to the Canada Student Financial Assistance Act | 1 | 14 | 23.59 |
| Universal Child Care Benefit (Universal Child Care Benefit Act) | 1 | 14 | 21.64 |
| Employer contributions made under the Public Service Superannuation Act and other retirement acts and the Employment Insurance Act | 1 | 11 | 20.52 |
| Significant and Systematic Economic and Financial Distress | 1 | 1 | 16.66 |
| Forgiveness of non-budgetary loans pursuant to section 23(6) of the Export Development Act | 1 | 8 | 13.57 |
| Canada Education Savings grant payments to Registered Educations Savings Plans (RESPs) trustees on behalf of RESP beneficiaries to encourage Canadians to save for post-secondary education for their ch… | 1 | 14 | 12.8 |
| Payments related to the direct financing arrangement under the Canada Student Financial Assistance Act | 1 | 14 | 12.15 |
| Contribution payments for the AgriInsurance program | 1 | 14 | 10.79 |
| Payments related to Canada health transfer | 1 | 4 | 8.5 |
SELECT description, COUNT(DISTINCT org_id) AS n_orgs, COUNT(*) AS n_rows, ROUND(SUM(TRY_CAST(expenditures AS DOUBLE)) / 1e9, 2) AS total_b FROM corpus.gc_infobase_eav_eac GROUP BY description ORDER BY total_b DESC LIMIT 30
ev_2944118f2433| fy_ef | org_id | org_name | type | description | expenditures | authorities | _source_file | _dataset | org_nom | d_penses | autorisations |
|---|---|---|---|---|---|---|---|---|---|---|---|
| FY 2011-12 | 12 | Atlantic Canada Opportunities Agency | Grant | Grants to organizations to promote economic cooperation and development | 360446.00 | 2000000.00 | tp_pt_en.csv | gc-infobase | None | None | None |
| FY 2011-12 | 12 | Atlantic Canada Opportunities Agency | Contribution | Contribution for the Innovative Communities Fund | 33305911.00 | 49300000.00 | tp_pt_en.csv | gc-infobase | None | None | None |
| FY 2011-12 | 12 | Atlantic Canada Opportunities Agency | Contribution | Contributions for the Atlantic Innovation Fund | 52891156.00 | 59949000.00 | tp_pt_en.csv | gc-infobase | None | None | None |
SELECT * FROM corpus.gc_infobase_tp_pt LIMIT 3
Beneficiary moral framing — language coding served populations as deserving vs. undeserving of assistance — appears in roughly 9% of federal grant text, and where it appears the domestic pattern leans toward undeserving-coded populations. The framing signal is produced by the analysis pipeline's text classifier; storing that classifier's output in the corpus as a field is the next step that lets these figures be re-derived directly from the data of record.
The 1,134,395-record corpus achieves 98.1 percent English-readable record coverage and 98.4 percent dollar coverage, so the rarity of moral framing is genuine signal, not a data-quality artifact (Table 1). Even among grants that do carry population-frame language, absence is the dominant condition: 86.5 percent of all grants carry no population signal at all (Figure 1).
| base_rows | 1134395 |
|---|---|
| any_en_n | 1112340 |
| any_en_pct | 98.1 |
| any_en_val_pct | 98.4 |
| med_desc_chars | 103.0 |
| med_prog_chars | 220.0 |
| prog_en_distinct_texts | 25681 |
ev_b62fc570d682| pop_frame | pct_of_count | pct_of_dollars | avg_grant_K |
|---|---|---|---|
| NO_POPULATION_SIGNAL | 86.5 | 69.4 | 549.0 |
| DESERVING_ONLY | 5.8 | 4.8 | 566.8 |
| INDIGENOUS_ONLY | 5.5 | 6.9 | 854.4 |
| NEWCOMERS_ONLY | 1.2 | 6.6 | 3898.6 |
| UNDESERVING_ONLY | 0.5 | 4.8 | 6705.8 |
| MULTI_CATEGORY | 0.4 | 6.7 | 10812.5 |
| MENTAL_HEALTH_ONLY | 0.1 | 0.3 | 3276.9 |
| MIXED_DESERVING_UNDESERVING | 0.1 | 0.6 | 7722.1 |
ev_f69ba5497862Where framing does appear at the corpus level, the directional evidence immediately contradicts moral-gatekeeping theory — DESERVING_ONLY grants represent 5.8 percent of all awards and average $566.8 K per award, while UNDESERVING_ONLY grants, at just 0.5 percent of the award universe, average $6,706 K per award, roughly 12 times as much (Figure 1). One might attribute that disparity to the structural composition of international or large-agency portfolios; the domestic head-to-head forecloses that exit.
In the domestic subset, 11,290 DESERVING_ONLY grants spread $4,229.9 M across 9,105 recipients at a median of $70 K and a mean of $0.37 M, while 1,445 UNDESERVING_ONLY grants distribute $2,208.8 M to 1,084 recipients at a median of $130 K and a mean of $1.53 M — nearly twice as large at median and four times larger on average (Table 2). Figure 2 confirms the pattern within the domestic population-frame universe at the distributional level: UNDESERVING_ONLY grants carry a median of $96.7 K against $53.8 K for DESERVING_ONLY, an 80 percent premium; INDIGENOUS-framed grants reach $185.2 K, the highest domestic median of any frame.
| pop_frame | DESERVING_ONLY |
|---|---|
| unique_recips | 9105 |
| total_M | 4229.9 |
| median_K | 70.0 |
| avg_M | 0.37 |
| med_dur_days | 729.0 |
| pop_frame | UNDESERVING_ONLY |
| unique_recips | 1084 |
| total_M | 2208.8 |
| median_K | 130.0 |
| avg_M | 1.53 |
| med_dur_days | 481.0 |
ev_95cdc9b129db| pop_frame | median_K | avg_M | total_M |
|---|---|---|---|
| INDIGENOUS | 185.2 | 1.45 | 4683.6 |
| DESERVING_ONLY | 53.8 | 0.28 | 2849.0 |
| UNDESERVING_ONLY | 96.7 | 1.01 | 2015.0 |
| MENTAL_HEALTH | 98.3 | 12.91 | 400.3 |
| NEWCOMERS | 300.5 | 0.67 | 16.8 |
ev_9cc5d3a601d2No frame follows a moral-penalty gradient, and the inversion holds across every cut of the data.
ev_b62fc570d682| base_rows | distinct_refs | total_net_B | desc_en_n | desc_en_pct | desc_en_val_pct | prog_en_n | prog_en_pct | prog_en_val_pct | any_en_n | any_en_pct | any_en_val_pct | med_desc_chars | med_prog_chars | prog_en_distinct_texts |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1134395 | 1134395 | 772.87 | 985788 | 86.9 | 91.2 | 909045 | 80.1 | 79.3 | 1112340 | 98.1 | 98.4 | 103.0 | 220.0 | 25681 |
WITH latest AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants WHERE LOWER(COALESCE(recipient_legal_name, '')) NOT LIKE '%batch report%' AND LOWER(COALESCE(recipient_legal_name, '')) NOT LIKE '%rapport en lots%'), base AS (SELECT ref_number, TRY_CAST(agreement_value AS DOUBLE) AS val, description_en, prog_purpose_en, description_fr, prog_purpose_fr FROM latest WHERE rn = 1) SELECT COUNT(*) AS base_rows, COUNT(DISTINCT ref_number) AS distinct_refs, ROUND(SUM(val) / 1e9, 2) AS total_net_B, SUM(CASE WHEN NOT description_en IS NULL AND LENGTH(TRIM(description_en)) > 20 THEN 1 ELSE 0 END) AS desc_en_n /* description_en coverage */, ROUND(100.0 * SUM(CASE WHEN NOT description_en IS NULL AND LENGTH(TRIM(description_en)) > 20 THEN 1 ELSE 0 END) / COUNT(*), 1) AS desc_en_pct, ROUND(100.0 * SUM(CASE WHEN NOT description_en IS NULL AND LENGTH(TRIM(description_en)) > 20 THEN val ELSE 0 END) / SUM(val), 1) AS desc_en_val_pct, SUM(CASE WHEN NOT prog_purpose_en IS NULL AND LENGTH(TRIM(prog_purpose_en)) > 20 THEN 1 ELSE 0 END) AS prog_en_n /* prog_purpose_en coverage */, ROUND(100.0 * SUM(CASE WHEN NOT prog_purpose_en IS NULL AND LENGTH(TRIM(prog_purpose_en)) > 20 THEN 1 ELSE 0 END) / COUNT(*), 1) AS prog_en_pct, ROUND(100.0 * SUM(CASE WHEN NOT prog_purpose_en IS NULL AND LENGTH(TRIM(prog_purpose_en)) > 20 THEN val ELSE 0 END) / SUM(val), 1) AS prog_en_val_pct, SUM(CASE WHEN (NOT description_en IS NULL AND LENGTH(TRIM(description_en)) > 20) OR (NOT prog_purpose_en IS NULL AND LENGTH(TRIM(prog_purpose_en)) > 20) THEN 1 ELSE 0 END) AS any_en_n /* union coverage (either EN field) */, ROUND(100.0 * SUM(CASE WHEN (NOT description_en IS NULL AND LENGTH(TRIM(description_en)) > 20) OR (NOT prog_purpose_en IS NULL AND LENGTH(TRIM(prog_purpose_en)) > 20) THEN 1 ELSE 0 END) / COUNT(*), 1) AS any_en_pct, ROUND(100.0 * SUM(CASE WHEN (NOT description_en IS NULL AND LENGTH(TRIM(description_en)) > 20) OR (NOT prog_purpose_en IS NULL AND LENGTH(TRIM(prog_purpose_en)) > 20) THEN val ELSE 0 END) / SUM(val), 1) AS any_en_val_pct, ROUND(MEDIAN(CASE WHEN NOT description_en IS NULL THEN LENGTH(description_en) ELSE 0 END), 0) AS med_desc_chars /* text length distribution for description_en (quality signal) */, ROUND(MEDIAN(CASE WHEN NOT prog_purpose_en IS NULL THEN LENGTH(prog_purpose_en) ELSE 0 END), 0) AS med_prog_chars, COUNT(DISTINCT CASE WHEN NOT prog_purpose_en IS NULL AND LENGTH(TRIM(prog_purpose_en)) > 20 THEN prog_purpose_en END) AS prog_en_distinct_texts /* prog_purpose_en cardinality (repeat text = program-level, not grant-level) */ FROM base LIMIT 2000
ev_f69ba5497862| pop_frame | n_grants | pct_of_count | total_M | pct_of_dollars | avg_grant_K | median_grant_K | p75_grant_K |
|---|---|---|---|---|---|---|---|
| NO_POPULATION_SIGNAL | 981269 | 86.5 | 538730.4 | 69.4 | 549.0 | 25.0 | 106.9 |
| DESERVING_ONLY | 66000 | 5.8 | 37408.2 | 4.8 | 566.8 | 26.6 | 150.0 |
| INDIGENOUS_ONLY | 62474 | 5.5 | 53380.8 | 6.9 | 854.4 | 6.0 | 122.0 |
| NEWCOMERS_ONLY | 13055 | 1.2 | 50896.5 | 6.6 | 3898.6 | 1388.3 | 4098.2 |
| UNDESERVING_ONLY | 5586 | 0.5 | 37458.8 | 4.8 | 6705.8 | 545.2 | 3897.5 |
| MULTI_CATEGORY | 4782 | 0.4 | 51705.4 | 6.7 | 10812.5 | 199.0 | 547.5 |
| MENTAL_HEALTH_ONLY | 658 | 0.1 | 2156.2 | 0.3 | 3276.9 | 376.9 | 1458.8 |
| MIXED_DESERVING_UNDESERVING | 571 | 0.1 | 4409.3 | 0.6 | 7722.1 | 971.6 | 4000.0 |
WITH latest AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants WHERE LOWER(COALESCE(recipient_legal_name, '')) NOT LIKE '%batch report%' AND LOWER(COALESCE(recipient_legal_name, '')) NOT LIKE '%rapport en lots%'), base AS (SELECT ref_number, TRY_CAST(agreement_value AS DOUBLE) AS val, LOWER(COALESCE(description_en, '') || ' ' || COALESCE(prog_purpose_en, '')) AS txt FROM latest WHERE rn = 1), cats AS (SELECT ref_number, val, (CASE WHEN txt LIKE '%disab%' THEN 1 ELSE 0 END + CASE WHEN txt LIKE '%illness%' OR txt LIKE '%disease%' OR txt LIKE '%cancer%' THEN 1 ELSE 0 END + CASE WHEN txt LIKE '%veteran%' OR txt LIKE '%armed forces%' THEN 1 ELSE 0 END + CASE WHEN txt LIKE '%senior%' OR txt LIKE '% elder %' OR txt LIKE '%older adult%' THEN 1 ELSE 0 END) AS n_deserving, (CASE WHEN txt LIKE '%homeless%' OR txt LIKE '%sans-abri%' THEN 1 ELSE 0 END + CASE WHEN txt LIKE '%poverty%' OR txt LIKE '% low-income%' OR txt LIKE '% low income%' THEN 1 ELSE 0 END + CASE WHEN txt LIKE '%addiction%' OR txt LIKE '%substance use%' OR txt LIKE '%substance abuse%' OR txt LIKE '%opioid%' OR txt LIKE '%harm reduction%' THEN 1 ELSE 0 END + CASE WHEN txt LIKE '%offend%' OR txt LIKE '%incarcerat%' OR txt LIKE '%criminal record%' THEN 1 ELSE 0 END) AS n_undeserving, CASE WHEN txt LIKE '%mental health%' THEN 1 ELSE 0 END AS mental_health, CASE WHEN txt LIKE '%indigenous%' OR txt LIKE '%first nation%' OR txt LIKE '%aboriginal%' OR txt LIKE '%autochtone%' THEN 1 ELSE 0 END AS indigenous, CASE WHEN txt LIKE '%newcomer%' OR txt LIKE '%refugee%' OR txt LIKE '%immigrant%' THEN 1 ELSE 0 END AS newcomers FROM base), framed AS (SELECT ref_number, val, CASE WHEN n_deserving > 0 AND n_undeserving = 0 AND mental_health = 0 AND indigenous = 0 THEN 'DESERVING_ONLY' WHEN n_undeserving > 0 AND n_deserving = 0 AND mental_health = 0 AND indigenous = 0 THEN 'UNDESERVING_ONLY' WHEN n_deserving > 0 AND n_undeserving > 0 THEN 'MIXED_DESERVING_UNDESERVING' WHEN mental_health = 1 AND n_deserving = 0 AND n_undeserving = 0 AND indigenous = 0 THEN 'MENTAL_HEALTH_ONLY' WHEN indigenous = 1 AND n_deserving = 0 AND n_undeserving = 0 AND mental_health = 0 THEN 'INDIGENOUS_ONLY' WHEN newcomers = 1 AND n_deserving = 0 AND n_undeserving = 0 AND mental_health = 0 AND indigenous = 0 THEN 'NEWCOMERS_ONLY' WHEN (mental_health = 1 OR indigenous = 1 OR newcomers = 1) AND (n_deserving > 0 OR n_undeserving > 0) THEN 'MULTI_CATEGORY' ELSE 'NO_POPULATION_SIGNAL' END AS pop_frame FROM cats) SELECT pop_frame, COUNT(*) AS n_grants, ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_count, ROUND(SUM(val) / 1e6, 1) AS total_M, ROUND(100.0 * SUM(val) / SUM(SUM(val)) OVER (), 1) AS pct_of_dollars, ROUND(AVG(val) / 1e3, 1) AS avg_grant_K, ROUND(MEDIAN(val) / 1e3, 1) AS median_grant_K, ROUND(QUANTILE_CONT(val, 0.75 ORDER BY val) / 1e3, 1) AS p75_grant_K FROM framed GROUP BY pop_frame ORDER BY n_grants DESC LIMIT 2000
ev_95cdc9b129db| pop_frame | base_rows | distinct_refs | unique_recips | total_M | median_K | avg_M | med_dur_days |
|---|---|---|---|---|---|---|---|
| DESERVING_ONLY | 11290 | 11290 | 9105 | 4229.9 | 70.0 | 0.37 | 729.0 |
| UNDESERVING_ONLY | 1445 | 1445 | 1084 | 2208.8 | 130.0 | 1.53 | 481.0 |
WITH latest AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants WHERE LOWER(COALESCE(recipient_legal_name, '')) NOT LIKE '%batch report%'), texts AS (SELECT ref_number, owner_org, recipient_legal_name, agreement_value, agreement_start_date, agreement_end_date, LOWER(COALESCE(description_en, '') || ' ' || COALESCE(prog_purpose_en, '') || ' ' || COALESCE(description_fr, '') || ' ' || COALESCE(prog_purpose_fr, '')) AS txt FROM latest WHERE rn = 1 AND NOT owner_org IN ('dfatd-maecd', 'infc')), framed AS (SELECT *, CASE WHEN txt LIKE '%indigenous%' OR txt LIKE '%first nation%' OR txt LIKE '%inuit%' OR txt LIKE '%métis%' OR txt LIKE '%metis%' OR txt LIKE '%aboriginal%' THEN 'INDIGENOUS' WHEN txt LIKE '%newcomer%' OR txt LIKE '%immigrant%' OR txt LIKE '%refugee%' OR txt LIKE '%settlement%' THEN 'NEWCOMERS' WHEN txt LIKE '%mental health%' THEN 'MENTAL_HEALTH' WHEN txt LIKE '%disabilit%' OR txt LIKE '%illness%' OR txt LIKE '%veteran%' OR txt LIKE '%cancer%' OR txt LIKE '%palliative%' THEN 'DESERVING_ONLY' WHEN txt LIKE '%poverty%' OR txt LIKE '%homeless%' OR txt LIKE '%addiction%' OR txt LIKE '%incarcerat%' THEN 'UNDESERVING_ONLY' ELSE 'NO_SIGNAL' END AS pop_frame FROM texts) SELECT pop_frame, COUNT(*) AS base_rows, COUNT(DISTINCT ref_number) AS distinct_refs, COUNT(DISTINCT recipient_legal_name) AS unique_recips, ROUND(SUM(TRY_CAST(agreement_value AS DOUBLE)) / 1e6, 1) AS total_M, ROUND(MEDIAN(TRY_CAST(agreement_value AS DOUBLE)) / 1e3, 1) AS median_K, ROUND(AVG(TRY_CAST(agreement_value AS DOUBLE)) / 1e6, 2) AS avg_M, ROUND(MEDIAN(DATE_DIFF('DAY', TRY_CAST(agreement_start_date AS DATE), TRY_CAST(agreement_end_date AS DATE))), 0) AS med_dur_days FROM framed WHERE pop_frame IN ('DESERVING_ONLY', 'UNDESERVING_ONLY') GROUP BY pop_frame ORDER BY pop_frame LIMIT 2000ev_9cc5d3a601d2| pop_frame | base_rows | distinct_refs | total_M | median_K | avg_M |
|---|---|---|---|---|---|
| NO_SIGNAL | 300193 | 300193 | 15090.4 | 7.6 | 0.05 |
| INDIGENOUS | 3239 | 3239 | 4683.6 | 185.2 | 1.45 |
| DESERVING_ONLY | 10176 | 10176 | 2849.0 | 53.8 | 0.28 |
| UNDESERVING_ONLY | 2000 | 2000 | 2015.0 | 96.7 | 1.01 |
| MENTAL_HEALTH | 31 | 31 | 400.3 | 98.3 | 12.91 |
| NEWCOMERS | 25 | 25 | 16.8 | 300.5 | 0.67 |
WITH latest AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants WHERE LOWER(COALESCE(recipient_legal_name, '')) NOT LIKE '%batch report%' AND owner_org = 'esdc-edsc'), texts AS (SELECT ref_number, owner_org, recipient_legal_name, agreement_value, agreement_start_date, agreement_end_date, LOWER(COALESCE(description_en, '') || ' ' || COALESCE(prog_purpose_en, '') || ' ' || COALESCE(description_fr, '') || ' ' || COALESCE(prog_purpose_fr, '')) AS txt FROM latest WHERE rn = 1), framed AS (SELECT *, CASE WHEN txt LIKE '%indigenous%' OR txt LIKE '%first nation%' OR txt LIKE '%inuit%' OR txt LIKE '%métis%' OR txt LIKE '%metis%' OR txt LIKE '%aboriginal%' THEN 'INDIGENOUS' WHEN txt LIKE '%newcomer%' OR txt LIKE '%immigrant%' OR txt LIKE '%refugee%' OR txt LIKE '%settlement%' THEN 'NEWCOMERS' WHEN txt LIKE '%mental health%' THEN 'MENTAL_HEALTH' WHEN txt LIKE '%disabilit%' OR txt LIKE '%illness%' OR txt LIKE '%veteran%' OR txt LIKE '%cancer%' OR txt LIKE '%palliative%' THEN 'DESERVING_ONLY' WHEN txt LIKE '%poverty%' OR txt LIKE '%homeless%' OR txt LIKE '%addiction%' OR txt LIKE '%incarcerat%' THEN 'UNDESERVING_ONLY' ELSE 'NO_SIGNAL' END AS pop_frame FROM texts) SELECT pop_frame, COUNT(*) AS base_rows, COUNT(DISTINCT ref_number) AS distinct_refs, ROUND(SUM(TRY_CAST(agreement_value AS DOUBLE)) / 1e6, 1) AS total_M, ROUND(MEDIAN(TRY_CAST(agreement_value AS DOUBLE)) / 1e3, 1) AS median_K, ROUND(AVG(TRY_CAST(agreement_value AS DOUBLE)) / 1e6, 2) AS avg_M FROM framed GROUP BY pop_frame ORDER BY total_M DESC LIMIT 2000
The engagement began from eight questions. The observations above are what the data showed; this maps each original question to its confidence grade.
| Q | Question (as posed) | Confidence |
|---|---|---|
| Q1 | Does past funding predict future funding? (Incumbency advantage.) Expectation: repeat recipients get more money, longer agreements, more frequent awards over time. | ✓ hardened |
| Q2 | Do older, ESTABLISHED organizations capture disproportionate funding? (e.g. long-standing religious institutions with 'precedent and credibility'.) Consider org age (CRA registration / incorporation d | ✓ hardened |
| Q3 | Does funding follow the 'DESERVINGNESS' of the population served? Groups framed as deserving (visible illness, disability) vs undeserving (poverty framed as personal failure). Would require classifyin | ✓ hardened |
| Q4 | Do granting-FOCUSED departments distribute more equitably than granting-MARGINAL ones? (WAGE/Heritage, where G&C is the core job, vs ESDC, where G&C is <5% of a huge budget.) Per-department recipient | ✓ hardened |
| Q5 | Are NONPROFITS treated worse than VENDORS for delivering the same government mandates? (The grants-vs-procurement double standard: partial funding + heavy oversight vs 100% market value.) Contracts<-> | ✓ hardened |
| Q6 | Is OVERSIGHT BURDEN inversely related to money? (Six reports for $30K vs two for $500K.) Reporting burden is not in the data (sponsor's stated limit) — stay qualitative; only weak proxies: amendment f | ✓ hardened |
| Q7 | Does government hold recipients to STANDARDS IT DOESN'T HOLD ITSELF to? (The internal-spending double standard: the ~20% admin-cost 'rule' for nonprofits vs government's own internal-services share; r | qualified |
| Q8 | WHERE DOES THE MONEY GO, geographically and structurally? Regional differences, riding-level distribution, urban/rural, and which provinces' own granting mirrors or offsets federal patterns. Geography | qualified |