Skip to content

Fatal NTSB investigations, Canada

180,604 investigations by the National Transportation Safety Board since 1962: 174,583 accidents, the rest incidents and other occurrences. Each has NTSB’s narrative and, once the investigation is done, its probable cause; since 2008, its findings and sequence of events.

Defining event

Finding

Phase of flight

Aircraft model

Cases per year

19662026

Latest cases

Query with SQL

SELECT c."ntsbNumber", c."eventDate", c."eventType", c."eventCity", c."eventStateOrRegion", a.n_number, a."aircraftMake", a."aircraftModel", c."highestInjuryLevel", c."totalFatal", c."completionStatus", c.report_url FROM 'https://openairdocs.org/data/latest/ntsb_case_detail.parquet' c LEFT JOIN 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' a ON a.case_mkey = c."mKey" AND a.position = 1 WHERE c."eventStateOrRegion" = 'CD' AND c."eventCountry" = 'USA' AND c."highestInjuryLevel" = 'Fatal' ORDER BY c."eventDate" DESC, c."ntsbNumber" DESC

duckdb -csv -c "SELECT c.\"ntsbNumber\", c.\"eventDate\", c.\"eventType\", c.\"eventCity\", c.\"eventStateOrRegion\", a.n_number, a.\"aircraftMake\", a.\"aircraftModel\", c.\"highestInjuryLevel\", c.\"totalFatal\", c.\"completionStatus\", c.report_url
  FROM 'https://openairdocs.org/data/latest/ntsb_case_detail.parquet' c LEFT JOIN 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' a ON a.case_mkey = c.\"mKey\" AND a.position = 1
  WHERE c.\"eventStateOrRegion\" = 'CD' AND c.\"eventCountry\" = 'USA'
    AND c.\"highestInjuryLevel\" = 'Fatal'
  ORDER BY c.\"eventDate\" DESC, c.\"ntsbNumber\" DESC"

Prints the whole list as CSV. It reads the release’s Parquet files over HTTPS, so there’s nothing to download first. Install DuckDB to run it.

SELECT c."ntsbNumber", c."eventDate", c."eventType", c."eventCity", c."eventStateOrRegion", a.n_number, a."aircraftMake", a."aircraftModel", c."highestInjuryLevel", c."totalFatal", c."completionStatus", c.report_url
FROM 'https://openairdocs.org/data/latest/ntsb_case_detail.parquet' c LEFT JOIN 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' a ON a.case_mkey = c."mKey" AND a.position = 1
WHERE c."eventStateOrRegion" = 'CD' AND c."eventCountry" = 'USA'
  AND c."highestInjuryLevel" = 'Fatal'
ORDER BY c."eventDate" DESC, c."ntsbNumber" DESC

Run it at the DuckDB prompt, or in any tool that reads Parquet over HTTPS. More ways to query the release are on Use the data.

NTSB LAX76O0020Accident · 2 fatalClosedPiper PA-28R · Victoria, CanadaAug 13, 1976 · N4849T · Destroyed NTSB UNK73X0122Accident · 2 fatalClosedMooney M20C · Emo, CanadaSep 3, 1973 · N9749M · Destroyed NTSB LAX73OL014Accident · 3 fatalClosedLockheed 1049G · Casey, CanadaJun 9, 1973 · N173W · Destroyed NTSB UNK73X0109Accident · 1 fatalClosedCessna 340 · Gander, CanadaMar 31, 1973 · N7691Q · Destroyed NTSB CAN73F0906Accident · 3 fatalClosedPiper PA-23 · Aklavik, CanadaDec 8, 1972 · N4919P · Destroyed NTSB UNK73X0101Accident · 2 fatalClosedBeech 35P · Vancouver, CanadaOct 20, 1972 · N9656Y · Destroyed NTSB UNK73X0100Accident · 1 fatalClosedCessna 172L · Lamacaza, CanadaOct 16, 1972 · N4257L · Destroyed NTSB UNK72X0111Accident · 2 fatalClosedPiper PA-28 · Kemptvlle, CanadaJul 20, 1972 · N2087T · Destroyed NTSB UNK71X0108Accident · 5 fatalClosedPiper PA-24 · Calgary, CanadaJun 5, 1971 · N5462P · Destroyed NTSB UNK71X0106Accident · 2 fatalClosedBeech M35 · Oshawa, CanadaFeb 17, 1971 · N9812R · Destroyed NTSB SEA71OS003Accident · 4 fatalClosedCessna 172C · Mission, CanadaJan 21, 1971 · N8482X · Substantial damage NTSB CAN71AP907Accident · 4 fatalClosedCessna T337B · Cranbrook, CanadaSep 22, 1970 · N2346S · Destroyed NTSB CAN714A905Accident · 1 fatalClosedGrumman TBM · St.Martin, CanadaJun 21, 1970 · N7031C · Destroyed NTSB CAN71A0902Accident · 2 fatalClosedPiper PA-18 · Ontario, CanadaMay 29, 1970 · N2321A · Substantial damage NTSB UNK70X0105Accident · 2 fatalClosedNpiper PA-22 · Brockville, CanadaNov 30, 1969 · N3934P · Destroyed NTSB UNK69X0140Accident · 1 fatalClosedMini Mustng 01 · Abbotsford, CanadaAug 8, 1969 · N9N · Destroyed NTSB CAN70A4457Accident · 3 fatalClosedSchweizer SGS126 · Hope, CanadaJul 26, 1969 · CF-VEQ · 2 aircraft · Destroyed NTSB UNK69X0131Accident · 2 fatalClosedCessna 150F · Radium, CanadaJul 9, 1969 · N8621S · Substantial damage NTSB UNK69X0128Accident · 3 fatalClosedBeech 35 · Westwold, CanadaJun 28, 1969 · N2800V · Destroyed NTSB CAN69AF526Accident · 2 fatalClosedPiper PA-22 · Bowmanville, CanadaMay 18, 1969 · N7050D · Substantial damage NTSB UNK68X0148Accident · 1 fatalClosedN.American AT-6G · Brantford, CanadaJun 8, 1968 · N400G · Destroyed NTSB UNK68X0100Accident · 5 fatalClosedBeech C-45G · Saskatchewan, CanadaOct 5, 1967 · N505N · Destroyed NTSB UNK67X0151Accident · 2 fatalClosedMooney M20D · Oshawa, CanadaAug 27, 1967 · N6828U · Destroyed NTSB UNK67X0132Accident · 2 fatalClosedCessna 175 · Armstrong, CanadaJun 29, 1967 · N7022M · Destroyed NTSB UNK67X0126Accident · 3 fatalClosedBell 47J · Ontario, CanadaJun 8, 1967 · N2833B · Destroyed NTSB UNK66X0172Accident · 2 fatalClosedAeronca 15AC · Z, CanadaSep 10, 1966 · N1467H · Destroyed NTSB UNK66X0171Accident · 4 fatalClosedCessna 320D · Embras, CanadaSep 7, 1966 · N4146T · Destroyed NTSB UNK66X0145Accident · 2 fatalClosedCessna 337 · Z, CanadaJun 4, 1966 · N2166X · Destroyed NTSB CAN66F0420Accident · 1 fatalClosedCessna 150F · Aishihik, CanadaApr 28, 1966 · N6934F · Substantial damage