# MA hedis hos oto

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

One row per (contract, year). 3-column HOS sheet. Single rate column. Published 2019 and 2021 only; absent 2022+ (CMS appears to have renamed/retired in favor of hedishos_mui).

**Grain.** One row per Medicare Advantage contract per HEDIS report year. The minimal verified key is (contract_id, report_year) — within any single year contract_id alone is unique (2019: 417/417 distinct, 2021: 475/475 distinct), and across the whole table contract_id || report_year yields 892 distinct = the full 892 rows. plan_name_raw, observed_period, effective_period_start, and effective_period_end are all functionally determined by (contract_id, report_year) and add nothing to the key (the profiler's 4-column grain is a non-minimal superset). Total 892 rows = 417 (report_year 2019) + 475 (report_year 2021).

**How to use it.** SELECT contract_id, plan_name_raw, report_year, rate FROM medicare_silver.hedis_hos_oto WHERE rate IS NOT NULL ORDER BY contract_id, report_year — gives each MA plan's osteoporosis-testing quality rate for 2019 and 2021, letting you rank plans on this HEDIS measure or compute the 2019->2021 change (e.g. H0028 rose 74.93 -> 80.21). Join on contract_id to other MA/Stars/HEDIS tables to pair quality with enrollment, benefits, or other measures.

**Traps.**
- NOT a cost/price table. Despite living in a US-healthcare-COST catalog, 'rate' here is a clinical QUALITY percentage (share of eligible women osteoporosis-tested, 0-100), not a dollar amount, payment rate, or price. Never treat 'rate' as money.
- OTO = 'Osteoporosis Testing in Older Women', a HEDIS measure collected via the Medicare Health Outcomes Survey (HOS). The sibling tables hedis_hos_frm (Fall Risk Management), hedis_hos_mui (Management of Urinary Incontinence), and hedis_hos_pao (Physical Activity in Older Adults) are different HOS measures with the same shape — pick by the measure, not by structure. Per Vlada's HEDIS verification ledger this OTO measure was RETIRED after 2021 (replaced in the HOS measure set), which is why the data stops at 2021.
- 2020 is genuinely missing, not a load bug: only report_year 2019 (417 rows) and 2021 (475 rows) exist (no 2020 HEDIS PUF was produced). Any year-over-year/trend logic must handle the 2019->2021 gap, not assume consecutive years.
- Minimal grain is just (contract_id, report_year) — 2 columns. The profiler's reported 4-column key (contract_id, plan_name_raw, effective_period_start, observed_period) is a valid but non-minimal SUPERSET; plan_name_raw, observed_period, effective_period_start/end are all derivable from (contract_id, report_year). contract_id alone is NOT unique across the table (505 distinct contracts span both years; 387 appear in both, 118 in only one) but IS unique within a single year.
- rate IS NULL (46 rows, ~5%) is meaningful, not dirty data: the contract exists but the OTO rate was not reported/was suppressed for that year (e.g. small or special-needs plans). Filter or impute deliberately; the contract+name are still valid.
- No snapshot/versioning to dedup: asserted_at, cms_vintage_sequence, and parse_revision are all single-valued (1 distinct each) and there is no is_current column. This is a one-time load of two CMS files — one row per contract-year already, so no is_current filter or latest-snapshot dedup is needed (unlike snapshot-style tables elsewhere in the warehouse).

| column | type | meaning |
|---|---|---|
| contract_id | string | CMS Medicare Advantage contract ID (H-number, e.g. H0028, H0104). 505 distinct contracts across the table; unique within a single report_year. This is the join key to other MA/Stars/HEDIS tables. NOT plan-benefit-package level — contract level only (no PBP/segment dimension). |
| plan_name_raw | string | Raw parent-organization / contract name as printed in the CMS HEDIS PUF (e.g. 'CHA HMO, INC.', 'BLUE CROSS AND BLUE SHIELD OF ALABAMA'). 416 distinct values, 'raw' = uncleaned/unnormalized as-published text. Redundant with contract_id for keying (one name per contract-year); do not use it as a grain column. |
| rate | double | The OTO measure result: percent (0-100 scale) of eligible older women in the contract who received osteoporosis testing. Observed range ~42-94, median ~77, mean ~75; 0 negatives, 0 values over 100. 46 of 892 rows (5.2%) are NULL = measure not publicly reported for that contract-year (suppressed small denominator / not collected). This is the single fact/value column of the table; everything else is a key or provenance attribute. |
| report_year | int | HEDIS reporting year of the PUF: only 2019 and 2021 exist (2020 is absent — the 2020 HOS/HEDIS PUF was not produced, and this OTO measure was retired after 2021). Part of the minimal grain. Equivalent to observed_period / effective_period_start year. |
| observed_period | date | Date stamp for the measurement period, always Jan 1 of the report_year (2019-01-01 or 2021-01-01). A year-as-date encoding; 2 distinct values, redundant with report_year. |
| asserted_at | date | Ingestion/load date into Vlada's silver layer — constant 2026-05-15 for every row (cardinality 1). Provenance only; carries no row-level versioning meaning here. |
| effective_period_start | date | Start of the period the rate covers: Jan 1 of report_year (2019-01-01 / 2021-01-01). Redundant with report_year/observed_period; the profiler used it as the temporal column. |
| effective_period_end | date | End of the covered period: Dec 31 of report_year (2019-12-31 / 2021-12-31). 2 distinct values, fully determined by report_year. |
| cms_vintage_sequence | int | Provenance sequence number for the CMS source vintage — constant 1 across all rows. No multiple vintages/re-releases are loaded, so it never disambiguates rows. |
| parse_revision | int | Vlada parser revision number — constant 1 across all rows. Provenance only; no second parse revision exists. |
| parser_artifact_hash | string | SHA-256 of the parser artifact that produced the row. Two distinct values, one per report_year file (2019 vs 2021 used different parser artifacts). Provenance lineage, not a key. |
| source_url | string | Original CMS download URL of the source ZIP. Two values: 2019 -> https://www.cms.gov/files/zip/hedis-ma-puf-2019.zip ; 2021 -> https://www.cms.gov/files/zip/ma-hedis-pufs-2021.zip (note the URL naming convention changed between years). One URL per report_year. |
| sha256_content_hash | string | SHA-256 content hash of the raw source file for provenance/integrity. Two distinct values, one per report_year file. Identical hash within a year confirms a single source file backs all that year's rows. |

Tools that read it: get_hos

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