Understanding the _metadata Table

Purpose: To master Epic’s built-in documentation system that makes the 551-table database navigable and understandable.

Epic’s Built-In Documentation

Every Epic EHI export includes comprehensive documentation for its tables and columns. Epic publishes this documentation at https://open.epic.com/EHITables, and a copy is included with each patient’s export. We’ve loaded this documentation into SQLite as the _metadata table, making it queryable right alongside your actual health data.

This single table transforms an overwhelming database into something you can explore systematically. It contains Epic’s official explanations for every table and column:

Your first look at the _metadata table structure

View SQL Query

SELECT 
    table_name,
    column_name,
    SUBSTR(documentation, 1, 100) || '...' as documentation_preview
FROM _metadata
WHERE table_name = 'PATIENT' 
  AND column_name = 'PAT_ID'
LIMIT 1;

table_name	column_name	documentation_preview
PATIENT	PAT_ID	The unique ID of the patient record for this row. This column is frequently used by other tables to ...

How _metadata Works

The table has three columns that document the entire database:

Understanding the _metadata structure

View SQL Query

SELECT 
    COUNT(DISTINCT table_name) as documented_tables,
    COUNT(*) as total_documentation_entries,
    COUNT(CASE WHEN column_name IS NULL THEN 1 END) as table_descriptions,
    COUNT(CASE WHEN column_name IS NOT NULL THEN 1 END) as column_descriptions
FROM _metadata;

documented_tables	total_documentation_entries	table_descriptions	column_descriptions
548	9747	548	9199

• table_name: The table being documented
• column_name: The specific column (NULL for table-level documentation)
• documentation: Epic’s explanation of purpose and usage

Table-Level Documentation

When column_name is NULL, you get the table’s overall purpose:

Discover what key tables do

View SQL Query

SELECT 
    table_name,
    SUBSTR(documentation, 1, 150) as table_purpose
FROM _metadata
WHERE column_name IS NULL
  AND table_name IN ('PATIENT', 'PAT_ENC', 'ORDER_MED', 'CLARITY_EDG')
ORDER BY table_name;

table_name	table_purpose
CLARITY_EDG	The CLARITY_EDG table contains basic information about diagnoses. Primary key: DX_ID.
ORDER_MED	The ORDER_MED table enables you to report on medications ordered in EpicCare (prescriptions). We have also included patient and contact identification
PATIENT	The PATIENT table contains one record for each patient in your system. The data contained in each record consists of demographics, PCP and primary loc
PAT_ENC	The patient encounter table contains one record for each patient encounter in your system. By default, this table does not contain Registration or PCP

The Essential Documentation Query

This query generates complete documentation for any table by combining SQLite’s schema information with Epic’s documentation:

Get full documentation for any table

View SQL Query

-- Replace 'PAT_ENC' with any table name
WITH table_info AS (
    SELECT name as column_name, cid as column_order
    FROM pragma_table_info('PAT_ENC')
)
SELECT 
    ti.column_name,
    COALESCE(m.documentation, '(No documentation available)') as description
FROM table_info ti
LEFT JOIN _metadata m 
    ON m.table_name = 'PAT_ENC' 
    AND m.column_name = ti.column_name
ORDER BY ti.column_order;

