HIPAA-Safe Clarity SQL: How to Query Epic Data Without Exposing PHI

HIPAA-safe Clarity SQL means every query returns only the data its purpose requires. You run it from an authorized source, and the output limits re-identification. Most privacy incidents in analytics do not come from attackers. They come from a well-meaning analyst who exports patient-level rows to Excel for a quick check.

I have supported UAT and reporting teams on Epic projects where that export happened. The fix is a set of habits, a few SQL patterns, and a documented access process. This article gives you all three. It is not legal advice. Your privacy officer and counsel decide what your organization may do, and state law can be stricter than HIPAA.

Where PHI Lives in Epic Clarity

HIPAA defines protected health information as individually identifiable health information. Data becomes PHI when health information combines with any of 18 identifier types listed in the Privacy Rule. Clarity holds both halves in the same database, often in neighboring tables.

Diagnosis, order, and encounter tables carry health information. Patient and encounter tables carry identifiers. A join between them creates PHI, even when each table looks harmless alone. The table below maps common identifiers to typical Clarity locations.

Names above follow standard Clarity but vary by version and security configuration. Many organizations restrict or remove some columns from analyst views. Check the Clarity Data Dictionary and your site’s access roles. Assume anything dated, geographic, or numeric about a person is an identifier until you prove otherwise.

A CSN is an identifier, even though it is not on the list by name

Analysts often assume an encounter ID is harmless because it looks like a random number. It is not. Anyone with Epic access can look up the patient from a CSN in seconds. Treat it as an account-number-type identifier, and strip it from anything that leaves the secured environment. The PAT_ENC table guide shows why the CSN is also the join key. That makes it easy to carry into an export by accident.

HIPAA Rules That Shape an Analyst’s Queries

Three parts of HIPAA govern most analytics work: the Privacy Rule, the Security Rule, and the Breach Notification Rule. Each affects the SQL you write, the places you store results, and what you do when something goes wrong.

The HHS minimum necessary guidance states that covered entities must make reasonable efforts to limit PHI to the minimum necessary. The standard does not apply to disclosures for treatment, which is why a clinician can see a full chart while an analyst cannot.

Three paths to using patient data in analytics

You have three legitimate ways to work with patient-derived data. Each trades detail for protection, and each has a different approval path.

The HHS de-identification guidance explains both methods and the 18 identifiers. Safe Harbor also requires that you have no actual knowledge that the remaining data could identify a person. Rare diagnoses in small facilities can fail that test.

Scenario: A UAT Evidence File with 2,000 MRNs Sent by Email

A regional health system was testing a new referral workflow before go-live. A QA analyst needed to prove that referral orders reached the right departments. She joined patients, encounters, and orders in Clarity and exported 2,000 rows to Excel. Then she emailed the file to a vendor consultant as test evidence.

The file held MRNs, birth dates, encounter dates, and diagnosis names. The vendor consultant’s email address was correct, but the vendor contract did not cover PHI from the production environment. The privacy office learned of it two days later during a routine mail review.

The privacy officer documented a risk assessment and decided on next steps. That decision belongs to compliance, and analysts should not guess at the outcome. What I could control was the test process. The team rewrote the evidence rule and built a masked reporting view.

The before query

-- BEFORE: row-level PHI exported as test evidence
SELECT  p.PAT_MRN_ID, p.PAT_NAME, p.BIRTH_DATE,
        e.PAT_ENC_CSN_ID, e.CONTACT_DATE,
        o.ORDER_PROC_ID, o.DESCRIPTION,
        dx.DX_NAME
FROM    PATIENT p
JOIN    PAT_ENC e        ON e.PAT_ID = p.PAT_ID
JOIN    ORDER_PROC o     ON o.PAT_ENC_CSN_ID = e.PAT_ENC_CSN_ID
JOIN    PAT_ENC_DX x     ON x.PAT_ENC_CSN_ID = e.PAT_ENC_CSN_ID
JOIN    CLARITY_EDG dx   ON dx.DX_ID = x.DX_ID
WHERE   o.DESCRIPTION LIKE '%REFERRAL%'
  AND   e.CONTACT_DATE >= '2026-09-01';

The after query

