Epic — Sources — Binom Governance

Copy formulas into the KPI Definition Card, then adapt grain and filters.

Example

Encounters count

How many encounters occurred in the period?

COUNT(*) FROM encounter WHERE admitDateTime IN period

Grain: Encounter
Fields: Encounter.id, Encounter.admitDateTime, Encounter.departmentId, Encounter.class
Dimensions: department, encounter_type

Only completed/valid encounters (status=finished) for reporting KPIs.

Adapt: Segment by encounter class (inpatient/outpatient/emergency).

Example

Appointments completed

How many appointments were attended in the period?

COUNT(*) FROM appointment WHERE status = 'arrived' AND start IN period

Grain: Attended appointment
Fields: Appointment.id, Appointment.status, Appointment.start, Appointment.departmentId
Dimensions: department, provider_specialty

Clarify arrived vs fulfilled status per Epic configuration.

Adapt: Report telehealth vs in-person separately when available.

No-show rate

What share of appointments ended as a no-show?

COUNT(*) FILTER (WHERE status = 'noshow') / COUNT(*) FROM appointment WHERE start IN period

Grain: Appointment (aggregated)
Fields: Appointment.status, Appointment.start, Appointment.departmentId
Dimensions: department, provider_specialty

Do not mix cancelled-before-appointment with no-show.

Adapt: Break down by weekday/time slot for scheduling optimization.

Average length of stay

What is the average length of stay for inpatient encounters?

AVG(dischargeDateTime - admitDateTime) FROM encounter WHERE class = 'inpatient' AND dischargeDateTime IN period

Grain: Inpatient encounter (aggregated)
Fields: Encounter.admitDateTime, Encounter.dischargeDateTime, Encounter.class, Encounter.departmentId
Dimensions: department

Only aggregated LOS values in marts — no individual encounter exports with patient link.

Adapt: Handle outliers (LOS > 30 days) separately to avoid skewing the average.

Diagnosis code frequency (aggregate)

How frequently did diagnosis codes occur in the period (aggregated)?

COUNT(*) FROM diagnosis_code dc JOIN encounter e ON e.id = dc.encounterId WHERE e.admitDateTime IN period GROUP BY dc.icdCode HAVING COUNT(*) >= :k_anonymity_threshold

Grain: Diagnosis code (aggregated, k-anonymous)
Fields: DiagnosisCode.icdCode, DiagnosisCode.encounterId, Encounter.admitDateTime
Dimensions: department, diagnosis_category

Only report categories with a minimum case count (k-anonymity) to avoid re-identification.

Adapt: Aggregate to ICD chapter level instead of full diagnosis when case counts are low.

Formula overview

Same formulas as a table — copy into KPI cards or SQL drafts.

Label	Formula	Grain	Fields
Encounters count	`COUNT(*) FROM encounter WHERE admitDateTime IN period`	Encounter	Encounter.id, Encounter.admitDateTime, Encounter.departmentId, Encounter.class
Appointments completed	`COUNT(*) FROM appointment WHERE status = 'arrived' AND start IN period`	Attended appointment	Appointment.id, Appointment.status, Appointment.start, Appointment.departmentId
No-show rate	`COUNT() FILTER (WHERE status = 'noshow') / COUNT() FROM appointment WHERE start IN period`	Appointment (aggregated)	Appointment.status, Appointment.start, Appointment.departmentId
Average length of stay	`AVG(dischargeDateTime - admitDateTime) FROM encounter WHERE class = 'inpatient' AND dischargeDateTime IN period`	Inpatient encounter (aggregated)	Encounter.admitDateTime, Encounter.dischargeDateTime, Encounter.class, Encounter.departmentId
Diagnosis code frequency (aggregate)	`COUNT() FROM diagnosis_code dc JOIN encounter e ON e.id = dc.encounterId WHERE e.admitDateTime IN period GROUP BY dc.icdCode HAVING COUNT() >= :k_anonymity_threshold`	Diagnosis code (aggregated, k-anonymous)	DiagnosisCode.icdCode, DiagnosisCode.encounterId, Encounter.admitDateTime

