# MA hedis hos pao

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

One row per (contract, year). 4-column HOS sheet. Two rates (rate1, rate2). Stable across 2019-2025 (excluding 2020 gap).

**Grain.** One row per Medicare Advantage contract per HEDIS report year. Verified: contract_id x report_year yields exactly 3,207 distinct combos = the 3,207 total rows, and within every single report_year the count of rows equals the count of distinct contract_id (0 duplicate excess in 2019/2021/2022/2023/2024/2025). The pipeline that builds this table declares pk_columns=["contract_id","report_year"] and asserts that uniqueness. The profiler's 4-column key (adding plan_name_raw + effective_period_start + observed_period) is a SUPERSET — those extra columns are functionally determined by contract_id+report_year and are not needed to make the row unique.

**How to use it.** Trend a single plan's preventive-care quality, or rank plans within a year. Example: SELECT report_year, rate1 AS discussing_pa, rate2 AS advising_pa FROM medicare_silver.hedis_hos_pao WHERE contract_id='H0104' AND rate1 IS NOT NULL ORDER BY report_year; -- shows Blue Cross Blue Shield of Alabama's Physical-Activity-in-Older-Adults discussion/advice rates over time. For a peer benchmark in one year: SELECT contract_id, plan_name_raw, rate1, rate2 FROM medicare_silver.hedis_hos_pao WHERE report_year=2025 AND rate1 IS NOT NULL ORDER BY rate1 DESC. Join to other medicare_silver Star/HEDIS tables on contract_id (+ report_year) to assemble a contract's full quality profile.

**Traps.**
- NOT a cost/dollar table. 'rate1'/'rate2' are SURVEY PERCENTAGES (0-100 scale, observed 26-79), not prices, dollars, or fee-schedule rates. This is a clinical-quality / Star-ratings input. Do not interpret rate1/rate2 as money or as a commercial-vs-Medicare ratio.
- The minimal grain is contract_id + report_year (the build pipeline asserts this PK). The auto-generated catalog card says grain='one row per contract_id', which is WRONG across years (contract_id alone has only 735 distinct vs 3,207 rows because most contracts appear in multiple years). The profiler's reported key (contract_id + plan_name_raw + effective_period_start + observed_period) is a redundant SUPERSET — plan_name_raw, observed_period, and effective_period_start are all functionally determined by contract_id+report_year.
- 2020 is MISSING from report_year (sequence is 2019 then jumps to 2021) — HOS reporting was waived for the COVID year. Any year-over-year trend across 2019->2021 spans a 2-year gap; do not assume continuous annual coverage.
- NULL rate1/rate2 means the measure was suppressed / not reported for that contract-year (small denominator or non-reporting plans like AIDS Healthcare Foundation and niche SNPs), NOT zero performance. 63 rate1 nulls and 58 rate2 nulls; exclude nulls from averages rather than coalescing to 0.
- plan_name_raw is verbatim and un-normalized: one contract_id can carry up to 4 different name spellings across years (rebrands / vendor changes). Join and aggregate on contract_id, never on plan_name_raw.
- This is the HOS Physical Activity in Older Adults (PAO) measure ONLY. It has sibling tables in medicare_silver (hedis_hos_frm = Falls Risk Management, hedis_hos_mui = Urinary Incontinence, hedis_hos_oto, hedis_measures = headline HEDIS measures). The two rate columns mean different things in each sibling, so do not pool rate1/rate2 across the hedis_hos_* tables. Single snapshot only (one asserted_at, vintage=1, revision=1) — there is no is_current flag and no superseding versions to dedup.