-- AFTER: aggregate evidence, no identifiers, small cells suppressed
SELECT  d.DEPARTMENT_NAME           AS referral_department,
        DATEFROMPARTS(YEAR(e.CONTACT_DATE), MONTH(e.CONTACT_DATE), 1) AS month_start,
        COUNT(DISTINCT o.ORDER_PROC_ID) AS referral_orders
FROM    ORDER_PROC o
JOIN    PAT_ENC e        ON e.PAT_ENC_CSN_ID = o.PAT_ENC_CSN_ID
JOIN    CLARITY_DEP d    ON d.DEPARTMENT_ID  = o.REFERRAL_DEPT_ID   -- placeholder column name
WHERE   o.DESCRIPTION LIKE '%REFERRAL%'
  AND   e.CONTACT_DATE >= '2026-09-01'
GROUP BY d.DEPARTMENT_NAME,
         DATEFROMPARTS(YEAR(e.CONTACT_DATE), MONTH(e.CONTACT_DATE), 1)
HAVING  COUNT(DISTINCT o.ORDER_PROC_ID) >= 11;

The aggregate answers the test question. Did referral orders reach the expected departments in the expected numbers? The vendor needed no names, no dates, and no MRNs. The REFERRAL_DEPT_ID column is a placeholder for whichever field your build uses to store the receiving department.

For the few cases where a tester must trace one order, use a test patient created for UAT, and cite the test MRN. Real patients do not belong in non-production evidence.

SQL Patterns That Reduce PHI Exposure

Six patterns cover most analytics requests. Use them in order: aggregate first, generalize second, mask third, restrict fourth.

Pattern 1: Aggregate instead of listing

Most business questions need counts, rates, or averages, not rows. Rewrite the request before you write the query. Ask what decision the number supports, and the aggregate usually answers it.

Pattern 2: Suppress small cells

A count of one or two can identify a person, especially for a rare condition in a small clinic. CMS suppresses cells with fewer than eleven beneficiaries in its public data products. Many health systems adopt the same threshold for internal reports shared outside the care team. Check your own policy for the number.

SELECT  d.DEPARTMENT_NAME, dxg.DX_GROUP_NAME,
        COUNT(DISTINCT e.PAT_ID) AS patients
FROM    PAT_ENC e
JOIN    CLARITY_DEP d  ON d.DEPARTMENT_ID = e.DEPARTMENT_ID
JOIN    RPT_DX_GROUP_MAP dxg ON dxg.PAT_ENC_CSN_ID = e.PAT_ENC_CSN_ID   -- your governed mapping table
WHERE   e.CONTACT_DATE >= '2026-01-01' AND e.CONTACT_DATE < '2026-10-01'
GROUP BY d.DEPARTMENT_NAME, dxg.DX_GROUP_NAME
HAVING  COUNT(DISTINCT e.PAT_ID) >= 11;

Suppression has a weakness called differencing. If a report shows the total and all but one cell, the missing cell is easy to compute. Suppress a second cell, or publish only the totals, when both a cell and its complement are exposed.

Pattern 3: Generalize dates, ages, and ZIP codes

SELECT  CASE WHEN age_years >= 90 THEN '90+'
             WHEN age_years >= 65 THEN '65-89'
             WHEN age_years >= 40 THEN '40-64'
             WHEN age_years >= 18 THEN '18-39'
             ELSE '0-17' END                         AS age_band,
        YEAR(x.CONTACT_DATE)                         AS contact_year,
        CASE WHEN z.ZIP3 IS NULL THEN '000' ELSE z.ZIP3 END AS zip3,
        COUNT(*)                                     AS encounters
FROM   (SELECT e.CONTACT_DATE, e.PAT_ID,
               DATEDIFF(year, p.BIRTH_DATE, e.CONTACT_DATE)
                 - CASE WHEN DATEADD(year, DATEDIFF(year, p.BIRTH_DATE, e.CONTACT_DATE), p.BIRTH_DATE) > e.CONTACT_DATE
                        THEN 1 ELSE 0 END            AS age_years,
               LEFT(p.ZIP, 3)                        AS zip3_raw
        FROM PAT_ENC e JOIN PATIENT p ON p.PAT_ID = e.PAT_ID
        WHERE e.CONTACT_DATE >= '2026-01-01' AND e.CONTACT_DATE < '2026-10-01') x
