# MA Star Ratings — Categorical Adjustment Index

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

The Categorical Adjustment Index that adjusts summary stars for low-income/disabled enrollment mix. Carries a Puerto-Rico-Only flag.

**Grain.** Logically one row per (contract_id × part × calculation_epoch) — i.e. one CAI/FAC assignment per Medicare contract, per rating dimension, per Star Ratings year. That clean grain is 28,964 logical rows. BUT 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). To 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. Physically, 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 use it.** Get 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. This 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.**
- cai_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.
- is_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.
- fac_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.
- '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.
- fac_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).
- restatement_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.
- rating_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.

| 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 |  |

Tools that read it: get_star_decomposition, get_star_risk

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