DEV Community: Season Mudbhary

Stop Naming Your Healthcare Columns Wrong — ISO-11179 Explained

Season Mudbhary — Mon, 18 May 2026 03:15:23 +0000

If you've ever inherited a healthcare database with columns named DOB, PatientID, or CLAIM_NUMBER — this guide is for you.

Healthcare data engineering has a naming problem. Every team, every vendor, every health plan names their columns differently. A "member ID" becomes MemberID in one system, mem_id in another, PATIENT_KEY in a third, and mbr_identifier in a fourth.

When you try to join these systems — and you always have to join them — you spend more time figuring out what columns mean than actually building the pipeline.

There's a standard that solves this. Most healthcare data engineers have never heard of it.

What is ISO-11179?

ISO-11179 is an international standard for naming data elements. It was developed specifically to make data interoperable across systems — which is exactly what healthcare data engineering needs.

The core idea is simple: every column name should follow a predictable structure:

So instead of DOB you get mbr_birth_dt.
Instead of CLAIM_NUMBER you get clm_nbr.
Instead of PatientID you get pt_id.

Every name tells you exactly what it is, what entity it belongs to, and what type of value to expect — without opening a data dictionary.

You can look up any of these standard abbreviations in the mdatool healthcare data glossary — it has 100,000+ standardized healthcare terms with their ISO-11179 abbreviations.

The Rules

1. Lowercase snake_case only

-- Wrong
MemberBirthDate
MEMBER_BIRTH_DATE
memberBirthDate

-- Right
mbr_birth_dt

Snowflake, BigQuery, and Databricks all handle lowercase snake_case consistently. Mixed case causes headaches with case-sensitive databases and makes joins fragile.

2. Standard abbreviations not made-up acronyms

This is where most teams go wrong. They abbreviate inconsistently:

-- Same concept, 4 different names on 4 tables
dob
birth_dt
patient_birth_date
DOB_DT

ISO-11179 gives you a fixed vocabulary. Learn it once, use it everywhere. The mdatool naming auditor checks your column names against this standard automatically.

3. Standard suffixes tell you the data type

This is the most powerful part of the standard. The suffix tells you exactly what kind of value to expect:

Suffix	Meaning	Example
`_dt`	Date	`clm_pd_dt`
`_ts`	Timestamp	`load_ts`
`_cd`	Code	`diag_cd`
`_id`	Identifier	`mbr_id`
`_nbr`	Number	`clm_nbr`
`_amt`	Amount (money)	`clm_pd_amt`
`_nm`	Name	`prvdr_nm`
`_flg`	Boolean flag	`is_pd_flg`
`_cnt`	Count	`clm_line_cnt`
`_pct`	Percentage	`coin_pct`
`_txt`	Free text	`note_txt`
`_ind`	Indicator	`actv_ind`

When you see clm_pd_amt you immediately know:

clm = claim
pd = paid
amt = monetary amount (NUMBER type, probably NUMBER(12,2))

No documentation required.

Standard Prefixes by Healthcare Domain

Claims

clm_id          -- claim identifier
clm_nbr         -- claim number (business key)
clm_pd_amt      -- claim paid amount
clm_alwd_amt    -- claim allowed amount
clm_chrg_amt    -- claim charge amount
clm_ded_amt     -- claim deductible amount
clm_coins_amt   -- claim coinsurance amount
clm_pd_dt       -- claim paid date
clm_rcvd_dt     -- claim received date
clm_sts_cd      -- claim status code
clm_typ_cd      -- claim type code

Browse all claims data terms →

Member / Enrollment

mbr_id          -- member identifier
mbr_nbr         -- member number
mbr_first_nm    -- member first name
mbr_last_nm     -- member last name
mbr_birth_dt    -- member birth date
mbr_gndr_cd     -- member gender code
mbr_eff_dt      -- member effective date
mbr_term_dt     -- member termination date
subscr_id       -- subscriber identifier
subscr_grp_nbr  -- subscriber group number

Browse all member enrollment terms →

Provider

prvdr_id        -- provider identifier
prvdr_npi       -- provider NPI number
prvdr_nm        -- provider name
prvdr_first_nm  -- provider first name
prvdr_last_nm   -- provider last name
prvdr_tax_cd    -- provider taxonomy code
prvdr_eff_dt    -- provider effective date
prvdr_term_dt   -- provider termination date

Browse all provider data terms →

Clinical

