SQL for Healthcare QA Analysts: Data Validation Queries for EHR Testing

SQL for healthcare QA analysts is the skill that proves what an EHR build did to the data. A screen shows only part of that. A screen can display the right value while the table underneath holds a duplicate, a null, or a code the downstream system rejects.

I have tested Epic builds, ADT and results interfaces, and payer file exchanges. The defects that cost the most were the ones nobody saw in the user interface. This article gives you nine query families, two scenarios with defect tickets, a decision tree, and a test evidence template. Start with the Epic Clarity SQL guide if the tables are new to you.

Why Healthcare QA Analysts Need SQL

Clinical software fails quietly. A wrong medication frequency, a duplicated encounter, or a lab result filed under the wrong patient does not crash anything. It surfaces weeks later in a billing denial or a safety review. The ISTQB Foundation syllabus frames testing as evaluating work products against requirements and expected behavior. In an EHR, the expected behavior often lives in the database.

SQL gives you three things the UI cannot. It checks volume, since you can count every record, not a sample of five. It checks hidden state, such as flags and codes that never appear on screen. And it gives repeatable evidence, because a saved query reruns after the next build.

Use all three layers. SQL covers the rows the UI skips and the state the API hides. For the relationship between business acceptance and technical verification, read BAT and UAT, then return to the data-level checks here.

What you do not need to learn first

You do not need stored procedures, query tuning, or database administration to be effective. Six constructs carry most validation work: SELECT with filters, COUNT with GROUP BY, JOIN and LEFT JOIN, EXCEPT or MINUS, HAVING, and CASE. Add window functions later for sequence checks.

Test Data and Environment Rules Before You Query

Query only non-production environments for test work unless a documented process says otherwise. Use synthetic test patients, and record their identifiers in the test plan. The HIPAA-safe Clarity SQL article covers why real patient rows do not belong in test evidence.

Check three facts before you trust any result. First, which environment and build you are on. Second, when the data last refreshed, since reporting copies lag the source. Third, whether your test patients still exist, because refreshes can overwrite them. Encounter-level checks also depend on the join key, which the PAT_ENC table guide covers.

-- Find your synthetic test patients by naming convention
SELECT  p.PAT_ID, p.PAT_MRN_ID, p.PAT_NAME
FROM    PATIENT p
WHERE   p.PAT_NAME LIKE 'ZZTEST,%'      -- agree the prefix with your build team
ORDER BY p.PAT_NAME;

Ask your build team for the test patient convention. Many sites prefix names with ZZ or TEST. Never assume. If the convention changes, a query like this one will return real patients.

Nine Validation Query Families for Healthcare QA

Every validation query belongs to one of nine families. Learn the family, and you can adapt the query to any table. The examples use standard Clarity names and a staging table called INTF_ADT_STAGE that stands for an interface landing table.

Family 1: Row count reconciliation

Start here for any migration, interface, or extract test. Count the source, count the target, and compare by a grouping that matches the business, such as date and message type.

SELECT  s.msg_date, s.src_count, t.tgt_count, s.src_count - t.tgt_count AS gap
FROM   (SELECT CAST(MSG_TIME AS date) AS msg_date, COUNT(*) AS src_count
        FROM INTF_ADT_STAGE WHERE MSG_TYPE = 'A03'      -- discharge messages
        GROUP BY CAST(MSG_TIME AS date)) s
LEFT JOIN
       (SELECT CAST(HOSP_DISCH_TIME AS date) AS msg_date, COUNT(*) AS tgt_count
        FROM PAT_ENC_HSP WHERE HOSP_DISCH_TIME IS NOT NULL
        GROUP BY CAST(HOSP_DISCH_TIME AS date)) t
       ON t.msg_date = s.msg_date
WHERE  s.src_count <> ISNULL(t.tgt_count, 0)
ORDER BY s.msg_date;

A gap on one day points to a failed batch. A small gap on every day points to a systematic rule, such as messages rejected for a missing field. Look at the shape of the gap before you guess at the cause.

Family 2: Required-field nulls and blanks

SELECT  COUNT(*)                                                    AS rows_checked,
        SUM(CASE WHEN DISCH_DISP_C IS NULL THEN 1 ELSE 0 END)       AS missing_disposition,
        SUM(CASE WHEN HOSP_ADMSN_TIME IS NULL THEN 1 ELSE 0 END)    AS missing_admit,
        SUM(CASE WHEN LTRIM(RTRIM(ISNULL(CAST(ADT_PAT_CLASS_C AS varchar(20)), ''))) = '' THEN 1 ELSE 0 END) AS blank_class
