Drugs and formularies

FDA Drug Shortages

drug_silver.fact_drug_shortage·live·silver·other·FDA Drug Shortages

Rows

1,733

Columns

History

2012-01-01 to 2026-06-01

Card

yesgrain, traps, filters

Typed tools

What it is

1The current US drug-shortage list from the FDA (via the openFDA Drug Shortages API), one row per shorted/discontinued drug package, enriched with openFDA drug-label fields (brand, manufacturer, route, RxNorm RXCUI, FDA application number).
2In a cost catalog it is the supply-disruption signal: it flags which NDCs are in shortage or discontinued so an agent can flag price-spike / acquisition-cost risk by bridging to NADAC, ASP, and other cost tables on NDC.

Grain

Key Grain

Key	Grain
`package_ndcinitial_posting_date`	1One row per drug-shortage EVENT = one (package_ndc x initial_posting_date) pair, restricted to the SCD-current slice (is_current=true). 2Verified: 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.

package_ndcinitial_posting_date

1One row per drug-shortage EVENT = one (package_ndc x initial_posting_date) pair, restricted to the SCD-current slice (is_current=true).
2Verified: 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 read it

1Find 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').
2This 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

1is_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.
2package_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).
3Three 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.
4Two 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.
5availability 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.
6NDC 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).

Columns (30)

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

describe_data_source(source_fqn="drug_silver.fact_drug_shortage")     # the card, live
query_substrate(sql="SELECT * FROM drug_silver.fact_drug_shortage WHERE package_ndc = '<value>' AND initial_posting_date = '<value>' LIMIT 100")

Item	Value
Typed tools that read it	none; raw SQL
Identity key	`package_ndc`
Join	`how_to_join(source_fqn="drug_silver.fact_drug_shortage")`
Raw door	Athena (read-only via query_substrate): drug_silver.fact_drug_shortage

This page as markdown: /docs-md/data/drug_silver_fact_drug_shortage.md · every page in one file: /llms-full.txt