# ICD-10 to HCC crosswalk (reference)

`medicare_silver.dim_icd10_hcc_crosswalk` · Medicare Advantage, Part D, Medicaid · status live · 171,676 rows

CMS reference crosswalk mapping each ICD-10-CM diagnosis to its Hierarchical Condition Category under a specific risk-model version and payment year. The diagnosis-to-risk-bucket dictionary behind MA / Part D risk scoring.

**Grain.** One row per (icd_code, model_version, rate_year) — i.e. one diagnosis code's HCC assignment under one risk model in one payment year. Verified unique: COUNT(DISTINCT (icd_code, model_version, rate_year)) = 171,676 = total row_count. Each such triple maps to exactly ONE hcc_category (0 triples have >1 HCC), so hcc_category is the looked-up VALUE, not part of the key.

**How to use it.** To risk-adjust a 2026 MA Part C population, map each patient diagnosis to its current payment HCC: SELECT icd_code, hcc_category, icd_description FROM medicare_silver.dim_icd10_hcc_crosswalk WHERE model_version='V28' AND rate_year=2026 AND icd_code IN (...patient ICDs...). This returns the V28 HCC bucket per diagnosis, which then joins to the HCC coefficient/RAF tables to compute the risk score (use RxHCC_V08 instead for the Part D drug component).

**Traps.**
- model_version is part of the grain and CHANGES the answer: the same ICD maps to different HCCs across models. Example 2026, ICD A0104 -> HCC39 under V21/V22/V24 but HCC92 under V28. NEVER look up an ICD without pinning model_version (use V28 for current MA Part C payment) AND rate_year.
- hcc_category is ALWAYS prefixed 'HCC' even for the Part D drug model RxHCC_V08 — the prefix does NOT distinguish payment-HCC from Rx-HCC. The two taxonomies use entirely different numbering (e.g. ICD A072 -> HCC6 under V28 but HCC5 under RxHCC_V08). The taxonomy is defined solely by model_version, not by the hcc_category string.
- rate_year 2027 is provisional: its 171k-subset is byte-identical to 2026 (0 mapping mismatches, same sha256_content_hash, source_url is '2027-initial'). Treat 2027 as a placeholder reuse of 2026 midyear-final mappings, not a confirmed independent CMS release; 2026 is the latest real/final year.
- Several columns look like keys but are NOT: hcc_category is the looked-up value (1 per grain triple, not a key); bronze_publication_id is strictly 1:1 with rate_year (redundant); icd_code alone is NOT unique (11,395 codes -> 171,676 rows). The only verified minimal key is (icd_code, model_version, rate_year).
- No is_current / snapshot / SCD column exists. The table is simply partitioned by rate_year (identity), each year = one CMS publication. There is no per-row versioning to dedup; asserted_at and parser_artifact_hash are provenance only (asserted_at = a ~6-min load on 2026-05-18, NOT the CMS publish date).
- icd_description is not a reliable dictionary: it can differ across years for the same code (CMS wording edits) and is duplicated per (model_version) row, so SELECT DISTINCT icd_description can return >1 string for one code. icd_code is dot-free (A0103) — strip dots before joining to a dotted ICD source.

| column | type | meaning |
|---|---|---|
| rate_year | int | CMS payment/benefit year the mapping applies to (int). Partition column (identity partition). 4 values: 2024, 2025, 2026, 2027. Part of the grain key. Each year = one CMS publication load. |
| model_version | string | CMS risk-adjustment model that defines the HCC taxonomy. 5 values: V21, V22, V24, V28 (CMS-HCC payment models for MA Part C — V28 is the current/newest payment model, V21/V22/V24 are legacy/blend models still used in transition) and RxHCC_V08 (the Part D prescription-drug RxHCC model, a DIFFERENT taxonomy). Part of the grain key — the SAME ICD maps to DIFFERENT HCCs under different model_versions, so you must always filter this. |
| icd_code | string | ICD-10-CM diagnosis code in CMS dot-free format (e.g. 'A0103', not 'A01.03'). Lengths 3-7, no decimal points, no nulls. 11,395 distinct codes total. Part of the grain key. |
| hcc_category | string | The Hierarchical Condition Category the diagnosis maps to, e.g. 'HCC92'. This is the LOOKED-UP VALUE, not a key. ALWAYS prefixed 'HCC' even for the RxHCC model, so the prefix does NOT tell you which taxonomy — the numbering only makes sense within a given model_version (numeric part ranges 1-463). 218 distinct values across all models; e.g. for 2026: V28 spans 115 HCCs, RxHCC_V08 84. |
| icd_description | string | Plain-English long description of the ICD-10 code (e.g. 'Typhoid pneumonia'). Sourced from the CMS mapping file. NOT a stable per-code dictionary — wording can drift across rate_year for the same code (15 codes differ, e.g. 'Megaloencephalic'->'Megalencephalic'), because it follows the year's published file. No nulls. |
| source_url | string | CMS download URL of the ZIP the mappings were parsed from (e.g. '.../2026-midyear-final-icd-10-mappings.zip'). One distinct URL per rate_year. Note the 2026 file is labeled 'midyear-final' while 2024/2025/2027 are 'initial' — provenance, not a join key. |
| sha256_content_hash | string | SHA-256 of the source file content, for provenance/integrity. One distinct hash per rate_year — EXCEPT 2026 and 2027 share the identical hash (2027 reuses the 2026 midyear-final content). A change in this hash signals CMS republished the file. |
| bronze_publication_id | string | Provenance ID of the bronze ingestion that produced this year's rows (e.g. 'hcc_all_2026_66d75bf7'). Exactly 1:1 with rate_year (4 ids, 4 years) — REDUNDANT with rate_year for grain; do not mistake it for a finer key. |
| asserted_at | timestamp | Timestamp the silver rows were materialized/asserted. Only 4 distinct values, all within a ~6-minute window on 2026-05-18 — one assertion per rate_year load. This is a pipeline-load time, NOT when CMS published; use source_url/rate_year for CMS recency. |
| parser_artifact_hash | string | Hash identifying the parser code version that produced the rows. IDENTICAL across every row ('e10d366596d1c801') — a single parser version built the whole table; useful only to detect future reparses. |

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