Drugs and formularies

Medicaid Drug Rebate Program — product file

drug_silver.fact_drug_medicaid_rebate_product·live·silver·quarterly·Medicaid Drug Rebate Program — product file

Rows

N/A

Columns

History

2014-07-01 (2014 Q3) to 2026-01-01 (2026 Q1)

Card

yesgrain, traps, filters

Typed tools

What it is

1The CMS Medicaid Drug Rebate Program (MDRP) Drug Products reference list: a per-quarter snapshot of every NDC-11 package that is (or was) eligible/reportable under Medicaid drug rebates, with FDA identity, drug-classification, and rebate-eligibility metadata.
2Despite "rebate" in the name it carries NO dollar/price/rebate amounts — it is the product MASTER that anchors what is rebate-eligible, used to classify drugs (innovator vs generic), look up package/unit definitions, and date-bound a product's market/termination status for cost analysis.

Grain

Key Grain

Key	Grain
`ndc11effective_period_start`	1One row per NDC-11 package per quarterly Medicaid snapshot. 2Verified: COUNT(DISTINCT ndc11 \|\| effective_period_start) = 1,954,970 = exact row_count (and equivalently ndc11 × data_year × data_quarter). 3Within 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. 4The profiler's 14-column key is a large superset; 511 of those columns (all the labeler/product/provenance/period fields) are either constant table-wide or fully derivable.

ndc11effective_period_start

1One row per NDC-11 package per quarterly Medicaid snapshot.
2Verified: COUNT(DISTINCT ndc11 || effective_period_start) = 1,954,970 = exact row_count (and equivalently ndc11 × data_year × data_quarter).
3Within 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.
4The profiler's 14-column key is a large superset;
511 of those columns (all the labeler/product/provenance/period fields) are either constant table-wide or fully derivable.

How to read it

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

1NOT 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.
2Minimal 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.
3cod_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.
4It 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.
5ndc11 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.

Columns (39)

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

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

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

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