FDA AEMS Adverse Events Dataset

About the Dataset

The FDA AEMS (Adverse Event Monitoring System) Adverse Events Dataset delivers analysis-ready drug and medical device adverse event reports from the U.S. Food and Drug Administration (FDA) — the unified successor to FAERS (FDA Adverse Event Reporting System) and MAUDE (Manufacturer and User Facility Device Experience). 9M+ drug safety reports and 14M+ device adverse event reports are sourced directly from the openFDA real-time feed, updated daily as new data is published, and delivered with native BOOLEAN flags, MedDRA-coded reactions, full untruncated narratives, and pre-built PRR (Proportional Reporting Ratio) signal-detection aggregates — built for pharmacovigilance analysts, drug safety scientists, regulatory affairs teams, medical device safety engineers, biotech R&D, and post-market surveillance programs.

Get Full Access | Snowflake Marketplace | Free Trial

Quick Access

Drug Stream	DRUG_REPORTS · DRUG_REACTIONS · DRUG_SUBSTANCES · DRUG_REPORT_SOURCES · DRUG_REPORT_DUPLICATES
Device Stream	DEVICE_REPORTS · DEVICE_DETAILS · DEVICE_NARRATIVES · DEVICE_PATIENTS · DEVICE_PROBLEMS
Reference Tables	DEVICE_CLASSIFICATION · DRUG_NDC_DIRECTORY
Signal Aggregates	DRUG_REACTION_ASSOCIATIONS · DRUG_SAFETY_TRENDS · DRUG_MANUFACTURER_EVENTS · DEVICE_MANUFACTURER_EVENTS · DEVICE_EXECUTIVE_DASHBOARD
Sources	openFDA Drug Event · openFDA Device Event · openFDA Device Classification · openFDA Drug NDC Directory
Coverage	2020-present (pre-2020 backfill available on request: drug to 2004, device to 1991)
Records	9M+ drug reports · 14M+ device reports · 27M+ drug reactions · 32M+ device narratives
Update Frequency	Weekly (drug + device events) · Monthly (NDC + device classification reference)
Automatic Updates	Our pipeline monitors openFDA daily and loads new bulk releases within hours of publication — no manual refresh needed
Platforms	Snowflake Marketplace · Databricks Delta Sharing

Pharmacovigilance & Post-Market Safety Surveillance Overview

The FDA AEMS Adverse Events Dataset consolidates two of the most-queried public safety surveillance systems — drug adverse events (formerly FAERS) and medical device adverse events (formerly MAUDE) — into a single, consistently-modeled Snowflake share with shared design patterns across both streams:

• Native BOOLEAN flags for seriousness, death, hospitalization, life-threatening, disability, congenital anomaly, expedited reporting, single-use, implantable, and product-problem indicators — query WHERE serious = TRUE directly without VARCHAR-to-boolean gymnastics

• MedDRA preferred terms (reaction_meddra_pt) for drug reactions with title-case spelling per the openFDA standard ('Death', not 'DEATH')

• Full untruncated device narratives — no 16K-character limit on DEVICE_NARRATIVES.text; both Description of Event AND Manufacturer Narrative types preserved

• Full openFDA enrichment on drug substances — substance_name, rxcui, unii, nui, all 4 pharm_class_* variants (EPC, MoA, PE, CS), spl_id, spl_set_id, package_ndc, application_number

• 2017-expanded patient demographics on devices — patient_ethnicity, patient_race, patient_problems, outcome and treatment sequence numbers

• Pre-built PRR signal-detection — DRUG_REACTION_ASSOCIATIONS carries Proportional Reporting Ratio with 95% confidence intervals (Evans et al. 2001) plus an is_signal BOOLEAN flag (PRR>=2.0 AND N>=3 AND lower-95>=1.0)

• One row per report, always current — every view returns the latest version of each report keyed on safety_report_id (drugs) or report_number (devices). No batch-history filtering, no is_latest = TRUE clauses — just query and go.

Why FDA AEMS, Not FAERS or MAUDE Separately

FDA consolidated the FAERS drug-adverse-event database and the MAUDE medical-device-adverse-event database under the unified Adverse Event Monitoring System (AEMS) brand on openFDA. The two streams share a common surveillance philosophy (voluntary reporting, MedDRA coding where applicable, semiquarterly bulk releases) but live as separate tables here because their schemas diverge sharply — drug events key on safety_report_id while device events key on report_number. This product gives you both under one Marketplace listing with shared design conventions so a query learned on one stream transfers cleanly to the other.

Entity Relationship Diagram

FDA AEMS Entity Relationship Diagram

Primary join keys:

• Drug stream: JOIN every drug table on safety_report_id — DRUG_REPORTS is the header, DRUG_REACTIONS / DRUG_SUBSTANCES / DRUG_REPORT_SOURCES / DRUG_REPORT_DUPLICATES are line-level children

• Device stream: JOIN every device table on report_number — DEVICE_REPORTS is the header, DEVICE_DETAILS / DEVICE_NARRATIVES / DEVICE_PATIENTS / DEVICE_PROBLEMS are line-level children

• Reference enrichment: DEVICE_DETAILS.device_report_product_code joins DEVICE_CLASSIFICATION.product_code (near-complete coverage). DRUG_SUBSTANCES.product_ndc joins DRUG_NDC_DIRECTORY.product_ndc (SPLIT/FLATTEN required — see DRUG_NDC_DIRECTORY section)

One row per report, always current. Each view returns the latest version of every report — one row per safety_report_id (drugs) or report_number (devices). No WHERE is_latest = TRUE, no batch-history JOINs, no deduplication step. Query any table directly and you get the current state of FDA's data.

Drug Adverse Event Tables (formerly FAERS)

The drug stream covers spontaneous, mandatory-from-manufacturer, and study-derived adverse event reports for FDA-regulated drugs, biologics, and combination products. JOIN every drug table on safety_report_id.

DRUG_REPORTS — Drug Safety Report Headers

Header-level drug safety reports — one row per safety_report_id. Carries patient demographics (age group, onset age, sex, weight, ethnicity when present), outcome severity (seriousness_death, seriousness_hospitalization, seriousness_life_threatening, seriousness_disabling, seriousness_congenital_anomaly, seriousness_other), the native BOOLEAN serious flag, reporter context (reporter_country, reporter_qualification_decoded), sender / receiver organizations, and the high-impact patient_summary_narrative field for full clinical context.

Key columns (full schema: 40+ columns):

Column	Type	Description
safety_report_id	VARCHAR	Primary key — FDA safety report identifier. JOIN target for all drug child tables.
safety_report_version	VARCHAR	Version number — FDA reissues updated reports as new info arrives
receive_date	DATE	Date FDA received the report (default cutoff: 2020-01-01)
serious	BOOLEAN	Serious adverse event flag — death, hospitalization, life-threatening, disability, congenital anomaly, or required intervention
seriousness_death	BOOLEAN	TRUE when event resulted in patient death
seriousness_hospitalization	BOOLEAN	TRUE when event caused or prolonged hospitalization
seriousness_life_threatening	BOOLEAN	TRUE when event was life-threatening
patient_onset_age	NUMERIC	Patient age at onset (unit in patient_onset_age_unit — 800=Decade through 805=Hour)
patient_age_group	VARCHAR	Bucket: neonate / infant / child / adolescent / adult / elderly
patient_sex_decoded	VARCHAR	Male / Female / Unknown
patient_death_present	BOOLEAN	TRUE when patient.patientdeath block was present (covers cases where date is unparseable)
patient_summary_narrative	TEXT	Free-text clinical case summary — highest-value field for context
reporter_qualification_decoded	VARCHAR	Physician / Pharmacist / Other HCP / Lawyer / Consumer
primary_source_country	VARCHAR	ISO country code from primary source
occur_country	VARCHAR	ISO country where event occurred
report_type_decoded	VARCHAR	Spontaneous / Report from study / Other / Not available
reaction_count, drug_count	NUMERIC	Number of reactions / drugs on this report — pre-computed for quick filtering

