HCC Risk Adjustment Data Model: Building Accurate Risk Score Pipelines

What Is HCC Risk Adjustment and Why Does the Data Model Matter?

Hierarchical Condition Categories (HCC) risk adjustment is the mechanism CMS uses to pay Medicare Advantage plans accurately for the health status of their enrolled members. Each member's Risk Adjustment Factor ([RAF score](/terms/raf-score)) is calculated from their age, sex, dual-eligibility status, and the HCC codes derived from their diagnoses. A member with diabetes plus chronic kidney disease and heart failure carries a higher RAF score — and therefore higher capitation payments — than a healthy 65-year-old.

The data model that supports this process is not a reporting afterthought. It is the backbone of revenue integrity. A pipeline that drops diagnosis codes, miscodes encounter types, or fails to correctly map ICD-10 to HCC under the active model year (currently V28) will produce RAF scores that undercount the true burden of illness in your population — directly reducing capitation revenue and exposing the plan to RADV audit risk.

This guide covers the core tables, relationships, SQL patterns, and data quality checks needed to build a production-grade HCC risk adjustment pipeline on Snowflake or BigQuery.

---

The CMS-HCC V28 Model: What Changed and Why It Matters

CMS transitioned from HCC V24 to HCC V28 starting in payment year 2024, phasing in over three years. V28 restructured the HCC hierarchy, added new condition categories, and recalibrated coefficients across the board. The key operational impact for data engineers:

• ICD-10 to HCC mappings changed. A diagnosis code that mapped to HCC 18 under V24 may map to a different HCC — or no HCC at all — under V28. Both crosswalk files must be maintained in your reference data layer during the transition.
• Hierarchies changed. V28 expanded the number of HCC groups. A member who triggers multiple HCCs within a group keeps only the highest-severity one. Your pipeline must apply the correct hierarchy for the payment year being scored.
• Coefficients changed. V28 coefficients weight chronic conditions differently than V24. Do not reuse V24 RAF score values for V28 payment years.

Store both crosswalk tables as versioned reference data. Tag every RAF score calculation with its model version. Downstream analytics and audit responses depend on knowing which model year produced a given score.

---

Core Data Model

The HCC risk adjustment model has four core layers: member demographics, diagnosis claims, HCC mapping reference data, and the computed risk score output.

Member Demographics Table

CREATE TABLE risk.member_demographics (
  mbr_id            VARCHAR(50)   NOT NULL,
  plan_id           VARCHAR(20)   NOT NULL,
  dob               DATE          NOT NULL,
  sex_cd            CHAR(1)       NOT NULL,   -- M / F
  dual_elig_cd      VARCHAR(10),              -- FBDE, FIDE, etc.
  esrd_ind          BOOLEAN       DEFAULT FALSE,
  medicaid_ind      BOOLEAN       DEFAULT FALSE,
  enrollment_dt     DATE          NOT NULL,
  disenroll_dt      DATE,
  payment_year      INT           NOT NULL,
  created_dt        TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
  PRIMARY KEY (mbr_id, payment_year)
);

Diagnosis Claims Input Table

This table holds the cleaned, deduplicated diagnosis codes that will be evaluated for HCC mapping. Load from professional (837P) and institutional (837I) claims, ensuring encounter type validation before any code reaches this table.

CREATE TABLE risk.dx_claims_input (
  clm_id            VARCHAR(50)   NOT NULL,
  mbr_id            VARCHAR(50)   NOT NULL,
  svc_dt            DATE          NOT NULL,
  dx_cd             VARCHAR(7)    NOT NULL,   -- ICD-10-CM, no decimal
  dx_cd_position    SMALLINT      NOT NULL,   -- 1 = principal
  enc_type_cd       VARCHAR(10)   NOT NULL,   -- INPATIENT, OUTPATIENT, etc.
  prvdr_npi         VARCHAR(10)   NOT NULL,
  payment_year      INT           NOT NULL,
  data_year         INT           NOT NULL,   -- calendar year of service
  PRIMARY KEY (clm_id, dx_cd, dx_cd_position)
);

HCC Reference Crosswalk