column_name	description
PAT_ID	The unique ID assigned to the patient record. This ID may be encrypted if you have elected to use enterprise reporting�s encryption utility.
PAT_ENC_DATE_REAL	A unique contact date in decimal format. The integer portion of the number indicates the date of contact. The digits after the decimal distinguish different contacts on the same date and are unique for each contact on that date. For example, .00 is the first/only contact, .01 is the second contact, etc.
PAT_ENC_CSN_ID	A unique serial number for this encounter. This number is unique across all patients and encounters in the system.
CONTACT_DATE	The date of this contact in calendar format.
PCP_PROV_ID	The unique ID of the provider record for the patient�s General Primary Care Provider as of the date of the encounter. This ID may be encrypted if you have elected to use enterprise reporting�s security utility.
FIN_CLASS_C_NAME	The category value associated with the Financial Class of the encounter. Note: This item is only populated through an interface. It is not populated if you have billing system installed.
VISIT_PROV_ID	The unique ID for the visit provider associated with this encounter. In cases where there are multiple providers for one encounter, this is the ID of the first provider in the list. This item may be NULL if there is no provider for this encounter. This ID may be encrypted.
VISIT_PROV_TITLE_NAME	The visit provider�s provider title (SER 5). See VISIT_PROV_ID above for the definition of visit provider.
DEPARTMENT_ID	The ID of the department for the encounter. If there are multiple departments for the encounter, this is the ID of the first department in the list.
LMP_DATE	The date of the patient�s Last Menstrual Period. Only contains data for encounters with female patients.
ENC_CLOSED_YN	A flag that signifies if this encounter is closed as of the time of the enterprise reporting extract. This column will have the value Y, N or null. Null indicates that closing the encounter does not apply, such as a future appointment.
ENC_CLOSED_USER_ID_NAME	The name of the user record. This name may be hidden.
ENC_CLOSED_USER_ID	The unique ID of the system user who closed the patient encounter. This ID may be encrypted.
ENC_CLOSE_DATE	The date on which the patient encounter was closed.
LOS_MODIFIER1_ID_MODIFIER_NAME	The name of the modifier record.
LOS_MODIFIER1_ID	The first Level of Service modifier applied to the encounter. This item will appear empty if no modifier is present.
LOS_MODIFIER2_ID_MODIFIER_NAME	The name of the modifier record.
LOS_MODIFIER2_ID	The second Level of Service modifier applied to the encounter. This item will appear empty if no modifier is present.
LOS_MODIFIER3_ID_MODIFIER_NAME	The name of the modifier record.
LOS_MODIFIER3_ID	The third Level of Service modifier applied to the encounter. This item will appear empty if no modifier is present.
LOS_MODIFIER4_ID_MODIFIER_NAME	The name of the modifier record.
LOS_MODIFIER4_ID	The fourth Level of Service modifier applied to the encounter. This item will appear empty if no modifier is present.
APPT_STATUS_C_NAME	The category value associated with the appointment status of the encounter as of the most recent enterprise reporting extract, such as 1 � Scheduled, 2 � Completed, 3 � Canceled, etc.
APPT_CANC_USER_ID_NAME	The name of the user record. This name may be hidden.
APPT_CANC_USER_ID	The unique ID of the user who canceled the appointment.
CHECKIN_USER_ID_NAME	The name of the user record. This name may be hidden.
CHECKIN_USER_ID	The unique ID of the system user who checked in the patient for this encounter. If the encounter has not been through the Check In process this field will be NULL. This ID may be encrypted.
HOSP_ADMSN_TIME	The date and time that the patient was first admitted to the facility, bedded in the ED, or confirmed for an HOV for this contact, regardless of patient's base patient class.
HOSP_DISCHRG_TIME	The hospital discharge date and time for this patient contact.
HOSP_ADMSN_TYPE_C_NAME	The category value for the type of admission for this encounter.
NONCVRED_SERVICE_YN	A flag used to indicate whether the appointment is scheduled in a service not covered by the patient's coverage benefits. The flag is set to "Y" when the service is not covered and an "N" when it is covered.
REFERRAL_REQ_YN	A flag used to indicate whether an appointment requires a referral as determined by the visit coverage. This flag is set to �Y� when the appointment requires a referral. If the appointment does not require a referral, it is set to �N."
REFERRAL_ID	The unique ID of the referral record linked to this appointment.
ACCOUNT_ID	The ID number of the guarantor account assigned to the visit at the time it is scheduled or when it is checked in. This ID may be encrypted.
COVERAGE_ID	The ID number of the coverage record assigned to the visit at the time it is scheduled or when it is checked in. This ID may be encrypted.
CLAIM_ID	The unique ID of the billing system Claim record (CLM record) linked to charges associated with this visit.
PRIMARY_LOC_ID	The unique ID of the patient�s primary location as of the contact date of the encounter. Note: This may not be the same as the patient�s current primary location.
CHARGE_SLIP_NUMBER	The encounter form number or charge slip number assigned to this encounter. Note: The charge slip number is also stored in the financial table CLARITY_TDL. You can use this field to link to CLARITY_TDL to identify financial transactions associated with the encounter.
COPAY_DUE	The dollar amount shown in the Copay Due field of the scheduling system's Check In Patient activity. This amount may be calculated by the system using the patient's coverage benefit information or be manually entered by a user. This field may also be empty if no copay amount was entered when the patient's appointment was checked in.
UPDATE_DATE	The time this patient encounter was pulled into enterprise reporting.
HSP_ACCOUNT_ID	The ID number of the hospital billing account assigned to the encounter.
ADM_FOR_SURG_YN	Indicates whether the patient is being admitted for surgery.
SURGICAL_SVC_C_NAME	The category value corresponding to the surgical service for this patient contact.
INPATIENT_DATA_ID	The ID number of the record used to determine how inpatient data is stored for the encounter.
IP_EPISODE_ID	The ID number of the inpatient episode of care. This includes discharges from the ED.
EXTERNAL_VISIT_ID	The ID for the contact as assigned by a non-system. Usually populated by an interface.
CONTACT_COMMENT	Comments entered by the provider for the contact.
OUTGOING_CALL_YN	Indicates whether a call associated with a telephone encounter was initiated by the patient or by the clinic / hospital. A "Y" indicates an outgoing call placed by the clinic / hospital while an "N" indicates and incoming call from the patient.
DATA_ENTRY_PERSON	This is the name of the user who created the encounter.
REFERRAL_SOURCE_ID_REFERRING_PROV_NAM	The name of the referral source.
REFERRAL_SOURCE_ID	The referral ID number of the referring physician. This physician may be from an external organization.
WC_TPL_VISIT_C_NAME	A field used to indicate whether the patient's contact is related to workers compensation or third party liability situation.
CONSENT_TYPE_C_NAME	This item describes the type of consent that was filed for a given encounter. It is a single-response customer-defined category.
BMI	This is the patient's Body Mass Index, which is calculated based on the recorded height and weight.
BSA	This is the patient's Body Surface Area, which is calculated based on the recorded height and weight.
AVS_PRINT_TM	The instant that the After Visit Summary (AVS) was printed for this encounter.
AVS_FIRST_USER_ID_NAME	The name of the user record. This name may be hidden.
AVS_FIRST_USER_ID	Unique ID of the user who first prints out the After Visit Summary (AVS) for the encounter.
ENC_MED_FRZ_RSN_C_NAME	The encounter medication freeze reason's category value.
EFFECTIVE_DATE_DT	The date of the encounter. The returned date is handled differently depending on the contact type of the encounter: If it is a surgery encounter, the date of the surgery will be returned. If it is a Hospital encounter, Admission/Discharge/Transfer (ADT) info will be used to return an appropriate date. If ADT info cannot be found, then the Hospital Admission date (I EPT 18850) will be returned. If the Hospital Admission Date cannot be found, the temporary admission date (I EPT 18846) will be returned..
DISCHARGE_DATE_DT	The discharge date for the encounter.
COPAY_PD_THRU_NAME	The method by which the copay for an appointment was paid (e.g., via MyChart, a kiosk).
INTERPRETER_NEED_YN	A flag used to indicate whether a patient requires an interpreter for an encounter.
VST_SPECIAL_NEEDS_C_NAME	This field captures any special needs for a visit.
BEN_ENG_SP_AMT	Stores the adjudicated self-pay amount (the amount required to be paid by the patient) when determining the copay amount for the visit.
BEN_ADJ_COPAY_AMT	Stores the adjudicated copy amount for the visit according to the patient's coverage benefits.
BEN_ADJ_METHOD_C_NAME	Flag to indicate if and how the adjudicated copay was overridden
ENC_CREATE_USER_ID_NAME	The name of the user record. This name may be hidden.
ENC_CREATE_USER_ID	The ID number of the user who create the patient or encounter record.
ENC_INSTANT	The instant an encounter was created
EFFECTIVE_DATE_DTTM	The start date and time of an encounter. The start date is pulled from the date stored in the EFFECTIVE_DATE_DT column. The time references the first populated time in the following fields: hospital admission time (EPT 18851), hospital temporary admission time (EPT 18847), ADT arrival time (EPT 10815), and expected admission time (EPT 10300). The SlicerDicer reporting application uses this column to determine the EffectiveStartDate of encounters.
CALCULATED_ENC_STAT_C_NAME	A status flag used to determine whether to include data from the encounter in the SlicerDicer reporting application. Statuses includes 1-Possible (e.g., the encounter is a scheduled outpatient appointment or the admission is pending) or 2-Complete (e.g., the appointment is complete, the admission is discharged).

