🏥 `health`¶

Pharmaceutical safety and public health data. Phase 1 covers five openFDA tables: NDC product catalog, drug approvals, drug recalls, adverse events (FAERS), and device recalls. All sourced from api.fda.gov.

56 datasets · 498 columns

fda_ndc_products · table¶

FDA National Drug Code directory. Primary drug reference table; join hub for fda_drug_approvals (application_number), rxnorm_drugs (rxcui), and medicaid_drug_utilization (ndc = product_ndc). Covers all finished and unfinished drug products listed with FDA. labeler_name is unstructured (no per-labeler id — product_ndc is per-product, and many products share one labeler). ref.canonical_org_entity carries it as an already-resolved identity (fda_labeler_name, with an LEI when matched); join there on the name rather than re-running a fuzzy match against gleif_entities.legal_name by hand.

Column	Type	Null	Description
`product_ndc`	string	no	NDC product code (e.g. "24909-042") — PK
`product_ndc9`	string	yes	Normalized 9-digit product NDC (labeler[5]+product[4], zero-padded) derived from product_ndc. Join key to medicaid_drug_utilization.product_ndc9 — the raw hyphenated product_ndc and Medicaid's 11-digit package NDC never match directly.
`generic_name`	string	yes	INN/generic drug name
`generic_name_normalized`	string	yes	Join key to fda_drug_shortages.generic_name_normalized. This table's raw generic_name never includes dosage form, and joins combinations with "and" in source order; fda_drug_shortages embeds the dosage form and joins combinations with ";" in alphabetized order, so a raw or case-insensitive join matches only ~4% of shortage drugs. Both sides derive this column the same way (GenericNameNormalizer): uppercase, strip trailing dosage-form phrase, split combination components, strip each component's salt suffix, sort, rejoin.
`brand_name`	string	yes	Proprietary/brand name
`brand_name_base`	string	yes	Brand name without suffix
`labeler_name`	string	yes	Manufacturer or distributor name
`dosage_form`	string	yes	e.g. TABLET, INJECTION, OINTMENT
`route`	string	yes	e.g. ORAL, TOPICAL, INTRAVENOUS
`product_type`	string	yes	HUMAN OTC DRUG or HUMAN PRESCRIPTION DRUG
`marketing_category`	string	yes	NDA, ANDA, OTC MONOGRAPH DRUG, etc.
`marketing_start_date`	string	yes	YYYYMMDD
`marketing_end_date`	string	yes	YYYYMMDD; NULL means still actively marketed under this listing, not "unknown" (D-253). A distinct-labeler count meant to measure current market concentration should filter to marketing_end_date IS NULL — otherwise every repackager/relabeler and discontinued listing that ever existed for a generic drug counts as a current manufacturer, which is exactly backwards from the concentration the count is meant to show.
`listing_expiration_date`	string	yes	YYYYMMDD the labeler's annual NDC listing certification lapses; distinct from marketing_end_date (a discontinuation the labeler actively reported) — a lapsed listing with no marketing_end_date means the labeler simply stopped re-certifying, not that the product was discontinued.
`application_number`	string	yes	FK to fda_drug_approvals.application_number
`rxcui`	string	yes	RxNorm concept ID — FK to rxnorm_drugs.rxcui (Phase 3)
`finished`	boolean	yes	True if finished drug product (vs. bulk ingredient)

fda_drug_approvals · table¶

FDA drug approval applications (NDA, BLA, ANDA). One row per application. FK target for fda_ndc_products.application_number. sponsor_name is unstructured (no per-row id); ref.canonical_org_entity carries it as an already-resolved identity (fda_sponsor_name, with an LEI/SEC CIK when matched) — join there on the name rather than fuzzy-matching sponsor_name against sec company facts or ref.gleif_entities.legal_name by hand.