CREATE TABLE risk.icd_to_hcc_xwalk (
  dx_cd             VARCHAR(7)    NOT NULL,
  hcc_nbr           SMALLINT      NOT NULL,
  hcc_desc          VARCHAR(255)  NOT NULL,
  model_version     VARCHAR(10)   NOT NULL,   -- 'V24' or 'V28'
  effective_dt      DATE          NOT NULL,
  end_dt            DATE,
  PRIMARY KEY (dx_cd, hcc_nbr, model_version)
);

CREATE TABLE risk.hcc_hierarchy (
  hcc_nbr           SMALLINT      NOT NULL,
  trumped_by_hcc    SMALLINT      NOT NULL,
  model_version     VARCHAR(10)   NOT NULL,
  PRIMARY KEY (hcc_nbr, trumped_by_hcc, model_version)
);

Member-HCC Mapping (output of stage 1)

CREATE TABLE risk.member_hcc_mapping (
  mbr_id            VARCHAR(50)   NOT NULL,
  hcc_nbr           SMALLINT      NOT NULL,
  hcc_desc          VARCHAR(255)  NOT NULL,
  triggering_dx_cd  VARCHAR(7)    NOT NULL,
  svc_dt            DATE          NOT NULL,
  payment_year      INT           NOT NULL,
  model_version     VARCHAR(10)   NOT NULL,
  hierarchy_applied BOOLEAN       DEFAULT FALSE,
  dropped_by_hcc    SMALLINT,
  PRIMARY KEY (mbr_id, hcc_nbr, payment_year, model_version)
);

RAF Score Output

CREATE TABLE risk.member_raf_score (
  mbr_id              VARCHAR(50)   NOT NULL,
  payment_year        INT           NOT NULL,
  model_version       VARCHAR(10)   NOT NULL,
  demo_raf            NUMBER(6,4)   NOT NULL,
  disease_raf         NUMBER(6,4)   NOT NULL,
  total_raf           NUMBER(6,4)   NOT NULL,
  hcc_count           SMALLINT      NOT NULL,
  calculated_dt       TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
  PRIMARY KEY (mbr_id, payment_year, model_version)
);

---

RAF Score Calculation Logic

The demographic RAF component is a lookup against the CMS coefficient table for age band, sex, and enrollment type. The disease component is the sum of HCC coefficients for all HCCs attributed to the member after hierarchy is applied.

-- Step 1: Map diagnoses to HCCs
INSERT INTO risk.member_hcc_mapping
SELECT DISTINCT
    d.mbr_id,
    x.hcc_nbr,
    x.hcc_desc,
    d.dx_cd                     AS triggering_dx_cd,
    MIN(d.svc_dt) OVER (PARTITION BY d.mbr_id, x.hcc_nbr) AS svc_dt,
    d.payment_year,
    x.model_version,
    FALSE                       AS hierarchy_applied,
    NULL                        AS dropped_by_hcc
FROM risk.dx_claims_input d
JOIN risk.icd_to_hcc_xwalk x
    ON  d.dx_cd         = x.dx_cd
    AND d.payment_year  BETWEEN YEAR(x.effective_dt) AND COALESCE(YEAR(x.end_dt), 9999)
    AND x.model_version = 'V28';

-- Step 2: Apply hierarchy — mark lower-severity HCCs as dropped
UPDATE risk.member_hcc_mapping m
SET    hierarchy_applied = TRUE,
       dropped_by_hcc    = h.trumped_by_hcc
FROM   risk.hcc_hierarchy h
WHERE  m.hcc_nbr       = h.hcc_nbr
  AND  m.model_version = h.model_version
  AND  EXISTS (
         SELECT 1 FROM risk.member_hcc_mapping m2
         WHERE  m2.mbr_id        = m.mbr_id
           AND  m2.hcc_nbr       = h.trumped_by_hcc
           AND  m2.payment_year  = m.payment_year
           AND  m2.model_version = m.model_version
       );

-- Step 3: Compute final RAF scores
INSERT INTO risk.member_raf_score
SELECT
    m.mbr_id,
    m.payment_year,
    'V28'                          AS model_version,
    d.demo_raf,
    SUM(c.coefficient)             AS disease_raf,
    d.demo_raf + SUM(c.coefficient) AS total_raf,
    COUNT(DISTINCT m.hcc_nbr)      AS hcc_count,
    CURRENT_TIMESTAMP()