Finding Tables by Healthcare Concept

Search documentation by keyword to discover relevant tables:

Find all tables related to diagnoses

View SQL Query

SELECT DISTINCT
    table_name,
    COUNT(*) as relevant_columns
FROM _metadata
WHERE LOWER(documentation) LIKE '%diagnos%'
  AND column_name IS NOT NULL
GROUP BY table_name
ORDER BY relevant_columns DESC
LIMIT 10;

table_name	relevant_columns
HSP_CLAIM_DETAIL1	24
HSP_ACCT_DX_LIST	15
HSP_ACCT_EXTINJ_CD	11
PAT_ENC_DX	11
CLM_VALUES_4	8
ARPB_TRANSACTIONS	6
CL_RMT_INP_ADJ_INF	6
CL_RMT_PRV_SUP_INF	6
CLM_DX	5
ORDER_DX_PROC	5

Try other medical concepts: '%medication%', '%insurance%', '%allergy%', or '%appointment%'.

Locating Specific Data Elements

Need to find where a specific type of data lives? Search column documentation:

Find all date/time columns related to admission

View SQL Query

SELECT 
    table_name,
    column_name,
    SUBSTR(documentation, 1, 100) as description
FROM _metadata
WHERE column_name LIKE '%ADMSN%' 
   OR (LOWER(documentation) LIKE '%admission%' AND column_name LIKE '%_DT%')