FROM    PAT_ENC_HSP
WHERE   HOSP_DISCH_TIME >= '2026-09-01' AND HOSP_DISCH_TIME < '2026-10-01';

Check blanks and nulls separately. An empty string passes a NOT NULL constraint and still fails the business rule. Trim whitespace before you compare.

Family 3: Duplicates

SELECT  MSG_CONTROL_ID, COUNT(*) AS copies
FROM    INTF_ADT_STAGE
WHERE   MSG_TIME >= '2026-09-01'
GROUP BY MSG_CONTROL_ID
HAVING  COUNT(*) > 1
ORDER BY copies DESC;

HL7 version 2 messages carry a control ID in the MSH segment. Duplicates by that ID usually mean a replay. Duplicates by patient, event, and time with different control IDs point to two real sends.

Family 4: Orphans and referential integrity

-- Encounters whose department does not exist in the department master
SELECT  e.PAT_ENC_CSN_ID, e.DEPARTMENT_ID
FROM    PAT_ENC e
LEFT JOIN CLARITY_DEP d ON d.DEPARTMENT_ID = e.DEPARTMENT_ID
WHERE   e.DEPARTMENT_ID IS NOT NULL
  AND   d.DEPARTMENT_ID IS NULL;

The anti-join pattern finds children without parents. Add the filter on the left-side key, as above, so rows that never had a department do not appear as false failures.

Family 5: Code and domain validity

Healthcare data runs on code sets: ICD-10-CM for diagnoses, LOINC for lab observations, CPT and HCPCS for procedures. A code that looks valid can still be missing from the active set. The CMS ICD-10 code files publish the annual ICD-10-CM and ICD-10-PCS files you can load as a reference table.

-- Diagnoses on encounters that are not in the loaded ICD-10-CM reference
SELECT  x.PAT_ENC_CSN_ID, edg.CODE AS icd10_code
FROM    PAT_ENC_DX x
JOIN    EDG_CURRENT_ICD10 edg ON edg.DX_ID = x.DX_ID
LEFT JOIN RPT_ICD10CM_REF ref ON ref.CODE = edg.CODE      -- table you load from the CMS files
WHERE   ref.CODE IS NULL
  AND   x.CONTACT_DATE >= '2026-09-01';

RPT_ICD10CM_REF is a table you create from the CMS release that matches your service dates. ICD-10-CM updates every October 1, so a visit in September and a visit in October use different valid sets. Match the reference version to the date of service, or you will flag valid codes as errors.

Family 6: Date and sequence logic

SELECT  PAT_ENC_CSN_ID, HOSP_ADMSN_TIME, HOSP_DISCH_TIME
FROM    PAT_ENC_HSP
WHERE   HOSP_DISCH_TIME < HOSP_ADMSN_TIME          -- discharge before admit
   OR   HOSP_ADMSN_TIME > GETDATE()                -- admit in the future
   OR   DATEDIFF(day, HOSP_ADMSN_TIME, HOSP_DISCH_TIME) > 365;   -- implausible stay

A query that returns zero rows is a pass only if the data exists. Always count the rows you checked, so you know the zero came from a clean dataset and not an empty one.

Family 7: Set comparison before and after a build

Regression testing at the data level compares a snapshot taken before the change to the state after. EXCEPT (SQL Server, PostgreSQL, Snowflake) or MINUS (Oracle) returns rows in one set but not the other.

-- Rows present before the build but missing or changed after
SELECT PAT_ENC_CSN_ID, DEPARTMENT_ID, ENC_TYPE_C, APPT_STATUS_C
FROM   qa_snap.PAT_ENC_PRE
EXCEPT
SELECT PAT_ENC_CSN_ID, DEPARTMENT_ID, ENC_TYPE_C, APPT_STATUS_C
FROM   PAT_ENC
WHERE  CONTACT_DATE >= '2026-09-01' AND CONTACT_DATE < '2026-10-01';

-- Then reverse the order to find rows that appeared

Run it in both directions. The first direction finds lost or changed rows. The second finds new ones. A refresh between snapshots changes data for reasons unrelated to your build, so use fixed date ranges and document them.

Family 8: Value range and format