Id	Label	Grain	Notes
`department`	Department	department.id / name	Primary slice for volume and utilization KPIs.
`encounter_type`	Encounter type	encounter.class	Separate inpatient/outpatient/emergency for LOS and volume.
`provider_specialty`	Provider specialty	provider.specialty	Specialty rollups for utilization and workload.
`diagnosis_category`	Diagnosis category	ICD code category (chapter level)	Only aggregated category level in default marts — observe k-anonymity.

PII hotspots

Inventory direct and quasi identifiers for this source — tag, restrict RAW, hash or drop before marts.

Entity	Fields	Classification	Stage treatment	Treatment
Patient demographics	`mrn, birthDate, name, address, ssn, phone`	Sensitive	RAW: strictly restrict (PHI); Curated: tokenized only; Mart: aggregates only	PHI strict — never land MRN/DOB/name/address/SSN in analytics marts; tokenized id only.
Clinical notes / progress notes	`note text, progress note body, discharge summary text`	Sensitive	RAW: never store; Curated: never; Mart: never	Never land clinical notes in the analytics warehouse — highest PHI sensitivity.
Diagnosis / procedure identifiers linked to patient	`icdCode + patientId, cptCode + patientId`	Sensitive	RAW: strictly restrict; Curated: k-anonymous aggregates only; Mart: aggregates only	Code + patient link is a re-identification risk — only aggregated, k-anonymous analysis.
Provider identity	`npi, name, email`	Workforce	RAW: workforce policy; Curated: id/NPI only; Mart: aggregates only	Provider name/email — workforce PII; NPI usable as a stable, less sensitive key.
Imaging / lab result content	`DICOM binaries, lab result narrative value`	Sensitive	RAW: never store; Curated: never; Mart: never	Never land imaging/lab content in the warehouse — order/result-flag meta only.

DSDR discovery

Where personal data can hide beyond obvious contact fields — warehouse copies included.

Focus	Notes
Match keys	patient MRN (tokenized/hashed), FHIR patient id, encounter id, provider NPI.
Primary objects	Patient identifiers (tokenized), encounter, appointment, diagnosis/procedure codes, provider.
HL7/FHIR interface engine copies	Interface engines (Rhapsody, Mirth, etc.) duplicate PHI in message queues — keep retention short.
Reporting workbench / research extract copies	Epic Reporting Workbench extracts and research datasets require separate IRB/governance approval.

Source-specific guidance: what goes wrong, how to prevent it, what staff must know — plus where metadata lives and how to access it.

Data quality guides 5

Prevent issues in the source first. Warehouse checks are a safety net — not a substitute for staff habits.

High Encounter without a patient reference

Problem: Encounters without a (tokenized) patientId break all patient-level aggregations.
Prevent: FHIR extract: patient reference required; ensure tokenization before landing.
Staff must know: Data: Encounter without a patient link is an extract error — report immediately.
Checks: encounter.patient_id IS NULL
Fix in source: Re-extract with patient reference; verify the tokenization service.
Warehouse safety net: Quarantine in a restricted PHI-review area; never expose to broad marts.

High Invalid ICD code

Problem: Codes not present in the ICD code table distort diagnosis-frequency aggregates.
Prevent: Code-set validation at diagnosis capture time in the EHR.
Staff must know: HIM/coding: Use only valid, current ICD-10 codes.
Checks: diagnosis_code.icd_code NOT IN ref_icd_codeset
Fix in source: Correct coding in the source; re-extract.
Warehouse safety net: Quarantine invalid codes; exclude from frequency aggregates.

Medium Appointment status inconsistent with encounter

Problem: status=arrived without a resulting encounter distorts completed and no-show KPIs.
Prevent: Validate the check-in-to-encounter workflow in the scheduling system.
Staff must know: Front desk: Check-in must consistently lead to encounter creation.
Checks: appointment.status = 'arrived' AND appointment.id NOT IN (SELECT appointment_id FROM encounter)
Fix in source: Investigate the scheduling-to-encounter integration.
Warehouse safety net: Flag status_inconsistency; exclude from the completed-appointment KPI until resolved.

High Diagnosis aggregate below the k-anonymity threshold

