🚗 `transport`¶

U.S. transportation data spanning motor-vehicle safety (NHTSA recalls, consumer complaints, FARS fatal crashes), aviation (BTS airline on-time performance, FAA airport master, BTS T-100 air-carrier traffic), public transit (FTA National Transit Database monthly ridership), and highways (FHWA state motor-vehicle registrations). Each row is a discrete event or a place-and-period observation joinable to geo (states/counties), econ (fuel prices, employment), sec (airline / automaker filings), and weather. Together they describe how people and goods move, how safely, and at what capacity.

32 datasets · 473 columns

vehicle_recalls · table¶

NHTSA vehicle safety recall campaigns — one row per recall (NHTSA campaign id), with component, estimated potentially-affected unit count, recall type (Vehicle/Equipment/Tire/Child Seat), and park-outside / do-not-drive advisory flags. All-history to current (~30K campaigns), served as a single Socrata JSON export, so this is a snapshot table: the whole set is re-fetched and the partition overwritten each run. The affected make / model / year set is rolled into the description text (not exploded). Source: data.transportation.gov/resource/6axg-epim.json.

Column	Type	Null	Description
`nhtsa_id`	string	no	NHTSA recall campaign id (e.g. 26V434000) — PK
`report_received_date`	date	yes	Date NHTSA received the recall report
`manufacturer`	string	yes	Recalling manufacturer
`subject`	string	yes	Recall subject line
`component`	string	yes	Affected component category
`mfr_campaign_number`	string	yes	Manufacturer's own campaign number
`recall_type`	string	yes	Vehicle / Equipment / Tire / Child Seat
`potentially_affected`	long	yes	Estimated number of units potentially affected
`recall_description`	string	yes	Description of the defect (SODA defect_summary)
`consequence_summary`	string	yes	Consequence of the defect
`corrective_action`	string	yes	Manufacturer's remedy
`park_outside_advisory`	string	yes	Fire-risk-when-parked advisory (Yes/No)
`do_not_drive_advisory`	string	yes	Do-not-drive advisory (Yes/No)
`recall_url`	string	yes	NHTSA recall detail page URL (from recall_link.url)

safety_complaints · table¶

NHTSA consumer safety-defect complaints — one row per complaint component record since 1995 (all-history bulk flat file, refreshed daily), carrying crash/fire flags, injury and death counts, mileage at failure, and free-text complaint description, keyed to manufacturer/make/model/ model_year with both the consumer's state (state_abbr) and the incident state (state_of_incident). Snapshot: the whole file is re-fetched and the partition overwritten each run. Source: static.nhtsa.gov/odi/ffdd/cmpl/FLAT_CMPL.zip.

Column	Type	Null	Description
`cmplid`	string	no	NHTSA internal unique sequence number — PK
`odino`	string	yes	NHTSA internal reference number (may repeat across components)
`manufacturer`	string	yes	Manufacturer name
`make`	string	yes	Vehicle/equipment make
`model`	string	yes	Vehicle/equipment model
`model_year`	string	yes	Model year (9999 = unknown/NA)
`crash`	string	yes	Was vehicle involved in a crash (Y/N)
`fail_date`	date	yes	Date of incident
`fire`	string	yes	Was vehicle involved in a fire (Y/N)
`injured`	integer	yes	Number of persons injured
`deaths`	integer	yes	Number of fatalities
`component_desc`	string	yes	Specific component description
`city`	string	yes	Consumer's city
`state_abbr`	string	yes	Consumer's state code (USPS abbr; FK component to geo.states)
`vin`	string	yes	Partial VIN
`date_added`	date	yes	Date added to file
`date_complaint_received`	date	yes	Date complaint received by NHTSA
`miles`	integer	yes	Vehicle mileage at failure
`complaint_description`	string	yes	Free-text description of the complaint
`complaint_type`	string	yes	Source-of-complaint code (IVOQ, EWR, VOQ, ...)
`product_type`	string	yes	Product type (V=vehicle, T=tires, E=equipment, C=child restraint)
`state_of_incident`	string	yes	Incident state code (USPS abbr)

fatal_crashes · table¶

NHTSA Fatality Analysis Reporting System — one row per fatal traffic crash (accident.csv, grain ST_CASE) for the given data year, with fatality, person, vehicle, and pedestrian counts, crash date/hour/day-of-week, lat/lon, and decoded conditions (weather, light, manner of collision, roadway function). Delta by year: past years immutable, newest complete year (effective_year = year - 2) re-fetched. Joins to geo.counties via county_fips; see fatal_crashes_by_state_year for the state x year rollup. Source: static.nhtsa.gov/nhtsa/downloads/FARS/{year}/National/FARS{year}NationalCSV.zip.

Column	Type	Null	Description
`st_case`	integer	no	FARS crash case number (unique within data year) — PK
`state_fips`	string	yes	2-digit state FIPS code (FK to geo.states)
`state_name`	string	yes	State name
`county_fips`	string	yes	5-digit county FIPS code (FK to geo.counties)
`county_name`	string	yes	County name
`city_name`	string	yes	City name
`crash_date`	date	yes	Crash date (from YEAR/MONTH/DAY)
`day_of_week`	string	yes	Day of week
`hour`	integer	yes	Hour of crash (0-23; 99=unknown mapped to null)
`latitude`	double	yes	Crash latitude (77.7777+ sentinel mapped to null)
`longitude`	double	yes	Crash longitude (-999.x sentinel mapped to null)
`harmful_event`	string	yes	First harmful event (decoded)
`manner_of_collision`	string	yes	Manner of collision (decoded)
`light_condition`	string	yes	Light condition (decoded)
`weather`	string	yes	Atmospheric conditions (decoded)
`roadway_function`	string	yes	Roadway functional classification (decoded)
`persons_count`	integer	yes	Number of persons in the crash
`vehicles_count`	integer	yes	Number of vehicles (contact + non-contact)
`fatalities`	integer	yes	Number of fatalities in the crash
`pedestrians`	integer	yes	Number of pedestrians/non-motorists involved

airline_ontime · table¶

BTS Reporting Carrier On-Time Performance — one row per scheduled domestic flight for the (year, month) with departure/arrival delays, cancellations, diversions, and delay-cause minutes. A curated subset of the 110-column source is projected. Delta by (year, month); dataLag 1 keeps to complete prior years so no unpublished month is requested. In DQ mode dqRowLimit caps each month. Source: transtats.bts.gov/PREZIP/On_Time_Reporting_Carrier_On_Time_Performance_1987_present_{year}_{month}.zip.

Column	Type	Null	Description
`flight_date`	date	yes	Flight date
`reporting_airline`	string	yes	Reporting carrier IATA-style code
`dot_id_reporting_airline`	integer	yes	DOT-assigned carrier id
`tail_number`	string	yes	Aircraft tail number
`flight_number`	string	yes	Flight number
`origin`	string	yes	Origin airport code (FK to airports.arpt_id)
`origin_city_name`	string	yes	Origin city name
`origin_state`	string	yes	Origin state (USPS abbr)
`dest`	string	yes	Destination airport code (FK to airports.arpt_id)
`dest_city_name`	string	yes	Destination city name
`dest_state`	string	yes	Destination state (USPS abbr)
`crs_dep_time`	string	yes	Scheduled departure time (local hhmm)
`dep_delay`	double	yes	Departure delay in minutes (negative = early)
`dep_del15`	double	yes	Departure delayed >= 15 min (1/0)
`crs_arr_time`	string	yes	Scheduled arrival time (local hhmm)
`arr_delay`	double	yes	Arrival delay in minutes (negative = early)
`arr_del15`	double	yes	Arrival delayed >= 15 min (1/0)
`cancelled`	double	yes	Flight cancelled (1/0)
`cancellation_code`	string	yes	Cancellation reason (A=carrier, B=weather, C=NAS, D=security)
`diverted`	double	yes	Flight diverted (1/0)
`air_time`	double	yes	Flight time in the air (minutes)
`distance`	double	yes	Distance between airports (miles)
`carrier_delay`	double	yes	Delay minutes attributed to the carrier
`weather_delay`	double	yes	Delay minutes attributed to weather
`nas_delay`	double	yes	Delay minutes attributed to the National Airspace System
`security_delay`	double	yes	Delay minutes attributed to security
`late_aircraft_delay`	double	yes	Delay minutes attributed to a late inbound aircraft

