# FDA Drug Shortages

`drug_silver.fact_drug_shortage` · Drugs and formularies · status live · 1,733 rows

The FDA drug-shortage list — current/resolved shortages by package NDC with availability status, generic name, manufacturer, route, and openFDA enrichments (brand, RxCUI, application number). Supply-risk signal for a drug book (1,733 records).

**Grain.** One row per drug-shortage EVENT = one (package_ndc x initial_posting_date) pair, restricted to the SCD-current slice (is_current=true). Verified: 1700 rows, and DISTINCT (package_ndc, initial_posting_date) = exactly 1700. package_ndc alone is NOT unique (1663 distinct) because 37 NDCs have two concurrent open shortage events distinguished only by their FDA posting date. effective_period_start and observed_period are byte-identical aliases of initial_posting_date, so either also completes the key, but initial_posting_date is the cleanest semantic choice.

**How to use it.** Find shorted/discontinued drugs that have measurable acquisition-cost exposure: SELECT s.package_ndc, s.generic_name, s.availability, s.update_type, s.related_info FROM fact_drug_shortage s JOIN (SELECT DISTINCT package_ndc, ndc11 FROM dim_drug WHERE package_ndc IS NOT NULL AND ndc11 IS NOT NULL) d ON s.package_ndc = d.package_ndc JOIN (SELECT DISTINCT ndc11 FROM fact_drug_acquisition_cost) c ON d.ndc11 = c.ndc11 WHERE s.is_current = true AND s.availability IN ('Unavailable','Limited Availability'). This answers 'which drugs currently in (or with constrained) supply have a NADAC price, so I can quantify shortage-driven cost/price-spike risk' — it returned 596 priced shorted NDCs in this snapshot.

**Traps.**
- is_current is TRUE for ALL 1700 rows and is an SCD-CURRENT flag, NOT a 'latest snapshot' flag. The table merges 7 ingest snapshots (1676 rows from the 2026-06-03 master + 24 carried over from 6 earlier daily snapshots) keeping one current row per NDC; the 24 carryovers have ZERO NDC overlap with the master. Always filter WHERE is_current = true, and never assume all current rows share one bronze_object_key/asserted_at.
- package_ndc alone is NOT the grain (1663 distinct vs 1700 rows). 37 NDCs have two simultaneous open shortage events distinguished only by initial_posting_date. The profiler's 10-column grain is a massive superset; the true minimal key is just (package_ndc, initial_posting_date).
- Three date columns are byte-identical copies: initial_posting_date == observed_period == effective_period_start (event start) for all 1700 rows; and effective_period_end == asserted_at (the snapshot date) for all 1700 rows. effective_period_end is the snapshot-seen date, NOT an open-ended validity sentinel. Do not treat these as independent dimensions.
- Two columns are 100% NULL despite appearing in the profiler's reported grain key: openfda_product_ndc_first and drug_source_version_id. Never key, filter, or join on them.
- availability NULL is meaningful, not missing-at-random: it is NULL for every 'New' row (515) and 29 'Revised' rows (status not yet classified). Also two raw FDA source typos are preserved verbatim ('Unavailabld', 'Avaliable') — normalize availability before grouping. update_type has only 3 values: Reverified/New/Revised.
- NDC format mismatch blocks naive cost joins. package_ndc is FDA dashed 5-4-2 ('0078-0405-05'), while cost tables (e.g. fact_drug_acquisition_cost) use zero-padded 11-digit ndc11 ('00780040505'). A plain replace('-','') join returns 0 matches. Join through dim_drug.package_ndc (same dashed format; matches 1458/1663 = 87.7%) to pick up ndc11, then to NADAC (596 NDCs get an acquisition cost). The openfda_* enrichment columns are ~85-89% populated (NULL = no openFDA label match, e.g. compounded/unapproved products).

