Epic's Data Architecture: From Chronicles to Clarity

Purpose: To understand why Epic’s data looks the way it does by tracing its journey from hierarchical to relational.

The Tale of Two Databases

To understand Epic’s EHI export, you must first understand Epic’s unique dual-database architecture. Unlike most modern systems that use a single database, Epic runs two completely different databases in parallel:

1. Chronicles: The real-time hierarchical database where clinicians work
2. Clarity: The relational reporting database that feeds analytics

This isn’t a design quirk—it’s a deliberate architecture that balances the competing needs of speed (for patient care) and structure (for reporting). Let’s explore how this shapes everything you see in the EHI export.

Chronicles: The MUMPS Legacy

Epic’s Chronicles database runs on technology older than the Internet itself. Born in 1966 at Massachusetts General Hospital, MUMPS (Massachusetts General Hospital Utility Multi-Programming System) was revolutionary for its time. It combined a programming language with an integrated database—a radical idea when RAM was measured in kilobytes.

See evidence of hierarchical data in relational form

View SQL Query

-- Count tables that use the LINE pattern
SELECT COUNT(DISTINCT table_name) as tables_with_line_column
FROM _metadata
WHERE column_name = 'LINE';

tables_with_line_column
275

That LINE column? It’s Chronicles’ way of handling multiple values for a single concept—what MUMPS calls “multiple responses.” In a hierarchical database, you don’t create separate tables for one-to-many relationships; you create numbered lines within the same record.

How Chronicles Stores Data

In Chronicles, data lives in Master Files (called INIs), each containing records with:

• Item Number: The unique identifier for the record
• Items: Individual data fields, which can be single-valued or multi-valued

Here’s how this translates to what you see:

Observe hierarchical patterns in account contacts

View SQL Query

SELECT 
    ACCOUNT_ID,
    LINE,
    LETTER_NAME as contact_name,
    CONTACT_STATUS_C_NAME,
    CONTACT_DATE
FROM ACCOUNT_CONTACT
WHERE ACCOUNT_ID IN (
    SELECT ACCOUNT_ID 
    FROM ACCOUNT_CONTACT 
    GROUP BY ACCOUNT_ID 
    HAVING COUNT(*) > 1
)
ORDER BY ACCOUNT_ID, LINE
LIMIT 10;

ACCOUNT_ID	LINE	contact_name	CONTACT_STATUS_C_NAME	CONTACT_DATE
1810018166	1		Inquiry	8/13/2018 12:00:00 AM
1810018166	2		Inquiry	8/13/2020 12:00:00 AM
1810018166	3		Inquiry	8/13/2020 12:00:00 AM
1810018166	4		Inquiry	9/20/2022 12:00:00 AM
1810018166	5		Account Status Changed	10/5/2022 12:00:00 AM
1810018166	6		Inquiry	10/5/2022 12:00:00 AM
1810018166	7		Demand Bill	10/6/2022 12:00:00 AM
1810018166	8		Account Note Made	10/6/2022 12:00:00 AM
1810018166	9	ZAPL SMALL BALANCE LETTER ; EAR: 1810018166	Letter	10/6/2022 12:00:00 AM
1810018166	10		Defer in Account WQ	10/6/2022 12:00:00 AM

In Chronicles, this would be stored as a single account record with multiple contact “lines.” The ETL process flattens this into rows, preserving the line numbers.

The Nightly Transformation: ETL

Every night, Epic performs a massive Extract, Transform, Load (ETL) operation:

DiagramCode

Loading diagram...

This process:

1. Extracts data from Chronicles’ hierarchical structure
2. Transforms it into relational tables (creating those LINE columns)
3. Loads it into Clarity’s 18,000+ SQL Server tables

The result? Yesterday’s real-time data becomes today’s analytics.

Why Two Databases?

This architecture seems complex, but it solves real problems:

See the scale that demands this architecture

View SQL Query

-- Epic must handle massive transaction volumes
SELECT 
    'Orders' as data_type, COUNT(*) as record_count FROM ORDER_PROC
UNION ALL
SELECT 'Results', COUNT(*) FROM ORDER_RESULTS
UNION ALL
SELECT 'Medications', COUNT(*) FROM ORDER_MED
UNION ALL
SELECT 'Encounters', COUNT(*) FROM PAT_ENC
ORDER BY record_count DESC;

data_type	record_count
Encounters	111
Results	27
Orders	25
Medications	4

Chronicles excels at:

