Modeling History and Change: The _HX Pattern

Purpose: To understand Epic’s comprehensive approach to tracking data changes over time.

Healthcare’s Audit Trail Imperative

In healthcare, knowing not just what data is current but how it changed over time can be critical. Who updated a diagnosis? When was an address changed? What was the patient’s insurance last year? Epic’s _HX pattern provides complete audit history for critical data elements.

Discover the scope of history tracking

View SQL Query

SELECT 
    COUNT(*) as history_tables,
    GROUP_CONCAT(REPLACE(name, '_HX', ''), ', ') as tracked_entities
FROM sqlite_master 
WHERE type = 'table' 
  AND name LIKE '%_HX'
ORDER BY name;

history_tables	tracked_entities
23	ACCT_HOME_PHONE, ARPB_TX_CHG_REV, ARPB_TX_MATCH, ARPB_TX_STMCLAIMHX, FAMILY, FRONT_END_PMT_COLL, GUAR_ACCT_STMT, GUAR_ADDR, GUAR_PMT_SCORE_PB, HSP_BKT_NAA_ADJ, MEDICAL, MEDS_REV, ORDER_RPTD_SIG, PATIENT_ALG_UPD, PAT_ADDR_CHNG, PAT_MEDS, PAT_RELATIONSHIP_LIST, PROBLEM_LIST, PROB_LIST_REV, SOCIAL_ADL, SOCIAL, SURGICAL, UNIV_CHG_LN_MSG

With 23 history tables, Epic tracks changes to everything from problem lists to addresses, medications to insurance coverage.

Anatomy of a History Table

History tables mirror their parent tables but add crucial temporal tracking:

Compare a regular table to its history counterpart

View SQL Query

-- First, see a current address
SELECT 
    PAT_ID,
    LINE,
    ADDRESS
FROM PAT_ADDRESS
WHERE PAT_ID = 'Z7004242';

PAT_ID	LINE	ADDRESS
Z7004242	1	REDACTED

See the address change history

View SQL Query

-- Then see the address history with full details
SELECT 
    PAT_ID,
    LINE,
    EFF_START_DATE,
    EFF_END_DATE,
    ADDR_HX_LINE1,
    CITY_HX,
    ZIP_HX
FROM PAT_ADDR_CHNG_HX
WHERE PAT_ID = 'Z7004242'
ORDER BY EFF_START_DATE;

PAT_ID	LINE	EFF_START_DATE	EFF_END_DATE	ADDR_HX_LINE1	CITY_HX	ZIP_HX
Z7004242	1	7/17/2018 12:00:00 AM	8/9/2018 12:00:00 AM	REDACTED	MADISON	REDACTED
Z7004242	2	8/9/2018 12:00:00 AM		REDACTED	MADISON	REDACTED

The key additions in _HX tables:

• EFF_START_DATE: When this version became active
• EFF_END_DATE: When this version was replaced (NULL = currently active)
• LINE: Tracks multiple historical records

The Active Record Rule

A NULL or empty EFF_END_DATE indicates the currently active record:

Understand the active record pattern

View SQL Query

SELECT 
    PAT_ID,
    LINE,
    EFF_START_DATE,
    EFF_END_DATE,
    CASE 
        WHEN EFF_END_DATE IS NULL OR EFF_END_DATE = '' 
        THEN '✓ Currently Active'
        ELSE 'Historical'
    END as record_status
FROM PAT_ADDR_CHNG_HX
ORDER BY PAT_ID, EFF_START_DATE;

PAT_ID	LINE	EFF_START_DATE	EFF_END_DATE	record_status
Z7004242	1	7/17/2018 12:00:00 AM	8/9/2018 12:00:00 AM	Historical
Z7004242	2	8/9/2018 12:00:00 AM		✓ Currently Active

This pattern is identical to data warehousing’s Type 2 Slowly Changing Dimension, where:

• New records are added rather than updating existing ones
• Effective dates track the validity period
• The current record has no end date

Point-in-Time Queries

The real power of _HX tables is reconstructing data as it existed at any moment:

Find a patient's address on a specific date

View SQL Query

-- What was the patient's address on August 1, 2018?
SELECT 
    PAT_ID,
    ADDR_HX_LINE1,
    CITY_HX,
    ZIP_HX,
    EFF_START_DATE,
    EFF_END_DATE,
    'Note: Sample data may not contain historical addresses' as note
