> For the complete documentation index, see [llms.txt](https://docs.dataplex-consulting.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.dataplex-consulting.com/data-catalog/fda-aems-dataset.md). # 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. {% hint style="info" %} **Get Full Access** | [Snowflake Marketplace](https://app.snowflake.com/marketplace/listing/GZT1Z7QRT19A) | [Free Trial](https://trial.dataplex-consulting.com) {% endhint %} ### 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](/files/9avj6aXD4PjVjQY5065O) **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: {% tabs %} {% tab title="Snowflake" %} ```sql -- 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; ``` {% endtab %} {% tab title="Databricks" %} ```sql SELECT 'DRUG_REPORTS' AS stream, MAX(receive_date) AS latest_report FROM fda_aems_dwv.drug_reports UNION ALL SELECT 'DEVICE_REPORTS', MAX(date_received) FROM fda_aems_dwv.device_reports; ``` {% endtab %} {% endtabs %} ### Top Drug Adverse Reactions Find the most commonly reported adverse reactions across all drug reports — the classic pharmacovigilance "what reactions are showing up" question. {% tabs %} {% tab title="Snowflake" %} ```sql 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; ``` {% endtab %} {% tab title="Databricks" %} ```sql SELECT r.reaction_meddra_pt, COUNT(*) AS report_count, SUM(CASE WHEN rpt.serious THEN 1 ELSE 0 END) AS serious_count FROM fda_aems_dwv.drug_reactions r JOIN fda_aems_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; ``` {% endtab %} {% endtabs %} ### 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`: {% tabs %} {% tab title="Snowflake" %} ```sql 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; ``` {% endtab %} {% tab title="Databricks" %} ```sql 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 fda_aems_dwv.drug_substances s JOIN fda_aems_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; ``` {% endtab %} {% endtabs %} ### Device Narratives — Full-Text Signal Search Search the full device-event narrative corpus (no 16K truncation, both Description and Manufacturer narrative types) for keywords: {% tabs %} {% tab title="Snowflake" %} ```sql 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; ``` {% endtab %} {% tab title="Databricks" %} ```sql SELECT n.report_number, n.text_type_code, dr.event_type, dr.date_received, LEFT(n.text, 400) AS narrative_preview FROM fda_aems_dwv.device_narratives n JOIN fda_aems_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; ``` {% endtab %} {% endtabs %} ### Device Events by Manufacturer Analyze device adverse events by device-side manufacturer and event type: {% tabs %} {% tab title="Snowflake" %} ```sql 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; ``` {% endtab %} {% tab title="Databricks" %} ```sql SELECT d.manufacturer_name, dr.event_type, COUNT(*) AS event_count, COUNT(DISTINCT dr.report_number) AS unique_reports FROM fda_aems_dwv.device_details d JOIN fda_aems_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; ``` {% endtab %} {% endtabs %} ### 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: {% tabs %} {% tab title="Snowflake" %} ```sql 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; ``` {% endtab %} {% tab title="Databricks" %} ```sql 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 fda_aems_dwv.drug_reaction_associations WHERE is_signal = TRUE AND report_count >= 10 ORDER BY prr_score DESC LIMIT 25; ``` {% endtab %} {% endtabs %} ### Device Classification Cross-Reference Enrich device adverse events with FDA product-code classification — filter to life-sustaining / life-supporting devices with fatal outcomes: {% tabs %} {% tab title="Snowflake" %} ```sql 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; ``` {% endtab %} {% tab title="Databricks" %} ```sql 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 fda_aems_dwv.device_reports dr JOIN fda_aems_dwv.device_details d ON dr.report_number = d.report_number JOIN fda_aems_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; ``` {% endtab %} {% endtabs %} ### 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): {% tabs %} {% tab title="Snowflake" %} ```sql 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; ``` {% endtab %} {% tab title="Databricks" %} ```sql 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 fda_aems_dwv.drug_substances s LATERAL VIEW EXPLODE(SPLIT(s.product_ndc, '; ')) t AS value 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 fda_aems_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; ``` {% endtab %} {% endtabs %} ## 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**](https://docs.dataplex-consulting.com/data-catalog/fda-aems-dataset#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**](/data-catalog/cms-medicaid-provider-spending-dataset.md) — link adverse events to provider prescribing patterns for outcomes research * [**CMS NPPES Provider Dataset**](/data-catalog/cms-nppes-provider-dataset.md) — enrich reporter qualification + sender organization with NPI-level provider context * [**FDA Total Diet Study Dataset**](/data-catalog/fda-tds-dataset.md) — 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)** ```sql -- 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; ``` ```sql -- 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)** ```sql -- 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; ``` ```sql -- 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** ```sql -- 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'; ``` ```sql -- 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. {% hint style="success" %} **Ready to access FDA AEMS adverse event data?** Questions? [Contact our team](mailto:support@dataplex-consulting.com) for a walkthrough. {% endhint %} | Platform | Action | | -------------- | --------------------------------------------------------------------------------------------------------------------------------------- | | **Snowflake** | [Get on Marketplace](https://app.snowflake.com/marketplace/listing/GZT1Z7QRT19A) \| [Free Trial](https://trial.dataplex-consulting.com) | | **Databricks** | [Start 14-Day Free Trial](https://trial.dataplex-consulting.com) | ### 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](https://www.fda.gov/drugs/questions-and-answers-fdas-adverse-event-reporting-system-faers/fda-adverse-event-reporting-system-faers-public-dashboard) — FDA's own drug-event explorer * [MAUDE Search](https://www.accessdata.fda.gov/scripts/cdrh/cfdocs/cfmaude/search.cfm) — FDA's own device-event search interface * [MedDRA](https://www.meddra.org/) — the Medical Dictionary for Regulatory Activities used for reaction coding * [FDA Product Code Database](https://www.accessdata.fda.gov/scripts/cdrh/cfdocs/cfpcd/pcdsimplesearch.cfm) — 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. --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.dataplex-consulting.com/data-catalog/fda-aems-dataset.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.