pt_id           -- patient identifier
diag_cd         -- diagnosis code
proc_cd         -- procedure code
enc_id          -- encounter identifier
admn_dt         -- admission date
dsch_dt         -- discharge date
icd_ver_cd      -- ICD version code

Browse all clinical data terms →

Pharmacy

rx_id           -- prescription identifier
ndc_cd          -- NDC drug code
rx_fill_dt      -- prescription fill date
days_sup_cnt    -- days supply count
qty_disp_cnt    -- quantity dispensed

Browse all pharmacy data terms →

The Most Common Naming Mistakes

Here's what I see constantly in healthcare data warehouses — and the correct ISO-11179 equivalent:

Wrong	Right	Why
`DOB`	`mbr_birth_dt`	Ambiguous — whose DOB? What entity?
`PatientID`	`pt_id`	PascalCase, no domain prefix
`CLAIM_NUMBER`	`clm_nbr`	SCREAMING_SNAKE_CASE, wrong suffix
`MemberEffectiveDate`	`mbr_eff_dt`	PascalCase, too verbose
`paid_amount`	`clm_pd_amt`	Missing entity prefix
`npi`	`prvdr_npi`	No entity context — NPI of what?
`status`	`clm_sts_cd`	No entity, no suffix
`flag`	`is_pd_flg`	No entity, no context
`date1`	`clm_eff_dt`	Completely meaningless
`id`	`mbr_id`	No entity prefix

Not sure what the right name is for a column? The mdatool name generator generates ISO-11179 standard names from a plain English description.

A Real Example — Before and After

Here's a typical claims table I've encountered in the wild:

-- Before (nightmare to work with)
CREATE TABLE Claims (
  ID              INT,
  ClaimNum        VARCHAR(20),
  MemberID        VARCHAR(20),
  DOB             DATE,
  ProviderNPI     VARCHAR(10),
  DOS             DATE,
  BilledAmt       DECIMAL(12,2),
  PaidAmt         DECIMAL(12,2),
  AllowedAmt      DECIMAL(12,2),
  StatusCode      VARCHAR(3),
  PaidDate        DATE,
  ReceivedDate    DATE
);

And the same table following ISO-11179:

-- After (self-documenting)
CREATE TABLE fact_claim (
  clm_id          VARCHAR(20)      NOT NULL,
  clm_nbr         VARCHAR(20)      NOT NULL,
  mbr_id          VARCHAR(20)      NOT NULL,
  mbr_birth_dt    DATE,
  prvdr_npi       VARCHAR(10)      NOT NULL,
  svc_dt          DATE             NOT NULL,
  clm_chrg_amt    NUMBER(12,2),
  clm_pd_amt      NUMBER(12,2),
  clm_alwd_amt    NUMBER(12,2),
  clm_sts_cd      VARCHAR(3),
  clm_pd_dt       DATE,
  clm_rcvd_dt     DATE
);

The second version tells you everything you need to know without a single comment or data dictionary lookup. A new engineer joining your team can read this schema and immediately understand what each column represents.

Want to generate a schema like this automatically for any healthcare domain? Try the mdatool AI data modeling tool — it generates production-ready DDL with ISO-11179 column names for Snowflake, BigQuery, Databricks, and more.

Why This Matters Beyond Aesthetics

Consistent naming isn't just clean code — it has real business impact in healthcare:

1. Faster onboarding
New data engineers can understand your schema in hours instead of weeks. In healthcare IT where turnover is high this matters enormously.

2. HEDIS and CMS reporting accuracy
When your column names clearly indicate what a field contains you're less likely to join on the wrong field or use the wrong date in a quality measure denominator. Wrong HEDIS rates have real financial consequences.

3. Easier compliance audits
HIPAA auditors reviewing your data lineage want to understand what data flows where. A self-documenting schema makes this dramatically easier.

4. Multi-system integration
Healthcare data comes from Epic, Cerner, Availity, Change Healthcare, and dozens of other systems. When every team follows the same naming standard integration becomes mapping instead of archaeology.

How to Audit Your Existing Schema

If you have an existing schema that doesn't follow these conventions here's a SQL query to find the worst offenders:

-- Find columns that likely violate naming standards
SELECT 
  table_name,
  column_name,
  data_type,
  CASE
    WHEN column_name != LOWER(column_name) 
      THEN 'Has uppercase letters'
    WHEN column_name NOT LIKE '%\_%' 
      THEN 'No underscore separator'
    WHEN column_name IN (
      'id','date','name','status',
      'code','flag','amount','number'
    )
      THEN 'Too generic — no entity prefix'
    WHEN LENGTH(column_name) < 4 
      THEN 'Too short — likely an abbreviation'
    ELSE 'Check manually'
  END as issue
