# MA Star Ratings — per-measure value + star

`medicare_silver.stars_ratings_measure` · Medicare Advantage, Part D, Medicaid · status live

The measure-level layer beneath the summary star: each contract's value and star on each measure. Drives Stars-trajectory and measure-gap analysis (Wolfe sell-side).

**Grain.** One row = one (contract_id × measure_id × rating_year) — i.e. a single Medicare plan contract's score on one specific Star measure for one annual rating cycle. VERIFIED minimal: contract_id × measure_id alone yields only 86,697 distinct combos vs 439,831 rows (so the year is required), while contract_id × measure_id × rating_year yields exactly 439,831 = the full row count, with a GROUP BY ... HAVING COUNT(*)>1 returning 0 duplicate groups. rating_year is interchangeable here with calculation_epoch, bronze_publication_id, and effective_period_start (all four are strictly 1:1 — exactly one CMS publication per rating year).

**How to use it.** Trend one plan contract's quality on a specific measure over time, or rank contracts within a rating year. Example: SELECT rating_year, raw_value, star_value FROM medicare_silver.stars_ratings_measure WHERE contract_id='H0028' AND measure_id='C01' AND star_value_status='VALID' ORDER BY rating_year — returns that MA contract's medical-quality measure C01 raw percentage and 1-5 star score year by year. To pull a contract's full RY2026 scorecard: WHERE contract_id=:c AND rating_year=2026; to find top performers on a measure: WHERE measure_id='D08' AND rating_year=2026 AND star_value=5. Always join measure_id to an external measure dictionary to label the codes, and pair contract_id with cost/spend tables to get a value (quality-vs-cost) view.

**Traps.**
- GRAIN trap: the profiler reports the key as contract_id × measure_id × bronze_publication_id, but bronze_publication_id is 1:1 with rating_year (one CMS publication per year). The true human grain is contract_id × measure_id × rating_year (= 439,831 rows exactly). contract_id × measure_id ALONE is NOT unique (only 86,697 distinct) — you MUST include the year or you'll silently collapse a plan's 15-year history into one row.
- is_current / vintage dedup is currently a NO-OP: every row has is_current = true, cms_vintage_sequence = 1, parse_revision = 0, and restatement_of / effective_period_end are 100% NULL. So there are no superseded versions to filter today — but a single rating_year still maps to exactly one publication, so a naive COUNT over the table already counts each fact once. Add WHERE is_current = true defensively for forward-compatibility, not because duplicates exist now.
- NULL values carry meaning, and the TWO status columns can disagree. raw_value/star_value are NULL exactly when their respective *_status is not 'VALID' (suppressed: PLAN_TOO_SMALL, NO_DATA, or generic OTHER_STATUS) — a NULL is 'suppressed/unavailable', not missing-at-random. Critically, raw_value_status and star_value_status DIVERGE on ~15k rows (e.g. OTHER_STATUS raw but VALID star), so check BOTH; don't infer one from the other.
- Units of raw_value are per-measure and wildly non-uniform (0 to 3.36M; most measures are 0-100 percentages, others are counts/rates). NEVER sum or average raw_value across different measure_ids — it mixes percentages with counts. There is also NO measure-name/label column here: measure_id (C01.., D01.., PRE2009_*) is an opaque code that needs an external measure dictionary to interpret.
- This is the MEASURE level (per individual quality measure) and star_value is whole-stars only (1-5, zero half-stars). It is NOT the contract's overall/summary Star Rating — that headline number (with half-star values like 4.5) lives in a separate summary/rating table. Also: rating years 2009-2013 are absent, and 2007-2008 use legacy PRE2009_* free-text measures that don't map to the C/D codes — don't treat measure sets as constant across years.
- performance_year (= rating_year - 2) and payment_year (= rating_year) are fully derivable from rating_year and add no information; asserted_at is the May-2026 backfill/ingest time, NOT the rating date. For any time-based analysis use rating_year (or effective_period_start), never asserted_at.

