Part D Specialty Analytics

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.

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"]

Prescriber Path

Problem & Outcome

Ranking prescribers only by pharmacy claim volume hides cost concentration. CMS publishes a large public prescriber-by-drug file (~28M rows for 2024). Interactive charts should hit pre-aggregated marts — not scan tens of millions of rows in Tableau Public.

Outcome: full national fact in Snowflake, the 685 drugs with specialty flag TRUE joined in, NPI-level AGG marts, and Part D Prescribers Explorer on Tableau Public (map, filters, top people by drug cost and pharmacy claims). Headline: cost leaders and claim leaders are largely different people.

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.