# Medicaid Drug Rebate Program — product file

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

The Medicaid Drug Rebate Program product file by NDC and quarter — labeler, FDA product name + application number, drug category/type, unit type, units per package, therapeutic-equivalence code, COD status. The drug-product reference for Medicaid rebate eligibility (pairs with the utilization file).

**Grain.** One row per NDC-11 package per quarterly Medicaid snapshot. Verified: COUNT(DISTINCT ndc11 || effective_period_start) = 1,954,970 = exact row_count (and equivalently ndc11 × data_year × data_quarter). Within any single quarter ndc11 is unique (max rows per ndc11×quarter = 1) and maps to exactly one labeler/product, so ndc11 alone is the per-period natural key. The profiler's 14-column key is a large superset; 11 of those columns (all the labeler/product/provenance/period fields) are either constant table-wide or fully derivable.

**How to use it.** To find all single-source innovator drugs that were actively on-market in Q4 2024: SELECT ndc11, labeler_name, fda_product_name, unit_type FROM drug_silver.fact_drug_medicaid_rebate_product WHERE effective_period_start = DATE '2024-10-01' AND drug_category = 'S' AND (termination_date IS NULL OR termination_date >= effective_period_start). This answers 'which NDCs are rebate-eligible single-source drugs active in a given quarter' and gives the canonical FDA identity (labeler, product name, NDC decomposition, unit type) you then join by (ndc11, period) to price/rebate-dollar tables (ASP/NADAC/AMP) for actual cost analysis.

**Traps.**
- NOT a rebate-AMOUNT table. There are ZERO price/dollar/URA/rebate-percent columns — only drug identity + classification + eligibility metadata. The 'rebate' in the name means 'Medicaid Drug Rebate PROGRAM product list.' For actual rebate/price dollars use a different table (e.g. ASP/NADAC/AMP sources). This is the drug MASTER, not the money.
- Minimal grain is just ndc11 × effective_period_start (= ndc11 × data_year × data_quarter), 1,954,970 rows. The profiler's 14-column key is a huge superset: 11 of those columns are either constant table-wide (source_id, bronze_object_key, s3_version_id, sha256, asserted_at) or fully derivable from the NDC/period (labeler_code, product_code, package_size_code, observed_period). Do NOT carry them as grain.
- cod_status code-system trap: the SAME status is stored both zero-padded ('01','03','10') and unpadded ('1','3','9') depending on vintage (pre-2019 mostly unpadded, 2019+ padded, 2021 mixed). Grouping or joining on raw cod_status will double-count; normalize with lpad(cod_status,2,'0'). units_per_pkg_size has the same dual-encoding problem ('0000001.000' vs '1000') and is a string, so it cannot be summed without parsing.
- It is a CUMULATIVE snapshot, not incremental: one CMS file (filename says '1q_2026') contains all 47 quarters back to 2014 Q3, and it RETAINS terminated NDCs — 44,120 rows have a termination_date earlier than their own quarter. A row's presence does NOT mean the drug was active that quarter; you must filter on termination_date IS NULL OR termination_date >= effective_period_start to get products actually on-market.
- ndc11 is NOT a static dimension across periods: 19,208 NDCs change labeler_name over time (relabeling / purchased products via purchased_product_date). The labeler/product attributes are the values AS OF that quarter. Join to other tables on (ndc11, period), and treat null termination_date / reactivation_date / purchased_product_date as 'not terminated / never reactivated / not purchased' — these nulls carry meaning. is_current is true for every row (single load), so it currently filters nothing.

