CMS Scorecard - Dimensional Modeling for Healthcare Quality Data

Turning 817,000 Rows of Messy Healthcare Data Into Star Ratings

dbt, star schemas, z-scores, and the art of making five incompatible datasets look like one

In the first post, I extracted 11 datasets from CMS’s public API and landed them in a PostgreSQL warehouse. Every column was TEXT. Five different values meant “null.” The data was raw, faithful, and completely unusable for analysis.

This post covers what happened next: a dbt transformation pipeline that cleans the data, models it into a star schema, z-scores 170 measures, rolls them up into five weighted domains, and produces a single summary score per hospital. Everything downstream of the warehouse, up to the point where Python takes over for clustering.

The Three-Layer Architecture

The warehouse has three schemas, each representing a level of refinement:

staging    →    transform    →    mart
(11 views)      (3 tables)       (3 tables)
raw TEXT        star schema      scoring

Staging views sit directly on top of the raw tables. They handle type casting, null normalization, column renaming, and deduplication. They don’t join, filter, or aggregate — they just clean.

Transform tables form the star schema: dim_hospital, dim_measure, and fact_hospital_measure. This is where the 5 scoring datasets get unified into a single grain.

Mart tables compute the actual scores: z-scores per measure, domain rollups, and the weighted summary. This is the analytical layer — it exists to answer the question “how good is this hospital?”

dbt materializes staging models as views (no storage cost, always fresh) and transform/mart models as tables (precomputed for query performance). The entire pipeline — 17 models — builds in 2.2 seconds.

Taming the Nulls

Before anything else, the staging layer needs to survive the data. CMS uses at least five representations for missing values, sometimes in the same column:

The temptation is to build a nullif chain: nullif(nullif(nullif(x, 'Not Available'), 'N/A'), ''). This works until it doesn’t — you’ll always be one sentinel value behind CMS’s imagination.

For numeric columns, I used a regex gate instead. This is a dbt macro — which means it’s written in Jinja2, the templating language that dbt embeds in SQL files. If you’ve never seen Jinja2 before, the {% %} and {{ }} delimiters are the tell: everything inside them is evaluated at compile time, before the SQL ever reaches your database:

{% macro safe_numeric(column) -%}
    case when {{ column }} ~ '^-?[0-9]*\.?[0-9]+$'
         then {{ column }}::numeric
    end
{%- endmacro %}

{{ column }} gets replaced with the actual column name at compile time. The SQL your database sees is just a plain CASE WHEN — no templating syntax, no runtime overhead. But in your codebase, you write {{ safe_numeric('score') }} once and never think about null-safe casting again.

If it matches the pattern of a number, cast it. Everything else — "Not Available", "high", "Please consult your physician", whatever CMS adds next quarter — silently becomes NULL. No enumeration, no maintenance, no surprises.

For integers and dates, the nullif chain is still fine because those columns don’t have the categorical-label problem. But they need one more entry than you’d expect:

{% macro _nullify(column) -%}
    nullif(nullif(nullif(nullif({{ column }},
        'Not Available'), 'Not Applicable'), 'N/A'), '')
{%- endmacro %}

That fourth value — "Not Applicable" — is semantically different from "Not Available". It means the measure doesn’t apply to this hospital, not that data is missing. We preserve this distinction with boolean flags in the staging views (answer_pct_not_applicable, linear_mean_not_applicable) before collapsing both to NULL for the type cast. The metadata survives; the casting doesn’t care.

The Star Schema

dim_hospital (5,426 rows)

One row per US hospital. Facility ID, name, address, type, ownership, and — critically — the CMS-published star rating. That last column is the answer key. We won’t look at it until validation, but it’s there.

The model is a single SELECT ... FROM stg_hospital_general. No joins, no logic. All the cleaning happened in the staging view. This feels anticlimactic until you realize that’s the whole point of the staging layer — the transform model just decides which columns belong in the dimension.

dim_measure (168 rows)

One row per quality measure — HAI_1_SIR, MORT_30_AMI, H_CLEAN_LINEAR_SCORE, etc. Three important columns:

• domain — which of the 5 scoring domains this measure belongs to (or null for non-scoring measures)
• is_scored — whether it feeds into star ratings at all
• higher_is_better — the directionality flag that controls z-score sign inversion later

The domain assignment happens at the source level — each CTE in the UNION ALL hardcodes its domain:

select distinct 'safety' as domain, measure_id, measure_name
from staging.hai
union all
select distinct 'mortality' as domain, measure_id, measure_name
from staging.complications_and_deaths

This avoids a brittle 168-row CASE statement mapping measure IDs to domains. The source table is the domain.

HCAHPS is the exception. It contains both scoring measures (anything ending in _LINEAR_SCORE → patient experience domain) and non-scoring rows (individual answer breakdowns, per-question star ratings). A CASE on the suffix handles this:

case
    when hcahps_measure_id like '%LINEAR_SCORE' then 'patient_experience'
    else null
end as domain

Directionality avoids a giant lookup table too. Domain-level defaults cover most measures (mortality/safety/readmission → lower is better; patient experience/timely care → higher is better), with just 5 measure-level overrides for things like OP_18b (ED wait time — lower is better despite being in the timely & effective domain). 168 measures, 12 lines of SQL.

fact_hospital_measure (266,829 rows)

This is where it gets interesting. Five datasets with different schemas need to UNION ALL into a single grain: one row per hospital × measure × reporting period.

The problem is that these tables don’t agree on what to call things:

Each source gets its own CTE that maps its columns to the common schema:

hcahps as (
    select
        facility_id,
        measure_id,
        hcahps_linear_mean_value as score,
        number_of_completed_surveys::numeric as denominator,
        has_number_of_completed_surveys_footnote as has_footnote,
        start_date,
        end_date
    from stg_hcahps
    where measure_id like '%LINEAR_SCORE'
),

Three design decisions here:

HCAHPS score: hcahps_linear_mean_value, not patient_survey_star_rating. The star rating is a CMS output — their pre-computed 1-5 rating per survey question. The linear mean is the input — the continuous score that actually gets z-scored in the methodology. Using the star rating would be circular: scoring CMS’s scores instead of computing our own from the underlying data.

HCAHPS filter: where measure_id like '%LINEAR_SCORE'. Each HCAHPS question generates ~5 rows per hospital: individual answer percentages (_A_P, _U_P, _SN_P), a linear mean (_LINEAR_SCORE), and a star rating (_STAR_RATING). Only the linear means are scoring inputs. Without this filter, you’d quintuple-count every patient experience measure.

where score is not null on the final output. After staging’s safe_numeric turns "Not Available" into NULL, these rows can’t contribute to z-scoring. Filtering them early prevents null scores from inflating row counts and creating confusing gaps in downstream aggregations.

What’s NOT in the fact table

Only 5 of the 11 datasets are scoring inputs. The remaining 6:

• Hospital General Info → already consumed by dim_hospital
• HAC Reduction → validation dataset (CMS’s published safety z-scores)
• HVBP General/Efficiency/Safety/Clinical → validation datasets (CMS’s published domain scores)

The HVBP tables are CMS’s own computed scores. Including them would be double-counting, not computing. They stay as separate staging views for Phase 5 validation: “here’s what we calculated; here’s what CMS calculated; how close are we?”

Even if they were scoring inputs, they couldn’t union into the fact table. They’re wide-format — one row per hospital with per-measure column groups (hai_1_sir, hai_1_winsorized_z_score, hai_1_achievement_points…). The scoring tables are tall-format with a measure_id column. You’d need intermediate unpivot models to bridge the shape mismatch. Since they’re validation-only, that work isn’t necessary.

The Scoring Pipeline

With the star schema in place, three mart models compute the actual scores.

zscore_by_measure (266,829 rows)

For each measure, standardize every hospital’s score against the national average:

z = (hospital_score − national_mean) / national_stddev

The elegant part is that PostgreSQL window functions compute the national stats inline — no GROUP BY, no self-join, no CTE to pre-aggregate:

select
    facility_id,
    measure_id,
    score,
    avg(score) over (partition by measure_id) as national_mean,
    stddev(score) over (partition by measure_id) as national_stddev