DRUG_REACTIONS — MedDRA-Coded Adverse Reactions

Line-level adverse reactions per safety report. Multiple rows per safety_report_id (one per reaction).

Column	Type	Description
safety_report_id	VARCHAR	FK to DRUG_REPORTS
reaction_meddra_pt	VARCHAR	MedDRA Preferred Term — title-case (e.g. 'Death', not 'DEATH'). UPPER() for case-insensitive filtering.
reaction_meddra_pt_code	VARCHAR	Numeric MedDRA preferred-term code — JOIN to external MedDRA dimension tables
reaction_meddra_version	VARCHAR	MedDRA dictionary version (e.g. '26.1')
reaction_outcome_decoded	VARCHAR	Recovered / Recovering / Not recovered / Recovered with sequelae / Fatal / Unknown

DRUG_SUBSTANCES — Drugs Implicated in Each Report

Line-level drugs implicated in each report with full openFDA enrichment. Multiple rows per safety_report_id.

Column	Type	Description
safety_report_id	VARCHAR	FK to DRUG_REPORTS
substance_name	VARCHAR	openFDA-normalized active ingredient (e.g. 'METFORMIN' covers Glucophage, Fortamet) — primary key for substance-level analytics
medicinal_product	VARCHAR	Reported drug name as entered by the reporter (often brand or local name)
drug_characterization_decoded	VARCHAR	Suspect / Concomitant / Interacting
brand_name	VARCHAR	Brand / trade name from openFDA enrichment
generic_name	VARCHAR	Generic / non-proprietary name
manufacturer_name	VARCHAR	Manufacturer from openFDA enrichment
product_ndc	VARCHAR	National Drug Code — may be ; -separated list when one substance has multiple NDCs. SPLIT+FLATTEN before JOIN to DRUG_NDC_DIRECTORY
pharm_class_epc	VARCHAR	Established Pharmacologic Class (e.g. 'HMG-CoA Reductase Inhibitor [EPC]')
pharm_class_moa, pharm_class_pe, pharm_class_cs	VARCHAR	Mechanism of Action / Physiologic Effect / Chemical Structure pharm classes
rxcui	VARCHAR	RxNorm CUI — JOIN to RxNorm for clinical-vocabulary cross-references
unii	VARCHAR	FDA UNII (Unique Ingredient Identifier)
application_number	VARCHAR	FDA application number (NDA/ANDA/BLA prefix)
drug_indication, drug_dosage_text, drug_start_date, drug_end_date	VARCHAR/DATE	Indication, dosage, therapy window
action_drug_decoded	VARCHAR	Drug withdrawn / Dose reduced / Dose increased / Dose not changed / Unknown / Not applicable

DRUG_REPORT_SOURCES — Reporter / Sender / Receiver Provenance

One row per (safety_report_id, source_kind ∈ {primary, sender, receiver}). Lets you trace which organization filed each report, which qualification level (physician, consumer, etc.) the reporter held, and any literature reference for study-derived cases.

DRUG_REPORT_DUPLICATES — Multi-Source Dedup Chain

One row per reportduplicate[] entry — present when the same case was filed by multiple sources (e.g. manufacturer + foreign regulatory authority). Lets you walk the cross-source dedup chain when reconciling reports.

Medical Device Adverse Event Tables (formerly MAUDE)

The device stream covers medical device adverse event reports submitted via the Medical Device Reporting (MDR) regulation. JOIN every device table on report_number.

DEVICE_REPORTS — MDR Report Headers

Header-level medical device reports — one row per report_number. Carries event classification (event_type: Death / Injury / Malfunction / Other), dates (date_received, date_of_event, report_date), native BOOLEAN flags (adverse_event_flag, product_problem_flag, single_use_flag, reprocessed_and_reused_flag, implant_flag), the full manufacturer_contact_* address block, distributor info, recall references (removal_correction_number, pma_pmn_num), and the back-compat first-narrative field mdr_text_narrative.

Column	Type	Description
report_number	VARCHAR	Primary key — MDR (Medical Device Report) number
event_type	VARCHAR	'Death', 'Injury', 'Malfunction', 'Other', 'No answer provided' — title-case
date_received	DATE	Date FDA received the report (default cutoff: 2020-01-01)
date_of_event	DATE	Date the adverse event physically occurred
adverse_event_flag	BOOLEAN	TRUE when event involved patient harm
product_problem_flag	BOOLEAN	TRUE when a product problem contributed — see DEVICE_PROBLEMS for the per-problem rows
single_use_flag, reprocessed_and_reused_flag, implant_flag	BOOLEAN	Native flags for downstream filtering
manufacturer_contact_country, manufacturer_contact_state, manufacturer_contact_city	VARCHAR	Full HQ address block for the manufacturer's reporting contact
distributor_name, distributor_city, distributor_state	VARCHAR	Distributor identification
pma_pmn_num	VARCHAR	Top-level PMA / 510(k) reference
removal_correction_number	VARCHAR	Recall / correction action reference (Z-number)

DEVICE_DETAILS — Device Identification per Report

Line-level device identification — multiple rows per report_number if multiple devices were implicated. Carries brand / generic / model / catalog / lot, parsed numeric device age, the manufacturer's device-side address block, FDA product code, and the openFDA classification cross-reference set (openfda_pma_number, openfda_k_number, openfda_fei_number, openfda_registration_number, openfda_implant_flag, openfda_life_sustain_support_flag, openfda_class_advisor_committee).

The most important field is device_report_product_code — the FDA 3-letter device code — which joins DEVICE_CLASSIFICATION on product_code at near-complete coverage, enriching every device-event row with the full FDA classification context (device class, medical specialty, regulation number, implant flag, life-sustain support flag).

DEVICE_NARRATIVES — Full-Text Event Narratives

Full event / manufacturer-evaluation / additional-narrative text per device adverse event report. Multiple rows per report_number. text_type_code distinguishes 'Description of Event or Problem' from 'Manufacturer Narrative' and 'Additional Manufacturer Narrative'.

No 16K truncation, both Description AND Manufacturer narrative types — narratives carry up to ~10 million characters per row, and reports with multiple narrative types (Description of Event, Manufacturer Narrative, Additional Manufacturer Narrative) keep each one on its own row. Full-text search hits the complete investigative record.

DEVICE_PATIENTS — 2017-Expanded Patient Demographics

One row per patient[] entry per device adverse event report. Carries patient_age + patient_age_unit (YR/MO/DA/WK), patient_sex, patient_weight + patient_weight_unit, plus the 2017-expanded demographics: patient_ethnicity, patient_race, patient_problems (semicolon-joined list of what was happening to the patient), and the sequence_number_outcome / sequence_number_treatment arrays.

DEVICE_PROBLEMS — Product-Problem Codes per Report

One row per (report_number, product_problem) — when a device adverse event involved multiple product problems, each problem gets its own row. Filter with WHERE product_problem = 'Device Sensing Problem' to find every report that cited that specific problem, no string parsing required.

Reference Tables

DEVICE_CLASSIFICATION — FDA Device Product-Code Reference

FDA device product-code reference table — one row per 3-letter product_code (~7K codes). Carries device_name, regulation_number (21 CFR), medical_specialty + description, device_class (1/2/3), and native BOOLEAN flags (implant_flag, life_sustain_support_flag, gmp_exempt_flag, third_party_flag) — filter directly with WHERE implant_flag = TRUE, no string parsing. Includes openFDA cross-reference arrays (openfda_k_number, openfda_fei_number, openfda_registration_number).

Join coverage: Near-complete — virtually every DEVICE_DETAILS row with a device_report_product_code finds a matching DEVICE_CLASSIFICATION.product_code. This is the clean substitute for any device-class enrichment work — use this view in preference to the openFDA enrichment columns directly on DEVICE_DETAILS.

DRUG_NDC_DIRECTORY — National Drug Code Reference

