Medicare Advantage, Part D, Medicaid

MA Star Ratings — Categorical Adjustment Index

medicare_silver.stars_cai·live·silver·annual·Medicare Advantage Star Ratings

Rows

N/A

Columns

History

rating_year 2017 (effective_period_start 2016-10-12) to rating_year 2026 (effective_period_start 2025-10-10)

Card

yesgrain, traps, filters

Typed tools

What it is

1CMS Medicare Advantage / Part D Star Ratings Categorical Adjustment Index (CAI) reference table: for each MA/Part-D contract and each rating dimension (Part C, Part D MA-PD, Part D PDP, Overall) it records which CAI "Final Adjustment Category" (FAC) bucket the contract fell into for a given Star Ratings year.
2The CAI is the LIS/DE/disability case-mix adjustment CMS adds to a contract's summary/overall Star Rating.
3This is a quality-rating context table (it carries no dollar amounts itself); it is useful in COST analysis only as a covariate — e.g. linking a plan's risk-adjusted star quality to its benchmark/payment or to commercial-vs-Medicare price comparisons.
4Sourced from the annual CMS Star Ratings data-table ZIPs (2017-2026).

Grain

Key Grain

Key	Grain
`contract_idpartcalculation_epoch`	1Logically one row per (contract_id × part × calculation_epoch) — i.e. one CAI/FAC assignment per Medicare contract, per rating dimension, per Star Ratings year. 2That clean grain is 28,964 logical rows. 3BUT the physical table has 32,730 rows because the three earliest epochs (ry2017/2018/2019) were each ingested twice and both copies are left flagged is_current=true: 3,766 (contract,part,epoch) groups appear as a duplicate pair — one stale row with fac_category=NULL (asserted_at 2026-05-17) and one good row with fac_category populated (later asserted_at). 4To get the true one-row-per-entity grain you must dedup by keeping MAX(asserted_at) within (contract_id, part, calculation_epoch); the latest row is always the fac-populated one. 5Physically, the only fully-unique key is (contract_id, part, calculation_epoch, asserted_at) = 32,730, but asserted_at is an ingest-lineage timestamp, not a business key.

contract_idpartcalculation_epoch

1Logically one row per (contract_id × part × calculation_epoch) — i.e. one CAI/FAC assignment per Medicare contract, per rating dimension, per Star Ratings year.
2That clean grain is 28,964 logical rows.
3BUT the physical table has 32,730 rows because the three earliest epochs (ry2017/2018/2019) were each ingested twice and both copies are left flagged is_current=true: 3,766 (contract,part,epoch) groups appear as a duplicate pair — one stale row with fac_category=NULL (asserted_at 2026-05-17) and one good row with fac_category populated (later asserted_at).
4To get the true one-row-per-entity grain you must dedup by keeping MAX(asserted_at) within (contract_id, part, calculation_epoch); the latest row is always the fac-populated one.
5Physically, the only fully-unique key is (contract_id, part, calculation_epoch, asserted_at) = 32,730, but asserted_at is an ingest-lineage timestamp, not a business key.

How to read it

1Get each contract's current CAI Final-Adjustment-Category for the latest rating year, deduplicated correctly: WITH d AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY contract_id, part, calculation_epoch ORDER BY asserted_at DESC) rn FROM medicare_silver.stars_cai) SELECT contract_id, part, fac_category, rating_year FROM d WHERE rn=1 AND part='Overall' AND rating_year=2026 AND fac_category IS NOT NULL.
2This answers 'which CAI case-mix adjustment tier did each MA contract land in for the 2026 Star Ratings?' — joinable by contract_id to PBP/Stars/payment tables to relate quality-rating case-mix to plan benchmarks or commercial-vs-Medicare cost comparisons.

Traps

