# Medicare Part D Prescriber PUF (prescribing by provider × drug × year)

`drug_silver.fact_part_d_prescriber` · Drugs and formularies · status live · 103,105,990 rows

The CMS Medicare Part D Prescriber public-use file — prescribing volume and cost per prescriber, drug, and year: total_claims, 30-day fills, day supply, total_drug_cost_usd, beneficiaries, prescriber type/specialty/geography. The provider-level drug-prescribing lens (~103M rows) — outlier prescribing, drug-mix by specialty, brand-vs-generic behavior.

**Grain.** One row per (prescriber NPI x brand_name x generic_name x data_year). VERIFIED: 103,105,990 rows = 103,105,990 distinct values of (prescriber_npi, brand_name, generic_name, data_year), both with and without the is_current filter. BOTH brand_name and generic_name are required: the same (npi, brand, year) appears with multiple generic strings (192,828 such groups) and the same (npi, generic, year) appears with multiple brands (4,687,328 such groups), so neither alone is unique. Covers 1,339,949 distinct prescribers across 4 data years (2020-2023), ~25-27M rows per year.

**How to use it.** Example: total Medicare Part D gross drug cost and claim volume for a drug by specialty/state in a year. SELECT prescriber_state, sum(total_drug_cost_usd) AS gross_cost, sum(total_claims) AS claims, count(DISTINCT prescriber_npi) AS prescribers FROM fact_part_d_prescriber WHERE is_current AND data_year=2023 AND brand_name='Eliquis' AND prescriber_type='Cardiology' GROUP BY prescriber_state ORDER BY gross_cost DESC. Answers which states' cardiologists drive the most Part D spend on Eliquis and at what volume. Sum claims/cost, not beneficiaries (suppressed in 57% of rows).

**Traps.**
- The grain needs BOTH brand_name and generic_name -- it is NOT (npi, generic, year) [only 97.6M distinct] nor (npi, brand, year) [only 102.9M distinct]. Only (npi, brand, generic, year) hits 103,105,990 = exact row count. Dropping either column double-counts a prescriber's spend.
- Claims floor = 11, and suppressed combos are DELETED not flagged: min(total_claims)=11 and 0 rows have <11. A prescriber-drug pair absent from the table means it was either never prescribed OR had <=10 claims -- you cannot tell which, and absence must NOT be read as zero. This biases per-prescriber and long-tail-drug totals downward.
- Two independent suppression systems on the >=65 block: ge65_suppression_flag (non-null => ALL five ge65_* metrics NULL: '*'=<=10 benes, '#'=count 11-110) AND a separate ge65_bene_suppression_flag that NULLs only ge65_total_beneficiaries. NULL in any ge65 metric = redacted, never zero. The non-suppressed total_* metrics (claims/30day_fills/day_supply/drug_cost) are ALWAYS populated.
- total_beneficiaries (the all-ages bene count) is NULL in 57% of rows due to 1-10-beneficiary privacy suppression -- you cannot reliably sum beneficiary counts, only claims/fills/cost. Beneficiary-based denominators are heavily missing.
- total_drug_cost_usd is GROSS pharmacy cost (ingredient + dispensing fee + tax), NOT the Medicare-paid amount and NOT net of manufacturer rebates -- do not treat it as Medicare program net spend or as a negotiated/contracted price.
- Four columns (data_year, observed_period, effective_period_start, effective_period_end) all encode the same single calendar year; one source file per year. is_current is 100% true and asserted_at is ingestion date (2026-05), so none of the snapshot/provenance columns subdivide the grain. drug_source_version_id is entirely NULL and vintage_tag is the constant 'none'.
- prescriber_type is per-file/per-year derived (ranked Claim-Specialty > NPPES-Specialty > NPPES-Taxonomy via prescriber_type_source), so the SAME NPI can carry different specialties across years -- do not treat prescriber_type as a stable attribute of the NPI.