National Drug Code reference — 100K+ NDCs. Carries brand_name, generic_name, labeler_name, manufacturer_name, product_type ('HUMAN PRESCRIPTION DRUG' / 'HUMAN OTC DRUG' / 'VACCINE' / etc.), marketing_category (NDA / ANDA / BLA / OTC monograph final), parsed marketing-start/end/expiration dates, dea_schedule (controlled-substance flag), dosage_form, route, and a full openFDA enrichment block (spl_id, spl_set_id, rxcui, unii, nui, application_number, all 4 pharm_class variants).

JOIN pattern (CRITICAL): when joining from DRUG_SUBSTANCES, the product_ndc column is a ; -separated list of NDCs (one substance can carry multiple NDCs). You MUST SPLIT_TO_TABLE + LATERAL FLATTEN before equality-joining — this boosts the hit rate from ~12% to ~98%. See the example below.

Pre-Built Signal Aggregates (Paid-Only)

These five tables are pre-computed aggregations powered by the underlying drug and device streams. They make pharmacovigilance signal detection and executive reporting one-query operations instead of multi-CTE rollups against the full report history.

DRUG_REACTION_ASSOCIATIONS — PRR Safety Signals

One row per (substance_name, reaction_meddra_pt) pair. Carries the full 2×2 contingency for Proportional Reporting Ratio (PRR): report_count, total_drug_reports, total_reaction_reports, total_reports plus prr_score, prr_lower_95, prr_upper_95 (Evans et al. 2001 formula), and the boolean is_signal flag (TRUE when prr_score >= 2.0 AND report_count >= 3 AND prr_lower_95 >= 1.0 — the FDA's signal threshold). Filter WHERE is_signal = TRUE for emerging safety signals; sort by prr_score DESC within a substance for strongest associations.

DRUG_SAFETY_TRENDS — Time-Series by Substance × Reaction

Monthly / Quarterly / Yearly time-series per (substance, reaction) pair. Carries event_count, serious_events, death_events, serious_rate, death_rate, prior_event_count (LAG), yoy_event_count (year-ago), yoy_change_pct, and trend_direction (INCREASING when YoY >= +20%, DECREASING when <= -20%, STABLE otherwise, NEW when no prior-year comparison). Filter period_type = 'QUARTER' and a substance name for a single drug's trajectory.

DRUG_MANUFACTURER_EVENTS — Drug Manufacturer Rollups

One row per manufacturer with total_events, unique_drugs, all seriousness rollups (serious_events, death_events, life_threatening_events, hospitalization_events, disability_events), drug-characterization splits (suspect / concomitant / interacting), top_substance, top_substance_count, reporting-window dates, and a coarse risk_class banding (HIGH / MEDIUM / LOW / MINIMAL) for at-a-glance triage. Powers competitive intelligence + investor due diligence on the drug side.

DEVICE_MANUFACTURER_EVENTS — Device Manufacturer Rollups

One row per (manufacturer_name, manufacturer_d_country) for geography drill-down. Carries total_reports, event-type splits (death_count, injury_count, malfunction_count, other_count), product-problem / adverse-event rates, unique_devices, reports_last_12_months, top_product_code (JOIN to DEVICE_CLASSIFICATION for context), and the same risk_class banding.

DEVICE_EXECUTIVE_DASHBOARD — Board-Ready Device KPIs

Board-deck-ready KPIs in relational shape. Grain: one row per (manufacturer × device_class × medical_specialty × quarter), with convenience columns report_year and report_quarter. Carries total_reports, rolling_4q_reports (trailing 12-month sum), prior_year_reports, yoy_delta, event-type rates, and top_product_problem + count. Powers BI dashboards without scanning the full DEVICE_REPORTS history.

Getting Started — How to Query FDA AEMS Data

Platform Schema Reference

This dataset is available on both Snowflake and Databricks. Queries use schema-only references — the database is already set by the share or catalog context.

Platform	Schema	Example
Snowflake	DWV	DWV.DRUG_REPORTS
Databricks	fda_aems_dwv	fda_aems_dwv.drug_reports

Data Freshness Check

Quick sanity check — confirms the dataset has loaded recent openFDA data for both streams:

Snowflake

-- Data freshness — should be within ~7 days of today
SELECT 'DRUG_REPORTS' AS stream, MAX(receive_date) AS latest_report
FROM DWV.DRUG_REPORTS
UNION ALL
SELECT 'DEVICE_REPORTS', MAX(date_received)
FROM DWV.DEVICE_REPORTS;

Databricks

Top Drug Adverse Reactions

Find the most commonly reported adverse reactions across all drug reports — the classic pharmacovigilance "what reactions are showing up" question.

Snowflake

SELECT r.reaction_meddra_pt,
       COUNT(*) AS report_count,
       SUM(CASE WHEN rpt.serious THEN 1 ELSE 0 END) AS serious_count
FROM DWV.DRUG_REACTIONS r
JOIN DWV.DRUG_REPORTS rpt
    ON r.safety_report_id = rpt.safety_report_id
GROUP BY r.reaction_meddra_pt
ORDER BY report_count DESC
LIMIT 25;

Databricks

Drug Adverse Events by Substance (with Pharm-Class Enrichment)

Analyze adverse event counts and seriousness by drug substance with the full pharm-class enrichment available on DRUG_SUBSTANCES:

Snowflake

SELECT s.substance_name, s.pharm_class_epc, s.pharm_class_moa,
       COUNT(DISTINCT rpt.safety_report_id) AS total_reports,
       SUM(CASE WHEN rpt.seriousness_death THEN 1 ELSE 0 END) AS death_reports,
       SUM(CASE WHEN rpt.seriousness_hospitalization THEN 1 ELSE 0 END) AS hospital_reports
FROM DWV.DRUG_SUBSTANCES s
JOIN DWV.DRUG_REPORTS rpt
    ON s.safety_report_id = rpt.safety_report_id
WHERE s.substance_name IS NOT NULL
GROUP BY s.substance_name, s.pharm_class_epc, s.pharm_class_moa
ORDER BY total_reports DESC
LIMIT 25;

Databricks

Device Narratives — Full-Text Signal Search

Search the full device-event narrative corpus (no 16K truncation, both Description and Manufacturer narrative types) for keywords:

Snowflake

SELECT n.report_number, n.text_type_code, dr.event_type, dr.date_received,
       LEFT(n.text, 400) AS narrative_preview
FROM DWV.DEVICE_NARRATIVES n
JOIN DWV.DEVICE_REPORTS dr
    ON n.report_number = dr.report_number
WHERE UPPER(n.text) LIKE '%MALFUNCTION%'
  AND dr.event_type IN ('Injury', 'Death')
ORDER BY dr.date_received DESC
LIMIT 25;

Databricks

Device Events by Manufacturer

Analyze device adverse events by device-side manufacturer and event type:

Snowflake

SELECT d.manufacturer_name,
       dr.event_type,
       COUNT(*) AS event_count,
       COUNT(DISTINCT dr.report_number) AS unique_reports
FROM DWV.DEVICE_DETAILS d
JOIN DWV.DEVICE_REPORTS dr
    ON d.report_number = dr.report_number
WHERE d.manufacturer_name IS NOT NULL
GROUP BY d.manufacturer_name, dr.event_type
ORDER BY event_count DESC
LIMIT 25;

Databricks

Pharmacovigilance Signal Detection via PRR

Find emerging drug-reaction safety signals using Proportional Reporting Ratio (PRR) with 95% confidence intervals (Evans et al. 2001). The is_signal flag is TRUE when prr_score >= 2.0 AND report_count >= 3 AND prr_lower_95 >= 1.0 — the FDA's standard signal-detection threshold:

Snowflake

SELECT substance_name, reaction_meddra_pt,
       report_count, serious_count, death_count,
       ROUND(prr_score, 2) AS prr_score,
       ROUND(prr_lower_95, 2) AS prr_lower_95,
       ROUND(prr_upper_95, 2) AS prr_upper_95,
       first_seen_date, last_seen_date
FROM DWV.DRUG_REACTION_ASSOCIATIONS
WHERE is_signal = TRUE
  AND report_count >= 10
ORDER BY prr_score DESC
LIMIT 25;

Databricks

Device Classification Cross-Reference

Enrich device adverse events with FDA product-code classification — filter to life-sustaining / life-supporting devices with fatal outcomes:

Snowflake

SELECT dr.report_number, dr.event_type,
       d.brand_name, d.device_report_product_code,
       dc.device_name AS classification_name,
       dc.device_class, dc.regulation_number,
       dc.medical_specialty_description,
       dc.life_sustain_support_flag
FROM DWV.DEVICE_REPORTS dr
JOIN DWV.DEVICE_DETAILS d
    ON dr.report_number = d.report_number
JOIN DWV.DEVICE_CLASSIFICATION dc
    ON d.device_report_product_code = dc.product_code
WHERE dc.life_sustain_support_flag = TRUE
  AND dr.event_type = 'Death'
ORDER BY dr.date_received DESC
LIMIT 25;

Databricks

NDC Directory Drill-Down (Multi-NDC SPLIT/FLATTEN)

Enrich drug substances with the full NDC directory context. DRUG_SUBSTANCES.product_ndc is a ; -separated list of NDCs when a substance has multiple codes — SPLIT + LATERAL FLATTEN before equality-joining to DRUG_NDC_DIRECTORY for full coverage (~98% hit rate vs ~12% without splitting):

Snowflake

WITH ndcs AS (
  SELECT s.safety_report_id, s.substance_name, s.product_ndc AS raw_ndcs,
         TRIM(t.value::STRING) AS single_ndc
  FROM DWV.DRUG_SUBSTANCES s,
       LATERAL FLATTEN(SPLIT(s.product_ndc, '; ')) t
  WHERE s.product_ndc IS NOT NULL AND s.product_ndc != ''
)
SELECT n.safety_report_id, n.substance_name, n.single_ndc AS product_ndc,
       nd.brand_name AS ndc_brand,
       nd.marketing_category, nd.dea_schedule,
       nd.dosage_form, nd.route, nd.package_ndcs
FROM ndcs n
JOIN DWV.DRUG_NDC_DIRECTORY nd ON n.single_ndc = nd.product_ndc
WHERE nd.dea_schedule IS NOT NULL
ORDER BY n.safety_report_id DESC
LIMIT 25;

Databricks

Migrating from FDA FAERS or FDA MAUDE

If you previously subscribed to the standalone FDA FAERS or FDA MAUDE datasets, FDA AEMS is the unified successor — same source, same MedDRA coding, consolidated under FDA's new Adverse Event Monitoring System brand. A few schema changes to know about:

• Table renames:

  • DRUG__EVENTS → DRUG_REPORTS

  • DRUG__EVENTS_REACTIONS → DRUG_REACTIONS

  • DRUG__EVENTS_DRUGS → DRUG_SUBSTANCES

  • DEVICE__EVENTS → DEVICE_REPORTS

  • DEVICE__EVENTS_DEVICES → DEVICE_DETAILS

  • DEVICE__EVENTS_TEXT → DEVICE_NARRATIVES

  • DEVICE__EVENTS_PATIENTS → DEVICE_PATIENTS

  • DEVICE__DEVICE_CLASSIFICATION → DEVICE_CLASSIFICATION

• Native BOOLEAN flags — legacy serious = '1' becomes serious = TRUE. The original openFDA codes are preserved on *_raw shadow columns. Native BOOLEAN columns:

  • Drug-side: seriousness_* flags

  • DEVICE_REPORTS: adverse_event_flag, product_problem_flag, single_use_flag, reprocessed_and_reused_flag, report_to_fda, report_to_manufacturer

  • DEVICE_CLASSIFICATION: implant_flag, life_sustain_support_flag, gmp_exempt_flag, third_party_flag

  • Watch out: DEVICE_DETAILS.implant_flag is TEXT (the openFDA 'Y'/'N'/'U' value) — use DEVICE_CLASSIFICATION.implant_flag (joined via device_report_product_code → product_code) when you need a BOOLEAN filter.

• MedDRA case sensitivity: legacy returned reaction terms in UPPERCASE ('DEATH'); FDA AEMS uses openFDA's title case ('Death'). Wrap with UPPER(reaction_meddra_pt) to keep prior query filters working, or update string literals.

• Surrogate IDs gone, natural keys in: legacy used internal drug_event_id / device_event_id surrogates as join keys. FDA AEMS keys on FDA's own safety_report_id (drug) and report_number (device) — re-derive any surrogate cross-references via MD5(safety_report_id) if you need to preserve old join hashes.

• PRR aggregate keys on substance, not brand: legacy DRUG__AGG_DRUG_REACTION_ASSOCIATIONS keyed on brand name ('TYLENOL EXTRA STRENGTH'); FDA AEMS DRUG_REACTION_ASSOCIATIONS keys on openFDA-normalized substance_name ('ACETAMINOPHEN'). Smaller table, cleaner signals — but remap brand-based queries to substance.

• Executive dashboard reshape: legacy DEVICE__AGG_EXECUTIVE_DASHBOARD used a key-value layout (metric_name = 'TOTAL_ADVERSE_EVENTS', metric_value = ...); FDA AEMS DEVICE_EXECUTIVE_DASHBOARD is fully relational with one row per (manufacturer × class × specialty × quarter) and each metric as its own column.

For the full schema crosswalk — table renames, column-by-column diffs, data-type changes, aggregate-table renames, and drop-in query rewrites — see the Migration Reference — FAERS / MAUDE → AEMS Schema Diff section near the bottom of this page.

Who Uses This Data

Common Use Cases

• Pharmacovigilance signal detection — Monitor drug and biologic adverse event reports in near real-time to detect emerging safety signals, disproportionality patterns, and unexpected reactions. The DRUG_REACTION_ASSOCIATIONS PRR aggregate makes this a one-query operation against the full openFDA drug-event corpus.

• Medical device post-market surveillance — Track device-related adverse events, malfunctions, and patient injuries by product code, manufacturer, and event type to support post-market safety, recall assessment, and 522 surveillance studies.

• Cross-product safety analysis — Analyze adverse events across both drug and device categories to identify combination-product risks, drug-device interaction signals, and multi-product safety patterns under one consistent schema.

• Regulatory submission support — Generate comprehensive adverse event summaries, MedDRA-coded reaction profiles, and temporal trend analyses for regulatory submissions, REMS assessments, periodic safety reports (PSUR/PBRER), and safety label updates.

• Competitive intelligence — Compare adverse event profiles across competitors' products by reaction type, seriousness, and outcome to inform product positioning, M&A diligence, and risk communication strategies.

• Drug safety scientist daily workflow — Search MedDRA-coded reactions, validate PRR scores, drill into individual case narratives via patient_summary_narrative, and trace reporter provenance via DRUG_REPORT_SOURCES.

• Device safety engineering — Search the full untruncated DEVICE_NARRATIVES corpus for specific failure modes, JOIN to DEVICE_CLASSIFICATION for device-class context, and aggregate via DEVICE_MANUFACTURER_EVENTS for benchmarks.

Target Audience

Pharmacovigilance analysts · drug safety scientists · regulatory affairs · medical device safety engineers · post-market surveillance teams · biotech R&D · pharma medical affairs · health economics / outcomes research · CROs · clinical safety database vendors · FDA / EMA submission consultants · device manufacturers · healthcare AI / NLP teams building safety models on adverse event text.

Related Dataplex Products

This dataset pairs well with:

• CMS Medicaid Provider Spending — link adverse events to provider prescribing patterns for outcomes research

• CMS NPPES Provider Dataset — enrich reporter qualification + sender organization with NPI-level provider context

• FDA Total Diet Study Dataset — pair drug-substance safety with FDA's food-contaminant exposure data for combined safety analytics

Frequently Asked Questions

What's the difference between FDA AEMS and the legacy FAERS / MAUDE datasets? FDA AEMS is the unified successor brand. FDA consolidated FAERS (drug) and MAUDE (device) under the Adverse Event Monitoring System (AEMS) umbrella on openFDA. This product carries both streams under one Snowflake share with shared design conventions (native BOOLEANs, MedDRA reaction coding, full untruncated narratives, pre-built PRR aggregates), so a query learned on the drug side transfers cleanly to the device side. If you currently subscribe to the standalone FDA FAERS Dataset or FDA MAUDE Dataset, see the "Migrating from FDA FAERS or FDA MAUDE" section above for the schema crosswalk.

Why doesn't this include pre-2020 data? The default coverage is 2020-present, which matches 99%+ of pharmacovigilance and post-market surveillance use cases (recent data drives FDA signal detection). Pre-2020 backfill is available on request — drug history goes back to 2004 and device history to 1991 in the openFDA archive. Contact our team to enable the full historical horizon for your subscription.

How often is the data updated? Updated daily. Our pipeline checks openFDA every day for new releases and loads new data within hours of publication — no manual refresh needed. The data behind your queries stays within ~24 hours of FDA's latest publish.

Why does reaction_meddra_pt = 'DEATH' return zero rows? FDA AEMS uses openFDA's title-case spelling ('Death', 'Nausea', 'Headache'), not the legacy uppercase FAERS spelling. Either update string literals or wrap with UPPER(reaction_meddra_pt) for case-insensitive filtering. This is the most common gotcha when migrating from legacy FAERS queries.

Why does my JOIN DRUG_NDC_DIRECTORY ON product_ndc only return ~12% of substances? Because DRUG_SUBSTANCES.product_ndc is a ; -separated list when one substance carries multiple NDCs. You need to SPLIT + LATERAL FLATTEN the column before equality-joining to the NDC directory. The "NDC Directory Drill-Down" example above shows the full pattern — hit rate jumps from ~12% to ~98%.

Are there known data quality issues? None currently affecting customer-facing tables. For best device-classification context, always join DEVICE_DETAILS.device_report_product_code to DEVICE_CLASSIFICATION.product_code — near-complete hit rate, no edge cases. The reference table is the authoritative source for device class, medical specialty, regulation number, and the implant / life-sustain flags.

Can I detect safety signals without writing PRR math from scratch? Yes — DRUG_REACTION_ASSOCIATIONS carries pre-computed prr_score, prr_lower_95, prr_upper_95 (Evans et al. 2001 formula) and an is_signal BOOLEAN flag implementing the FDA's standard signal threshold (PRR>=2.0 AND N>=3 AND lower-95>=1.0). One WHERE is_signal = TRUE filter surfaces all flagged drug-reaction pairs.

What's the difference between DRUG_REPORTS.serious and the per-outcome seriousness_* flags? serious is TRUE when ANY of the per-outcome flags is TRUE (death, hospitalization, life-threatening, disability, congenital anomaly, or "other serious outcomes"). The individual seriousness_* flags let you filter to a specific seriousness category — e.g., WHERE seriousness_death = TRUE for fatal outcomes only.

How do I find all narratives for a specific device report? Each DEVICE_REPORTS row links to multiple DEVICE_NARRATIVES rows (avg ~2.2 per report) — one per narrative type (Description of Event, Manufacturer Narrative, Additional Manufacturer Narrative). JOIN on report_number and filter by text_type_code if you want a specific narrative type. No 16K truncation — text can reach ~10 million characters per row.

What's the difference between this and querying openFDA directly? openFDA's REST API is rate-limited (1000 requests/day unauthenticated, 240 requests/minute authenticated) and caps at 100 records per page, so a full historical pull means thousands of paginated requests and your own JSON-handling code. The Dataplex FDA AEMS Dataset gives you the same data as joinable relational tables in Snowflake or Databricks — native BOOLEAN flags, MedDRA-coded reactions, pre-built PRR safety signals, and the complete drug-report and device-report history in one SQL query, with automatic updates and no rate limits.

Migration Reference — FAERS / MAUDE → AEMS Schema Diff

This section is for engineers, analysts, and data scientists currently running production workloads against the legacy FDA FAERS Dataset (DWV.DRUG__* views) or FDA MAUDE Dataset (DWV.DEVICE__* views) who need a concrete crosswalk to the new FDA AEMS Dataset (DWV.* views). It covers table renames, column-level renames, dropped columns, added columns, data-type changes, and aggregate-table renames.

If you don't have legacy queries to migrate, you can skip this section. The earlier docs cover the AEMS schema as a standalone product.

Table-Level Crosswalk

Legacy (FDA FAERS / MAUDE)	AEMS	Grain change	Notes
DRUG__EVENTS	DRUG_REPORTS	1 row per safety_report_id (was 1 row per internal surrogate id)	Drug-event header. Reporter / sender / serious flags / patient header columns all live here.
DRUG__EVENTS_REACTIONS	DRUG_REACTIONS	Same — 1 row per (safety_report_id, MedDRA PT)	Join key changed from surrogate drug_event_id to natural safety_report_id.
DRUG__EVENTS_DRUGS	DRUG_SUBSTANCES	Same — 1 row per (safety_report_id, substance)	Renamed to reflect that each row is a substance reported on the case, not a "drug event".
DRUG__EVENTS_PATIENTS	(merged into DRUG_REPORTS)	Was 1:1 with parent — flattened up	Patient demographics (sex, age, weight, onset age, age group) now live on DRUG_REPORTS directly.
(no equivalent)	DRUG_REPORT_SOURCES	1 row per (safety_report_id, source kind)	New — separates PRIMARY source (reporter country / qualification / literature ref) from SENDER source (organization / sender type) when both are present.
(no equivalent)	DRUG_REPORT_DUPLICATES	1 row per (safety_report_id, duplicate ordinal)	New — multi-source duplicate registry cross-references (was previously collapsed into two columns on the header).
(no equivalent)	DRUG_NDC_DIRECTORY	1 row per product_ndc	New reference table — NDC enrichment (brand, generic, manufacturer, RxNorm CUI, UNII, pharm class). Use SPLIT_TO_TABLE + LATERAL FLATTEN to join (product_ndc is a ; -separated list on DRUG_SUBSTANCES).
DEVICE__EVENTS	DEVICE_REPORTS	1 row per report_number (was 1 row per internal surrogate id + mdr_report_key)	Device-event header.
DEVICE__EVENTS_DEVICES	DEVICE_DETAILS	Same — 1 row per (report_number, device_sequence_number)	Device descriptors (brand, generic, model, lot, manufacturer, openFDA enrichment).
DEVICE__EVENTS_TEXT	DEVICE_NARRATIVES	Same — 1 row per (report_number, mdr_text_ordinal)	Full untruncated narratives (no 16K cap).
DEVICE__EVENTS_PATIENTS	DEVICE_PATIENTS	Same — 1 row per (report_number, patient_sequence_number)	Patient demographics + outcomes.
(no equivalent — was a ; list inside DEVICE__EVENTS)	DEVICE_PROBLEMS	1 row per (report_number, problem_sequence)	New — exploded product-problem codes. Filter directly with WHERE product_problem = '...'.
DEVICE__DEVICE_CLASSIFICATION	DEVICE_CLASSIFICATION	Same — 1 row per FDA product_code	Promoted to a first-class reference table — join target for DEVICE_DETAILS.device_report_product_code.
DRUG__AGG_DRUG_REACTION_ASSOCIATIONS	DRUG_REACTION_ASSOCIATIONS	Re-keyed: was (brand_name, reaction); now (substance_name, reaction_meddra_pt)	Smaller, cleaner table — openFDA substance normalization collapses brand variants. Remap brand-based queries.
DRUG__AGG_SAFETY_TRENDS	DRUG_SAFETY_TRENDS	Same grain — (substance_name, reaction_meddra_pt, period_type, time_period)	Adds trend_direction enum (NEW / INCREASING / STABLE / DECREASING) derived from YoY delta.
DRUG__AGG_MANUFACTURER_ADVERSE_EVENTS	DRUG_MANUFACTURER_EVENTS	Same grain — 1 row per manufacturer_name	Adds risk_class enum.
DEVICE__AGG_MANUFACTURER_ADVERSE_EVENTS	DEVICE_MANUFACTURER_EVENTS	Same grain — 1 row per (manufacturer_name, manufacturer_d_country)	Adds risk_class, product_problem_rate, adverse_event_rate.
DEVICE__AGG_EXECUTIVE_DASHBOARD	DEVICE_EXECUTIVE_DASHBOARD	Reshaped: was tall (key-value metric_name/metric_value); now wide (one row per (manufacturer × class × specialty × quarter), one column per metric)	Pivot any legacy WHERE metric_name = '...' queries to direct column reads.
DRUG__AGG_DEMOGRAPHIC_RISK_PROFILES	(not published)	—	Subsumed by ad-hoc demographic rollups on DRUG_REPORTS (patient_sex, patient_age_group, seriousness_*).
DEVICE__AGG_SAFETY_TRENDS	(not published)	—	Subsumed by ad-hoc trend queries on DEVICE_REPORTS (date_received + manufacturer + product_code). Use DRUG_SAFETY_TRENDS as a query template.
DEVICE__AGG_COMPETITIVE_INTELLIGENCE	(not published)	—	Build on top of DEVICE_MANUFACTURER_EVENTS joined to DEVICE_CLASSIFICATION for competitor benchmarks.
DEVICE__AGG_REGULATORY_COMPLIANCE	(not published)	—	Out of scope for AEMS (adverse-event focused). Recall / 510(k) / PMA endpoints live outside this dataset.
DEVICE__510K_CLEARANCES, DEVICE__PREMARKET_APPROVALS, DEVICE__RECALLS, DEVICE__REGISTRATIONLISTINGS, DEVICE__UDIS, DEVICE__ENFORCEMENT_EVENTS, DEVICE__COVID19SEROLOGY_EVALUATIONS	(not in AEMS)	—	These are non-adverse-event openFDA endpoints (510(k), PMA, recalls, UDI, enforcement). AEMS scopes to adverse events only. Contact us if you need any of these as a separate product.

DRUG_REPORTS Column Crosswalk (was DRUG__EVENTS)

Legacy column	AEMS column	Change type	Notes
ID	(dropped)	Removed	Was an internal surrogate. AEMS keys on natural safety_report_id.
SAFETY_REPORT_ID	safety_report_id	Same	Primary key.
SAFETY_REPORT_VERSION (NUMBER)	safety_report_version (TEXT)	Type	TEXT to preserve leading zeros from openFDA.
(new)	safety_report_id_version_pair	Added	Convenience composite key for joining to versioned audit trails.
RECEIVE_DATE, RECEIPT_DATE, TRANSMISSION_DATE	same names	Same	DATE preserved. Plus new *_format companions carry the original openFDA precision indicator.
REPORT_TYPE	report_type	Same	New report_type_decoded companion (e.g., 'Spontaneous', 'Report from study').
SERIOUS, SERIOUSNESS_DEATH, SERIOUSNESS_HOSPITALIZATION, SERIOUSNESS_DISABLING, SERIOUSNESS_OTHER	same names	Same	BOOLEAN preserved.
SERIOUSNESS_LIFETHREATENING	seriousness_life_threatening	Rename	Underscore inserted for readability.
SERIOUSNESS_CONGENITAL_ANOMALI	seriousness_congenital_anomaly	Rename	Typo fix (legacy was truncated by openFDA's column-name length limit).
(new)	serious_raw	Added	Shadow column preserving the original openFDA '1'/'2' codes. Pattern repeats across all BOOLEANs.
FULFILL_EXPEDITE_CRITERIA (TEXT)	fulfill_expedite_criteria (BOOLEAN)	Type	Was string '1'/'2'; now native BOOLEAN.
DUPLICATE (BOOLEAN)	duplicate (TEXT)	Type	Watch out: legacy was BOOLEAN; AEMS preserves the source TEXT because openFDA's value space is wider than 0/1. Cast explicitly if needed.
COMPANY_NUMB	company_number	Rename	Typo fix.
SENDER_ORGANIZATION_NAME	sender_organization	Rename	_NAME dropped (redundant).
SENDER_ORGANIZATION (was an OID code)	sender_type	Repurpose	Legacy SENDER_ORGANIZATION held a numeric OID; AEMS exposes the decoded sender_type (e.g., 'Pharmaceutical Company') instead.
RECEIVER_ORGANIZATION, RECEIVER_TYPE	same names	Same	—
PRIMARY_SOURCE_REPORTER_COUNTRY	reporter_country	Rename	Shorter form.
PRIMARY_SOURCE_QUALIFICATION (NUMBER)	reporter_qualification (TEXT) + reporter_qualification_decoded (TEXT)	Type + companion	Was a numeric code; now exposed as both code and decoded form ('Physician', 'Pharmacist', 'Other HCP', 'Lawyer', 'Consumer').
PRIMARY_SOURCE_COUNTRY, OCCUR_COUNTRY	same names	Same	—
PATIENT_SUMMARY_NARRATIVE	same	Same	Free-text clinical narrative.
DRUG_COUNT, REACTION_COUNT	same	Same	Pre-computed on header for quick filtering.
PATIENT_COUNT	(dropped)	Removed	Almost always 1; not a useful filter. Use DRUG_REPORTS row count instead.
REPORT_DUPLICATE_NUMBER, REPORT_DUPLICATE_SOURCE	(moved to DRUG_REPORT_DUPLICATES)	Moved	Now per-row on the child to support multi-source duplicate registry entries.
SOURCE_PRIMARY_SOURCE_QUALIFICATION, SOURCE_SENDER_TYPE, SOURCE_RECEIVER_TYPE, SOURCE_FULFILL_EXPEDITE_CRITERIA	(dropped)	Removed	Legacy SOURCE_* shadow columns replaced by the _raw / _decoded pattern.
EXPORT_DATE (TEXT)	(dropped)	Removed	Use created_at for batch lineage; export_date was openFDA's tile date and is no longer surfaced.
(new)	authority_number, patient_age_group, patient_onset_age, patient_onset_age_unit, patient_sex, patient_sex_decoded, patient_weight, patient_death_date, patient_death_present	Added	Patient demographics flattened up from the legacy DRUG__EVENTS_PATIENTS table. patient_death_present is a convenience BOOLEAN.
CREATED_AT (TIMESTAMP_LTZ)	created_at (TIMESTAMP_NTZ)	Type	TZ removed — AEMS standard is NTZ for source-derived timestamps. updated_at remains LTZ (dbt build time).

DRUG_REACTIONS Column Crosswalk (was DRUG__EVENTS_REACTIONS)

Legacy	AEMS	Change
DRUG_EVENT_REACTION_ID	(dropped)	Surrogate removed. Composite key is now (safety_report_id, reaction_meddra_pt_code).
DRUG_EVENT_ID	(dropped)	Join key changed to natural safety_report_id.
SAFETY_REPORT_ID	same	—
REACTION_MEDDRA_PT	same	Case change: legacy returned 'DEATH'; AEMS uses openFDA title case 'Death'. Wrap with UPPER() for backwards-compatible filters.
REACTION_MEDDRA_PT_CODE	same	—
REACTION_OUTCOME (NUMBER)	reaction_outcome (TEXT) + reaction_outcome_decoded (TEXT)	Now both code + decoded form.
(new)	reaction_meddra_version	Added — MedDRA version applied (e.g., '25.0').

DRUG_SUBSTANCES Column Crosswalk (was DRUG__EVENTS_DRUGS)

Legacy	AEMS	Change
DRUG_EVENT_DRUG_ID, DRUG_EVENT_ID, ARRAY_INDEX	(dropped)	Surrogate + array-index keys retired. Natural key is safety_report_id.
MEDICINAL_PRODUCT	same	Brand name as reported.
DRUG_CHARACTERIZATION (NUMBER)	drug_characterization (TEXT) + drug_characterization_decoded (TEXT)	Was code-only; now both code and decoded ('Suspect', 'Concomitant', 'Interacting').
DRUG_INDICATION, DRUG_DOSAGE_TEXT, DRUG_DOSAGE_FORM, DRUG_ADMINISTRATION_ROUTE, DRUG_AUTHORIZATION_NUMBER, DRUG_BATCH_NUMBER, DRUG_TREATMENT_DURATION, DRUG_TREATMENT_DURATION_UNIT, DRUG_RECURR_ADMINISTRATION, DRUG_ADDITIONAL	same names	—
DRUG_START_DATE, DRUG_END_DATE	same (DATE preserved)	—
ACTION_DRUG (NUMBER)	action_drug (TEXT) + action_drug_decoded (TEXT)	Code + decoded.
(new)	active_substance_name, substance_name, brand_name, generic_name, manufacturer_name, product_ndc, package_ndc, product_type, route, application_number, pharm_class_epc, pharm_class_moa, pharm_class_pe, pharm_class_cs, rxcui, unii, nui, spl_id, spl_set_id	Added — full openFDA substance enrichment block. Use product_ndc + SPLIT_TO_TABLE to join to DRUG_NDC_DIRECTORY.
SOURCE_* shadow columns	(dropped)	Replaced by _decoded pattern.

DEVICE_REPORTS Column Crosswalk (was DEVICE__EVENTS)

Legacy	AEMS	Change
ID, MDR_REPORT_KEY, EVENT_KEY	(dropped)	Surrogates retired. Natural key is report_number.
REPORT_NUMBER	same	Primary key.
DATE_REPORT, DATE_OF_EVENT, DATE_RECEIVED	same (DATE)	—
EVENT_TYPE, PREVIOUS_USE_CODE, EVENT_LOCATION, DISTRIBUTOR_NAME, DISTRIBUTOR_CITY, DISTRIBUTOR_STATE	same names	—
MANUFACTURER_COUNTRY	manufacturer_contact_country	Rename — _CONTACT_ prefix added for consistency with the rest of the manufacturer contact block.
MANUFACTURER_CITY, MANUFACTURER_STATE, MANUFACTURER_NAME	manufacturer_contact_city, manufacturer_contact_state, manufacturer_contact_t_name	Rename
ADVERSE_EVENT_FLAG, PRODUCT_PROBLEM_FLAG	same (BOOLEAN)	—
REPORT_SOURCE_CODE	source_type	Rename.
PMA_PMN_NUMBER	pma_pmn_num	Rename — _NUMBER → _NUM to match openFDA.
DEVICE_COUNT, PATIENT_COUNT, PRODUCT_PROBLEM_COUNT, REMEDIAL_ACTION_COUNT, SOURCE_TYPE_COUNT, TYPE_OF_REPORT_COUNT, MDR_TEXT_COUNT	(dropped)	Removed
ADVERSE_EVENT_URL	(dropped)	Removed
SOURCE_* shadow columns, EXPORT_DATE	(dropped)	Removed
(new)	report_date, date_facility_aware, report_to_fda (BOOLEAN), report_to_fda_raw (TEXT), report_to_manufacturer (BOOLEAN), single_use_flag (BOOLEAN), reprocessed_and_reused_flag (BOOLEAN), implant_flag (BOOLEAN), reporter_occupation_code, type_of_report, remedial_action, number_devices_in_event, number_patients_in_event, manufacturer_link_flag, device_date_of_manufacturer, removal_correction_number, summary_report, mdr_text_narrative, distributor / manufacturer contact address columns	Added
CREATED_AT (TIMESTAMP_LTZ)	created_at (TIMESTAMP_NTZ)	Type

DEVICE_DETAILS Column Crosswalk (was DEVICE__EVENTS_DEVICES)

Legacy	AEMS	Change
DEVICE_EVENT_DEVICE_ID, DEVICE_EVENT_ID, ARRAY_INDEX, MDR_REPORT_KEY, DEVICE_EVENT_KEY	(dropped)	Surrogates retired. Composite key is (report_number, device_sequence_number).
BRAND_NAME, GENERIC_NAME, MODEL_NUMBER, CATALOG_NUMBER, LOT_NUMBER, DEVICE_REPORT_PRODUCT_CODE, MANUFACTURER_D_NAME, MANUFACTURER_D_CITY, MANUFACTURER_D_STATE, OPENFDA_DEVICE_CLASS, OPENFDA_DEVICE_NAME, DEVICE_OPERATOR	same names	—
MANUFACTURER_D_COUNTRY	same	—
IMPLANT_FLAG (BOOLEAN)	implant_flag (TEXT)	Type
OPENFDA_DEVICE_CLASS_DESCRIPTION, OPENFDA_MEDICAL_SPECIALTY_DESCRIPTION	(replaced)	—
DEVICE_DATE_RECEIVED, DATE_RETURNED_TO_MANUFACTURER	(dropped)	Removed — were sparsely populated on legacy and are absent from current openFDA.
EXPORT_DATE	(dropped)	Removed.
(new)	device_age_text (raw), device_age (NUMBER, parsed), device_evaluated_by_manufacturer, device_expiration_date_of_device, expiration_date_of_device, other_id_number, manufacturer_d_address_1, manufacturer_d_postal_code, device_availability, openfda_medical_specialty, openfda_regulation_number, openfda_pma_number, openfda_k_number, openfda_fei_number, openfda_registration_number, openfda_implant_flag, openfda_life_sustain_support_flag, openfda_class_advisor_committee	Added — full openFDA device descriptor block.

DEVICE_NARRATIVES Column Crosswalk (was DEVICE__EVENTS_TEXT)

Legacy	AEMS	Change
TEXT_ID, DEVICE_EVENT_ID, MDR_REPORT_KEY, TEXT_SEQUENCE_NUMBER	(dropped)	Surrogates retired.
MDR_TEXT_KEY	same	—
(new)	mdr_text_ordinal (NUMBER)	Added — per-narrative ordinal within a report (1, 2, 3 ...).
TEXT_TYPE_CODE	same	'Description of Event or Problem', 'Manufacturer Narrative', 'Additional Manufacturer Narrative'.
PATIENT_SEQUENCE_NUMBER, DATE_REPORT	same	—
TEXT_CONTENT (typically truncated at 16K in legacy)	text	Rename + no 16K cap — narratives reach up to ~10 million characters per row. The single biggest reason customers migrate to AEMS.

DEVICE_PATIENTS Column Crosswalk (was DEVICE__EVENTS_PATIENTS)

Legacy	AEMS	Change
DEVICE_EVENT_PATIENT_ID, DEVICE_EVENT_ID, MDR_REPORT_KEY, ARRAY_INDEX	(dropped)	Surrogates retired. Key is (report_number, patient_sequence_number).
PATIENT_DATE_RECEIVED	date_received	Rename.
PATIENT_AGE_YEARS (FLOAT)	patient_age (TEXT) + patient_age_unit (TEXT)	Type + split
PATIENT_WEIGHT_KG (FLOAT)	patient_weight (TEXT) + patient_weight_unit (TEXT)	Type + split
PATIENT_SEX, PATIENT_ETHNICITY, PATIENT_RACE	same names	—
PATIENT_OUTCOMES	sequence_number_outcome	Rename
PATIENT_TREATMENTS	sequence_number_treatment	Rename
(new)	patient_problems	Added — ; -separated list of patient problem codes (the 2017 MAUDE schema expansion).

Aggregate-Table Notes — Behavioral Changes

DRUG_REACTION_ASSOCIATIONS (was DRUG__AGG_DRUG_REACTION_ASSOCIATIONS) — PRR signal aggregate now keys on openFDA-normalized substance_name instead of brand_name. Practical effect: instead of three rows for 'TYLENOL', 'TYLENOL EXTRA STRENGTH', 'TYLENOL 8 HOUR', you get one row for 'ACETAMINOPHEN' with the combined denominator — cleaner PRR scores, fewer false-negative signals from brand fragmentation. Remap brand-based filters to substance.

DEVICE_EXECUTIVE_DASHBOARD (was DEVICE__AGG_EXECUTIVE_DASHBOARD) — legacy was a tall key-value layout (metric_name / metric_value); AEMS is wide (one column per metric — total_reports, rolling_4q_reports, prior_year_reports, yoy_delta, death_rate, injury_rate, top_product_problem). Rewrite legacy WHERE metric_name = 'TOTAL_ADVERSE_EVENTS' as a direct column read.

DRUG_SAFETY_TRENDS — the trend_direction enum (NEW / INCREASING / STABLE / DECREASING) is now derived from yoy_change_pct, not from prior-period count comparisons. Buckets: >= +20% = INCREASING, <= -20% = DECREASING, otherwise STABLE; rows where prior-period count is zero are flagged NEW. If you relied on the legacy bucketing, validate against the published thresholds.

Aggregates dropped: DRUG__AGG_DEMOGRAPHIC_RISK_PROFILES, DEVICE__AGG_SAFETY_TRENDS, DEVICE__AGG_COMPETITIVE_INTELLIGENCE, DEVICE__AGG_REGULATORY_COMPLIANCE. These were under-used and computable with one or two CTEs against the base tables. Use DRUG_SAFETY_TRENDS as the design template for device-side trends; pair DEVICE_MANUFACTURER_EVENTS with DEVICE_CLASSIFICATION for competitive benchmarks.

Drop-in Query Rewrites — Bridge Examples

Legacy: top reactions for a drug (FAERS)

-- Legacy (FAERS)
SELECT r.reaction_meddra_pt, COUNT(*) AS n
FROM DWV.DRUG__EVENTS_DRUGS d
JOIN DWV.DRUG__EVENTS_REACTIONS r ON r.drug_event_id = d.drug_event_id
WHERE UPPER(d.medicinal_product) LIKE '%TYLENOL%'
GROUP BY 1 ORDER BY n DESC LIMIT 20;

-- AEMS
SELECT r.reaction_meddra_pt, COUNT(*) AS n
FROM DWV.DRUG_SUBSTANCES s
JOIN DWV.DRUG_REACTIONS r ON r.safety_report_id = s.safety_report_id
WHERE s.substance_name = 'ACETAMINOPHEN'   -- substance, not brand
GROUP BY 1 ORDER BY n DESC LIMIT 20;

Two key changes: join on safety_report_id (not drug_event_id), and filter on substance_name (openFDA-normalized) instead of medicinal_product (brand as reported).

Legacy: serious device events for a manufacturer (MAUDE)

-- Legacy (MAUDE)
SELECT date_trunc('month', e.date_received) AS month, COUNT(*) AS n
FROM DWV.DEVICE__EVENTS e
WHERE e.manufacturer_name ILIKE '%MEDTRONIC%'
  AND e.event_type IN ('Death', 'Injury')
  AND e.date_received >= '2023-01-01'
GROUP BY 1 ORDER BY 1;

-- AEMS — identical structure, just rename the table
SELECT date_trunc('month', e.date_received) AS month, COUNT(*) AS n
FROM DWV.DEVICE_REPORTS e
WHERE e.manufacturer_contact_t_name ILIKE '%MEDTRONIC%'  -- column rename
  AND e.event_type IN ('Death', 'Injury')
  AND e.date_received >= '2023-01-01'
GROUP BY 1 ORDER BY 1;

Most legacy device queries port over with a table rename + the manufacturer_* → manufacturer_contact_* column rename. Note manufacturer-side context lives separately on DEVICE_DETAILS.manufacturer_d_name (the device manufacturer, which may differ from the report's contact manufacturer).

Legacy: pivot the executive dashboard

-- Legacy (MAUDE) — tall key-value
SELECT manufacturer_name, time_period, metric_value AS total_adverse_events
FROM DWV.DEVICE__AGG_EXECUTIVE_DASHBOARD
WHERE metric_name = 'TOTAL_ADVERSE_EVENTS';

-- AEMS — wide, direct column read
SELECT manufacturer_name, time_period, total_reports AS total_adverse_events
FROM DWV.DEVICE_EXECUTIVE_DASHBOARD;

Migration Checklist (Engineering Sequence)

1. Inventory legacy refs. Grep your codebase for DWV.DRUG__ and DWV.DEVICE__ (or whatever database alias you mounted the legacy share under) — those are your migration targets.

2. Apply the table rename map. Most refs port over with a one-line table swap (DWV.DRUG__EVENTS → DWV.DRUG_REPORTS, etc.) once you point at the new share.

3. Rewrite join keys. Every legacy join on surrogate drug_event_id / device_event_id / mdr_report_key becomes a join on natural safety_report_id / report_number. Run an EXPLAIN — joining on natural keys can change the row count estimate; verify cardinality.

4. Fix MedDRA case filters. Wrap legacy 'DEATH' / 'NAUSEA' literals with UPPER(reaction_meddra_pt) = ... or update to title case.

5. Convert PRR brand → substance. WHERE brand_name = 'TYLENOL' → WHERE substance_name = 'ACETAMINOPHEN'. Consult the openFDA substance index if you're unsure of the canonical name.

6. Pivot the executive dashboard. Replace WHERE metric_name = '...' with the corresponding wide column.

7. Replace dropped rollup counts. Anywhere you read DEVICE__EVENTS.device_count / patient_count / product_problem_count, swap to a JOIN ... GROUP BY ... COUNT(*) against the child table.

8. Audit BOOLEAN/TEXT shifts. Two columns flipped TYPE between legacy and AEMS: DRUG_REPORTS.duplicate (BOOLEAN → TEXT) and DEVICE_DETAILS.implant_flag (BOOLEAN → TEXT, with the BOOLEAN form on DEVICE_CLASSIFICATION.implant_flag instead).

9. Run row-count parity in DEV first. For each migrated query, compare row counts and aggregates between the legacy and AEMS versions. Drug-stream parity is in the high-99% range and device-stream parity in the high-98% range within the 2020+ window — the small delta is openFDA's own dedup behavior (legacy MAUDE retained some version-superseded rows that AEMS's natural-key dedup removes). Expect tiny aggregate shifts; investigate anything more than a few percent.

10. Cut over. Update the share/catalog reference, point your BI tool / dbt project / notebook at the AEMS DWV.* views, and decommission the legacy queries.

If you have legacy queries that don't have an obvious AEMS equivalent (e.g., depending on a dropped SOURCE_* shadow column or one of the deprecated aggregates), contact our support team — we can either point you at the right pattern or, if there's enough demand, restore the missing surface as a new column or table.

Ready to access FDA AEMS adverse event data?

Questions? Contact our team for a walkthrough.

Platform	Action
Snowflake	Get on Marketplace | Free Trial
Databricks	Start 14-Day Free Trial

Trial Access

The free trial provides a deterministic 1,500-drug-report + 1,500-device-report sample with all child tables (reactions, substances, report sources, duplicates, device details, narratives, patients, problems) joined to the trial seed. Every parent-child JOIN returns complete results — you can run any of the example queries above against trial data and get accurate, representative results before subscribing. Reference tables (DEVICE_CLASSIFICATION, DRUG_NDC_DIRECTORY) include 500-row representative samples. Aggregate signal tables (DRUG_REACTION_ASSOCIATIONS, etc.) are paid-only because aggregates need full denominators to be meaningful.

Data Sources & References

• FAERS Public Dashboard — FDA's own drug-event explorer

• MAUDE Search — FDA's own device-event search interface

• MedDRA — the Medical Dictionary for Regulatory Activities used for reaction coding

• FDA Product Code Database — device product-code reference (also available as DEVICE_CLASSIFICATION)

Important Data Interpretation Guidelines

Adverse event reports are voluntary and do not undergo extensive FDA validation. They may be incomplete, inaccurate, or duplicative. A causal relationship cannot be established between a drug or device and the reported reaction based solely on this data. Reports represent a small fraction of total drug or device use and should not be the sole basis for clinical decisions. Use FDA AEMS for signal detection, hypothesis generation, post-market surveillance, and regulatory submission support — not as definitive evidence of causation.