SELECT  ORD_NUM_VALUE, REFERENCE_UNIT, COUNT(*) AS results
FROM    ORDER_RESULTS
WHERE   COMPONENT_ID = 1234                      -- hemoglobin component in your build
  AND   (ORD_NUM_VALUE < 0 OR ORD_NUM_VALUE > 25)
GROUP BY ORD_NUM_VALUE, REFERENCE_UNIT;

A hemoglobin of 140 is plausible in grams per liter and impossible in grams per deciliter. Unit errors create range violations that look like data corruption. Check the unit column next to the value.

Family 9: State transitions

Orders, results, and appointments move through statuses. A legal path exists, and a build can break it. Compare event times in the order they must occur.

SELECT  o.ORDER_PROC_ID, o.ORDER_TIME, r.RESULT_TIME, v.VERIFIED_TIME
FROM    ORDER_PROC o
JOIN    ORDER_RESULT_TIMES r ON r.ORDER_PROC_ID = o.ORDER_PROC_ID     -- placeholder join
JOIN    ORDER_VERIFY_TIMES v ON v.ORDER_PROC_ID = o.ORDER_PROC_ID     -- placeholder join
WHERE   r.RESULT_TIME < o.ORDER_TIME
   OR   v.VERIFIED_TIME < r.RESULT_TIME;

Table names here are placeholders. Your build stores order, result, and verification times in specific tables, so locate them in the Clarity Data Dictionary first.

The NULL Trap That Produces False Passes

The most dangerous validation mistake in SQL is NOT IN against a list that contains a null. SQL uses three-valued logic. Comparing anything to null returns unknown, not true or false, and WHERE keeps only true.

-- Looks right, returns ZERO rows if any ORDER_ID in the target is NULL
SELECT s.ORDER_ID FROM source_orders s
WHERE  s.ORDER_ID NOT IN (SELECT t.ORDER_ID FROM target_orders t);

-- Safe pattern: NOT EXISTS
SELECT s.ORDER_ID FROM source_orders s
WHERE  NOT EXISTS (SELECT 1 FROM target_orders t WHERE t.ORDER_ID = s.ORDER_ID);

One null in the subquery makes the first query return nothing, and you report a clean reconciliation that is wrong. Use NOT EXISTS or a LEFT JOIN anti-join for every missing-record test.

Scenario: Discharge Messages Missing After an ADT Interface Upgrade

A hospital upgraded its ADT interface engine ahead of a billing system cutover. Regression tests passed in the UI. Registration staff discharged test patients, and billing saw them. I was the QA lead asked to sign off a full-volume parallel run before go-live.

The parallel run sent a week of production-volume discharge messages through the new engine into a test environment. I compared message counts to encounter counts in the target by day.

The pattern emerged from a second query: a null check on the staging table, grouped by whether the message produced a discharge time. Every missing discharge shared one trait. The disposition field was empty in the source feed.

SELECT  CASE WHEN s.DISCH_DISP IS NULL OR LTRIM(RTRIM(s.DISCH_DISP)) = '' THEN 'blank' ELSE 'populated' END AS disposition,
        COUNT(*) AS messages,
        SUM(CASE WHEN h.HOSP_DISCH_TIME IS NULL THEN 1 ELSE 0 END) AS no_discharge_time
FROM    INTF_ADT_STAGE s
LEFT JOIN PAT_ENC_HSP h ON h.PAT_ENC_CSN_ID = s.PAT_ENC_CSN_ID
WHERE   s.MSG_TYPE = 'A03' AND s.MSG_TIME >= '2026-09-14' AND s.MSG_TIME < '2026-09-21'
GROUP BY CASE WHEN s.DISCH_DISP IS NULL OR LTRIM(RTRIM(s.DISCH_DISP)) = '' THEN 'blank' ELSE 'populated' END;

A 100 percent versus 0 percent split is the cleanest evidence a defect ticket can carry. The interface team changed the engine rule to accept blank dispositions, and the follow-up run closed the gap. No UI test would have found it, because the test patients all had dispositions.

Scenario: Lab Results Landing Under the Wrong Component

A laboratory build remapped a set of local result codes to LOINC. After go-live in the test environment, a clinical quality analyst noticed that a glucose trend chart looked flat. The UI showed results for each patient correctly. The trend report did not.

I grouped result counts by component and LOINC code for the test period. The query showed 6,310 results under a component named “Glucose, POC” with a LOINC mapped to blood glucose by serum assay. Point-of-care glucose and serum glucose are clinically different measurements, and combining them distorts trends.

