DEV Community: Season Mudbhary

Defeating Schema Chaos: Automated Metadata & PHI/PII Flags for Healthcare Data Teams

Season Mudbhary — Sat, 11 Jul 2026 02:55:27 +0000

As healthcare data architects, modelers, and engineers, we face an intense dual challenge: moving fast enough to support business analytics while keeping data architectures strictly locked down under HIPAA compliance.

The traditional way of dealing with this is painful. An engineer updates a table, someone forgets to update a static Excel sheet, and suddenly a column like patient_alt_id or home_phone is pushed to a production environment without proper masking or tokenization.

Every hour spent manually chasing down missing table descriptions, writing dbt schemas from scratch, or hunting for hidden compliance landmines is a massive operational drain.

At mdatool, we build purpose-built engineering utilities to automate these exact headaches. If you are operating in Snowflake, BigQuery, Databricks, or dbt, here is how you can instantly scale your documentation and compliance workflow.

1. Stop Writing dbt YAML and Data Dictionaries by Hand

Documenting an enterprise-grade schema shouldn't feel like a punishment. If your team is stuck manually typing descriptions for every newly deployed physical column, documentation is going to slip.

Instead of starting from a blank page, you can streamline the process entirely using a Metadata Generator. By pasting your raw DDL scripts straight into the utility, you instantly generate a comprehensive, structured data dictionary, a business glossary, and fully configured schema.yml configurations for dbt.

This lets your data architects focus on schema design while your engineers instantly copy-paste deployment-ready YAML files directly into their code repositories.

2. Automate PHI/PII Classification Before Production

Inadvertently exposing Protected Health Information (PHI) or Personally Identifiable Information (PII) is the biggest risk factor for any healthcare platform. If a pipeline ingests unstructured or vaguely named fields, a compliance leak is only one bad query away.

Data teams cannot afford to rely on guesswork or manual code reviews to catch sensitive variables. Security belongs inside the automated pipeline lifecycle:

Instant Tagging: Automated systems parse your database properties and immediately isolate fields containing patient names, geographic codes, specific dates, or medical identification strings.
Proactive Security: By identifying data exposure variables at the design phase rather than inside production logs, security teams can proactively apply masking, hashing, or structural access controls.

# Automated Metadata & Security Scans Turn Bloated Data into Compliance-Ready Assets
version: 2

models:
  - name: stg_patient_enrollment
    description: "Standardized staging layer tracking member longitudinal records."
    columns:
      - name: member_id
        description: "Unique enterprise identifier for the insured patient."
        data_tests:
          - unique
          - not_null
      - name: date_of_birth
        description: "Patient birth date identifier."
        meta:
          phi_classification: "PHI"
          security_tier: "restricted"
          masking_policy: "sha256_hash"

3. Standardize Schema Language (ISO-11179)

Inconsistent, chaotic naming conventions make metadata and privacy auditing nearly impossible. If one table labels an identifier as mbr_id and another uses SubscriberNumber, global classification rules fail.

To ensure your automated metadata scanners accurately track sensitive entities, enforce strict architectural standards:

Bulk Sanitizer: Clean messy, legacy source attributes in bulk to align instantly with ISO-11179 convention parameters.
Naming Auditor: Programmatically check incoming column layouts against enterprise standards before running migration scripts.
Name Generator: Create structured, compliant column text strings automatically from plain business terms to ensure schema uniformness.

4. Offload Healthcare Parsing & Modeling Complexities

Scaling your metadata landscape also requires understanding domain-specific files without hard-coding fragile scripts.

HL7 v2 Message Parsing: Don't waste engineering time creating messy arrays to unpack clinical documents. Use a specialized HL7 v2 Parser to isolate, split, and decode raw ADT, ORU, or ORM segments into highly structured, documented layouts.
Schema Evolution Tracker: Use Schema Diff to side-by-side evaluate your code changes during platform migrations, immediately identifying any newly added columns or modified data types that might require a fresh security classification check.

Build a Secure, Predictable Data Architecture

Robust healthcare data engineering is built on eliminating the gap between documentation, architecture code, and security compliance.

If you want to spend less time manually mapping PII risks and typing out endless data definitions, leverage the free workflow utilities available on the mdatool Engineering Platform tools.

