Epic Clarity SQL for Analysts: Common Tables, Query Patterns, and Data Validation

A build validation that relies only on clicking through Hyperspace catches what’s visible on screen and misses everything underneath it — a diagnosis code mapped to the wrong category, an order type missing from a report’s underlying filter, a duplicate encounter inflating a volume count. Epic Clarity SQL lets analysts query the same relational data Epic’s own reports pull from, directly, which is the only way to validate a build change at the data level instead of trusting the UI to be honest about what happened underneath. This guide covers the core Clarity tables every BA and QA analyst should recognize, the query patterns that come up constantly during UAT and go-live validation, and the mistakes that turn a five-minute reconciliation query into a half-day debugging session.

What Is Epic Clarity, and Why Analysts Query It Directly

Clarity is Epic’s relational reporting database, refreshed on a nightly ETL cycle from the live Chronicles transactional system. It is not real-time — a query against Clarity reflects data as of the last nightly load, which matters enormously when validating same-day build changes. For a full comparison of Clarity against Epic’s newer dimensional warehouse, Epic Caboodle data warehouse covers the architecture differences in depth. This guide focuses specifically on Clarity, since it remains the standard for ad hoc validation queries during build testing.

Core Clarity Tables Every Analyst Should Know

A small set of tables accounts for the large majority of validation queries a BA or QA analyst writes against Clarity.

Table	Grain	What It Holds
PATIENT	One row per patient	Demographics, MRN, identifiers
PAT_ENC	One row per encounter (CSN)	Encounter-level data — date, department, provider
PAT_ENC_HSP	One row per hospital encounter	Inpatient-specific encounter attributes (admit/discharge)
PAT_ENC_DX	One row per diagnosis per encounter	Diagnosis codes attached to a visit
ORDER_PROC	One row per procedure order	Labs, imaging, and procedure orders (CPOE)
ORDER_MED	One row per medication order	Medication orders tied to an encounter
CLARITY_SER	One row per provider	Provider (serving) master record
CLARITY_EDG	One row per diagnosis code	ICD-10 diagnosis dictionary
ZC_* tables	One row per category value	Category/lookup dictionaries (status, type, class codes)

The ZC_ prefix marks Epic’s category dictionary tables — every coded value in Clarity (encounter type, order status, diagnosis type) resolves through one of these. A query returning a category ID instead of a readable label almost always means a missing join to the relevant ZC_ table.

How These Tables Relate

PAT_ENC
one row per CSN

PATIENT
demographics, MRN

CLARITY_SER
provider record

PAT_ENC_DX
diagnosis \u2192 CLARITY_EDG

ORDER_PROC
labs, imaging, procedures

PAT_ENC_HSP
inpatient admit/discharge

PAT_ENC is the hub almost every validation query starts from. Everything else — patient identity, diagnoses, orders, provider — joins back to it through the encounter’s CSN (contact serial number), which brings up the single most common grain mistake covered later in this guide.

Common Query Patterns

Validating a New Order Type Build

After a build adds a new order type, the standard validation checks whether orders are being placed correctly and categorized under the right procedure category.

SELECT op.order_proc_id, op.description, zc.name AS proc_category,
       op.order_time
FROM ORDER_PROC op
JOIN ZC_PROC_CAT zc ON op.proc_cat_c = zc.proc_cat_c
WHERE op.proc_id = 123456
  AND op.order_time >= '2026-08-01'
ORDER BY op.order_time DESC;

The join to ZC_PROC_CAT is what turns a raw category ID into a readable category name — skip it, and the validation query technically runs but returns nothing a reviewer can actually confirm against the build spec.

Encounter-Level Validation for Go-Live

Go-live volume validation typically reconciles encounter counts between a legacy system extract and Clarity, filtered to a specific department and date range.

SELECT COUNT(DISTINCT pe.pat_enc_csn_id) AS encounter_count,
       pe.department_id
