CMS Quality Scorecard - End-to-End Pipeline Project

Project Objective

Rebuild the CMS 5-Star Hospital Quality Rating from first principles using public data. Extract 11 datasets from the CMS Provider Data Catalog REST API, land raw JSON in a MinIO data lake, transform through a PostgreSQL warehouse with dbt, score with k-means clustering, and validate against CMS-published results. Doubles as interview-ready portfolio piece and blog post series.

Stack

CMS Datasets (11 total)

API: https://data.cms.gov/data-api/v1/dataset/{uuid}/data?size=5000&offset=0 — no auth, all strings, paginate until empty array.

Project Structure

cms-scorecard/
  .venv/                     # Python 3.13 (gitignored)
  .env                       # DB/MinIO creds (gitignored)
  .env.example
  pyproject.toml             # Project metadata + dependencies

  extract/
    cms_api.py               # Paginated API client (httpx)
    datasets.py              # Dataset registry (UUIDs, names)
    extract_all.py           # Prefect flow: all datasets -> MinIO bronze

  load/
    minio_client.py          # MinIO read/write helpers
    load_staging.py          # Prefect flow: MinIO bronze -> PG staging

  dbt_cms/                   # dbt project
    dbt_project.yml
    profiles.yml
    models/
      staging/               # 1:1 views on raw staging tables
      transform/             # Star schema (dim/fact)
      mart/                  # Scoring aggregations
    tests/
    macros/

  score/
    cluster.py               # K-means (5 clusters on summary scores)
    safety_cap.py            # Bottom-quartile safety -> cap at 4 stars
    assign_stars.py          # Prefect flow: run scoring, write mart.star_rating

  validate/
    compare_stars.py         # Our stars vs CMS published stars
    compare_hac.py           # Our safety z-scores vs HAC dataset
    compare_hvbp.py          # Our domain scores vs HVBP dataset
    report.py                # Generate validation markdown + plots

  app/
    streamlit_app.py         # Interactive dashboard
    pages/
      national_overview.py
      hospital_detail.py
      validation.py

  lib/
    config.py                # Env var loading
    db.py                    # psycopg connection helper

  notebooks/                 # EDA at each pipeline stage

  flows/
    pipeline.py              # Master Prefect flow chaining all stages

Domain Weights (CMS 2026 Methodology)

Scoring Algorithm

1. Z-score per measure: z = (hospital_score - national_mean) / national_stddev — flip sign for “lower is better” measures
2. Domain rollup: Average z-scores within each domain per hospital
3. Weighted summary: 0.22*mort + 0.22*safety + 0.22*readm + 0.22*pt_exp + 0.12*te — redistribute weights if domain missing
4. K-means clustering: sklearn.cluster.KMeans(n_clusters=5) on summary scores, sort centroids to assign 1-5 stars
5. Safety cap: Hospitals in bottom quartile for safety capped at 4 stars max

Dimensional Model

dim_hospital

• facility_id (PK) — 6-character CMS facility identifier
• facility_name, address, city, state, zip_code, county
• hospital_type, hospital_ownership
• emergency_services (boolean)
• overall_rating — CMS-published star rating (answer key)

dim_measure

• measure_id (PK) — e.g., HAI_1_SIR, MORT_30_AMI
• measure_name
• domain — Mortality, Safety, Readmission, Patient Experience, Timely & Effective Care
• higher_is_better (boolean) — crucial for z-score sign inversion

fact_hospital_measure

• facility_id (FK), measure_id (FK)
• reporting_period_start, reporting_period_end
• score (float), denominator (int)
• UNION ALL across HAI, mortality, readmissions, HCAHPS, T&E — normalized to common grain

Implementation Phases

Phase 0: Scaffolding & Infrastructure ✅

• Project directory, venv (Python 3.13), pyproject.toml
• MinIO container on cyrion (cms-minio, API port 9754, console 9755)
• cms_scorecard database on phlegethon with cms_pipeline user + claude_reader access
• Schemas: staging, transform, mart
• dbt project initialized, dbt debug passes
• Prefect server on cyrion, UI at prefect.c0smere.net (Blocky DNS + Caddy proxy)
• .env.example with all required vars

Phase 1: Extract

• Paginated API client in extract/cms_api.py using httpx
• Dataset registry in extract/datasets.py (all 11 UUIDs)
• Prefect flow in extract/extract_all.py: iterates datasets, paginates, writes raw JSON to MinIO bronze/{dataset}/{timestamp}/page_NNN.json + _metadata.json
• Done when: All 11 datasets land in MinIO bronze (~650K+ total rows across ~150 pages)

Phase 2: Load to Staging

• load/load_staging.py Prefect flow: reads latest bronze from MinIO, parses JSON, converts types ("Not Available" → NULL), bulk loads into staging.* tables
• DDL for all 11 staging tables
• Done when: SELECT count(*) FROM staging.hospital_general returns ~5,426; all 11 tables populated

Phase 3: dbt Transformations

• Staging models: clean views (consistent naming, type casts, null handling)
• dim_hospital, dim_measure, fact_hospital_measure (UNION ALL + normalize)
• Mart models: zscore_by_measure, domain_rollup, weighted_summary
• dbt tests on all models (unique, not_null, referential integrity, accepted_values)
• Done when: dbt build passes; fact_hospital_measure has ~5K+ distinct facilities

Phase 4: Scoring

• K-means clustering on weighted_summary → 1-5 star assignment
• Safety cap: bottom-quartile safety → max 4 stars
• mart.star_rating table with domain z-scores, summary score, final star, CMS published star, match flag
• Done when: Star match rate measurable (target: 60-70%+ exact match)

Phase 5: Validation & Analysis

• Confusion matrix, exact/within-1 match rates vs CMS published
• Safety z-score correlation vs HAC Reduction Program
• Domain rollup correlation vs HVBP published scores
• Validation report with honest gap discussion
• Done when: validation_report.md generated with per-domain correlation coefficients

Phase 6: Streamlit Dashboard

• National overview: star distribution, bar charts by hospital type/ownership
• Hospital detail: 5 domain scores, measure-level z-scores, national ranking
• Validation page: our stars vs CMS, scatter plots, confusion matrix
• Done when: All 3 pages functional with real data

Phase 7: Prefect Pipeline Orchestration

• Master flow: extract → load → dbt build → score → validate
• Deploy to Prefect server on cyrion
• Error handling, retries, optional scheduling
• Done when: End-to-end flow runs on cyrion; Prefect UI shows DAG and execution history

Blog Post Map

1. “Extracting CMS Hospital Data at Scale” — API pagination, MinIO bronze layer, ELT philosophy
2. “Dimensional Modeling for Healthcare Quality Data” — Star schema, dbt models, staging→transform→mart
3. “The Math Behind CMS 5-Star Ratings” — Z-scores, domain rollups, k-means, safety cap
4. “Validating a Data Pipeline Against Ground Truth” — Confusion matrices, correlation, honest gap discussion
5. “Orchestrating It All with Prefect” — Flow composition, retries, observability

Infrastructure

• MinIO bucket: cms-bronze
• Prefect UI: https://prefect.c0smere.net
• Credentials in Infra_RELOADED/CMS Scorecard — Credentials & Ops.md