How does your data team manage PHI/PII identification and dbt schema generation across development pipelines? Share your documentation scripts or workflow tips in the comments below!

Stop the Schema Chaos: A Workflow Guide for Healthcare Data Architects

Season Mudbhary — Sat, 11 Jul 2026 02:50:59 +0000

As healthcare data architects, modelers, and engineers, we all share the same nightmare: a massive, undocumented schema dump with zero consistency.

Is Prov_ID an NPI, a legacy internal key, or a taxonomy string? It depends on which business unit built the pipeline five years ago.

Every hour spent hunting down metadata definitions or manually rewriting DDL between Snowflake, BigQuery, and SQL Server is an hour stolen from building scalable infrastructure.

At mdatool, we believe healthcare data governance shouldn't live in passive spreadsheets. It belongs directly inside your technical workflow. Here is how to eliminate the chaos and build a highly standardized, deployment-ready data architecture.

1. Decentralize the Dictionary, Centralize the Source

Managing healthcare metadata in a static Excel sheet or an isolated wiki page is a recipe for drift. The moment a pipeline updates, the documentation dies.

Data modelers need an instant, searchable library directly embedded in the development loop. When mapping clinical and financial datasets, teams require real-time access to standard definitions for industry concepts like:

Identifiers: NPI, Tax ID, MPI
Coding Systems: ICD-10-CM, CPT, HCPCS, LOINC, RxNorm
Domain Contexts: Claims, enrollment, pharmacy, and electronic health records (EHR)

Having a unified source of truth ensures that an analytical feature built by an engineer perfectly matches the logical intent of the architect.

2. Enforce Programmatic Naming Conventions

Inconsistent naming conventions are the silent killer of clean analytics. If one pipeline uses clm_pmt_amt and another uses ClaimAmountPaid, cross-domain querying becomes a massive headache.

Instead of relying on manual code reviews, architects should automate naming compliance. By running raw, bulk column lists against a centralized glossary, you can instantly audit attributes for compliance.

-- The Chaos: Out-of-sync legacy schemas
CREATE TABLE raw_claims (
    Clm_ID VARCHAR,
    provNum INT,
    DOS_start DATE,
    AmtPaid NUMERIC
);

-- The Goal: Standardized, deterministic architectures
CREATE TABLE stg_claims (
    claim_id VARCHAR,
    billing_provider_npi INT,
    claim_service_start_date DATE,
    claim_paid_amount NUMERIC(18,2)
);

Standardizing your attributes prior to generating DDL ensures deterministic schemas across your entire modern data stack.

3. Automate Multi-Dialect DDL Migrations

In modern data architectures, you are rarely locked into a single ecosystem. You might be modeling in a local environment, staging in SQL Server, and running massive analytical workloads in Snowflake or BigQuery.

Manually refactoring DDL data types, constraint syntax, and formatting across platforms is a poor use of an engineer's time. Data teams need the ability to instantly lint SQL for security flaws or performance bottlenecks while seamlessly translating schema code across dialects:

Snowflake ↔ BigQuery
SQL Server ↔ PostgreSQL
Oracle ↔ Databricks

4. Accelerate Modeling with Domain-Aware AI

Building logical Entity-Relationship Diagrams (ERDs) and physical DDL from scratch takes weeks of design reviews.

By leveraging generative engines specifically trained on healthcare data standards (like HIPAA, HL7, and OMOP), architects can bootstrap schemas instantly. Generating validated DDL scripts and logical models in seconds allows your engineering team to skip the tedious boilerplate and jump straight to ingestion.

Build a Predictable Healthcare Data Stack

Scalable data engineering relies on removing friction between the architectural blueprint and the deployed code.

If you want to spend less time researching standard naming conventions and manually converting SQL scripts, check out the mdatool Platform to see how we are optimizing the workflow for healthcare data teams.

How does your team handle naming conventions and DDL migrations across different data platforms? Drop your workflow tips or horror stories in the comments below!

Epic Clarity Data Model Guide for Healthcare Data Engineers

Season Mudbhary — Sat, 11 Jul 2026 02:20:37 +0000

Epic is installed at approximately 38% of US hospitals. If you work in healthcare data engineering long enough, you will encounter Epic Clarity. This guide covers everything you need to work with it effectively.

