Epic Clarity PAT_ENC Table: Columns, Joins, and Query Patterns for Analysts

The Epic Clarity PAT_ENC table holds one row per patient encounter contact, and almost every visit-level report you will validate starts there. It also hides the most common reconciliation trap in Epic reporting: a row in PAT_ENC is not always a visit. This article shows which columns matter, how to join the table safely, and how to prove your counts match what clinicians see in Hyperspace.

I have used PAT_ENC in go-live validation, UAT sign-off, and month-end volume reconciliations. The queries below come from that work. Column names follow the standard Clarity schema, but your organization may expose different columns or versions. Check each one against the Clarity Data Dictionary on Epic UserWeb before you ship a report. If you are new to the database itself, start with the Epic Clarity SQL guide and return here for the encounter table.

What the Epic Clarity PAT_ENC Table Contains

PAT_ENC stores patient encounters. Epic calls each encounter a contact, and every contact receives a contact serial number, abbreviated CSN. The CSN is the primary key. In Clarity it appears as PAT_ENC_CSN_ID, and nearly every encounter-linked table joins back to it.

That grain surprises people who expect one row per visit. A contact exists whenever Epic needs to attach work to a patient on a date. Office visits qualify. So do telephone calls, refill requests, orders-only contacts, documentation-only contacts, and scheduled appointments that never happened. A single patient who calls the clinic, books a slot, cancels, rebooks, and arrives creates several rows.

Chronicles, the transactional database, writes these contacts in real time. Clarity copies them overnight. The distinction matters for timing, and Chronicles and Clarity covers the architecture. For this article, remember one rule: Clarity reflects the state of Chronicles at the last extract, not the current moment.

Why CSN, not MRN, is the key you join on

The medical record number identifies a patient. The CSN identifies one contact. If you join diagnoses to visits on PAT_ID, every diagnosis the patient ever received attaches to every visit. Row counts multiply, and the report looks plausible until someone sums charges. Join on PAT_ENC_CSN_ID whenever the target table has it.

The same identifier maps outward. HL7 FHIR represents an Epic contact as an Encounter resource, and many Epic interface builds carry the CSN as an encounter identifier. The HL7 FHIR Encounter resource defines the status and class values you will compare against when a payer or HIE asks for encounter data.

Grain comparison: patient, encounter, and hospital stay

PAT_ENC_HSP shares the CSN with PAT_ENC and adds hospital-specific fields. Inpatient and emergency analysts live in that pair. Outpatient analysts rarely leave PAT_ENC. Length-of-stay logic sits on the hospital side, and length-of-stay calculations walks through it.

PAT_ENC Columns Analysts Use Most

PAT_ENC carries hundreds of columns. Analysts touch a small subset. The table below lists the columns I reach for first, what they mean, and the trap attached to each.

Category columns end in _C

Epic stores many attributes as numeric category codes. The suffix _C flags them. ENC_TYPE_C and APPT_STATUS_C return numbers until you join to the matching ZC lookup table. Two examples:

Never hard-code the numbers in a production query. A code that means “completed” at your site may differ at another, and a future category change will break your filter without an error. Join to the lookup table and filter on the name, or store the code list in a governed reference table.

Scenario: A Clinic Volume Report That Overcounted Visits by 9%

A multi-specialty group moved its ambulatory clinics onto a new Epic build. The week after go-live, the operations director compared Clarity volumes to the Reporting Workbench visit report. Clarity showed 9 percent more visits in the cardiology clinic. Both reports claimed to count “visits.” Nobody trusted either one.

I was the QA analyst on the validation team. The report developer had written a simple count of PAT_ENC rows by department and date. The Workbench report filtered to completed appointments. The gap was the set of contacts that were not completed visits: telephone encounters, refill requests, and appointments that were canceled or never arrived.

The before query

-- BEFORE: counts every contact, not visits
SELECT  d.DEPARTMENT_NAME,
        CAST(e.CONTACT_DATE AS DATE) AS visit_date,
        COUNT(*)                     AS visits
