Healthcare Claims Data Model: Complete SQL Schema for Payers

Why Healthcare Claims Data Models Break

Claims data is the most complete record of what healthcare was actually delivered and paid for. But most organizations struggle with the same recurring problems:

• Claim types lumped together — professional, institutional, and dental claims have different structures.
• Missing adjudication history — storing only the final claim discards the full picture.
• Diagnosis ordering lost — the primary diagnosis (sequence 1) is clinically and legally different from secondary diagnoses.

This guide presents a production-ready claims data model with full SQL schema.

---

Claim Types and Why They Need Separate Treatment

---

Core Schema

Members

CREATE TABLE member (
    member_id        VARCHAR(36)  NOT NULL PRIMARY KEY,
    first_name       VARCHAR(100) NOT NULL,
    last_name        VARCHAR(100) NOT NULL,
    date_of_birth    DATE         NOT NULL,
    gender_cd        CHAR(1),
    zip_cd           CHAR(5),
    plan_id          VARCHAR(36),
    enrollment_dt    DATE,
    disenrollment_dt DATE,
    created_at       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

🔄

Free Tool

Convert this DDL to Snowflake, BigQuery, or PostgreSQL instantly →

Providers

CREATE TABLE provider (
    provider_id      VARCHAR(36)  NOT NULL PRIMARY KEY,
    npi              CHAR(10)     NOT NULL UNIQUE,
    provider_type_cd VARCHAR(10)  NOT NULL,
    first_name       VARCHAR(100),
    last_name        VARCHAR(100),
    org_name         VARCHAR(200),
    specialty_cd     VARCHAR(20),
    taxonomy_cd      VARCHAR(20),
    created_at       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Claim Header

CREATE TABLE claim (
    claim_id               VARCHAR(36)   NOT NULL PRIMARY KEY,
    claim_type_cd          VARCHAR(5)    NOT NULL,  -- 'PRO', 'INST', 'DENT'
    claim_status_cd        VARCHAR(10)   NOT NULL,  -- 'original', 'adjustment', 'void'
    original_claim_id      VARCHAR(36),
    member_id              VARCHAR(36)   NOT NULL REFERENCES member(member_id),
    billing_provider_npi   CHAR(10)      NOT NULL,
    rendering_provider_npi CHAR(10),
    service_from_dt        DATE          NOT NULL,
    service_to_dt          DATE          NOT NULL,
    received_dt            DATE          NOT NULL,
    adjudicated_dt         DATE,
    paid_dt                DATE,
    total_billed_amt       NUMERIC(12,2) NOT NULL,
    total_allowed_amt      NUMERIC(12,2),
    total_paid_amt         NUMERIC(12,2),
    member_liability_amt   NUMERIC(12,2),
    deductible_amt         NUMERIC(12,2),
    copay_amt              NUMERIC(12,2),
    coinsurance_amt        NUMERIC(12,2),
    claim_denial_cd        VARCHAR(20),
    payer_claim_nbr        VARCHAR(50),
    created_at             TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at             TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_claim_member   ON claim (member_id, service_from_dt);
CREATE INDEX idx_claim_provider ON claim (billing_provider_npi, service_from_dt);
CREATE INDEX idx_claim_status   ON claim (claim_status_cd, adjudicated_dt);

Claim Lines (Professional)

CREATE TABLE claim_line_professional (
    claim_line_id          VARCHAR(36)   NOT NULL PRIMARY KEY,
    claim_id               VARCHAR(36)   NOT NULL REFERENCES claim(claim_id),
    line_seq_no            SMALLINT      NOT NULL,
    service_from_dt        DATE          NOT NULL,
    place_of_svc_cd        CHAR(2)       NOT NULL,
    cpt_cd                 VARCHAR(10),
    hcpcs_cd               VARCHAR(10),
    modifier_1_cd          VARCHAR(2),
    modifier_2_cd          VARCHAR(2),
    units                  NUMERIC(8,2)  NOT NULL DEFAULT 1,
    billed_amt             NUMERIC(12,2) NOT NULL,
    allowed_amt            NUMERIC(12,2),
    paid_amt               NUMERIC(12,2),
    denial_cd              VARCHAR(20),
    rendering_provider_npi CHAR(10),
    created_at             TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Claim Diagnoses

CREATE TABLE claim_diagnosis (
    claim_diag_id  VARCHAR(36) NOT NULL PRIMARY KEY,
    claim_id       VARCHAR(36) NOT NULL REFERENCES claim(claim_id),
    diag_seq_no    SMALLINT    NOT NULL,
    icd10_cd       VARCHAR(10) NOT NULL,
    diag_type_cd   VARCHAR(5),
    poa_ind        CHAR(1),
    created_at     TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (claim_id, diag_seq_no)
);

CREATE INDEX idx_cld_claim ON claim_diagnosis (claim_id);
CREATE INDEX idx_cld_icd10 ON claim_diagnosis (icd10_cd);

Pharmacy Claims

CREATE TABLE claim_pharmacy (
    rx_claim_id             VARCHAR(36)   NOT NULL PRIMARY KEY,
    claim_status_cd         VARCHAR(10)   NOT NULL,
    member_id               VARCHAR(36)   NOT NULL REFERENCES member(member_id),
    prescriber_npi          CHAR(10),
    dispensing_pharmacy_npi CHAR(10),
    fill_dt                 DATE          NOT NULL,
    ndc_cd                  CHAR(11)      NOT NULL,
    drug_name               VARCHAR(200),
    days_supply             SMALLINT      NOT NULL,
    quantity_dispensed      NUMERIC(10,3) NOT NULL,
    refill_no               SMALLINT      NOT NULL DEFAULT 0,
    billed_amt              NUMERIC(12,2) NOT NULL,
    ingredient_cost_amt     NUMERIC(12,2),
    dispensing_fee_amt      NUMERIC(12,2),
    paid_amt                NUMERIC(12,2),
    member_copay_amt        NUMERIC(12,2),
    formulary_tier_cd       VARCHAR(5),
    created_at              TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP
);

---

Adjudication History

Store original, adjustment, and void claims — not just the final paid amount:

-- Net paid calculation accounting for adjustments and voids
SELECT
    member_id,
    SUM(CASE WHEN claim_status_cd IN ('original','adjustment') THEN total_paid_amt
             WHEN claim_status_cd = 'void' THEN -total_paid_amt
             ELSE 0 END) AS net_paid_amt
FROM claim
WHERE service_from_dt BETWEEN '2026-01-01' AND '2026-12-31'
GROUP BY member_id;

---

Key Reporting Queries

Top diagnosis codes by spend

SELECT
    cd.icd10_cd,
    COUNT(DISTINCT cd.claim_id) AS claim_count,
    SUM(c.total_paid_amt)       AS total_paid
FROM claim_diagnosis cd
JOIN claim c ON c.claim_id = cd.claim_id
WHERE cd.diag_seq_no = 1
  AND c.claim_status_cd = 'original'
  AND c.service_from_dt >= '2026-01-01'
GROUP BY cd.icd10_cd
ORDER BY total_paid DESC
LIMIT 20;

---

Validating Your Schema

Before loading real claims data, validate your DDL with the SQL Linter tool. Validate all NPI values using the NPI Lookup tool. For DDL conversion between Oracle/SQL Server and Snowflake/PostgreSQL, use the DDL Converter tool.