• Lightning-fast lookups for individual patients
• Real-time data entry during patient care
• Handling constantly changing clinical workflows
• Supporting hundreds of simultaneous users

Clarity excels at:

• Complex queries across millions of records
• Population health analytics
• Financial reporting
• Research datasets

Chronicles Artifacts in Your Data

Understanding Chronicles helps explain many “quirks” in the EHI export:

Discover Epic's internal date system

View SQL Query

-- Epic stores dates as days since 12/31/1840
SELECT 
    column_name,
    COUNT(DISTINCT table_name) as table_count,
    GROUP_CONCAT(DISTINCT table_name) as appears_in_tables
FROM _metadata
WHERE column_name LIKE '%_REAL'
GROUP BY column_name
ORDER BY table_count DESC
LIMIT 5;

column_name	table_count	appears_in_tables
CONTACT_DATE_REAL	48	ABN_FOLLOW_UP,ACCOUNT_CREATION,CAREPLAN_CNCT_INFO,CARE_INTEGRATOR,CL_QANSWER_OVTM,CL_QQUEST_OVTM,DOCS_RCVD_ALGS,DOCS_RCVD_ALGS_CMT,DOCS_RCVD_ALG_REAC,DOCS_RCVD_ASMT,DOCS_RCVD_PROC,EPISODE_OT,GOAL_CONTACT,HSP_CLAIM_PRINT,HSP_CLP_REV_CODE,IMM_ADMIN,IMM_ADMIN_COMPONENTS,IMM_ADMIN_GROUPS,IMM_ADMIN_GROUPS_FT,IMM_DUE,MDL_HISTORY,MED_CVG_ALTERNATIVES,MED_CVG_DETAILS,MED_CVG_DX_VALUE,MED_CVG_ESTIMATE_VALS,MED_CVG_RESPONSE_RSLT,MED_CVG_RESP_RSLT_DETAIL,MED_CVG_STATUS_DETAILS,MED_CVG_USERACTION,MED_DISPENSE_SIG,NOTE_CONTENT_INFO,NOTE_ENC_INFO,NOTE_ENC_INFO_2,OBS_MTHD_ID,ORDER_DISP_INFO,ORDER_DISP_INFO_2,ORDER_DISP_INFO_3,ORDER_DOCUMENTS,ORDER_READ_ACK,ORDER_RES_COMMENT,ORD_RSLT_COMPON_ID,PROB_UPDATES,PT_GOALS_UPDATES,RECONCILE_CLAIM_STATUS,RECONCILE_CLM_OT,SDD_ENTRIES,TIMEOUT_ANSWERS,TIMEOUT_ANSWERS_2
PAT_ENC_DATE_REAL	41	AN_RELINK_INFO,APPT_LETTER_RECIPIENTS,ASSOCIATED_REFERRALS,CLARITY_ADT,DISCONTINUED_MEDS,ECHKIN_STEP_INFO,FAM_HX_PAT_ONLY,HOMUNCULUS_PAT_DATA,HV_ORDER_PROC,KIOSK_QUESTIONNAIR,MED_PEND_APRV_STAT,MYC_APPT_QNR_DATA,MYC_MESG,OPH_EXAM_DATA,ORDER_DX_MED,ORDER_DX_PROC,ORDER_MED,ORDER_MEDINFO,ORDER_PROC,PATIENT_ENC_VIDEO_VISIT,PAT_ADDENDUM_INFO,PAT_CR_TX_SINGLE,PAT_ENC,PAT_ENC_3,PAT_ENC_6,PAT_ENC_7,PAT_ENC_CURR_MEDS,PAT_ENC_DISP,PAT_ENC_DOCS,PAT_ENC_DX,PAT_ENC_ELIG_HISTORY,PAT_ENC_HSP_2,PAT_ENC_SEL_PHARMACIES,PAT_MYC_MESG,PAT_REVIEW_ALLERGI,PAT_REVIEW_DATA,PAT_REVIEW_PROBLEM,PAT_UCN_CONVERT,PAT_UTILIZATION_REVIEW,SURGICAL_HX,TREATMENT
ORD_DATE_REAL	4	ORDER_IMPRESSION,ORDER_NARRATIVE,ORDER_RESULTS,ORDER_STATUS
FLO_CNCT_DATE_REAL	2	IP_FLWSHT_EDITED,IP_FLWSHT_MEAS
SVC_DATE_REAL	1	REFERRAL

These _REAL columns contain Epic’s internal date format—the number of days since December 31, 1840. Why 1840? It’s a MUMPS convention that predates Unix time by decades.