LEFT JOIN RPT_ZIP3_ALLOWED z ON z.ZIP3 = x.zip3_raw   -- ZIP3 areas above 20,000 people
GROUP BY CASE WHEN age_years >= 90 THEN '90+' WHEN age_years >= 65 THEN '65-89'
              WHEN age_years >= 40 THEN '40-64' WHEN age_years >= 18 THEN '18-39' ELSE '0-17' END,
         YEAR(x.CONTACT_DATE),
         CASE WHEN z.ZIP3 IS NULL THEN '000' ELSE z.ZIP3 END;

Safe Harbor lets you keep the first three ZIP digits only when all ZIP codes sharing those digits cover more than 20,000 people. Otherwise replace them with 000. The Census Bureau publishes the population data you need to build the allowed list. Notice also that the age calculation corrects for birthdays not yet reached, which plain DATEDIFF(year) gets wrong. Generalizing dates also breaks metrics that need exact timestamps, such as a Clarity LOS calculation. Decide which reports need full dates under a limited data set.

Pattern 4: Never hash an identifier and call it de-identified

Analysts often replace the MRN with a hash and consider the data safe. It is not de-identified. The Privacy Rule says a re-identification code must not be derived from information about the individual. A hash of an MRN is derived from it, and anyone with the MRN list can recompute the hash.

-- Random surrogate key, assigned once and stored securely
INSERT INTO rpt_secure.PAT_SURROGATE (PAT_ID, SURROGATE_KEY)
SELECT  p.PAT_ID, NEWID()
FROM    PATIENT p
WHERE   NOT EXISTS (SELECT 1 FROM rpt_secure.PAT_SURROGATE s WHERE s.PAT_ID = p.PAT_ID);

Restrict the lookup table to the few people who maintain it. Anyone who can read both the surrogate table and the source can reverse the mapping.

Where Results Go: Tool Risk Comparison

A safe query still becomes unsafe when the output lands in the wrong place. Each tool you use to view or share results carries a different exposure profile. The table below ranks the tools I see most often on Epic projects.

Most incidents I have reviewed involved the last four rows. The analyst used the tool for convenience and never considered where a copy would persist. Make the safe path the easy path by providing a secured share and a masked view.

How Access to Clarity Usually Works

Clarity access is a local decision, and it varies more than people expect. A typical arrangement gives analysts a database login tied to an Active Directory group. The group maps to a role that can read selected tables or views on a reporting server, never the Chronicles production system.

Some sites grant broad read access to all Clarity tables for a small team of senior analysts. Others expose only curated views. The curated model costs more to maintain and removes most accidental exposure. If your organization offers it, use it.

Ask your Clarity administrator which model you work under. If the answer is “everything,” your own habits are the only control. Apply the checklist at the end of this article to every query.

If It Goes Wrong: The First Hour

Mistakes happen, and how you respond matters more than the mistake. Analysts who report quickly keep a small problem small. Analysts who hide a mistake create a larger one.

Stop sending the data. Do not forward, delete, or edit anything yet, because the privacy team needs the original facts. Write down what you sent, to whom, when, and through which channel. Then call or message your privacy officer or security contact directly, using the incident process your organization defined.

Under the Breach Notification Rule, a covered entity evaluates an impermissible use or disclosure through a documented risk assessment. That assessment considers the nature of the data, the recipient, whether it was actually viewed, and how well the risk has been reduced. Your report starts that clock.

After the review, convert the lesson into a control. The email incident above produced a masked view, a revised evidence standard, and a short training segment. A new rule alone would have been forgotten within a quarter.

Masked Evidence for Jira and Test Reports

QA teams attach screenshots to tickets all day, and each screenshot can carry an MRN, a name, or a date of birth. Build masking into the test routine rather than rely on memory at the end of a long day.

Use test patients wherever the environment allows it. Crop to the relevant field. Replace visible identifiers with a solid box rather than a blur, because blur can sometimes be reversed. Check the ticket after upload, including the attachment preview, and confirm the filename does not contain a patient name.