Column	Type	Null	Description
`application_number`	string	no	Application number e.g. NDA022369 — PK
`sponsor_name`	string	yes	Applicant/manufacturer — resolved identity at ref.canonical_org_entity.fda_sponsor_name
`brand_name`	string	yes	Brand or trade name of the drug product
`generic_name`	string	yes	Generic name(s) of the drug product
`product_type`	string	yes	Type of drug product (e.g. human prescription drug, human OTC drug)
`dosage_form`	string	yes	Dosage form of the drug (e.g. TABLET, SOLUTION FOR INJECTION)
`route`	string	yes	Route of administration of the drug product
`marketing_status`	string	yes	Prescription, OTC, Discontinued, etc.
`te_code`	string	yes	Therapeutic equivalence code
`latest_submission_type`	string	yes	ORIG (original) or SUPPL (supplement)
`latest_submission_status`	string	yes	AP = approved, TA = tentatively approved
`latest_submission_date`	string	yes	YYYYMMDD of the most recent submission action — a supplement (label change, manufacturing change, etc.), not the approval, for any application with post-approval activity. Use original_approval_date for the application's actual approval year.
`review_priority`	string	yes	STANDARD or PRIORITY
`original_approval_date`	string	yes	YYYYMMDD the application's ORIG submission reached status AP (approved) — the application's real approval date and the only usable year dimension for an approval cohort (D-251). NULL when the ORIG submission's own status isn't AP in this feed (e.g. tentative approval only) or wasn't found. openFDA's drugsfda endpoint carries no separate filing/received date anywhere in the record (confirmed live against NDA020702/Lipitor's own ORIG entry), so review time (submission-to-approval duration) is not computable from this source — only the approval date itself. See fda_drug_submissions for the full per-submission history this is derived from.

fda_drug_submissions · table¶

Full submission history per FDA drug application — one row per (application_number, submission_number). ORIG (submission_number 1, almost always) is the original application; SUPPL rows are post-approval supplements (label changes, manufacturing changes, new indications, etc.). An application's real approval date is its ORIG row's submission_status_date where submission_status = AP (also surfaced directly as fda_drug_approvals.original_approval_date). openFDA exposes no separate filing/received date anywhere in this record — only this status date — so review time (submission-to-approval duration) cannot be computed from this table or any other openFDA source; it is available only from FDA's own PDUFA performance reports. FK to fda_drug_approvals.application_number.

Column	Type	Null	Description
`application_number`	string	no	FK to fda_drug_approvals.application_number — PK part 1
`submission_number`	string	no	PK part 2 — ORIG is almost always "1"; SUPPL numbers increment per supplement
`submission_type`	string	yes	ORIG (original application) or SUPPL (post-approval supplement)
`submission_status`	string	yes	AP = approved, TA = tentatively approved
`submission_status_date`	string	yes	YYYYMMDD this submission reached submission_status
`submission_class_code`	string	yes	e.g. TYPE 1 (New Molecular Entity), EFFICACY, LABELING, MANUF (CMC)
`submission_class_code_description`	string	yes	Human-readable form of submission_class_code
`review_priority`	string	yes	STANDARD or PRIORITY

fda_drug_recalls · table¶

FDA drug enforcement (recall) actions, one row per recall_number with severity classification (Class I/II/III), status (Terminated/Ongoing/Pending), and voluntary vs. FDA-mandated origin. Joins to fda_ndc_products only via recalling_firm/labeler_name text match — no structural NDC column is present in this table. recalling_firm is unstructured (no per-row id). ref.canonical_org_entity carries it as an already-resolved identity (fda_drug_recalling_firm, with an LEI when matched, independently resolved from fda_ndc_products.labeler_name's own fda_labeler_name entry) — join there on the name rather than re-running a fuzzy match against gleif_entities.legal_name by hand.

Column	Type	Null	Description
`recall_number`	string	no	Recall identifier e.g. D-321-2016 — PK
`event_id`	string	yes	Numeric ID FDA assigns to a specific recall event for tracking
`status`	string	yes	Terminated, Ongoing, Pending
`classification`	string	yes	Class I (most severe), Class II, Class III
`voluntary_mandated`	string	yes	Who initiated the recall: firm-voluntary or FDA-mandated
`recalling_firm`	string	yes	Text FK to fda_ndc_products.labeler_name
`city`	string	yes	City in which the recalling firm is located
`state`	string	yes	FK to geo.state_ref.state_abbr
`country`	string	yes	Country in which the recalling firm is located
`product_description`	string	yes	Brief description of the product being recalled
`reason_for_recall`	string	yes	How the product is defective and violates FDA law
`product_quantity`	string	yes	Amount of defective product subject to recall
`distribution_pattern`	string	yes	General area of initial distribution (e.g. states, nationwide)
`code_info`	string	yes	Lot numbers, expiration dates
`recall_initiation_date`	string	yes	YYYYMMDD
`report_date`	string	yes	YYYYMMDD
`termination_date`	string	yes	YYYYMMDD; null if recall is ongoing

fda_drug_shortages · table¶

FDA Drug Shortages Database — one row per shortage episode (a given package_ndc can recur across multiple episodes over time, so the PK is package_ndc + initial_posting_date, not package_ndc alone). status/ availability/update_type track the episode's current lifecycle stage (Current/To Be Discontinued/Resolved). product_ndc/application_number join to fda_ndc_products/fda_drug_approvals; company_name/manufacturer_name are text-only (no structural FK, matching fda_drug_recalls' recalling_firm). A handful of exact-duplicate (package_ndc, initial_posting_date) rows exist in FDA's own source feed — see the DQ T6 warn, not a pipeline defect.

Column	Type	Null	Description
`package_ndc`	string	no	Package-level NDC for this shortage episode — PK part 1
`initial_posting_date`	date	no	Date this shortage episode was first posted — PK part 2 (parsed from the source's own MM/DD/YYYY presentation)
`posting_year`	integer	yes	Calendar year of initial_posting_date — avoids a date function for the common "shortages per year" query
`generic_name`	string	yes
`generic_name_normalized`	string	yes	Join key to fda_ndc_products.generic_name_normalized. This table's raw generic_name embeds the dosage form as a trailing word/phrase (e.g. "Atropine Sulfate Injection", even though dosage_form is already a separate column) and joins combinations with ";" in alphabetized order; fda_ndc_products never includes dosage form and joins combinations with "and" in source order, so a raw or case-insensitive join matches only ~4% of shortage drugs. Both sides derive this column the same way (GenericNameNormalizer): uppercase, strip trailing dosage-form phrase, split combination components, strip each component's salt suffix, sort, rejoin. Tested against the full 245-name shortage list: recovers 226 (92.2%), up from 9 raw/case-insensitive.
`update_type`	string	yes	e.g. New, Revised, Reverified
`update_date`	date	yes	Date of the most recent update to this episode (parsed from the source's own MM/DD/YYYY presentation)
`status`	string	yes	Current, To Be Discontinued, Resolved
`availability`	string	yes
`discontinued_date`	date	yes	Null unless status is a discontinuation (parsed from the source's own MM/DD/YYYY presentation)
`dosage_form`	string	yes
`presentation`	string	yes	Strength/form/package description
`therapeutic_category`	string	yes
`company_name`	string	yes
`contact_info`	string	yes
`related_info`	string	yes
`brand_name`	string	yes
`manufacturer_name`	string	yes
`product_ndc`	string	yes	FK to fda_ndc_products.product_ndc
`application_number`	string	yes	FK to fda_drug_approvals application_number
`route`	string	yes
`substance_name`	string	yes

fda_drug_shortages_history · table¶

Point-in-time history of FDA's drug shortage list, reconstructed from Wayback Machine captures of accessdata.fda.gov's own CSV export (the live-feed fda_drug_shortages table cannot answer "what did the shortage list look like on date X" — FDA purges resolved shortages ~6 months after resolution). One row per (snapshot_date, generic_name, presentation, initial_posting_date); the same generic_name recurs across snapshots as its episode moves through Current -> To Be Discontinued -> Resolved, and multiple presentations of the same generic_name coexist within one snapshot. snapshot_date is the Wayback capture date, not the shortage's own update/change date (both are carried as separate columns). status values are canonicalized (the source's own export mixes "To be Discontinued"/"To Be Discontinued" casing across snapshots — both map to "To Be Discontinued" here). The captured timestamp set is fixed (~99 Wayback captures spanning Oct 2019 - Jul 2026 as of onboarding), not an open-ended crawl; new captures require appending to the snapshot_ts dimension's literal value list.

Column	Type	Null	Description
`snapshot_date`	string	no	Wayback capture date (yyyy-MM-dd), derived from the capture timestamp — PK part 1
`generic_name`	string	no	PK part 2
`presentation`	string	no	Strength/form/package description — PK part 3 (disambiguates multiple presentations of one generic_name)
`initial_posting_date`	date	yes	Date this shortage episode was first posted — PK part 4 (parsed from the source's own MM/DD/YYYY presentation)
`posting_year`	integer	yes	Calendar year of initial_posting_date — avoids a date function for the common "shortages per year" query
`company_name`	string	yes
`contact_info`	string	yes
`update_type`	string	yes	e.g. New, Revised, Reverified
`update_date`	date	yes	Date of the most recent update to this episode as of this snapshot (parsed from the source's own MM/DD/YYYY presentation)
`availability`	string	yes
`related_info`	string	yes
`resolved_note`	string	yes
`reason_for_shortage`	string	yes
`therapeutic_category`	string	yes
`status`	string	yes	Canonicalized to Current, To Be Discontinued, or Resolved
`change_date`	date	yes	Parsed from the source's own MM/DD/YYYY presentation
`discontinued_date`	date	yes	Null unless status is a discontinuation (parsed from the source's own MM/DD/YYYY presentation)
`generic_name_note`	string	yes
`generic_name_link`	string	yes
`company_info_link`	string	yes	Rarely populated in the source export — legitimately sparse, not a pipeline defect (DQ T4/T5 warn)
`availability_link`	string	yes	Rarely populated in the source export — legitimately sparse, not a pipeline defect (DQ T4/T5 warn)
`related_info_link`	string	yes
`resolved_note_link`	string	yes	Rarely populated in the source export — legitimately sparse, not a pipeline defect (DQ T4/T5 warn)
`discontinued_note_link`	string	yes

chr_premature_death · table¶

Premature Death (years of potential life lost before age 75, per 100,000 population) at nation/state/county grain, from County Health Rankings & Roadmaps' national analytic-data file (University of Wisconsin Population Health Institute / RWJF; input mortality data is NCHS's own multi-year detailed-death files). This is the current-era, county-grain mortality-outcome series CDC WONDER cannot provide at all: WONDER's NVSS underlying-cause- of-death API rejects sub-national location grouping entirely (confirmed live; see cdc_wonder_cause_of_death's comment), and CDC's own county-grain product (PLACES) covers disease prevalence and risk factors, not mortality. Premature Death (YPLL) is a truncated (deaths before age 75) measure, not a full all-ages age-adjusted rate — appropriate for relative county comparisons and trend questions, not a substitute for cdc_mortality's all-ages state-level rate. geo_level distinguishes nation/state/county rows sharing this one table (see the rollup-vs-leaf convention used elsewhere in this corpus) — filter to geo_level='county' rather than summing across levels.

Column	Type	Null	Description
`geo_level`	string	no	'nation', 'state', or 'county' — filter, don't sum across levels
`state_fips`	string	yes	2-digit state FIPS ('00' for the national row)
`county_fips`	string	yes	3-digit county FIPS ('000' for state/nation rows)
`fips_code`	string	yes	5-digit combined FIPS code
`state_abbr`	string	yes	FK to geo.state_ref.state_abbr (nation row carries 'US')
`name`	string	yes	County, state, or "United States" name
`release_year`	integer	yes	CHR annual data-release year (the underlying mortality data lags this by several years — see CHR's own methodology)
`premature_death_rate`	double	yes	Years of potential life lost before age 75, per 100,000 population (age-adjusted)
`premature_death_numerator`	double	yes	Total years of potential life lost
`premature_death_denominator`	double	yes	Population denominator
`ci_low`	double	yes
`ci_high`	double	yes
`data_flag`	string	yes	0 = no flag, 1 = unreliable (small numerator), 2 = suppressed
`rate_aian`	double	yes	Premature death rate, American Indian/Alaska Native subpopulation
`rate_asian`	double	yes
`rate_black`	double	yes
`rate_hispanic`	double	yes
`rate_white`	double	yes
`rate_nhopi`	double	yes	Native Hawaiian/Other Pacific Islander subpopulation

chr_premature_death_trends · table¶

Premature Death (years of potential life lost before age 75, per 100,000 population) historical time series at nation/state/county grain, from County Health Rankings & Roadmaps' own "Trends" data release — a single file spanning 46 yearspans, 1997-1999 through 2023 (verified live 2026-09-11: Story County IA numerator/rate values for the earliest yearspans are internally consistent and plausible). Complements chr_premature_death (the current single-vintage release, which also carries race/ethnicity-stratified rates the trends file does not) with the long-run history that table cannot provide — chr_premature_death has no year dimension and only ever holds the latest release. Sourced from the same CHR "Trends" CSV used for every CHR measure (chr_trends_csv_{release_year}.csv); this table is scoped to Premature Death (measureid=1) via source.rowFilter — the file's other ~14 measures are a real, documented follow-on, not ingested here. yearspan is a rolling 3-year window through the mid-2010s, then single calendar years in recent releases; period_end_year (the span's last year) is provided for time-ordering and joins. geo_level distinguishes nation/state/county rows sharing this one table — filter to geo_level='county' rather than summing across levels. Joins to geo.counties via fips_code (county rows only).

Column	Type	Null	Description
`geo_level`	string	no	'nation', 'state', or 'county' — filter, don't sum across levels
`state_fips`	string	yes	2-digit state FIPS ('00' for the national row)
`county_fips`	string	yes	3-digit county FIPS ('000' for state/nation rows)
`fips_code`	string	yes	5-digit combined FIPS code
`state_abbr`	string	yes	FK to geo.state_ref.state_abbr (nation row carries 'US')
`name`	string	yes	County, state, or "United States" name
`yearspan`	string	yes	Raw CHR yearspan as published — a 3-year rolling window (e.g. "1997-1999") for older releases, a single calendar year for recent ones
`period_end_year`	integer	yes	Last year of yearspan (or the year itself, for a single-year span) — for time-ordering and joins
`premature_death_numerator`	double	yes	Total years of potential life lost
`premature_death_denominator`	double	yes	Population denominator; null for a yearspan CHR did not publish a denominator for (not fabricated)
`premature_death_rate`	double	yes	Years of potential life lost before age 75, per 100,000 population (age-adjusted)
`ci_low`	double	yes
`ci_high`	double	yes
`chr_release_year`	integer	yes	CHR release year that last restated this yearspan's value; null when never restated after first publication
`trend_break_flag`	integer	yes	CHR's own flag marking a methodology break at this yearspan (non-null only where CHR flagged one)
`differ_flag`	string	yes	CHR's own data-differs annotation, verbatim (sparse; domain not independently documented by CHR)

fda_adverse_events · table¶

FDA Adverse Event Reporting System (FAERS). One row per report; reactions are comma-joined from the MedDRA reaction list. Ingested from openFDA bulk partition ZIPs on download.open.fda.gov (no API key, no pagination cap) — one quarter-partition file per request, enumerated from download.json and filtered by GOVDATA_START_YEAR. Joins to fda_ndc_products on primary_drug name (text).

Column	Type	Null	Description
`safety_report_id`	string	no	FAERS safety report ID — PK
`receive_date`	string	yes	YYYYMMDD date report was received
`receive_year`	string	yes	4-digit year derived from receive_date
`serious`	string	yes	1 = serious adverse event
`serious_death`	string	yes	1 = outcome was death
`patient_age`	string	yes	Age of the patient when the event first occurred (patientonsetage)
`patient_age_unit`	string	yes	801 = years
`patient_sex`	string	yes	1 = male, 2 = female
`primary_drug`	string	yes	First suspect drug name — text FK to fda_ndc_products.generic_name
`drug_indication`	string	yes	Reported indication for the drug's use (drugindication)
`drug_route`	string	yes	Drug's route of administration (drugadministrationroute)
`reactions`	string	yes	Comma-joined MedDRA reaction preferred terms
`reporter_country`	string	yes	Country from which the report was submitted

fda_device_recalls · table¶

FDA medical device recall (enforcement) actions, one row per cfres_id. Complementary to fda_drug_recalls, but carries recall_status (Terminated/Ongoing) and root_cause_description instead of a Class I/II/III severity field — this table has no classification column. Includes product_code (device classification code) and k_numbers (510(k) premarket notification numbers). recalling_firm is unstructured (no per-row id). ref.canonical_org_entity carries it as an already-resolved identity (fda_device_recalling_firm, with an LEI when matched — the structural twin of fda_drug_recalls' fda_drug_recalling_firm); join there on the name rather than re-running a fuzzy match against gleif_entities.legal_name by hand.

Column	Type	Null	Description
`cfres_id`	string	no	CFRES recall identifier — PK
`product_res_number`	string	yes	e.g. Z-0001-04
`recall_status`	string	yes	Terminated, Ongoing
`product_code`	string	yes	FDA device product classification code
`k_numbers`	string	yes	510(k) premarket notification number
`recalling_firm`	string	yes	Firm initiating the recall or responsible for the recalled device
`city`	string	yes	City in which the recalling firm is located
`state`	string	yes	FK to geo.state_ref.state_abbr
`product_description`	string	yes	Brief description of the device being recalled
`reason_for_recall`	string	yes	How the device is defective and violates FDA law
`root_cause_description`	string	yes	Design, Manufacturing, Labeling, Other, etc.
`action`	string	yes	Corrective action taken
`product_quantity`	string	yes	Amount of defective product subject to recall
`distribution_pattern`	string	yes	General area of initial distribution (e.g. states, nationwide)
`event_date_initiated`	string	yes	YYYY-MM-DD
`event_date_terminated`	string	yes	YYYY-MM-DD; null if ongoing

clinical_trials · table¶

Clinical trial studies from clinicaltrials.gov, one row per nct_id (PK) with overall_status, study_type, phase, enrollment_count, lead_sponsor, funder_type, and start/primary-completion/completion/update dates. Restricted by default to studies with a last-update-post-date on or after 2020-01-01. Joins to clinical_trial_conditions and clinical_trial_interventions (both on nct_id); see the trial_full view for a pre-joined denormalized version. To reach a trial's lead sponsor's cross-schema identity FROM another schema rather than by name, join ref.canonical_org_entity on clinical_trials_nct_id = nct_id — that table carries one foreign key per source, so the sponsor's LEI/SEC CIK and this trial's nct_id sit in the same row and the join is exact. Matching lead_sponsor as text finds the wrong entities and misses sponsors filing under a differently-worded name.

Column	Type	Null	Description
`nct_id`	string	no	NCT identifier — PK
`brief_title`	string	yes	Short study title written in lay language
`overall_status`	string	yes	ACTIVE_NOT_RECRUITING, ENROLLING_BY_INVITATION, etc.
`study_type`	string	yes	INTERVENTIONAL, OBSERVATIONAL, EXPANDED_ACCESS
`phase`	string	yes	Phase 1, Phase 2, Phase 3, Phase 4, N/A
`enrollment_count`	int	yes	Number of participants enrolled (actual or anticipated)
`lead_sponsor`	string	yes	Entity that initiates and has authority/control over the study
`funder_type`	string	yes	INDUSTRY, NIH, OTHER_GOV, FED, INDIV, NETWORK, RRCF, STATE, UNKNOWN
`start_date`	string	yes	YYYY-MM-DD
`primary_completion_date`	string	yes	YYYY-MM-DD
`completion_date`	string	yes	YYYY-MM-DD
`first_submit_date`	string	yes	YYYYMMDD
`last_update_date`	string	yes	YYYYMMDD
`brief_summary`	string	yes	Truncated to 2000 chars
`has_results`	boolean	yes	Whether results have been posted to the study's ClinicalTrials.gov results database (the "reported results" flag, distinct from overall_status/completion dates — a study can be COMPLETED with no results ever posted). Sourced from the top-level `hasResults` field on the API v2 study element (sibling of protocolSection, not nested under it).

clinical_trial_conditions · table¶

One row per condition per clinical trial. Composite PK: (nct_id, condition_name). FK: nct_id → clinical_trials.nct_id.

Column	Type	Null	Description
`nct_id`	string	no	NCT identifier — FK to clinical_trials.nct_id
`condition_name`	string	no	Condition/disease name — composite PK
`type`	string	no	Always "clinical_trial_conditions"

clinical_trial_interventions · table¶

One row per intervention per clinical trial. Composite PK: (nct_id, intervention_name). FK: nct_id → clinical_trials.nct_id.

Column	Type	Null	Description
`nct_id`	string	no	NCT identifier — FK to clinical_trials.nct_id
`intervention_type`	string	yes	BEHAVIORAL, BIOLOGICAL, DEVICE, DIETARY_SUPPLEMENT, DRUG, GENETIC, PROCEDURE, RADIATION, OTHER
`intervention_name`	string	no	Intervention name — composite PK
`description`	string	yes	Truncated to 2000 chars
`type`	string	no	Always "clinical_trial_interventions"

cdc_covid_vaccinations · table¶

COVID-19 vaccination cumulative counts and population percentages (dose 1, primary series complete, booster, bivalent booster) at the US national level by report date and demographic_category (age group, race/ethnicity, or overall) — no state or county breakdown. Warning: source data frozen at 2023-05-10; no updates since (any sinceDate past that returns zero rows from CDC).

Column	Type	Null	Description
`date`	string	yes	Report date (YYYY-MM-DD)
`demographic_category`	string	yes	Age group, race/ethnicity, or overall
`dose1_administered`	string	yes	Cumulative count of people with at least one dose administered
`dose1_pct_us`	string	yes	Percentage of US population
`series_complete_count`	string	yes	Cumulative count of people who completed the primary series
`series_complete_pct`	string	yes	Percent of population that completed the primary series
`booster_count`	string	yes	Cumulative count of people who received a booster dose
`booster_pct`	string	yes	Booster recipients as a percent of the vaccinated age group
`bivalent_booster_count`	string	yes	Cumulative count of people who received a bivalent (updated) booster
`bivalent_booster_pct`	string	yes	Percent of the age-group population with a bivalent booster

cms_hospital_quality · table¶

Medicare/Medicaid hospital quality ratings and metrics, one row per facility (PK: facility_id) with location (city/state/zip/county), hospital_type, hospital_ownership, overall_rating (1-5 stars), and counts of mortality/safety/ readmission measures better or worse than the national benchmark. Facility-level; see hospital_quality_by_state for a state-level aggregate and hospital_county_summary for a county-level aggregate.

Column	Type	Null	Description
`facility_id`	string	no	CMS facility ID — PK
`facility_name`	string	yes	Name of the hospital facility
`address`	string	yes	Hospital street address
`city`	string	yes	City or town where the hospital is located
`state`	string	yes	FK to geo.state_ref.state_abbr
`zip_code`	string	yes	Hospital ZIP code
`county`	string	yes	County or parish where the hospital is located
`hospital_type`	string	yes	Critical Access, Acute Care, Children's, Psychiatric, etc.
`hospital_ownership`	string	yes	Government, Private non-profit, Physician-owned, etc.
`emergency_services`	string	yes	Whether the hospital provides emergency services (Yes/No)
`overall_rating`	string	yes	1-5 star rating
`mort_measures_better`	string	yes	Count of mortality measures where hospital is better than national
`mort_measures_worse`	string	yes	Count of mortality measures where hospital is worse than national
`safety_measures_better`	string	yes	Count of safety-of-care measures better than the national benchmark
`safety_measures_worse`	string	yes	Count of safety-of-care measures worse than the national benchmark
`readm_measures_better`	string	yes	Readmission measures better than national
`readm_measures_worse`	string	yes	Readmission measures worse than national
`patient_exp_rating`	string	yes	Patient experience group measure count
`birthing_friendly`	string	yes	Meets criteria for birthing-friendly designation
`type`	string	no	Always "cms_hospital_quality"

cms_pos_facilities · table¶

CMS Provider of Services File (POS) — identity, location, and bed-count fields for every certified hospital and non-hospital facility, one row per provider_number (CMS Certification Number, unique). The current-quarter snapshot only (whole file replaced each release, per CMS's own accrualPeriodicity of R/P3M — no history retained upstream to backfill). provider_category_code is CMS's PRVDR_CTGRY_CD: 01=hospital, 06=psychiatric/behavioral residential treatment, 12=clinic, 19=community mental health center, 21=other (verified live against the 2026-Q2 file's actual category population; CMS's own layout PDF, linked from the data.json dataset's describedBy field, has the authoritative full code list). bed_count is the facility's total certified beds; the curated sub-type columns (icf_iid/medicaid_nf/medicare_snf/hospice/ rehab/psych_unit/rehab_unit) break that total down by care type — most are null/0 for non-bed facility categories (clinics, mental health centers). county_fips is derived by concatenating FIPS_STATE_CD + FIPS_CNTY_CD (only when both are present at their expected 2-digit/3-digit widths). Source: data.cms.gov/data.json, dataset "Provider of Services File - Quality Improvement and Evaluation System". CORRECTED 2026-09-11: despite this being a "current snapshot" release, CMS does NOT purge terminated facilities from it — verified live (Hahnemann University Hospital's real 2019-09-06 closure, PRVDR_NUM 390290, is present in the current file alongside an unrelated 1998 predecessor-CCN termination). This file therefore already carries facility-level termination history; see cms_pos_termination_history for the termination-code/date columns this table does not carry.

Column	Type	Null	Description
`provider_number`	string	no	CMS Certification Number (CCN) — PK
`provider_category_code`	string	yes	PRVDR_CTGRY_CD (01=hospital, 06=psych/behavioral residential, 12=clinic, 19=community mental health, 21=other)
`provider_subtype_code`	string	yes	PRVDR_CTGRY_SBTYP_CD, refines provider_category_code
`facility_name`	string	yes	FAC_NAME
`city`	string	yes	CITY_NAME
`state`	string	yes	STATE_CD (2-letter USPS; FK to geo.state_ref)
`zip_code`	string	yes	ZIP_CD
`state_fips`	string	yes	FIPS_STATE_CD (2-digit; FK to geo.state_ref)
`county_fips`	string	yes	Derived 5-digit county FIPS (FIPS_STATE_CD + FIPS_CNTY_CD; FK to geo.counties)
`ownership_code`	string	yes	GNRL_CNTL_TYPE_CD (general control/ownership type)
`certification_date`	string	yes	CRTFCTN_DT (YYYYMMDD)
`bed_count`	long	yes	BED_CNT — total certified beds
`certified_bed_count`	long	yes	CRTFD_BED_CNT
`icf_iid_bed_count`	long	yes	ICFIID_BED_CNT — intermediate care facility for intellectual disabilities
`medicaid_nf_bed_count`	long	yes	MDCD_NF_BED_CNT — Medicaid-certified nursing facility beds
`medicare_snf_bed_count`	long	yes	MDCR_SNF_BED_CNT — Medicare-certified skilled nursing facility beds
`medicare_medicaid_snf_bed_count`	long	yes	MDCR_MDCD_SNF_BED_CNT — dually-certified SNF beds
`hospice_bed_count`	long	yes	HOSPC_BED_CNT
`rehab_bed_count`	long	yes	REHAB_BED_CNT
`psych_unit_bed_count`	long	yes	PSYCH_UNIT_BED_CNT — psychiatric unit within a non-psychiatric hospital
`rehab_unit_bed_count`	long	yes	REHAB_UNIT_BED_CNT — rehab unit within a non-rehab hospital
`type`	string	no	Always "cms_pos_facilities"

cms_pos_termination_history · table¶

Hospital/facility closure and termination events from CMS's Provider of Services File (POS) — one row per provider_number (CCN), same source dataset as cms_pos_facilities but scoped to the termination-code/date fields that table does not carry. Despite CMS presenting this as a "current" release, terminated CCNs are NOT purged from it: verified live 2026-09-11 against Hahnemann University Hospital's real closure — PRVDR_NUM 390290 shows termination_code='01', termination_date=2019-09-06, matching its actual September 2019 shutdown; a separate, older PRVDR_NUM 390051 for the same facility name shows an unrelated 1998 termination_code='04' (change-of- ownership) predecessor record. One CCN can therefore have zero, one, or (rarely, across a CHOW/successor chain) multiple related termination rows — cross_ref_provider_number links a terminated CCN to its predecessor/successor number when CMS recorded one. is_terminated is derived from termination_date being non-null, not from termination_code alone (a blank code with a real date, or vice versa, both count as a real event; neither is fabricated when absent). psych_unit/rehab_unit termination fields capture a sub-unit closure that didn't end the whole facility's participation. Joins to cms_pos_facilities via provider_number.

Column	Type	Null	Description
`provider_number`	string	no	CMS Certification Number (CCN) — PK; FK to cms_pos_facilities.provider_number
`facility_name`	string	yes	FAC_NAME
`provider_category_code`	string	yes	PRVDR_CTGRY_CD (01=hospital, 06=psych/behavioral residential, 12=clinic, 19=community mental health, 21=other)
`state`	string	yes	STATE_CD (2-letter USPS; FK to geo.state_ref)
`state_fips`	string	yes	FIPS_STATE_CD (2-digit; FK to geo.state_ref)
`county_fips`	string	yes	Derived 5-digit county FIPS (FK to geo.counties); null unless both source fields are present at their expected width
`cross_ref_provider_number`	string	yes	Predecessor/successor CCN CMS recorded for this facility (e.g. across a change-of-ownership), when one exists
`original_participation_date`	date	yes	Date this CCN first began Medicare participation (ORGNL_PRTCPTN_DT)
`certification_action_type_code`	string	yes	CRTFCTN_ACTN_TYPE_CD — CMS's own certification-action code
`termination_code`	string	yes	PGM_TRMNTN_CD — CMS's termination-reason code; null when the facility has never been terminated
`termination_date`	date	yes	Real termination/closure date (TRMNTN_EXPRTN_DT), verified live against Hahnemann University Hospital's actual 2019-09-06 closure
`is_terminated`	boolean	no	Derived from termination_date being present — the primary column for "has this facility closed" questions
`psych_unit_termination_code`	string	yes	PSYCH_UNIT_TRMNTN_CD — termination of a psychiatric sub-unit without the whole facility closing
`psych_unit_termination_date`	date	yes
`rehab_unit_termination_code`	string	yes	REHAB_UNIT_TRMNTN_CD — termination of a rehab sub-unit without the whole facility closing
`rehab_unit_termination_date`	date	yes

hospital_cost_report_financials · table¶

CMS Hospital Provider Cost Report (HCRIS) financials — one row per hospital per cost-reporting fiscal year, from CMS's own pre-aggregated extract (no raw HOSP-10 worksheet join needed; CMS already did it). Current snapshot only (FY2023, ~6,100 hospitals, verified live 2026-09-11) — CMS replaces this dataset in place with each year's newly finalized cost reports, so there is no per-year id to backfill history from; a prior year's data is not retrievable through this API once superseded. Cross-checked live: Mayo Clinic Hospital Rochester (CCN 240010) shows Net Patient Revenue ~$3.70B, Net Income ~$1.24B for FY2023 — plausible for a major academic medical center. Financial fields are as CMS reports them (a facility with a fiscal year spanning parts of two calendar years is assigned to fiscal_year by its Fiscal Year End Date). Joins to cms_pos_facilities/cms_pos_termination_history via provider_number (CCN) for a real payer-mix-vs-closure-risk analysis — the pairing ops#186 originally asked for.

Column	Type	Null	Description
`report_record_number`	string	yes	CMS cost-report record number (rpt_rec_num) — not itself a stable cross-year key
`provider_number`	string	no	CMS Certification Number (CCN); FK to cms_pos_facilities.provider_number
`hospital_name`	string	yes
`street_address`	string	yes
`city`	string	yes
`state`	string	yes	FK to geo.state_ref.state_abbr
`zip_code`	string	yes
`county_name`	string	yes	County name as reported (not a FIPS code — join cms_pos_facilities.county_fips via provider_number instead)
`cbsa_number`	string	yes
`rural_urban`	string	yes	'R' rural, 'U' urban
`facility_type`	string	yes
`provider_type`	string	yes
`ownership_code`	string	yes
`fiscal_year_begin_date`	date	yes
`fiscal_year_end_date`	date	yes
`fiscal_year`	integer	yes	Calendar year of Fiscal Year End Date — this table's own "year" for grouping/joins
`fte_employees`	double	yes
`number_of_beds`	integer	yes
`cost_of_charity_care`	double	yes
`total_bad_debt_expense`	double	yes
`cost_of_uncompensated_care`	double	yes
`total_unreimbursed_uncompensated_care`	double	yes
`total_costs`	double	yes
`inpatient_revenue`	double	yes
`outpatient_revenue`	double	yes
`total_patient_revenue`	double	yes
`net_patient_revenue`	double	yes	Net patient revenue (gross patient revenue less contractual allowances/discounts) — the ticket's "payer-mix revenue" figure
`net_income_from_patient_services`	double	yes
`total_other_income`	double	yes
`total_income`	double	yes
`net_income`	double	yes	Bottom-line net income — the ticket's "total margin" figure (as a dollar amount; divide by total_income for a margin ratio)
`net_revenue_from_medicaid`	double	yes	Payer-mix component — Medicaid net revenue
`medicaid_charges`	double	yes
`net_revenue_from_chip`	double	yes
`chip_charges`	double	yes

cms_nursing_home · table¶

CMS Provider Data Catalog "Nursing Homes including Rehab Services — Provider Information" (dataset 4pq5-n9py), one row per facility (PK: ccn — CMS Certification Number). Carries staffing hours-per-resident-day (nurse aide, LPN, RN, total), star ratings (overall/health inspection/staffing/QM), health -deficiency counts and score from the facility's most recent standard survey, and penalty/fine counts together in one file — a staffing-vs-deficiency question needs no second CMS dataset joined in. Complements cms_hospital_quality (acute-care hospitals) and cms_pos_facilities (all certified facility types, identity/bed-count only, no ratings or staffing) — this table is the only one in the corpus carrying skilled-nursing-facility staffing and deficiency detail. Source: data.cms.gov/provider-data/dataset/4pq5-n9py.

Column	Type	Null	Description
`ccn`	string	no	CMS Certification Number — PK
`provider_name`	string	yes	Facility legal/doing-business-as name
`address`	string	yes	Facility street address
`city`	string	yes	City or town
`state`	string	yes	FK to geo.state_ref.state_abbr
`zip_code`	string	yes	ZIP code
`county`	string	yes	County or parish
`ownership_type`	string	yes	For profit - Corporation, Non profit, Government, etc.
`certified_beds`	string	yes	Total certified beds
`avg_residents_per_day`	string	yes	Average daily census
`overall_rating`	string	yes	1-5 star overall rating
`health_inspection_rating`	string	yes	1-5 star health inspection rating
`qm_rating`	string	yes	1-5 star quality-measure rating
`staffing_rating`	string	yes	1-5 star staffing rating
`nurse_aide_hours_per_resident_day`	string	yes	Reported nurse aide staffing hours per resident per day
`lpn_hours_per_resident_day`	string	yes	Reported LPN staffing hours per resident per day
`rn_hours_per_resident_day`	string	yes	Reported RN staffing hours per resident per day
`total_nurse_hours_per_resident_day`	string	yes	Reported total nurse staffing hours per resident per day (nurse aide + LPN + RN)
`nursing_staff_turnover_pct`	string	yes	Total nursing staff turnover (percent)
`rn_turnover_pct`	string	yes	Registered nurse turnover (percent)
`total_health_deficiencies`	string	yes	Total number of health deficiencies cited on the most recent standard survey (rating cycle 1)
`health_deficiency_score`	string	yes	Weighted health deficiency score for the most recent standard survey (rating cycle 1)
`number_of_fines`	string	yes	Count of fines issued
`total_fines_dollars`	string	yes	Total dollar amount of fines issued
`number_of_payment_denials`	string	yes	Count of payment-denial penalties
`total_number_of_penalties`	string	yes	Total count of all penalty types (fines + payment denials + other)
`latitude`	string	yes	Facility latitude
`longitude`	string	yes	Facility longitude
`type`	string	no	Always "cms_nursing_home"

ahrf_physician_supply · table¶

HRSA Area Health Resources File (AHRF), county-level release — one row per U.S. county (fips_st_cnty; 3,235 counties/county-equivalents in the 2024-2025 release, including territories). AHRF ships raw physician/population COUNTS only — no per-100k rate column exists upstream — so primary_care_physicians_per_100k and total_active_physicians_per_100k are computed here from the matched count and population columns. primary_care_physicians is HRSA's "Phys, Primary Care, Patient Care" measure: non-federal MD+DO physicians in patient care, excluding hospital residents and physicians aged 75+, sourced from the AMA Physician Masterfile. total_active_physicians is all non-federal active MDs plus DOs (all specialties, not primary-care-filtered). AHRF is a wide file — each variable is a family of year-suffixed columns (e.g. phys_nf_prim_care_pc_exc_rsdt_23, phys_nf_prim_care_pc_exc_rsdt_22, ...) rather than one row per year, and each annual release adds a newest-year column while keeping older ones. AhrfPhysicianSupplyTransformer scans the header at load time for the highest-numbered suffix present and records which data year it picked in physician_data_year / population_data_year — so a stale suffix never silently ships; the years advance automatically as HRSA adds columns in future releases. rural_urban_code is the USDA ERS Rural-Urban Continuum Code (1-9; lower = more urban), useful for physician-shortage/rural-access analysis alongside the supply counts. Source: data.hrsa.gov/data/download, "Area Health Resources Files" county CSV distribution.

Column	Type	Null	Description
`county_fips`	string	no	5-digit county FIPS (fips_st_cnty) — PK
`state_fips`	string	yes	fips_st (2-digit; FK to geo.state_ref)
`state_abbr`	string	yes	st_name_abbrev (2-letter USPS; FK to geo.state_ref)
`state_name`	string	yes	st_name
`county_name`	string	yes	cnty_name
`rural_urban_code`	string	yes	rural_urban_contnm_YY — USDA ERS Rural-Urban Continuum Code (1-9; lower codes are more urban/metro), most-recent year available in this release
`population_estimate`	long	yes	popn_est_YY — Census county population estimate, most-recent year available
`population_data_year`	integer	yes	The YY this release's population_estimate reflects (e.g. 2024)
`primary_care_physicians`	long	yes	phys_nf_prim_care_pc_exc_rsdt_YY — non-federal MD+DO primary care physicians in patient care, excluding hospital residents and physicians aged 75+ (AMA Physician Masterfile), most-recent year available
`physician_data_year`	integer	yes	The YY this release's primary_care_physicians / total_active_physicians reflect (e.g. 2023)
`primary_care_physicians_per_100k`	double	yes	Computed — primary_care_physicians / population_estimate * 100000
`total_active_physicians`	long	yes	Computed sum of md_nf_activ_YY + do_nf_activ_YY (total non-federal active MDs and DOs, all specialties — not primary-care-filtered) for physician_data_year
`total_active_physicians_per_100k`	double	yes	Computed — total_active_physicians / population_estimate * 100000
`type`	string	no	Always "ahrf_physician_supply"

medicaid_drug_utilization · table¶

Medicaid drug utilization and reimbursement by state, drug (NDC), year/quarter, and utilization_type (FFSU fee-for-service vs. MCOU managed care). Composite PK: (state, ndc, year, quarter, utilization_type). Default dataset is 2023 data.

Column	Type	Null	Description
`state`	string	no	State abbreviation — composite PK
`ndc`	string	no	National Drug Code (11-digit package NDC) — composite PK
`product_ndc9`	string	yes	Normalized 9-digit product NDC (package segment dropped, zero-padded to labeler[5]+product[4]) derived from ndc. FK/join key to fda_ndc_products.product_ndc9.
`year`	string	no	Calendar year — composite PK
`quarter`	string	no	Q1, Q2, Q3, Q4 — composite PK
`utilization_type`	string	yes	Record type: FFSU (fee-for-service) or MCOU (managed care)
`product_name`	string	yes	FDA product name (first ~10 characters as listed with FDA)
`labeler_code`	string	yes	First NDC segment identifying the manufacturer/labeler
`units_reimbursed`	string	yes	Units of the NDC reimbursed (FFS) or dispensed (MCO) in the period
`number_of_prescriptions`	string	yes	Outpatient Rx claims reimbursed (FFS) or dispensed (MCO)
`total_amount_reimbursed`	string	yes	Total reimbursement in dollars
`medicaid_amount_reimbursed`	string	yes	Medicaid portion of reimbursement
`non_medicaid_amount_reimbursed`	string	yes	Non-Medicaid portion (other payers)
`suppression_used`	string	yes	true if data suppressed due to low counts

cdc_mortality · table¶

CDC/NCHS mortality statistics at the state level (annual data also includes a United States total row). Unions annual (bi63-dtpu: NCHS leading/113-cause deaths and age-adjusted rate per 100,000, 2000 US standard population, 1999-2017) and weekly provisional data (source_type=weekly) from two Socrata vintages of the same "AH Weekly Death Counts by State and Select Causes" series: 3yf8-kanr (no COVID columns; loaded here from 2014 through 2019, not just 2018-2019 as its nickname might suggest) and muzy-jte6 (2020-2023, adds COVID-19 columns). Weekly rows surface the all-cause weekly total for the 3yf8-kanr years and the COVID-19 underlying-cause count for 2020-2023; source_type distinguishes annual from weekly, and age_adjusted_rate is populated for annual rows only.

Column	Type	Null	Description
`year`	VARCHAR	yes	Data year of deaths; MMWR year for weekly rows
`week_ending_date`	VARCHAR	yes	Week-ending date; weekly rows only, null for annual
`state`	VARCHAR	yes	State/jurisdiction; annual includes a United States total row
`cause_name`	VARCHAR	yes	Cause-of-death name; NCHS leading cause (annual) or COVID-19 (weekly)
`full_cause_name`	VARCHAR	yes	Detailed cause name (NCHS 113-cause for annual; COVID-19 detail for weekly)
`deaths`	VARCHAR	yes	Number of deaths for the cause/period (COVID-19 deaths for weekly rows). NULL means CDC suppressed this week's count under its small-cell privacy threshold (typically <10), NOT that zero deaths occurred — confirmed live (2,296 weekly state-level COVID-19 rows carry this NULL). Do not treat NULL as 0 in a SUM; a plain SUM(deaths) already excludes these rows correctly by SQL's own NULL-skipping behavior, but an explicit COALESCE(deaths, 0) or an outer join that backfills a missing week with 0 would silently convert real suppression into a false zero. CDC signals weekly COVID-19 suppression two different ways in the source: some weeks omit the count field entirely, others include it as a "0" placeholder alongside a companion suppression flag field — CdcMortalityResponseTransformer checks that flag and nulls the placeholder case too, so every suppressed weekly COVID-19 row reaches this table as NULL either way.
`age_adjusted_rate`	VARCHAR	yes	Age-adjusted deaths per 100,000 (2000 US std pop); annual only
`source_type`	VARCHAR	no	annual or weekly

cdc_brfss · table¶

CDC Behavioral Risk Factor Surveillance System prevalence data (Socrata dttw-5yxu). State-level health behavior estimates by year, question, and demographic breakout.

Column	Type	Null	Description
`year`	VARCHAR	yes	Survey year
`state`	VARCHAR	yes	state abbreviation from locationabbr
`question`	VARCHAR	yes	Survey question text
`category`	VARCHAR	yes	BRFSS class grouping the topic (source class, e.g. Health Status)
`topic`	VARCHAR	yes	Topic within the class
`response`	VARCHAR	yes	Response/answer option to the question
`pct`	VARCHAR	yes	prevalence percentage (data_value)
`sample_size`	VARCHAR	yes	Number of survey respondents behind the estimate
`break_out`	VARCHAR	yes	Demographic subgroup value (e.g. 18-24)
`break_out_category`	VARCHAR	yes	Demographic dimension of the breakout (e.g. Age Group)
`type`	VARCHAR	no	Constant record-type discriminator cdc_brfss

cdc_wonder_cancer_incidence · table¶

United States Cancer Statistics (USCS) invasive + in situ cancer incidence by state and year, 1999-2022, from CDC WONDER database D205. State-level grouping is confirmed to work through the WONDER XML API (unlike mortality/natality, USCS is not a National Vital Statistics System database and is not subject to that API's location-grouping restriction). No cancer data exists elsewhere in govdata.

Column	Type	Null	Description
`year`	VARCHAR	yes	Data year of diagnosis, 1999-2022
`state`	VARCHAR	yes	State of residence at diagnosis
`state_fips`	VARCHAR	yes	State FIPS code
`cases`	VARCHAR	yes	Invasive + in situ cancer incidence case count, all sites combined
`type`	VARCHAR	no	Constant record-type discriminator cdc_wonder_cancer_incidence

cdc_wonder_std_morbidity · table¶

Sexually Transmitted Disease Morbidity (reported case counts) by state, year, and disease, 1984-2014, from CDC WONDER database D127. CDC WONDER stopped updating this database after 2014 — it is a historical archive, not a live surveillance feed; current STI surveillance is published outside WONDER (e.g. NCHHSTP AtlasPlus). No STI data exists elsewhere in govdata.

Column	Type	Null	Description
`year`	VARCHAR	yes	Data year, 1984-2014 (CDC WONDER's STD Morbidity database was not updated past 2014; current STI surveillance lives outside WONDER, e.g. NCHHSTP AtlasPlus)
`state`	VARCHAR	yes	Reporting state/jurisdiction
`state_fips`	VARCHAR	yes	State FIPS code
`disease`	VARCHAR	yes	Disease category name — a rollup hierarchy (e.g. Total Syphilis is the parent of Primary and Secondary Syphilis), not a flat list; see disease_hierarchy_level
`disease_hierarchy_level`	VARCHAR	yes	Depth of this disease row in the rollup hierarchy (1=top-level disease e.g. Gonorrhea/Total Syphilis; 2/3=descendants e.g. Primary and Secondary Syphilis, Primary Syphilis). Filter to one level before summing cases — parent and child rows overlap and double-count if summed together.
`cases`	VARCHAR	yes	Reported case count
`population`	VARCHAR	yes	Population denominator used for the rate
`rate_per_100k`	VARCHAR	yes	Cases per 100,000 population
`type`	VARCHAR	no	Constant record-type discriminator cdc_wonder_std_morbidity

cdc_wonder_tb · table¶

Tuberculosis case counts by state and year, 1993-2024, from CDC WONDER's Online Tuberculosis Information System (OTIS), database D209. No TB data exists elsewhere in govdata.

Column	Type	Null	Description
`year`	VARCHAR	yes	Data year, 1993-2024
`state`	VARCHAR	yes	State of residence at diagnosis
`state_fips`	VARCHAR	yes	State FIPS code
`cases`	VARCHAR	yes	Tuberculosis case count
`pct_of_total`	VARCHAR	yes	This row's cases as a percent of the grand total across the full result set (not just this state or year) — a WONDER-computed convenience field, not independently meaningful per row
`type`	VARCHAR	no	Constant record-type discriminator cdc_wonder_tb

cdc_wonder_natality · table¶

National annual live birth counts, 2016-2024, from the NVSS Natality (expanded) database via CDC WONDER, database D149. National-only: CDC WONDER's API explicitly rejects grouping or limiting National Vital Statistics System data (mortality, natality) by any location field, even though the interactive web UI offers it — this is a documented CDC WONDER API policy, not a gap in this table. No births/fertility data exists elsewhere in govdata.

Column	Type	Null	Description
`year`	VARCHAR	yes	Birth year, 2016-2024 (expanded Natality dataset)
`births`	VARCHAR	yes	Live birth count, national total (CDC WONDER's API rejects sub-national location grouping for National Vital Statistics System data — this table is national-only by design, not an omission)
`type`	VARCHAR	no	Constant record-type discriminator cdc_wonder_natality

cdc_wonder_cause_of_death · table¶

National deaths by year and ICD-10 sub-chapter cause-of-death category, 1999-2020, from the NVSS Underlying Cause of Death database via CDC WONDER, database D76. National-only for the same CDC WONDER API location-grouping policy documented on cdc_wonder_natality. Finer cause-of-death granularity than the existing state-level 113-cause cdc_mortality table (which comes from a different source, data.cdc.gov/ Socrata, not CDC WONDER) — complements rather than duplicates it.

Column	Type	Null	Description
`year`	VARCHAR	yes	Data year, 1999-2020
`icd_subchapter`	VARCHAR	yes	ICD-10 sub-chapter cause-of-death category name
`icd_subchapter_code`	VARCHAR	yes	ICD-10 sub-chapter code range, e.g. A00-A09
`deaths`	VARCHAR	yes	Death count, national total (CDC WONDER's API rejects sub-national location grouping for National Vital Statistics System data — this table is national-only by design, not an omission)
`population`	VARCHAR	yes	National population denominator for the crude rate
`crude_rate`	VARCHAR	yes	Deaths per 100,000 population, national
`type`	VARCHAR	no	Constant record-type discriminator cdc_wonder_cause_of_death

cdc_wonder_cause_of_death_2018_2024 · table¶

National deaths by year and ICD-10 sub-chapter cause-of-death category, 2018-2024, from the NVSS Underlying Cause of Death (Single Race) database via CDC WONDER, database D158 — the post-2020 continuation of cdc_wonder_cause_of_death (D76, frozen at 1999-2020). Same national-only restriction as D76 (confirmed live: the WONDER API rejects location grouping, including County even though the interactive web UI offers it, for every vintage of this NVSS database). Overlaps D76 for 2018-2020 by design — see the cdc_wonder_cause_of_death_1999_2024 view, which picks D158 for 2018+ and D76 for earlier years to give one non-overlapping 1999-2024 series.

Column	Type	Null	Description
`year`	VARCHAR	yes	Data year, 2018-2024
`icd_subchapter`	VARCHAR	yes	ICD-10 sub-chapter cause-of-death category name
`icd_subchapter_code`	VARCHAR	yes	ICD-10 sub-chapter code range, e.g. A00-A09
`deaths`	VARCHAR	yes	Death count, national total (CDC WONDER's API rejects sub-national location grouping for National Vital Statistics System data — this table is national-only by design, not an omission)
`population`	VARCHAR	yes	National population denominator for the crude rate
`crude_rate`	VARCHAR	yes	Deaths per 100,000 population, national
`type`	VARCHAR	no	Constant record-type discriminator cdc_wonder_cause_of_death_2018_2024

cdc_county_overdose_deaths · table¶

Provisional 12-month-ending drug overdose death counts by county of residence, from CDC's Vital Statistics Rapid Release (VSRR) program. Currently updated (not a frozen historical archive). County-level drug overdose mortality is NOT obtainable from CDC WONDER — its API rejects location grouping for National Vital Statistics System mortality data even though the interactive web UI offers it (confirmed live) — so this Socrata feed is the only public source for this grain. Counts of 1-9 are suppressed for privacy (see footnote).

Column	Type	Null	Description
`data_as_of`	VARCHAR	yes	Date this provisional data snapshot was generated
`year`	VARCHAR	yes	12-month-ending period's year
`month`	VARCHAR	yes	12-month-ending period's month (numeric)
`st_abbrev`	VARCHAR	yes	State postal abbreviation
`state_name`	VARCHAR	yes	State name
`countyname`	VARCHAR	yes	County name (decedent's county of residence)
`fips`	VARCHAR	yes	Combined state+county FIPS code
`statefips`	VARCHAR	yes	State FIPS code
`countyfips`	VARCHAR	yes	County FIPS code
`provisional_drug_overdose`	VARCHAR	yes	Provisional 12-month-ending drug overdose death count for this county
`footnote`	VARCHAR	yes	Suppression/reliability footnote (e.g. counts under 10 are suppressed for privacy)
`percentage_of_records_pending`	VARCHAR	yes	Percent of this period's death records still pending investigation/certification — provisional counts rise as pending records finalize
`monthendingdate`	VARCHAR	yes	Last date of the 12-month-ending window
`start_date`	VARCHAR	yes	First date of the 12-month-ending window
`end_date`	VARCHAR	yes	Last date of the 12-month-ending window
`type`	VARCHAR	no	Constant record-type discriminator cdc_county_overdose_deaths

cdc_county_injury_mortality · table¶

County-level death counts and rates for drug overdose, suicide, homicide, and firearm injury, from CDC's Mapping Injury, Overdose, and Violence (MIOV) tool. Both yearly and trailing-twelve-month periods. Currently updated. Complements cdc_county_overdose_deaths with suicide/homicide/firearm categories. Counts of 1-9 are suppressed for privacy (see count_sup); when suppressed, rate is NULL (the source has no crude estimate) and rate_m flags that a Bayesian small-area model was used instead, with its credible interval in rate_m_ci.

Column	Type	Null	Description
`geoid`	VARCHAR	yes	County FIPS code
`name`	VARCHAR	yes	County name
`st_geoid`	VARCHAR	yes	State FIPS code
`st_name`	VARCHAR	yes	State name
`intent`	VARCHAR	yes	Death category: All_Homicide, All_Suicide, Drug_OD, FA_Deaths (all firearm deaths), FA_Homicide, FA_Suicide
`period`	VARCHAR	yes	Yearly or trailing-twelve-months reporting period
`count_sup`	VARCHAR	yes	Death count; suppressed (blank/flagged) when 1-9 deaths in the area for privacy
`rate`	DOUBLE	yes	Crude death rate per 100,000 population; NULL when suppressed (see rate_m) rather than the source's -999 sentinel
`rate_m`	BOOLEAN	yes	True when the crude rate was suppressed and CDC substituted a Bayesian small-area model estimate (source field is a 0/1 flag, not a rate, despite its name)
`rate_m_ci`	VARCHAR	yes	Credible interval for the model-based estimate when rate_m is true; NULL otherwise (source's -999 sentinel is mapped to NULL)
`ttm_date_range`	VARCHAR	yes	Date range covered when period is trailing-twelve-months
`type`	VARCHAR	no	Constant record-type discriminator cdc_county_injury_mortality

cdc_state_vital_provisional · table¶

State-level monthly provisional counts of live births, deaths, and infant deaths, from CDC's Vital Statistics Rapid Release (VSRR) program. State-level Natality is NOT obtainable from CDC WONDER — its API rejects location grouping for NVSS data even though the interactive web UI offers it (confirmed live) — so this Socrata feed is the only public source for state-grain birth counts. Also gives state-level all-cause and infant mortality trend, independent of cdc_mortality (data.cdc.gov, different endpoint) and cdc_wonder_cause_of_death (national-only, cause-specific).

Column	Type	Null	Description
`state`	VARCHAR	yes	State name, or UNITED STATES for the national total row
`year`	VARCHAR	yes	Data year
`month`	VARCHAR	yes	Data month name
`period`	VARCHAR	yes	Reporting period type (Monthly)
`indicator`	VARCHAR	yes	Number of Live Births, Number of Deaths, or Number of Infant Deaths
`data_value`	VARCHAR	yes	Provisional count for this state/year/month/indicator, rounded (nearest thousand for births/deaths, nearest hundred for infant deaths)
`type`	VARCHAR	no	Constant record-type discriminator cdc_state_vital_provisional

cdc_teen_birth_rates_county · table¶

County-level birth rates for females age 15-19, from NCHS. General county-level Natality counts are not publicly available (NCHS's All-County Natality File requires a restricted- access application for privacy reasons), but this teen-birth-rate subset is public.

Column	Type	Null	Description
`year`	VARCHAR	yes	Data year
`state`	VARCHAR	yes	State name
`county`	VARCHAR	yes	County name
`state_fips_code`	VARCHAR	yes	State FIPS code
`county_fips_code`	VARCHAR	yes	County FIPS code
`combined_fips_code`	VARCHAR	yes	Combined state+county FIPS code
`birth_rate`	VARCHAR	yes	Birth rate per 1,000 females age 15-19
`lower_confidence_limit`	VARCHAR	yes	95% confidence interval lower bound
`upper_confidence_limit`	VARCHAR	yes	95% confidence interval upper bound
`type`	VARCHAR	no	Constant record-type discriminator cdc_teen_birth_rates_county

cdc_wonder_multiple_cause_of_death · table¶

National deaths by year and MCD (Multiple Cause of Death) Drug/Alcohol Induced Cause category, 1999-2020, from CDC WONDER database D77. Counts a death if the cause appears anywhere among its up to 20 listed multiple causes, not only as the underlying cause — the standard epidemiological definition for drug overdose mortality surveillance, and the reason this table (unlike cdc_wonder_cause_of_death, which is underlying-cause-only) actually answers an opioid/drug-involvement question. National-only for the same CDC WONDER API location-grouping restriction documented on cdc_wonder_natality.

Column	Type	Null	Description
`year`	VARCHAR	yes	Data year
`cause`	VARCHAR	yes	MCD Drug/Alcohol Induced Cause category, e.g. 'Drug poisonings (overdose) Unintentional (X40-X44)' — the ICD-10 code range is embedded in the label text, not a separate column
`deaths`	VARCHAR	yes	Death count where this cause appears anywhere among the death's up to 20 listed multiple causes (not just the underlying cause) — the standard epidemiological definition for drug overdose mortality. National total (CDC WONDER's API rejects sub-national location grouping for National Vital Statistics System data — this table is national-only by design, not an omission)
`population`	VARCHAR	yes	National population denominator for the crude rate
`crude_rate`	VARCHAR	yes	Deaths per 100,000 population, national
`type`	VARCHAR	no	Constant record-type discriminator cdc_wonder_multiple_cause_of_death

cdc_wonder_multiple_cause_of_death_2018_2024 · table¶

National deaths by year and MCD Drug/Alcohol Induced Cause category, 2018-2024, from CDC WONDER database D157 — the post-2020 continuation of cdc_wonder_multiple_cause_of_death (D77, frozen at 1999-2020). Overlaps D77 for 2018-2020 by design — see the cdc_wonder_multiple_cause_of_death_1999_2024 view, which picks D157 for 2018+ and D77 for earlier years to give one non-overlapping 1999-2024 series.

Column	Type	Null	Description
`year`	VARCHAR	yes	Data year
`cause`	VARCHAR	yes	MCD Drug/Alcohol Induced Cause category, e.g. 'Drug poisonings (overdose) Unintentional (X40-X44)' — the ICD-10 code range is embedded in the label text, not a separate column
`deaths`	VARCHAR	yes	Death count where this cause appears anywhere among the death's up to 20 listed multiple causes (not just the underlying cause) — the standard epidemiological definition for drug overdose mortality. National total (CDC WONDER's API rejects sub-national location grouping for National Vital Statistics System data — this table is national-only by design, not an omission)
`population`	VARCHAR	yes	National population denominator for the crude rate
`crude_rate`	VARCHAR	yes	Deaths per 100,000 population, national
`type`	VARCHAR	no	Constant record-type discriminator cdc_wonder_multiple_cause_of_death_2018_2024

cdc_wonder_vaers · table¶

Vaccine Adverse Event Reporting System (VAERS) report counts by year reported and state/territory, from CDC WONDER database D8. CAUTION: VAERS is a raw, unverified self-reporting system — reports can be filed by anyone (patients, providers, manufacturers) with minimal validation, a report being present does NOT establish that the vaccine caused the reported event, and the data includes known data-entry errors (e.g. a small number of reports dated before VAERS existed in 1990). Use for signal detection / reporting-volume trends only, never as evidence of causation. Public access to full VAERS detail was expanded May 2025; older third-party WONDER "saved queries" for this database predate that change and are no longer valid — this table's fields were rebuilt against the current form.

Column	Type	Null	Description
`year`	VARCHAR	yes	Year the report was received by VAERS (not necessarily the year of vaccination)
`state`	VARCHAR	yes	State/territory of the vaccinated person
`events`	VARCHAR	yes	Count of VAERS reports (raw report counts, not verified adverse events — see table comment)
`pct_of_total`	VARCHAR	yes	This row's reports as a percent of the grand total across the full result set (not just this state or year) — a WONDER-computed convenience field, not independently meaningful per row
`type`	VARCHAR	no	Constant record-type discriminator cdc_wonder_vaers

cms_open_payments · table¶

CMS Open Payments (Sunshine Act) payments from pharmaceutical/device manufacturers to physicians. Bulk CSV downloads from download.cms.gov, one file per program year (CY2018+) and payment_type (general/research/ownership), partitioned by both. paying_entity_name is unstructured (no per-row id). ref.canonical_org_entity carries it as an already-resolved identity (cms_paying_entity_name, with an LEI/SEC CIK when matched); join there on the name rather than re-running a fuzzy match against gleif_entities.legal_name by hand.

Column	Type	Null	Description
`program_year`	VARCHAR	yes	Calendar year the payment/transfer of value was reported
`change_type`	VARCHAR	yes	Record status vs prior publication (new/changed/unchanged)
`physician_profile_id`	VARCHAR	yes	CMS-assigned unique ID for the covered recipient
`physician_first_name`	VARCHAR	yes	Covered recipient first name
`physician_last_name`	VARCHAR	yes	Covered recipient last name
`physician_specialty`	VARCHAR	yes	Covered recipient primary medical specialty (e.g. Internal Medicine)
`physician_state`	VARCHAR	yes	Covered recipient state
`physician_country`	VARCHAR	yes	Covered recipient country
`paying_entity_name`	VARCHAR	yes	Applicable manufacturer or GPO making the payment
`total_amount`	VARCHAR	yes	Total payment/transfer-of-value amount in US dollars
`payment_date`	VARCHAR	yes	Date of the payment or transfer of value
`number_of_payments`	VARCHAR	yes	Number of payments included in the total amount
`nature_of_payment`	VARCHAR	yes	Nature of the payment or transfer of value (e.g. Consulting Fee)
`product_name`	VARCHAR	yes	Name of associated drug, biological, device, or medical supply
`product_category`	VARCHAR	yes	Whether the product is a drug, biological, device, or medical supply
`payment_type`	VARCHAR	no	general, research, or ownership

rxnorm_drugs · table¶

RxNorm drug concepts from the NLM bulk allconcepts endpoint. Covers ingredient (IN), brand name (BN), and clinical drug (SCD) term types. Single-page call (~37k concepts). rxcui joins to fda_ndc_products.rxcui.

Column	Type	Null	Description
`rxcui`	VARCHAR	yes	RxNorm concept unique identifier — PK; FK target of fda_ndc_products.rxcui
`name`	VARCHAR	yes	RxNorm concept name/string
`tty`	VARCHAR	yes	term type: IN, BN, SCD
`type`	VARCHAR	no	Constant record-type discriminator rxnorm_drugs

who_gho_indicators · table¶

WHO Global Health Observatory indicators: value per country x indicator x dimension x year. Tall — the international analog of the U.S. openFDA/clinical tables. country_code is ISO alpha-3 (FK to ref.countries); WHO REGION / income-group rows are flagged is_aggregate. Fan out one GHO OData indicator per request; year is emitted per row and the partition is replaced wholesale on re-run (overwritePartitions).

Column	Type	Null	Description
`indicator_code`	string	yes	GHO indicator id (the fan-out key)
`indicator_name`	string	yes	Indicator label (from the bundled catalog)
`country_code`	string	yes	Country ISO alpha-3 (GHO SpatialDim; FK to ref.countries.iso_alpha3)
`spatial_type`	string	yes	GHO SpatialDimType (COUNTRY / REGION / WORLDBANKINCOMEGROUP)
`sex`	string	yes	Sex dimension (BTSX / MLE / FMLE) when the indicator is sex-disaggregated
`dimension`	string	yes	Other GHO Dim1 value (age group, cause, ...) when present
`value_numeric`	double	yes	Numeric value (GHO NumericValue; null when only a display string exists)
`value_display`	string	yes	Display value string (e.g. "72.6 [70.1-75.0]")
`unit`	string	yes	Unit when GHO supplies one (usually null for this endpoint)
`year`	integer	no	Data year (from GHO TimeDim)
`is_aggregate`	boolean	yes	True when spatial_type != COUNTRY (region/income-group roll-up)

cdc_wonder_cause_of_death_1999_2024 · view¶

National deaths by year and ICD-10 sub-chapter, 1999-2024 — cdc_wonder_cause_of_death (D76, 1999-2020) unioned with cdc_wonder_cause_of_death_2018_2024 (D158, 2018-2024), preferring D158 for the 2018-2020 years both cover so the combined series never double-counts.