FROM information_schema.columns
WHERE table_schema = 'your_schema'
  AND (
    column_name != LOWER(column_name)
    OR column_name NOT LIKE '%\_%'
    OR LENGTH(column_name) < 4
  )
ORDER BY table_name, column_name;

Or skip the manual work and paste your DDL into the mdatool naming auditor — it checks against ISO-11179 healthcare standards automatically and flags every violation with a suggested fix.

Start Small

You don't have to rename everything at once. Start with:

All new tables follow ISO-11179 from day one
Any table you're modifying — rename columns in that PR
Create views with standard names over legacy tables

Six months from now your new engineers will thank you. Your HEDIS submissions will be cleaner. Your CMS audits will be smoother.

And you'll never spend 20 minutes figuring out whether DOS means "Date of Service" or "Denial of Service" again.

Resources mentioned in this article:

mdatool Healthcare Data Glossary — 100,000+ terms with ISO-11179 abbreviations
Naming Auditor — audit your schema against healthcare standards
Name Generator — generate standard column names from plain English
AI Data Modeling — generate production-ready DDL with standard naming
DDL Converter — convert schemas between Snowflake, BigQuery, Databricks

mdatool is a free platform for healthcare data engineers. No account required for most tools.

NPI Number Validation: How to Clean and Enrich Provider Data in Your Database

Season Mudbhary — Fri, 01 May 2026 15:05:26 +0000

A claims load that accepts an invalid NPI does not fail loudly.

It loads successfully, the claim routes to a provider that does not exist, payment fails downstream, and the root cause takes days to find. In a denial management workflow, invalid rendering provider NPIs are one of the top five reasons for payer rejections — and most of them could be caught at ingestion.

The National Provider Identifier (NPI) is a 10-digit number assigned to every covered healthcare provider in the US. It is required on all HIPAA-standard transactions — 837 claims, 270/271 eligibility inquiries, 278 prior authorization requests. Your provider data is only as reliable as the NPI validation layer in front of it.

This guide covers NPI validation approaches, NPPES enrichment patterns, and the SQL queries that catch provider data quality problems before they surface in claims adjudication.

What Makes an NPI Valid

An NPI is a 10-digit number that passes the Luhn checksum algorithm. The structure is:

Digits 1–9: assigned sequentially by CMS
Digit 10: check digit, computed using Luhn's algorithm with a prefix of 80840

An NPI can be syntactically valid (passes Luhn, correct format) but logically invalid (deactivated, reassigned, or wrong entity type for the context). Both layers of validation are necessary.

Syntactic Validation

Start with a basic format check — 10 digits, numeric only:

SELECT
    npi_id,
    CASE
        WHEN NOT REGEXP_LIKE(npi_id, '^[0-9]{10}$') THEN 'INVALID_FORMAT'
        ELSE 'FORMAT_OK'
    END AS format_status
FROM dim_provider
WHERE NOT REGEXP_LIKE(npi_id, '^[0-9]{10}$');

The Luhn check for NPI uses a prefix of 80840 prepended before computing the checksum. The full 15-digit number 80840 + [9 digits] + [check digit] must produce a valid Luhn result.

In PostgreSQL, implement the full checksum check as a reusable function:

CREATE OR REPLACE FUNCTION validate_npi(npi TEXT) RETURNS BOOLEAN AS $$
DECLARE
    full_number TEXT := '80840' || npi;
    total       INT  := 0;
    digit       INT;
    i           INT;
    len         INT;
BEGIN
    IF npi !~ '^[0-9]{10}$' THEN RETURN FALSE; END IF;
    len := LENGTH(full_number);
    FOR i IN 1..len LOOP
        digit := SUBSTRING(full_number, i, 1)::INT;
        IF MOD(len - i, 2) = 1 THEN
            digit := digit * 2;
            IF digit > 9 THEN digit := digit - 9; END IF;
        END IF;
        total := total + digit;
    END LOOP;
    RETURN MOD(total, 10) = 0;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Find all invalid NPIs in your provider dimension
SELECT npi_id, provider_last_name
FROM dim_provider
WHERE NOT validate_npi(npi_id);

Logical Validation Against NPPES

Syntactic validation catches format errors. Logical validation requires checking the NPI against the NPPES (National Plan and Provider Enumeration System) database.

