Data Engineering · Analytics Engineering · Cloud · BI
A production-style portfolio project using public CMS data, designed to turn annual prescription datasets into analytics-ready data products for geographic, specialty, prescriber, and drug analysis.
The problem
The project transforms public CMS Medicare Part D provider-and-drug data into analytical products that support analysis of drug spend, provider behavior, specialty patterns, geographic trends, and year-over-year changes—without implying an adopting healthcare organization or realized business outcome.
My role
I designed and implemented the architecture, Python/pandas ingestion, AWS storage and Athena integration, Snowflake load path, dbt transformation layers, analytical models and marts, and Tableau delivery.
I built this project to demonstrate how I would design an end-to-end modern analytics platform around a large public healthcare dataset, combining data engineering, analytics engineering, cloud architecture, and BI delivery.
Architecture
The main path keeps source ingestion, cloud storage, warehouse loading, transformation, and BI distinct. Athena remains a limited side integration from S3.
Data pipeline
The implementation makes year scope explicit and keeps the source-aligned RAW load separate from analytical transformation.
01 · Data ingestion
--year defines one reporting year per invocation.REPORT_YEAR, and can prepare gzip output.raw/year=YYYY/ for explicit year organization.02 · Snowflake
COPY INTO maps 23 staged fields into RAW.PARTD_PRESCRIBERS with explicit casts.DELETE for the year, then COPY INTO.dbt & data modeling
The design uses views for staging and analytics, then tables for consumer-oriented marts.
Standardizes the RAW provider, drug, measure, geography, specialty, and reporting-year fields.
stg_partd_prescribersEstablishes the analytical fact projection and reusable geography and specialty groupings.
fct_partd_prescriptionsdim_geographydim_specialtyPublishes question-oriented aggregates for BI and analytical exploration.
mart_drug_spend_by_statemart_specialty_summarymart_top_prescribersmart_brand_generic_summaryAnalytical marts
mart_drug_spend_by_stateSpend, claims, beneficiaries, provider counts, and unit-cost measures by geography and reporting year.
mart_specialty_summaryCost and utilization patterns across prescriber specialties, broader specialty groups, and reporting year.
mart_top_prescribersPrescriber-level spend and utilization ranking enriched with geography and specialty attributes.
mart_brand_generic_summaryBrand and generic cost, utilization, prescriber, and per-claim or per-beneficiary comparisons.
Data quality
The current dbt YAML declares 14 baseline not_null tests across staging, the fact model, and four marts.
Tableau / analytics delivery
Each dashboard is available on Tableau Public. Static portfolio screenshots remain a presentation enhancement rather than a dependency for verifying the work.
Geographic analysis
Explore drug spend and utilization across states and reporting years.
View Drug Spend Dashboard (opens in a new tab)Specialty analysis
Compare cost and utilization patterns across prescriber specialty groups.
View Specialty Dashboard (opens in a new tab)Prescriber analysis
Review prescriber-level spend, claims, and beneficiary measures.
View Top Prescribers Dashboard (opens in a new tab)Engineering decisions
The implementation balances a clear portfolio-scale workflow with explicit paths for production hardening.
| Decision | Why | Trade-off |
|---|---|---|
| Process source data in 200,000-row pandas chunks | Bounds dataframe processing instead of loading the complete annual CSV at once. | The preparation step remains local and single-process; no runtime benchmark is claimed. |
| Parameterize one reporting year per invocation | Makes reload and backfill scope explicit across filenames, S3 paths, and warehouse operations. | Supported years and multi-year orchestration are not configured in the CLI. |
| Organize S3 data under raw/year=YYYY | Creates a predictable, year-scoped landing convention for discovery and Snowflake loading. | Upload and Athena repair are controlled by hard-coded flags that are off by default. |
| Separate RAW loading from dbt transformations | Preserves a source-aligned warehouse landing layer before analytical modeling. | COPY uses ON_ERROR = CONTINUE without persisted rejected-row reconciliation. |
| Replace one year with DELETE followed by COPY | Supports deliberate reprocessing of a requested reporting year. | The sequence is not an atomic replacement workflow. |
| Use views for staging/analytics and tables for marts | Keeps reusable transformation logic current while giving BI consumers stable aggregates. | Views repeat downstream query work, while marts require table rebuilds. |
| Build four question-oriented marts | Aligns modeled data with geography, specialty, prescriber, and brand/generic analysis. | Metric documentation and test coverage remain intentionally baseline. |
Production evolution
These improvements extend the demonstrated architecture without overstating its current operating model.
Stage, reconcile, and promote a requested year as one recoverable workflow rather than separate delete and copy statements.
Persist source counts, accepted rows, rejected rows, and load outcomes before considering a run complete.
Replace direct AWS credentials in stage DDL with a managed Snowflake storage integration.
Add grain, relationship, accepted-value, freshness, and domain checks beyond baseline not-null declarations.
Externalize execution flags and add status polling, structured run metadata, alerts, and exception-safe cleanup.
Align dbt profile naming and regenerate deliberately selected artifacts after running the complete current project.
Outcomes
Explore the project
Source code and each published dashboard are available as direct evidence of the work.