FROM PAT_ENC pe
WHERE pe.contact_date BETWEEN '2026-08-01' AND '2026-08-07'
  AND pe.department_id = 10450
GROUP BY pe.department_id;

COUNT(DISTINCT ...) on the CSN matters here — without it, a patient with multiple charge lines or diagnosis rows tied to the same encounter can inflate the count well past the true visit total.

Diagnosis Code Validation After a Build Change

A common UAT check after a build change to diagnosis mapping confirms every diagnosis code attached to recent encounters resolves to a valid, current ICD-10 entry.

SELECT ped.pat_enc_csn_id, ped.dx_id, edg.current_icd10_list
FROM PAT_ENC_DX ped
LEFT JOIN CLARITY_EDG edg ON ped.dx_id = edg.dx_id
WHERE edg.dx_id IS NULL
  AND ped.dx_date >= '2026-08-01';

The LEFT JOIN combined with a null check on the right-hand table is the standard pattern for finding orphaned records — diagnosis entries with no matching dictionary row, which is exactly the kind of silent data-integrity gap a build change can introduce without anyone noticing in the UI.

Data Validation Techniques Beyond the SELECT Statement

Writing a query that returns rows is the easy part. Real validation compares what should be true against what actually is.

Technique	What It Catches
Row-count reconciliation	Missing or duplicated records between two systems or two time points
Orphaned record checks (LEFT JOIN + NULL)	Foreign key references pointing at dictionary entries that don’t exist
Null-rate spot checks on required fields	Build changes that silently stopped populating a field
Before/after count comparison on a build change	Unexpected volume shifts a build change wasn’t supposed to cause

Common Mistakes and Edge Cases

Querying PAT_ENC without a date filter. This table grows to tens of millions of rows in any established health system. A query without a contact_date boundary can time out or lock resources other analysts are relying on — always filter by date range first, then narrow further.

Forgetting Clarity isn’t real-time. Clarity refreshes on a nightly batch cycle. Validating a same-day build change against Clarity before that night’s ETL run will show stale or missing data that has nothing to do with the actual defect — always confirm the last refresh time before treating a Clarity result as current.

Confusing encounter grain with patient grain. Counting rows in PAT_ENC_DX or ORDER_PROC directly, instead of counting distinct CSNs, silently inflates any count where a single encounter has multiple diagnoses or orders — a frequent source of “the numbers don’t match” discrepancies between two seemingly identical queries.

Assuming ZC_ category IDs match across environments. Category dictionary values are not guaranteed to have identical IDs between a test environment and production, especially after a recent build. Hardcoding a category ID from one environment into a query meant for another is a documented source of silently wrong results.

When I’d Use Raw Clarity SQL vs. Reporting Workbench vs. Caboodle

Tool	Best For
Raw Clarity SQL	Ad hoc UAT/go-live validation, one-off reconciliation, defect investigation
Epic Reporting Workbench	Recurring operational reports for end users who don’t write SQL
Caboodle	Large-scale, performance-sensitive analytics across long date ranges

Raw Clarity SQL is the right choice specifically because UAT and go-live validation is one-off, investigative work — building a Reporting Workbench template or waiting on a Caboodle model for a question you’ll ask once doesn’t make sense. The moment a validation query needs to run on a recurring schedule for non-technical stakeholders, that’s the signal to migrate it out of ad hoc SQL and into Reporting Workbench instead.

Quick Reference Checklist by Role

The next time a build validation only checks what’s visible in Hyperspace, add one reconciliation query against Clarity before signing off. The defects that matter most — a missing category mapping, an orphaned diagnosis reference, a silently excluded visit type — are exactly the ones that never show up until someone counts the rows.

---

Further reading: CMS’s official ICD-10 coding resources govern the diagnosis code standards referenced in this guide. For the formal query language specification, see the ISO/IEC 9075 (SQL:2016) standard maintained by ISO and ANSI.