# Medicaid State Drug Utilization (by state × NDC × quarter)

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

CMS State Drug Utilization Data — Medicaid units reimbursed, prescriptions, and total / Medicaid / non-Medicaid amounts reimbursed by state, NDC, and quarter (FFS vs MCO via utilization_type). The Medicaid DRUG spend lens by state (NOTE: this is Medicaid drug utilization, not Medicaid MEDICAL rates, which remain absent).

**Grain.** One row per (utilization_type x state x NDC-11 package x calendar quarter). Verified: across all 25,810,702 rows, COUNT(*) exactly equals COUNT(DISTINCT (utilization_type, state, ndc11, data_year, data_quarter)) = 25,810,702, with no filter needed (entire physical table is is_current=true). It is a measured fact row: how many Rx of that drug package were reimbursed by that state's Medicaid program (FFS or managed care) in that quarter, and the dollars paid.

**How to use it.** National 2024 spend on a specific drug across all of Medicaid: SELECT data_year, data_quarter, SUM(total_amount_reimbursed_usd) AS spend, SUM(number_of_prescriptions) AS rx FROM drug_silver.fact_drug_medicaid_utilization WHERE state='XX' AND data_year=2024 AND suppression_used=false AND ndc11='<ndc>' GROUP BY data_year, data_quarter; — returns quarterly Medicaid-paid dollars and Rx counts for that drug package nationally (use state='XX' for national, or state<>'XX' grouped by state for a state breakdown), and SUM utilization_type FFSU+MCOU for the full program.

**Traps.**
- HALF THE TABLE HAS NO NUMBERS: suppression_used=true on 12.78M rows (~49.5%) and on those EVERY metric (units_reimbursed, number_of_prescriptions, all *_amount_reimbursed_usd) is NULL — CMS privacy-suppressed small cells. Always filter WHERE suppression_used=false (or guard NULLs) before SUM/AVG, and remember unsuppressed rows always have >=11 prescriptions, so low-volume drugs are systematically missing.
- state='XX' IS THE NATIONAL ROLL-UP, NOT A STATE. Including it alongside per-state rows double-counts. Worse, XX is NOT the simple sum of the visible state rows: for 2023-Q1 unsuppressed, XX total=$27.86B vs sum-of-states=$26.14B, because the national figure also absorbs cells that were suppressed at the state level. Pick state<>'XX' for state analysis OR state='XX' for national totals — never mix, and don't reconstruct national by summing states (you'll undercount).
- GRAIN NEEDS BOTH utilization_type AND state. ndc11 alone is far from unique (71,806 distinct vs 25.8M rows); dropping utilization_type drops to 19.16M distinct (FFSU and MCOU rows collide on the same drug/state/quarter), and dropping state collapses to 1.56M. The full minimal key is (utilization_type, state, ndc11, data_year, data_quarter).
- labeler_code, product_code, package_size, observed_period, effective_period_start/end are all DERIVED, not independent: ndc11 is byte-for-byte concat(labeler_code,product_code,package_size) (0 mismatches) and the date columns are just the quarter boundaries from data_year/data_quarter. They add no grain and shouldn't be treated as separate keys. Note package_size is the NDC package CODE, not a dispensed quantity.
- product_name is unreliable free text — one ndc11 can appear under 12+ different name spellings across filings. Identify drugs by ndc11 (or labeler/product code), never by product_name.
- Provenance/SCD columns are inert in this snapshot: is_current=true, cms_vintage_sequence=1, parse_revision=1, vintage_tag='none', asserted_at=2026-05-16, drug_source_version_id=NULL are all single-valued. is_current filtering is currently a no-op and drug_source_version_id can't be joined yet — they're forward-compat scaffolding, so don't read meaning into them today. PR (Puerto Rico) data exists only from 2023 onward (52 states 2020-2022, 53 from 2023).

