🏥 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.

69 datasets · 736 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, taken from the latest submission (see latest_submission_type), so it describes a supplement's review whenever one is the latest action. openFDA leaves it empty on many submissions, mostly older ones, so NULL is common; the ORIG submission's own value is in fda_drug_submissions.review_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 for this submission. openFDA leaves it empty on many submissions, mostly older ones, so NULL is common.

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. Single vintage, no time dimension: the table holds only the latest CHR release (release_year 2025: 1 nation, 51 state and 3,152 county rows), release_year is the same value on every row, and the mortality years behind the rates are not carried. For "what was it in year X" and for trends use chr_premature_death_trends, whose rows are keyed by the multi-year period (period_end_year); do not read release_year here as an observation year.

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).
results_first_submit_date	string	yes	Date the sponsor first submitted results (YYYY-MM-DD; may be YYYY-MM at month precision). Null when no results were ever submitted. Compare with primary_completion_date to measure how long after completion results were submitted — FDAAA 801 sets the due date at 12 months after primary completion for applicable clinical trials. The API publishes no due-date field and no applicable-clinical-trial flag, so the due date is derived by the reader.
results_first_post_date	string	yes	Date results first became public on ClinicalTrials.gov (YYYY-MM-DD; may be YYYY-MM at month precision), after the registry's quality-control review. Later than results_first_submit_date; null until posted.
is_fda_regulated_drug	boolean	yes	Sponsor-declared: the study studies a drug product regulated by the FDA (oversightModule.isFdaRegulatedDrug). One input to FDAAA 801 applicability; null when the sponsor did not declare it, as on many studies registered before 2017.
is_fda_regulated_device	boolean	yes	Sponsor-declared: the study studies a device product regulated by the FDA (oversightModule.isFdaRegulatedDevice). One input to FDAAA 801 applicability; null when the sponsor did not declare it, as on many studies registered before 2017.

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. Also carries this same CMS extract's balance-sheet and operating-expense fields (cash_on_hand, current/total assets and liabilities, total_operating_expense, depreciation_cost, cost_to_charge_ratio) — the source for an operating margin (net_income_from_patient_services / total_patient_revenue) or days-cash- on-hand ((cash_on_hand / (total_operating_expense - depreciation_cost)) * 365) ratio; neither ratio is stored precomputed since either can be divide-by-zero for a facility with no reported operating expense. Confirmed live 2026-09-26: this same CMS dataset (44060663-47d8-4ced-a115-b53b4c270acb) has no Medicare-specific, self-pay/uninsured, or private-insurance revenue field — only Medicaid and stand-alone CHIP are broken out; a Medicare/uninsured/ private payer-mix split is not available from this extract.

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
total_operating_expense	double	yes	Operating margin denominator component — net_income_from_patient_services / total_patient_revenue is the operating margin ratio
depreciation_cost	double	yes	Non-cash portion of total_operating_expense — subtract from it for the days-cash-on-hand denominator
cash_on_hand	double	yes	Days-cash-on-hand numerator — (cash_on_hand / (total_operating_expense - depreciation_cost)) * 365
total_current_assets	double	yes
total_current_liabilities	double	yes
total_assets	double	yes
total_liabilities	double	yes
total_fund_balances	double	yes	Net worth/equity — Total Assets less Total Liabilities
cost_to_charge_ratio	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"

cms_nursing_home_deficiencies · table