FROM    PAT_ENC e
JOIN    CLARITY_DEP d ON d.DEPARTMENT_ID = e.DEPARTMENT_ID
WHERE   e.CONTACT_DATE BETWEEN '2026-09-01' AND '2026-09-07'
  AND   d.DEPARTMENT_NAME = 'CARDIOLOGY CLINIC'
GROUP BY d.DEPARTMENT_NAME, CAST(e.CONTACT_DATE AS DATE);

Two defects hide in this query. COUNT(*) counts every contact type. And BETWEEN with a date-only end value drops September 7 contacts that carry a time component. The second defect would have undercounted if the first had not overcounted. Two wrong filters partly canceled out, which is how reports pass a casual review.

The breakdown that found the cause

SELECT  t.NAME  AS enc_type,
        s.NAME  AS appt_status,
        COUNT(*) AS contacts
FROM    PAT_ENC e
JOIN    CLARITY_DEP d        ON d.DEPARTMENT_ID = e.DEPARTMENT_ID
LEFT JOIN ZC_DISP_ENC_TYPE t ON t.DISP_ENC_TYPE_C = e.ENC_TYPE_C
LEFT JOIN ZC_APPT_STATUS s   ON s.APPT_STATUS_C   = e.APPT_STATUS_C
WHERE   e.CONTACT_DATE >= '2026-09-01'
  AND   e.CONTACT_DATE <  '2026-09-08'
  AND   d.DEPARTMENT_NAME = 'CARDIOLOGY CLINIC'
GROUP BY t.NAME, s.NAME
ORDER BY contacts DESC;

The output showed 1,412 office visits with a completed status. It also showed 74 telephone encounters, 31 refill contacts, and 22 canceled appointments. Those 127 rows accounted for the entire gap.

The after query

-- AFTER: completed office visits only, half-open date range
SELECT  d.DEPARTMENT_NAME,
        CAST(e.CONTACT_DATE AS DATE) AS visit_date,
        COUNT(DISTINCT e.PAT_ENC_CSN_ID) AS completed_visits
FROM    PAT_ENC e
JOIN    CLARITY_DEP d        ON d.DEPARTMENT_ID = e.DEPARTMENT_ID
JOIN    ZC_DISP_ENC_TYPE t   ON t.DISP_ENC_TYPE_C = e.ENC_TYPE_C
JOIN    ZC_APPT_STATUS s     ON s.APPT_STATUS_C   = e.APPT_STATUS_C
WHERE   e.CONTACT_DATE >= '2026-09-01'
  AND   e.CONTACT_DATE <  '2026-09-08'
  AND   d.DEPARTMENT_NAME = 'CARDIOLOGY CLINIC'
  AND   t.NAME = 'Office Visit'
  AND   s.NAME = 'Completed'
GROUP BY d.DEPARTMENT_NAME, CAST(e.CONTACT_DATE AS DATE);

The corrected query returned 1,412 and matched the Workbench report. The fix took ten minutes. Agreeing on the definition of “visit” took two meetings. That is the real lesson. The SQL was easy, and the definition was the defect. Write the definition into the report specification before anyone writes a query.

Joining PAT_ENC to Other Clarity Tables

PAT_ENC sits near the middle of the encounter model. Most analysts join outward to four groups of tables: people, places, clinical detail, and money. The diagram below shows the joins I use most. Solid lines carry the CSN. Dashed lines use a different key.

PAT_ENC
key: PAT_ENC_CSN_ID

PATIENT
on PAT_ID

CLARITY_DEP
on DEPARTMENT_ID

CLARITY_SER
on VISIT_PROV_ID = PROV_ID

PAT_ENC_DX
on CSN (many per CSN)

ORDER_PROC
on CSN (many per CSN)

PAT_ENC_HSP
on CSN (one per CSN)

HSP_ACCOUNT
on HSP_ACCOUNT_ID
Teal = who and where. Purple = clinical detail. Orange = hospital and billing.

Fan-out: the join mistake that survives code review

A fan-out happens when you join a one-row-per-encounter table to a many-rows-per-encounter table and then count the result. PAT_ENC joined to PAT_ENC_DX returns one row per diagnosis, not one row per visit. A visit with four diagnoses counts four times.

