# FDA Drug Recalls (Enforcement Reports)

`drug_silver.fact_drug_recall` · Drugs and formularies · status live

FDA Enforcement Reports for drug recalls — classification (Class I/II/III), status, voluntary/mandated, product description, quantity, reason for recall, recalling firm + location, distribution pattern. Quality/safety signal.

**Grain.** One row per FDA drug recall (one unique recall_number). recall_number alone is the verified minimal key — 17,679 distinct values across 17,679 rows, zero duplicates. event_id groups multiple recalls into one enforcement action and is NOT the grain.

**How to use it.** Find every active (not-yet-terminated) Class I (most serious) drug recall and what was wrong: SELECT recall_number, recalling_firm, product_description, reason_for_recall, recall_initiation_date FROM drug_silver.fact_drug_recall WHERE classification='Class I' AND status='Ongoing' ORDER BY recall_initiation_date DESC. Answers 'which serious drug recalls are currently open and why'. To check if a specific drug was ever recalled when openfda fields are null, also match product_description (it embeds brand/generic/NDC as text).

**Traps.**
- recall_number is the ENTIRE grain by itself (0 duplicates, 17,679 distinct = 17,679 rows). The profiler's 10-column candidate key is a bloated SUPERSET — most of those extra columns (s3_version_id, bronze_object_key, source_id, drug_source_version_id, openfda_product_ndc_first) are either constants or 100% NULL and add nothing. Join/dedup on recall_number alone.
- openfda_product_ndc_first and drug_source_version_id are 100% NULL; cms_vintage_sequence, parse_revision (=1), vintage_tag (='none'), product_type (='Drugs'), source_id, source_url, sha256_content_hash, bronze_object_key, observed_period/asserted_at (=2026-06-02) are all CONSTANTS. None of these are usable join/filter keys despite some being flagged as 'candidate keys' — they're unique only because they're constant or null.
- This is a SINGLE full-replace snapshot, NOT SCD2 history: is_current=true for every row and there is exactly one asserted_at (2026-06-02). There is nothing to dedup and filtering is_current=true is a no-op. A later snapshot would replace the whole table, so do not expect to find superseded versions of a recall here.
- effective_period_end is BITEMPORAL, not the real recall end: it equals termination_date only for closed recalls; for the 2,883 Ongoing/Completed recalls with NULL termination_date it is back-filled to the snapshot date 2026-06-02. Using effective_period_end as 'when the recall ended' will be wrong for every open recall — use termination_date (and treat NULL as 'not yet terminated').
- Two date traps: (1) data_year = year(recall_initiation_date), NOT the year in the recall_number suffix (suffix matches only ~56%). (2) effective_period_start is an exact alias of recall_initiation_date. The four real FDA dates run init <= center_classification <= report <= termination; report_date is the best 'made public' date but only exists from 2012 onward, while initiation dates reach back to 2006.
- This is a drug-SAFETY/recall table, not a COST table. It is the openFDA Drug Enforcement feed (source_id='fda_drug_enforcement') and contains no prices — unlike its cost-bearing siblings in drug_silver (fact_drug_asp_quarterly, fact_drug_acquisition_cost, fact_drug_va_fss_price, fact_drug_spending_part_b/part_d). Use it to flag whether a drug/lot was recalled, not for pricing.
- Product/NDC identity is mostly trapped in free text: structured NDC is unusable (all NULL) and the openfda_* normalized fields (brand/generic/mfr/route/app#) cover only ~18% of rows (EPC only ~6%). For the other ~82% you must parse product_description / code_info text to get NDC, lot, manufacturer. recalling_firm is frequently a repackager/distributor (Aidapak, Attix, Cardinal), not the original labeler.

| column | type | meaning |
|---|---|---|
| recall_number | string | FDA recall identifier, e.g. 'D-1448-2014'. THE GRAIN KEY: globally unique, one row per value (17,679 distinct = 17,679 rows). Format is <product-prefix>-<sequence>-<year>. CAUTION: the 4-digit year suffix is the FDA cohort/case year and matches data_year only ~56% of the time (9,908/17,679) — do not treat the suffix as the recall year. |
| data_year | int | Integer year. VERIFIED to equal year(recall_initiation_date) = year(effective_period_start) for 100% of rows — i.e. the year the firm initiated the recall, NOT the recall_number suffix year. Ranges 2006-2026; heavily back-loaded (2013+ is the bulk; 2006-2011 are a handful of stragglers). |
| event_id | string | FDA enforcement-event id grouping related recalls. NOT unique — 17,679 recalls map to only 4,562 events. One event can span hundreds of recall_numbers (largest events: 470, 465, 382 recalls). Use to group a multi-product recall action; never as a row key. |
| classification | string | FDA recall severity (health-hazard) class. Exactly 3 values: 'Class II' (14,259, most common = could cause temporary/reversible harm), 'Class I' (1,727 = serious/death risk), 'Class III' (1,693 = unlikely to cause harm). Never null. |
| status | string | Recall lifecycle state. 3 values: 'Terminated' (14,793 = FDA closed it), 'Ongoing' (2,452 = still active), 'Completed' (434 = firm finished but FDA not yet terminated). Drives termination_date nullness (see gotchas). |
| voluntary_mandated | string | Who initiated. Almost always 'Voluntary: Firm initiated' (17,615); 'FDA Mandated' is rare (29); 35 nulls. |
| product_type | string | CONSTANT 'Drugs' for every row — this table is the drug slice of FDA enforcement only. Carries no information; do not filter on it. |
| product_description | string | Free-text description of the recalled product: brand/generic name, strength, package/NDC, manufacturer & distributor. The richest product identifier in the table since structured NDC columns are unusable (see openfda_product_ndc_first). NDCs are often embedded here as text. |
| product_quantity | string | Free-text amount recalled, e.g. '20 vials', '73,800 cups', '31,704 bottles'. Non-numeric/non-normalized; ~2,064 rows null (15,615 populated). Parse with care. |
| code_info | string | Free-text lot numbers / expiration dates / product codes identifying the specific affected units. High-cardinality (14,042 distinct) but NOT unique and not a key. |
| distribution_pattern | string | Free-text geographic distribution scope, e.g. 'Nationwide', 'PA, CA', or a sentence listing states. Effectively always present (1 null). |
| reason_for_recall | string | Free-text reason, e.g. 'Presence of Particulate Matter', 'Labeling: Label Mix-Up', 'Misbranded', 'cGMP Deviations'. 4,376 distinct values — descriptive, not coded. |
| recalling_firm | string | Name of the firm conducting the recall (1,629 distinct). Top firms are repackagers/compounders/distributors (Aidapak 538, Attix 470, King Bio 465, Cardinal Health 213), not just original manufacturers. |
| firm_city | string | City of the recalling firm. Free text. |
| firm_state | string | US state abbreviation of the recalling firm (49 distinct values present). |
| firm_country | string | Country of the recalling firm. Overwhelmingly 'United States' (16,632); foreign firms exist (Canada 572, India 250, China 85, etc.). |
| firm_postal_code | string | ZIP/postal code of the recalling firm (often ZIP+4). |
| initial_firm_notification | string | How the firm first notified customers: 'Letter' (9,003), 'Telephone' (2,706), 'Press Release' (2,571), a combined 'Two or more...' bucket (1,725), 'E-Mail', 'Other', 'FAX', 'Visit'; 279 null. Note 'E-Mail' and the legacy 'FAX' both appear. |
| observed_period | date | Bitemporal: the snapshot/ingest date when these facts were observed. CONSTANT 2026-06-02 across ALL rows — the whole table is one snapshot. Not a recall date; do not use for recall timing. |
| asserted_at | date | Bitemporal assertion date = 2026-06-02 for every row (same single snapshot as observed_period). Provenance bookkeeping, not recall timing. |
| effective_period_start | date | Bitemporal validity start. VERIFIED IDENTICAL to recall_initiation_date for 100% of rows — it is an alias of the recall-initiation date. Range 2006-02-24 to 2026-05-12. |
| effective_period_end | date | Bitemporal validity end. Equals termination_date when the recall is terminated (14,796 rows), BUT when termination_date is NULL (2,883 Ongoing/Completed rows) it is set to the SNAPSHOT date 2026-06-02 (an open-ended 'still in effect as of snapshot' floor), NOT a real recall end. Never null. See gotchas. |
| cms_vintage_sequence | int | Provenance vintage sequence. CONSTANT 1 — single ingested vintage. No information for analysis. |
| parse_revision | int | Parser revision number. CONSTANT 1 across all rows. |
| parser_artifact_hash | string | SHA-256 of the parser artifact that produced the rows. Single constant value (one parser build). |
| source_url | string | Origin API endpoint. CONSTANT 'https://api.fda.gov/drug/enforcement.json' — this table is openFDA Drug Enforcement (recall) data. |
| sha256_content_hash | string | SHA-256 of the ingested source content (bronze object). Single constant value — the whole table came from one bronze file/snapshot. |
| source_id | string | Logical source identifier. CONSTANT 'fda_drug_enforcement'. |
| is_current | boolean | SCD/current-flag. TRUE for 100% of rows — this is a full-replace snapshot with NO retained history. Filtering is_current=true is a harmless no-op; there are no is_current=false rows to dedup against. |
| bronze_object_key | string | S3 key of the bronze source object (e.g. 'drug/bronze/fda_drug_enforcement/2026-06-02/...json'). Single constant value for the snapshot. Provenance only. |
| s3_version_id | string | S3 object version id of the bronze file. Single constant value. Provenance only; the profiler mis-listed it as a candidate key because it is constant. |
| vintage_tag | string | Vintage label. CONSTANT literal string 'none' for every row — carries no information. |
| drug_source_version_id | string | Intended drug-source version linkage. 100% NULL — unused/unpopulated in this table. Do not rely on it. |
| recall_initiation_date | date | Date the firm started the recall. The canonical recall start; equals effective_period_start exactly. First in the FDA date chain (init <= center_classification <= report <= termination). Never null; range 2006-02-24 to 2026-05-12. |
| center_classification_date | date | Date the FDA center assigned the hazard classification. Never null. Falls between recall_initiation_date and report_date in 17,151/17,679 rows. |
| report_date | date | Date FDA published the recall in the enforcement report. Never null; range 2012-06-20 to 2026-05-27. This is the best 'when did it become public' date and the most useful for recency/feed-style queries (note it starts in 2012 even though initiation dates go back to 2006). |
| termination_date | date | Date FDA terminated (closed) the recall. NULL for 2,883 rows — essentially all 'Ongoing' (2,452) and most 'Completed' (430), plus 1 'Terminated' edge case. Presence ≈ a closed recall. Last in the date chain. |
| openfda_brand_name | string | openFDA-enriched brand name (e.g. 'VENTOLIN'). SPARSE: only 3,157/17,679 rows (~18%) enriched; rest NULL because FDA could not match the product to an openFDA drug record. Often equals the generic name for generics. |
| openfda_generic_name | string | openFDA-enriched generic/established name (e.g. 'ALBUTEROL SULFATE'). Same ~18% coverage as brand (3,157 populated). |
| openfda_manufacturer_name | string | openFDA-enriched labeler/manufacturer name. ~18% coverage (3,157). May differ from recalling_firm (which is often a repackager/distributor). |
| openfda_product_ndc_first | string | Intended first product NDC from openFDA. 100% NULL across the entire table — completely unpopulated. The profiler listed it as part of a candidate key only because an all-NULL column is trivially 'unique'. DO NOT use for NDC joins; get NDCs from product_description text instead. |
| openfda_application_number_first | string | openFDA FDA application number (NDA/ANDA/BN...), e.g. 'NDA020983', 'ANDA076477'. Populated for 3,098 rows (~18%); NULL elsewhere. Useful key into FDA drug-application data when present. |
| openfda_route | string | openFDA route of administration (e.g. 'ORAL', 'INTRAVENOUS', 'RESPIRATORY (INHALATION)'). ~18% coverage (3,106). |
| openfda_dosage_form | string | openFDA dosage form (tablet, injection, etc.). Same ~18% enrichment coverage as the other openfda_* columns. |
| openfda_pharm_class_epc | string | openFDA Established Pharmacologic Class (EPC), e.g. 'Angiotensin 2 Receptor Blocker [EPC]', 'Corticosteroid [EPC]'. Sparsest enrichment: only 1,092 rows (~6%) — present only when the matched product has an EPC. |

Tools that read it: recall_spend_window

Query it: `query_substrate` with `SELECT ... FROM drug_silver.fact_drug_recall WHERE ... LIMIT 100`; read `describe_data_source("drug_silver.fact_drug_recall")` first.