| column | type | meaning |
|---|---|---|
| contract_id | string | CMS Medicare Advantage contract identifier (the H/R-number), e.g. 'H0028'. 735 distinct values. All values begin with 'H' (707 contracts, MA/MA-PD) or 'R' (28 contracts, regional PPO); no 'S' (PDP) since HOS surveys MA enrollees. Combined with report_year this is the row key. A single contract_id can appear under multiple plan_name_raw strings across years (vendor name changes / rebrands). |
| plan_name_raw | string | Marketing/legal name of the contracting organization as printed in the CMS PUF, e.g. 'BLUE CROSS AND BLUE SHIELD OF ALABAMA', 'WELLCARE OF GEORGIA, INC.' (635 distinct). 'raw' = verbatim, un-normalized. NOT part of the grain — it is an attribute of contract_id and can drift year to year (one contract_id maps to up to 4 different name strings). |
| rate1 | double | First PAO sub-measure rate, a survey-based percentage (double). Range observed 33.33-79.40, mean ~55.9, median ~55.8, never exceeds 100. For HOS Physical Activity in Older Adults this is the 'Discussing Physical Activity' rate — % of older enrollees who reported their doctor discussed/talked with them about physical activity. 63 NULLs = measure not reported/suppressed for that contract-year (null means no-data, NOT zero). CMS publishes the column literally headered 'rate1'; the two-rate meaning lives in the PUF documentation, not the data. |
| rate2 | double | Second PAO sub-measure rate, survey-based percentage (double). Range 26.00-76.44, mean ~49.9, median ~50.2, never >100. For HOS PAO this is the 'Advising Physical Activity' rate — % who received advice to start/maintain/increase physical activity. Consistently lower than rate1 on average (advising is a stricter step than discussing). 58 NULLs (suppressed/no-data). 58 rows are both-null; only 5 rows have rate1 null while rate2 is present, so partial reporting is rare but exists. |
| report_year | int | Integer HEDIS reporting year and the temporal half of the grain. Six values present: 2019, 2021, 2022, 2023, 2024, 2025. 2020 is ABSENT (HOS reporting was waived during COVID). Row counts grow over time: 417 (2019) -> 475 -> 522 -> 579 -> 605 -> 609 (2025) as MA contract count grows. |
| observed_period | date | Date = Jan 1 of report_year (the period the survey data describes). 6 distinct values. 100% equal to effective_period_start (redundant with it). Part of the profiler's superset key but not needed for uniqueness. |
| asserted_at | date | Provenance: the date Vlada ingested/asserted this snapshot into the silver table. Single value for the entire table: 2026-05-15. Use to know data freshness, not as a row dimension. |
| effective_period_start | date | Start of the measurement period = Jan 1 of report_year (e.g. 2025-01-01). This is the profiler's temporal column; earliest 2019-01-01, latest 2025-01-01. Equals observed_period for every row. |
| effective_period_end | date | End of the measurement period = Dec 31 of report_year (e.g. 2025-12-31). Together with effective_period_start it brackets the calendar reporting year; fully determined by report_year. |
| cms_vintage_sequence | int | Provenance/versioning integer for which CMS release vintage of a given period this is. Constant = 1 for all rows (only the first/initial PUF vintage per year has been loaded; no restated/re-released vintages present). A higher number would indicate a CMS re-release of the same year. |
| parse_revision | int | Internal parser revision number for the row. Constant = 1 across the whole table (single parse generation; would bump if the file were re-parsed with new logic). |
| parser_artifact_hash | string | SHA-256 of the parser artifact/run that produced the row (code+config provenance). 6 distinct values = one per (year) parse run / source file. Lets you trace exactly which parser build emitted a row. |
| source_url | string | CMS download URL of the originating ZIP, one per report_year, e.g. 'https://www.cms.gov/files/zip/ma-hedis-pufs-2025.zip' (2019 uses the older 'hedis-ma-puf-2019.zip' naming). The PAO numbers come from the 'hedishos_pao' sheet inside that MA HEDIS PUF zip. |
| sha256_content_hash | string | SHA-256 content hash of the exact source file the row was parsed from (byte-level provenance / dedup key). 6 distinct values = one per yearly source ZIP. All rows of a given report_year share one hash. |

Tools that read it: get_hos

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