CMS publishes the full NPPES data dissemination file monthly — a flat file containing all active NPI records. Loading this into your warehouse enables offline NPI validation and provider enrichment without API rate limits.

NPPES Reference Schema

CREATE TABLE ref_nppes_providers (
    npi_id                      CHAR(10)        NOT NULL,
    entity_type_code            CHAR(1)         NOT NULL,   -- '1'=Individual, '2'=Organization
    provider_last_name          VARCHAR(100),
    provider_first_name         VARCHAR(100),
    provider_organization_name  VARCHAR(300),
    provider_taxonomy_code_1    VARCHAR(10),
    npi_deactivation_date       DATE,
    npi_reactivation_date       DATE,
    provider_enumeration_date   DATE            NOT NULL,
    last_update_date            DATE            NOT NULL,

    CONSTRAINT pk_nppes PRIMARY KEY (npi_id)
);

CREATE INDEX idx_nppes_last_name ON ref_nppes_providers (provider_last_name);
CREATE INDEX idx_nppes_taxonomy  ON ref_nppes_providers (provider_taxonomy_code_1);

Checking for Deactivated NPIs

SELECT
    p.provider_key,
    p.npi_id,
    p.provider_last_name,
    n.npi_deactivation_date,
    n.npi_reactivation_date,
    CASE
        WHEN n.npi_id IS NULL                                    THEN 'NOT_IN_NPPES'
        WHEN n.npi_deactivation_date IS NOT NULL
         AND n.npi_reactivation_date IS NULL                     THEN 'DEACTIVATED'
        WHEN n.npi_deactivation_date IS NOT NULL
         AND n.npi_reactivation_date < CURRENT_DATE              THEN 'DEACTIVATED'
        ELSE 'ACTIVE'
    END AS npi_status
FROM dim_provider p
LEFT JOIN ref_nppes_providers n USING (npi_id)
WHERE npi_status != 'ACTIVE';

⚠️ Run this check before every claims load cycle — not just during initial data onboarding. NPIs get deactivated mid-month and your reference table needs to reflect that.

Provider Data Enrichment Patterns

The NPPES file contains more than validation data. It is a complete provider directory — taxonomy codes, practice addresses, organizational affiliations, and enumeration dates. Use it to enrich your provider dimension.

Taxonomy Code Enrichment

Provider taxonomy codes identify provider specialty. They are essential for network adequacy analysis, prior authorization routing, and quality measure attribution.

SELECT
    p.provider_key,
    p.npi_id,
    n.provider_taxonomy_code_1,
    t.taxonomy_description,
    t.taxonomy_grouping,
    t.taxonomy_classification,
    t.taxonomy_specialization
FROM dim_provider p
JOIN ref_nppes_providers n USING (npi_id)
LEFT JOIN ref_provider_taxonomy t
    ON n.provider_taxonomy_code_1 = t.taxonomy_code;

Entity Type Validation in Claims Context

NPIs are issued to individuals (Type 1) and organizations (Type 2). A rendering provider NPI on a professional claim must be Type 1. Validate entity type in the claims context:

-- Flag professional claims where rendering provider is an organization NPI
SELECT
    c.claim_id,
    c.rendering_npi_id,
    n.entity_type_code,
    n.provider_organization_name
FROM fct_claims c
JOIN ref_nppes_providers n
    ON c.rendering_npi_id = n.npi_id
WHERE c.claim_type_code = '1'   -- professional claim
  AND n.entity_type_code = '2'; -- organization NPI on rendering provider

This is a common data quality issue in practices that accidentally use their billing NPI (Type 2) as the rendering NPI (must be Type 1) on professional claims.

Handling NPI Updates in Your Provider Dimension

NPIs can be deactivated, reassigned, or have their associated data updated. Your provider dimension needs a strategy for handling these changes every month.

Monthly NPPES Refresh Pattern

-- Identify what changed between current NPPES and your reference table
SELECT
    s.npi_id,
    CASE
        WHEN p.npi_id IS NULL                           THEN 'NEW'
        WHEN s.last_update_date > p.last_update_date    THEN 'UPDATED'
        WHEN s.npi_deactivation_date IS NOT NULL
         AND p.npi_deactivation_date IS NULL            THEN 'DEACTIVATED'
        ELSE 'UNCHANGED'
    END AS change_type
FROM stg_nppes_current s
LEFT JOIN ref_nppes_providers p USING (npi_id);

-- Apply changes via MERGE
MERGE INTO ref_nppes_providers AS target
USING stg_nppes_current AS source
    ON target.npi_id = source.npi_id
