Part D Specialty Analytics
Public Medicare Part D analytics in Snowflake: Part D Specialty Economics (drug mart and specialty flag) and Part D Prescribers Explorer (national 2024 aggregates) — both on Tableau Public.
Drug Path
Problem & Outcome
CMS publishes national Medicare Part D drug-spend files that are free to use, but they arrive wide by year and need clear modeling before they support reliable analysis — including a documented specialty flag aligned to CMS specialty-tier economics.
Outcome: Part D Spending by Drug loaded into Snowflake (as-loaded raw layer, then a drug-by-year mart), a specialty flag based on average spend per beneficiary versus CMS’s $950 specialty-tier monthly cost threshold, quality checks on row counts, and a Tableau Public dashboard from a mart extract.
Dashboard
Part D Specialty Economics — drug mart preview (Tableau toolbar at the bottom of the frame).
Architecture
Small warehouse layers: as-loaded raw data, an analysis mart with explicit grain (brand × generic × year), then a mart extract for Tableau Public.
flowchart TB A["CMS Part D Spending by Drug"] --> B["Snowflake RAW"] B --> C["Snowflake MART"] C --> D["CSV extract"] D --> E["Tableau Public"]
Specialty Flag
Part D plans use a CMS specialty-tier cost threshold of $950 for a 30-day equivalent supply (CY 2024–2026 bidding guidance). The mart’s specialty flag marks drugs whose average Part D spend per beneficiary, divided by 12, meets or exceeds that monthly bar:
est_monthly = avg_spnd_per_bene / 12 specialty flag = (est_monthly ≥ 950)
That is a documented cost proxy, not an official formulary “specialty” label. Very large national spend can still sit below the flag when many beneficiaries share the total (for example common chronic therapies).
| Check (2024) | Result |
|---|---|
| RAW rows | 14,536 |
| Overall drug groups | 3,625 |
| Specialty flag TRUE | 685 |
| FALSE / null | 2,741 / 199 |
Prescriber Path
Problem & Outcome
Ranking prescribers by pharmacy claim volume hides where the drug cost sits. CMS publishes a public prescriber-by-drug file of about 28 million rows for 2024. The charts read summaries of the drugs with the specialty flag.
Outcome: the full national file is in Snowflake. The 685 drugs with specialty flag TRUE are joined in. Prescriber summaries feed Part D Prescribers Explorer on Tableau Public: a map, filters, and top people by drug cost and by pharmacy claims. On this set, the people with the highest drug cost and the people with the most pharmacy claims are largely different people.
Dashboard
Part D Prescribers Explorer — filters, map, and top lists on drugs with specialty flag TRUE. Loads when you open this tab.
Architecture
Scale path: load the full public fact once, serve charts from AGG marts only. Tableau Public never receives the 28M-row fact.
flowchart TB A["CMS Part D Prescribers 2024"] --> B["Snowflake RAW ~28M rows"] B --> C["AGG marts for specialty-flag drugs"] C --> D["Extract"] D --> E["Tableau Public"]
The specialty flag is defined on the drug mart (Specialty Economics view). This path uses those 685 drugs and AGG marts — not the drug-spend year mart alone.
Tech Stack
One project stack covers both views (specialty economics and the prescribers explorer).
| Layer | Choice | Why |
|---|---|---|
| Source | CMS data.cms.gov | Drug spend + prescriber PUFs (public) |
| Warehouse | Snowflake | Raw load, a drug mart, and prescriber summaries. The SQL can be run again. |
| Transform | SQL + Python loader | Drug mart + specialty flag; full prescriber load + AGG |
| BI | Tableau Public | The charts use extracts of the summaries. |
| This site | Static embed | Preview + full-page links to Public |
Takeaways
- CMS public files in Snowflake, with both dashboards on Tableau Public
- Specialty Economics: a documented specialty flag on a drug-by-year grain
- Prescribers Explorer: the national file stays in Snowflake, and the charts use the prescriber summaries
- On the specialty-flag drugs, the people with the highest drug cost and the people with the most pharmacy claims are largely different people