How to Model Healthcare Claims Data in erwin Data Modeler

Why erwin for Healthcare Claims Data Modeling?

erwin Data Modeler has been the preferred data modeling tool at CMS (Centers for Medicare & Medicaid Services) for over two decades. Healthcare claims data has a complex entity structure that benefits from formal logical modeling before physical implementation.

Free tools: Validate column names at mdatool.com/tools/naming-auditor and convert DDL at mdatool.com/tools/ddl-converter.

---

The Core Healthcare Claims Entities

1. CLAIM_HEADER

The central fact entity — one record per submitted claim.

Key attributes:

• clm_nbr VARCHAR(20) — natural key from billing system
• mbr_id VARCHAR(20) — FK to MEMBER
• billng_prvdr_npi VARCHAR(10) — billing provider NPI — never INTEGER
• rndrng_prvdr_npi VARCHAR(10) — rendering provider NPI
• payer_id VARCHAR(20) — FK to PAYER
• svc_dt_from DATE — service start date
• svc_dt_to DATE — service end date
• prim_diag_cd VARCHAR(7) — primary diagnosis code — never INTEGER
• icd_vrsn VARCHAR(5) — ICD9 or ICD10
• bld_amt DECIMAL(12,2) — billed amount
• paid_amt DECIMAL(12,2) — paid amount
• clm_sts_cd VARCHAR(20) — claim status

2. MEMBER

The member entity with SCD Type 2 for plan changes.

• mbr_id VARCHAR(20) — natural key
• mbr_dob DATE — date of birth (PHI)
• mbr_gndr_cd VARCHAR(1)
• plan_cd VARCHAR(20)
• grp_nbr VARCHAR(20)
• eff_dt DATE — SCD Type 2 effective date
• exp_dt DATE — SCD Type 2 expiration date (NULL = current record)
• curr_rec_flg BOOLEAN

3. PROVIDER

Role-playing dimension used as both billing and rendering provider.

• npi_nbr VARCHAR(10) — always VARCHAR, never INTEGER
• prvdr_last_nm VARCHAR(100)
• prvdr_first_nm VARCHAR(100)
• prvdr_spclty_cd VARCHAR(10)
• prvdr_taxnmy_cd VARCHAR(10)
• npi_sts_cd VARCHAR(1)

4. PAYER

The insurance payer entity.

• payer_id VARCHAR(20)
• payer_nm VARCHAR(200)
• payer_type_cd VARCHAR(20) — Commercial, Medicare, Medicaid

5. SERVICE_LINE

One-to-many child of CLAIM_HEADER.

• clm_nbr VARCHAR(20) — FK to CLAIM_HEADER
• line_nbr INTEGER — sequence number
• proc_cd VARCHAR(10) — CPT/HCPCS code
• proc_modifier_1 VARCHAR(2)
• units_qty DECIMAL(8,2)
• bld_amt DECIMAL(12,2)
• svc_dt DATE

---

Setting Up erwin Naming Standards for Healthcare

Before creating entities, configure the naming standards template to enforce ISO-11179 conventions:

Tools → Naming Standards → Edit

Word Separator: underscore (_)
Case: lowercase

Key healthcare abbreviations to add:
member     → mbr
provider   → prvdr
claim      → clm
service    → svc
diagnosis  → diag
procedure  → proc
amount     → amt
date       → dt
number     → nbr
identifier → id
status     → sts
code       → cd
indicator  → ind
flag       → flg

Applying these ensures that when you create a logical attribute "Member Date of Birth" erwin generates the physical column name mbr_dob automatically.

---

Building the Logical Model in erwin

Step 1: Create entities

1. Right-click diagram → Insert Entity
2. Name: Claim Header (logical name)
3. Add attributes with logical names: "Claim Number", "Member ID", "Billed Amount"
4. Designate primary keys
5. Create relationships between entities

Step 2: Define relationships

CLAIM_HEADER ──< SERVICE_LINE   (one claim, many service lines)
MEMBER ─────── CLAIM_HEADER     (one member, many claims)
PROVIDER ────── CLAIM_HEADER    (role-playing: billing + rendering)
PAYER ──────── CLAIM_HEADER     (one payer, many claims)

Step 3: Use identifying relationships

For SERVICE_LINE, use an identifying relationship to CLAIM_HEADER — the service line cannot exist without the parent claim. This generates a composite primary key (clm_nbr + line_nbr) in the physical model.

---

Generating Physical DDL from erwin

Actions → Forward Engineer → Schema Generation
Target: Snowflake (or SQL Server)
Options:
  → Generate CREATE TABLE statements
  → Include NOT NULL constraints
  → Include PRIMARY KEY constraints
  → Include FOREIGN KEY constraints
  → Include column COMMENTS

Generated DDL example:

CREATE TABLE claim_header (
  clm_key          INTEGER        NOT NULL,
  clm_nbr          VARCHAR(20)    NOT NULL,
  mbr_id           VARCHAR(20)    NOT NULL,
  billng_prvdr_npi VARCHAR(10),
  rndrng_prvdr_npi VARCHAR(10),
  payer_id         VARCHAR(20)    NOT NULL,
  svc_dt_from      DATE           NOT NULL,
  prim_diag_cd     VARCHAR(7),
  icd_vrsn         VARCHAR(5),
  bld_amt          DECIMAL(12,2)  NOT NULL,
  paid_amt         DECIMAL(12,2),
  CONSTRAINT pk_claim_header PRIMARY KEY (clm_key)
);

CREATE TABLE service_line (
  clm_key          INTEGER        NOT NULL,
  line_nbr         INTEGER        NOT NULL,
  proc_cd          VARCHAR(10)    NOT NULL,
  proc_modifier_1  VARCHAR(2),
  units_qty        DECIMAL(8,2),
  bld_amt          DECIMAL(12,2),
  svc_dt           DATE,
  CONSTRAINT pk_svc_line PRIMARY KEY (clm_key, line_nbr),
  CONSTRAINT fk_svc_clm FOREIGN KEY (clm_key)
    REFERENCES claim_header(clm_key)
);

Need to convert to BigQuery, Databricks, or Oracle? Use the free DDL Converter at mdatool.com.

---

Common Healthcare Modeling Mistakes in erwin

1. Storing ICD-10 codes as numeric erwin will default to INTEGER for fields named "Diagnosis Code" unless you explicitly set VARCHAR(7). ICD-10 codes contain letters (A00.1) — storing as INTEGER corrupts them.

2. Storing NPI as numeric NPI is exactly 10 digits but must be VARCHAR(10) — leading zeros are significant and NPI is never used in arithmetic.

3. Missing SCD Type 2 on Member entity Without eff_dt, exp_dt, and curr_rec_flg on the MEMBER entity, historical population analytics will be incorrect.

4. Not using erwin domains Create reusable domains: npi_domain (VARCHAR 10), icd10_domain (VARCHAR 7), amount_domain (DECIMAL 12,2). Apply consistently across all entities.

5. Single-table diagnosis design Consider a separate CLAIM_DIAGNOSIS entity with diag_pos_nbr for cleaner modeling when you need flexible diagnosis code querying.

---

Validate Column Names Against ISO-11179

After generating DDL from erwin, validate all column names using the free Naming Auditor at mdatool.com before deploying to production.

---

Related Resources

• Healthcare Data Glossary — 37,000+ standard terms
• DDL Converter — Snowflake, BigQuery, SQL Server, Oracle
• Naming Auditor — ISO-11179 column name validation
• Metadata Generator — DDL to data dictionary
• Dimensional Modeling for Healthcare
• Healthcare Claims Data Model Guide