WHEN MATCHED AND source.last_update_date > target.last_update_date THEN
    UPDATE SET
        provider_taxonomy_code_1   = source.provider_taxonomy_code_1,
        npi_deactivation_date      = source.npi_deactivation_date,
        npi_reactivation_date      = source.npi_reactivation_date,
        last_update_date           = source.last_update_date
WHEN NOT MATCHED THEN
    INSERT (
        npi_id, entity_type_code, provider_last_name,
        provider_first_name, provider_organization_name,
        provider_taxonomy_code_1, npi_deactivation_date,
        provider_enumeration_date, last_update_date
    )
    VALUES (
        source.npi_id, source.entity_type_code, source.provider_last_name,
        source.provider_first_name, source.provider_organization_name,
        source.provider_taxonomy_code_1, source.npi_deactivation_date,
        source.provider_enumeration_date, source.last_update_date
    );

Building an NPI Data Quality Dashboard

Track NPI data quality as an operational metric, not a one-time audit. Add this query to your weekly data quality pipeline:

SELECT
    COUNT(*)                                            AS total_providers,
    COUNT(CASE WHEN NOT REGEXP_LIKE(npi_id, '^[0-9]{10}$') 
               THEN 1 END)                             AS invalid_format_count,
    COUNT(CASE WHEN n.npi_id IS NULL 
               THEN 1 END)                             AS not_in_nppes_count,
    COUNT(CASE WHEN n.npi_deactivation_date IS NOT NULL
                AND n.npi_reactivation_date IS NULL 
               THEN 1 END)                             AS deactivated_count,
    ROUND(
        100.0 * COUNT(
            CASE WHEN n.npi_id IS NOT NULL
                  AND n.npi_deactivation_date IS NULL 
                 THEN 1 END
        ) / NULLIF(COUNT(*), 0), 2
    )                                                  AS valid_active_pct
FROM dim_provider p
LEFT JOIN ref_nppes_providers n USING (npi_id);

A drop in valid_active_pct signals a provider roster import that bypassed NPI validation — catch it before it reaches claims adjudication.

3 NPI Mistakes That Break Claims Pipelines

1. Not deactivating providers when CMS deactivates their NPI

Historical claims remain valid. But stop routing new claims to deactivated NPIs — flag those provider records and alert your credentialing team immediately.

2. Assuming two providers can share an NPI

They cannot. NPIs are unique to a single entity. If you find two provider records with the same NPI, you have a data entry error — likely the same provider entered twice under different names. Deduplicate before it reaches downstream systems.

3. Using organization NPI as rendering provider on professional claims

The rendering provider NPI on an 837P professional claim must be a Type 1 individual NPI. Using a Type 2 organization NPI causes CMS and payer rejections. Add the entity type validation query above to your claims pre-submission checks.

FAQ

Does every provider need an NPI?

Every covered healthcare provider — individual or organization — that transmits HIPAA standard transactions must have an NPI. Providers who only bill Medicaid in certain states may have state-specific identifiers but still require an NPI for federal programs.

What happens when an NPI is deactivated?

CMS deactivates NPIs when a provider retires, changes entity type, or is excluded from Medicare. A deactivated NPI is no longer valid on new claims. Historical claims associated with a deactivated NPI remain valid — do not delete those records. Flag the provider record and stop routing new claims to that NPI.

Can two providers share an NPI?

No. NPIs are unique to a single entity. If you see two provider records with the same NPI in your dimension, you have a data quality problem — likely a data entry error or a provider entered twice under different names.

Free Tools for NPI Validation

If you're managing provider data at scale, mdatool provides free tooling for every stage of NPI quality:

🔍 NPI Lookup — validate any NPI in real time against the NPPES registry, returning entity type, taxonomy, name, and active status without building a local pipeline
🔧 SQL Linter — catch bugs in your NPI validation SQL before production
📋 Naming Auditor — ensure your NPI columns follow consistent naming conventions (npi_id, rendering_npi_id, billing_npi_id) so masking policies and data quality rules apply reliably
📖 Healthcare Data Dictionary — 100,000+ verified definitions including NPI, NPPES, taxonomy code, and related provider data terminology

Free to start. No credit card required.

Originally published at mdatool.com

HCC Risk Adjustment Data Model: Building Accurate Risk Score Pipelines in SQL

Season Mudbhary — Fri, 01 May 2026 14:53:40 +0000

