Epic Clarity Data Model Guide for Healthcare Data Engineers

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

Enter fullscreen mode Exit fullscreen mode

_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;

Enter fullscreen mode Exit fullscreen mode

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;

Enter fullscreen mode Exit fullscreen mode

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;

Enter fullscreen mode Exit fullscreen mode

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;

Enter fullscreen mode Exit fullscreen mode

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

Enter fullscreen mode Exit fullscreen mode

# 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']

Enter fullscreen mode Exit fullscreen mode

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;

Enter fullscreen mode Exit fullscreen mode

🟡 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;

Enter fullscreen mode Exit fullscreen mode

🔴 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);

Enter fullscreen mode Exit fullscreen mode

🔴 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;

Enter fullscreen mode Exit fullscreen mode

🟡 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;

Enter fullscreen mode Exit fullscreen mode

Migrating Clarity to Snowflake — 3-Step Architecture