# FDA NDC Directory (drug product dimension)

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

The FDA National Drug Code Directory — the authoritative drug product dimension (NDC11, product code, package code, labeler, proprietary/ non-proprietary name, dosage form, route). The identity spine for every NDC that appears in NADAC, ASP, WAC, Part D pricing, and formulary tables.

**Grain.** One row per drug package (NDC-11) per weekly FDA-NDC-Directory snapshot. Verified: ndc11 x observed_period = 642,379 distinct = 642,379 total rows. The table currently holds 3 full snapshots (2026-05-15, 2026-05-25, 2026-06-01); within any single snapshot ndc11 (equivalently package_ndc) is unique. ndc11 alone is NOT unique across the table (214,924 distinct ndc11 but 642,379 rows — ~3 snapshot copies each).

**How to use it.** Resolve a drug's identity/attributes and join it to cost data. Example (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'). Answers '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. Always pin to one snapshot via observed_period (do NOT use is_current).

**Traps.**
- is_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.
- ndc11 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).
- Three 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).
- start_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.
- This 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.
- Snapshots 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.

| column | type | meaning |
|---|---|---|
| ndc11 | string | Canonical 11-digit National Drug Code, zero-padded, no dashes (the package-level drug identifier). VERIFIED = labeler_code(5) + product_code(4) + package_code(2) concatenated (0 mismatches). This is the primary join key to every fact_drug_* cost table. 214,924 distinct values; ~3 copies (one per snapshot). |
| labeler_code | string | First NDC segment (5 chars) = FDA-assigned manufacturer/labeler code. 5,354 distinct. Always 5 chars. |
| product_code | string | Second NDC segment (4 chars) = the product (drug formulation) within a labeler. 9,573 distinct. |
| package_code | string | Third NDC segment (2 chars) = the specific package size/configuration of the product. |
| product_ndc | string | Product-level NDC in dashed format, e.g. '0002-0152' (labeler-product, no package segment). 112,469 distinct products in latest snapshot vs 214,474 packages. One ndc11 maps to exactly one product_ndc. Use this to roll packages up to the product. |
| package_ndc | string | Full package-level NDC in FDA dashed format, e.g. '0002-0152-01'. 1:1 with ndc11 (same identity, dashed presentation). 214,924 distinct. |
| is_active | boolean | Boolean. TRAP: it is TRUE for 100% of rows in every snapshot — the FDA NDC Directory file lists currently-marketed finished products, so this is effectively a constant, not a useful filter. To find discontinued packages use end_marketing_date instead, or compare across snapshots. |
| proprietary_name | string | Brand / trade name, e.g. 'Zepbound', 'Humulin'. Always populated (0 null). |
| nonproprietary_name | string | Generic / nonproprietary name, e.g. 'tirzepatide', 'Insulin human'. Always populated (0 null). |
| dosage_form_name | string | FDA dosage form, e.g. 'INJECTION, SOLUTION', 'TABLET'. 137 distinct forms. Always populated. |
| route_name | string | Route of administration, e.g. 'SUBCUTANEOUS', 'ORAL' (top route, ~140K), 'TOPICAL', 'INTRAVENOUS'. 192 distinct; multi-route combos use '; ' delimiter (e.g. 'INTRADERMAL; PERCUTANEOUS; SUBCUTANEOUS'). ~1% null. |
| marketing_category_name | string | FDA marketing/approval pathway: ANDA (generic, ~107K, the plurality), OTC MONOGRAPH DRUG, UNAPPROVED HOMEOPATHIC, NDA (brand new drug), BLA (biologic), NDA AUTHORIZED GENERIC, EMERGENCY USE AUTHORIZATION, etc. |
| application_number | string | FDA submission identifier with a pathway prefix that mirrors marketing_category_name: ANDA##### (generic), NDA##### (new drug), BLA##### (biologic), M###/part### (OTC monograph citations). ~15% null in latest snapshot — OTC-monograph and homeopathic products often have none. |
| labeler_name | string | Manufacturer / labeler name, e.g. 'Eli Lilly and Company'. 7,726 distinct in latest snapshot. Always populated. |
| substance_name | string | Active ingredient(s), UPPERCASE; combination drugs delimit multiple actives with '; ' (e.g. 'TROSPIUM CHLORIDE; XANOMELINE' for Cobenfy). ~1.3% null. |
| active_numerator_strength | string | DEAD COLUMN — 100% NULL across all 642,379 rows. Exists in schema but never populated; the silver build does not split strength out. Get strength from package_description text or downstream tables. |
| active_ingred_unit | string | DEAD COLUMN — 100% NULL across all 642,379 rows. Companion to active_numerator_strength, also never populated. |
| pharm_classes | string | FDA Established Pharmacologic Class string(s), comma-separated with bracketed type tags: [EPC] established class, [MoA] mechanism of action, [CS] chemical structure, [PE] physiologic effect. ~34% null (homeopathic/OTC monograph products often lack a class). |
| dea_schedule | string | DEA controlled-substance schedule: NULL = non-controlled (~95% of rows / 202,889), else CII / CIII / CIV / CV. NULL is meaningful (= not a controlled substance), not missing data. |
| product_type_name | string | FDA product type: HUMAN PRESCRIPTION DRUG (~118K), HUMAN OTC DRUG (~91K), NON-STANDARDIZED ALLERGENIC, PLASMA DERIVATIVE, STANDARDIZED ALLERGENIC, VACCINE, CELLULAR THERAPY. |
| package_description | string | Free-text human-readable package contents incl. quantities and unit sizes, e.g. '1 VIAL, SINGLE-DOSE in 1 CARTON (0002-0152-01) / .5 mL in 1 VIAL, SINGLE-DOSE'. This is where pack size / unit strength effectively lives (since the strength columns are null). |
| start_marketing_date | string | Date the package began marketing. STRING in YYYYMMDD format (length always 8), NOT a date type. Range 19000101 (sentinel/placeholder for very old/unknown) to 20260529. Parse/cast before date math. |
| end_marketing_date | string | Date marketing ends/ended, STRING YYYYMMDD. ~97.5% NULL — NULL is meaningful: package is still actively marketed with no scheduled end. A non-null value (e.g. '20261215') indicates a planned/known discontinuation. |
| observed_period | date | Snapshot date = the day this FDA NDC Directory file was ingested. One of 3 values (2026-05-15, 2026-05-25, 2026-06-01). Part of the grain key. Equal to asserted_at, effective_period_start, and effective_period_end for the same row. |
| asserted_at | date | When this snapshot's facts were asserted; identical to observed_period (1:1 with the snapshot). Redundant with observed_period. |
| effective_period_start | date | Start of the row's validity window; equals the snapshot date and equals effective_period_end (single-day, point-in-time snapshot — this is NOT a true SCD2 range). Profiler's chosen time column; interchangeable with observed_period as the snapshot key. |
| effective_period_end | date | End of validity window; equals effective_period_start for every row (no open-ended or multi-day ranges). Confirms snapshot, not range, semantics. |
| cms_vintage_sequence | int | Vintage sequence integer; constant value 1 for all rows (not a useful discriminator here). |
| parse_revision | int | Parser revision number; constant 1 for all rows. |
| parser_artifact_hash | string | SHA-256 of the parser artifact used to build the snapshot. 3 distinct values (one per snapshot). Provenance/lineage. |
| source_url | string | Origin URL of the raw file; single constant value 'https://www.accessdata.fda.gov/cder/ndctext.zip' (FDA NDC Directory zip). |
| sha256_content_hash | string | SHA-256 of the raw source file content; 3 distinct values, 1:1 with the snapshot (changes week to week as FDA updates the file). Provenance/dedup key. |
| source_id | string | Logical source identifier; single constant 'fda_ndc_directory'. Confirms the whole table is sourced solely from the FDA NDC Directory. |
| is_current | boolean | Boolean is-current flag. MAJOR TRAP: TRUE for 100% of rows (all 3 snapshots), so it does NOT isolate the latest snapshot. Do not filter on is_current to dedup — use MAX(observed_period) or the specific snapshot date instead. |
| bronze_object_key | string | S3 key of the bronze raw object backing the snapshot, e.g. 'drug/bronze/fda_ndc_directory/2026-06-01/0776ac0c...-ndctext.zip'. 3 distinct, 1:1 with snapshot; the date is embedded in the path. |
| s3_version_id | string | S3 object version id of the bronze file; 3 distinct (one per snapshot). Lineage. |
| vintage_tag | string | Vintage label; constant literal 'none' for all rows (unused). |
| drug_source_version_id | string | DEAD COLUMN — 100% NULL across all 642,379 rows. Intended cross-source version linkage but never populated. |

Tools that read it: ira_mfp_exposure, recall_spend_window

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