ORDER BY table_name, column_name
LIMIT 10;

table_name	column_name	description
ADT_ORDER_INFORMATION	ADT_EXPECT_ADMSN_DATE	Stores the date the patient is expected to be admitted.
CLM_VALUES_2	ADMSN_DT	The admission date on the claim. For outpatient claims, this date represents the visit or start of c
CLM_VALUES_2	ADMSN_DX	The admission diagnosis for the claim.
CLM_VALUES_2	ADMSN_DX_QUAL	The qualifier that identifies the code set for the admission diagnosis for the claim.
CLM_VALUES_2	ADMSN_SRC	The admission source for the claim.
CLM_VALUES_2	ADMSN_TM	The time at which the patient was admitted to the facility. This time is only available for institut
CLM_VALUES_2	ADMSN_TYP	The admission type for the claim.
CLM_VALUES_2	CLM_FROM_DT	The earliest date represented on the claim. This date could be the minimum service date for an outpa
CLM_VALUES_3	ADMSN_QUAL	The qualifier to identify when the admission hour is reported along with the admission date.
ED_IEV_EVENT_INFO	ADT_EVENT_ID	The unique ID of the Admission, Transfer, Discharge, or Leave of Absence (ADT) event record link tha

Discovering Data Relationships

The _metadata table reveals how tables connect through shared columns:

Find foreign key relationships for patients

View SQL Query

SELECT 
    table_name,
    COUNT(*) as patient_linked_columns
FROM _metadata
WHERE column_name = 'PAT_ID'
  AND table_name != 'PATIENT'