The Hidden Hierarchy

Even in relational form, you can see Chronicles’ hierarchical DNA:

Trace hierarchical relationships through shared IDs

View SQL Query

-- Find tables that share the same base record structure
WITH base_tables AS (
    SELECT DISTINCT 
        SUBSTR(table_name, 1, LENGTH(table_name) - 2) as base_name,
        table_name
    FROM _metadata
    WHERE table_name LIKE '%\_2' ESCAPE '\'
       OR table_name LIKE '%\_3' ESCAPE '\'
       OR table_name LIKE '%\_4' ESCAPE '\'
)
SELECT 
    base_name,
    GROUP_CONCAT(table_name, ', ') as related_tables,
    COUNT(*) as table_count
FROM base_tables
GROUP BY base_name
HAVING table_count > 1
ORDER BY table_count DESC
LIMIT 10;

base_name	related_tables	table_count
REFERRAL	REFERRAL_2, REFERRAL_3, REFERRAL_4	3
PAT_ENC	PAT_ENC_2, PAT_ENC_3, PAT_ENC_4	3
PATIENT	PATIENT_2, PATIENT_3, PATIENT_4	3
ORDER_PROC	ORDER_PROC_2, ORDER_PROC_3, ORDER_PROC_4	3
ORDER_MED	ORDER_MED_2, ORDER_MED_3, ORDER_MED_4	3
HSP_ACCOUNT	HSP_ACCOUNT_2, HSP_ACCOUNT_3, HSP_ACCOUNT_4	3
CLM_VALUES	CLM_VALUES_2, CLM_VALUES_3, CLM_VALUES_4	3
SVC_LN_INFO	SVC_LN_INFO_2, SVC_LN_INFO_3	2
ORDER_DISP_INFO	ORDER_DISP_INFO_2, ORDER_DISP_INFO_3	2
HSP_TRANSACTIONS	HSP_TRANSACTIONS_2, HSP_TRANSACTIONS_3	2

Tables like ACCOUNT, ACCOUNT_2, ACCOUNT_3 aren’t separate entities—they’re continuations of the same Chronicles record, split because SQL Server has column limits that Chronicles doesn’t respect.

From INI to Table

Chronicles master files (INIs) map directly to Clarity tables:

Common Epic master file prefixes

View SQL Query

SELECT 
    SUBSTR(table_name, 1, 3) as prefix,
    COUNT(*) as table_count,
    GROUP_CONCAT(DISTINCT table_name) as example_tables
FROM (
    SELECT table_name
    FROM _metadata
    WHERE column_name IS NULL
      AND table_name NOT LIKE '\_%' ESCAPE '\'
    ORDER BY table_name
    LIMIT 200
)
GROUP BY SUBSTR(table_name, 1, 3)
HAVING COUNT(*) >= 3
ORDER BY table_count DESC
LIMIT 10;