Problem: Aggregates with a very small case count enable re-identification of individual patients.
Prevent: Enforce a k-anonymity threshold in every diagnosis/procedure aggregation.
Staff must know: Privacy/compliance: Never publish small cells (< k).
Checks: COUNT(*) GROUP BY icd_code, department_id HAVING COUNT < :k_anonymity_threshold
Fix in source: n/a — control lives in the aggregation/publishing logic.
Warehouse safety net: Suppress or roll up small cells to a higher category level.

Medium Provider with an invalid NPI

Problem: Invalid/missing NPI breaks external provider reference joins (e.g. NPPES).
Prevent: NPI validation (Luhn check) at provider onboarding.
Staff must know: Credentialing: Validate NPI before go-live.
Checks: provider.npi IS NULL OR LENGTH(provider.npi) <> 10
Fix in source: Correct/verify the NPI in the provider directory.
Warehouse safety net: Flag invalid_npi; exclude from external reference joins.

MDM / duplicates 2

Patient Patient duplicates (enterprise master patient index)

Match keys: MRN (tokenized), EMPI id, FHIR patient id
Prevent in source: EMPI/MPI as source of truth; strict registration validation.
Resolve in warehouse: Golden tokenized patient id via EMPI merge events.
Survivorship: EMPI golden record wins; local MRNs as aliases (tokenized).

Provider Provider NPI mapping drift

Match keys: npi, provider id, department id
Prevent in source: Credentialing process as source of truth; enforce NPI validation.
Resolve in warehouse: Canonical provider id → NPI SCD2 map.
Survivorship: Current credentialing record wins.

Product settings to improve 3

Identity → Enterprise master patient index EMPI merge/unmerge governance and the tokenization service

Why: Foundation for consistent, PHI-safe patient identifiers in the warehouse.
How: Document the merge/unmerge process; propagate to the tokenized id crosswalk.

Privacy/compliance → Reporting policy Set a k-anonymity threshold for all diagnosis/procedure aggregates

Why: Prevents re-identification via small cells in reports.
How: Define a threshold (e.g. k=11 per common standard); enforce in the publishing layer.

Interoperability → Interface engine retention Short retention for HL7/FHIR message queues in the interface engine

Why: Reduces the PHI exposure window outside the EHR system.
How: Configure queue TTL; audit message store access.

Metadata: what exists & how to get it 4

Source-native catalogs for schema, picklists/properties and access paths — not a full dump.

FHIR US Core profiles

Where in product: Epic on FHIR → US Core / Argonaut implementation guides
How to access: Inventory resource fields (Patient, Encounter, Appointment); define an allowlist.
Use for: Extract design, PHI tags, join keys (tokenized patient, encounter).
Watchouts: Never include DocumentReference/Observation.note in the select.

Clarity / Caboodle reporting schema

Where in product: Epic UserWeb → Clarity data model / Caboodle dimensional model docs
How to access: Use existing reporting tables as a field reference for metadata aggregates.
Use for: Encounter/appointment facts, department/provider dims.
Watchouts: Clarity tables contain full PHI — select only meta columns for the analytics warehouse.

ICD-10 / CPT code sets

Where in product: CMS ICD-10-CM/PCS files; AMA CPT code set (licensed)
How to access: Maintain reference code sets for validation and category rollups.
Use for: Diagnosis/procedure dims, code-validation DQ, category-level aggregates.
Watchouts: CPT is licensed (AMA) — clarify legal use in the warehouse.

Interconnect / interface engine schema

Where in product: Epic Interconnect / HL7 interface engine (Rhapsody, Mirth) message specs
How to access: Document message types (ADT, SIU, ORU) and relevant segments for meta extraction.
Use for: Near-real-time encounter/appointment facts.
Watchouts: HL7 segments often bundle full PHI — extract only allowlisted fields from message parsing.

Object overview with short descriptions and load guidance — not a full schema dump.