CMS Provider Data Catalog "Health Deficiencies" (dataset r5ix-sfxw), one row per citation (PK: ccn + survey_date + deficiency_tag_number + inspection_cycle). Multi-year citation-level history of nursing-home health deficiencies, carrying the Scope-Severity code (CMS's G–L actual-harm/immediate-jeopardy scale), correction status, and inspection cycle per citation. Complements cms_nursing_home (single-snapshot facility-level provider information) with the trailing-window, severity-filtered citation detail needed for repeat-violation analysis. Source: data.cms.gov/provider-data/dataset/r5ix-sfxw.

Column	Type	Null	Description
ccn	string	no	CMS Certification Number — PK component
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
survey_date	string	no	Date of the survey/inspection — PK component
survey_type	string	yes	Health or Fire Safety
deficiency_prefix	string	yes	F (federal) or state-specific prefix
deficiency_category	string	yes	Category grouping (e.g. Resident Assessment and Care Planning Deficiencies)
deficiency_tag_number	string	no	CMS deficiency tag number — PK component
deficiency_description	string	yes	Narrative description of the deficiency
scope_severity_code	string	yes	CMS Scope-Severity code (A–L; G–L = actual harm / immediate jeopardy)
deficiency_corrected	string	yes	Correction status (e.g. Deficient Provider has date of correction, Past Non-Compliance)
correction_date	string	yes	Date the deficiency was corrected
inspection_cycle	string	no	Inspection cycle number — PK component
standard_deficiency	string	yes	Y/N — cited on a standard survey
complaint_deficiency	string	yes	Y/N — cited on a complaint survey
infection_control_inspection_deficiency	string	yes	Y/N — cited on an infection-control focused inspection
citation_under_idr	string	yes	Y/N — citation under Informal Dispute Resolution
citation_under_iidr	string	yes	Y/N — citation under Independent Informal Dispute Resolution
type	string	no	Always "cms_nursing_home_deficiencies"

cms_pbj_nurse_staffing · table

CMS Payroll-Based Journal (PBJ) "Daily Nurse Staffing" (data.cms.gov catalog entry "Payroll Based Journal Daily Nurse Staffing"), one row per facility (PROVNUM — joins to cms_nursing_home.ccn) per calendar day (work_date), with hours split by nursing role (RN Director of Nursing, RN admin, staff RN, LPN admin, staff LPN, CNA, nurse-aide-in-training, medication aide) and further split employee vs. contract. Fills the gap cms_nursing_home leaves: that table carries only a current-snapshot hours-per-resident-day ratio with no history, this table is the underlying daily time series a staffing trend needs — divide any hrs_*/resident_census pair for a per-resident-day rate on any day or rolled-up period. Quarterly releases back to 2017Q1 (~37 quarters as of 2026Q1); each quarter is a separate, independently-hosted CSV with no derivable URL pattern, so this table is built by catalog discovery rather than a templated URL (see the provider class comment). Verified live 2026-09-27: 2017Q1-Q4 name the RN-director and LPN-admin total columns hrs_rn_donadmin/hrs_lpn_admin instead of the hrs_rndon/hrs_lpnadmin used from 2018Q1 on — both aliased to the same output column by the provider. The 2017Q2 file itself has a malformed header (no hrs_medaide_ctr column; a stray duplicate hrs_rn_donadmin token appears in its place) — medication_aide_hours_contract is null for that one quarter, not a defect in this pipeline. Large: ~237MB per quarter CSV, full history is a multi-GB backfill.

Column	Type	Null	Description
ccn	string	no	CMS Certification Number (source PROVNUM) — PK component, joins to cms_nursing_home.ccn
provider_name	string	yes	Facility legal/doing-business-as name
city	string	yes	City or town
state	string	yes	FK to geo.state_ref.state_abbr
county_name	string	yes	County or parish
county_fips	string	yes	County FIPS code as published in the source file
report_quarter	string	yes	Source file's own CY_Qtr label (e.g. "2017Q1"), kept for audit — the year partition column is derived from work_date, not this field
work_date	date	no	Calendar day these hours were reported for — PK component
resident_census	integer	yes	MDS resident census for the day — denominator for a per-resident-day rate on any hrs_* column
rn_don_hours	double	yes	RN Director of Nursing hours (employee + contract)
rn_don_hours_employee	double	yes	RN Director of Nursing hours, employee only
rn_don_hours_contract	double	yes	RN Director of Nursing hours, contract only
rn_admin_hours	double	yes	RN administrative (non-DON) hours (employee + contract)
rn_admin_hours_employee	double	yes	RN administrative hours, employee only
rn_admin_hours_contract	double	yes	RN administrative hours, contract only
rn_hours	double	yes	Staff RN direct-care hours (employee + contract)
rn_hours_employee	double	yes	Staff RN direct-care hours, employee only
rn_hours_contract	double	yes	Staff RN direct-care hours, contract only
lpn_admin_hours	double	yes	LPN administrative hours (employee + contract)
lpn_admin_hours_employee	double	yes	LPN administrative hours, employee only
lpn_admin_hours_contract	double	yes	LPN administrative hours, contract only
lpn_hours	double	yes	Staff LPN direct-care hours (employee + contract)
lpn_hours_employee	double	yes	Staff LPN direct-care hours, employee only
lpn_hours_contract	double	yes	Staff LPN direct-care hours, contract only
cna_hours	double	yes	CNA direct-care hours (employee + contract)
cna_hours_employee	double	yes	CNA direct-care hours, employee only
cna_hours_contract	double	yes	CNA direct-care hours, contract only
nurse_aide_trainee_hours	double	yes	Nurse-aide-in-training hours (employee + contract)
nurse_aide_trainee_hours_employee	double	yes	Nurse-aide-in-training hours, employee only
nurse_aide_trainee_hours_contract	double	yes	Nurse-aide-in-training hours, contract only
medication_aide_hours	double	yes	Medication aide hours (employee + contract)
medication_aide_hours_employee	double	yes	Medication aide hours, employee only
medication_aide_hours_contract	double	yes	Medication aide hours, contract only — null for 2017Q2, whose source file has a malformed header (see table comment)
type	string	no	Always "cms_pbj_nurse_staffing"
year	integer	no	Calendar year of work_date (partition column)

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 carry an All Cause total for every state-week 2014-2023, plus a COVID-19 underlying-cause count for each 2020-2023 state-week (two rows per state-week in those years). The annual series with cause_name 'All causes' ends at 2017, so weekly All Cause rows are the only all-cause series after 2017. The 2020-2023 weekly rows cover the 50 states, DC, New York City, Puerto Rico and a United States total, by state of occurrence. source_type distinguishes annual from weekly, and age_adjusted_rate is populated for annual rows only. This table has no county grain. For county-level mortality outcomes use chr_premature_death_trends (all-cause years of potential life lost before age 75, county rows via geo_level='county', through 2023), or cdc_county_injury_mortality and cdc_county_overdose_deaths for drug overdose, suicide, homicide and firearm deaths.

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 All Cause / COVID-19 (weekly; COVID-19 for 2020-2023 only)
full_cause_name	VARCHAR	yes	Detailed cause name (NCHS 113-cause for annual; All Cause / COVID-19 detail for weekly)
deaths	VARCHAR	yes	Number of deaths for the cause/period (weekly rows: All Cause total or COVID-19 underlying-cause deaths, per cause_name). 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 in the source with a companion flag_cov19ucod field on weeks where the underlying-cause count field is absent; CdcMortalityResponseTransformer maps both to NULL. A zero COVID-19 count is a real zero.
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_places_county · table

CDC PLACES model-based county estimates of adult chronic disease, health-risk-behavior, prevention, disability and social-need prevalence (Socrata swc5-untb, 2025 release; data years 2022 and 2023), ~3,000 counties x ~40 measures x crude/age-adjusted. This is the county-grain source for asthma (measureid CASTHMA, 'Current asthma among adults'), COPD, coronary heart disease, diabetes, etc.; cdc_brfss carries the same topics only at state grain. Estimates are small-area model outputs from BRFSS (or ACS) with 95% confidence limits, not counts of cases; data_value is a percent of adults. Each county-measure appears twice (Crude and Age-adjusted prevalence) — filter data_value_type to avoid double counting. Covers disease prevalence only; EPA regulatory-impact-analysis projections (avoided deaths/illnesses per rule) are per-rule PDF documents with no structured feed and are not in this table. CDC republishes PLACES under a new Socrata dataset id each annual release, so the source url here is a re-verify-on-new-release item.

Column	Type	Null	Description
year	VARCHAR	yes	Data year the model-based estimates describe (the 2025 PLACES release carries 2022 and 2023)
stateabbr	VARCHAR	yes	State postal abbreviation
statedesc	VARCHAR	yes	State name
locationname	VARCHAR	yes	County name
locationid	VARCHAR	yes	5-digit county FIPS code (state+county)
datasource	VARCHAR	yes	Underlying survey the estimate is modeled from (BRFSS or ACS)
category	VARCHAR	yes	PLACES measure category (Health Outcomes, Prevention, Health Risk Behaviors, Health Status, Disability, Health-Related Social Needs)
categoryid	VARCHAR	yes	Short code for category (e.g. HLTHOUT)
measure	VARCHAR	yes	Full measure text (e.g. 'Current asthma among adults')
measureid	VARCHAR	yes	Short measure code (e.g. CASTHMA = current asthma, COPD, CHD, DIABETES)
short_question_text	VARCHAR	yes	Short measure label (e.g. 'Current Asthma')
data_value_type	VARCHAR	yes	Crude prevalence or Age-adjusted prevalence — filter to one, the two are separate rows
datavaluetypeid	VARCHAR	yes	Short code for data_value_type (CrdPrv, AgeAdjPrv)
data_value_unit	VARCHAR	yes	Unit of data_value (percent)
data_value	VARCHAR	yes	Model-based prevalence estimate, percent of adults
low_confidence_limit	VARCHAR	yes	Lower bound of the 95% confidence interval
high_confidence_limit	VARCHAR	yes	Upper bound of the 95% confidence interval
totalpopulation	VARCHAR	yes	County total population used for the estimate
totalpop18plus	VARCHAR	yes	County adult (18+) population used for the estimate
type	VARCHAR	no	Constant record-type discriminator cdc_places_county

cdc_state_drug_overdose_deaths · table

Provisional drug overdose death counts by state of occurrence and drug class, from CDC's Vital Statistics Rapid Release (VSRR) program (Socrata xkb8-kh2a). Currently updated. Each row is one jurisdiction x reporting month x indicator, and the period is a 12-month-ending window: a row is the count over the twelve months ending in its year and month, not that single month. Indicators are counts by ICD-10 drug class (all opioids T40.0-T40.4/T40.6, natural and semi-synthetic opioids T40.2, methadone T40.3, synthetic opioids excluding methadone T40.4 which is where illicit fentanyl sits, heroin T40.1, cocaine T40.5, psychostimulants T43.6) plus total deaths, total drug overdose deaths and the percent of deaths with a drug specified. Drug-class counts overlap because one death can involve several drugs, so do not sum them. Jurisdictions are the 50 states, DC, PR, the national total ('US') and New York City ('YC'). State of occurrence, not residence; for county residence see cdc_county_overdose_deaths, and for national counts by named drug (fentanyl, xylazine, oxycodone and others) see cdc_drug_overdose_deaths_by_drug.

Column	Type	Null	Description
state	VARCHAR	yes	Jurisdiction postal abbreviation: 50 states, DC, PR, 'US' national total, 'YC' New York City
state_name	VARCHAR	yes	Jurisdiction name
year	VARCHAR	yes	Year in which the 12-month-ending period ends
month	VARCHAR	yes	Month name in which the 12-month-ending period ends
period	VARCHAR	yes	Period type, always '12 month-ending'
indicator	VARCHAR	yes	Death measure or ICD-10 drug class, e.g. 'Synthetic opioids, excl. methadone (T40.4)', 'Opioids (T40.0-T40.4,T40.6)', 'Number of Drug Overdose Deaths', 'Percent with drugs specified'. Drug-class indicators overlap; do not sum them
data_value	DOUBLE	yes	Reported value for the indicator: a death count, or a percent for 'Percent with drugs specified'. NULL where CDC suppresses it (see footnote)
predicted_value	DOUBLE	yes	CDC's estimate of the final value after adjusting for records still pending; NULL where CDC publishes none
percent_complete	VARCHAR	yes	Percent of expected death records received for this jurisdiction and period
percent_pending_investigation	VARCHAR	yes	Percent of records still pending investigation; reported counts rise as these finalize
footnote	VARCHAR	yes	Reliability or suppression note for the value, e.g. data not shown due to low data quality
footnote_symbol	VARCHAR	yes	Symbol that marks the footnote on the source table
type	VARCHAR	no	Constant record-type discriminator cdc_state_drug_overdose_deaths

cdc_drug_overdose_deaths_by_drug · table

Provisional national and HHS-region drug overdose death counts by named drug, from CDC's Vital Statistics Rapid Release (VSRR) program (Socrata 8hzs-zshh). Currently updated. Drugs are fentanyl, heroin, oxycodone, cocaine, methamphetamine, amphetamine, xylazine, ketamine, MDMA, gabapentin, bromazolam and mitragynine. Each row is one jurisdiction x month x drug and the period is a 12-month-ending window: a row is the count over the twelve months ending in its month. A death is counted under every drug involved, so drug counts overlap and must not be summed. Jurisdictions are 'United States' and the ten HHS regions ('Region 1' to 'Region 10'), by state of occurrence. This is the table for separating fentanyl and other illicit-drug involvement from prescription opioids such as oxycodone; state-level opioid drug classes are in cdc_state_drug_overdose_deaths, and prescription dispensing volume is in cdc_opioid_dispensing_rates_state and cdc_opioid_dispensing_rates_county.

Column	Type	Null	Description
data_as_of	VARCHAR	yes	Date this provisional data snapshot was generated
death_year	VARCHAR	yes	Year in which the 12-month-ending period ends
death_month	VARCHAR	yes	Month name in which the 12-month-ending period ends
jurisdiction_occurrence	VARCHAR	yes	'United States' or an HHS region ('Region 1' to 'Region 10'), by state of occurrence
drug_involved	VARCHAR	yes	Named drug involved in the death, e.g. Fentanyl, Heroin, Oxycodone, Xylazine. A death is counted under each drug involved, so rows overlap
time_period	VARCHAR	yes	Period type, always '12 month-ending'
month_ending_date	VARCHAR	yes	Last date of the 12-month-ending window
drug_overdose_deaths	DOUBLE	yes	Provisional count of drug overdose deaths involving this drug over the 12-month-ending window
type	VARCHAR	no	Constant record-type discriminator cdc_drug_overdose_deaths_by_drug

cdc_opioid_dispensing_rates_state · table

Retail-pharmacy opioid prescriptions dispensed per 100 persons per year, by state, from CDC's opioid dispensing rate maps (IQVIA retail pharmacy prescription data), 2019 onward. One row per state per year for the 50 states and DC. This is prescribing volume, not overdose deaths and not pills or dosage: prescriptions per 100 residents. Pair with cdc_state_drug_overdose_deaths to compare dispensing against overdose deaths, noting that prescription opioids are no longer the main driver of overdose deaths. The county version is cdc_opioid_dispensing_rates_county. Rates are revised in later updates.

Column	Type	Null	Description
year	INTEGER	yes	Calendar year of the dispensing rate
state_name	VARCHAR	yes	State name
state_abbr	VARCHAR	yes	State postal abbreviation
state_fips	VARCHAR	yes	2-digit state FIPS code
opioid_dispensing_rate	DOUBLE	yes	Opioid prescriptions dispensed at retail pharmacies per 100 persons in the year
rate_category	VARCHAR	yes	CDC's map legend bin for the rate, e.g. '>= 49.9' or '33.9 to < 40.4'; the bin edges are those of the latest map
type	VARCHAR	no	Constant record-type discriminator cdc_opioid_dispensing_rates_state

cdc_opioid_dispensing_rates_county · table

Retail-pharmacy opioid prescriptions dispensed per 100 persons per year, by county, from CDC's opioid dispensing rate maps (IQVIA retail pharmacy prescription data), 2019 onward. One row per county per year covering about 98% of US counties; counties CDC does not publish a rate for carry a NULL opioid_dispensing_rate. This is prescribing volume, not overdose deaths: prescriptions per 100 residents. Join county overdose deaths on the 5-digit FIPS code (cdc_county_overdose_deaths.fips). The state version is cdc_opioid_dispensing_rates_state. Rates are revised in later updates.

Column	Type	Null	Description
year	INTEGER	yes	Calendar year of the dispensing rate
state_name	VARCHAR	yes	State name
state_abbr	VARCHAR	yes	State postal abbreviation
county_name	VARCHAR	yes	County name
county_fips	VARCHAR	yes	5-digit combined state+county FIPS code
opioid_dispensing_rate	DOUBLE	yes	Opioid prescriptions dispensed at retail pharmacies per 100 persons in the year; NULL where CDC publishes no rate ('Data unavailable')
rate_category	VARCHAR	yes	CDC's map legend bin for the rate, e.g. '< 17.7' or 'Data unavailable'; the bin edges are those of the latest map
type	VARCHAR	no	Constant record-type discriminator cdc_opioid_dispensing_rates_county

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); for those counties CDC publishes a Bayesian small-area model estimate in rate instead of a crude rate, flagged by rate_m=true with its credible interval in rate_m_ci. rate is therefore a crude rate where rate_m is false and a model estimate where rate_m is true, so a population-weighted rate over many counties blends the two. rate is NULL only where CDC publishes no estimate at all. year is the observation year: the calendar year for a Yearly row (2019 onward), and for a trailing-twelve-months row the year in which the window ends (ttm_date_range, e.g. June 2025 to May 2026 is 2026), so filter period to the four-digit rows for like-for-like annual comparisons.

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
year	INTEGER	yes	Observation year: the four-digit period for a yearly row, the year the window ends for a trailing-twelve-months row (from ttm_date_range)
period	VARCHAR	yes	Yearly (a four-digit year) or TTM (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	Death rate per 100,000 population: the crude rate when rate_m is false, CDC's Bayesian small-area model estimate when rate_m is true (count suppressed as 1-9). NULL where CDC publishes no estimate at all (the source's -999 sentinel is mapped to NULL)
rate_m	BOOLEAN	yes	True when rate is a Bayesian small-area model estimate rather than a crude rate, which CDC does for counts of 1-9 (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_nndss_weekly · table

Weekly provisional case counts for nationally notifiable infectious diseases (measles, pertussis, mumps, salmonellosis, Lyme disease, tuberculosis-adjacent conditions not already covered by cdc_wonder_tb, and ~130 others), from CDC's National Notifiable Diseases Surveillance System (NNDSS). State/territory/HHS-region/national grain, by MMWR year and week. Currently updated weekly (confirmed live 2026-09-27: latest data is MMWR week 37 of 2026). disease carries a suffix distinguishing case classification for some conditions (e.g. "Measles, Imported" vs "Measles, Indigenous" — sum both for a total). current_week_count/previous_52_week_max/cumulative_ytd_current_year/ cumulative_ytd_previous_year are the source's four case-count measures; each has a companion _flag column that is populated INSTEAD of the count when CDC did not publish a number — see each flag column's comment for the code meanings. Do not treat a NULL count as zero; a flag value of "-" (no reported cases) is the only code that means zero, "U" (unavailable) and "NN"/"N" (not nationally/jurisdictionally notifiable) are not. Complements cdc_wonder_std_morbidity (STI-specific, 1984-2014, frozen) and cdc_wonder_tb (tuberculosis-specific, 1993-2024) with a live, broader disease panel; there is no county grain here (NNDSS publishes at state/territory grain only). reporting_area/location1/ location2 casing is inconsistent across years in the source itself (confirmed live 2026-09-27: ALL CAPS for 2022 rows, mixed case e.g. "Connecticut" for current 2026 rows) — use UPPER()/LOWER() when joining or filtering on state name rather than assuming one casing convention.

Column	Type	Null	Description
reporting_area	VARCHAR	yes	State, territory, HHS region, 'U.S. Residents' total, or 'Non-U.S. Residents' category
mmwr_year	VARCHAR	yes	MMWR (epidemiological) year
mmwr_week	VARCHAR	yes	MMWR week number (1-53)
disease	VARCHAR	yes	Disease/condition label; may carry a case-classification suffix (e.g. Imported, Indigenous, Confirmed, Probable)
current_week_count	VARCHAR	yes	Cases reported for mmwr_week; NULL when current_week_flag is populated instead
current_week_flag	VARCHAR	yes	Populated instead of current_week_count when no number was published: U=unavailable, -=no reported cases, N=not reportable in this jurisdiction, NN=not nationally notifiable, NP=nationally notifiable but not published
previous_52_week_max	VARCHAR	yes	Maximum weekly case count over the previous 52 weeks the condition was reportable; NULL when previous_52_week_max_flag is populated instead
previous_52_week_max_flag	VARCHAR	yes	Populated instead of previous_52_week_max when no number was published (same codes as current_week_flag)
cumulative_ytd_current_year	VARCHAR	yes	Cumulative year-to-date case count for mmwr_year (partial year when the condition was reportable less than the full year); NULL when cumulative_ytd_current_year_flag is populated instead
cumulative_ytd_current_year_flag	VARCHAR	yes	Populated instead of cumulative_ytd_current_year when no number was published (same codes as current_week_flag)
cumulative_ytd_previous_year	VARCHAR	yes	Cumulative year-to-date case count for the prior MMWR year, same week cutoff; NULL when cumulative_ytd_previous_year_flag is populated instead
cumulative_ytd_previous_year_flag	VARCHAR	yes	Populated instead of cumulative_ytd_previous_year when no number was published (same codes as current_week_flag)
location1	VARCHAR	yes	State/territory name; populated only for individually-mappable reporting_area rows, not regional/national aggregates
location2	VARCHAR	yes	Region/national grouping name; populated only for aggregate reporting_area rows, not individual states
sort_order	VARCHAR	yes	Source-assigned display sort key
type	VARCHAR	no	Constant record-type discriminator cdc_nndss_weekly

cdc_school_vaccination_coverage · table

Annual kindergarten vaccination coverage and exemption rates, from CDC's School Vaccination Assessment Program (SchoolVaxView). National and state grain, by school year (e.g. "2025-26"); no county grain. vaccine covers DTP/DTaP/DT, MMR, MMR (PAC, Puerto Rico/American Samoa/Commonwealths reporting), Polio, Hepatitis B, Varicella, and a separate "Exemption" row-type (dose distinguishes Medical/Non-Medical/Any Exemption for those rows). geography_type is National (geography is either "United States" — weighted national estimate — or "U.S. Median" — median of state estimates, NOT the same number) or States (geography is the state name). coverage_estimate is a percentage; confirmed live 2026-09-27 against CDC's Sept 24, 2026 measles update: MMR/United States is 92.4% for 2025-26, down from 95.2% for 2019-20, matching the cited figures exactly. number_of_exemptions is populated only for Exemption rows. Complements cdc_covid_vaccinations (COVID-only, national, frozen at 2023-05-10) with a live, routinely-updated non-COVID childhood vaccination series.

Column	Type	Null	Description
vaccine	VARCHAR	yes	Vaccine (DTP, DTaP, or DT; MMR; MMR (PAC); Polio; Hepatitis B; Varicella) or 'Exemption' for exemption-rate rows
dose	VARCHAR	yes	Dose/status description (e.g. '2 Doses (or disease history)', 'UTD (or disease history)'); for vaccine='Exemption' this is Medical Exemption, Non-Medical Exemption, or Any Exemption instead
geography_type	VARCHAR	yes	National or States
geography	VARCHAR	yes	State name, or (for geography_type=National) 'United States' (weighted national estimate) or 'U.S. Median' (median of state estimates)
school_year	VARCHAR	yes	School year, e.g. 2025-26
coverage_estimate	VARCHAR	yes	Coverage or exemption percentage
population_sample_size	VARCHAR	yes	Kindergartner population size the estimate is based on
percent_surveyed	VARCHAR	yes	Percent of the kindergartner population represented by the survey
foot_notes	VARCHAR	yes	Source footnote text, when present
number_of_exemptions	VARCHAR	yes	Exemption count; populated only for vaccine='Exemption' rows
survey_type	VARCHAR	yes	Survey methodology description (varies by state/year; public and private-school methodology often differ within one row)
type	VARCHAR	no	Constant record-type discriminator cdc_school_vaccination_coverage

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)