| column | type | meaning |
|---|---|---|
| package_ndc | string | FDA package-level NDC in dashed 5-4-2 format (e.g. '0078-0405-05'); uniformly 12 chars / dashed for all 1700 rows. This is the de-dashed-10-digit FDA convention, NOT the zero-padded 11-digit HIPAA ndc11. Identifies the specific drug package on FDA's shortage list. 1663 distinct values. |
| initial_posting_date | date | DATE FDA first posted this shortage event. The real event-start dimension and the second half of the grain key. Spans 2012-01-01 to 2026-06-01 (211 distinct dates). A drug can have >1 posting date if FDA opened separate shortage events for the same NDC (e.g. Dextrose 50% NDC 0409-4902-34 has events from 2022-01-13 AND 2025-11-07). IDENTICAL to observed_period and effective_period_start in every one of the 1700 rows. |
| update_type | string | FDA's update classification for the shortage record. Exactly 3 values: 'Reverified' (769) = FDA re-confirmed an ongoing shortage, 'New' (515) = newly posted, 'Revised' (416) = details updated. NOT a uniqueness key (package_ndc+update_type = 1699, not 1700). |
| availability | string | Current supply status of the drug. Values: 'Available' (733, supply recovered), 'Unavailable' (296), 'Limited Availability' (125), NULL (544). Plus 2 source typos preserved verbatim: 'Unavailabld' (1) and 'Avaliable' (1). NULL is meaningful: it is NULL for ALL 515 'New' rows and 29 'Revised' rows (status not yet classified on a fresh posting). |
| generic_name | string | FDA's free-text drug name as listed on the shortage notice (e.g. 'Docetaxel Injection', 'Amlodipine Besylate; Benazepril Hydrochloride Capsule'). Includes dosage form. 254 distinct values. 100% populated. This is FDA's own string, distinct from the cleaner openfda_generic_name. |
| contact_info | string | Manufacturer contact phone number as a free-text string (e.g. '888-669-6682'). 100% populated. |
| related_info | string | FDA free-text reason/note for the shortage. 520 NULL; common values: 'Discontinuation of the manufacture of the drug' (317), 'Check wholesalers for inventory' (100), 'Estimated recovery: TBD' (90). Text is non-normalized (multiple near-duplicate phrasings of 'discontinuation'). |
| observed_period | date | Provenance/temporal alias. DATE the shortage was observed. IDENTICAL to initial_posting_date and effective_period_start for all 1700 rows — a redundant copy, not an independent dimension. 100% populated. |
| asserted_at | date | DATE this row was asserted/loaded by Vlada's pipeline = the snapshot/observation date. IDENTICAL to effective_period_end for all 1700 rows. Takes 7 values matching the 7 ingest snapshots: 2026-05-16, -19, -23, -26, -28, -30 (2-7 rows each) and 2026-06-03 (1676 rows). |
| effective_period_start | date | SCD effective-from date. IDENTICAL to initial_posting_date / observed_period for all 1700 rows. Profiler's named temporal column. |
| effective_period_end | date | SCD effective-to date. IDENTICAL to asserted_at (the snapshot date) for all 1700 rows — i.e. the record is considered valid through the snapshot in which it was last seen, NOT an open-ended 9999 sentinel. |
| cms_vintage_sequence | int | Vestigial provenance integer from the shared drug-silver schema. Constant = 1 for all rows (this is an FDA/openFDA source, not a CMS-vintaged one). No analytic meaning here. |
| parse_revision | int | Parser revision integer. Constant = 1 for all rows. |
| parser_artifact_hash | string | SHA-256 of the parser artifact used. 7 distinct values, one per ingest snapshot. Provenance, not analytic. |
| source_url | string | Origin endpoint. Constant = 'https://api.fda.gov/drug/shortages.json' (the openFDA Drug Shortages API) for all rows. |
| sha256_content_hash | string | SHA-256 of the bronze source file content. One value per snapshot (used for change detection / dedup). |
| source_id | string | Logical source identifier. Constant = 'fda_drug_shortages' for all rows. |
| is_current | boolean | SCD-current flag. TRUE for ALL 1700 rows. Means 'latest known state of this NDC's shortage record across ALL snapshots', NOT 'rows from the latest snapshot' — the silver builder merges snapshots and keeps one current row per NDC. Verified: the 24 rows carried over from the 6 earlier snapshots have 0 NDC overlap with the 2026-06-03 master. Always filter WHERE is_current = true. |
| bronze_object_key | string | S3 key of the bronze JSON the row came from (e.g. 'drug/bronze/fda_drug_shortages/2026-06-03/...json'). 7 distinct values = the 7 snapshots. The date in the path = the snapshot/asserted_at date. Provenance; NOT a grain key. |
| s3_version_id | string | S3 object version id of the bronze file. 7 distinct values (one per snapshot). Provenance. |
| vintage_tag | string | Vintage label. Constant = 'none' for all rows. |
| drug_source_version_id | string | TRAP: 100% NULL for all 1700 rows. Despite being included in the profiler's reported grain superset, it carries no value. Do not key or join on it. |
| openfda_brand_name | string | openFDA-enriched brand name (e.g. 'LOTREL', 'DOCETAXEL'). 89.4% populated (1520/1700). NULL = the FDA shortage NDC had no matching openFDA drug-label record (compounded / unapproved / unmatched products). |
| openfda_generic_name | string | openFDA-enriched, normalized generic name (e.g. 'AMLODIPINE BESYLATE AND BENAZEPRIL HYDROCHLORIDE'). 89.4% populated. Cleaner counterpart to the free-text generic_name column. |
| openfda_manufacturer_name | string | openFDA-enriched manufacturer/labeler name (e.g. 'Novartis Pharmaceuticals Corporation'). 89.4% populated. 155 distinct values. |
| openfda_product_ndc_first | string | TRAP: 100% NULL for all 1700 rows. Intended to hold the first openFDA product-NDC but never populated. Included in the profiler's grain superset but useless. Do not rely on it; use package_ndc. |
| openfda_application_number_first | string | openFDA FDA application number, first of any matches (e.g. 'NDA020364', 'NDA022234'). 88.5% populated. Bridges to Orange Book / approval data. |
| openfda_route | string | openFDA route of administration (e.g. 'ORAL', 'INTRAVENOUS'). 88.9% populated. |
| openfda_substance_name | string | openFDA active substance name (e.g. 'AMLODIPINE BESYLATE', 'DOCETAXEL ANHYDROUS'). 88.8% populated. |
| openfda_rxcui_first | string | RxNorm RXCUI, first of any matches (e.g. '898342'). 85.2% populated. The clinical-concept bridge to RxNorm-keyed tables. |

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