prefix	table_count	example_tables
CLA	33	CLAIMS_DERIVE_PAT_FLAGS,CLAIM_INFO,CLAIM_INFO2,CLAIM_INFO3,CLAIM_INFO_3,CLARITY_ADT,CLARITY_COMPONENT,CLARITY_DEP,CLARITY_DEP_4,CLARITY_EAP,CLARITY_EAP_3,CLARITY_EAP_5,CLARITY_EDG,CLARITY_EEP,CLARITY_EMP,CLARITY_EPM,CLARITY_EPP,CLARITY_FSC,CLARITY_HM_TOPIC,CLARITY_IMMUNZATN,CLARITY_LLB,CLARITY_LOC,CLARITY_LOC_2,CLARITY_LOT,CLARITY_LWS,CLARITY_LWS_3,CLARITY_MEDICATION,CLARITY_MOD,CLARITY_NRG,CLARITY_PRC,CLARITY_RMC,CLARITY_SA,CLARITY_SER
CL_	26	CL_COL_AGNCY,CL_ELG,CL_LQH,CL_ORD_FST_LST_SCH,CL_OTL,CL_QANSWER,CL_QANSWER_OVTM,CL_QFORM1,CL_QQUEST_OVTM,CL_REMIT,CL_RMT_CLM_DT_INFO,CL_RMT_CLM_ENTITY,CL_RMT_CLM_INFO,CL_RMT_DELIVER_MTD,CL_RMT_HC_RMK_CODE,CL_RMT_INP_ADJ_INF,CL_RMT_OPT_ADJ_INF,CL_RMT_PRV_SUM_INF,CL_RMT_PRV_SUP_INF,CL_RMT_SVCE_LN_INF,CL_RMT_SVC_AMT_INF,CL_RMT_SVC_DAT_INF,CL_RMT_SVC_LVL_ADJ,CL_RMT_SVC_LVL_REF,CL_RSN_FOR_VISIT,CL_UB_REV_CODE
ARP	15	ARPB_AUTH_INFO,ARPB_CHG_ENTRY_DX,ARPB_PMT_RELATED_DENIALS,ARPB_TRANSACTIONS,ARPB_TRANSACTIONS2,ARPB_TRANSACTIONS3,ARPB_TX_ACTIONS,ARPB_TX_CHG_REV_HX,ARPB_TX_MATCH_HX,ARPB_TX_MODERATE,ARPB_TX_MODIFIERS,ARPB_TX_STMCLAIMHX,ARPB_TX_STMT_DT,ARPB_TX_VOID,ARPB_VISITS
DOC	12	DOCS_FOR_HOSP_ACCT,DOCS_RCVD_ALGS,DOCS_RCVD_ALGS_CMT,DOCS_RCVD_ALG_REAC,DOCS_RCVD_ASMT,DOCS_RCVD_PROC,DOC_CSN_REFS,DOC_INFORMATION,DOC_INFORMATION_2,DOC_INFO_DICOM,DOC_LINKED_PATS,DOC_LINKED_PAT_CSNS
CLM	11	CLM_ALL,CLM_DX,CLM_INJURY_DESC,CLM_NOTE,CLM_OTHER_DXS,CLM_VALUES,CLM_VALUES_2,CLM_VALUES_3,CLM_VALUES_4,CLM_VALUES_5,CLM_VALUE_RECORD
ACC	11	ACCOUNT,ACCOUNT_2,ACCOUNT_3,ACCOUNT_CONTACT,ACCOUNT_CONTACT_2,ACCOUNT_CREATION,ACCT_ADDR,ACCT_COVERAGE,ACCT_GUAR_PAT_INFO,ACCT_HOME_PHONE_HX,ACCT_TX
HSP	7	HSP_ACCOUNT,HSP_ACCOUNT_2,HSP_ACCOUNT_3,HSP_ACCOUNT_4,HSP_ACCT_ADJ_LIST,HSP_ACCT_ADMIT_DX,HSP_ACCT_ATND_PROV
COV	7	COVERAGE,COVERAGE_2,COVERAGE_3,COVERAGE_BENEFITS,COVERAGE_COPAY_ECD,COVERAGE_MEMBER_LIST,COVERAGE_SPONSOR
CAR	6	CAREPLAN_CNCT_INFO,CAREPLAN_ENROLLMENT_INFO,CAREPLAN_INFO,CAREPLAN_PT_TASK_INFO,CARE_INTEGRATOR,CARE_PATH
HM_	5	HM_ENC_DATE,HM_FORECAST_INFO,HM_HISTORICAL_STATUS,HM_HISTORY,HM_PLAN_INFO

Each prefix typically represents a Chronicles master file:

• PAT: Patient master file
• HSP: Hospital accounts
• ORD: Orders
• CLA: Clarity-specific tables

The Performance Trade-off

This dual architecture creates a 24-hour lag but enables both systems to excel at their jobs. Real-time clinical care happens in Chronicles. Historical analysis happens in Clarity. The EHI export gives you the Clarity view—comprehensive but not instantaneous.

Many tables include update tracking columns that show when data was last refreshed from Chronicles, though these are often empty in EHI exports.

Working With the Architecture

Understanding this architecture helps you:

1. Interpret LINE patterns: When you see numbered lines, think “this was a multi-valued field in Chronicles”
2. Handle split tables: PATIENT, PATIENT_2, etc. are one logical record
3. Understand timing: Data is always at least one day old due to ETL
4. Recognize Chronicles types: _YN for yes/no, _C for category lists, _REAL for internal dates

---

Key Takeaways

• Epic uses two databases: Chronicles (hierarchical, real-time) and Clarity (relational, analytical)
• The EHI export comes from Clarity after nightly ETL processing
• MUMPS, created in 1966 at Mass General, still powers Epic’s core database
• The LINE column pattern (in 275 tables) reveals Chronicles’ hierarchical structure
• Tables ending in _2, _3, etc. are continuations of the same logical record
• Understanding this architecture explains many “quirks” in the data model