va_medical_facilities · table

Veterans Health Administration (VHA) medical-facility roster with geocoding — station number, facility type, name, address, city, state, ZIP, and (lat, lon). Static-snapshot table: the whole layer is re-paginated and the partition overwritten each run; history lives in Iceberg snapshots. Enables drive-time / distance-to-nearest VA-medical-facility computation for a veteran-population question (e.g. joined to census.acs_veterans by state or county). Cross-checked live 2026-09-13 against the real Palo Alto VA Medical Center (STA_NO='640'): city=Palo Alto CA 94304, LAT/LON = (37.40132, -122.13911), matches the real 3801 Miranda Ave campus location. Facility-type coverage (facility_type, 1696 rows total, confirmed live 2026-09-13): PCCBOC 414, OOS 400, VTCR 318, MSCBOC 289, VAMC 171, MVCTR 73, DRRTP 15, HCC 12, CLC 2, MCS 2 — these 10 codes are the complete set present in the layer. VTCR (Vet Center) and MVCTR (Mobile Vet Center) together are 391 rows (23% of the table) — Vet Centers ARE included here, correcting this table's earlier sourcing research, which had assumed (without checking the live facility_type distribution) that this HIFLD layer was VHA-medical-only. None of the 10 codes correspond to a National Cemetery Administration or Veterans Benefits Administration facility, so this layer does not carry cemeteries or benefits regional offices; VA's own Facilities API (developer.va.gov, self-service API key required) would be needed to add them. Source: the HIFLD-hosted mirror of VA's VAST (VHA Site Tracking) database at services2.arcgis.com/VFLAJVozK0rtzQmT — an individually-hosted ArcGIS mirror re-verifying VA's public-domain federal data (same accepted mirror pattern as housing.opportunity_zones). Re-verify-if-404s risk: if the HIFLD mirror stops serving this layer, the fallback is VA's own developer.va.gov Facilities API, which needs an instant-issuance self-service key and would broaden coverage to Vet Centers, cemeteries, and benefits offices in return. street_address (from ArcGIS S_ADD1) is populated almost exclusively for Vet Center/Mobile Vet Center rows (VTCR 307/318, MVCTR 73/73 — confirmed live) — every other facility_type, including VAMC itself (1/171), mostly carries city/state/ZIP without a street. Use (city, state, zip) as the mailing/routing granularity when street_address is missing; (lat, lon) remain populated for every row and are the reliable input for distance computation.

