CMS Scorecard — Phase 0 Dev Log

CMS Scorecard — Phase 0 Dev Log (2026-03-15)

Issues encountered and resolved while standing up the project infrastructure.

1. Python 3.14 Breaks dbt (mashumaro serialization)

Symptom: dbt debug crashes immediately with:

mashumaro.exceptions.UnserializableField: Field "schema" of type Optional[str] in JSONObjectSchema is not serializable

Cause: dbt depends on mashumaro for dataclass serialization. Python 3.14 changed something in the type system that breaks mashumaro’s code generation for Optional[str] fields. The crash happens at import time — dbt can’t even start.

Fix: Created the venv with Python 3.13 instead using uv venv --python 3.13. uv auto-downloaded CPython 3.13.12. Pinned requires-python = ">=3.12,<3.14" in pyproject.toml to prevent future accidents.

Lesson: Arch ships bleeding-edge Python. When using tools with deep dependency trees (dbt → dbt-common → mashumaro), pin to the latest stable minor version, not the latest available.

2. pip Editable Install Fails Without Build Backend

Symptom: pip install -e ".[dev]" fails with:

ERROR: Failed to build 'file:///...' when getting requirements to build editable

Cause: pyproject.toml had [project] metadata but no [build-system] table. PEP 517 requires an explicit build backend for editable installs.

Fix: Added:

[build-system]
requires = ["setuptools>=68.0"]
build-backend = "setuptools.build_meta"

3. setuptools Can’t Auto-Discover Packages

Symptom: After adding the build backend, pip still fails:

To find more information, look for "package discovery" on setuptools docs.

Cause: The project uses a flat layout with multiple top-level packages (lib/, extract/, load/, score/, etc.) instead of the conventional single-package or src/ layout. setuptools’ auto-discovery doesn’t handle this.

Fix: Added explicit package discovery config:

[tool.setuptools.packages.find]
include = ["lib*", "extract*", "load*", "score*", "validate*", "flows*", "app*"]

4. Prefect UI Can’t Reach Server API

Symptom: Prefect UI loads at prefect.c0smere.net but shows:

Can't connect to Server API at http://0.0.0.0:4200/api. Check that it's accessible from your machine.

Cause: Prefect server binds to 0.0.0.0:4200 inside the container and the UI’s client-side JavaScript uses that same address to make API calls. When accessed through Caddy reverse proxy from another machine, the browser tries to reach http://0.0.0.0:4200/api on your machine — which obviously doesn’t exist.

Fix: Added PREFECT_UI_API_URL: "https://prefect.c0smere.net/api" to the container’s environment in docker-compose.yml. This tells the UI frontend to use the Caddy proxy URL for API requests instead of the container’s bind address.

Lesson: Any web app with a split frontend/backend that sits behind a reverse proxy needs its public URL configured explicitly. The server-side bind address (0.0.0.0) is never the right value for client-side API calls.

Infrastructure Checklist (for future projects)

These are the touchpoints hit during Phase 0 — useful template for the next homelab data project:

• Python version compatibility check (especially on Arch)
• pyproject.toml with explicit build backend + package discovery
• PostgreSQL database + schemas + user + claude_reader grants
• Docker container in master compose on cyrion
• Blocky DNS entry for *.c0smere.net
• Caddy reverse proxy block
• Env vars in /home/nox/docker/.env for Docker, local .env for dev
• Verify reverse-proxied web UIs work from a different machine (not just localhost)

Phase 1 Dev Log (2026-03-15)

5. CMS API Endpoint Migration

Symptom: All API calls to https://data.cms.gov/data-api/v1/dataset/{uuid}/data return 404. The Socrata-style endpoint (/resource/{uuid}.json) returns 410 Gone.

Cause: CMS migrated their Provider Data Catalog API. The old endpoint is dead.

New endpoint: https://data.cms.gov/provider-data/api/1/datastore/query/{uuid}/0

