Adyan Ahmad Quazi
  1. Home
  2. Work
  3. 03

Project 03 · CanadaBuys contract award history

Defence Procurement Intelligence

The field that would measure Canadian content is empty on every one of 21,890 rows. Once two defects in the file are fixed, the same $19.9B across 2,756 defence contracts reads 78.17% Canadian by registered address and 24.16% to 41.04% Canadian-controlled, and the 70% target is met or missed depending on which definition you pick.

Data
Every PSPC contract award and amendment published since June 2023, Open Government Licence, Canada
Scale
21,890 rows and 91 columns, 57.7 MB, keyed to 13,296 contracts
In the repository
03-defence-procurement · findings memo
Reproducibility
334 of 337 figures reproduced exactly on the 2026-09-07 fresh pull, including the naive and keyed sums to the cent; 3 declared.
01

The question

The Defence Industrial Strategy targets 70% of defence acquisitions going to Canadian firms and does not publish what "Canadian firm" means. Under each defensible definition, what share does the published award data show, and how far apart do the readings sit?

02

The data and its scale

21,890 rows and 91 columns, one row per contract amendment, with a SHA-256 manifest. 13,296 contracts, 4,314 of them amended at least once. The publisher restructured the file in spring 2026 and added three columns the plan predated; I found them by reading the live header against the data dictionary.

03

What the source did

  1. A15Percentage of goods by country of origin, the publisher’s own Canadian-content field, is null on 21,890 of 21,890 rows. It stays as a row in the sensitivity table, because the emptiness is the finding.
  2. A21The total contract value is repeated unchanged on every amendment row, so summing the column double-counts every amended contract: $132,853,897,948.13 against a keyed $66,363,740,301.42, a 100.2% overstatement. Fixed by a keyed dedup, one row per reference number at its latest amendment, not by DISTINCT, where nothing collapses, or MAX, which is right only by accident of the denormalisation.
  3. A9Supplier country is written CA on 18,315 rows and Canada on 1,488, a coding migration visible by award year. The literal filter gives 15.36% Canadian; resolving all 49 raw values through a published crosswalk gives 89.13%. One N/A row is resolved by rule, recovering $654.9M.
  4. ·The amendment-amount column has no single meaning: on 2,168 testable contracts, 39.9% fit neither an increment nor a restatement reading and carry 69.9% of the value, and 2,686 contracts have no row typed Original. It is never aggregated anywhere in this project.
  5. A244,239 of the 13,296 contracts are standing offers or supply arrangements, which carry 3,360 of the 3,665 zero-value contracts.
  6. ·1,762 rows have no UNSPSC code, no instrument type and no amendment type, and none carries a CanadaBuys number: records carried over from the previous procurement system, and every undated row is one of them.
  7. A23One contract has no supplier name. Its null key had dropped it from every inner join, which I caught by reconciling the Power BI export against its source.

Twenty-five assertion rules with expected-against-found counts, four of them blocking, all matching.

04

Method

Three readings of "Canadian firm": registered address; Canadian-controlled, from a 28-supplier ownership crosswalk researched in value order until it covered 80.06% of Canadian-address defence value, each row sourced, dated and graded; and Canadian content, which cannot be computed.

Two scopes: all PSPC contracts, and the defence end-user subset of 2,756 contracts worth $19,878,465,949.91, built by substring because one end-user cell can name 25 bodies and an exact match catches 374 rows against 4,229. Two measures: value and count.

The control reading is a range, the 1,059 unresearched suppliers counted as foreign at the lower bound and Canadian at the upper, with no extrapolation, and it is read only where research covers more than 50% of the value.

05

Finding

The 70% target, under each reading of Canadian firm

