VladaOmega
Sign inStart free
Medicare Advantage, Part D, Medicaid

MA Star Ratings — per-measure value + star

medicare_silver.stars_ratings_measure·live·silver·annual·Medicare Advantage Star Ratings
Rows
N/A
Columns
26
History
RY2007 (effective_period_start 2006-11-09; performance_year 2005) to RY2026 (effective_period_start 2025-10-10; performance_year 2024). Coverage: 2007, 2008, then 2014-2026 continuous (2009-2013 absent).
Card
yesgrain, traps, filters
Typed tools
5

What it is

  1. 1CMS Medicare Part C & D Star Ratings at the most granular level: one performance score per Medicare Advantage / Part D plan contract, per individual quality measure, per annual rating year.
  2. 2It is the silver (cleaned, provenance-stamped) layer of the publicly published CMS Star Ratings program, spanning rating years 2007-2026.
  3. 3In a cost-data catalog it is a quality/value signal (the "Q" to pair against cost): it lets an agent tie a payer contract's medical (Part C) and drug (Part D) quality scores to its spend and rate data, and underpins the Quality Bonus Payment dimension of MA economics.

Grain

KeyGrain
contract_idmeasure_idrating_year
  1. 1One 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.
  2. 2VERIFIED 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 ...
  3. 3HAVING 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 read it

  1. 1Trend one plan contract's quality on a specific measure over time, or rank contracts within a rating year.
  2. 2Example: 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.
  3. 3To 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.
  4. 4Always 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

  1. 1GRAIN 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.
  2. 2is_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.
  3. 3NULL 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.
  4. 4Units 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.
  5. 5This 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.
  6. 6performance_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.

Columns (26)

ColumnTypeMeaning
contract_idstringCMS 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_idstringThe 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_valuedoubleThe 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_statusstringQuality/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_valuedoubleThe 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_statusstringQuality 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_textstring
raw_value_message_codestring
star_value_textstring
star_value_message_codestring
effective_period_startdateDate 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_enddateEnd 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_epochstringLogical 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_ofstringPointer 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_revisionintParser revision counter for the bronze->silver extraction. Always 0 (single value) — every row was produced by the first parse revision.
parser_artifact_hashstringShort hash of the parser code/artifact that produced the row (e.g. '86dfb448d9b9beb9') — provenance/reproducibility. Effectively one parser across the table.
source_urlstringThe 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_hashstringSHA-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_idstringUUID 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_attimestampTimestamp 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_yearintThe 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_yearintThe 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_yearintThe 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_currentbooleanFlag 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_sequenceintSequence 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_yearint

Query it

describe_data_source(source_fqn="medicare_silver.stars_ratings_measure")     # the card, live
query_substrate(sql="SELECT * FROM medicare_silver.stars_ratings_measure WHERE contract_id = '<value>' AND measure_id = '<value>' AND rating_year = '<value>' LIMIT 100")
ItemValue
Typed tools that read itget_star_benchmark, get_star_decomposition, get_star_risk, get_stars, get_stars_rollup
Identity keycontract_id
Joinhow_to_join(source_fqn="medicare_silver.stars_ratings_measure")
Raw doorAthena (read-only via query_substrate): medicare_silver.stars_ratings_measure (filter is_current = true)
NoteGrain-governed (medicare_ma_stars). Powers the bit-exact RY2015-2027 recompute.

This page as markdown: /docs-md/data/medicare_silver_stars_ratings_measure.md · every page in one file: /llms-full.txt