FDA NDC Directory (drug product dimension)

What it is

1. 1The FDA NDC Directory drug master (package-level catalog) for the cost-data platform — one row per NDC-11 package per weekly snapshot — providing the canonical drug identity and descriptors (names, labeler, form/route, approval pathway, DEA schedule, pharm class) that all drug-cost fact tables join to via ndc11.

Grain

How to read it

1. 1Resolve a drug's identity/attributes and join it to cost data.
2. 2Example (current GLP-1 packages with their identity, ready to join to ASP/price facts): SELECT ndc11, proprietary_name, nonproprietary_name, labeler_name, dosage_form_name, route_name, marketing_category_name, package_description FROM drug_silver.dim_drug WHERE observed_period = (SELECT MAX(observed_period) FROM drug_silver.dim_drug) AND nonproprietary_name IN ('tirzepatide','semaglutide').
3. 3Answers 'what packages/NDCs exist for this drug, who makes them, and what is their FDA identity' — then join ndc11 to fact_drug_asp_quarterly / fact_drug_consumer_price / fact_part_d_plan_drug_pricing to attach prices.
4. 4Always pin to one snapshot via observed_period (do NOT use is_current).

Traps

1. 1is_current is useless for dedup: ALL 642,379 rows have is_current=true (no false rows exist yet). To get a single current view you MUST filter on a snapshot date, e.g. WHERE observed_period = (SELECT MAX(observed_period) FROM dim_drug). Treating is_current=true as 'the latest' returns ~3x duplicate NDCs.
2. 2ndc11 is NOT unique table-wide — it repeats once per weekly snapshot (214,924 distinct ndc11 vs 642,379 rows). Any join on ndc11 alone fans out ~3x; always pair ndc11 with a snapshot filter (the true grain is ndc11 x observed_period).
3. 3Three columns are 100% NULL across every row and look like real fields but carry no data: active_numerator_strength, active_ingred_unit, and drug_source_version_id. Do not rely on them for strength/units — strength is only available inside the package_description free-text (and richer detail lives in dim_drug_label / dim_orange_book_product).
4. 4start_marketing_date and end_marketing_date are STRINGS in YYYYMMDD format, not DATE types — cast before comparing. start_marketing_date uses '19000101' as a sentinel for old/unknown dates. end_marketing_date is ~97.5% NULL, and NULL means 'still actively marketed' (not missing); is_active is always true so it cannot flag discontinued packages — use a non-null end_marketing_date or cross-snapshot disappearance instead.
5. 5This is the drug spine/master only (package identity + descriptors). It contains NO prices. Drug cost lives in sibling fact tables in drug_silver (fact_drug_asp_quarterly, fact_drug_acquisition_cost, fact_drug_consumer_price, fact_drug_va_fss_price, fact_drug_medicaid_rebate_product, fact_part_d_plan_drug_pricing, fact_drug_formulary, etc.), which you join back to this table on ndc11. Deeper FDA label/approval/patent attributes live in dim_drug_label, dim_drug_application, dim_orange_book_product, and dim_purple_book_product — not here.
6. 6Snapshots are point-in-time, not SCD2 ranges: effective_period_start == effective_period_end == observed_period == asserted_at for every row (single-day). Across the 3 snapshots ~1,145 NDCs were added between 2026-05-15 and 2026-06-01, so the NDC universe grows week to week — pick a snapshot deliberately when reproducing results.

Columns (38)

Query it

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