-- Wrong: counts diagnosis lines
SELECT COUNT(*) FROM PAT_ENC e
JOIN PAT_ENC_DX x ON x.PAT_ENC_CSN_ID = e.PAT_ENC_CSN_ID;

-- Right: counts encounters that have at least one diagnosis
SELECT COUNT(DISTINCT e.PAT_ENC_CSN_ID) FROM PAT_ENC e
JOIN PAT_ENC_DX x ON x.PAT_ENC_CSN_ID = e.PAT_ENC_CSN_ID;

Prefer COUNT(DISTINCT e.PAT_ENC_CSN_ID) as a habit whenever a many-sided table appears in the FROM clause. For primary diagnosis reporting, filter to the primary flag (PRIMARY_DX_YN in standard Clarity) first. Each visit then contributes one row. Diagnosis names come from CLARITY_EDG, which joins on the diagnosis ID.

Five Query Patterns for the Epic Clarity PAT_ENC Table

These patterns cover most of the requests that reach a BA or QA analyst. All use SQL Server syntax. Oracle and Snowflake users swap CAST(... AS DATE) and DATEADD for local equivalents.

Pattern 1: Completed visits by department and month

SELECT  d.DEPARTMENT_NAME,
        DATEFROMPARTS(YEAR(e.CONTACT_DATE), MONTH(e.CONTACT_DATE), 1) AS month_start,
        COUNT(DISTINCT e.PAT_ENC_CSN_ID) AS completed_visits
FROM    PAT_ENC e
JOIN    CLARITY_DEP d      ON d.DEPARTMENT_ID = e.DEPARTMENT_ID
JOIN    ZC_APPT_STATUS s   ON s.APPT_STATUS_C = e.APPT_STATUS_C
WHERE   e.CONTACT_DATE >= '2026-01-01'
  AND   e.CONTACT_DATE <  '2026-10-01'
  AND   s.NAME = 'Completed'
GROUP BY d.DEPARTMENT_NAME,
         DATEFROMPARTS(YEAR(e.CONTACT_DATE), MONTH(e.CONTACT_DATE), 1)
ORDER BY d.DEPARTMENT_NAME, month_start;

Use this as the base query for any volume report. Add the encounter type filter your organization agreed on. The month grouping keeps the output small enough to paste into a UAT evidence document.

Pattern 2: No-show rate by clinic

SELECT  d.DEPARTMENT_NAME,
        SUM(CASE WHEN s.NAME = 'No Show'   THEN 1 ELSE 0 END) AS no_shows,
        SUM(CASE WHEN s.NAME IN ('Completed','No Show') THEN 1 ELSE 0 END) AS resolved_appts,
        CAST(100.0 * SUM(CASE WHEN s.NAME = 'No Show' THEN 1 ELSE 0 END)
             / NULLIF(SUM(CASE WHEN s.NAME IN ('Completed','No Show') THEN 1 ELSE 0 END), 0)
             AS DECIMAL(5,1)) AS no_show_pct
FROM    PAT_ENC e
JOIN    CLARITY_DEP d    ON d.DEPARTMENT_ID = e.DEPARTMENT_ID
JOIN    ZC_APPT_STATUS s ON s.APPT_STATUS_C = e.APPT_STATUS_C
WHERE   e.CONTACT_DATE >= '2026-07-01'
  AND   e.CONTACT_DATE <  '2026-10-01'
GROUP BY d.DEPARTMENT_NAME
ORDER BY no_show_pct DESC;

The denominator is the decision. I use completed plus no-show appointments. Some organizations include late cancellations, and others exclude walk-ins. Document the choice in the report footer, because two leaders will compare your number against a figure from a different tool.

Pattern 3: Contacts with no matching appointment status

SELECT  t.NAME AS enc_type, COUNT(*) AS contacts
FROM    PAT_ENC e
LEFT JOIN ZC_DISP_ENC_TYPE t ON t.DISP_ENC_TYPE_C = e.ENC_TYPE_C
WHERE   e.CONTACT_DATE >= '2026-09-01'
  AND   e.CONTACT_DATE <  '2026-10-01'
  AND   e.APPT_STATUS_C IS NULL
GROUP BY t.NAME
ORDER BY contacts DESC;

