ICD-10 to Snowflake: A Production DDL Schema Guide

Most healthcare data engineers treat ICD-10 as a lookup table problem. Load the CMS flat file, create a reference table, join on diagnosis code, done.

That works until your claims pipeline hits 50 million rows, your HEDIS query times out after 4 minutes, and your compliance team asks why the same diagnosis code is showing up with three different descriptions depending on which report they run.

The problem isn’t the data. It’s the schema.

This guide covers how to design a production-grade ICD-10 schema in Snowflake — one that handles versioning, scales to hundreds of millions of claims rows, and gives your analytics team consistent, trustworthy results.

What ICD-10 Data Actually Looks Like

Before designing a schema, understand what you’re working with. CMS releases two ICD-10 code sets annually:

ICD-10-CM — Clinical Modification. Used for diagnosis codes on claims, encounters, and medical records. Approximately 70,000+ codes. Updated every October 1.

ICD-10-PCS — Procedure Coding System. Used for inpatient procedure codes on institutional claims. Approximately 78,000+ codes. Also updated October 1.

Each annual release is a separate flat file from CMS containing:

• The diagnosis or procedure code
• Short description (up to 60 characters)
• Long description (up to 323 characters)
• A valid/billable flag (not all codes are billable — some are header codes only)

The critical thing most schemas miss: ICD-10 codes change every year. Codes are added, deleted, and revised. A code valid in 2023 may be invalid in 2024. Your schema must track which version of a code was active when a claim was submitted — not just the latest version.

The Wrong Way To Model ICD-10

Here is what most teams build first:

sql

-- The naive approach — don't do this
CREATE TABLE icd10_codes (
    icd10_code          VARCHAR(10)  NOT NULL,
    short_description   VARCHAR(60)  NOT NULL,
    long_description    VARCHAR(323) NOT NULL,
    is_billable         BOOLEAN      NOT NULL,
    PRIMARY KEY (icd10_code)
);

This breaks in three ways:

No versioning — when CMS updates a code description in the October release, your UPDATE overwrites historical context. A claim from 2022 now joins to a 2024 description that didn’t exist when the claim was submitted.

No distinction between CM and PCS — ICD-10-CM and ICD-10-PCS are separate code sets. Some codes look identical across sets. Without a code type column you will join the wrong description to procedure codes on institutional claims.

No audit trail — compliance audits ask when a code was loaded, which version it came from, and whether it was valid on a specific date of service. A single-row-per-code design cannot answer those questions.

The Production Snowflake Schema

Here is the schema designed for production healthcare data pipelines:

sql

-- ICD-10 reference database and schema
CREATE DATABASE IF NOT EXISTS healthcare_ref;
CREATE SCHEMA IF NOT EXISTS healthcare_ref.code_sets;

USE SCHEMA healthcare_ref.code_sets;

sql

-- Core ICD-10 reference table with full versioning
CREATE OR REPLACE TABLE ref_icd10_codes (
    icd10_code_key          NUMBER          NOT NULL AUTOINCREMENT PRIMARY KEY,
    icd10_code              VARCHAR(10)     NOT NULL,
    icd10_code_type         VARCHAR(5)      NOT NULL,  -- 'CM' or 'PCS'
    fiscal_year             SMALLINT        NOT NULL,  -- CMS fiscal year e.g. 2024
    effective_date          DATE            NOT NULL,  -- Oct 1 of fiscal year
    expiration_date         DATE,                      -- Oct 1 of next year or NULL if current
    short_description       VARCHAR(60)     NOT NULL,
    long_description        VARCHAR(323)    NOT NULL,
    is_billable_flag        BOOLEAN         NOT NULL DEFAULT FALSE,
    is_header_code_flag     BOOLEAN         NOT NULL DEFAULT FALSE,
    is_current_flag         BOOLEAN         NOT NULL DEFAULT FALSE,
    chapter_number          VARCHAR(10),
    chapter_description     VARCHAR(200),
    category_code           VARCHAR(10),
    category_description    VARCHAR(200),
    cms_source_file         VARCHAR(200)    NOT NULL,
    loaded_datetime         TIMESTAMP_NTZ   NOT NULL DEFAULT CURRENT_TIMESTAMP(),
    loaded_by               VARCHAR(100)    NOT NULL DEFAULT CURRENT_USER(),
    CONSTRAINT uq_icd10_code_version 
        UNIQUE (icd10_code, icd10_code_type, fiscal_year)
);