1cai_value is 100% NULL — the actual CAI numeric adjustment is NOT stored here; this table only gives you the FAC bucket id (fac_category 1-12). If you need the CAI dollar/decimal add-on you must source it elsewhere.
2is_current = TRUE on every row and does NOT dedup the table. The three earliest epochs (ry2017/2018/2019) were ingested twice and both copies are flagged current: 3,766 (contract,part,epoch) groups have a stale fac_category=NULL row plus a good fac-populated row. Always dedup by MAX(asserted_at) within (contract_id, part, calculation_epoch) — that keeps the fac-populated row and yields the true 28,964-row grain.
3fac_category=NULL is overloaded: some NULLs are the legitimate 'no FAC assigned for this dimension' (e.g. most D-PDP rows; ~12,253 survive after dedup), but ~3,766 NULLs are purely the stale-duplicate artifact. After dedupping by latest asserted_at, a remaining NULL means genuinely no FAC.
4'part' is the rating DIMENSION (C / D-MA-PD / D-PDP / Overall), not the contract's product type. A standalone PDP (S-prefix) contract still has C and Overall rows. Do not infer plan type from part — and do not assume contract prefix predicts which parts are populated.
5fac_category looks like part of the key but is NOT — including it in a join/group makes the duplicate NULL-vs-populated pair look like two distinct rows. The minimal logical key is (contract_id, part, calculation_epoch); the only physically-unique key is that plus asserted_at (an ingest timestamp, not a business field).
6restatement_of, effective_period_end, parse_revision (0), cms_vintage_sequence (1), and parser_artifact_hash are all single-valued/empty SCD-and-provenance scaffolding today — useful for lineage but they carry no analytic signal in the current data.
7rating_year == payment_year, and performance_year == rating_year - 2 in every row; use calculation_epoch ('ryYYYY_v1') as the canonical year key since it is 1:1 with all of them.

Columns (21)