Key differences:

• Pagination param changed from size to limit
• Response is {"results": [...], "count": N} instead of a bare JSON array — extract body["results"]
• The /0 suffix refers to the first distribution of the dataset
• All 11 UUIDs are still valid — only the base URL changed

Lesson: Public government APIs change without notice. Build in enough flexibility to swap endpoints. The data.json DCAT catalog at https://data.cms.gov/data.json is the authoritative source for discovering current endpoints.

6. CMS API Page Size Limit

Symptom: Requests with limit=5000 return 400 Bad Request. limit=1000 works fine.

Cause: The new API enforces a maximum page size between 1000-2000 (1000 is confirmed safe, 2000 is rejected).

Fix: Changed PAGE_SIZE from 5000 to 1000. This means more API calls (817 pages instead of ~160) but each call is fast (~1-2s).

7. Content Hash OOM on Large Datasets

Symptom: Extraction process gets killed (exit code 144) while processing HCAHPS (325K rows). Memory grows to ~780MB then the process silently dies.

Cause: The original content_hash() function called json.dumps(rows, sort_keys=True) on the entire dataset at once — serializing 325K rows into a single multi-hundred-MB string, then .encode() creates another copy. Two copies of a ~500MB+ string in memory plus the original data structure exceeds available memory.

Fix: Changed to incremental hashing — hash each row individually and feed it into a running SHA-256:

def content_hash(rows: list[dict]) -> str:
    h = hashlib.sha256()
    for row in rows:
        h.update(json.dumps(row, sort_keys=True).encode())
    return h.hexdigest()

Peak memory dropped from ~780MB to ~280MB for the same dataset. Note: this produces different hashes than the bulk approach, so the first run after the fix re-extracts everything (subsequent runs skip correctly).

Lesson: Never serialize an entire large dataset into a single string for hashing. Stream it.

Phase 3 Dev Log (2026-03-15)

8. CMS Data Has Inconsistent Null Representations

Symptom: safe_numeric macro crashes with invalid input syntax for type numeric: "N/A" when querying HAI staging views.

Cause: CMS uses at least four different representations for missing data across its datasets:

• "Not Available" — most common (all datasets)
• "N/A" — appears in HAI scores
• "" — empty string (footnote fields, some scores)
• Text labels in numeric fields — "high", "low", "medium", "very high" in Timely & Effective Care

A simple nullif(x, 'Not Available')::numeric misses the other variants.

Fix: safe_numeric now uses a regex gate instead of nullif chains:

case when column ~ '^-?[0-9]*\.?[0-9]+$' then column::numeric end

Only values that actually look like numbers get cast; everything else silently becomes NULL. This handles all current and future non-numeric junk CMS might put in score fields.

safe_int and safe_date still use nullif chains ('Not Available', 'N/A', '') since their fields don’t have the text-label problem.

Lesson: When working with government open data, don’t assume consistent null representations — even within the same API. Regex validation before casting is safer than trying to enumerate every possible sentinel value.

9. “Not Applicable” — The Fifth Null Variant

Symptom: After fixing safe_numeric, querying stg_hcahps crashes with invalid input syntax for type integer: "Not Applicable".

Cause: The HCAHPS patient_survey_star_rating column contains "Not Applicable" in 282,551 out of 325,652 rows. This is semantically different from "Not Available" — it means the measure doesn’t apply to that hospital (e.g., maternity care questions at a non-birthing facility), not that data is missing.

The _nullify macro only stripped 'Not Available', 'N/A', and ''. safe_int feeds through _nullify then casts, so "Not Applicable" passed through untouched and hit the ::int cast.

Fix: Added 'Not Applicable' to the _nullify chain:

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

Design decision: For HCAHPS, we also added boolean flags (answer_pct_not_applicable, linear_mean_not_applicable) to distinguish “not applicable” from genuinely missing data. Initially assumed these would always co-occur — they don’t. 244K rows have hcahps_linear_mean_value = 'Not Applicable' while hcahps_answer_percent is numeric, and 38K have the reverse. Each column needs its own flag.