This query surfaces everything a status filter would drop. Run it before you finalize any report that filters on APPT_STATUS_C. If a large contact type shows up here that stakeholders expected in the report, the filter is wrong or the requirement is incomplete.

Pattern 4: Encounters with no diagnosis

SELECT  d.DEPARTMENT_NAME, COUNT(*) AS closed_visits_without_dx
FROM    PAT_ENC e
JOIN    CLARITY_DEP d ON d.DEPARTMENT_ID = e.DEPARTMENT_ID
WHERE   e.CONTACT_DATE >= '2026-09-01'
  AND   e.CONTACT_DATE <  '2026-10-01'
  AND   e.ENC_CLOSED_YN = 'Y'
  AND   NOT EXISTS (SELECT 1 FROM PAT_ENC_DX x
                    WHERE x.PAT_ENC_CSN_ID = e.PAT_ENC_CSN_ID)
GROUP BY d.DEPARTMENT_NAME
ORDER BY closed_visits_without_dx DESC;

Closed visits without a diagnosis point to a documentation gap. A broken build, such as a diagnosis association that failed to save, causes the same symptom. Revenue cycle teams care because a missing ICD-10 code can hold a claim. NOT EXISTS avoids the fan-out and handles nulls cleanly.

Pattern 5: Duplicate-looking contacts for one patient and day

SELECT  e.PAT_ID, e.DEPARTMENT_ID, CAST(e.CONTACT_DATE AS DATE) AS contact_day,
        COUNT(*) AS contacts_that_day
FROM    PAT_ENC e
WHERE   e.CONTACT_DATE >= '2026-09-01'
  AND   e.CONTACT_DATE <  '2026-10-01'
GROUP BY e.PAT_ID, e.DEPARTMENT_ID, CAST(e.CONTACT_DATE AS DATE)
HAVING  COUNT(*) > 1
ORDER BY contacts_that_day DESC;

Several rows per patient, department, and day is often normal. A visit plus a telephone follow-up produces two. Five or more is the signal. It suggests a scheduling loop, an interface replaying messages, or a test script that created contacts in the wrong environment.

Decision Tree: Which PAT_ENC Rows Belong in Your Count?

Before writing a filter, walk the request through this tree. It forces the definition conversation that ended the cardiology dispute.

What is the report counting?
Write the definition first

Patient seen in person
Completed visit volume

Demand for access
Scheduling and no-show rates

All patient touchpoints
Workload and outreach

Filter:
ENC_TYPE = Office Visit
APPT_STATUS = Completed
COUNT DISTINCT CSN
Exclude telephone,
canceled, orders-only

Filter:
All scheduled appointments
Group by APPT_STATUS
Denominator: documented
Keep canceled and no-show
Drop null status rows

Filter:
All contacts by ENC_TYPE
Report each type separately
Never one blended number
Label it “contacts,”
not “visits”

Most disputes come from the third branch. Someone builds the all-contacts number, calls it “visits,” and presents it next to a revenue figure that only counts completed visits. Name the metric after what it counts.

PAT_ENC vs PAT_ENC_HSP vs the Caboodle Encounter Fact

Analysts ask which table to use when the question involves inpatient stays or enterprise reporting. The comparison below reflects how I choose.

Use PAT_ENC for validation work because it sits closest to the source. Use Caboodle when a governed data model already answers the question. Validate Caboodle outputs against a PAT_ENC reconciliation query, never the other way around.

Role Checklists: BA, QA, and Data Analyst

Common PAT_ENC Mistakes I See in Reviews

When I Would Use PAT_ENC, and When I Would Not

I use PAT_ENC when I need encounter-level truth: validating a scheduling build, reconciling a Workbench report, or investigating a volume discrepancy. The grain is fine, the keys are stable, and the lookup logic is transparent.

I avoid it for enterprise dashboards that executives refresh daily. A model in Caboodle or a governed warehouse handles those better, because the joins and definitions are written once and tested. I also avoid it for real-time operational needs. Clarity lags Chronicles by hours, so bed management and same-day staffing belong in Chronicles-based reports, not Clarity.

For billing-oriented questions, start with the hospital account tables instead. PAT_ENC links to them, but charge capture and claim status live elsewhere, and a visit count tells you nothing about payment.