FROM PAT_ADDR_CHNG_HX
LIMIT 5;

PAT_ID	ADDR_HX_LINE1	CITY_HX	ZIP_HX	EFF_START_DATE	EFF_END_DATE	note
Z7004242	REDACTED	MADISON	REDACTED	7/17/2018 12:00:00 AM	8/9/2018 12:00:00 AM	Note: Sample data may not contain historical addresses
Z7004242	REDACTED	MADISON	REDACTED	8/9/2018 12:00:00 AM		Note: Sample data may not contain historical addresses

Common History Tracking Patterns

Let’s explore what Epic typically tracks in history tables:

Examine different types of historical data

View SQL Query

SELECT 
    table_name,
    COUNT(*) as column_count,
    GROUP_CONCAT(column_name, ', ') as example_columns
FROM _metadata
WHERE table_name LIKE '%_HX'
   OR table_name LIKE '%_HX_%'
   OR table_name LIKE '%HIST%'
   OR table_name LIKE '%CHNG%'
GROUP BY table_name
ORDER BY table_name
LIMIT 10;

table_name	column_count	example_columns
ACCT_HOME_PHONE_HX	6	ACCOUNT_ID, CHANGE_DATE, CHANGE_SOURCE_C_NAME, LINE, PHONE_NUMBER
ARPB_TX_CHG_REV_HX	10	CR_HX_ACTIVITY_C_NAME, CR_HX_CONT_LINE_YN, CR_HX_DATE, CR_HX_TIME, CR_HX_USER_COMMENT, CR_HX_USER_ID, CR_HX_USER_ID_NAME, LINE, TX_ID
ARPB_TX_MATCH_HX	21	LINE, MTCH_TX_HX_AMT, MTCH_TX_HX_COMMENT, MTCH_TX_HX_DSUSR_ID, MTCH_TX_HX_DSUSR_ID_NAME, MTCH_TX_HX_DT, MTCH_TX_HX_DTTM, MTCH_TX_HX_D_CVG_ID, MTCH_TX_HX_ID, MTCH_TX_HX_INS_AMT, MTCH_TX_HX_INV_NUM, MTCH_TX_HX_LINE, MTCH_TX_HX_PAT_AMT, MTCH_TX_HX_UDUSR_ID, MTCH_TX_HX_UDUSR_ID_NAME, MTCH_TX_HX_UN_COM, MTCH_TX_HX_UN_CV_ID, MTCH_TX_HX_UN_DT, MTCH_TX_HX_UN_DTTM, TX_ID
ARPB_TX_STMCLAIMHX	21	BC_HX_ACCEPT_DATE, BC_HX_ACCEPT_DTTM, BC_HX_AMOUNT, BC_HX_AR_CLASS_C_NAME, BC_HX_ASSIGNED_YN, BC_HX_AUX_PROC, BC_HX_BO_PROC_ID, BC_HX_CLM_DB_ID, BC_HX_COVERAGE_ID, BC_HX_DATE, BC_HX_FIRST_CLM_FLG, BC_HX_HELD_AMOUNT, BC_HX_INVOICE_NUM, BC_HX_PAYMENT_AMT, BC_HX_PAYMENT_DATE, BC_HX_PAYOR_ID, BC_HX_RESUBMIT_DATE, BC_HX_TYPE_C_NAME, LINE, TX_ID
FAMILY_HX	13	AGE_OF_ONSET, AGE_OF_ONSET_END, COMMENTS, FAM_HX_SRC_C_NAME, FAM_MEDICAL_DX_ID, FAM_MED_REL_ID, FAM_RELATION_NAME, LINE, MEDICAL_HX_C_NAME, MEDICAL_OTHER, PAT_ENC_CSN_ID, RELATION_C_NAME
FAMILY_HX_STATUS	29	CONTACT_DATE, FAM_HX_FERT_NOTE, FAM_HX_FERT_STAT_C_NAME, FAM_STAT_ADOPT_C_NAME, FAM_STAT_ADPT_PAR_1, FAM_STAT_ADPT_PAR_2, FAM_STAT_COD_C_NAME, FAM_STAT_COMMENT, FAM_STAT_DEATH_AGE, FAM_STAT_DELIV_EPISODE_ID, FAM_STAT_DOB_DT, FAM_STAT_DOB_END_DT, FAM_STAT_FATHER_ID, FAM_STAT_GENDER_IDENTITY_C_NAME, FAM_STAT_ID, FAM_STAT_IDENT_TWIN, FAM_STAT_MOTHER_ID, FAM_STAT_NAME, FAM_STAT_PREG_EPISODE_ID, FAM_STAT_REL_C_NAME, FAM_STAT_REL_ID, FAM_STAT_SEX_C_NAME, FAM_STAT_SRC_C_NAME, FAM_STAT_STATUS_C_NAME, FAM_STAT_TWIN, HX_LNK_ENC_CSN, LINE, PAT_ENC_CSN_ID
FAM_HX_PAT_ONLY	6	CONTACT_DATE, FAM_HX_FERT_STAT_C_NAME, FAM_HX_FERT_STAT_NOTES, PAT_ENC_CSN_ID, PAT_ENC_DATE_REAL
FRONT_END_PMT_COLL_HX	31	COLL_INSTANT_UTC_DTTM, COLL_WORKFLOW_TYPE_C_NAME, CONTACT_DATE, ENC_DEPARTMENT_ID, EVENT_TYPE_C_NAME, GUARANTOR_ACCOUNT_ID, HB_COPAY_COLL, HB_COPAY_DUE, HB_COPAY_PAID, HB_PREPAY_COLL, HB_PREPAY_DUE, HB_PREPAY_PAID, HB_PREV_BAL_COLL, HB_PREV_BAL_DUE, HB_PREV_BAL_PAID, LINE, LOGIN_DEPARTMENT_ID, PAT_ENC_CSN_ID, PB_COPAY_COLL, PB_COPAY_DUE, PB_COPAY_PAID, PB_PREPAY_COLL, PB_PREPAY_DUE, PB_PREPAY_PAID, PB_PREV_BAL_COLL, PB_PREV_BAL_DUE, PB_PREV_BAL_PAID, PREPAY_DISCOUNT_OFFERED, RSN_NON_COLL_AMT_COMMENT, RSN_NON_COLL_AMT_C_NAME
GUAR_ACCT_STMT_HX	16	ACCOUNT_ID, LINE, STMT_HX_1ST_VW_DTTM, STMT_HX_DVRY_MTHD_C_NAME, STMT_HX_INVOICE_NUM, STMT_HX_LST_VW_DTTM, STMT_HX_NEW_BALANCE, STMT_HX_NEW_CHARGE, STMT_HX_STMT_DATE, STMT_HX_TTL_AMT_HLD, STMT_HX_TTL_AMT_VD, STMT_HX_TTL_CR_ADJ, STMT_HX_TTL_DB_ADJ, STMT_HX_TTL_PMT, STMT_HX_WHY_2_PR_C_NAME
GUAR_ADDR_HX	11	ACCOUNT_ID, ADDR_CHANGE_DATE, ADDR_CHANGE_SRC_C_NAME, ADDR_HX_1, ADDR_HX_2, ADDR_HX_EXTRA, CITY_HX, LINE, STATE_HX_C_NAME, ZIP_HX