| column | type | meaning |
|---|---|---|
| contract_id | string | CMS contract identifier for the MA/Part-D plan being rated (e.g. 'H0028'). 1,703 distinct. First letter is the contract type: H = local MA/MAPD (1,521 contracts, ~89% of rows), S = standalone Part-D PDP (139), R = regional PPO (37), E = employer/union EGWP (6). This is the contract level, NOT the plan-benefit-package (PBP) level — Star ratings are published per contract, not per PBP. |
| measure_id | string | The individual Star quality measure scored. 80 distinct across all years, in three namespaces: C01-C36 = Part C / medical (MA) measures (e.g. screenings, plan administration, member experience); D01-D15 = Part D / prescription-drug measures (e.g. medication adherence, MTM); PRE2009_* = legacy free-text measure names used only in the 2007-2008 pre-standardized era (e.g. 'PRE2009_RATING_OF_DRUG_PLAN'). measure_id is a stable per-program code but is NOT self-describing — there is no measure-name/label column in this table, so a separate measure dimension/lookup is needed to know what each code means. Measure sets change yearly (e.g. C35/C36 exist only in 2014; C34 ends 2019; D13-D15 retired by 2021). |
| raw_value | double | The underlying performance value for the measure BEFORE conversion to stars — float. Units are PER-MEASURE and not uniform: most measures are percentages (0-100), but some are counts or rates (global range observed 0 to 3,362,318; median ~80.7). Never aggregate or average raw_value ACROSS different measure_ids — it mixes incompatible units. NULL whenever raw_value_status is not 'VALID'. No negatives (global min = 0). |
| raw_value_status | string | Quality/availability flag for raw_value. Four values: VALID (242,587 rows, raw_value populated), OTHER_STATUS (153,329 — generic not-reported, raw NULL), PLAN_TOO_SMALL (22,908 — denominator too small to report, raw NULL), NO_DATA (21,007 — no data submitted, raw NULL). Non-VALID ALWAYS means raw_value is NULL — so a NULL raw_value is meaningful (suppressed/unavailable), not missing-by-accident; read this column to know WHY. |
| star_value | double | The measure-level star rating derived by binning raw_value into cut-points — float, integer stars 1.0-5.0 ONLY (no half-stars at this grain; verified 0 fractional values). Distribution skews high (4★ most common at 73,252; 1★ rarest at 14,957). Monotonic with raw_value for higher-is-better measures (e.g. C01: 1★ tops out at raw 57, 5★ starts at raw 65). NULL whenever star_value_status is not 'VALID'. NOTE: this is the per-MEASURE star, not the contract's overall/summary Star Rating (that lives in a separate summary table and DOES carry half-stars). |
| star_value_status | string | Quality flag for star_value, same four-value enum as raw_value_status: VALID (254,840), OTHER_STATUS (143,070), PLAN_TOO_SMALL (22,908), NO_DATA (19,013). IMPORTANTLY this can DIVERGE from raw_value_status: 13,788 rows have OTHER_STATUS raw but a VALID star, and 1,519 have VALID raw but NO_DATA star — so the two status columns are independent and must each be checked; do not assume one implies the other. |
| raw_value_text | string |  |
| raw_value_message_code | string |  |
| star_value_text | string |  |
| star_value_message_code | string |  |
| effective_period_start | date | Date the rating publication takes effect (e.g. 2016-10-12 for RY2017) — proxies the CMS release date. 15 distinct values, exactly one per rating year, 1:1 with rating_year / calculation_epoch / bronze_publication_id. Used as the table's temporal axis. (Note RY2024 sits at 2024-07-02, an off-cadence early date, vs the typical Oct release.) |
| effective_period_end | date | End of the effective window for SCD-2-style validity. 100% NULL across all 439,831 rows — an unused placeholder; the table currently keeps only one (current) version per rating year so no row has ever been end-dated. |
| calculation_epoch | string | Logical version label of the calculation run, formatted 'ry{YEAR}_v1' (e.g. 'ry2017_v1'). 15 distinct, 1:1 with rating_year. The '_v1' suffix and the fact that all are v1 confirm only one calculation vintage per year has been ingested (no restatements yet). |
| restatement_of | string | Pointer to a prior version this row restates/supersedes (for CMS mid-year restatements). 100% NULL — the restatement machinery exists in the schema but has never been exercised; no row restates another. |
| parse_revision | int | Parser revision counter for the bronze->silver extraction. Always 0 (single value) — every row was produced by the first parse revision. |
| parser_artifact_hash | string | Short hash of the parser code/artifact that produced the row (e.g. '86dfb448d9b9beb9') — provenance/reproducibility. Effectively one parser across the table. |
| source_url | string | The CMS download URL the data was extracted from (e.g. the published Star Ratings ZIP on cms.gov). 15 distinct — one source file per rating year. Provenance/lineage to the public origin. |
| sha256_content_hash | string | SHA-256 of the source file's content — content-addressed provenance to detect/identify the exact bronze artifact. 15 distinct (one per source file/publication). |
| bronze_publication_id | string | UUID of the bronze-layer publication (ingest event) this row came from. 15 distinct, strictly 1:1 with rating_year. The profiler reports this as part of the grain, but it is REDUNDANT with rating_year — it identifies the source publication, not a finer slice. Use rating_year for human queries; bronze_publication_id is the lineage handle. |
| asserted_at | timestamp | Timestamp when this row was written/asserted into the silver table (the bitemporal 'system time'). Ranges 2026-05-18 to 2026-05-28 — i.e. the whole 2007-2026 history was backfilled in a single late-May 2026 load window. This is ingestion time, NOT the measurement/rating date (use effective_period_start / rating_year for that). |
| rating_year | int | The CMS Star Ratings 'rating year' — the year the ratings are published/branded (e.g. 2026 ratings released Oct 2025). 15 values: 2007, 2008, then a GAP, then a continuous 2014-2026. The primary human-facing time key. NOTE the missing 2009-2013 years — this table does not contain those rating years. |
| performance_year | int | The measurement/data-collection year the scores are based on. Strictly = rating_year - 2 for EVERY row (verified). Derivable from rating_year; reflects that Star Ratings lag the measured performance by ~2 years (e.g. RY2026 ratings reflect 2024 performance). |
| payment_year | int | The MA Quality-Bonus-Payment year tied to the rating. Strictly = rating_year for EVERY row (verified; offset 0). Fully redundant with rating_year in this data. |
| is_current | boolean | Flag marking the current/active version of a row in the bitemporal model. TRUE for 100% of rows (all 439,831). Because no restatements have been loaded, this column is currently a no-op — but query hygiene says filter is_current = true so future superseded rows won't double-count. |
| cms_vintage_sequence | int | Sequence number of the CMS vintage for that rating year (1 = first/original release, would increment on a restated release). Always 1 — only the original vintage of each year is present. |
| qbp_payment_year | int |  |

Tools that read it: get_star_benchmark, get_star_decomposition, get_star_risk, get_stars, get_stars_rollup

Query it: `query_substrate` with `SELECT ... FROM medicare_silver.stars_ratings_measure WHERE ... LIMIT 100`; read `describe_data_source("medicare_silver.stars_ratings_measure")` first.