How a Contact Reaches PAT_ENC: From Scheduling to Clarity

Understanding the data path saves hours of debugging. A scheduler books an appointment in Cadence, Epic’s scheduling application. Epic creates a contact in Chronicles and assigns the CSN. At check-in, registration updates the same contact. When the clinician opens the encounter, documentation attaches to it. When the provider closes it, billing picks it up.

Clarity sees none of this live. The nightly extract reads Chronicles and writes relational rows. A change made at 4:55 p.m. appears in Clarity the next morning, if the extract completed. When an extract fails, Clarity holds yesterday’s data without any visible error in your query results. Ask your Clarity administrators for the extract completion timestamp and include it in every test report.

Apply that timeline to your test design. A UAT script that books, checks in, and completes a visit in one afternoon should query Clarity the following morning. Testers who query the same afternoon report false failures, and the build team loses a day chasing a defect that does not exist.

Finding One Contact End to End

Aggregate checks tell you that something is wrong. A single-contact trace tells you why. When a stakeholder says “this visit is missing,” trace it in four steps.

First, get the CSN from Hyperspace. Open the encounter, then use the activity or chart header that displays the contact serial number. Your site’s security class decides whether you see it. Second, query PAT_ENC for that CSN and confirm the row exists. Third, check each downstream table for the same CSN. Fourth, compare timestamps against the extract completion time.

DECLARE @csn NUMERIC(18,0) = 123456789;   -- test patient CSN from the UAT script

SELECT 'PAT_ENC' AS src, COUNT(*) AS rows_found FROM PAT_ENC     WHERE PAT_ENC_CSN_ID = @csn
UNION ALL
SELECT 'PAT_ENC_DX',     COUNT(*) FROM PAT_ENC_DX                 WHERE PAT_ENC_CSN_ID = @csn
UNION ALL
SELECT 'ORDER_PROC',     COUNT(*) FROM ORDER_PROC                 WHERE PAT_ENC_CSN_ID = @csn
UNION ALL
SELECT 'PAT_ENC_HSP',    COUNT(*) FROM PAT_ENC_HSP                WHERE PAT_ENC_CSN_ID = @csn;

A CSN in PAT_ENC but not in PAT_ENC_DX means the diagnosis never saved or the extract lags. A CSN absent from PAT_ENC means the contact does not exist in Clarity yet. Both findings point to different owners, and the ticket should name the right one.

Use test patients for this exercise. Never trace a real patient’s CSN for curiosity. Audit logs record the access, and your compliance office will ask why.

Query Performance on a Large PAT_ENC

At a large health system PAT_ENC holds hundreds of millions of rows. A careless query can run for an hour and compete with scheduled reports. Four habits keep analyst queries cheap.

Check with your DBA before running wide date ranges during business hours. Many sites provide a reporting replica or an off-peak window. Respect it. A runaway analyst query on the primary reporting server delays the same morning reports clinicians rely on.

Scenario: A Payer Reconciliation That Started with a Missing CSN

A payer-provider integration team asked me to explain why 212 professional claims for one clinic carried no encounter identifier on the outbound 837 file. The payer rejected them for missing encounter data. Revenue cycle suspected the interface. The interface team suspected the build.

I started in PAT_ENC. I pulled completed office visits for the clinic over the claim period and counted 3,106. Then I compared that list to the claim extract by CSN. The 212 rejected claims shared one pattern: every contact had a null HSP_ACCOUNT_ID and a visit provider who worked under a newly created provider record.

The provider build team had set up the new records without linking billing credentials. The encounter existed and the visit completed, but the claim logic could not resolve the rendering provider. The fix belonged to provider configuration, not the interface. Without the PAT_ENC cross-check, the interface team would have spent days replaying messages.

The query that isolated the pattern was simple.

SELECT  e.VISIT_PROV_ID, s.PROV_NAME, COUNT(*) AS visits_without_account
FROM    PAT_ENC e
LEFT JOIN CLARITY_SER s ON s.PROV_ID = e.VISIT_PROV_ID
WHERE   e.CONTACT_DATE >= '2026-08-01'
  AND   e.CONTACT_DATE <  '2026-09-01'
  AND   e.HSP_ACCOUNT_ID IS NULL