Running tally of CMS null representations:

1. "Not Available" — data not reported
2. "N/A" — shorthand for not available
3. "" — empty string
4. "Not Applicable" — measure doesn’t apply to this hospital
5. "high", "low", "medium", "very high" — categorical labels in numeric fields (Timely & Effective Care)

Lesson: What looks like missing data might carry semantic meaning. "Not Available" vs "Not Applicable" is a real distinction that affects scoring — you can’t just NULL them both and move on. Capture the state before you destroy it.

10. Facility ID: Don’t Cast to Integer

Symptom: Not a crash — a design question. Should facility_id be INT?

Cause: Facility IDs are zero-padded 6-character codes like 010001. The first two digits encode the state (01 = Alabama, etc.). Casting to integer loses the leading zeros and destroys the embedded structure.

Fix: Keep as TEXT, but trim() it in every staging view. CMS data sometimes has trailing whitespace.

Lesson: Just because a column looks numeric doesn’t mean it should be stored as a number. If the leading zeros or character structure carry meaning, it’s a code, not a quantity.

11. Timely & Effective Care Has Text Scores

Symptom: Not a crash (thanks to safe_numeric’s regex gate), but ~5 distinct non-numeric values appear in the score column: "high", "low", "medium", "very high", plus "Not Available".

Cause: Some Timely & Effective Care measures use categorical labels instead of numeric scores. These are ordinal (“low” < “medium” < “high” < “very high”) but the dataset mixes them in the same column as numeric values.

Fix at staging layer: safe_numeric NULLs out the text values. Added a score_is_text boolean flag so the transform layer can identify affected rows:

score !~ '^-?[0-9]*\.?[0-9]+$' and score is not null
    and score != 'Not Available' as score_is_text

Resolution (transform layer): Investigated the data — only one measure (EDV, Emergency Department Volume) uses text scores. It’s a hospital size classification, not a performance metric. Mapped to ordinal integers at staging (low=1, medium=2, high=3, very high=4) with a score_is_ordinal boolean flag. This keeps the column queryable without a separate lookup table. Since EDV isn’t a scoring measure, it gets higher_is_better = null in dim_measure.

12. PostgreSQL Identifier Truncation (63-char limit)

Symptom: The HVBP General table has a column named unweighted_normalized_efficiency_and_cost_reduction_domain_scor — clearly truncated.

Cause: PostgreSQL silently truncates identifiers to 63 characters (compile-time NAMEDATALEN - 1). The full CMS column name unweighted_normalized_efficiency_and_cost_reduction_domain_score is 64 characters. The load script creates columns from API field names, so the truncation happened silently at table creation.

Fix: Not a problem for the pipeline — the staging view renames it to unweighted_efficiency_score anyway. But worth knowing: if you’re auto-generating DDL from API field names, check for truncation. PostgreSQL won’t warn you.

13. HVBP Wide Tables — A Transform Layer Problem

The three HVBP detail tables (hvbp_safety, hvbp_clinical, hvbp_efficiency) are wide-format: one row per hospital with repeating {measure}_{metric} column groups. For example, hvbp_safety has 58 columns — 7 metrics (achievement_threshold, benchmark, baseline_rate, performance_rate, achievement_points, improvement_points, measure_score) for each of 7 HAI measures, plus a combined SSI score.

These stay wide at the staging layer. Unpivoting them into the long-format fact_hospital_measure grain is a transform-layer concern. These are validation datasets (comparing our computed scores against CMS’s published HVBP scores), so the wide format might actually be more convenient for side-by-side comparison.

Staging Layer Design Patterns

Patterns that emerged across all 11 staging views:

• Drop hospital demographics from every table except stg_hospital_general. Each raw table repeats facility_name, address, city, state, zip, county, phone — just keep facility_id as the FK. Demographics live in dim_hospital.
• Drop measure_name — keep measure_id only. Measure metadata (name, domain, directionality) belongs in dim_measure.
• trim() on all ID and categorical columns — CMS data occasionally has trailing whitespace.
• Footnote pattern: nullif(footnote, '') is not null as has_footnote + nullif(footnote, '') as footnote — boolean for quick filtering, cleaned text for details.
• Percent columns: Divide by 100.0 at staging to store as decimals (0.75 not 75). Applied to hcahps_answer_percent and survey_response_rate_percent.
• Boolean conversion: case when 'Yes' then true when 'No' then false end for fields like emergency_services, payment_reduction.
• Dates: safe_date macro handles 'Not Available', 'Not Applicable', 'N/A', '' → NULL → ::date.

Transform Layer Dev Log (2026-03-16)

14. dim_hospital Is Trivially Simple (And That’s the Point)

The dim_hospital transform model is a single SELECT ... FROM stg_hospital_general with no joins, no logic, just column selection. This feels anticlimactic until you realize: that’s the whole point of the staging layer. All the type casting, null handling, boolean conversion, and column renaming already happened in the view. The transform model just decides which columns belong in this dimension.

dbt materializes it as a table (5,426 rows), so downstream queries hit a real table instead of re-evaluating the view chain every time.

15. dim_measure: Domain Assignment at the Source

Building dim_measure required collecting distinct (measure_id, measure_name) pairs from 5 different source tables. The key design decision: assign the domain column inside each CTE before the UNION ALL, because once the measures are unioned together you lose the source table context.

This avoids a brittle 168-row CASE statement mapping measure IDs to domains. Instead:

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

HCAHPS is the exception — it has both scoring measures (*_LINEAR_SCORE → patient_experience domain) and non-scoring rows (individual answer breakdowns, star ratings) in the same table. A CASE expression on the measure_id suffix handles this: LINEAR_SCORE suffix gets a domain, everything else gets null.

The is_scored boolean is then just domain is not null.

16. dim_measure: Directionality Without a Giant Lookup

The higher_is_better flag could have been a 168-row mapping. Instead, it’s domain-level defaults with 5 exceptions:

• Domain defaults: mortality, safety, readmission → false (lower is better). Patient experience → true. Timely & effective → true.
• Exceptions (lower is better): OP_18% (ED wait times), OP_22 (left before being seen), HH_% (hospital harm), SAFE_USE_OF_OPIOIDS
• Special case: EDV (ED volume) → null (not directional, it’s a classification)

The order of CASE conditions matters: domain-level rules fire first for the easy cases, then measure-specific overrides catch the T&E exceptions, then the final when domain = 'timely_effective' then true is the catch-all default for the rest.

168 measures, 12 lines of SQL.

17. HCAHPS Measure Structure: Questions vs. Answers vs. Scores

HCAHPS is structured differently from every other dataset. Each “measure” has multiple rows per hospital:

The LINEAR_SCORE rows are what you actually want for scoring — CMS has already aggregated the answer percentages into a single continuous score per question per hospital. The individual answer breakdowns (_A_P, _U_P, _SN_P) are useful for exploratory analysis but don’t feed into the star rating.

H_STAR_RATING (no prefix) is the overall patient experience summary star — basically CMS’s answer key for this domain.

Initial instinct was to pick “top box” scores and manually aggregate. The pre-computed linear means are better — they’re what CMS actually uses, and they save a non-trivial aggregation step.

18. “Not Applicable” Co-occurrence: Trust Nothing, Verify Everything

When HCAHPS had both hcahps_answer_percent and hcahps_linear_mean_value containing “Not Applicable”, the natural assumption was they’d always co-occur — if a measure doesn’t apply, all its columns should be N/A. Queried it:

• 244,239 rows: linear mean is “Not Applicable” but answer percent is numeric
• 38,312 rows: answer percent is “Not Applicable” but linear mean is numeric

They’re almost anti-correlated. Each column needed its own *_not_applicable boolean flag. The lesson generalizes: in government data, “Not Applicable” in one column tells you nothing about adjacent columns in the same row.

19. DBeaver Statement Splitting

Not a data issue, but a tooling gotcha worth noting: DBeaver splits SQL on what it thinks are statement boundaries. A CTE with a closing ) followed by select * from cte_name can get split into two separate executions — the second one fails with “relation does not exist” because the CTE isn’t in scope.

Fix: Select the entire query text before executing, or use the “Execute SQL Script” button instead of “Execute SQL Statement.”

20. fact_hospital_measure: Only 5 of 11 Datasets Are Scoring Inputs

The initial instinct was to UNION ALL every staging table into the fact table. Wrong. Of the 11 CMS datasets, only 5 are scoring inputs — the rest are validation or dimensional:

The HVBP/HAC tables publish CMS’s own computed scores — they’re answer keys, not inputs. Including them would be double-counting. They stay as separate staging views for compare_hac.py and compare_hvbp.py in Phase 5.

21. fact_hospital_measure: Column Harmonization Across Sources

The 5 source tables don’t share a schema. The UNION ALL required mapping each table’s columns to a common grain:

HCAHPS score choice: Used hcahps_linear_mean_value, not patient_survey_star_rating. The star rating is a CMS output (their computed 1-5 star per question). The linear mean is the input — the continuous score that gets z-scored in the methodology. Using the star rating would be circular: we’d be scoring CMS’s scores instead of computing our own.

HCAHPS filtering: Only %LINEAR_SCORE measure IDs are included. This aligns with dim_measure, which assigns domain = 'patient_experience' only to those measures. The individual answer breakdowns (_A_P, _U_P, _SN_P) and per-question star ratings (_STAR_RATING) are not scoring inputs.

where score is not null: Drops rows where CMS reported “Not Available” / “N/A” (already nulled by safe_numeric in staging). These can’t contribute to z-scoring, so filtering early prevents them from inflating row counts and creating confusing NULLs in downstream aggregations.

Column renames: start_date → reporting_period_start, end_date → reporting_period_end to match the dimensional model spec and be self-documenting at the mart layer.

22. Wide vs. Tall: Why the HVBP Tables Don’t Fit

Even if the HVBP tables were scoring inputs, they couldn’t UNION into the fact table without intermediate unpivot models. They’re wide format — one row per hospital with per-measure column groups like hai_1_sir, hai_1_winsorized_z_score, hai_1_achievement_points, etc.

The 5 scoring tables are tall format — one row per hospital × measure, with a measure_id column. These shapes are fundamentally incompatible for UNION ALL. Unpivoting wide → tall would require intermediate int_hvbp_*_unpivot.sql models. Since the HVBP tables are validation-only, this work isn’t needed for the scoring pipeline.

Transform Layer Summary

Key decision: dim_measure reads from raw {{ source() }} tables instead of {{ ref('stg_*') }} views because the staging views deliberately dropped measure_name — it belongs in the dimension, not in every fact row. HCAHPS also needs hcahps_question which was dropped from stg_hcahps. This is the one legitimate case where a transform model should bypass the staging views.

Mart Layer Dev Log (2026-03-17)

23. Z-Score Sign Inversion — Getting Directionality Right

The z-score formula is (score - mean) / stddev. A hospital with a low mortality rate gets a negative z-score — below the mean. But low mortality is good. Without correction, the best hospitals in mortality/safety/readmission would appear to be the worst.