Table	Description	Grain	Load
`patient` Patient	Patient — MRN, DOB, name; strict PHI, only tokenized identifiers in marts.	One patient (MRN / FHIR id)	Load
`encounter` Encounter	Encounter meta — type, admit/discharge, department; no clinical note text.	One encounter (id)	Load
`appointment` Appointment	Appointment meta — scheduled time, status; no free-text reason.	One appointment (id)	Load
`department` Department	Department/location — name, specialty; dimension.	One department (id)	Load
`provider` Provider	Provider — NPI, name, specialty; workforce PII.	One provider (NPI / id)	Load
`diagnosis_code` Diagnosis code	Diagnosis code (ICD) per encounter — code only, no free-text description in the mart.	One diagnosis code entry (encounter_id, code)	Optional
`procedure_code` Procedure code	Procedure code (CPT) per encounter — code only.	One procedure code entry (encounter_id, code)	Optional
`lab_result_meta` Lab result meta	Lab result meta — testCode, resultFlag; no free-text comment/narrative value.	One lab result meta (id)	Optional

Most important fields to load early — not a full schema dump.

Entity	Field	Role	Why
Patient	`Patient.id`	Key	FHIR patient join (tokenized)
Patient	`Patient.mrn`	PII	Medical record number — PHI
Patient	`Patient.birthDate`	PII	Date of birth — PHI
Patient	`Patient.name`	PII	Name — PHI
Patient	`Patient.gender`	Dimension	Demographic dim (use in aggregate)
Encounter	`Encounter.id`	Key	Encounter join
Encounter	`Encounter.patientId`	Dimension	Patient back-join (tokenized)
Encounter	`Encounter.class`	Dimension	inpatient / outpatient / emergency
Encounter	`Encounter.admitDateTime`	Measure	Admit timestamp
Encounter	`Encounter.dischargeDateTime`	Measure	Discharge timestamp
Encounter	`Encounter.departmentId`	Dimension	Department back-join
Encounter	`Encounter.providerId`	Dimension	Provider back-join
Encounter	`Encounter.status`	Dimension	planned / in-progress / finished
Appointment	`Appointment.id`	Key	Appointment join
Appointment	`Appointment.patientId`	Dimension	Patient back-join (tokenized)
Appointment	`Appointment.start`	Measure	Scheduled time
Appointment	`Appointment.status`	Dimension	booked / arrived / no-show / cancelled
Appointment	`Appointment.departmentId`	Dimension	Department back-join
Department	`Department.id`	Key	Department join
Department	`Department.name`	Dimension	Department name
Department	`Department.specialty`	Dimension	Specialty
Provider	`Provider.id`	Key	Provider join
Provider	`Provider.npi`	Dimension	National provider identifier
Provider	`Provider.name`	PII	Provider name / PII
Provider	`Provider.specialty`	Dimension	Specialty
DiagnosisCode	`DiagnosisCode.encounterId`	Key	Encounter back-join
DiagnosisCode	`DiagnosisCode.icdCode`	Dimension	ICD-10 code (not free text)
ProcedureCode	`ProcedureCode.encounterId`	Key	Encounter back-join
ProcedureCode	`ProcedureCode.cptCode`	Dimension	CPT code (not free text)
LabResultMeta	`LabResultMeta.id`	Key	Lab result join
LabResultMeta	`LabResultMeta.testCode`	Dimension	LOINC test code
LabResultMeta	`LabResultMeta.resultFlag`	Dimension	normal / abnormal / critical (no value)

System and noise objects/fields you typically should not sync into the warehouse by default.

Skip tables / objects

Table	Category	Why
`Clinical / progress notes bodies`	System	Strict PHI skip — highest sensitivity, never land.
`Imaging / DICOM binaries`	System	Imaging binary data — never in the warehouse.
`Lab result narrative / free-text values`	System	Free-text findings — only flag meta allowed.
`Full HL7/FHIR message payload bulk`	System	Full messages bundle PHI — extract allowlisted fields only.

Skip fields / content

Clinical/progress notes bodies — Strict PHI skip in every stage.
Direct patient identifiers in marts (MRN/DOB/name/SSN) — Only tokenized id in analytics stages.
Imaging / DICOM binaries — Never in the warehouse.
Lab result narrative / free text — PHI — flag meta only.
Diagnosis/procedure + patient-level export below k-anonymity threshold — Re-identification risk — only aggregated categories with a minimum case count.