GROUP BY e.VISIT_PROV_ID, s.PROV_NAME
ORDER BY visits_without_account DESC;

Grouping a failure set by every available attribute is the fastest way to find the shared cause. Provider, department, encounter type, and date each took ten seconds. Provider revealed the answer.

One caveat: HSP_ACCOUNT_ID is populated mainly for hospital-based billing. Professional billing for outpatient visits often flows through a different account structure at some sites. Confirm the identifier your organization uses for professional claims before you copy this query.

Edge Cases That Break Clean Logic

Ideal data rarely exists. These five cases account for most of the surprises I have met.

Version differences matter too. Epic adds and deprecates Clarity columns across releases. After every upgrade, run your validation pack against the same date range before and after, then compare results row by row.

Handling PHI When You Query PAT_ENC

PAT_ENC links every contact to a patient. That makes the data protected health information under HIPAA once PAT_ID sits beside dates and departments. The HHS minimum necessary standard requires you to limit use and disclosure to what the task needs. For a volume reconciliation, you need counts, not names.

Aggregate in SQL. Never export row-level results to a laptop for a count check. Mask or hash identifiers in screenshots attached to Jira tickets. Use non-production Clarity copies for test queries when your site provides them. The HIPAA-safe Clarity SQL article covers access requests, audit logging, and how to document your query purpose.

A Reconciliation Pack for UAT Sign-Off

I run the same four checks before every sign-off that involves encounter data. Save them as a script and attach the output to the test evidence.

-- Check 4 helper: row count must not change across a one-to-one join
SELECT  (SELECT COUNT(*) FROM PAT_ENC
         WHERE CONTACT_DATE >= '2026-09-01' AND CONTACT_DATE < '2026-10-01') AS base_rows,
        (SELECT COUNT(*) FROM PAT_ENC e
         JOIN CLARITY_DEP d ON d.DEPARTMENT_ID = e.DEPARTMENT_ID
         WHERE e.CONTACT_DATE >= '2026-09-01' AND e.CONTACT_DATE < '2026-10-01') AS joined_rows;

If the two numbers differ, the join dropped rows. That is your first lead, usually a null department or an inner join that should be a LEFT JOIN. Resolve it before you look at anything else.

Questions Analysts Ask About PAT_ENC

Is PAT_ENC the same as a visit table?

No. It stores every contact, and a visit is one kind of contact. Filter on encounter type and status to approximate visits, and write that definition down.

What is the difference between a CSN and a HAR?

A CSN identifies one contact. A hospital account record, abbreviated HAR, identifies a billing account that can span several contacts. An inpatient stay often has many CSNs under one HAR.

Which column holds the encounter date?

CONTACT_DATE holds the contact date as a datetime. APPT_TIME holds the scheduled time. Hospital admit and discharge times sit in PAT_ENC_HSP. Pick the date that matches the business question.

Can I use PAT_ENC in Caboodle?

Not directly. Caboodle models encounters in its own fact and dimension tables. Use PAT_ENC to validate those tables, not to replace them.

How do I get the full column list?

Open the Clarity Data Dictionary on Epic UserWeb and search for the table, or query the system catalog in your database. The dictionary adds descriptions and category value lists that the catalog lacks.

Run This Before Your Next Go-Live Sign-Off

Pick one report that counts visits. Run Pattern 3, the null-status query, for the last full month. Compare the large contact types it returns against the report’s filter. If any large type is missing and nobody can explain why, the report undercounts. That check takes ten minutes and finds more defects than a day of screen-by-screen testing.

Sources and Further Reading

Primary sources for the standards referenced above:

• HL7 FHIR R4: Encounter resource. Defines encounter status and class values used when mapping Epic contacts to external systems.
• HHS: Minimum Necessary Requirement. Official HIPAA guidance on limiting PHI use in analytics queries.
• Epic Clarity Data Dictionary and Epic Data Model documentation on Epic UserWeb (requires an Epic customer login). Treat it as the authority for column names in your version.

Every table name in this article is a standard Clarity name. Column availability and category values vary by site and Epic version, so confirm them in your own dictionary before you deploy any query.