📖 Full interactive guide with tools and glossary links: mdatool.com/guides/epic-clarity

What Is Epic Clarity?

Epic Clarity is the relational reporting database within the Epic EHR ecosystem, containing a read-optimized copy of clinical, administrative, and operational data extracted nightly from Epic Chronicles — the underlying operational store.

Unlike Chronicles, which uses a proprietary columnar format requiring Epic-specific tooling, Clarity runs on Microsoft SQL Server with a standard relational schema queryable by any SQL-fluent data engineer. This makes Clarity the primary data extraction point for health system analytics teams building data warehouses on Snowflake, Databricks, and BigQuery.

Chronicles vs Clarity vs Cogito vs Caboodle

Understanding the four layers of the Epic data architecture is essential before building any pipeline or report against Epic data.

Layer	Type	SQL Queryable	Notes
Chronicles	Operational DB	❌ No	Proprietary Master Files format — feeds Clarity via nightly ETL
Clarity	Reporting DB	✅ Yes	SQL Server — primary extraction target for data engineers
Cogito	Analytics platform	Via Clarity	SlicerDicer, Radar dashboards — built on top of Clarity
Caboodle	Enterprise DW	✅ Yes	Dimensional star schema — better for population health analytics

Key Epic Clarity Tables for Data Engineers

Clarity contains thousands of tables, but most data engineering work centers on a core set.

Clarity Table	Domain	Primary Key	DW Equivalent	Notes
`PAT_ENC`	Encounters	`PAT_ENC_CSN_ID`	FACT_ENCOUNTER	Central join point for most queries
`PAT_ENC_HSP`	Inpatient	`PAT_ENC_CSN_ID`	FACT_INPATIENT_VISIT	Admit date, discharge date, LOS
`PATIENT`	Demographics	`PAT_ID`	DIM_PATIENT	DOB, sex, race, address — contains PHI
`PAT_ENC_DX`	Diagnoses	`CSN_ID + LINE`	FACT_ENCOUNTER_DX	Join to CLARITY_EDG for descriptions
`CLARITY_EDG`	Diagnoses	`DX_ID`	DIM_ICD10	ICD-9 and ICD-10 codes and descriptions
`ORDER_PROC`	Orders	`ORDER_PROC_ID`	FACT_ORDER	Procedure and lab orders
`CLARITY_EAP`	Procedures	`PROC_ID`	DIM_PROCEDURE	CPT codes, procedure names
`ORDER_RESULTS`	Lab Results	`ORDER_ID + LINE`	FACT_LAB_RESULT	Join to ORDER_PROC for context
`CLARITY_ADT`	ADT Events	`EVENT_ID`	FACT_ADT_EVENT	Admission, discharge, transfer events
`HSP_ACCOUNT`	Billing	`HSP_ACCOUNT_ID`	FACT_CLAIM_HEADER	Charges, payments, adjustments
`CLARITY_SER`	Providers	`PROV_ID`	DIM_PROVIDER	Names, specialty, NPI
`CLARITY_DEP`	Departments	`DEPARTMENT_ID`	DIM_DEPARTMENT	Name, specialty, facility
`ZC_PAT_CLASS`	Reference	`PAT_CLASS_C`	DIM_PATIENT_CLASS	Decodes PAT_CLASS_C → Inpatient/Outpatient
`COVERAGE`	Insurance	`COVERAGE_ID`	DIM_INSURANCE	Payer, plan, policy number

Understanding Epic Clarity Naming Conventions

Epic Clarity naming conventions differ significantly from ISO-11179 standards. Understanding these patterns is essential for mapping Clarity columns to your warehouse standards.

`_C` suffix → Category fields

-- PAT_CLASS_C, ENC_TYPE_C, SEX_C
-- Always join to ZC_[CATEGORY] to decode
SELECT PAT_CLASS_C, zc.NAME as pat_class_desc
FROM PAT_ENC e
LEFT JOIN ZC_PAT_CLASS zc ON zc.PAT_CLASS_C = e.PAT_CLASS_C

`_ID` suffix → Identifier fields

Always store as VARCHAR, never INTEGER. PAT_ENC_CSN_ID uniquely identifies each encounter.

`ZC_` prefix → Lookup tables