FROM risk.member_hcc_mapping m
JOIN risk.hcc_coefficients c
    ON  m.hcc_nbr      = c.hcc_nbr
    AND m.model_version= c.model_version
    AND m.payment_year = c.payment_year
JOIN risk.demo_raf_lookup d
    ON  m.mbr_id = d.mbr_id AND m.payment_year = d.payment_year
WHERE (m.hierarchy_applied = FALSE OR m.dropped_by_hcc IS NULL)
GROUP BY m.mbr_id, m.payment_year, d.demo_raf;

---

Critical Data Quality Checks

RADV audits scrutinize whether each HCC attributed to a member is supported by a valid diagnosis in a face-to-face encounter. Build these gates into your pipeline before any RAF score reaches the submission layer.

-- Flag diagnosis codes not in CMS reference file for the active model year
SELECT dx_cd, COUNT(*) AS occurrences
FROM risk.dx_claims_input
WHERE payment_year = 2025
  AND dx_cd NOT IN (
        SELECT DISTINCT dx_cd FROM risk.icd_to_hcc_xwalk
        WHERE model_version = 'V28'
      )
GROUP BY dx_cd
ORDER BY occurrences DESC;

-- Flag members whose RAF score dropped more than 10% year-over-year
SELECT
    c.mbr_id,
    p.total_raf  AS prior_year_raf,
    c.total_raf  AS current_year_raf,
    ROUND((c.total_raf - p.total_raf) / NULLIF(p.total_raf, 0) * 100, 1) AS pct_change
FROM risk.member_raf_score c
JOIN risk.member_raf_score p
    ON c.mbr_id = p.mbr_id AND c.payment_year = p.payment_year + 1
WHERE ABS((c.total_raf - p.total_raf) / NULLIF(p.total_raf, 0)) > 0.10
ORDER BY pct_change;

---

Frequently Asked Questions

What is the difference between HCC V24 and HCC V28?

CMS-HCC V28 restructured the condition hierarchy compared to V24, added new HCC groups for mental health and substance use disorders, and recalibrated all coefficients. Some ICD-10 codes that mapped to an HCC under V24 do not map to any HCC under V28, and vice versa. During the three-year transition (payment years 2024–2026), plans must maintain both crosswalk tables and blend V24 and V28 scores according to CMS phase-in ratios.

How is a RAF score calculated?

A member's RAF score has two components: a demographic component (based on age band, sex, Medicaid dual-eligibility status, and enrollment type) and a disease component (the sum of CMS-published coefficients for each HCC attributed to the member after hierarchy is applied). The total RAF score multiplies against a base rate to produce the per-member-per-month capitation payment.

What is HCC hierarchy and why does it matter for data pipelines?

The CMS-HCC model groups related conditions into hierarchies where a more severe condition "trumps" a less severe one in the same disease group. If a member has both diabetic retinopathy and uncomplicated diabetes, only the retinopathy HCC contributes to the RAF score. Your pipeline must apply hierarchy logic after mapping diagnoses to HCCs — counting both conditions overstates the RAF score and creates RADV audit exposure.

What encounter types are valid for HCC risk adjustment?

CMS accepts diagnoses from inpatient hospital stays, outpatient hospital encounters, and physician office visits — but only when billed by an acceptable provider type with a valid face-to-face encounter. Telephone visits, nurse-only visits, and certain ancillary provider types are not acceptable. Filter on encounter type code and rendering provider specialty before allowing a diagnosis into your HCC pipeline.

How do I prevent RADV audit findings in my data model?

The most common RADV findings come from four sources: diagnosis codes with no supporting documentation, HCCs attributed from unacceptable encounter types, incorrect ICD-to-HCC crosswalk versions, and hierarchy applied incorrectly. Build automated quality gates that validate encounter type, crosswalk version, and hierarchy application. Store the triggering claim and date of service for every HCC attributed to every member so medical record requests during audit can be fulfilled within CMS timelines.