For a Medicare Advantage plan with 100,000 members, a 0.01 difference in average RAF score translates to roughly $4 million in annual revenue.

A modeling error that drops 3% of valid diagnosis codes does not produce an error. The pipeline runs clean, the submission goes out, and the plan receives significantly less than it should. You find out twelve months later during reconciliation — if at all.

This guide covers the CMS-HCC data model, the pipeline architecture that produces accurate RAF scores, the SQL patterns for each stage, and the common mistakes that silently undercount risk.

How CMS-HCC Risk Adjustment Works

CMS risk-adjusts Medicare Advantage capitation payments to account for member health status:

CMS assigns a base rate (benchmark) for each county
Each member's RAF (Risk Adjustment Factor) score is calculated from demographics and diagnosis history
The plan's capitation payment = benchmark × average RAF score across members
A RAF score of 1.0 means average expected cost. 1.5 means 50% above average.

The RAF score has two components:

Demographic score — based on age, sex, dual eligibility status, and Medicaid eligibility. Computed from enrollment data.

Disease score — based on HCC codes mapped from submitted diagnosis codes. Computed from encounter and claims data.

The CMS-HCC model (Version 28 as of 2024) maps roughly 10,000 ICD-10-CM diagnosis codes into 115 HCC categories. Each HCC has a coefficient that contributes to the RAF score. HCCs interact — comorbidity adjustments apply when certain HCC pairs occur together.

The Core Data Model

The HCC risk adjustment pipeline requires four key tables.

Diagnosis Encounter Staging

CREATE TABLE stg_risk_adjustment_encounters (
    encounter_key           BIGINT          NOT NULL GENERATED ALWAYS AS IDENTITY,
    member_id               VARCHAR(50)     NOT NULL,
    rendering_npi_id        CHAR(10)        NOT NULL,
    service_date            DATE            NOT NULL,
    face_to_face_flag       BOOLEAN         NOT NULL DEFAULT FALSE,
    diagnosis_code          VARCHAR(10)     NOT NULL,
    diagnosis_code_type     CHAR(5)         NOT NULL DEFAULT 'ICD10',
    data_source             VARCHAR(50)     NOT NULL,  -- 'CLAIMS','ENCOUNTER','CHART'
    submission_year         SMALLINT        NOT NULL,
    loaded_datetime         TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT pk_stg_ra_encounters PRIMARY KEY (encounter_key)
);

CREATE INDEX idx_stg_ra_member_year 
    ON stg_risk_adjustment_encounters (member_id, submission_year);
CREATE INDEX idx_stg_ra_diagnosis   
    ON stg_risk_adjustment_encounters (diagnosis_code);

CMS-HCC Mapping Reference and Coefficients

-- Loaded from CMS software release each year
CREATE TABLE ref_icd10_to_hcc (
    icd10_code      VARCHAR(10)  NOT NULL,
    hcc_number      SMALLINT     NOT NULL,
    hcc_label       VARCHAR(200) NOT NULL,
    model_version   VARCHAR(10)  NOT NULL,  -- 'V28', 'V24'
    effective_year  SMALLINT     NOT NULL,

    CONSTRAINT pk_icd10_hcc 
        PRIMARY KEY (icd10_code, hcc_number, model_version, effective_year)
);

-- CMS model coefficients by member segment
CREATE TABLE ref_hcc_coefficients (
    model_version   VARCHAR(10)     NOT NULL,
    payment_year    SMALLINT        NOT NULL,
    hcc_number      SMALLINT        NOT NULL,
    segment         VARCHAR(50)     NOT NULL,  -- 'CNA','ESRD','NE','SNPNE'
    coefficient     DECIMAL(10, 6)  NOT NULL,

    CONSTRAINT pk_hcc_coefficients 
        PRIMARY KEY (model_version, payment_year, hcc_number, segment)
);

Member HCC Summary and RAF Score Output

-- One row per member per HCC per year
CREATE TABLE fct_member_hcc (
    member_hcc_key      BIGINT      NOT NULL GENERATED ALWAYS AS IDENTITY,
    member_id           VARCHAR(50) NOT NULL,
    hcc_number          SMALLINT    NOT NULL,
    submission_year     SMALLINT    NOT NULL,
    encounter_count     SMALLINT    NOT NULL DEFAULT 1,
    first_service_date  DATE        NOT NULL,
    last_service_date   DATE        NOT NULL,

    CONSTRAINT pk_member_hcc PRIMARY KEY (member_hcc_key),
    CONSTRAINT uq_member_hcc_year UNIQUE (member_id, hcc_number, submission_year)
);