airports · table¶

FAA airport / aviation-facility master (NTAD, NASR-derived) — one row per facility (~19,445), keyed by FAA location id. The ArcGIS FeatureServer paginates 2000 features/page, so a self-paginating ResponseTransformer fetches all pages and flattens features[].attributes. Snapshot, refreshed on the FAA 56-day NASR cycle. arpt_id joins airline_ontime.origin/dest. Source: services.arcgis.com/.../NTAD_Aviation_Facilities/FeatureServer/0.

Column	Type	Null	Description
`arpt_id`	string	no	FAA location identifier (e.g. ATL) — PK
`arpt_name`	string	yes	Airport/facility name
`city`	string	yes	Associated city
`state_code`	string	yes	State (USPS abbr; FK component to geo.states)
`state_name`	string	yes	State name
`county_name`	string	yes	County name
`latitude`	double	yes	Latitude (decimal degrees, WGS84)
`longitude`	double	yes	Longitude (decimal degrees, WGS84)
`elevation_ft`	double	yes	Field elevation (feet)
`site_type_code`	string	yes	Facility type (A=airport, H=heliport, S=seaplane base, ...)
`facility_use_code`	string	yes	PU=public, PR=private
`ownership_type_code`	string	yes	Ownership (PU=public, PR=private, MA/MN/MR=military)
`arpt_status`	string	yes	Operational status (O=operational)
`region_code`	string	yes	FAA region code
`eff_date`	string	yes	NASR effective date (56-day cycle) — freshness marker

transit_ridership · table¶

FTA National Transit Database — Complete Monthly Ridership (with adjustments and estimates). Long format: one row per transit agency × mode × type-of-service × month with unlinked passenger trips (upt), vehicles operated in max service (voms), and vehicle revenue hours/miles (vrh/vrm). Delta by (year, month) sliced from the Socrata date field. Source: data.transportation.gov/resource/8bui-9xvu.json.

Column	Type	Null	Description
`ntd_id`	string	yes	NTD agency id
`agency`	string	yes	Transit agency name
`uza_name`	string	yes	Urbanized area name
`mode`	string	yes	NTD mode code (e.g. DR, MB, HR, LR)
`mode_name`	string	yes	Grouped mode label (e.g. Bus, Heavy Rail)
`tos`	string	yes	Type of service (DO=directly operated, PT=purchased transportation)
`reporter_type`	string	yes	Reporter type (Full Reporter, ...)
`state_abbr`	string	yes	State (USPS abbr; FK component to geo.states)
`fta_region`	string	yes	FTA region number
`ride_date`	date	yes	Observation month (first of month)
`upt`	double	yes	Unlinked passenger trips
`voms`	double	yes	Vehicles operated in maximum service
`vrh`	double	yes	Vehicle revenue hours
`vrm`	double	yes	Vehicle revenue miles
`agency_mode_tos_date`	string	no	Composite natural key (agency_mode_tos_reporter_uace_date) — PK

t100_segments · table¶

BTS T-100 Domestic Segment (All Carriers) — monthly air-carrier traffic and capacity per (carrier, origin, dest, aircraft type): passengers, freight, mail, seats, and departures performed/scheduled. Complements airline_ontime (which has punctuality but no volumes). One fetch per year returns all months. T-100 has no stable download URL, so a DataProvider reproduces the TranStats ASP.NET postback (gnoyr_VQ=GEE). Delta by year; dataLag 1 keeps to complete prior years. Source: transtats.bts.gov DL_SelectFields.aspx.

Column	Type	Null	Description
`month`	integer	yes	Calendar month (1-12)
`carrier`	string	yes	Unique carrier code
`carrier_name`	string	yes	Unique carrier name
`origin`	string	yes	Origin airport code (FK to airports.arpt_id)
`origin_city_name`	string	yes	Origin city name
`origin_state`	string	yes	Origin state (USPS abbr)
`dest`	string	yes	Destination airport code (FK to airports.arpt_id)
`dest_city_name`	string	yes	Destination city name
`dest_state`	string	yes	Destination state (USPS abbr)
`aircraft_type`	string	yes	BTS aircraft type code
`service_class`	string	yes	Service class (F=scheduled passenger/cargo, ...)
`departures_scheduled`	double	yes	Departures scheduled on the segment for the month
`departures_performed`	double	yes	Departures performed on the segment for the month
`seats`	double	yes	Available seats
`passengers`	double	yes	Passengers transported
`freight`	double	yes	Freight transported (pounds)
`mail`	double	yes	Mail transported (pounds)
`distance`	double	yes	Segment distance (miles)

vehicle_registrations · table¶

FHWA Highway Statistics table MV-1 — state motor-vehicle registrations by vehicle class (automobiles, buses, trucks, motorcycles, and the all-vehicle total) for the data year. FHWA publishes MV-1 only as a merged multi-header Excel workbook, so a ResponseTransformer parses the fixed MV-1 layout and derives state_fips. Delta by year; dataLag 2 targets the newest available data year. Joins to geo.states via state_fips. Source: fhwa.dot.gov/policyinformation/statistics/{year}/xls/mv1.xlsx.

Column	Type	Null	Description
`state_fips`	string	no	2-digit state FIPS code (FK to geo.states)
`state_name`	string	yes	State name (footnote markers stripped)
`automobiles`	long	yes	Registered automobiles (total, public + private)
`buses`	long	yes	Registered buses (total)
`trucks`	long	yes	Registered trucks (total)
`motorcycles`	long	yes	Registered motorcycles (total)
`all_motor_vehicles`	long	yes	All registered motor vehicles (total)

pavement_roughness · table¶

FHWA Highway Statistics table HM-64 — functional-system road mileage by measured pavement roughness (International Roughness Index bucket), broken out by area type (rural/urban) and road class (interstate, other freeways & expressways, other principal arterial, minor arterial — the last reported for rural only). FHWA publishes HM-64 only as a merged multi-header, multi-block Excel workbook (4 sheets, one or more 10-column road-class blocks per sheet), so a ResponseTransformer parses the fixed HM-64 layout and derives state_fips. Joins to geo.states via state_fips. Source: fhwa.dot.gov/policyinformation/statistics/{year}/xls/hm64.xlsx.

Column	Type	Null	Description
`state_fips`	string	no	2-digit state FIPS code (FK to geo.states)
`state_name`	string	yes	State name (footnote markers stripped)
`area_type`	string	no	'rural' or 'urban'
`road_class`	string	no	interstate, other_freeways_expressways, other_principal_arterial, or minor_arterial (rural only)
`miles_not_reported`	double	yes	Miles with no measured roughness on record
`miles_iri_under_60`	double	yes	Miles with IRI < 60 inches/mile
`miles_iri_60_94`	double	yes	Miles with IRI 60-94 inches/mile
`miles_iri_95_119`	double	yes	Miles with IRI 95-119 inches/mile
`miles_iri_120_144`	double	yes	Miles with IRI 120-144 inches/mile
`miles_iri_145_170`	double	yes	Miles with IRI 145-170 inches/mile
`miles_iri_171_194`	double	yes	Miles with IRI 171-194 inches/mile
`miles_iri_195_220`	double	yes	Miles with IRI 195-220 inches/mile
`miles_iri_over_220`	double	yes	Miles with IRI > 220 inches/mile
`miles_total_reported`	double	yes	Total miles with measured roughness on record