Warehouse-neutral examples for landing (RAW) and curated models — copy and adapt to your dialect. Not vendor SOQL/API DDL.

Landing (RAW)

RAW patient (tokenized) + encounter landing

FHIR shape — patient tokenized only; no cleartext MRN/DOB/name; no notes.

-- Warehouse-neutral RAW from Epic FHIR extract; patient identifiers tokenized before landing
CREATE TABLE raw_epic_patient (
  patient_token        VARCHAR, -- tokenized surrogate, never raw MRN
  gender               VARCHAR,
  birth_year           INT, -- year only, not full DOB, to reduce re-identification risk
  _loaded_at           TIMESTAMP
  -- no mrn, birth_date, name, address, ssn in any RAW column
);

CREATE TABLE raw_epic_encounter (
  encounter_id         VARCHAR,
  patient_token        VARCHAR, -- tokenized
  class                VARCHAR, -- inpatient | outpatient | emergency
  admit_at             TIMESTAMP,
  discharge_at         TIMESTAMP,
  department_id        VARCHAR,
  provider_id          VARCHAR,
  status               VARCHAR,
  _loaded_at           TIMESTAMP
  -- no clinical notes / progress note text
);

RAW appointment + diagnosis/procedure code landing

Appointment without free-text reason; codes only, no description text.

CREATE TABLE raw_epic_appointment (
  appointment_id       VARCHAR,
  patient_token        VARCHAR, -- tokenized
  department_id        VARCHAR,
  scheduled_at         TIMESTAMP,
  status               VARCHAR, -- booked | arrived | noshow | cancelled
  _loaded_at           TIMESTAMP
  -- no free-text appointment reason
);

CREATE TABLE raw_epic_diagnosis_code (
  encounter_id         VARCHAR,
  icd_code             VARCHAR, -- code only, not description text
  _loaded_at           TIMESTAMP
);

CREATE TABLE raw_epic_procedure_code (
  encounter_id         VARCHAR,
  cpt_code             VARCHAR, -- code only, not description text
  _loaded_at           TIMESTAMP
);

Curated

Curated fact epic encounter

Encounter grain — only tokenized patient reference; no clinical content.

CREATE TABLE curated_fct_epic_encounter AS
SELECT
  e.encounter_id,
  e.patient_token,
  e.class,
  e.admit_at,
  e.discharge_at,
  e.department_id,
  e.provider_id,
  e.status
FROM raw_epic_encounter e
WHERE e.patient_token IS NOT NULL;

Curated k-anonymous diagnosis aggregate

Only aggregated cells with a minimum case count (k-anonymity) — no patient-level exports.

CREATE TABLE curated_agg_epic_diagnosis_frequency AS
SELECT
  dc.icd_code,
  e.department_id,
  COUNT(*) AS case_count
FROM raw_epic_diagnosis_code dc
JOIN raw_epic_encounter e ON e.encounter_id = dc.encounter_id
GROUP BY dc.icd_code, e.department_id
HAVING COUNT(*) >= :k_anonymity_threshold;

Curated measure SELECTs

Encounters, no-show rate, average LOS — adapt period filters; all results aggregated.

-- Encounters in period
SELECT COUNT(*) AS encounters_count
FROM curated_fct_epic_encounter
WHERE admit_at BETWEEN :period_start AND :period_end;

-- No-show rate in period
SELECT
  COUNT(*) FILTER (WHERE status = 'noshow')::FLOAT / COUNT(*) AS no_show_rate
FROM raw_epic_appointment
WHERE scheduled_at BETWEEN :period_start AND :period_end;

-- Average length of stay for inpatient encounters (aggregate only)
SELECT AVG(discharge_at - admit_at) AS avg_length_of_stay
FROM curated_fct_epic_encounter
WHERE class = 'inpatient'
  AND discharge_at BETWEEN :period_start AND :period_end;

Tools

Open related Binom-Tools workflows to adapt formulas, PII and measures for this source.

Playbooks

Related governance stories for modelling, PII and measure design around this source.

Vendor help on Resources Official docs, governance and learning paths for this source. Open Resources →