ZC_ tables decode _C code fields. ZC_PAT_CLASS maps numeric codes to Inpatient, Outpatient, Emergency.

`CLARITY_` prefix → Master/reference tables

CLARITY_EDG (diagnoses), CLARITY_EAP (procedures), CLARITY_SER (providers) — joined to fact-like tables for description lookups.

`LINE` column → Sequence number

Used in multi-value tables. Always include LINE in GROUP BY and ORDER BY when aggregating.

Common Epic Clarity SQL Patterns

1. Patient Encounters with Diagnoses

SELECT
    e.PAT_ENC_CSN_ID          AS enctr_id,
    e.PAT_ID                  AS pat_id,
    e.CONTACT_DATE            AS enctr_dt,
    zt.NAME                   AS enc_type_desc,
    d.DEPARTMENT_NAME         AS dept_nm,
    dx.ICD10_CODE             AS prim_diag_cd,
    edg.DX_NAME               AS prim_diag_desc,
    s.PROV_NAME               AS rndrng_prvdr_nm,
    s.NPI                     AS rndrng_prvdr_npi
FROM PAT_ENC e
LEFT JOIN ZC_ENC_TYPE zt    ON zt.ENC_TYPE_C = e.ENC_TYPE_C
LEFT JOIN CLARITY_DEP d     ON d.DEPARTMENT_ID = e.DEPARTMENT_ID
LEFT JOIN PAT_ENC_DX dx     ON dx.PAT_ENC_CSN_ID = e.PAT_ENC_CSN_ID
                            AND dx.LINE = 1
LEFT JOIN CLARITY_EDG edg   ON edg.DX_ID = dx.DX_ID
LEFT JOIN CLARITY_SER s     ON s.PROV_ID = e.VISIT_PROV_ID
WHERE e.CONTACT_DATE >= DATEADD(month, -12, GETDATE())
  AND e.ENC_CLOSED_YN = 'Y'
ORDER BY e.CONTACT_DATE DESC;

2. Inpatient Admissions with LOS

SELECT
    e.PAT_ENC_CSN_ID                          AS enctr_id,
    h.HOSP_ADMSN_TIME                         AS admit_ts,
    h.HOSP_DISCHRG_TIME                       AS dschrg_ts,
    DATEDIFF(day,
        h.HOSP_ADMSN_TIME,
        h.HOSP_DISCHRG_TIME)                  AS los_days,
    zd.NAME                                   AS dschrg_dispo_desc,
    h.DRG_MPI_CODE                            AS ms_drg_cd,
    p.BIRTH_DATE                              AS pat_dob
FROM PAT_ENC e
JOIN PAT_ENC_HSP h  ON h.PAT_ENC_CSN_ID = e.PAT_ENC_CSN_ID
JOIN PATIENT p      ON p.PAT_ID = e.PAT_ID
LEFT JOIN ZC_DISCH_DISP zd ON zd.DISCH_DISP_C = h.DISCH_DISP_C
WHERE h.HOSP_ADMSN_TIME >= DATEADD(year, -1, GETDATE())
  AND h.HOSP_DISCHRG_TIME IS NOT NULL;

3. Lab Orders and Results

SELECT
    op.ORDER_PROC_ID      AS order_id,
    op.PAT_ENC_CSN_ID     AS enctr_id,
    eap.PROC_NAME         AS proc_nm,
    eap.PROC_CODE         AS cpt_cd,
    r.LINE                AS result_line,
    r.ORD_VALUE           AS result_val,
    r.REFERENCE_UNIT      AS result_unit,
    r.REFERENCE_LOW       AS ref_rng_low,
    r.REFERENCE_HIGH      AS ref_rng_high,
    r.RESULT_FLAG         AS abnrml_flg
FROM ORDER_PROC op
JOIN CLARITY_EAP eap    ON eap.PROC_ID = op.PROC_ID
LEFT JOIN ORDER_RESULTS r ON r.ORDER_ID = op.ORDER_PROC_ID
WHERE op.ORDER_TIME >= DATEADD(month, -3, GETDATE())
  AND op.ORDER_STATUS_C = 5  -- 5 = Resulted
ORDER BY op.ORDER_TIME DESC, r.LINE;

4. ADT Events for Patient Flow