sql

-- Clustering key for Snowflake performance
-- Cluster on code type and fiscal year — most queries filter on both
ALTER TABLE ref_icd10_codes 
    CLUSTER BY (icd10_code_type, fiscal_year, icd10_code);

sql

-- Current codes view — always returns the active fiscal year only
-- Use this view in all analytics queries, not the base table
CREATE OR REPLACE VIEW vw_icd10_current AS
SELECT
    icd10_code,
    icd10_code_type,
    fiscal_year,
    effective_date,
    short_description,
    long_description,
    is_billable_flag,
    is_header_code_flag,
    chapter_number,
    chapter_description,
    category_code,
    category_description
FROM ref_icd10_codes
WHERE is_current_flag = TRUE;

sql

-- Point-in-time lookup view — use for historical claims joins
-- Pass the date of service to get the correct code version
CREATE OR REPLACE VIEW vw_icd10_point_in_time AS
SELECT
    icd10_code,
    icd10_code_type,
    fiscal_year,
    effective_date,
    expiration_date,
    short_description,
    long_description,
    is_billable_flag,
    chapter_number,
    chapter_description,
    category_code,
    category_description
FROM ref_icd10_codes
WHERE is_current_flag = FALSE
   OR is_current_flag = TRUE;

Loading ICD-10 Data Into Snowflake

CMS releases ICD-10 files as ZIP archives on their website each year. The flat files are tab-delimited text. Here is the complete loading pattern:

sql

-- Create a Snowflake stage for the CMS flat files
-- Store files in your S3 bucket or internal stage
CREATE OR REPLACE STAGE icd10_cms_stage
    URL = 's3://your-bucket/reference-data/icd10/'
    STORAGE_INTEGRATION = your_s3_integration
    FILE_FORMAT = (
        TYPE = 'CSV'
        FIELD_DELIMITER = '\t'
        SKIP_HEADER = 1
        NULL_IF = ('NULL', 'null', '')
        EMPTY_FIELD_AS_NULL = TRUE
    );

sql