Column	Type	Meaning
`contract_id`	string	CMS Medicare contract ID, the entity this row is about. 1,147 distinct. First letter is the CMS contract-type convention observed in data: H = MA / MA-PD local plan, R = regional PPO / EGWP, S = standalone Part-D (PDP), E = employer/union EGWP. Joins to other Stars/PBP/enrollment tables on contract_id.
`part`	string	The Star-Ratings dimension the CAI/FAC assignment is for, NOT the contract's product type. 4 values: 'C' (Part C summary), 'D-MA-PD' (Part D within an MA-PD), 'D-PDP' (Part D within a standalone PDP), 'Overall'. A single contract appears under multiple parts (even S/PDP contracts have C and Overall rows), so do not treat part as the contract's plan type.
`fac_category`	int	The CAI 'Final Adjustment Category' bucket assigned to this contract-part-year, integer 1-12 (CMS groups contracts into FAC tiers by their LIS/DE + disability mix; the FAC maps to the actual CAI add-on value). NULL on ~49% of physical rows (16,019). Two distinct reasons: (a) legitimate — the contract-part-year simply had no FAC assigned (common for D-PDP and some dimensions; ~12,253 NULLs survive after dedup); (b) ARTIFACT — the stale duplicate copy of an early-epoch row (3,766 NULLs that have a populated twin). Higher categories are rarer (cat 1-2 most common, 11-12 very rare).
`cai_value`	double	Intended to hold the numeric CAI adjustment for the contract-part-year. WARNING: this column is 100% NULL across all 32,730 rows (0 non-null). The actual CAI numeric add-on is NOT in this table — only the FAC bucket id (fac_category) is populated. Treat cai_value as not-yet-loaded / empty.
`puerto_rico_flag`	boolean	Boolean. True for 484 rows (~1.5%), false for the rest. Marks Puerto-Rico-located contracts, which CMS handles specially in the CAI methodology (PR has no LIS, so a separate CAI track applies). Used as a methodology qualifier; it does NOT subdivide the grain (it is constant within a contract-part-epoch group).
`effective_period_start`	date	The CMS publication date of that year's Star Ratings data file — one single date per epoch (e.g. ry2019 = 2018-10-10). Acts as the as-of/release date. Note ry2024 = 2024-07-02 (an early July release that year, confirmed by the source filename '2024-star-ratings-data-tables-jul-2-2024.zip'), all other years release in October. 10 distinct dates 2016-10-12 → 2025-10-10.
`effective_period_end`	date	Intended close-date of the validity window. 100% NULL (no row has it set) — every record is treated as open-ended/current. SCD-2 scaffolding that is currently unused.
`calculation_epoch`	string	The canonical year-version key, format 'ryYYYY_v1' (ry2017_v1 … ry2026_v1, 10 values). 1:1 with rating_year and with effective_period_start. The '_v1' suffix implies versioned restatements are anticipated but none exist yet (all rows are _v1). This is the cleanest column to use as the year dimension in the grain.
`restatement_of`	string	Pointer to a prior calculation_epoch that this row restates/corrects. 100% NULL — no Star Ratings restatements have been ingested. Provenance/SCD scaffolding for future corrected releases.
`parse_revision`	int	Parser revision number for the row. Constant = 0 for all rows (single parser revision). Lineage field.
`parser_artifact_hash`	string	Hash identifying the parser code/artifact that produced the row. Single distinct value across the whole table — everything was parsed by one parser build.
`source_url`	string	The exact CMS download URL the row was extracted from — the annual Star-Ratings-and-display-measures / star-ratings-data-tables ZIP. One distinct URL per epoch; full provenance back to the public CMS file.
`sha256_content_hash`	string	SHA-256 of the source file content. One distinct hash per epoch (10 total), giving byte-level provenance / immutability proof of the ingested file.
`bronze_publication_id`	string	UUID of the bronze-layer ingest/publication batch that produced this row. 10 distinct values — one per epoch. Groups rows by the load event; the duplicate early-epoch rows still share the same bronze_publication_id as their good twin, so this id does NOT distinguish the stale copy (use asserted_at for that).
`asserted_at`	timestamp	Microsecond UTC timestamp when this specific row was written to silver — UNIQUE per row (32,730 distinct), spanning 2026-05-17 → 2026-05-28. This is the true SCD tiebreaker: the stale duplicate of an early epoch has the earlier asserted_at (2026-05-17, fac=NULL) and the good row a later one (fac populated). Dedup with MAX(asserted_at) per contract+part+epoch.
`rating_year`	int	The Star Ratings year (2017-2026). Equals payment_year in every row. 1:1 with calculation_epoch. This is the year the ratings are published-as.
`performance_year`	int	The underlying measurement/performance year, always rating_year minus 2 (e.g. rating_year 2019 → performance_year 2017), reflecting the ~2-year lag between the data-collection period and the published rating.
`payment_year`	int	The MA payment year the rating drives (quality-bonus payments). Identical to rating_year in all rows here. Present for downstream payment joins.
`is_current`	boolean	Intended is-active SCD flag. WARNING: it is TRUE on 100% of rows, including the 3,766 stale early-epoch duplicates — so is_current does NOT deduplicate to one logical row and cannot be relied on to pick the good copy. Effectively a no-op column in current data.
`cms_vintage_sequence`	int	Intended ordinal of the CMS vintage/restatement for a given year. Constant = 1 for every row (no second vintage exists yet). Placeholder for future restated releases.
`qbp_payment_year`	int

Query it

describe_data_source(source_fqn="medicare_silver.stars_cai")     # the card, live
query_substrate(sql="SELECT * FROM medicare_silver.stars_cai WHERE contract_id = '<value>' AND part = '<value>' AND calculation_epoch = '<value>' LIMIT 100")

Item	Value
Typed tools that read it	get_star_decomposition, get_star_risk
Identity key	`contract_id`
Join	`how_to_join(source_fqn="medicare_silver.stars_cai")`
Raw door	Athena (read-only via query_substrate): medicare_silver.stars_cai (filter is_current = true)
Note	Grain-governed (medicare_ma_stars).

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