SELECT
    a.PAT_ENC_CSN_ID      AS enctr_id,
    a.EVENT_TIME          AS adt_event_ts,
    za.NAME               AS adt_event_type,
    fd.DEPARTMENT_NAME    AS from_dept_nm,
    td.DEPARTMENT_NAME    AS to_dept_nm,
    DATEDIFF(minute,
        LAG(a.EVENT_TIME) OVER (
            PARTITION BY a.PAT_ENC_CSN_ID
            ORDER BY a.EVENT_TIME),
        a.EVENT_TIME)     AS mins_in_prior_location
FROM CLARITY_ADT a
LEFT JOIN ZC_ADT_EVENT_TYPE za ON za.EVENT_TYPE_C = a.EVENT_TYPE_C
LEFT JOIN CLARITY_DEP fd ON fd.DEPARTMENT_ID = a.FROM_DEPARTMENT_ID
LEFT JOIN CLARITY_DEP td ON td.DEPARTMENT_ID = a.TO_DEPARTMENT_ID
WHERE a.EVENT_TIME >= DATEADD(day, -30, GETDATE())
ORDER BY a.PAT_ENC_CSN_ID, a.EVENT_TIME;

dbt Model for Epic Clarity in Snowflake

After replicating Clarity tables to Snowflake, use dbt to standardize column naming and enforce data quality.

-- models/staging/stg_clarity_encounters.sql
{{ config(materialized='view', schema='staging', tags=['clarity', 'encounters']) }}

with source as (
    select * from {{ source('clarity', 'PAT_ENC') }}
),
enc_type as (
    select ENC_TYPE_C, NAME as enc_type_desc
    from {{ source('clarity', 'ZC_ENC_TYPE') }}
),
staged as (
    select
        -- ISO-11179 naming
        e.PAT_ENC_CSN_ID::varchar(18)   as enctr_id,
        e.PAT_ID::varchar(18)            as pat_id,
        e.DEPARTMENT_ID::varchar(18)     as dept_id,
        e.VISIT_PROV_ID::varchar(18)     as rndrng_prvdr_id,
        e.CONTACT_DATE::date             as enctr_dt,
        e.APPT_TIME::timestamp           as appt_ts,
        e.ENC_TYPE_C                     as enc_type_c,
        et.enc_type_desc,
        e.ENC_CLOSED_YN                  as enc_closed_flg
    from source e
    left join enc_type et on et.ENC_TYPE_C = e.ENC_TYPE_C
    where e.CONTACT_DATE >= dateadd(year, -3, current_date)
)
select * from staged

# models/staging/stg_clarity_encounters.yml
version: 2
models:
  - name: stg_clarity_encounters
    columns:
      - name: enctr_id
        tests: [not_null, unique]
      - name: pat_id
        tests: [not_null]
      - name: enctr_dt
        tests: [not_null]
      - name: enc_closed_flg
        tests:
          - accepted_values:
              values: ['Y', 'N']

Clarity Data Quality Rules

🔴 HIGH — Duplicate CSN_IDs after replication

SELECT PAT_ENC_CSN_ID, COUNT(*) as cnt
FROM stg_clarity_encounters
GROUP BY PAT_ENC_CSN_ID
HAVING COUNT(*) > 1;

🟡 MEDIUM — Undecoded ZC_ category codes

SELECT enc_type_c, COUNT(*) as cnt
FROM stg_clarity_encounters
WHERE enc_type_desc IS NULL
  AND enc_type_c IS NOT NULL
GROUP BY enc_type_c;

🔴 HIGH — Missing NPI on providers

SELECT PROV_ID, PROV_NAME, PROV_TYPE
FROM CLARITY_SER
WHERE NPI IS NULL
   OR LEN(NPI) != 10
  AND PROV_TYPE_C NOT IN (20, 21);

🔴 HIGH — Negative LOS from data entry errors

SELECT PAT_ENC_CSN_ID,
    DATEDIFF(day, HOSP_ADMSN_TIME, HOSP_DISCHRG_TIME) as los_days
FROM PAT_ENC_HSP
WHERE HOSP_DISCHRG_TIME < HOSP_ADMSN_TIME
   OR DATEDIFF(day, HOSP_ADMSN_TIME, HOSP_DISCHRG_TIME) > 365;

🟡 MEDIUM — Open encounters never closed