| column | type | meaning |
|---|---|---|
| prescriber_npi | string | 10-digit National Provider Identifier of the prescriber. Always present. 1,339,949 distinct. 17 rows are organization NPIs (prescriber_first_name NULL). Part of the grain key. |
| brand_name | string | Drug brand/trade name as labeled by CMS (e.g. 'Eliquis'). 3,516 distinct. For generically-dispensed drugs CMS sets brand_name = generic_name (70,791,854 rows = 69%). Part of the grain key. |
| generic_name | string | Active-ingredient / generic name (e.g. 'Apixaban' for Eliquis). 1,947 distinct. Part of the grain key. A single brand can map to multiple generic strings and vice-versa, which is why both are in the key. |
| data_year | int | Integer calendar year the prescribing activity occurred (2020, 2021, 2022, 2023). Part of the grain key. Maps 1:1 to one source file per year. |
| prescriber_last_org_name | string | Prescriber last name, or organization name for org NPIs. Descriptive only; not part of the key. |
| prescriber_first_name | string | Prescriber first name. NULL for the 17 organization-NPI rows. Descriptive only. |
| prescriber_city | string | City of the prescriber's primary practice location (from NPPES). Descriptive only. |
| prescriber_state | string | 2-letter state/territory of practice. Always present. 63 distinct values including territories (PR=1.34M rows, VI, GU) and special codes: 'ZZ' (foreign, 4,359), 'XX' (unknown, 2,479), and military 'AA'/'AE'/'AP'. Top states CA/FL/TX/NY/PA. |
| prescriber_state_fips | string | Numeric FIPS state code matching prescriber_state. 61 distinct (vs 63 states) because some territory/special codes lack or share a numeric FIPS. |
| prescriber_type | string | Prescriber specialty (e.g. 'Family Practice', 'Internal Medicine', 'Nurse Practitioner'). NULL in only 71 rows. Derived per-file, so the same NPI can show different specialties across years. |
| prescriber_type_source | string | Provenance of prescriber_type: 'Claim-Specialty' (90%, assigned from claims), 'NPPES-Specialty' (8%), or 'NPPES-Taxonomy' (2%) fallback when claim specialty is unavailable. |
| total_claims | int | Total Part D claim count (incl. refills) for this prescriber-drug-year. ALWAYS populated (0 nulls). CMS suppression floor: minimum value is 11 -- any combo with <=10 claims is omitted from the file entirely (NOT flagged), so absence != zero. |
| total_30day_fills | double | Claims normalized to 30-day equivalents (a 90-day script counts ~3). Double. Always >= total_claims (0 violations observed). Always populated. |
| total_day_supply | int | Sum of days-supply across all claims for this prescriber-drug-year. Always populated. |
| total_drug_cost_usd | double | Total GROSS drug cost in USD (ingredient cost + dispensing fee + sales tax = amount paid to pharmacy; NOT Medicare/patient-paid, NOT net of rebates). Always populated. Range $0 to $81.78M, median ~$668, mean ~$6,894. Core COST column. |
| total_beneficiaries | int | Distinct Part D beneficiaries who received the drug from this prescriber. NULL in 58,606,644 rows (57%) -- CMS SUPPRESSES when 1-10 beneficiaries. NULL means 'suppressed small count', not zero. Main suppressed column on the row. |
| ge65_total_claims | int | Subset of total_claims for beneficiaries aged >=65. NULL whenever ge65_suppression_flag is non-null (46M rows). Always <= total_claims (0 violations). |
| ge65_total_30day_fills | double | 30-day-equivalent fills for the >=65 subgroup. NULL when ge65_suppression_flag is set. |
| ge65_total_drug_cost_usd | double | Total gross drug cost (USD) for the >=65 subgroup. NULL when ge65_suppression_flag is set. |
| ge65_total_day_supply | int | Day supply for the >=65 subgroup. NULL when ge65_suppression_flag is set. |
| ge65_total_beneficiaries | int | Distinct >=65 beneficiaries. NULL whenever EITHER ge65_suppression_flag OR ge65_bene_suppression_flag is set; populated in only 11,147,609 rows. |
| ge65_suppression_flag | string | Controls the WHOLE >=65 metric block. NULL=metrics shown (57.1M); '*'=group <=10 benes, entire >=65 block redacted (13.0M); '#'=counts 11-110, >=65 metrics redacted (33.0M). When non-null, all five ge65_* fields are NULL. |
| ge65_bene_suppression_flag | string | Separately gates ONLY ge65_total_beneficiaries. NULL=shown (11.1M); '*'=1-10 benes suppressed (62.8M); '#'=11-110 suppressed (29.2M). Can suppress the >=65 bene count even when the rest of the >=65 block is visible. |
| observed_period | date | Date the activity was observed = January 1 of data_year. Redundant with data_year (one of four temporal columns encoding the same year). |
| asserted_at | date | Vlada/CMS ingestion (assertion) date, NOT data time: 2026-05-16 for DY2023, 2026-05-19 for DY2020-2022. When this batch was loaded. |
| effective_period_start | date | Start of the reporting period = Jan 1 of data_year (2020/2021/2022/2023-01-01). Profiler's 'time column'; same year as data_year. |
| effective_period_end | date | End of reporting period = Dec 31 of data_year. With start, defines the full calendar-year window. |
| cms_vintage_sequence | int | Vintage/version sequence of the CMS release. Constant = 1 (only one vintage loaded; no restated versions present). |
| parse_revision | int | Parser revision number. Constant = 1. |
| parser_artifact_hash | string | SHA-256 of the parser artifact/code that produced the rows. 3 distinct values (~one parser run per yearly batch). Provenance. |
| source_url | string | Public CMS download URL of the source CSV (e.g. data.cms.gov/.../MUP_DPR_RY24_P04_V10_DY22_NPIBN.csv). One distinct URL per data_year. |
| sha256_content_hash | string | SHA-256 of the raw source file content. One distinct value per data_year; pins the exact file the rows came from. |
| source_id | string | Logical source identifier, constant 'medicare_part_d_prescriber_by_drug' for every row. |
| is_current | boolean | Snapshot flag for the bitemporal model. 100% TRUE here -- NO superseded rows, so it never disambiguates the grain today, but filter is_current=true defensively for future restatements. |
| bronze_object_key | string | S3 key of the bronze (raw landed) object. 4 distinct values (one per year). Lineage to the bronze layer. |
| s3_version_id | string | S3 object version id of the bronze object. Lineage pin to the exact stored bytes. |
| vintage_tag | string | Human vintage label. Constant literal string 'none' for all rows (no special vintage tagging in use). |
| drug_source_version_id | string | Drug-source version identifier. 100% NULL in this table -- unused column. |

Tools that read it: excluded_prescriber_payment_screen

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