A ticket like this gives developers what they need without carrying real patient data into a tool with broad visibility. Note the explicit data-handling line. It reminds reviewers to check, and it gives auditors a clear record.

Pattern 5: Give analysts a masked view, not the base tables

Habits fail under deadline pressure. Structure holds. Build a reporting schema with views that already exclude names, street addresses, and exact birth dates, and grant analysts access to the views only.

CREATE VIEW rpt_safe.vw_encounter_deid AS
SELECT  s.SURROGATE_KEY                         AS person_key,
        YEAR(e.CONTACT_DATE)                    AS contact_year,
        MONTH(e.CONTACT_DATE)                   AS contact_month,
        e.DEPARTMENT_ID,
        e.ENC_TYPE_C,
        e.APPT_STATUS_C
FROM    PAT_ENC e
JOIN    rpt_secure.PAT_SURROGATE s ON s.PAT_ID = e.PAT_ID;
GO
GRANT  SELECT ON SCHEMA::rpt_safe TO analyst_role;
DENY   SELECT ON PATIENT TO analyst_role;

A view limits what the query can return, but it does not by itself prove that the data is de-identified. Dates by month and department may still identify someone in a small clinic. Have your privacy officer approve the view design, and document the approval.

Pattern 6: Restrict rows with row-level security

Some analysts should see only one hospital or one service line. SQL Server row-level security applies a predicate to every query, so the restriction does not depend on each author remembering a WHERE clause.

CREATE FUNCTION rpt_safe.fn_dept_filter(@DEPARTMENT_ID NUMERIC(18,0))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN SELECT 1 AS allowed
       FROM rpt_secure.ANALYST_DEPT_ACCESS a
       WHERE a.LOGIN_NAME = USER_NAME() AND a.DEPARTMENT_ID = @DEPARTMENT_ID;
GO
CREATE SECURITY POLICY rpt_safe.DeptPolicy
ADD FILTER PREDICATE rpt_safe.fn_dept_filter(DEPARTMENT_ID) ON rpt_safe.encounter_table
WITH (STATE = ON);

Apply this on a reporting copy or a warehouse table, not on Epic’s own Clarity tables. Epic controls those, and local changes can break upgrades. Oracle offers Virtual Private Database for the same purpose, and Snowflake has row access policies.

Decision Tree: Can You Run and Export This Query?

Run every request through this tree before you hit execute. The first two questions stop most incidents.

Do I have a documented purpose
and role-based access?

No: stop,
request access
Yes

Can an aggregate answer it?
Counts, rates, averages
Yes
No

Aggregate it
Suppress cells under the
policy threshold. Check for
differencing. Export is low risk.

Do you need row-level data?
Use the de-identified view.
Drop MRN, CSN, name, full
dates, address.

Is identifiable detail truly
required?
Ask the privacy officer first.

Row-level PHI stays inside
the secured environment
No email, no personal laptop,
no unsecured Excel or chat.
Share via approved tools only.

The tree ends in the same place for most requests: the data stays inside. Moving it out requires a business associate agreement, a data use agreement, or a documented de-identification, and those take time to arrange.

Scenario: A Vendor Asks for a Readmission Extract

A health system hired an analytics vendor to model readmission risk. The vendor asked for three years of encounters with dates, diagnoses, and ZIP codes. The project manager asked me whether the team could “just pull it.”

The answer depended on the contract and the data. With a business associate agreement in place, the health system may disclose PHI to the vendor for the covered purpose, still under minimum necessary. Without one, it cannot. Even with the agreement, the vendor did not need names, street addresses, or phone numbers.

We agreed on a limited data set. The extract kept admission and discharge dates, city, state, and ZIP, because readmission modeling needs time between encounters. It dropped names, street addresses, phone numbers, MRNs, and account numbers, and replaced the MRN with a random surrogate key. A data use agreement covered the rest.

Keeping dates mattered for the model. A Safe Harbor version would have reduced everything to year and killed the readmission interval. That is the tradeoff: more utility needs more controls. A different project might have chosen Expert Determination to keep some detail without a data use agreement.

Counsel reviewed the agreements. My role was to build the extract, test that the removed fields were absent, and document the field list that the privacy officer approved.

Template: Data Access Request for Analysts