-- Final RAF score output table
CREATE TABLE fct_member_raf_score (
    member_id           VARCHAR(50)     NOT NULL,
    payment_year        SMALLINT        NOT NULL,
    segment             VARCHAR(50)     NOT NULL,
    demographic_score   DECIMAL(10, 6)  NOT NULL,
    disease_score       DECIMAL(10, 6)  NOT NULL,
    interaction_score   DECIMAL(10, 6)  NOT NULL DEFAULT 0,
    total_raf_score     DECIMAL(10, 6)  NOT NULL,
    hcc_count           SMALLINT        NOT NULL,
    calculated_datetime TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT pk_member_raf PRIMARY KEY (member_id, payment_year, segment)
);

Stage 1: Diagnosis Code Ingestion and Validation

The first pipeline stage ingests diagnosis codes from multiple sources — claims (837P/837I), encounter records, and chart reviews — and applies validation rules.

⚠️ The face_to_face_flag filter is critical. CMS only accepts diagnosis codes from face-to-face encounters with eligible provider types. Missing this filter is the #1 cause of CMS submission rejections.

-- Load validated encounters only
INSERT INTO stg_risk_adjustment_encounters (
    member_id, rendering_npi_id, service_date, face_to_face_flag,
    diagnosis_code, data_source, submission_year
)
SELECT
    s.member_id,
    s.rendering_npi_id,
    s.service_date,
    s.face_to_face_flag,
    s.diagnosis_code,
    s.data_source,
    EXTRACT(YEAR FROM s.service_date)::SMALLINT
FROM stg_raw_encounters s
INNER JOIN ref_icd10_codes r
    ON s.diagnosis_code = r.icd10_code
   AND r.effective_year = EXTRACT(YEAR FROM s.service_date)::SMALLINT
WHERE s.face_to_face_flag = TRUE
  AND s.service_date >= DATE_TRUNC('year', CURRENT_DATE - INTERVAL '2 years');

Always run this audit query after loading — it tells you how many codes were silently rejected:

SELECT
    s.diagnosis_code,
    COUNT(*) AS rejected_count
FROM stg_raw_encounters s
LEFT JOIN ref_icd10_codes r
    ON s.diagnosis_code = r.icd10_code
   AND r.effective_year = EXTRACT(YEAR FROM s.service_date)::SMALLINT
WHERE r.icd10_code IS NULL
GROUP BY s.diagnosis_code
ORDER BY rejected_count DESC;

Stage 2: ICD-10 to HCC Mapping

Map valid diagnosis codes to HCC categories using the CMS crosswalk. A member gets credit for an HCC once — regardless of how many times the code appears across encounters.

INSERT INTO fct_member_hcc (
    member_id, hcc_number, submission_year,
    encounter_count, first_service_date, last_service_date
)
SELECT
    e.member_id,
    h.hcc_number,
    e.submission_year,
    COUNT(DISTINCT e.service_date) AS encounter_count,
    MIN(e.service_date)            AS first_service_date,
    MAX(e.service_date)            AS last_service_date
FROM stg_risk_adjustment_encounters e
JOIN ref_icd10_to_hcc h
    ON e.diagnosis_code = h.icd10_code
   AND h.model_version = 'V28'
   AND h.effective_year = e.submission_year
GROUP BY e.member_id, h.hcc_number, e.submission_year
ON CONFLICT (member_id, hcc_number, submission_year)
DO UPDATE SET
    encounter_count   = EXCLUDED.encounter_count,
    last_service_date = EXCLUDED.last_service_date;

Stage 3: HCC Hierarchy Application

The CMS-HCC model uses hierarchies — when a more severe HCC is present, less severe HCCs in the same category are suppressed.

Example: HCC 19 (Diabetes without Complication) is suppressed if HCC 17 or HCC 18 is present.

Skipping this step causes your internal RAF projections to overcount — and your numbers will never match CMS's calculation.

-- Hierarchy suppression reference table
CREATE TABLE ref_hcc_hierarchy (
    model_version   VARCHAR(10) NOT NULL,
    higher_hcc      SMALLINT    NOT NULL,  -- if present...
    lower_hcc       SMALLINT    NOT NULL,  -- ...suppress this one
    CONSTRAINT pk_hcc_hierarchy 
        PRIMARY KEY (model_version, higher_hcc, lower_hcc)
);