SELECT YEAR(CONTACT_DATE) as yr,
       MONTH(CONTACT_DATE) as mo,
       ENC_CLOSED_YN,
       COUNT(*) as enc_cnt
FROM PAT_ENC
WHERE CONTACT_DATE >= DATEADD(month, -6, GETDATE())
GROUP BY YEAR(CONTACT_DATE), MONTH(CONTACT_DATE), ENC_CLOSED_YN
ORDER BY yr, mo;

Migrating Clarity to Snowflake — 3-Step Architecture

Why Your Medicare Advantage RAF Scores Are Wrong (And How to Check)

Season Mudbhary — Sat, 11 Jul 2026 01:59:32 +0000

Most health plans are leaving money on the table with risk adjustment.

Not because of fraud. Not because of bad coding. Because of incomplete diagnosis capture — and no one checks until CMS reconciliation hits.

Here's how RAF scores actually work:

Two components make up every RAF score:

Demographic component — age, gender, enrollment status (community vs institutional). A 72-year-old female in the community starts at 0.379 before a single diagnosis is added.
Diagnosis component — each qualifying ICD-10 code maps to an HCC category, and each HCC carries a CMS-published coefficient. These add up. But hierarchies prevent double-counting — the most severe condition in each disease group wins.

The V28 shift most plans still haven't fully absorbed:

CMS moved from V24 (86 HCC categories) to V28 (115 categories) starting in 2024. The blend runs through 2026 — 33% V24 / 67% V28 in 2025, then 100% V28 in 2026.

V28 added significant weight to behavioral health and substance use disorder categories. Plans that haven't updated their coding workflows for V28 are systematically under-capturing RAF on their highest-cost members.

What a missed HCC actually costs:

Take a member with diabetes + chronic kidney disease stage 3. Under V28:

Diabetes with complications: HCC coefficient ≈ 0.302
CKD stage 3-4: HCC coefficient ≈ 0.289

Miss the CKD diagnosis in the annual coding sweep → lose 0.289 RAF points per member.

At a $900/month base rate: $900 × 0.289 = $260/member/month in underpayment.

At 1,000 members with missed CKD → $3.1M/year in lost revenue.

That's one diagnosis code. On one condition group.

The practical check every risk adjustment team should run:

Pull members with diabetes diagnoses (E10-E13 codes)
Cross-reference against lab results — HbA1c > 9.0 without a complications HCC is a red flag
Pull members on ACE inhibitors or ARBs without a CKD or hypertension HCC — likely under-coded
Check members 65+ without a mental health or behavioral health HCC against pharmacy claims for psych medications

Most plans find 3-8% of their member panel with actionable coding gaps on the first pass.

We built a free RAF score calculator to help teams model this:

Paste in age, gender, enrollment status, and ICD-10 codes → get the full CMS-HCC V28 RAF score with component breakdown and payment impact estimate.

No account required. Useful for:

Coding education and training
Scenario modeling before annual sweeps
Quick-checking individual member profiles
Understanding V28 vs V24 scoring differences

👉 Try it free: mdatool.com/tools/hcc-calculator

The Pro version lets risk adjustment teams model entire member cohorts, save and compare scenarios, and export RAF reports for actuarial review.

I Built an Open Healthcare Data Dictionary with 100k+ ISO-11179 Terms — Here's What I Learned

Season Mudbhary — Tue, 30 Jun 2026 02:41:39 +0000

If you've ever worked on a healthcare data team, you've lived this problem: one system calls it mem_id, another calls it member_number, a third calls it subscriber_key — and they all mean the exact same thing.

Multiply that across claims, clinical, provider, pharmacy, and finance domains, and you get a data warehouse where nobody fully trusts a join without checking three wikis and pinging someone on Slack first.

I got tired of solving this problem from scratch on every project, so I built a standardized healthcare data dictionary — and I'm open-sourcing a sample of it.

The Core Idea: ISO-11179

ISO-11179 is an international standard for metadata registries — basically, a formal way to define data elements so that "Member Identifier" means the same thing, has the same data type, and follows the same naming convention no matter which system, team, or analyst is touching it.

In practice, for a healthcare data warehouse, that looks like:

Term	Standard Column	Data Type	Definition
Member Identifier	`mbr_id`	`VARCHAR(20)`	Unique identifier assigned to a health plan member
Rendering Provider NPI	`rndrng_prvdr_npi`	`VARCHAR(10)`	NPI of the provider who performed the service
Primary Diagnosis Code	`prim_diag_cd`	`VARCHAR(7)`	Principal ICD-10-CM diagnosis code for the claim

Once you standardize this once, every dbt model, every Snowflake schema, and every BI dashboard downstream inherits consistency for free.

What I Built

A healthcare data dictionary with 100,000+ terms spanning:

Claims
Clinical
Member / Enrollment
Provider
Pharmacy
Finance
Quality (HEDIS, risk adjustment)
Laboratory
Population health
Behavioral health
Supply chain
Compliance
Technology / operations

Every term has a consistent abbreviation convention, a defined data type, and a plain-English definition — plus links to related terms and example DDL.

Open Sample: 100 Terms on GitHub

I pulled 100 representative terms into a public repo so you can see exactly what this looks like without needing to sign up for anything:

github.com/mdatool/healthcare-data-glossary

It's available as both a browsable Markdown table and a CSV you can drop into a spreadsheet, a dbt seed, or whatever tool you're using.

A small sample:

Claim Number              → clm_nbr            VARCHAR(20)
National Provider Identifier → npi_nbr          VARCHAR(10)
Primary Diagnosis Code     → prim_diag_cd       VARCHAR(7)
Paid Amount                → paid_amt           DECIMAL(12,2)
HCC Category Code          → hcc_cat_cd         VARCHAR(10)

The Harder Problem: Generating This From an Existing Schema

Having a clean dictionary is great for new builds. But most of us aren't starting from scratch — we're staring at a legacy claims_hdr table someone built in 2014 with column names like mem_first, provider_npi, and diag_1, and zero documentation.

So I also built a tool that takes raw DDL and automatically generates:

A data dictionary — business definitions for every column
Standardized naming — maps your existing columns to ISO-11179 conventions
PHI/PII classification — flags HIPAA-sensitive fields automatically (SSN, DOB, names, etc.)
dbt schema YAML — tests and descriptions, ready to commit
Business glossary links — ties every column back to a canonical definition

Here's a real, unedited example of what it outputs when you paste in a typical claims header schema:

github.com/mdatool/healthcare-metadata-sample

The repo includes the raw input DDL (claims-schema.sql) alongside the full generated report so you can see the before/after.

A snippet of the generated dbt YAML:

- name: mbr_ssn
  description: "Government-issued Social Security Number of the member."
  meta:
    phi: true
    phi_risk: high
  tests:
    - not_null

- name: rndrng_prvdr_npi
  description: "National Provider Identifier of the provider who performed the service."
  tests:
    - not_null
    - dbt_utils.expression_is_true:
        expression: "length(rndrng_prvdr_npi) = 10"

That PHI flag on the SSN field, generated automatically from a generic column name (mem_ssn), is the part I find most useful day-to-day — catching sensitive fields before they accidentally end up unmasked in a BI tool is a real, recurring problem in healthcare data work.

Try It Yourself

If you want to run this against your own schema, the tool is free to use:

mdatool.com/tools/metadata-generator

And the full 100,000+ term dictionary (searchable, with production DDL examples per term) is here:

mdatool.com/glossary

There's also a small set of other free tools I built alongside this — a DDL converter for moving schemas between Snowflake/BigQuery/Databricks/SQL Server, an NPI lookup against the CMS registry, and an ICD-10 search tool with data-engineering-specific context (column naming, dbt models, data quality checks) rather than just clinical lookup.

What I'd Love Feedback On

This is very much a living project. A few things I'm actively thinking about:

Whether the abbreviation conventions I've chosen match what other healthcare data teams actually use in practice (there's no universal standard, just strong conventions)
What additional domains or term categories would be most useful to prioritize next
Whether the PHI/PII classification logic should be more conservative (flag more) or more precise (flag less, but with higher confidence)

If you work in healthcare data and have opinions on any of this — or just want to poke holes in the approach — I'd genuinely like to hear it in the comments.

If this was useful, the GitHub repos are linked above and contributions/issues are welcome. No pitch beyond that — just sharing something I built to solve a problem I kept running into.

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