The fix: multiply by -1 for “lower is better” measures, so that positive z-scores always mean better performance regardless of the measure’s natural direction. dim_measure.higher_is_better controls this. The flag was set up during the transform layer (dev log #16) using domain-level defaults with 5 measure-specific overrides — that design decision pays off here.

Edge case — zero standard deviation: If every hospital reports the same score for a measure, stddev is 0 and the z-score would be division by zero. The model returns null for these, and the downstream domain_rollup filters them out via where z_score is not null so they don’t drag domain averages toward zero.

24. Domain Rollup — Simple Average, No Weighting Within Domains

Each domain’s score is the simple avg(z_score) across all measures in that domain for a given hospital. There’s no within-domain weighting — CMS treats all measures in a domain equally. A hospital with 6 HAI measures and 8 mortality measures just averages each set independently.

The rollup collapses 266,829 measure-level rows into 20,412 domain-level rows — roughly 4,599 hospitals × ~4.4 domains each. Not every hospital has all 5 domains, which is the whole reason the next step (weighted summary) needs weight redistribution.

25. PostgreSQL FILTER Clause — Conditional Aggregation for Pivoting

Pivoting the domain rollup from tall (one row per hospital × domain) to wide (one row per hospital, domain columns) would normally require a CTE with a self-join or a CASE WHEN inside each aggregate. PostgreSQL’s FILTER (WHERE ...) clause is much cleaner:

select
    facility_id,
    max(domain_z_score) filter (where domain = 'mortality') as mortality_z,
    max(domain_z_score) filter (where domain = 'safety') as safety_z,
    ...
from domain_rollup
group by facility_id

max() works here because there’s exactly one row per facility × domain after the rollup — it’s just extracting the single value. FILTER is standard SQL:2003 but only PostgreSQL and SQLite implement it. If this ever needed to run on another database, you’d fall back to max(case when domain = 'mortality' then domain_z_score end).

26. Weight Redistribution — The Algebra Shortcut

CMS assigns base weights to each domain: mortality 22%, safety 22%, readmission 22%, patient experience 22%, timely & effective care 12%. When a hospital is missing a domain, those weights need to be redistributed proportionally across the remaining domains so they still sum to 1.0.

The explicit approach would compute each domain’s redistributed weight individually. For a hospital missing patient experience (base weight 0.22):

• Sum of remaining base weights: 0.22 + 0.22 + 0.22 + 0.12 = 0.78
• Redistributed weights: mort = 0.22/0.78 ≈ 0.282, safety ≈ 0.282, readm ≈ 0.282, te = 0.12/0.78 ≈ 0.154
• Summary = 0.282×mort_z + 0.282×safety_z + 0.282×readm_z + 0.154×te_z

This is correct but requires computing 5 conditional weight columns — lots of branching SQL for every possible combination of missing domains (2⁵ = 32 possibilities).

The shortcut: multiply each domain by its original base weight, sum them, then divide by the total weight of domains that exist. This is algebraically identical because division distributes over addition:

(0.22×mort_z + 0.22×safety_z + 0.22×readm_z + 0.12×te_z) / 0.78

…is the same as:

(0.22/0.78)×mort_z + (0.22/0.78)×safety_z + (0.22/0.78)×readm_z + (0.12/0.78)×te_z

The SQL implementation uses coalesce(domain_z * base_weight, 0) for the numerator (missing domains contribute zero) and sums the base weights of non-null domains for the denominator. One division instead of five conditional weight calculations. Works for all 32 possible combinations of missing domains without any branching logic.

Result: 4,599 hospitals with a summary_score. That’s ~827 fewer than the 5,426 in dim_hospital — those hospitals had zero reportable measures across all domains.

Mart Layer Summary

The full lineage from staging to summary score:

staging (11 views)
  → dim_hospital (5,426)
  → dim_measure (168)
  → fact_hospital_measure (266,829)
    → zscore_by_measure (266,829)
      → domain_rollup (20,412)
        → weighted_summary (4,599)

Phase 3 (dbt transforms) is complete. Phase 4 (scoring) picks up the summary_score column in Python for k-means clustering and the safety cap rule.