usace_locks · table¶

US Army Corps of Engineers navigation lock inventory (Lock Performance Monitoring System master list) — one row per lock chamber, 234 locks nationally, with the year each opened as the age field. No per-lock condition or deferred-maintenance figure is published anywhere (NID's own CONDITION_ASSESSMENT field reads "Not Available" for every USACE navigation dam checked) — only age is sourced here, not condition or backlog. Hosted on ArcGIS Hub under an individual account rather than an official USACE/HIFLD org account; re-verify the URL if it ever 404s, same caveat class as this warehouse's other individually-hosted ArcGIS mirrors (housing.opportunity_zones, lands.va_facilities). Joins to geo.states via state_abbr. Source: opendata.arcgis.com dataset 78ef8693f30b40b980737c561ce6b103_0.

Column	Type	Null	Description
`lock_id`	string	no	USACE National Datum/Drainage Code — stable natural key, PK
`name`	string	yes
`river`	string	yes
`state_abbr`	string	yes	2-letter USPS state code (FK component to geo.states)
`division`	string	yes	USACE division code (e.g. LRD, SWD)
`district`	string	yes	USACE district code (e.g. LRL, SWL)
`year_open`	integer	yes	Year the lock opened — the age field this table exists to carry
`length_ft`	double	yes	Usable chamber length (feet)
`width_ft`	double	yes	Usable chamber width (feet)
`lift_ft`	double	yes	Normal lift (feet)
`latitude`	double	yes
`longitude`	double	yes

fmcsa_carriers · table¶

FMCSA Company Census File — one row per USDOT-registered motor carrier (~4.47M rows), carrying registration, fleet size (drivers, CDL drivers, power/truck/bus units), operation type, hazmat authorization, and (for carriers that received a compliance review) the assigned safety rating and a per-carrier recordable_crash_rate. Event-level crash/inspection/ out-of-service records are NOT on this file (only ~54K carriers are ever rated); they publish as separate per-event SMS datasets (SMS Input - Crash 4wxs-vbns / aayw-vxb3, SMS Input - Inspection rbkj-cgst) and would be their own raw tables + a rollup view, per the one-source-per-table rule. Bulk CSV export of DOT Socrata dataset az4n-8mr2 — the whole set is downloaded in one unpaginated request and the partition overwritten each run, so this is a snapshot. Source: data.transportation.gov/api/views/az4n-8mr2/rows.csv?accessType=DOWNLOAD. To reach a carrier's cross-schema identity FROM another schema rather than by name, join ref.canonical_org_entity on fmcsa_dot_number = dot_number — that table carries one foreign key per source, so the carrier's LEI/SEC CIK and its dot_number sit in the same row and the join is exact. Matching carrier_name as text (including against sec.filing_metadata) finds the wrong entities and misses carriers filing under a differently-worded name.

Column	Type	Null	Description
`dot_number`	string	no	USDOT number — PK
`carrier_name`	string	yes	Legal carrier name — for the exact cross-schema join use ref.canonical_org_entity.fmcsa_dot_number = dot_number, not a name match
`dba_name`	string	yes	Doing-business-as name (often blank)
`phy_city`	string	yes	Principal place of business — city
`state_abbr`	string	yes	Principal office state (USPS abbr; FK component to geo.state_ref)
`phy_zip`	string	yes	Principal place of business — ZIP
`business_org`	string	yes	Business organization (CORPORATION / LLC / SOLE PROPRIETOR / ...)
`classdef`	string	yes	Carrier classification (semicolon multi-value, e.g. PRIVATE PROPERTY;AUTHORIZED FOR HIRE)
`operation_type`	string	yes	Operation classification (A=Interstate, B=Intrastate Hazmat, C=Intrastate Non-Hazmat)
`safety_rating`	string	yes	Safety rating (S=Satisfactory, C=Conditional, U=Unsatisfactory; blank unless a compliance review occurred)
`safety_rating_date`	date	yes	Date of the most recent safety rating
`review_date`	date	yes	Date of the most recent compliance review
`total_drivers`	integer	yes	Number of drivers
`total_cdl`	integer	yes	Number of CDL drivers
`power_units`	integer	yes	Power units (trucks and tractors)
`truck_units`	integer	yes	Truck units
`bus_units`	integer	yes	Bus units
`hazmat_flag`	string	yes	Authorized to transport hazardous materials (Y/N)
`recordable_crash_rate`	double	yes	Crashes per million miles, FMCSA's own computed field — NOT populated for the whole carrier census. FMCSA computes it only for a carrier with a compliance review or a reported mileage figure on file, which is 20% of active carriers (447,289 of 2,236,332, measured live 2026-09-10) — a caller must not treat a non-null rate as representative of the other 80%, since carriers with a compliance review skew toward ones FMCSA already had reason to look at. 999.0/999.99/999.999 are FMCSA's own placeholder sentinels for "not computed" (489 rows, measured live) and are excluded here rather than left as data — a naive AVG or top-N over the raw field is dominated by them. Values above 900 that are NOT one of those three exact sentinels (a handful of rows in the 909-954 range) are real: an extremely low-mileage carrier can genuinely compute to a rate in that range and is not filtered out.
`mcs150_mileage`	long	yes	Annual vehicle miles traveled reported on the MCS-150
`mcs150_mileage_year`	integer	yes	Year of the reported MCS-150 mileage
`status_code`	string	yes	Carrier record status code (A=Active, I=Inactive, ...)

faa_aircraft_master · table¶

FAA MASTER.txt — one row per registered aircraft (~314K), keyed by N-number. Faithful recreation of the master file: registration, address, airworthiness/certificate dates, Mode-S transponder codes, fractional ownership, kit build, and the reference/engine join codes. Snapshot. Source: registry.faa.gov/database/ReleasableAircraft.zip (MASTER.txt).

Column	Type	Null	Description
`n_number`	string	no	FAA N-number / tail number, canonical N-prefixed form (e.g. N100) — PK; join target for airline_ontime.tail_number and ntsb_aviation_accidents.aircraft_n_number
`serial_number`	string	yes	Manufacturer serial number
`mfr_mdl_code`	string	yes	7-char Aircraft Mfr/Model/Series code (FK to faa_aircraft_reference.code)
`eng_mfr_mdl`	string	yes	5-char engine Mfr/Model code (FK to faa_engine_reference.code)
`year_mfr`	integer	yes	Year of manufacture
`type_registrant`	string	yes	Registrant type code (1=Individual, 3=Corporation, 5=Government, 7=LLC, ...)
`registrant_name`	string	yes	Registered owner name
`street`	string	yes	Registrant street address
`street2`	string	yes	Registrant street address line 2
`city`	string	yes	Registrant city
`state_abbr`	string	yes	Registrant state (USPS abbr; FK to geo.state_ref)
`zip_code`	string	yes	Registrant ZIP code
`region`	string	yes	FAA region code (internal, not census)
`county_code`	string	yes	County code (3-digit FIPS county within state, US records)
`country`	string	yes	Country code (US = domestic)
`last_action_date`	date	yes	Date of last registration action
`cert_issue_date`	date	yes	Certificate issue date
`certification`	string	yes	Airworthiness classification + approved operations (packed code)
`type_aircraft`	string	yes	Aircraft type code (denormalized; see faa_aircraft_reference.type_acft)
`type_engine`	string	yes	Engine type code (denormalized; see faa_aircraft_reference.type_eng)
`status_code`	string	yes	Registration status code (V=Valid, R=pending, ...)
`mode_s_code`	string	yes	Mode-S transponder code (octal)
`fract_owner`	string	yes	Fractional ownership indicator (Y/blank)
`airworthiness_date`	date	yes	Date of original airworthiness certificate
`other_names_1`	string	yes	Additional registrant name 1
`other_names_2`	string	yes	Additional registrant name 2
`other_names_3`	string	yes	Additional registrant name 3
`other_names_4`	string	yes	Additional registrant name 4
`other_names_5`	string	yes	Additional registrant name 5
`expiration_date`	date	yes	Registration expiration date
`unique_id`	string	yes	FAA stable unique aircraft id
`kit_mfr`	string	yes	Kit manufacturer (amateur-built)
`kit_model`	string	yes	Kit model (amateur-built)
`mode_s_code_hex`	string	yes	Mode-S / ICAO 24-bit address (hex; ADS-B flight-tracking join key)

faa_aircraft_reference · table¶

FAA ACFTREF.txt — aircraft reference file, one row per Mfr/Model/Series code (~94K): manufacturer, model, aircraft/engine type, category, engine and seat counts, weight class, cruising speed, type-certificate holder. Faithful recreation; joined from faa_aircraft_master on mfr_mdl_code. Source: registry.faa.gov/database/ReleasableAircraft.zip (ACFTREF.txt).

Column	Type	Null	Description
`code`	string	no	7-char Aircraft Mfr/Model/Series code — PK
`mfr`	string	yes	Manufacturer name
`model`	string	yes	Model / series name
`type_acft`	string	yes	Aircraft type code (4=Fixed wing single, 5=Fixed wing multi, 6=Rotorcraft, ...)
`type_eng`	string	yes	Engine type code (1=Reciprocating, 4=Turbo-jet, 5=Turbo-fan, 10=Electric, ...)
`ac_cat`	string	yes	Aircraft category code (1=Land, 2=Sea, 3=Amphibian)
`build_cert_ind`	string	yes	Builder certification (0=Type Certificated, 1=Not TC, 2=Light Sport)
`no_eng`	integer	yes	Number of engines
`no_seats`	integer	yes	Maximum number of seats
`ac_weight`	string	yes	Weight class (CLASS 1 <=12499 lb, CLASS 2, CLASS 3 20000+, CLASS 4 UAV)
`speed`	integer	yes	Average cruising speed (mph)
`tc_data_sheet`	string	yes	Type Certificate data sheet
`tc_data_holder`	string	yes	Type Certificate data holder

faa_engine_reference · table¶

FAA ENGINE.txt — engine reference file, one row per engine Mfr/Model code (~5K): manufacturer, model, engine type, horsepower, thrust. Faithful recreation; joined from faa_aircraft_master on eng_mfr_mdl. Source: registry.faa.gov/database/ReleasableAircraft.zip (ENGINE.txt).

Column	Type	Null	Description
`code`	string	no	5-char engine Mfr/Model code — PK
`mfr`	string	yes	Engine manufacturer name
`model`	string	yes	Engine model name
`engine_type_code`	string	yes	Engine type code (1=Reciprocating, 4=Turbo-jet, 5=Turbo-fan, 10=Electric, ...)
`horsepower`	integer	yes	Rated horsepower (piston/turboprop)
`thrust`	integer	yes	Rated thrust in pounds (jet)

ntsb_aviation_accidents · table¶

NTSB aviation accident & incident database — one row per aircraft involved in an event, with injury severity and fatal/serious/minor injury counts, aircraft damage level, FAR operating part, phase-of-flight code, probable-cause narrative, and event lat/lon. NTSB's own avall.zip is documented as spanning 1982-present, but the actual MIN(event_year) in this table should be checked directly (SELECT MIN(event_year), MAX(event_year), COUNT(*) FROM ... — there is no observedCoverage block here since this table has no year= partition to anchor one) rather than assumed from the source's nominal range. The download (avall.zip, ~95MB) is a single Microsoft Access database (avall.mdb, ~553MB) read in-JVM with Jackcess by NtsbAviationTransformer: the aircraft table (grain ev_id + Aircraft_Key) is streamed and joined to events (ev_id) and the narratives.narr_cause probable-cause text. The MDB has no county column, so only state_fips is derived (from USPS ev_state). Snapshot: the whole MDB is re-ingested and the partition overwritten each run. event_year is a column (not a partition) since one file carries all years; aircraft_n_number joins to faa_aircraft_master.n_number. Source: data.ntsb.gov/avdata/avall.zip.

Column	Type	Null	Description
`event_id`	string	no	NTSB event id (ev_id) — PK component
`aircraft_key`	integer	no	Aircraft sequence within the event (Aircraft_Key) — PK component
`event_year`	integer	yes	Year of the event
`event_date`	date	yes	Date of the accident/incident
`event_type`	string	yes	Accident / Incident (decoded from ev_type)
`aircraft_n_number`	string	yes	Aircraft tail number, canonical N-prefixed (regis_no; FK to faa_aircraft_master.n_number)
`aircraft_make`	string	yes	Aircraft manufacturer (acft_make)
`aircraft_model`	string	yes	Aircraft model (acft_model)
`aircraft_category`	string	yes	Aircraft category code (acft_category; e.g. AIR, HELI)
`far_part`	string	yes	FAR operating part (091, 121, 135, ...) — operation type
`carrier_name`	string	yes	Operator/carrier name (oper_name)
`city`	string	yes	Event city (ev_city)
`state_abbr`	string	yes	Event state (USPS abbr; FK component to geo.state_ref)
`state_fips`	string	yes	2-digit state FIPS code (FK to geo.states)
`country`	string	yes	Event country code (ev_country)
`airport_id`	string	yes	Nearest airport id (ev_nr_apt_id; null for en-route)
`airport_name`	string	yes	Nearest airport name (apt_name)
`injury_severity`	string	yes	Highest injury level (Fatal / Serious / Minor / None; decoded)
`fatal_injuries`	integer	yes	Total fatalities (inj_tot_f)
`serious_injuries`	integer	yes	Total serious injuries (inj_tot_s)
`minor_injuries`	integer	yes	Total minor injuries (inj_tot_m)
`aircraft_damage`	string	yes	Aircraft damage (Destroyed / Substantial / Minor / None; decoded)
`phase_flt_code`	integer	yes	NTSB phase-of-flight code (phase_flt_spec; raw code, not decoded)
`latitude`	double	yes	Event latitude (dec_latitude, WGS84)
`longitude`	double	yes	Event longitude (dec_longitude, WGS84)
`probable_cause`	string	yes	NTSB probable-cause narrative (narr_cause, truncated to 4000 chars)

cfs_shipments · table¶

Census/BTS Commodity Flow Survey 2017 Public Use File (PUF) — one row per sampled shipment (~5.98M), the shipment-level microdata behind every CFS cross-tab. Origin/destination state + CFS area, SCTG commodity, mode, shipper NAICS, value ($), weight (lb), great-circle + routed distance (miles), and WGT_FACTOR (the tabulation weight: SUM(measure * wgt_factor) estimates the population total). All published cross-tabs (origin x dest x commodity x mode, by-NAICS, by-distance, ...) are SQL views over this one table (never Java) — e.g. the bts_freight_flows view. Aggregates are sample-weighted estimates and approximate the official CFS tables. Bulk CSV export of BTS Socrata y9wr-xk52; snapshot, partition overwritten each run. Source: data.bts.gov/api/views/y9wr-xk52/rows.csv.

Column	Type	Null	Description
`shipmt_id`	string	no	Shipment identifier — PK
`survey_year`	integer	no	CFS survey year (this PUF file is 2017)
`orig_state_fips`	string	yes	Origin state FIPS (FK to geo.state_ref)
`orig_cfs_area`	string	yes	Origin CFS area code (state-MA)
`dest_state_fips`	string	yes	Destination state FIPS (FK to geo.state_ref)
`dest_cfs_area`	string	yes	Destination CFS area code (state-MA)
`naics`	string	yes	Shipper NAICS code
`quarter`	integer	yes	Quarter of shipment (1-4)
`sctg_code`	string	yes	SCTG commodity code, zero-padded to 2 digits (FK to cfs_sctg_ref.sctg_code for single non-suppressed codes; a "NN-NN" value is a confidentiality-collapsed range with no single ref row)
`mode_code`	string	yes	CFS transport mode code, zero-padded to 2+ digits (raw; some recoded for confidentiality; FK to cfs_mode_ref.mode_code)
`shipmt_value`	double	yes	Shipment value (USD)
`shipmt_weight_lb`	double	yes	Shipment weight (pounds)
`dist_gc_miles`	double	yes	Great-circle distance origin->destination (miles)
`dist_routed_miles`	double	yes	Routed distance origin->destination (miles)
`temp_controlled`	string	yes	Temperature-controlled shipment (true/false)
`export_yn`	string	yes	Export shipment (true/false)
`export_country`	string	yes	Export final destination country indicator
`hazmat`	string	yes	Hazardous material code (N = not hazmat)
`wgt_factor`	double	yes	Shipment tabulation weighting factor (multiply measures by this for population estimates)

cfs_sctg_ref · table¶

SCTG (Standard Classification of Transported Goods) code list — Appendix A-3 of the 2017 CFS PUF Data Users Guide, the cfs_shipments.sctg_code lookup. Census/BTS publish this only as a PDF table (no CSV/XLSX distribution), parsed here via CfsSctgRefTransformer. 43 rows: the 41 detailed 2-digit codes, "43" (mixed freight), and "00" (suppressed). sctg_group is populated only on the first code of a confidentiality-collapsed range (e.g. "01" carries group "01-05"); cfs_shipments.sctg_code itself can also independently hold the collapsed "NN-NN" range value directly, which has no matching single row here — that is expected, not a join defect (see the sctg_code column comment on cfs_shipments). 2017-vintage, one-time reference data; Census has not revised this appendix since.

Column	Type	Null	Description
`sctg_code`	string	no	2-digit SCTG code (PK; "00" = suppressed, "43" = mixed freight)
`description`	string	no	Official SCTG commodity description
`sctg_group`	string	yes	Confidentiality-collapsed SCTG group range this code heads (e.g. "01-05"), only where the appendix gives one

cfs_mode_ref · table¶

Mode of transportation code list — Appendix A-4 of the 2017 CFS PUF Data Users Guide, the cfs_shipments.mode_code lookup. Parsed via CfsModeRefTransformer from the same PDF as cfs_sctg_ref (no CSV/XLSX distribution exists). 21 rows: the 20 detailed mode codes (e.g. "04" for-hire truck, "06" rail, "101" multiple waterways) plus "00" (suppressed). 2017-vintage, one-time reference data; Census has not revised this appendix since.

Column	Type	Null	Description
`mode_code`	string	no	CFS mode code (PK; "00" = suppressed)
`description`	string	no	Official mode-of-transportation description

bts_port_teu · table¶

Monthly container throughput (TEUs) for the Top-10 U.S. container ports, from BTS's Port Performance Freight Statistics Program (Socrata rd72-aq8r, "Monthly TEU Data"). Melted from the source's one-row-per-month/one-column-per-port layout into one row per (port, month). This is the best freely-ingestible port-level container-volume series found — the industry-standard NRF/Hackett Global Port Tracker has no free bulk-downloadable time series, only point figures in press releases (confirmed independently by two arms in Defect Register D-109). NOT a currently-updated series: the source dataset itself stops at October 2022 (46 months from Jan 2019) and has not been refreshed since — verified live 27 Aug 2026. Real historical data for that window, not a live/current-era proxy; a national customs-value proxy (econ.trade_imports) remains the only current-era option.

Column	Type	Null	Description
`report_date`	date	no	First of month
`port_code`	string	no	Source column name for this port (e.g. "los_angeles_ca")
`port_name`	string	yes	Human-readable port name
`teu`	double	yes	Twenty-foot equivalent units handled that month

bridges · table¶

FHWA National Bridge Inventory — one row per raw NBI record per annual data-year snapshot, NOT one row per bridge: the source file commingles primary bridge records, culvert records, and border/jointly-owned-structure secondary records under one row shape (see record_type). Filter to record_type='1' to match FHWA's own published national bridge count. Deck/superstructure/substructure condition ratings (0-9, N), FHWA's own precomputed bridge_condition (Good/Fair/Poor) and lowest_rating summary fields, location, year built/reconstructed, traffic, and improvement-cost fields (bridge/roadway/total, plus year_of_imp) for federal-spending comparisons. The "delimited" national file uses a single-quote text qualifier, not RFC4180 double quote, so source.response.quoted is false: a handful of free-text columns (e.g. features_desc, facility_carried) carry literal leading/trailing single quotes that this table does not strip — harmless for the numeric/coded columns this table curates — and any stray literal " in free text (an apostrophe mistyped as one, an inch mark) is read as ordinary text instead of an RFC4180 quote, which would otherwise merge that record with the next line and desync every column in both. decimal_latitude/decimal_longitude are computed from the source's packed DDMMSS.ss / DDDMMSS.ss fields (Items 16/17) using length-relative substring extraction (minutes/seconds anchored to the end of the string, degrees to whatever remains at the front) rather than fixed character positions, so a digit-count change at the front of the field (e.g. a leading zero) doesn't shift the split; county_code is zero-padded back to 3 digits for the same leading-zero-loss reason numeric fields generally have. LAT_016/LONG_017 are also listed in source.response.rawFields to stop the generic CSV ingest from routing them through numeric inference at all — undeclared as their own column, they'd otherwise be parsed as a Long/Double and re-serialized before this expression ever runs, which can append a trailing .0 that length-relative extraction can't distinguish from real digits. The raw LAT_016/LONG_017 fields are not carried through as their own output columns. Source: fhwa.dot.gov/bridge/nbi/{year}allstatesallrecsdel.zip (see fhwa.dot.gov/bridge/nbi/ascii.cfm).

Column	Type	Null	Description
`state_code`	string	yes	2-digit NBI/FIPS state code (FK to geo.state_ref)
`structure_number`	string	no	Structure number, unique within state — PK component
`record_type`	string	yes	NBI Item 5A: '1'=primary bridge inventory record, '2'=culvert record, a letter (A-Z)=secondary/reference record for a border or jointly-owned structure (see OTHER_STATE_CODE_098A/OTHR_STATE_STRUC_NO_099, not yet carried through). This table's raw source file (fhwa.dot.gov/bridge/nbi) commingles all three kinds under one row shape; FHWA's own published national bridge count (e.g. 623,218 for 2024) counts record_type='1' only — WHERE record_type='1' reproduces that figure to within 0.1%. Confirmed live: culverts + secondary records account for the ~117K-row gap between this table's raw COUNT(*) and FHWA's headline total.
`county_code`	string	yes	3-digit county code component (FK component to geo.counties via state_code+county_code)
`facility_carried`	string	yes
`location`	string	yes
`features_desc`	string	yes	Feature intersected (waterway, road, railroad, etc.)
`decimal_latitude`	double	yes	Decoded from packed NBI Item 16 (DDMMSS.ss). Minutes/seconds are taken from the right end of the string rather than a fixed offset, so a leading zero the generic CSV ingest's numeric inference would otherwise strip from a purely-numeric field doesn't shift a fixed-position split. LAT_016 is also listed in source.response.rawFields (see the table comment) so it reaches this expression as the exact source string, not a re-serialized number.
`decimal_longitude`	double	yes	Decoded from packed NBI Item 17 (DDDMMSS.ss); negated (US bridges are West). Length-relative extraction — see decimal_latitude's comment; longitude degrees is normally 3 digits (leading zero for <100°), which is why a fixed SUBSTRING(1,3) isn't safe here. LONG_017 is also listed in source.response.rawFields for the same reason as LAT_016.
`year_built`	integer	yes
`year_reconstructed`	integer	yes
`traffic_lanes_on`	integer	yes
`avg_daily_traffic`	integer	yes
`year_adt`	integer	yes
`deck_condition`	string	yes	NBI condition code 0-9 (9=excellent) or N (not applicable)
`superstructure_condition`	string	yes
`substructure_condition`	string	yes
`culvert_condition`	string	yes
`structural_eval`	string	yes
`bridge_condition`	string	yes	FHWA's Good/Fair/Poor rollup. FHWA's own precomputed BRIDGE_CONDITION field does not exist in the raw NBI file before 2019 (confirmed against the cached 2017/2018 files — the column is simply absent, not blank), which left this NULL on all 615,576 2017 rows and all 616,595 2018 rows. Computed here from the four component ratings using FHWA's own published classification (Poor: any of deck/superstructure/substructure/culvert <= 4; Good: all >= 7; Fair: otherwise) when the raw field is missing or invalid — validated against 617,084 record_type=1 rows in 2019, where both the raw field and the components are present: 100% agreement, zero mismatches. Also guards against a single malformed 2024 row that carried "42" (that year's state_code, apparently shifted into this field) by restricting the raw field to its only 4 legal values before trusting it, falling through to the computed rollup otherwise.
`lowest_rating`	integer	yes	Lowest of the four component condition ratings. Same 2017/2018 absence as bridge_condition (LOWEST_RATING is not a column in the raw NBI file before 2019) - computed from the components with the identical formula when the raw field is missing. A value of 99 means every component was itself missing/not applicable for that structure, not a genuinely high rating - filter it out of any MIN/aggregate.
`deck_area_sqm`	double	yes
`structure_length_m`	double	yes
`main_unit_spans`	integer	yes
`date_of_inspection`	string	yes	MMYYYY per the NBI coding guide
`inspect_freq_months`	integer	yes
`bridge_imp_cost_thousands`	double	yes
`roadway_imp_cost_thousands`	double	yes
`total_imp_cost_thousands`	double	yes
`year_of_imp`	integer	yes
`owner`	string	yes
`functional_class`	string	yes
`highway_system`	string	yes
`scour_critical`	string	yes	Scour vulnerability rating code (safety-critical for waterway crossings)