The 70% target, under each reading of Canadian firmDot and range chart with four rows. Registered-address share as a dot, Canadian-controlled share as a range, 0 to 100 percent. All PSPC by count: 89.31, range 0.50 to 88.22, 1.78% researched. All PSPC by value: 89.13, range 8.43 to 77.87, 22.08% researched. Defence subset by count: 86.39, range 1.56 to 82.44, 6.38% researched. These three are greyed because too little ownership research covers them. Defence subset by value, the one readable row: 78.17 by address and 24.16 to 41.04 Canadian-controlled on 78.41% coverage. A dashed line marks the 70% target. Without standing offers the readable row moves to 76.42 and 24.31 to 37.79. Reading C, Canadian content, is not plotted because the field is empty on every row.020406080100Share awarded to Canadian suppliers, %All PSPC, count1.78% researchedtoo little to readAll PSPC, count: address 89.31%, controlled 0.50% to 88.22%, 1.78% of value researchedAll PSPC, value22.08% researchedtoo little to readAll PSPC, value: address 89.13%, controlled 8.43% to 77.87%, 22.08% of value researchedDefence subset, count6.38% researchedtoo little to readDefence subset, count: address 86.39%, controlled 1.56% to 82.44%, 6.38% of value researchedDefence subset, value78.41% researchedreadableDefence subset, value: address 78.17%, controlled 24.16% to 41.04%, 78.41% of value researched24.1641.0478.1770% targetwithout standing offers: 76.42, and 24.31 to 37.79registered addressCanadian-controlled, range
The 70% target, under each reading of Canadian firmDot and range chart with four rows. Registered-address share as a dot, Canadian-controlled share as a range, 0 to 100 percent. All PSPC by count: 89.31, range 0.50 to 88.22, 1.78% researched. All PSPC by value: 89.13, range 8.43 to 77.87, 22.08% researched. Defence subset by count: 86.39, range 1.56 to 82.44, 6.38% researched. These three are greyed because too little ownership research covers them. Defence subset by value, the one readable row: 78.17 by address and 24.16 to 41.04 Canadian-controlled on 78.41% coverage. A dashed line marks the 70% target. Without standing offers the readable row moves to 76.42 and 24.31 to 37.79. Reading C, Canadian content, is not plotted because the field is empty on every row.020406080100Share awarded to Canadian suppliers, %All PSPC, count · 1.78% researchedAll PSPC, count: address 89.31%, controlled 0.50% to 88.22%, 1.78% of value researchedAll PSPC, value · 22.08% researchedAll PSPC, value: address 89.13%, controlled 8.43% to 77.87%, 22.08% of value researchedDefence subset, count · 6.38% researchedDefence subset, count: address 86.39%, controlled 1.56% to 82.44%, 6.38% of value researchedDefence subset, value · 78.41% researchedDefence subset, value: address 78.17%, controlled 24.16% to 41.04%, 78.41% of value researched24.1641.0478.1770% targetwithout offers: 76.42; 24.31 to 37.79registered addressCanadian-controlled, range
Dot: registered address. Bar: Canadian-controlled, from the unresearched tail counted as foreign to counted as Canadian. Grey rows have too little ownership research to read. Canadian content is not plotted: the field is empty on every row.03 · CanadaBuys award history
Data behind this chart
Sensitivity table, 03
Scope and measureAddressControlledResearched
All PSPC, count89.31%0.50% to 88.22%1.78%
All PSPC, value89.13%8.43% to 77.87%22.08%
Defence subset, count86.39%1.56% to 82.44%6.38%
Defence subset, value78.17%24.16% to 41.04%78.41%
Defence value, without offers76.42%24.31% to 37.79%82.36%
Canadian content, any scopefield empty on 21,890 of 21,890 rows
  1. Three of the four rows cannot be read.Ownership research covers 1.78% of all PSPC contracts, 22.08% of all PSPC value and 6.38% of defence contracts, too little for the control range to mean anything. They are drawn, greyed, rather than dropped.
  2. The one readable row.Defence value, on 78.41% coverage: 78.17% goes to Canadian addresses and 24.16% to 41.04% to Canadian-controlled suppliers.
  3. The target sits between the two readings.Eight points below the address reading and 29 points above the top of the control range, on the same $19.9B.
  4. Offers in or out.Excluding standing offers and supply arrangements moves the address reading to 76.42% and narrows the control range to 24.31% to 37.79%.