| column | type | meaning |
|---|---|---|
| utilization_type | string | Medicaid program channel. EXACTLY 2 values: FFSU = Fee-For-Service Utilization (12.30M rows), MCOU = Managed Care Org Utilization (13.51M rows). Part of the grain — the same drug/state/quarter appears once per channel. Sum both for total Medicaid; do not assume one. |
| state | string | 2-letter postal code of the state Medicaid program. 53 distinct values: 50 states + DC + PR (Puerto Rico, appears only from 2023) + 'XX'. 'XX' (1.56M rows) is the CMS NATIONAL aggregate, NOT a state — see gotchas. Part of grain. |
| ndc11 | string | 11-digit National Drug Code identifying the exact drug package (labeler+product+package). 71,806 distinct. Always exactly = concat(labeler_code, product_code, package_size) (0 mismatches). The drug-join key. Part of grain. |
| data_year | int | Calendar year of the utilization quarter (2020-2024). Part of grain; redundant with effective_period_start's year. |
| data_quarter | int | Calendar quarter 1-4 of utilization. Part of grain; together with data_year fully determines all period columns. |
| labeler_code | string | First 5 digits of the NDC = the drug labeler/manufacturer (FDA labeler code). 1,069 distinct. Derived from ndc11; descriptive, not independent grain. |
| product_code | string | NDC product segment, the 4 digits after the labeler code (drug+strength+form). 7,182 distinct. Derived from ndc11; descriptive only. |
| package_size | string | NDC package-size segment (trailing digits of the NDC, the package code — NOT a quantity/volume). Derived from ndc11; descriptive only. |
| product_name | string | CMS-reported short drug name (free text, often truncated e.g. 'TRIAMCINOL','CLOBETASOL'). NOT stable per NDC — a single ndc11 can carry 12+ different spellings across filings. Use ndc11 to identify the drug, never product_name. |
| suppression_used | boolean | CMS privacy flag. true (12.78M rows, ~49.5%) = the cell was suppressed for low volume and ALL metric columns are NULL. false (13.03M rows) = metrics populated. This is the single biggest data-handling fact: half the rows have no numbers. |
| units_reimbursed | double | Total drug units (e.g. tablets/mL) reimbursed in the quarter. double. NULL when suppression_used=true. Range on populated rows: 0.003 to 6.15e8. No negatives observed. |
| number_of_prescriptions | int | Count of reimbursed prescription claims (Rx) for that drug/state/channel/quarter. int. NULL when suppressed. On populated rows MIN=11 — confirming CMS suppresses any cell with <11 Rx. Max 1,812,525. |
| total_amount_reimbursed_usd | double | Total dollars reimbursed for the drug (Medicaid + non-Medicaid). double USD. NULL when suppressed. 0 to 6.27e8, no negatives. Equals medicaid + non_medicaid (matches for all but 8 rounding rows). |
| medicaid_amount_reimbursed_usd | double | Portion of total reimbursed paid by the state Medicaid program itself. double USD. NULL when suppressed. |
| non_medicaid_amount_reimbursed_usd | double | Portion of total reimbursed paid by third parties / patient cost-share (non-Medicaid). double USD. NULL when suppressed. medicaid + non_medicaid = total. |
| observed_period | date | date = first day of the utilization quarter. Fully derived from data_year/data_quarter (e.g. 2021-Q4 -> 2021-10-01). Equals effective_period_start. |
| asserted_at | date | date the ingest pipeline asserted this snapshot. Constant 2026-05-16 for every row (single load). Lineage scaffolding, not analytic. |
| effective_period_start | date | date = first day of the quarter the utilization covers (= observed_period). The profiler's temporal column. Derived from year+quarter. |
| effective_period_end | date | date = last day of the quarter (e.g. 2020-03-31, 2020-06-30). Derived from year+quarter; with start it bounds the quarter. |
| cms_vintage_sequence | int | Intended CMS restatement-vintage ordinal. Constant = 1 for all rows (only one vintage loaded). Currently carries no discriminating value. |
| parse_revision | int | Parser revision number. Constant = 1 for all rows. Lineage scaffolding. |
| parser_artifact_hash | string | SHA-256 of the parser code/artifact that produced the row (lineage). Single value observed across the load. |
| source_url | string | CMS download URL of the annual source CSV. 5 distinct values, one per year: https://download.medicaid.gov/data/StateDrugUtilizationData-YYYY.csv (2020-2024). |
| sha256_content_hash | string | SHA-256 of the source CSV content (the bronze object), for provenance/dedup. |
| source_id | string | Logical source identifier. Constant 'medicaid_sdud' for every row — single-source table. |
| is_current | boolean | SCD-2 current-version flag. true for 100% of rows (no superseded versions yet). Filtering on it is a no-op today but keep it for forward compatibility. |
| bronze_object_key | string | S3 key of the raw bronze CSV this row was parsed from (lineage), e.g. drug/bronze/medicaid_sdud/2021/<hash>-StateDrugUtilizationData-2021.csv. |
| s3_version_id | string | S3 object version id of the bronze source file (lineage). |
| vintage_tag | string | Human vintage label. Constant 'none' for all rows (no vintage tagging applied yet). |
| drug_source_version_id | string | FK to a drug source-version registry. NULL for every row currently — not yet populated; do not rely on it for joins. |

Tools that read it: ira_mfp_exposure, recall_spend_window

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