phmsa_hazardous_liquid_incidents · table¶

PHMSA hazardous liquid pipeline accident reports (Form PHMSA F 7000-1), one row per accident, January 2010-present. phmsa.dot.gov itself 403s automated fetches; this pulls the identical bulk file from data.transportation.gov's open-data catalog instead — the catalog entry (resource 27nc-rsge) is a Socrata "href" pointer, not a queryable Socrata table, so the fetch targets its file attachment directly rather than the Socrata row API. Curated to a subset of the source's ~650 columns (safety outcome, cost, cause, and identity fields — not the full forensic investigation detail). Confirmed live: this snapshot is already deduplicated to one row per REPORT_NUMBER (no repeated SUPPLEMENTAL_NUMBER revisions), unlike the multi-supplemental versioning PHMSA's other endpoints can carry. For per-mile safety-rate normalization, join to phmsa_hazardous_liquid_mileage on operator_id + report_year. Only the 2010-present era is loaded; PHMSA also publishes three earlier eras (1986-2001, 2004-2009, pre-1986) under an incompatible pre-2010 form revision, not ingested here.

Column	Type	Null	Description
`report_number`	string	no	PHMSA report number (PK)
`report_received_date`	string	yes
`activity_year`	integer	yes	Year of the incident (IYEAR)
`report_type`	string	yes
`operator_id`	string	yes	PHMSA operator identifier — join key to phmsa_hazardous_liquid_mileage.operator_id
`operator_name`	string	yes
`operator_state_abbr`	string	yes	2-letter USPS state code of the operator's address (not necessarily the incident location)
`local_datetime`	string	yes	Local date/time the incident occurred
`location_latitude`	double	yes
`location_longitude`	double	yes
`onshore_state_abbr`	string	yes	2-letter USPS state code of the incident location (onshore incidents only)
`onshore_county_name`	string	yes
`on_off_shore`	string	yes
`commodity_released_type`	string	yes
`system_part_involved`	string	yes
`unintentional_release_bbls`	double	yes
`intentional_release_bbls`	double	yes
`recovered_bbls`	double	yes
`fatalities_total`	integer	yes	Total fatalities (PHMSA's own precomputed sum across employee/contractor/emergency-responder/general-public)
`injuries_total`	integer	yes	Total injuries (PHMSA's own precomputed sum across employee/contractor/emergency-responder/general-public)
`ignite_ind`	string	yes
`explode_ind`	string	yes
`cause`	string	yes	PHMSA root-cause category (e.g. CORROSION, MATERIAL/WELD/EQUIP FAILURE, INCORRECT OPERATION, NATURAL FORCE DAMAGE, EXCAVATION DAMAGE, OTHER OUTSIDE FORCE DAMAGE, ALL OTHER CAUSES)
`cause_details`	string	yes
`est_cost_oper_paid`	double	yes	Estimated cost of operator-paid public/private property damage (USD)
`est_cost_prop_damage`	double	yes
`est_cost_emergency`	double	yes
`est_cost_environmental`	double	yes
`est_cost_other`	double	yes
`pipe_facility_type`	string	yes

phmsa_hazardous_liquid_mileage · table¶

PHMSA hazardous liquid pipeline operators' annual report (Form PHMSA F 7000-1.1), Part A (operator identity) through Part E (pipe-age distribution) fields, one row per (report_year, operator, commodity) — the per-mile normalization denominator for phmsa_hazardous_liquid_incidents (join on operator_id + report_year). Same data.transportation.gov bulk-file route as the incidents table (catalog resource aemm-7pyq, an "href" pointer; this fetches its file attachment directly). The source ZIP splits each year's annual report into ~11 "Part" CSVs (Part A-E, F-G, H, I, ... R); only the Part A-E file is ingested here, since it alone carries total_miles (Part D) and mileage-by-installation-decade (Part E) — the fields this warehouse needs. Confirmed identical column layout across 2017-2024 (the years the source publishes as separate Part-CSVs; extractPattern merges all matching entries into one table). 2010-2016 exist only as whole-workbook XLSX files (one per year, sheet "HL AR Part A to E") rather than separate Part-CSVs, but confirmed live to carry the exact same 88-column header as the 2017+ era — a PhmsaHazardousLiquidMileageHistoricalTransformer responseTransformer parses those years via POI and appends them to the 2017+ CSV text extractPattern already produces, so this table now covers 2010-present.

Column	Type	Null	Description
`report_year`	integer	yes
`report_number`	string	yes
`operator_id`	string	yes	PHMSA operator identifier — join key to phmsa_hazardous_liquid_incidents.operator_id
`operator_name`	string	yes
`operator_state_abbr`	string	yes	2-letter USPS state code of the operator's address
`commodity`	string	yes
`hca_miles_onshore`	double	yes	Miles in High Consequence Areas, onshore (Part B)
`hca_miles_offshore`	double	yes
`hca_miles_total`	double	yes
`total_miles`	double	yes	Total regulated pipeline miles operated (Part D) — the per-mile safety-rate denominator
`total_miles_onshore`	double	yes
`total_miles_offshore`	double	yes
`miles_installed_pre_1940`	double	yes
`miles_installed_2010s`	double	yes
`miles_installed_2020s`	double	yes
`miles_installation_year_unknown`	double	yes
`report_submission_type`	string	yes	e.g. INITIAL, AMENDED — later amended reports for the same (report_year, operator) supersede earlier ones; not deduplicated here

phmsa_gas_distribution_incidents · table¶

PHMSA gas distribution pipeline incident reports (Form PHMSA F 7100.1), one row per incident, January 2010-present. Same data.transportation.gov open-data catalog route as phmsa_hazardous_liquid_incidents (catalog resource 27nc-rsge, the same multi-commodity "href" pointer entry; this fetches the Gas Distribution file attachment instead of the Hazardous Liquid one). Confirmed live: 1,550 rows, one per REPORT_NUMBER, no comma-padding rows (unlike the annual-report CSVs). Gas incidents report release volumes in thousand standard cubic feet (mcf) per Form field A7/A8, not barrels — unintentional_release_mcf/intentional_release_mcf are not comparable to phmsa_hazardous_liquid_incidents' _bbls columns. No offshore concept for local distribution systems (location_state_abbr/location_county_name only, unlike the onshore/offshore split gas transmission and hazardous liquid carry). Only the 2010-present era is loaded; PHMSA also publishes three earlier eras (1984-2004, 2004-2009 [sic, overlapping label on the source catalog], and 1970-mid1984) under incompatible earlier form revisions, not ingested here.

Column	Type	Null	Description
`report_number`	string	no	PHMSA report number (PK)
`report_received_date`	string	yes
`activity_year`	integer	yes	Year of the incident (IYEAR)
`report_type`	string	yes
`operator_id`	string	yes	PHMSA operator identifier — join key to phmsa_gas_distribution_mileage.operator_id
`operator_name`	string	yes
`operator_state_abbr`	string	yes	2-letter USPS state code of the operator's address (not necessarily the incident location)
`local_datetime`	string	yes	Local date/time the incident occurred
`location_latitude`	double	yes
`location_longitude`	double	yes
`location_state_abbr`	string	yes	2-letter USPS state code of the incident location — gas distribution systems are always onshore, no on/off-shore split
`location_county_name`	string	yes
`commodity_released_type`	string	yes
`system_part_involved`	string	yes
`unintentional_release_mcf`	double	yes	Volume released unintentionally, thousand standard cubic feet (mcf) — Form field A7
`intentional_release_mcf`	double	yes	Volume released intentionally/controlled blowdown, mcf — Form field A8
`fatalities_total`	integer	yes	Total fatalities (PHMSA's own precomputed sum across employee/contractor/emergency-responder/general-public)
`injuries_total`	integer	yes	Total injuries (PHMSA's own precomputed sum across employee/contractor/emergency-responder/general-public)
`ignite_ind`	string	yes
`explode_ind`	string	yes
`cause`	string	yes	PHMSA root-cause category
`cause_details`	string	yes
`est_cost_oper_paid`	double	yes	Estimated cost of operator-paid public/private property damage (USD)
`est_cost_prop_damage`	double	yes
`est_cost_emergency`	double	yes
`est_cost_other`	double	yes
`gas_value_lost_unintentional_usd`	double	yes	Estimated dollar value of gas lost unintentionally — gas's analog to a liquid spill's environmental-cost field, since lost gas has no cleanup cost but does have a commodity value
`gas_value_lost_intentional_usd`	double	yes
`pipe_facility_type`	string	yes

phmsa_gas_distribution_mileage · table¶

PHMSA gas distribution operators' annual report (Form PHMSA F 7100.1-1), one row per (report_year, operator, commodity) — the per-mile normalization denominator for phmsa_gas_distribution_incidents (join on operator_id + report_year). Same data.transportation.gov bulk-file route as phmsa_hazardous_liquid_mileage (catalog resource aemm-7pyq, the same multi-commodity "href" pointer; this fetches the Gas Distribution file attachment). Unlike the hazardous-liquid annual report, this source publishes one flat CSV per year (not split into lettered Parts), but the column set still drifts slightly year to year (278 cols in 2017-2022, 274 in 2023, 300 in 2024 — all differences are trailing excavation-damage-detail columns not used here); the ETL's zip-merge aligns columns by name across files with differing headers, not position, so this is safe. Confirmed live: the 2020-2024 files carry comma-only padding lines (81-145 per year) that parse into all-NULL rows without a rowFilter, same defect class as phmsa_hazardous_liquid_mileage. An operator can file more than one commodity row per year (Natural Gas, Propane Gas, Landfill Gas, Synthetic Gas, Hydrogen Gas), so report_number — not operator_id + report_year — is the unique key. total_services (individual customer service-line count) has no analog in the hazardous-liquid table; gas distribution systems are sized by service count as well as main mileage. 2010-2016 exist only as whole-workbook XLSX files (one per year, single sheet "GD AR ") rather than separate per-year CSVs; a PhmsaGasDistributionMileageHistoricalTransformer responseTransformer parses those years via POI and appends them to the 2017+ CSV text extractPattern already produces, so this table now covers 2010-present. Confirmed live: the 2010-2014 workbooks (237 cols) carry no COMMODITY field at all — it was only added starting with the 2015 workbook (278 cols, matching the 2017+ header exactly) — so commodity is NULL for 2010-2014 rows rather than guessed; every other target field (including report_number as a clean unique key) is present for the whole 2010-2016 span.

Column	Type	Null	Description
`report_year`	integer	yes
`report_number`	string	no	PHMSA report number (PK) — an operator may file multiple commodity rows per year, so this is the unique key, not operator_id + report_year
`operator_id`	string	yes	PHMSA operator identifier — join key to phmsa_gas_distribution_incidents.operator_id
`operator_name`	string	yes
`operator_state_abbr`	string	yes	2-letter USPS state code of the operator's office address
`commodity`	string	yes	Natural Gas, Propane Gas, Landfill Gas, Synthetic Gas, or Hydrogen Gas
`total_miles`	double	yes	Total distribution main mileage operated, all materials combined — the per-mile safety-rate denominator
`total_services`	double	yes	Total individual customer service lines — gas-distribution-specific system-size denominator, no analog in the hazardous-liquid or gas-transmission mileage tables
`miles_installed_pre_1940`	double	yes
`miles_installed_2010s`	double	yes
`miles_installed_2020s`	double	yes
`report_submission_type`	string	yes	e.g. INITIAL, AMENDED — later amended reports for the same (report_year, operator) supersede earlier ones; not deduplicated here

phmsa_gas_transmission_incidents · table¶

PHMSA gas transmission & gathering pipeline incident reports (Form PHMSA F 7100.2), one row per incident, January 2010-present. Same data.transportation.gov open-data catalog route as phmsa_hazardous_liquid_incidents (catalog resource 27nc-rsge, the same multi-commodity "href" pointer entry; this fetches the Gas Transmission & Gathering file attachment instead). Confirmed live: 1,996 rows, one per REPORT_NUMBER, no comma-padding rows. Like gas distribution, release volumes are in thousand standard cubic feet (mcf), not barrels (unintentional_release_mcf/intentional_release_mcf, not _bbls). Unlike gas distribution, transmission pipelines can run offshore, so this table carries the same on_off_shore/onshore_state_abbr/onshore_county_name split as phmsa_hazardous_liquid_incidents. Only the 2010-present era is loaded; PHMSA also publishes three earlier eras (1970-mid1984, mid1984-2001, 2002-2009) under incompatible earlier form revisions, not ingested here.

Column	Type	Null	Description
`report_number`	string	no	PHMSA report number (PK)
`report_received_date`	string	yes
`activity_year`	integer	yes	Year of the incident (IYEAR)
`report_type`	string	yes
`operator_id`	string	yes	PHMSA operator identifier — join key to phmsa_gas_transmission_mileage.operator_id
`operator_name`	string	yes
`operator_state_abbr`	string	yes	2-letter USPS state code of the operator's address (not necessarily the incident location)
`local_datetime`	string	yes	Local date/time the incident occurred
`location_latitude`	double	yes
`location_longitude`	double	yes
`onshore_state_abbr`	string	yes	2-letter USPS state code of the incident location (onshore incidents only)
`onshore_county_name`	string	yes
`on_off_shore`	string	yes
`commodity_released_type`	string	yes
`system_part_involved`	string	yes
`unintentional_release_mcf`	double	yes	Volume released unintentionally, thousand standard cubic feet (mcf)
`intentional_release_mcf`	double	yes	Volume released intentionally/controlled blowdown, mcf
`fatalities_total`	integer	yes	Total fatalities (PHMSA's own precomputed sum across employee/contractor/emergency-responder/general-public)
`injuries_total`	integer	yes	Total injuries (PHMSA's own precomputed sum across employee/contractor/emergency-responder/general-public)
`ignite_ind`	string	yes
`explode_ind`	string	yes
`cause`	string	yes	PHMSA root-cause category
`cause_details`	string	yes
`est_cost_oper_paid`	double	yes	Estimated cost of operator-paid public/private property damage (USD)
`est_cost_prop_damage`	double	yes
`est_cost_emergency`	double	yes
`est_cost_other`	double	yes
`gas_value_lost_unintentional_usd`	double	yes	Estimated dollar value of gas lost unintentionally — gas's analog to a liquid spill's environmental-cost field
`gas_value_lost_intentional_usd`	double	yes
`pipe_facility_type`	string	yes

phmsa_gas_transmission_mileage · table¶

PHMSA gas transmission & gathering operators' annual report (Form PHMSA F 7100.2-1), Part A-D fields, one row per (report_year, operator, commodity) — the per-mile normalization denominator for phmsa_gas_transmission_incidents (join on operator_id + report_year). Same data.transportation.gov bulk-file route as phmsa_hazardous_liquid_mileage (catalog resource aemm-7pyq, the same multi-commodity "href" pointer; this fetches the Gas Transmission & Gathering file attachment). The source ZIP splits each year's annual report into ~12-14 "Part" CSVs (Part A-D, F-G, H, I, ... T); only the Part A-D file is ingested here, since it alone carries total_miles (Part D) and the HCA mileage summary (Part B) — the fields this warehouse needs. Confirmed identical column layout (154 cols) across 2017-2024. Unlike the gas distribution and hazardous-liquid annual reports, this form has no Part E installation-decade mileage breakdown at all (confirmed: no PARTE-prefixed columns anywhere in the 2024 zip), so miles_installed_ columns are not carried here. total_miles (PARTDTOTALMILES) is the sum of separately-reported transmission-only and gathering-only mileage (PARTDTTOTAL + PARTDGTOTAL, confirmed live), both also captured here since the two pipeline types differ operationally and are usually analyzed separately. Confirmed live: comma-only padding lines exist here too (1-16 per year, fewer than the other annual reports but the same defect), requiring the same OPERATOR_ID rowFilter. An operator can file more than one commodity row per year (Natural Gas, Landfill Gas, Hydrogen Gas, and assorted "Other Gas-" industrial gases), so report_number is the unique key, not operator_id + report_year. 2010-2016 exist only as whole-workbook XLSX files (one per year, sheet "GT AR Part A to D") rather than separate per-year Part-CSVs; a PhmsaGasTransmissionMileageHistoricalTransformer responseTransformer parses those years via POI and appends them to the 2017+ CSV text extractPattern already produces, so this table now covers 2010-present. Confirmed live: the 2010-2011 workbooks carry only 116 columns (vs. 154 for 2012+), but every target field this table ingests is present for the whole 2010-2016 span — the narrower layout only drops columns this table never used anyway.

Column	Type	Null	Description
`report_year`	integer	yes
`report_number`	string	no	PHMSA report number (PK) — an operator may file multiple commodity rows per year, so this is the unique key, not operator_id + report_year
`operator_id`	string	yes	PHMSA operator identifier — join key to phmsa_gas_transmission_incidents.operator_id
`operator_name`	string	yes
`operator_state_abbr`	string	yes	2-letter USPS state code of the operator's address
`commodity`	string	yes
`hca_miles_onshore`	double	yes	Miles in High Consequence Areas, onshore (Part B)
`hca_miles_offshore`	double	yes
`hca_miles_total`	double	yes
`total_transmission_miles`	double	yes	Transmission-only pipeline miles (Part D), excludes gathering lines
`total_gathering_miles`	double	yes	Gathering-only pipeline miles (Part D), excludes transmission lines
`total_miles`	double	yes	Total regulated pipeline miles operated, transmission + gathering combined (Part D) — the per-mile safety-rate denominator
`report_submission_type`	string	yes	e.g. INITIAL, AMENDED — later amended reports for the same (report_year, operator) supersede earlier ones; not deduplicated here

rail_service_performance · table¶

STB EP 724 Rail Service Data — weekly Class I railroad service-performance filings required under 49 CFR 1250 (BNSF, CN, CP, CPKC, CSXT, KCS, NS, UP). Long format: one row per (railroad, measure, sub_measure, report week) — average train speed (mph), terminal dwell time (hours), average cars on line, dwell time at origin for unit trains, trains held per day (by cause), cars in revenue service not moved in 48+ hours, grain car orders/ loadings/past-due, coal unit train loadings actual vs. planned, and carloads originated/received by commodity category — each measure carries its own units/scale in measure_units/measure_scale, so ALWAYS filter on measure_name_analytics (mixing measures in an aggregate mixes incompatible units, e.g. mph with hours with car counts). sub_measure's meaning depends on the measure: train type, terminal name, region, or commodity. Delta by year; dataLag 0 (year == effective_year, essentially no publish lag). Source: opendata.stb.gov/api/v2/catalog/datasets/ep724-public-data.

Column	Type	Null	Description
`railroad_mark`	string	no	Class I railroad AAR reporting mark (BNSF, CN, CP, CPKC, CSXT, KCS, NS, UP) — PK component
`railroad_name`	string	yes	Standardized railroad name
`measure_name_analytics`	string	no	Standardized snake_case measure identifier — PK component. The ~21 distinct measures carry incompatible units/scales (see measure_units/ measure_scale on the same row); enumerate with SELECT DISTINCT measure_name_analytics, measure_name_full, measure_units.
`measure_name_full`	string	yes	Human-readable measure name as filed (e.g. "Average Train Speed")
`data_element_num`	integer	yes	STB internal data element number for the measure
`sub_measure`	string	yes	Sub-classification as filed — PK component. Meaning depends on measure_name_analytics: train type (e.g. Grain, Intermodal) for speed / dwell-at-origin measures, terminal name for terminal dwell time, commodity for carloads, cause for train-holding measures.
`sub_measure_analytics`	string	yes	Standardized/normalized version of sub_measure
`fact_value_analytics`	double	yes	Standardized numeric value for the measure (NULL where the filed value was non-numeric, e.g. n/a)
`measure_units`	string	yes	Unit of measure (e.g. mph, hours, cars)
`measure_scale`	integer	yes	Scale factor applied to fact_value_analytics
`measure_confidentiality`	string	yes	Confidentiality designation (e.g. Public)
`report_period_start_date`	date	no	Start date of the weekly reporting period — PK component
`report_period_end_date`	date	yes	End date of the weekly reporting period
`report_year`	integer	yes	STB filing year (report_year_analytics; a report week can span into an adjacent calendar year)
`report_collection_frequency`	string	yes	Filing frequency (weekly)

airline_ontime_by_carrier_month · view¶

Monthly on-time summary by reporting carrier (nationwide, not by route): flight counts, average arrival delay, cancellation rate, and the share of departures delayed 15+ minutes (pct_dep_delayed_15). The headline punctuality rollup over airline_ontime.