Column	Type	Null	Description
station_number	string	no	VA station number (STA_NO) — unique facility ID within the VHA system (PK)
parent_station_number	string	yes	Station number of the parent facility (PAR_STA_NO), when this row is a satellite/sub-facility
facility_type	string	yes	Short code identifying the facility category — VAMC (medical center), HCC (Health Care Center), MSCBOC/PCCBOC (Community-Based Outpatient Clinic variants), CLC (Community Living Center), DRRTP (Domiciliary Residential Rehabilitation), OOS (Other Outpatient Service), VTCR (Vet Center), MVCTR (Mobile Vet Center), MCS (a small, otherwise-unconfirmed clinic code — 2 rows). VA's own facility-type taxonomy; see the table comment for the full observed distribution and what's NOT included.
facility_name	string	yes	Facility display name (STA_NAME), e.g. 'Palo Alto VA Medical Center'
street_address	string	yes	Street address (S_ADD1). Populated almost exclusively for Vet Center/Mobile Vet Center rows; NULL for most VAMC/CBOC/HCC rows, which carry city/state/ZIP without a street. (lat, lon) remain populated for every row.
city	string	yes	City (S_CITY)
state	string	yes	2-letter USPS state abbreviation (S_STATE)
zip	string	yes	5-digit ZIP code (S_ZIP)
county_name	string	yes	County name (CNAME) — as published by HIFLD, not joined to a FIPS code here
latitude	double	yes	Facility latitude (LAT) — populated for every row (unlike street_address)
longitude	double	yes	Facility longitude (LON) — populated for every row (unlike street_address)
homeless_poc	string	yes	Name of the facility's Homeless Program point of contact (Homeless_POC), when set
homeless_poc_email	string	yes	Email address of the Homeless Program point of contact (Homeless_POC_Email), when set