from fact_hospital_measure

Each row gets the national mean and standard deviation for its measure without collapsing the result set. The window function computes the aggregate across all hospitals sharing that measure_id, then attaches it to every row in the partition. You keep your 266,829 rows and gain two new columns. No intermediate table needed.

Then the z-score calculation, with the critical sign inversion:

case
    when national_stddev = 0 or national_stddev is null then null
    when higher_is_better then (score - national_mean) / national_stddev
    else -1.0 * (score - national_mean) / national_stddev
end as z_score

A hospital with a low mortality rate gets a negative raw z-score (below average). But low mortality is good. Multiplying by -1 when higher_is_better = false ensures that positive z-scores always mean better performance, regardless of the measure’s natural direction. This normalization is what makes cross-domain comparison possible — you can average a mortality z-score with an infection z-score because they’re both on the “higher = better” scale.

The zero-stddev guard handles an edge case: if every hospital reports the same score for a measure, the standard deviation is zero, and the z-score would be division by zero. These return null and get filtered out downstream.

domain_rollup (20,412 rows)

Each domain’s score is the simple average of z-scores across all measures in that domain for a given hospital:

select
    facility_id,
    domain,
    avg(z_score) as domain_z_score,
    count(*) as measure_count
from zscore_by_measure
where z_score is not null
group by facility_id, domain

This collapses 266,829 measure-level rows into 20,412 domain-level rows. There’s no within-domain weighting — CMS treats all measures in a domain equally. The measure_count column is useful for debugging and potentially for minimum-measure thresholds.

~5,400 hospitals × ~4.4 domains each. Not every hospital has all 5 domains, which is the whole reason the next step needs weight redistribution.

weighted_summary (4,599 rows)

CMS assigns base weights to each domain:

The formula: summary = 0.22×mort + 0.22×safety + 0.22×readm + 0.22×pt_exp + 0.12×te

When a hospital is missing a domain, those weights redistribute proportionally. A hospital missing patient experience (0.22) has its remaining weights scaled by 1/0.78 so they sum to 1.0. This keeps summary scores comparable across hospitals regardless of how many domains they report.

But the SQL doesn’t compute redistributed weights explicitly. There’s an algebraic shortcut.

The explicit approach: compute each domain’s redistributed weight individually. For a hospital missing patient experience:

• Sum of remaining weights: 0.22 + 0.22 + 0.22 + 0.12 = 0.78
• Redistributed: mort = 0.22/0.78 ≈ 0.282, safety ≈ 0.282, readm ≈ 0.282, te = 0.12/0.78 ≈ 0.154

The shortcut: multiply each domain by its base weight, sum them, and divide by the total weight of domains that exist:

(0.22×mort + 0.22×safety + 0.22×readm + 0.12×te) / 0.78

This is algebraically identical because division distributes over addition:

(0.22×mort) / 0.78 = (0.22/0.78) × mort = 0.282 × mort

One division replaces five conditional weight calculations. It works for all 32 possible combinations of missing domains without any branching logic:

-- total_weight: sum of base weights for domains that exist
case when mortality_z is not null then 0.22 else 0 end
+ case when safety_z is not null then 0.22 else 0 end
+ case when readmission_z is not null then 0.22 else 0 end
+ case when patient_experience_z is not null then 0.22 else 0 end
+ case when timely_effective_z is not null then 0.12 else 0 end
as total_weight

-- summary_score: weighted sum / total_weight
(
    coalesce(mortality_z * 0.22, 0)
    + coalesce(safety_z * 0.22, 0)
    + coalesce(readmission_z * 0.22, 0)
    + coalesce(patient_experience_z * 0.22, 0)
    + coalesce(timely_effective_z * 0.12, 0)
) / total_weight as summary_score

coalesce(domain_z * weight, 0) makes missing domains contribute zero to the numerator. The denominator only counts weights for domains that exist. The math handles itself.

This is also the kind of repetitive SQL pattern that Jinja2 was born to eliminate. Five domains, each with a case when for weight calculation and a coalesce for the weighted sum. Instead of writing ten near-identical expressions by hand:

{%- set domains = [
    ('mortality', 0.22),
    ('safety', 0.22),
    ('readmission', 0.22),
    ('patient_experience', 0.22),
    ('timely_effective', 0.12),
] -%}

-- total_weight
{% for name, weight in domains %}
    case when {{ name }}_z is not null then {{ weight }} else 0 end
    {%- if not loop.last %} + {% endif %}
{% endfor %} as total_weight,

-- weighted numerator
(
{% for name, weight in domains %}
    coalesce({{ name }}_z * {{ weight }}, 0)
    {%- if not loop.last %} + {% endif %}
{% endfor %}
) / total_weight as summary_score

The domain names and weights are defined once in a list. The {% for %} loop generates both the weight accumulator and the weighted sum. If CMS adds a sixth domain next year — or changes the weights, which they do periodically — you edit one list, not ten lines. This is the conditional logic power of Jinja2: it’s not just variable substitution, it’s a full control-flow language operating at compile time. The SQL that reaches PostgreSQL is the same flat CASE WHEN chain, but the source of truth is a three-line data structure instead of a wall of copy-paste.

One PostgreSQL-specific trick in this model: the pivot from tall (one row per domain) to wide (one row per hospital) uses the FILTER clause for conditional aggregation — and again, Jinja2 can generate the repetitive columns:

select
    facility_id,
    {% for name, _ in domains %}
    max(domain_z_score) filter (where domain = '{{ name }}') as {{ name }}_z
    {%- if not loop.last %},{% endif %}
    {% endfor %}
from domain_rollup
group by facility_id

Five columns from two lines of Jinja2. max() works because there’s exactly one row per facility × domain — it’s just extracting the single value. FILTER (WHERE ...) is standard SQL:2003 but rarely supported outside PostgreSQL and SQLite. The portable fallback is max(case when domain = 'mortality' then domain_z_score end), which does the same thing less readably.

The Funnel

The pipeline progressively condenses the data:

staging (11 views)          — raw data, all TEXT
  → dim_hospital              5,426 hospitals
  → dim_measure                 168 measures
  → fact_hospital_measure   266,829 scores (5 datasets unified)
    → zscore_by_measure     266,829 standardized scores
      → domain_rollup       20,412 domain averages
        → weighted_summary    4,599 summary scores

From 266,829 individual measure scores to 4,599 hospital summary scores. The ~827 hospitals that dropped out between dim_hospital (5,426) and weighted_summary (4,599) had zero reportable measures across all domains — they exist in the CMS directory but don’t report enough data to score.

What dim_measure Taught Me About Data Modeling

There’s a modeling decision in dim_measure that I initially thought was a mistake: it reads from raw {{ source() }} staging tables instead of {{ ref('stg_*') }} views. (Both of these are Jinja2 functions — ref() creates dependency-tracked references between dbt models, and source() references raw tables. dbt’s entire DAG is built from these calls at compile time.) This breaks the dbt convention that transform models should only reference staging views.

The reason: the staging views deliberately dropped measure_name. It’s descriptive text that belongs in the dimension, not repeated on every row of the fact table. But dbt’s source() tables still have it. HCAHPS also needs hcahps_question (the survey question text) for measure_name, which stg_hcahps also dropped.

This is the one case where a transform model should bypass the staging layer. The staging views were designed to be lean fact-table feeders. The dimension needs columns the views intentionally excluded. Going to the source is the correct decision, not a workaround.

What’s Next

The dbt pipeline produces a summary_score per hospital — a single number representing weighted quality across up to five domains. This is as far as SQL can take us.

Phase 4 is where Python enters: scikit-learn’s k-means clustering on the summary scores to assign 1-5 star buckets, plus the safety cap rule that hard-limits bottom-quartile safety hospitals to 4 stars regardless of their overall score. That can’t be expressed declaratively — it’s procedural logic with an iterative algorithm. Different tool for a different job.

Then the moment of truth: comparing our stars to CMS’s published ratings and seeing how close we got.

This is part 2 of a series on rebuilding CMS’s 5-Star Hospital Quality Rating from scratch. Part 1 covers the extraction pipeline.