-- View that applies hierarchy suppression per member
CREATE OR REPLACE VIEW vw_member_hcc_after_hierarchy AS
SELECT
    m.*,
    EXISTS (
        SELECT 1
        FROM ref_hcc_hierarchy h
        JOIN fct_member_hcc m2
            ON h.higher_hcc = m2.hcc_number
           AND m2.member_id = m.member_id
           AND m2.submission_year = m.submission_year
        WHERE h.lower_hcc = m.hcc_number
          AND h.model_version = 'V28'
    ) AS is_suppressed_flag
FROM fct_member_hcc m;

Stage 4: RAF Score Calculation

Sum coefficients for non-suppressed HCCs to get the disease score, then add the demographic score to produce the final RAF:

WITH disease_scores AS (
    SELECT
        m.member_id,
        m.submission_year,
        SUM(c.coefficient) AS disease_score,
        COUNT(*)           AS hcc_count
    FROM vw_member_hcc_after_hierarchy m
    JOIN ref_hcc_coefficients c
        ON m.hcc_number = c.hcc_number
       AND c.model_version = 'V28'
       AND c.payment_year = m.submission_year + 1
       AND c.segment = 'CNA'
    WHERE m.is_suppressed_flag = FALSE
    GROUP BY m.member_id, m.submission_year
)
INSERT INTO fct_member_raf_score (
    member_id, payment_year, segment,
    demographic_score, disease_score,
    interaction_score, total_raf_score, hcc_count
)
SELECT
    d.member_id,
    d.submission_year + 1               AS payment_year,
    'CNA'                               AS segment,
    dem.total_demographic_score         AS demographic_score,
    d.disease_score,
    0                                   AS interaction_score,
    dem.total_demographic_score 
        + d.disease_score               AS total_raf_score,
    d.hcc_count
FROM disease_scores d
JOIN fct_member_demographic_score dem
    ON d.member_id = dem.member_id
   AND dem.payment_year = d.submission_year + 1
ON CONFLICT (member_id, payment_year, segment)
DO UPDATE SET
    disease_score       = EXCLUDED.disease_score,
    total_raf_score     = EXCLUDED.total_raf_score,
    hcc_count           = EXCLUDED.hcc_count,
    calculated_datetime = CURRENT_TIMESTAMP;

5 Mistakes That Silently Undercount Risk

1. Not filtering for face-to-face encounters

Lab-only visits, ancillary services, and telehealth from non-eligible providers do not count. A pipeline that ingests all encounters without this filter loads codes CMS will reject at submission.

2. Using claim received dates instead of service dates

Late claims arrive in the following year. A service date of December 2025 on a claim loaded in February 2026 should count toward the 2025 submission year. Always use service_date, never claim_received_date.

3. Missing the hierarchy application step

If your pipeline sums coefficients for all HCCs without hierarchy suppression, you will overcount RAF scores internally — and your numbers will not match CMS.

4. Not deduplicating across data sources

The same encounter may appear in claims, an encounter feed, and a chart review extract. Without deduplication at the member-HCC-year level, you count the same HCC multiple times while CMS counts it once.

5. Dropping diagnosis codes with invalid NPI

Validate NPIs separately — do not drop the diagnosis code because the provider record has a formatting issue. You lose legitimate risk score contributions from those encounters.

FAQ

How far back does CMS look at diagnosis codes?

For the standard prospective model, CMS uses diagnoses from the prior calendar year. Diagnosis codes from 2025 determine risk scores for payment year 2026.

What is the difference between RAPS and EDPS submissions?

RAPS (legacy) accepts diagnosis codes extracted from claims. EDPS (current standard) accepts full encounter records — demographics, procedure codes, diagnosis codes, and provider information. Most plans submit via both pathways.

How do we handle mid-year member additions?

CMS applies a partial-year factor for members who join mid-year. Your fct_member_raf_score table should store both the full RAF score and the enrollment months count used by CMS for the prorated calculation.

Free Tools for HCC Risk Adjustment Pipelines

If you're building or validating an HCC risk adjustment pipeline, mdatool provides free tooling at every stage:

🧮 HCC Calculator — verify individual member RAF scores against CMS-HCC V28 coefficients without running a full pipeline
🔍 ICD-10 Search — look up codes and confirm HCC mapping before they enter your submission pipeline
🔧 SQL Linter — catch bugs in your risk adjustment SQL before production
📖 Healthcare Data Dictionary — 100,000+ verified definitions including HCC, RAF, RAPS, EDPS, and RADV terminology

Free to start. No credit card required.

Originally published at mdatool.com