SELECT  c.NAME AS component_name, m.LOINC_CODE, m.LOINC_LONG_NAME, COUNT(*) AS results
FROM    ORDER_RESULTS r
JOIN    CLARITY_COMPONENT c ON c.COMPONENT_ID = r.COMPONENT_ID
LEFT JOIN RPT_LOINC_MAP m   ON m.COMPONENT_ID = c.COMPONENT_ID     -- site mapping table
WHERE   r.RESULT_TIME >= '2026-09-01'
  AND   c.NAME LIKE '%GLUCOSE%'
GROUP BY c.NAME, m.LOINC_CODE, m.LOINC_LONG_NAME
ORDER BY c.NAME;

Two different component names pointed to one LOINC code. That is the defect. A test that checks only that a LOINC exists would pass. A test that checks one-to-one mapping between clinically distinct components catches it.

Clinical meaning belongs to the laboratory and terminology owners. QA surfaces the pairs and the owners decide. Do not mark a mapping correct because the query ran without errors.

Setting Up Snapshots and a Validation Schema

Set-comparison tests need a stable “before.” Capture it before the build lands, in a schema you own, so refreshes and cleanup jobs cannot touch it. Ask your DBA for a QA schema with create-table rights and a retention policy.

-- Capture the "before" state for the test window
SELECT  e.PAT_ENC_CSN_ID, e.DEPARTMENT_ID, e.ENC_TYPE_C, e.APPT_STATUS_C,
        GETDATE() AS snapped_at, 'build 2026.3' AS build_label
INTO    qa_snap.PAT_ENC_PRE
FROM    PAT_ENC e
WHERE   e.CONTACT_DATE >= '2026-09-01' AND e.CONTACT_DATE < '2026-10-01';

SELECT COUNT(*) AS rows_captured FROM qa_snap.PAT_ENC_PRE;

Label every snapshot with the build and the capture time. Six weeks later, nobody remembers which snapshot belongs to which release. Restrict the snapshot to test-patient rows where possible, which also keeps real patient data out of your QA schema.

Keep snapshots small. Compare only the columns the change can affect, plus the key. A full-row snapshot of a wide table burns storage and makes the comparison noisy, because unrelated columns change every night.

Automating the Standing Pack

A query you run by hand on Fridays gets skipped on the Friday of a release crunch. Automate the standing pack so it runs after every build and reports a clear pass or fail.

Wrap each check in a test framework your team already uses. Python with pytest and a database driver works well, as does JUnit with JDBC for Java shops. Each query becomes a test that asserts a count equals zero.

import pyodbc, pytest

CONN = "DRIVER={ODBC Driver 18 for SQL Server};SERVER=qa-reporting;DATABASE=clarity_qa;Trusted_Connection=yes;"

CHECKS = {
    "no_discharge_before_admit":
        "SELECT COUNT(*) FROM PAT_ENC_HSP WHERE HOSP_DISCH_TIME < HOSP_ADMSN_TIME",
    "no_encounter_without_department":
        "SELECT COUNT(*) FROM PAT_ENC e WHERE e.DEPARTMENT_ID IS NULL AND e.CONTACT_DATE >= '2026-09-01'",
    "no_orphan_department":
        "SELECT COUNT(*) FROM PAT_ENC e LEFT JOIN CLARITY_DEP d ON d.DEPARTMENT_ID = e.DEPARTMENT_ID "
        "WHERE e.DEPARTMENT_ID IS NOT NULL AND d.DEPARTMENT_ID IS NULL",
}

@pytest.mark.parametrize("name,sql", CHECKS.items())
def test_zero_violations(name, sql):
    with pyodbc.connect(CONN) as cn:
        violations = cn.cursor().execute(sql).fetchone()[0]
    assert violations == 0, f"{name}: {violations} violating rows"

Add a companion test that asserts the table has rows in the window. That guards against the empty-dataset false pass. Publish results to the same dashboard your functional tests use, so data defects get the same visibility as UI failures.

Keep credentials out of the file. Use integrated authentication or a secrets manager, and run the job under a service account with read-only access to the QA database.

Choosing Tools for SQL-Based Testing

Tool choice depends on who runs the checks and how often. I have used each of the options below on healthcare projects, and each has a place.

For most QA teams, I recommend a SQL client for exploration and a small test framework for the standing pack. Add a data quality framework only when your team owns a modeled warehouse. Adopting a framework before you have a stable set of checks adds maintenance without adding coverage.