GROUP BY table_name
ORDER BY table_name
LIMIT 15;

table_name	patient_linked_columns
ACCT_GUAR_PAT_INFO	1
ALLERGY_FLAG	1
ANTICOAG_SELF_REGULATING	1
BENEFITS	1
CLAIMS_DERIVE_PAT_FLAGS	1
CLARITY_ADT	1
CL_REMIT	1
COMMUNITY_RESRC_REVIEWED	1
COVERAGE_MEMBER_LIST	1
EXT_DATA_LAST_DONE	1
HAR_ALL	1
HM_ENC_DATE	1
HM_FORECAST_INFO	1
HM_HISTORICAL_STATUS	1
HM_HISTORY	1

Every table with a PAT_ID column links back to the PATIENT table. This pattern—shared column names indicating relationships—is consistent throughout Epic.

Documentation Coverage

Epic provides remarkably complete documentation:

Analyze metadata completeness

View SQL Query

WITH all_tables AS (
    SELECT name as table_name
    FROM sqlite_master
    WHERE type = 'table' AND name != '_metadata'
),
documented_tables AS (
    SELECT DISTINCT table_name
    FROM _metadata
)
SELECT 
    (SELECT COUNT(*) FROM all_tables) as total_tables,
    (SELECT COUNT(*) FROM documented_tables) as documented_tables,
    ROUND(
        100.0 * (SELECT COUNT(*) FROM documented_tables) / 
        (SELECT COUNT(*) FROM all_tables), 
        1
    ) as documentation_percentage,
    -- List any undocumented tables
    GROUP_CONCAT(
        CASE 
            WHEN at.table_name NOT IN (SELECT table_name FROM documented_tables)
            THEN at.table_name
        END
    ) as undocumented_tables
FROM all_tables at;

total_tables	documented_tables	documentation_percentage	undocumented_tables
550	548	99.6	ALT_BPA_ACT_TASK,RECURRING_BILLING_INFO

With 99.6% coverage, nearly every table and column has official documentation.

Practical Documentation Strategies

1. Search by Column Pattern

Epic uses consistent naming conventions:

• _YN for yes/no flags
• _C_NAME for category names
• _ID for identifiers
• _REAL for internal date format
• _AMT for currency amounts

2. Combine Documentation with Data

Documentation explains intent; actual data shows implementation:

First, get the documentation

View SQL Query

SELECT documentation 
FROM _metadata 
WHERE table_name = 'PAT_ENC' AND column_name = 'CONTACT_DATE';

documentation
The date of this contact in calendar format.

Then see actual data patterns

View SQL Query

SELECT 
    CONTACT_DATE,
    COUNT(*) as encounters_on_date
FROM PAT_ENC
WHERE CONTACT_DATE IS NOT NULL
GROUP BY CONTACT_DATE
ORDER BY encounters_on_date DESC
LIMIT 5;

CONTACT_DATE	encounters_on_date
9/28/2023 12:00:00 AM	4
8/9/2018 12:00:00 AM	4
7/21/2020 12:00:00 AM	4
8/29/2022 12:00:00 AM	3
7/15/2020 12:00:00 AM	3

3. Build Custom Views

Create your own documentation views for frequently used tables, combining Epic’s explanations with your notes about how the data appears in practice.

Working with Undocumented Elements

While rare, some columns may lack documentation. When this happens:

1. Examine the column name for patterns
2. Look at actual data values
3. Check related columns in the same table
4. Search for similar columns in other tables

---

Key Takeaways

• The _metadata table contains Epic’s official documentation from open.epic.com/EHITables
• Every EHI export includes this documentation, loaded here as a queryable table
• Use table-level documentation (where column_name IS NULL) to understand table purposes
• Search documentation by keyword to discover relevant tables for any healthcare concept
• Shared column names (like PAT_ID) indicate relationships between tables
• 99.6% documentation coverage makes this an extremely reliable resource
• Combine documentation queries with actual data sampling for complete understanding

---