History tables fall into several categories:

• Clinical History: Problem lists, medications, surgical procedures
• Administrative History: Addresses, phone numbers, insurance
• Financial History: Guarantor information, payment arrangements
• Audit History: User updates, system changes

The Audit Trail

Many _HX tables include audit columns to track who made changes:

Identify audit tracking columns

View SQL Query

SELECT 
    column_name,
    COUNT(DISTINCT table_name) as table_count
FROM _metadata
WHERE column_name LIKE '%_INST%'
   OR column_name LIKE '%_DTTM'
   OR column_name LIKE '%_USER%'
GROUP BY column_name
ORDER BY table_count DESC
LIMIT 10;

column_name	table_count
ENTRY_USER_ID_NAME	8
ENTRY_USER_ID	8
EDIT_USER_ID_NAME	4
EDIT_USER_ID	4
REC_CREATE_USER_ID_NAME	3
REC_CREATE_USER_ID	3
TAKEN_USER_ID_NAME	2
TAKEN_USER_ID	2
REVIEW_USER_ID_NAME	2
REVIEW_USER_ID	2

Common audit columns include:

• User who made the change
• Timestamp of the change
• System or interface that triggered the update

History Table Patterns

Analyze history table structures

View SQL Query

WITH hx_patterns AS (
    SELECT 
        table_name,
        CASE 
            WHEN column_name = 'LINE' THEN 'Has LINE'
            WHEN column_name LIKE '%EFF_START%' THEN 'Has EFF_START'
            WHEN column_name LIKE '%EFF_END%' THEN 'Has EFF_END'
            WHEN column_name LIKE '%UPDATE%USER%' THEN 'Has UPDATE_USER'
            ELSE NULL
        END as pattern
    FROM _metadata
    WHERE table_name LIKE '%_HX'
)
SELECT 
    pattern,
    COUNT(DISTINCT table_name) as table_count,
    GROUP_CONCAT(DISTINCT table_name) as example_tables