Copy this into your ticketing system. A complete request takes five minutes to write and removes most of the back-and-forth with privacy and security teams.

Audit Trails: Assume Every Query Leaves a Record

The Security Rule requires audit controls. In practice, your database platform, your reporting tools, and Epic’s own access logs may all record who ran what and when. Privacy teams review those logs after incidents and, at many organizations, during routine monitoring.

Write queries you would defend. Name the ticket in a comment at the top. Avoid exploratory SELECT * against patient tables, because the log shows that you touched every column. Keep a short record of why you ran each production query. When someone asks in six months, you will have an answer.

/* Ticket: SEC-0932-followup | Purpose: referral order counts for UAT sign-off
   Requester: QA | Approved by: Privacy Office 2026-10-02 | Aggregate only */
SELECT d.DEPARTMENT_NAME, COUNT(DISTINCT o.ORDER_PROC_ID) AS referral_orders
FROM   ORDER_PROC o
JOIN   CLARITY_DEP d ON d.DEPARTMENT_ID = o.REFERRAL_DEPT_ID
GROUP BY d.DEPARTMENT_NAME
HAVING COUNT(DISTINCT o.ORDER_PROC_ID) >= 11;

Do not look up patients you know personally, and never browse a coworker’s record out of curiosity. Snooping is a leading source of access violations in health systems, and audit logs catch it.

Role Checklists for HIPAA-Safe Clarity Work

Common HIPAA Mistakes in Clarity Work

When I Would Use Each Protection

For recurring dashboards, I build de-identified views once and point all reports at them. It costs a week of setup and saves a monthly debate. For one-time validation, I aggregate and suppress. For vendor work, I start with the contract and then pick the lightest data path that supports the model.

I use full identified access only for tasks that cannot work otherwise, such as a patient-level reconciliation for a billing dispute. Those run on the secured server, under a ticket, with results stored in an approved location and deleted on schedule.

For enterprise reporting, a governed model in Caboodle with built-in masking beats ad hoc Clarity queries. Validate it with the aggregate patterns above. The Epic Clarity SQL guide and the SQL for IT Analysts hub show where to find the query patterns that support those checks.

Edge Cases That Break Clean Privacy Rules

Legal requirements change. Confirm anything that carries regulatory weight with your privacy officer or counsel before you build on it. This article describes common practice, not a ruling on your situation.

A Pre-Run Checklist for Any Clarity Query

Questions Analysts Ask About HIPAA and Clarity

Is Clarity data always PHI?

Not every table, but any query that combines health information with an identifier produces PHI. Treat Clarity as PHI by default.

Can I use real patient data in a test environment?

Only if your organization has authorized it and applied the same safeguards as production. Many sites scrub non-production copies. Confirm yours is scrubbed before you rely on it.

Does removing the patient name make a dataset de-identified?

No. De-identification requires removing or generalizing all 18 identifier types, or a qualified expert’s determination. Dates, ZIP codes, and record numbers still identify people.

Is it safe to paste query results into an AI assistant?

Not unless your organization has approved that tool and signed the required agreements. Treat pasted PHI as a disclosure.

Who decides whether an incident is a breach?

Your privacy officer, using the risk assessment that the Breach Notification Rule requires. Report the incident quickly and let them decide.

Run This Before Your Next Export

Open the last query you exported and read its SELECT list. Count the identifiers: names, MRNs, CSNs, full dates, addresses. For each one, write the reason you needed it. If you cannot write a reason, rewrite the query as an aggregate. That ten-minute audit of your own work prevents the next SEC ticket.

Sources and Further Reading

• HHS: Guidance Regarding Methods for De-identification of PHI. Defines Safe Harbor, the 18 identifiers, and Expert Determination.
• HHS: Minimum Necessary Requirement. Official guidance on limiting PHI use and disclosure.
• 45 CFR Parts 160 and 164 (HIPAA Administrative Simplification regulations) on eCFR. Citations in this article refer to these parts.
• Epic Clarity Data Dictionary on Epic UserWeb (requires an Epic customer login). Authority for column names in your version.

This article does not constitute legal advice. Table and column names follow standard Clarity and vary by site. Placeholder objects are marked in the code.