Building a Coverage Matrix for Data Testing

A requirement without a data check is a gap. Map each data-bearing requirement to the query family that tests it. The matrix also shows reviewers what you did not test, which is as useful as what you did.

Review the matrix with the business analyst at each release gate. Blank rows mean unwritten tests, not passing tests. Count them honestly.

Decision Tree: Which SQL Check Fits the Defect You Suspect?

Start from the symptom, not the query. Pick the family that matches what you suspect, and write the smallest query that could prove you wrong.

What does the symptom look like?
Pick the closest match

Records missing
or too many

Wrong value or code
looks present, is wrong

Timeline looks odd
order of events

Worked before the
build, broke after

Families 1, 3, 4
Row count by day
Duplicates by key
Orphan anti-join
Use NOT EXISTS,
never NOT IN

Families 2, 5, 8
Null and blank counts
Code vs reference set
Range and unit checks
Match reference version
to date of service

Families 6, 9
Discharge before admit
Result before order
Future-dated records
Check time zone and
daylight saving

Family 7
Snapshot before build
EXCEPT in both directions
Fixed date range
Refreshes change data
unrelated to the build

Many defects need two families. The ADT scenario used Family 1 to detect the gap and Family 2 to explain it. Run the cheap check first and let its result choose the next one.

Writing SQL Evidence into Defect Tickets

A defect with good evidence gets fixed in a day. A defect with a screenshot and the phrase “data is wrong” gets questioned for a week. Developers need to rerun your check and see the same result.

Attach the query as text, not as a screenshot of a query window. Reviewers copy and run text. For privacy rules on attachments, follow your privacy office’s guidance.

A reusable test case with SQL steps

Role Checklists for Healthcare QA Teams

Common Mistakes in Healthcare SQL Testing

When I Would Use SQL and When I Would Not

I use SQL for migrations, interface parallel runs, nightly extracts, and report reconciliation. I also use it when the screen looks right and the downstream result looks wrong. SQL gives full-volume coverage that manual testing cannot match.

I do not use SQL to judge usability, clinical workflow fit, or alert design. Those need clinicians in the room. I also avoid SQL for checking calculated fields that the application computes at display time, since the database may hold only the inputs. For those, I test through the UI or an API and use SQL to confirm the inputs.

For ongoing practice with realistic data problems, work through the SQL Analyst Challenge Room. These checks feed the sign-off process described earlier.

Edge Cases in Healthcare Data Validation

A Standing Query Pack for Every Build

Teams that rerun the same checks after each build find regressions in hours instead of weeks. Keep the pack small, named, and owned.

Store the pack in version control with a README listing parameters and expected results. When a defect escapes, add the query that would have caught it. After a year, the pack becomes your institutional memory.

Questions QA Analysts Ask About SQL in Healthcare

How much SQL do I need to start?

Enough for joins, aggregates, and set comparison. Most validation uses six constructs, and you can learn them in a few weeks of practice on realistic data.

Can I run these queries in production?

Only with approval and read-only access under your organization’s policy. Prefer non-production for test work, and follow the privacy rules for any patient-level data.

Which database am I querying?

Clarity runs on SQL Server or Oracle at most Epic sites. Syntax differs slightly, such as EXCEPT versus MINUS and GETDATE() versus SYSDATE.

Should QA write SQL or ask a developer?

Write your own. An independent check carries more weight than one a developer wrote for their own build, and you control the timing.

What if I do not have database access?

Request read-only access to the non-production reporting database with a stated purpose. If the answer is no, ask for scheduled extracts of the tables you need.

Run This Before Your Next Sign-Off

Pick one interface or migration you are about to approve. Run a row count by day from source to target, then run the same check for nulls in its two most important fields. If either shows a gap you cannot explain, do not sign. It takes under an hour and protects you from the defect nobody saw on screen.

Sources and Further Reading

• ISTQB. The Foundation Level syllabus defines testing objectives and test techniques referenced above.
• CMS: ICD-10 code files. Annual ICD-10-CM and ICD-10-PCS releases for reference tables.
• Epic Clarity Data Dictionary on Epic UserWeb (requires an Epic customer login). Authority for column names in your version.

Table and column names follow standard Clarity where noted. Staging, snapshot, and reference tables are placeholders you create. Confirm every name in your environment, and run each query in non-production first.