FROM hx_patterns
WHERE pattern IS NOT NULL
GROUP BY pattern
ORDER BY table_count DESC;

pattern	table_count	example_tables
Has LINE	21	ACCT_HOME_PHONE_HX,ARPB_TX_CHG_REV_HX,ARPB_TX_MATCH_HX,ARPB_TX_STMCLAIMHX,FAMILY_HX,FRONT_END_PMT_COLL_HX,GUAR_ACCT_STMT_HX,GUAR_ADDR_HX,GUAR_PMT_SCORE_PB_HX,HSP_BKT_NAA_ADJ_HX,MEDICAL_HX,ORDER_RPTD_SIG_HX,PATIENT_ALG_UPD_HX,PAT_ADDR_CHNG_HX,PAT_MEDS_HX,PAT_RELATIONSHIP_LIST_HX,PROBLEM_LIST_HX,PROB_LIST_REV_HX,SOCIAL_ADL_HX,SURGICAL_HX,UNIV_CHG_LN_MSG_HX
Has EFF_START	1	PAT_ADDR_CHNG_HX
Has EFF_END	1	PAT_ADDR_CHNG_HX

Best Practices for History Queries

1. Always Consider Time Windows

-- Find all addresses for a patient during 2018
SELECT * FROM PAT_ADDR_CHNG_HX
WHERE PAT_ID = 'Z7004242'
  AND (EFF_START_DATE < '2019-01-01'
       AND (EFF_END_DATE >= '2018-01-01' OR EFF_END_DATE IS NULL));

2. Handle NULL End Dates

-- Safe date comparison with NULL handling
WHERE start_date <= target_date
  AND (end_date > target_date OR end_date IS NULL OR end_date = '')

3. Join to Current Data Carefully

-- Get current data with its history
SELECT
    c.*,
    h.EFF_START_DATE as current_since
FROM current_table c
JOIN history_table h ON c.ID = h.ID
WHERE h.EFF_END_DATE IS NULL;

History vs. Current Tables

Not all data needs history tracking. Epic’s approach is selective:

Compare tables with and without history tracking

View SQL Query

WITH all_tables AS (
    SELECT 
        name as table_name,
        CASE 
            WHEN name LIKE '%_HX' THEN 'History Table'
            WHEN EXISTS (
                SELECT 1 FROM sqlite_master sm2
                WHERE sm2.name = sm.name || '_HX'
            ) THEN 'Has History Table'
            ELSE 'No History Tracking'
        END as history_status
    FROM sqlite_master sm
    WHERE type = 'table' AND name != '_metadata'
)
SELECT 
    history_status,
    COUNT(*) as table_count
FROM all_tables
GROUP BY history_status
ORDER BY table_count DESC;

history_status	table_count
No History Tracking	525
History Table	23
Has History Table	2

---

Key Takeaways

• The _HX suffix identifies history tables that track changes over time
• EFF_START_DATE and EFF_END_DATE define when each version was active
• NULL or empty EFF_END_DATE indicates the currently active record
• This pattern implements Type 2 Slowly Changing Dimensions from data warehousing
• 23 history tables track critical clinical, administrative, and financial changes
• Point-in-time queries let you reconstruct data as it existed at any moment
• Not all tables have history tracking—Epic selectively applies it where audit trails matter most