-- Staging table — raw load before validation
CREATE OR REPLACE TABLE stg_icd10_cms_load (
    raw_code            VARCHAR(10),
    raw_short_desc      VARCHAR(60),
    raw_long_desc       VARCHAR(323),
    raw_billable_flag   VARCHAR(1),
    source_file_name    VARCHAR(200),
    load_datetime       TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

sql

-- Load from stage into staging table
COPY INTO stg_icd10_cms_load (
    raw_code,
    raw_short_desc,
    raw_long_desc,
    raw_billable_flag,
    source_file_name
)
FROM (
    SELECT
        $1,
        $2,
        $3,
        $4,
        METADATA$FILENAME
    FROM @icd10_cms_stage/FY2025_Code_Descriptions_Tabular_Order.txt
)
FILE_FORMAT = (FORMAT_NAME = 'icd10_tab_delimited')
ON_ERROR = 'CONTINUE';

sql

-- Validate before inserting to reference table
-- Check for malformed codes, missing descriptions, unexpected nulls
SELECT
    'Missing long description'      AS issue_type,
    COUNT(*)                        AS issue_count
FROM stg_icd10_cms_load
WHERE raw_long_desc IS NULL
   OR LENGTH(TRIM(raw_long_desc)) = 0
UNION ALL
SELECT
    'Code exceeds 10 characters'    AS issue_type,
    COUNT(*)
FROM stg_icd10_cms_load
WHERE LENGTH(raw_code) > 10
UNION ALL
SELECT
    'Invalid billable flag'         AS issue_type,
    COUNT(*)
FROM stg_icd10_cms_load
WHERE raw_billable_flag NOT IN ('0', '1')
  AND raw_billable_flag IS NOT NULL;

sql

-- Mark previous year codes as no longer current
UPDATE ref_icd10_codes
SET 
    is_current_flag = FALSE,
    expiration_date = '2025-10-01'  -- Start of new fiscal year
WHERE icd10_code_type = 'CM'
  AND fiscal_year = 2024
  AND is_current_flag = TRUE;

sql

-- Insert new fiscal year codes
INSERT INTO ref_icd10_codes (
    icd10_code,
    icd10_code_type,
    fiscal_year,
    effective_date,
    expiration_date,
    short_description,
    long_description,
    is_billable_flag,
    is_header_code_flag,
    is_current_flag,
    cms_source_file
)
SELECT
    UPPER(TRIM(raw_code))                       AS icd10_code,
    'CM'                                        AS icd10_code_type,
    2025                                        AS fiscal_year,
    '2024-10-01'::DATE                          AS effective_date,
    NULL                                        AS expiration_date,
    TRIM(raw_short_desc)                        AS short_description,
    TRIM(raw_long_desc)                         AS long_description,
    CASE raw_billable_flag 
        WHEN '1' THEN TRUE 
        ELSE FALSE 
    END                                         AS is_billable_flag,
    FALSE                                       AS is_header_code_flag,
    TRUE                                        AS is_current_flag,
    source_file_name                            AS cms_source_file
FROM stg_icd10_cms_load
WHERE raw_code IS NOT NULL
  AND LENGTH(TRIM(raw_code)) > 0;

Joining ICD-10 to Claims Data

This is where most pipelines make mistakes. Here are the correct patterns for every scenario:

sql

-- Pattern 1: Current claims reporting
-- Use the current view for any report on active data
SELECT
    c.claim_id,
    c.member_id,
    c.service_date,
    c.diagnosis_code_primary,
    icd.short_description,
    icd.chapter_description
FROM fct_medical_claims c
LEFT JOIN vw_icd10_current icd
    ON c.diagnosis_code_primary = icd.icd10_code
   AND icd.icd10_code_type = 'CM'
WHERE c.service_date >= '2024-01-01';

sql

-- Pattern 2: Historical claims — point in time join
-- Critical for audit, HEDIS, and risk adjustment queries
-- Always join on the fiscal year active at time of service
SELECT
    c.claim_id,
    c.member_id,
    c.service_date,
    c.diagnosis_code_primary,
    icd.short_description,
    icd.fiscal_year         AS code_version_used,
    icd.is_billable_flag
FROM fct_medical_claims c
LEFT JOIN ref_icd10_codes icd
    ON c.diagnosis_code_primary = icd.icd10_code
   AND icd.icd10_code_type = 'CM'
   AND c.service_date >= icd.effective_date
   AND (
       c.service_date < icd.expiration_date 
       OR icd.expiration_date IS NULL
   )
WHERE c.service_date BETWEEN '2020-01-01' AND '2024-12-31';

sql

-- Pattern 3: Multi-diagnosis claims
-- Most claims have up to 12 diagnosis codes (837I allows 25)
-- Unpivot diagnosis codes before joining
SELECT
    c.claim_id,
    c.member_id,
    c.service_date,
    diag.diagnosis_sequence,
    diag.diagnosis_code,
    icd.short_description,
    icd.chapter_description,
    icd.is_billable_flag
FROM fct_medical_claims c
CROSS JOIN LATERAL (
    SELECT 1  AS diagnosis_sequence, c.diagnosis_code_1  AS diagnosis_code WHERE c.diagnosis_code_1  IS NOT NULL
    UNION ALL
    SELECT 2,                        c.diagnosis_code_2            WHERE c.diagnosis_code_2  IS NOT NULL
    UNION ALL
    SELECT 3,                        c.diagnosis_code_3            WHERE c.diagnosis_code_3  IS NOT NULL
    UNION ALL
    SELECT 4,                        c.diagnosis_code_4            WHERE c.diagnosis_code_4  IS NOT NULL
    UNION ALL
    SELECT 5,                        c.diagnosis_code_5            WHERE c.diagnosis_code_5  IS NOT NULL
) diag
LEFT JOIN vw_icd10_current icd
    ON diag.diagnosis_code = icd.icd10_code
   AND icd.icd10_code_type = 'CM';

Snowflake-Specific Performance Patterns

A few optimizations that matter at scale for ICD-10 reference joins:

sql

-- Search optimization for point lookups
-- Enables sub-second code lookups without full table scans
ALTER TABLE ref_icd10_codes 
    ADD SEARCH OPTIMIZATION ON EQUALITY(icd10_code, icd10_code_type);

sql

-- Materialized view for the most common analytics join
-- Pre-computes current CM codes with chapter rollup
-- Refreshes automatically when ref_icd10_codes changes
CREATE OR REPLACE MATERIALIZED VIEW mv_icd10_cm_current AS
SELECT
    icd10_code,
    short_description,
    long_description,
    is_billable_flag,
    chapter_number,
    chapter_description,
    category_code,
    category_description,
    fiscal_year
FROM ref_icd10_codes
WHERE icd10_code_type = 'CM'
  AND is_current_flag = TRUE;

sql

-- Result cache warm-up query
-- Run this after each annual load to prime Snowflake's result cache
-- Subsequent identical queries return instantly from cache
SELECT
    chapter_description,
    COUNT(*)            AS code_count,
    SUM(CASE WHEN is_billable_flag THEN 1 ELSE 0 END) AS billable_count
FROM mv_icd10_cm_current
GROUP BY chapter_description
ORDER BY code_count DESC;

ICD-10 Code Quality Checks

Add these quality checks to your loading pipeline — run them after every annual update:

sql

-- Check 1: Codes present in last year but missing this year
-- These are deleted codes — claims using them after Oct 1 are invalid
SELECT
    prev.icd10_code,
    prev.short_description  AS previous_description,
    'DELETED IN NEW VERSION' AS status
FROM ref_icd10_codes prev
LEFT JOIN ref_icd10_codes curr
    ON prev.icd10_code = curr.icd10_code
   AND curr.fiscal_year = 2025
   AND curr.icd10_code_type = 'CM'
WHERE prev.fiscal_year = 2024
  AND prev.icd10_code_type = 'CM'
  AND curr.icd10_code IS NULL;

sql

-- Check 2: New codes added in this year's release
SELECT
    curr.icd10_code,
    curr.short_description  AS new_description,
    'NEW IN 2025'           AS status
FROM ref_icd10_codes curr
LEFT JOIN ref_icd10_codes prev
    ON curr.icd10_code = prev.icd10_code
   AND prev.fiscal_year = 2024
   AND prev.icd10_code_type = 'CM'
WHERE curr.fiscal_year = 2025
  AND curr.icd10_code_type = 'CM'
  AND prev.icd10_code IS NULL;

sql

-- Check 3: Description changes between versions
-- Important for compliance — same code, different meaning
SELECT
    curr.icd10_code,
    prev.long_description   AS old_description,
    curr.long_description   AS new_description
FROM ref_icd10_codes curr
JOIN ref_icd10_codes prev
    ON curr.icd10_code = prev.icd10_code
   AND curr.icd10_code_type = prev.icd10_code_type
   AND prev.fiscal_year = 2024
WHERE curr.fiscal_year = 2025
  AND curr.icd10_code_type = 'CM'
  AND curr.long_description != prev.long_description;

sql

-- Check 4: Active claims using deleted codes
-- Run this after each annual update to find at-risk claims
SELECT
    c.claim_id,
    c.service_date,
    c.diagnosis_code_primary,
    'Code deleted in FY2025 — review required' AS alert
FROM fct_medical_claims c
LEFT JOIN ref_icd10_codes valid
    ON c.diagnosis_code_primary = valid.icd10_code
   AND valid.icd10_code_type = 'CM'
   AND c.service_date >= valid.effective_date
   AND (c.service_date < valid.expiration_date OR valid.expiration_date IS NULL)
WHERE c.service_date >= '2024-10-01'
  AND valid.icd10_code IS NULL;

Chapter Rollup Reference Table

For analytics and reporting, you need ICD-10 codes grouped into clinical chapters. Add this reference table to your schema:

sql

CREATE OR REPLACE TABLE ref_icd10_chapters (
    chapter_number          VARCHAR(10)     NOT NULL,
    chapter_description     VARCHAR(200)    NOT NULL,
    code_range_start        VARCHAR(10)     NOT NULL,
    code_range_end          VARCHAR(10)     NOT NULL,
    icd10_code_type         VARCHAR(5)      NOT NULL DEFAULT 'CM',
    CONSTRAINT pk_icd10_chapters 
        PRIMARY KEY (chapter_number, icd10_code_type)
);

-- ICD-10-CM chapters (21 chapters)
INSERT INTO ref_icd10_chapters VALUES
('I',    'Certain infectious and parasitic diseases',          'A00', 'B99', 'CM'),
('II',   'Neoplasms',                                          'C00', 'D49', 'CM'),
('III',  'Diseases of the blood and blood-forming organs',     'D50', 'D89', 'CM'),
('IV',   'Endocrine, nutritional and metabolic diseases',      'E00', 'E89', 'CM'),
('V',    'Mental, behavioral and neurodevelopmental disorders','F01', 'F99', 'CM'),
('VI',   'Diseases of the nervous system',                     'G00', 'G99', 'CM'),
('VII',  'Diseases of the eye and adnexa',                     'H00', 'H59', 'CM'),
('VIII', 'Diseases of the ear and mastoid process',            'H60', 'H95', 'CM'),
('IX',   'Diseases of the circulatory system',                 'I00', 'I99', 'CM'),
('X',    'Diseases of the respiratory system',                 'J00', 'J99', 'CM'),
('XI',   'Diseases of the digestive system',                   'K00', 'K95', 'CM'),
('XII',  'Diseases of the skin and subcutaneous tissue',       'L00', 'L99', 'CM'),
('XIII', 'Diseases of the musculoskeletal system',             'M00', 'M99', 'CM'),
('XIV',  'Diseases of the genitourinary system',               'N00', 'N99', 'CM'),
('XV',   'Pregnancy, childbirth and the puerperium',           'O00', 'O9A', 'CM'),
('XVI',  'Certain conditions originating in the perinatal period','P00','P96','CM'),
('XVII', 'Congenital malformations and chromosomal abnormalities','Q00','Q99','CM'),
('XVIII','Symptoms, signs and abnormal clinical findings',     'R00', 'R99', 'CM'),
('XIX',  'Injury, poisoning and certain other consequences',   'S00', 'T88', 'CM'),
('XX',   'External causes of morbidity',                       'V00', 'Y99', 'CM'),
('XXI',  'Factors influencing health status',                  'Z00', 'Z99', 'CM');

5 Production Mistakes That Break ICD-10 Pipelines

1. Stripping the decimal point before loading. ICD-10 codes are stored without decimals in claims files (E1169 not E11.69) but CMS reference files sometimes include them. Standardize on no-decimal format throughout your pipeline and strip any decimals on load — never mix formats in the same table.

2. Not handling trailing whitespace. CMS flat files frequently have trailing spaces in code fields. A code stored as E1169 (with a trailing space) will never match E1169 in a join. Always TRIM() on load — add it to your staging validation checks.

3. Loading all codes as billable. The CMS file includes header codes — high level categories that are never valid on a claim. These have is_billable_flag = FALSE. If you load all codes as billable your claims validation will pass invalid codes through to submission.

4. Ignoring the October 1 cutoff. ICD-10 updates take effect October 1 each year. A claim with service date September 30 must use the old code set. A claim with service date October 1 must use the new one. Your point-in-time join must respect this boundary exactly.

5. Using a single table without versioning. The most common mistake. When you run your annual update, overwriting rows in a non-versioned table breaks every historical query that joins on diagnosis code. Use the versioned schema above from day one — retrofitting versioning onto a flat reference table is painful.

FAQ

Do I need both ICD-10-CM and ICD-10-PCS in the same table? Yes — store them in the same table with the icd10_code_type column distinguishing them. This makes joins simpler and lets you validate that professional claims (837P) only use CM codes and institutional claims (837I) use both CM for diagnoses and PCS for inpatient procedures.

How do I handle ICD-9 codes in historical claims? Add a diagnosis_code_versioncolumn to your claims table — values ICD9 or ICD10. Create a separate ref_icd9_codestable using the same schema pattern. Never join ICD-9 codes against an ICD-10 reference table.

How often does CMS release ICD-10 updates? Annually, effective October 1. CMS releases the files approximately 6 months in advance — typically in April or May. Subscribe to the CMS ICD-10 email list to get notified. Some years CMS releases interim updates for new conditions — COVID-19 coding got multiple mid-year updates.

Should I store ICD-10 codes in uppercase or mixed case? Always uppercase, no exceptions. CMS files are uppercase. Claims files are uppercase. Storing mixed case creates silent join failures that are extremely difficult to debug. Add UPPER(TRIM(raw_code)) to every load query.

Try the Free DDL Converter

If you’re migrating this schema from another database to Snowflake — or converting your existing ICD-10 Oracle or PostgreSQL schema — mdatool’s free DDL Converter translates DDL syntax across Snowflake, BigQuery, Oracle, SQL Server, and PostgreSQL instantly.

Additional free tools at mdatool.com:

• ICD-10 Search — look up any of 70,000+ ICD-10-CM codes by description or code prefix before they enter your pipeline
• SQL Linter — catch performance and security issues in your ICD-10 join queries before production
• Naming Auditor — verify your column names like icd10_code, fiscal_year, and is_billable_flag meet ISO-11179 standards
• Healthcare Data Dictionary — 100,000+ verified definitions including ICD-10, ICD-10-CM, ICD-10-PCS, and related terminology.

Originally published at *mdatool.com*