va_access_to_care_wait_times · table

VA Access to Care facility-level appointment wait times, by facility and specialty — closes the wait-time/access-to-care gap in va_medical_facilities (which carries only identity/location). One row per (facility, specialty) as of the source's daily sliceEndDate. established_patient_wait_days / new_patient_wait_days are VA's own "estWaitTime"/"newWaitTime" days-to-third-next-available-appointment metrics; avg_wait_time_days is carried but was N/A for every row observed live 14 Sep 2026 (VA appears to have stopped publishing it — not fabricated as zero). Covers 20 specialty categories (AUDIOLOGY, CARDIOLOGY, DENTAL, DERMATOLOGY, ENDOCRINOLOGY, GASTROENTEROLOGY, NEUROLOGY, OB/GYN, OCCUPATIONAL THERAPY, ORTHOPEDICS, PAIN MEDICINE, PHYSICAL THERAPY, PODIATRY, PRIMARY CARE, PULMONOLOGY, SLEEP MEDICINE, UROLOGY, WOMEN'S HEALTH, OPHTHALMOLOGY, OPTOMETRY) — no dedicated Mental Health code exists in this legacy system (confirmed live: every apptType integer not in this list returns a byte-identical fallback response, not a Mental Health metric). The source's patientType query parameter (New_Patient/Established_Patient) was confirmed live to have no effect on the response — every row already carries both wait-time columns regardless — so it is a fixed request parameter here, not a dimension. Snapshot table: the whole cross-product (state x specialty) is re-fetched and each state partition overwritten every run; history lives in Iceberg snapshots, not a year dimension. Source: VA's own public www.accesstocare.va.gov/PWT/getRawData API (no key, no auth; verified live against the real Antelope Valley VA Clinic, Lancaster CA — matches its known address and reported a 15-day AUDIOLOGY wait as of 2026-09-13). Coverage: 50 states + DC + PR (same set as housing.hmda_lar). Truncation risk: the source returns a fixed legacy 32,768-byte response buffer, null-padded — not paginated. The largest state/specialty observed live (TX, PRIMARY CARE) used 28,841 bytes (88% of capacity); if a single state's facility count grows enough to exceed the buffer, that state's response would silently truncate mid-row rather than error. Re-verify content length if a future DQ run shows an implausibly low per-state facility count.

Column	Type	Null	Description
facility_id	string	no	VA station ID (PK component; joins to health.va_medical_facilities.station_number)
visn	integer	yes	Veterans Integrated Service Network number
facility_name	string	yes
facility_type	string	yes	e.g. 'Clinic', 'Health Care System'
address	string	yes
city	string	yes
state	string	yes
zip	string	yes
phone	string	yes
latitude	double	yes
longitude	double	yes
appt_type_name	string	yes	Specialty/appointment category name, as published by the source (PK component)
established_patient_wait_days	integer	yes	Days to third-next-available appointment for an established patient; null for the source's 'N/A' sentinel
new_patient_wait_days	integer	yes	Days to third-next-available appointment for a new patient; null for the source's 'N/A' sentinel
avg_wait_time_days	integer	yes	Carried for schema completeness; observed N/A (null) for every row live 14 Sep 2026 — see table comment
same_day_access	string	yes	'True'/'False' — facility offers same-day access for this specialty
slice_end_date	string	yes	As-of date/time of this metric slice (MM/DD/YYYY HH:MM:SS). The sentinel '01/01/0001 00:00:00' means this facility/specialty has no current wait-time slice (both wait-day columns are null in that case, not fabricated as zero).
has_emergency_dept	string	yes
has_urgent_care	string	yes
same_day_primary_care	string	yes
same_day_mental_health	string	yes
walk_in_primary_care	string	yes
walk_in_mental_health	string	yes
telehealth_primary_care	string	yes
telehealth_mental_health	string	yes
non_va_wait_days	integer	yes	Community Care (non-VA referral) wait days, when published; null for the source's 'N/A' sentinel
pct_accepting_medicaid	double	yes
pct_accepting_medicare	double	yes
metro_area	string	yes
data_source_system	string	yes	Underlying VA scheduling system the metric was sourced from (e.g. 'VistA'); blank when the row has no current slice

ssa_oasdi_county · table

SSA "OASDI Beneficiaries by State and County" (Tables 4 & 5, December snapshot) — one row per county per state per year (plus one state-total row per state/year), wide across the 8 beneficiary-type breakdowns SSA publishes: retired workers, spouses/children of retired workers, widow(er)s-and-parents, children of deceased workers (survivors), and — the reason this table was sourced — disabled_workers_count/amount, the SSDI caseload/payment figures no other table in this warehouse carries (census.acs_disability is self-reported disability prevalence, a different measure entirely). Sourced for shock-vs-disability-rolls analysis (Autor-Dorn-Hanson style): actual program enrollment against a labor-market shock, rather than a self-reported-prevalence proxy. Deliberately excludes the same tables' "Aged 65 or older, by sex" (Men/Women) columns — a different demographic cross-tab of the same totals, not part of the beneficiary-type breakdown this table carries. Coverage: 50 states, no DC (SSA's per-state directory listing has no dc.xlsx — confirmed live 13 Sep 2026; DC's OASDI figures exist only in the national/state-level Table 2/3 files, out of scope for this county-grain table) and no territories (PR/VI are published but excluded — no matching geo.counties FK). county_fips is NULL for each state's own total row (SSA labels it "Total, " with its own state FIPS in the ANSI-code column rather than a real county code) — kept, not dropped, matching this schema's kept-total-row convention elsewhere; filter WHERE county_fips IS NOT NULL for a clean county-only sum. Akamai UA gate: www.ssa.gov 403s a blank UA and a plain "GovData/1.0" identifier alike (confirmed live) but passes "Wget/1.21.3" — the opposite of CDC WONDER/BLS's own UA gotchas (those block a self-identifying UA and require blank/curl-like instead). The gate applies to the FIRST request, not just a re-download — both the source: block's headers and SsaOasdiCountyTransformer's own re-download need this UA set explicitly. minYear 2005 (raw, pre-dataLag) reaches back to the oldest XLSX-format county file, FY2004 (2003 and earlier are PDF-only scans, not worth backfilling).

Column	Type	Null	Description
state_abbr	string	no	2-letter USPS state code (FK to geo.state_ref) — from which per-state file this row came, not a published column
county_fips	string	yes	5-digit county FIPS (FK to geo.counties); NULL on each state's own total row
county_name	string	no	County name, or "Total, " for the state-total row
data_year	integer	no	December-snapshot year this file reports (SSA's own report year, not a fiscal year)
total_count	double	yes	Total OASDI beneficiaries in current-payment status
retired_workers_count	double	yes
spouses_retirement_count	double	yes	Spouses of retired workers
children_retirement_count	double	yes	Children of retired workers
widowers_parents_count	double	yes	Widow(er)s and parents (survivors)
children_survivors_count	double	yes	Children of deceased workers (survivors)
disabled_workers_count	double	yes	Disabled workers — the SSDI caseload figure this table was sourced for
spouses_disability_count	double	yes	Spouses of disabled workers
children_disability_count	double	yes	Children of disabled workers
total_amount	double	yes	Total monthly benefit amount, $1000s
retired_workers_amount	double	yes	$1000s
spouses_retirement_amount	double	yes	$1000s
children_retirement_amount	double	yes	$1000s
widowers_parents_amount	double	yes	$1000s
children_survivors_amount	double	yes	$1000s
disabled_workers_amount	double	yes	Disabled-worker monthly benefit amount, $1000s — the SSDI payment figure this table was sourced for
spouses_disability_amount	double	yes	$1000s
children_disability_amount	double	yes	$1000s

medicare_geographic_variation · table

CMS "Medicare Geographic Variation - by National, State & County" Public Use File — one row per (year, geography, age group), 2014-2024. Sourced to close the Medicare fee-for-service spending-by-state gap #67 identified: USAspending's award-based federal-spending totals omit ~$1.0T (FY2023) in Medicare FFS payments entirely, since they never post as discrete awards. Curated subset, not the full PUF: CMS's source file has ~250 columns — payment, standardized-payment, and utilization-rate detail broken out separately for inpatient, outpatient, ASC, SNF, IRF, LTCH, home health, hospice, ED, procedures, tests, imaging, DME, ambulance, and several Prevention Quality Indicator (PQI) admission-rate measures, each further split by raw vs. per-capita vs. per-user vs. pct-of-total. This table carries only the headline totals (total/inpatient/outpatient/DME/hospice payment and per-capita figures) plus beneficiary counts and Medicare Advantage participation — the columns the spending-by-state question needs. Extending to the full category breakdown is a straightforward follow-up (same source, same grain) if a future need requires inpatient-specific or PQI-specific detail. Standardized vs. raw payment: total_medicare_stdz_payment removes each area's geographic price adjustment (wage index, GPCI, etc.), isolating utilization intensity from local price levels — the figure to compare how much care an area's beneficiaries use. total_medicare_payment is the actual dollar amount paid — the figure for a spending/fiscal question. Use stdz for utilization comparison, raw for spending totals. Suppression: CMS suppresses small-cell values (denoted '*' in the source) for privacy — these parse to NULL here via TRY_CAST rather than a fabricated 0. Small counties and the '<65'/'>=65' age-group splits are the rows most often affected; 'All' age-group rows are rarely suppressed. Geography: geoevel is 'Nationa, 'State', or 'County'. geo_fips is the 2-digit state FIPS or 5-digit county FIPS (FK to geo.state_ref/geo.counties); NULL for National rows and for the 'ZZ' catch-all state code CMS uses for unknown/foreign/territory beneficiaries (fully suppressed in every year checked). age_level is 'All', '<65', or '>=65' — filter to 'All' for a single row per geography/year unless the age split itself is needed. URL drift caveat: CMS's data.cms.gov file path encodes an upload date (sites/default/files/2026-04/...) that changes when CMS re-publishes the combined file with a new year appended. Re-verify the URL against the dataset's landing page (data.cms.gov/provider-summary-by-type-of-service/medicare-geographic-comparisons/ medicare-geographic-variation-by-national-state-county) if this table stops updating.

Column	Type	Null	Description
data_year	integer	no	Calendar year this row reports (YEAR)
geo_level	string	no	'National', 'State', or 'County' (BENE_GEO_LVL)
geo_desc	string	yes	State abbreviation, or 'State-County name' for county rows (BENE_GEO_DESC)
geo_fips	string	yes	2-digit state or 5-digit county FIPS (FK to geo.state_ref/geo.counties); NULL for National rows and the 'ZZ' catch-all (BENE_GEO_CD)
age_level	string	no	'All', '<65', or '>=65' (BENE_AGE_LVL)
beneficiaries_total	long	yes	Total Medicare beneficiaries (BENES_TOTAL_CNT)
beneficiaries_original_medicare	long	yes	Beneficiaries in Original (fee-for-service) Medicare (BENES_OM_CNT)
beneficiaries_medicare_advantage	long	yes	Beneficiaries in Medicare Advantage (BENES_MA_CNT)
ma_participation_rate	double	yes	Medicare Advantage participation rate (MA_PRTCPTN_RATE)
total_medicare_payment	double	yes	Total actual Medicare FFS payment, USD (TOT_MDCR_PYMT_AMT) — the spending-by-geography figure
total_medicare_stdz_payment	double	yes	Total price-standardized Medicare FFS payment, USD (TOT_MDCR_STDZD_PYMT_AMT) — utilization-intensity figure, price effects removed
total_medicare_payment_per_capita	double	yes	Actual Medicare FFS payment per beneficiary, USD (TOT_MDCR_PYMT_PC)
total_medicare_stdz_payment_per_capita	double	yes	Price-standardized Medicare FFS payment per beneficiary, USD (TOT_MDCR_STDZD_PYMT_PC)
inpatient_payment_per_capita	double	yes	Inpatient hospital Medicare payment per beneficiary, USD (IP_MDCR_PYMT_PC)
outpatient_payment_per_capita	double	yes	Outpatient hospital Medicare payment per beneficiary, USD (OP_MDCR_PYMT_PC)
dme_payment_per_capita	double	yes	Durable medical equipment Medicare payment per beneficiary, USD (DME_MDCR_PYMT_PC)
hospice_payment_per_capita	double	yes	Hospice Medicare payment per beneficiary, USD (HOSPC_MDCR_PYMT_PC)

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.

View — columns are resolved by the query engine at runtime.

cdc_wonder_multiple_cause_of_death_1999_2024 · view

National deaths by year and MCD Drug/Alcohol Induced Cause category, 1999-2024 — cdc_wonder_multiple_cause_of_death (D77, 1999-2020) unioned with cdc_wonder_multiple_cause_of_death_2018_2024 (D157, 2018-2024), preferring D157 for the 2018-2020 years both cover so the combined series never double-counts. This is the multiple-cause series (a cause counts if listed anywhere among a death's up to 20 causes) — the standard basis for drug overdose mortality trend analysis.

View — columns are resolved by the query engine at runtime.

who_health_snapshot · view

Headline WHO GHO indicators pivoted wide: one row per country x year with life expectancy, healthy life expectancy, under-5 mortality, and UHC service coverage (country rows, both-sexes). International complement to the U.S. openFDA tables; join to ref.countries for names/regions.

View — columns are resolved by the query engine at runtime.

drug_profile · view

Complete drug profile: NDC product joined to FDA approval record and RxNorm concept. Left joins preserve NDC products that lack an approval record or rxcui mapping.

View — columns are resolved by the query engine at runtime.

trial_full · view

Denormalised clinical trial view: one row per trial with conditions and interventions aggregated into semicolon-delimited strings.

View — columns are resolved by the query engine at runtime.

medicaid_drug_spend · view

Medicaid drug utilization enriched with NDC product details and RxNorm name, at the same (state, year, quarter, ndc, utilization_type) grain as medicaid_drug_utilization — no aggregation. Enables spend analysis by brand, generic, labeler, and rxcui. For annual state+drug totals see drug_spend_trends; for state+year totals across all drugs see medicaid_spend_by_state_geo.

View — columns are resolved by the query engine at runtime.

public_health_indicators · view

State-year summary combining CDC mortality totals (total_deaths, avg_age_adjusted_rate, distinct_causes) with BRFSS risk-factor averages (brfss_question_count, avg_risk_factor_pct) via FULL OUTER JOIN — rows where BRFSS has data but mortality does not appear with NULL death columns and vice versa. For a mortality-driven version with state geography see health_social_equity; for unaggregated per-cause/per-question detail see mortality_brfss_by_state.

View — columns are resolved by the query engine at runtime.

hospital_quality_by_state · view

State-level hospital quality summary: counts, average ratings, and quality tier breakdown, aggregated from all cms_hospital_quality facility rows in the state. For facility-level detail with county geography see hospital_with_geography; for a county-level aggregate see hospital_county_summary.

View — columns are resolved by the query engine at runtime.

hospital_with_geography · view

Hospital quality records enriched with county FIPS, county name, and rural-urban classification via the dominant-county ZIP crosswalk. One row per facility (same grain as cms_hospital_quality) — see hospital_quality_by_state for a state-level aggregate and hospital_county_summary for a county-level aggregate.

View — columns are resolved by the query engine at runtime.

medicaid_spend_by_state_geo · view

Medicaid spend aggregated by state and year across all drugs, enriched with state name and FIPS for choropleth-ready geographic analysis. For a per-drug breakout see drug_spend_trends, and for unaggregated per-quarter rows see medicaid_drug_spend.

View — columns are resolved by the query engine at runtime.

mortality_brfss_by_state · view

CDC annual mortality joined to BRFSS risk factors, both enriched with state geography. One row per state/year/cause/question combination (LEFT JOIN, so every mortality cause row is paired with every matching BRFSS question row) — filter on cause_name and question to avoid cross-product explosion. For pre-aggregated state/year totals see public_health_indicators (full outer join, retains BRFSS-only rows) or health_social_equity (mortality-driven, with geography).

View — columns are resolved by the query engine at runtime.

drug_manufacturer_span · view

Connects drug manufacturers across FDA approval, NDC catalog, and Medicaid spend. One row per labeler+application. Enables manufacturer market-size and reach analysis.

View — columns are resolved by the query engine at runtime.

rxnorm_medicaid_bridge · view

Connects RxNorm clinical concepts to Medicaid spending via NDC codes. One row per rxcui — enables clinical terminology to spending analysis.

View — columns are resolved by the query engine at runtime.

hospital_county_summary · view

Aggregates CMS hospital quality metrics to county level via ZIP-county crosswalk. One row per county — enables county-level healthcare access and quality analysis. See hospital_with_geography for the underlying facility-level rows with county geography, and hospital_quality_by_state for a state-level aggregate.

View — columns are resolved by the query engine at runtime.

drug_spend_trends · view

Annual Medicaid drug spend aggregated from quarterly utilization data, enriched with NDC product details. One row per state+year+ndc — enables year-over-year spend trend analysis by brand, generic, and labeler. For unaggregated per-quarter rows see medicaid_drug_spend; for state+year totals across all drugs see medicaid_spend_by_state_geo.

View — columns are resolved by the query engine at runtime.

health_social_equity · view

State-year health equity view: CDC annual mortality (deaths summed across all causes — cause_name is not broken out in this view) layered with BRFSS behavioral risk-factor prevalence, enriched with state name/FIPS geography. One row per state+year (LEFT JOIN from mortality, so BRFSS-only state-years are dropped). For a per-cause, per-question breakdown see mortality_brfss_by_state; for a version that also retains BRFSS-only rows see public_health_indicators.

View — columns are resolved by the query engine at runtime.

weather_health_correlation · view

Links annual CDC mortality to NOAA CDO monthly climate summaries by state and year. Temperature and precipitation are pivoted from long to wide via conditional aggregation. One row per state+year — enables climate-health correlation analysis.

View — columns are resolved by the query engine at runtime.