| column | type | meaning |
|---|---|---|
| ndc11 | string | 11-digit National Drug Code identifying a specific drug PACKAGE. Always exactly 11 digits. Decomposes 100%: digits 1-5 = labeler_code, 6-9 = product_code, 10-11 = package_size_code. This is the per-quarter natural key. |
| data_year | int | Calendar year of the snapshot quarter (2014-2026). Collinear with effective_period_start. |
| data_quarter | int | Quarter number 1-4 of the snapshot. Together (data_year, data_quarter) is equivalent to effective_period_start. |
| labeler_name | string | Name of the labeler/manufacturer (e.g. 'AMERICAN HEALTH PACKAGING'). 1,464 distinct. A SLOWLY-CHANGING attribute: 19,208 NDCs show >1 distinct labeler_name across history (relabeling / purchased products), so it is the labeler AS OF that quarter, not a static value. |
| labeler_code | string | 5-digit labeler segment of the NDC (FDA Labeler Code). 1,186 distinct. Equals substr(ndc11,1,5) for 100% of rows. |
| product_code | string | Product segment of the NDC (digits 6-9 of ndc11). Identifies the drug formulation within the labeler. Equals substr(ndc11,6,4) for 100% of rows. |
| package_size_code | string | Package-size segment of the NDC (digits 10-11). Identifies the package within the product. Equals substr(ndc11,10,2) for 100% of rows. |
| fda_product_name | string | FDA/labeler product name and form (e.g. 'Carbidopa & Levodopa Tablet'). Free-text proprietary/established name. |
| fda_application_number | string | FDA application number (NDA/ANDA, e.g. '0074260'). Empty/null for 2,316 rows (OTC/unapproved or pre-1938 products with no application). |
| drug_category | string | MDRP covered-outpatient-drug category. Observed values: N = non-innovator multiple-source / generic (1.52M), I = innovator multiple-source (227K), S = single-source innovator (206K). Drives which rebate formula a drug falls under. |
| drug_type_indicator | string | MDRP drug type: '1' = single-source / innovator (1.72M), '2' = multiple-source / non-innovator (237K). Determines the basic vs generic rebate calculation. |
| unit_type | string | Unit of measure for pricing/rebate: TAB, ML, CAP, EA, GM, TDP, AHF, SUP, MCI. (e.g. TAB=tablet, ML=milliliter). 9 distinct values. |
| units_per_pkg_size | string | Quantity of units in the package, stored as STRING. TWO incompatible encodings across vintages: zero-padded decimal ('0000001.000','0000100.000') vs plain integer ('1000','100000','30000'). Must be normalized before numeric use. |
| fda_therapeutic_equivalence_code | string | FDA Orange Book TE rating (AB, AP, AA, AT, NR=not rated, BX=not equivalent, etc.). 'AB' dominant (1.0M). Indicates generic substitutability. |
| cod_status | string | Covered-Outpatient-Drug status code (rebate eligibility/period status). CODE-SYSTEM TRAP: stored both zero-padded ('01','03','10') and unpadded ('1','3','9') depending on vintage — 2014-2018 mostly unpadded, 2019+ padded. Normalize with lpad(cod_status,2,'0') before grouping/joining. |
| clotting_factor_indicator | boolean | Boolean: product is a blood clotting factor (true for 9,163 rows). Relevant to special rebate/coverage handling. |
| pediatric_indicator | boolean | Boolean: product has a pediatric indication/limit (true for 4,550 rows). |
| line_extension_drug_indicator | boolean | Boolean: product is a line-extension of an existing innovator drug (true for 22,298 rows). Affects the alternative-rebate (line-extension) calculation under MDRP. |
| fda_approval_date | date | FDA approval date. Fully populated (0 nulls). |
| market_date | date | Date the product was first marketed. Fully populated (0 nulls). |
| package_size_intro_date | date | Date this specific package size was introduced to market. |
| termination_date | date | Date the product was terminated from the market; NULL for 1.80M rows (92%) = not terminated. NULL-MEANS-ACTIVE. The cumulative file RETAINS terminated NDCs (44,120 rows have a termination_date before their own quarter's start), so use this date to determine active-in-period status. |
| purchased_product_date | date | Date the product/NDC was acquired by the current labeler (M&A relabeling). NULL for 1.47M rows = not a purchased product. |
| reactivation_date | date | Date a previously terminated product was reactivated. NULL for 1.94M rows (99.4%) = never reactivated. |
| observed_period | date | Snapshot period anchor; equals effective_period_start (quarter-start date) for every row. Redundant with effective_period_start/data_year/data_quarter. |
| asserted_at | date | Date this fact was asserted/loaded into the silver layer. Constant table-wide = 2026-05-16 (single ingestion of the cumulative CMS file). |
| effective_period_start | date | First day of the snapshot quarter (e.g. 2024-10-01). The canonical period key. 47 distinct quarters, 2014-07-01 to 2026-01-01, ~91-day cadence. |
| effective_period_end | date | Last day of the snapshot quarter (e.g. 2024-12-31). Fully derivable from effective_period_start. |
| cms_vintage_sequence | int | Sequence number of the CMS file vintage. Constant = 1 for all rows (only one vintage loaded so far). |
| parse_revision | int | Parser revision number. Constant = 1 for all rows. |
| parser_artifact_hash | string | SHA-256 of the parser artifact that produced the rows (lineage). Single value table-wide. |
| source_url | string | CMS download URL of the source CSV. Single value: https://download.medicaid.gov/data/drugproducts1q_2026_05082026.csv. NOTE the file name says '1q_2026' yet the file is CUMULATIVE and contains all 47 quarters back to 2014. |
| sha256_content_hash | string | SHA-256 of the raw source file content. Single value table-wide (one source file). |
| source_id | string | Logical source identifier. Constant = 'medicaid_mdrp_drug_products' for all rows. |
| is_current | boolean | Boolean is-current/dedup flag. TRUE for 100% of rows — the versioning machinery is dormant (only one load exists), so filtering on is_current=true is currently a no-op but should still be applied for forward-compatibility. |
| bronze_object_key | string | S3 key of the bronze-layer object this row came from. Single value table-wide. |
| s3_version_id | string | S3 object version id of the bronze object. Single value table-wide. |
| vintage_tag | string | Vintage label; constant = 'none' for all rows. |
| drug_source_version_id | string | Drug source-version lineage id. NULL for 100% of rows — an unpopulated placeholder column (appears in the profiler's 'unique' key only because a constant NULL is harmless). |

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