Suppliers, at two grains. At the legal entity that signed: Bell Textron Canada, $2,283.0M on one contract; Allied Wings, $1,772.4M; Lockheed Martin Canada, $1,311.9M; Cascade Aerospace, $1,245.2M; Victoria Shipyards, $786.1M. At the ultimate parent, General Dynamics, awarded under four legal names at ranks 10 and 12, becomes the fifth-largest supplier at $837.6M across 57 contracts. The consolidation is a lower bound, since the parent grain can only merge what was researched.

Spend by capability. A keyword search fails and is kept as the evidence: "carbon fib" finds 0 rows, "composite" 20, "aerospace" or "aircraft" 252, and the uncrewed vocabulary 75. Segments therefore come from UNSPSC through a 350-prefix crosswalk. 45.1% of defence value, $8,968.5M on 260 contracts averaging $34.5M, carries no code at all, and all 260 are legacy-system records.

Of the $10,909.9M that is classified: aerospace 39.49%, of which one Bell Textron contract is 53.0%; specialized manufacturing 16.74%; uncrewed and autonomous 0.02%, $2.1M across 20 contracts; other 43.75%, including $1,208.2M of aviation fuel deliberately not counted as aerospace. Aerospace concentration, HHI 3,864 with a top-three share of 96.6%, is the same at both grains; specialized manufacturing moves from 2,138 to 2,446 and from 71.3% to 79.9% when General Dynamics is consolidated, the only gap between the grains.

06

What this cannot tell you

  • Whether any supplier manufactures in Canada. Address is not capability.
  • What "defence acquisitions" means in the target’s denominator. This is a PSPC file, so departments that contract directly, DND included, are absent, and the defence subset overlaps other departments.
  • Which definition the government will use, since the Strategy does not publish one.
  • Whether awarded value was spent, or what the amendments were worth.
  • How much value predates June 2023, since 2,686 contracts have no original row, or anything about a trend to 2036 from three years of dates.
  • Who controls the tail. The crosswalk is 28 suppliers at one point in time, five of them graded medium confidence, Allied Wings among them.
07

Artifacts

  • A 77-cell notebook and a findings memo
  • A decision log, D-01 to D-20, and a pitfalls record of sixteen traps, ten of them silent
  • A data contract and a source-to-target map for 25 mapped and 8 derived columns
  • Five published crosswalks, of 49, 1, 28, 303 and 350 rows, each row with its rule, source, check date and confidence
  • A seven-table star schema exported and reconciled row for row, with the DAX measure layer and a department-viewer row-level-security role specified; the Power BI dashboard itself is not yet built
The notebook’s definition-sensitivity chart: four rows of scope and measure, three greyed for too little ownership research, and the defence value row showing 78.2% by address and a 24.2 to 41.0 control range either side of a dashed 70% target.
The notebook’s own sensitivity chart. definition_sensitivity.png · original in the repository
Two bar charts of the top Canadian-address suppliers to DND by keyed contract value: on the left the legal entity that signed, led by Bell Textron Canada at $2,283M; on the right the ultimate parent, where General Dynamics consolidates four name keys and 57 contracts to $837.6M. Bars are coloured by control: Canadian, foreign subsidiary, joint venture, unresolved.
Top suppliers as awarded and as owned. top_suppliers_by_control.png · original in the repository
08

Reproducibility

The CI re-runs this notebook on a fresh pull of the award file. On 2026-09-07 the file came back with the same SHA-256 as the file the memo was written from, and 334 of the 337 figures came back exactly, including both sums to the cent, the sensitivity table to two decimals and all 25 assertion counts. The three declared figures are the notebook’s cell count, the source’s byte size and the six hand pulls the memo recorded.

CI status for this project
Figures in the manifest337
Reproduced exactly, 2026-09-07334
Declared3
Notebook run time on the committed baseline7.8 s
09

Sources

  1. CanadaBuys contract history, Public Services and Procurement Canada, Open Government Licence, Canada, with a SHA-256 manifest.
  2. The ownership crosswalk: each of its 28 rows carries its own source and check date.
  3. The findings memo, decision log, pitfalls record, data contract and source-to-target map in 03-defence-procurement/memo.