# MA Star Ratings — cross-year measure crosswalk

`medicare_silver.dim_stars_measure_crosswalk` · Medicare Advantage, Part D, Medicaid · status partial

The map that lets a measure be trended across CMS renumbering (e.g. C27 to C30). 84 certain (CMS Attachment J, RY2024-2026) plus 385 offline-derived (RY2015-2024).

**Grain.** Logically, one row per (source measure, source rating year) per provenance source — i.e. one mapping of a measure from year N to year N+1, recorded once per CMS source document. The MINIMAL verified unique key is (from_measure_id, from_rating_year, source) = 109 distinct logical rows (equivalently to_measure_id/to_rating_year are derivable, since no measure fans out). IMPORTANT: the physical table holds 53,079 rows, NOT 109 — every logical row is duplicated many times (median 631x for the primary source, 3x for the secondary), an append-without-dedup artifact. There are only 89 distinct (from_measure_id,from_rating_year,to_measure_id,to_rating_year) tuples and 109 distinct full rows. You MUST SELECT DISTINCT (or GROUP BY the key) to recover the true grain; raw COUNT(*) is meaningless.

**How to use it.** To translate a 2025 Part C measure to its 2026 ID before joining year-over-year Star Ratings data: SELECT DISTINCT from_measure_id, to_measure_id, crosswalk_type, notes FROM dim_stars_measure_crosswalk WHERE from_rating_year=2025 AND from_measure_id='C14'. Returns C14 -> C17 (renamed), telling you that RY2025 'Medication Reconciliation Post-Discharge' (C14) is RY2026 C17, so a naive join on code 'C14' across years would silently mismatch.

**Traps.**
- ROW COUNT IS A LIE: 53,079 physical rows but only 109 distinct logical rows / 89 distinct measure-pair tuples. Every mapping is appended many times (median 631x for source='cms_tech_notes_attachment_j', 3x for source='cms_tech_notes'). ALWAYS SELECT DISTINCT or GROUP BY the grain key — never trust COUNT(*), AVG, or unaggregated joins against this table or you will multiply downstream rows ~600x.
- is_current IS A DEAD FLAG: it is constant true for all 53,079 rows and performs NO dedup or versioning. Filtering is_current=true removes nothing and does NOT collapse the duplicates. (This is unlike sibling Medicare snapshot dims where is_current actually selects the live version.)
- TWO PROVENANCE VARIANTS OF THE SAME MAPPING: source has two values ('cms_tech_notes_attachment_j' = primary/authoritative, 'cms_tech_notes' = secondary). The same measure pair often appears under both, which is why distinct full-rows (109) > distinct measure tuples (89). Pick one source (prefer cms_tech_notes_attachment_j) when deduping, or you'll get two rows per mapping.
- to_rating_year and to_measure_id are NOT independent keys. to_rating_year is ALWAYS from_rating_year+1 (only consecutive-year links: 2024->2025, 2025->2026 — no multi-year hops). to_measure_id is functionally determined by (from_measure_id, from_rating_year) with zero fan-out. So the real key is just the from-side + source.
- THE WHOLE POINT IS ID REUSE DANGER — read notes. CMS renumbered Part C measures for RY2026 (inserted C13 'Kidney Health Evaluation', bumping downstream codes +3), so the SAME code means DIFFERENT measures across years. notes carries explicit WARNINGs (e.g. 'RY2026 C04 is a DIFFERENT measure', 'RY2026 C13 is a NEW measure, no RY2025 predecessor'). Part D (D01-D12) stayed identity in both transitions; 2024->2025 is 100% identity; only 2025->2026 Part C has renames. Do not assume a code is stable across years just because crosswalk_type isn't checked.

| column | type | meaning |
|---|---|---|
| from_measure_id | string | Source Star Ratings measure ID in the EARLIER rating year (string). 42 distinct values across two prefixes: C## = Part C / health-plan measures (e.g. C01 Breast Cancer Screening, C14 Controlling Blood Pressure), D## = Part D / drug-plan measures (D01-D12). Combined with from_rating_year this is the left side of the year-over-year mapping. NO nulls. |
| from_rating_year | int | The earlier CMS Star Ratings Year (int) of the mapping. Only two values observed: 2024 and 2025. This is the table's primary time column (profiler temporal: earliest 2024, latest 2025). |
| to_measure_id | string | The same logical measure's ID in the LATER rating year (string), 45 distinct values. For crosswalk_type='identity' it equals from_measure_id; for 'renamed' it is the renumbered ID (e.g. C14->C17, a +3 shift). Functionally determined by (from_measure_id, from_rating_year) — verified zero fan-out (no from-measure maps to >1 to-measure). NO nulls. |
| to_rating_year | int | The later Star Ratings Year (int). VERIFIED to ALWAYS equal from_rating_year + 1 (53,079/53,079 rows). So the table only links CONSECUTIVE years: 2024->2025 and 2025->2026. Carries no independent information beyond from_rating_year+1. |
| crosswalk_type | string | How the measure ID changed across the two years. Only TWO values: 'identity' (ID stable, to_measure_id == from_measure_id; 57 distinct logical mappings) and 'renamed' (ID renumbered, to_measure_id != from_measure_id; 52 distinct logical mappings). 2024->2025 is 100% identity; 2025->2026 has the renames (Part C shifted, Part D stayed identity). |
| confidence | string | Stated confidence of the mapping. CONSTANT — every row is 'certain'. Carries no discriminating information in the current data (1 distinct value); exists for future lower-confidence/fuzzy mappings. |
| source | string | Provenance label citing the CMS document the mapping was read from. TWO values: 'cms_tech_notes_attachment_j' (69 logical mappings — the primary/authoritative set, physically appended ~631x) and 'cms_tech_notes' (25 logical mappings — a secondary near-duplicate variant, appended only ~3x). The 89->109 distinct-row inflation is driven entirely by the same measure pair appearing under BOTH source labels. NO nulls. |
| notes | string | Free-text human description of the measure plus CRITICAL warnings about ID-reuse traps. ALWAYS populated (zero nulls/empties). Contains the renumbering rationale and danger flags, e.g. 'Controlling Blood Pressure. Bumped +3 (C13 newly used for Kidney Health Evaluation).' and 'WARNING: RY2026 C04 is a DIFFERENT measure ... returned from display' and 'WARNING: RY2026 C13 is a NEW measure ... no RY2025 predecessor.' This is the field that prevents naive ID-based joins across years. |
| is_current | boolean | Boolean snapshot/active flag. CONSTANT true for all 53,079 rows (0 false). It does NOT perform any dedup or versioning here — there is no historical/superseded slice. Filtering on is_current=true is a no-op and does NOT remove the physical row duplication. |

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