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

31 datasets · 189 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.

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
`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
`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 joins (text) to sec schema company facts and ref.gleif_entities.legal_name.

Column	Type	Null	Description
`application_number`	string	no	Application number e.g. NDA022369 — PK
`sponsor_name`	string	yes	Applicant/manufacturer — text FK to sec and ref.gleif_entities
`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 most recent submission action
`review_priority`	string	yes	STANDARD or PRIORITY

fda_drug_recalls · table¶

FDA drug enforcement (recall) actions. Joins to fda_ndc_products via labeler_name (text) or openfda.product_ndc (structural, ~70% populated).

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_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. Complementary to fda_drug_recalls; same classification scheme (Class I/II/III).

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. Primary key: nct_id. Joins to clinical_trial_conditions (nct_id) and clinical_trial_interventions (nct_id).

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

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 rates and counts by demographic group. Warning: data frozen at 2023-05-12; no recent updates.

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. Primary key: facility_id.

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"

medicaid_drug_utilization · table¶

Medicaid drug utilization and reimbursement by state, drug (NDC), year/quarter. Composite PK: (state, ndc, year, quarter). 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. Unions annual (bi63-dtpu) and weekly provisional (muzy-jte6) data. source_type dimension distinguishes the two.

Column	Type	Null	Description
`year`	VARCHAR	yes	Data year of deaths; MMWR year for weekly rows
`week_ending_date`	VARCHAR	yes	Week-ending date; weekly rows only, null for annual
`state`	VARCHAR	yes	State/jurisdiction; annual includes a United States total row
`cause_name`	VARCHAR	yes	Cause-of-death name; NCHS leading cause (annual) or COVID-19 (weekly)
`full_cause_name`	VARCHAR	yes	Detailed cause name (NCHS 113-cause for annual; COVID-19 detail for weekly)
`deaths`	VARCHAR	yes	Number of deaths for the cause/period (COVID-19 deaths for weekly rows)
`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

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.

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)

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.