A production-shaped commercial analytics stack on public Medicare data
- ROLE
- Solo build
- TIMEFRAME
- 2026
- STACK
- Python, DuckDB, dbt, Dagster, Plotly
- LINKS
- github ↗
2.7M
CMS rows ingested
147
dbt tests
57.9%
Volume from top decile
The problem
Pharma commercial-analytics teams need a dimensional warehouse that can answer "who are our best prescribers, and how did territory realignment affect the numbers", without silently overwriting history every time a territory changes hands. I built this to demonstrate that shape end to end, on real public data, for a ZS Associates application.
Approach
The pipeline pulls Medicare Part D prescriber data straight from the CMS API, 2.7 million prescription rows across 2020 to 2024, lands it in DuckDB, and builds a proper star schema through dbt: staging and intermediate layers, an incremental fact table, and a snap_territory_assignment SCD2 snapshot so a prescriber's territory history stays point-in-time-correct instead of being overwritten on realignment. Seven analytics marts sit on top, all pure SQL window functions: prescriber deciling, territory league tables, year-over-year growth, market share, HHI concentration, generic penetration, and writer churn. Dagster orchestrates the pipeline as software-defined assets with a weekly schedule and a freshness check, on top of 147 dbt tests as static schema contracts. The output is seven CSVs for Tableau plus a self-contained Plotly HTML dashboard with computed, real-number takeaway sentences.
Results
147 dbt tests cover the star schema and its seven marts: uniqueness, not-null, relationship, and accepted-value checks, plus a Dagster asset check that reconciles raw-ingested rows against the built fact table row-for-row, per year, with zero variance across all five years (2020 to 2024). The decile mart shows real concentration in the data: the top prescriber decile alone accounts for 57.9% of total prescription volume.