ETL Validation SQL: Source-to-Target Testing Queries for Data Warehouses

ETL validation SQL proves that the data a pipeline loaded matches the data it was supposed to load. Row counts that agree tell you almost nothing. A load can match on count and still truncate a column, drop late updates, or duplicate a dimension row. This article gives you the queries that catch those failures.

I have tested warehouse loads for hospital reporting and finance, including nightly extracts from Epic Clarity into a reporting warehouse. Most of the defects I found passed a count check. The checks below come from those projects. Table names are generic, so map them to your environment.

What ETL Validation Covers

ETL stands for extract, transform, load. Each stage fails in its own way, and each failure needs a different test. A complete validation plan covers six data quality dimensions.

These map directly to the validation query families in the nine validation query families article. ETL testing applies those families to a pipeline instead of an application, and adds checks for slowly changing dimensions and incremental loads.

Where ETL testing sits in the test pyramid

Most teams skip reconciliation because it feels like operations work. It is the only level that watches real data. A pipeline can pass every test environment check and still fail on production volumes, so build the reconciliation checks into the load itself.

A Reference Model for ETL Validation Queries

The examples use a simple warehouse pattern with a source, a staging layer, a fact table, a slowly changing dimension, and an audit table. Healthcare projects use exactly this shape when Clarity data feeds a reporting warehouse such as Caboodle.

Grain matters before any test runs. In this model the fact table holds one row per encounter, keyed by the contact serial number. The PAT_ENC table guide covers why that key is the right join and uniqueness test for encounter data.

Source
Clarity tables

Staging
Raw changed rows

Transform
Rules, mapping

Warehouse
Facts, dimensions

Reports
BI, extracts
V1
V2
V3
V4
V5
V6
V1 extract counts | V2 staging fidelity | V3 transformation rules | V4 load integrity and SCD
V5 reject review | V6 report reconciliation to source totals

Each checkpoint answers one question and has one owner. If V2 fails, the problem sits between the source and staging, so you do not waste time inspecting transformation logic. Run the checkpoints in order, and stop at the first failure.

Eleven ETL Validation SQL Checks

All checks use SQL Server syntax and the reference model. Each returns zero violating rows when the load is clean, and each should also report how many rows it examined.

Check 1: Row count reconciliation with the load log

SELECT  l.batch_id, l.rows_read, l.rows_written, l.rows_rejected,
        l.rows_read - (l.rows_written + l.rows_rejected) AS unaccounted
FROM    etl.load_log l
WHERE   l.load_date = CAST(GETDATE() AS date)
  AND   l.rows_read <> l.rows_written + l.rows_rejected;

This check trusts the pipeline’s own counts, so it catches only internal leaks. Pair it with an independent count taken directly from the source, as in Check 2. A pipeline that undercounts its own input passes Check 1 and fails Check 2.

Check 2: Independent source-to-target count by change window

SELECT  s.src_rows, t.tgt_rows, s.src_rows - t.tgt_rows AS gap
FROM   (SELECT COUNT(*) AS src_rows
        FROM src.PAT_ENC_HSP
        WHERE UPDATE_DATE >= @window_start AND UPDATE_DATE < @window_end) s
CROSS JOIN
       (SELECT COUNT(*) AS tgt_rows
        FROM dw.fact_encounter
        WHERE source_update_date >= @window_start AND source_update_date < @window_end) t;

Use the same filter column on both sides. Many warehouse tables carry the source’s change timestamp for this reason. If yours does not, add it. Without it, you cannot test incremental loads.

Check 3: Control totals on measures

SELECT  s.total_charges, t.total_charges AS dw_total, s.total_charges - t.total_charges AS diff
FROM   (SELECT SUM(TOTAL_CHARGES) AS total_charges FROM src.HSP_ACCOUNT
        WHERE DISCH_DATE >= '2026-09-01' AND DISCH_DATE < '2026-10-01') s
CROSS JOIN
       (SELECT SUM(total_charges) AS total_charges FROM dw.fact_encounter
        WHERE discharge_date >= '2026-09-01' AND discharge_date < '2026-10-01') t;

Compare sums, minimums, and maximums for every numeric measure. A matching sum can hide a sign flip offset by another error, so add the minimum and maximum. Money columns must use fixed decimals on both sides.

Check 4: Row-level comparison with hashes

Totals prove aggregates. They do not prove that each row is right. Hash a canonical string of the compared columns on each side, then compare keys and hashes. The Microsoft HASHBYTES documentation lists supported algorithms and the input-size limits to respect.

WITH s AS (
    SELECT PAT_ENC_CSN_ID AS k,
           HASHBYTES('SHA2_256', CONCAT_WS('|', ISNULL(CONVERT(varchar(30), HOSP_ADMSN_TIME, 121), '~'),
                                               ISNULL(CONVERT(varchar(30), HOSP_DISCH_TIME, 121), '~'),
                                               ISNULL(CAST(DEPARTMENT_ID AS varchar(20)), '~'))) AS h
    FROM src.PAT_ENC_HSP WHERE UPDATE_DATE >= @window_start AND UPDATE_DATE < @window_end),
t AS (
    SELECT encounter_csn AS k,
           HASHBYTES('SHA2_256', CONCAT_WS('|', ISNULL(CONVERT(varchar(30), admit_time, 121), '~'),
                                               ISNULL(CONVERT(varchar(30), discharge_time, 121), '~'),
                                               ISNULL(CAST(department_id AS varchar(20)), '~'))) AS h
    FROM dw.fact_encounter WHERE source_update_date >= @window_start AND source_update_date < @window_end)
SELECT COALESCE(s.k, t.k) AS key_value,
       CASE WHEN s.k IS NULL THEN 'extra in target'
            WHEN t.k IS NULL THEN 'missing in target'
            ELSE 'value mismatch' END AS problem
FROM   s FULL OUTER JOIN t ON t.k = s.k
WHERE  s.k IS NULL OR t.k IS NULL OR s.h <> t.h;

Replace nulls with a marker before hashing, or the concatenation function drops them and different rows can produce the same string. Convert datetimes to a fixed text format on both sides. A hash mismatch from a format difference wastes an afternoon.

Check 5: Transformation rule tests

Every mapping rule deserves a test with known inputs. Write the rule as a CASE expression in the test, run it against the source, and compare the result to the warehouse.

-- Rule: patient class code 1 -> 'Inpatient', 2 -> 'Observation', 3 -> 'Emergency', else 'Other'
SELECT  f.encounter_csn, f.patient_class AS dw_value,
        CASE s.ADT_PAT_CLASS_C WHEN 1 THEN 'Inpatient' WHEN 2 THEN 'Observation'
                               WHEN 3 THEN 'Emergency'  ELSE 'Other' END AS expected_value
FROM    dw.fact_encounter f
JOIN    src.PAT_ENC_HSP s ON s.PAT_ENC_CSN_ID = f.encounter_csn
WHERE   f.patient_class <> CASE s.ADT_PAT_CLASS_C WHEN 1 THEN 'Inpatient' WHEN 2 THEN 'Observation'
                                                  WHEN 3 THEN 'Emergency'  ELSE 'Other' END;

An independent re-implementation catches errors the developer repeated in both code and test. The code values above are illustrative. Take real mappings from the signed-off mapping document.

Check 6: Uniqueness at the declared grain

SELECT  encounter_csn, COUNT(*) AS copies
FROM    dw.fact_encounter
GROUP BY encounter_csn
HAVING  COUNT(*) > 1;

Run this on every fact and dimension table, using the key that defines the grain. Declared primary keys help, but many warehouses skip enforcement for load speed, so the database will not stop duplicates for you.

Check 7: Foreign keys and unknown members

SELECT  COUNT(*) AS facts_with_unknown_dept,
        SUM(CASE WHEN d.department_key IS NULL THEN 1 ELSE 0 END) AS orphan_facts
FROM    dw.fact_encounter f
LEFT JOIN dw.dim_department d ON d.department_key = f.department_key
WHERE   f.department_key = -1 OR d.department_key IS NULL;

Dimensional models use a placeholder row, often with key -1, for facts whose dimension value is missing. A few unknown members are normal. A jump after a release is a defect, so track the count over time against a threshold.

Check 8: Slowly changing dimension integrity

Type 2 dimensions keep history by adding a row each time an attribute changes, with effective dates and a current flag. Kimball dimensional modeling techniques describe the pattern. Three things go wrong: overlapping date ranges, gaps, and more than one current row.

-- Overlapping effective ranges for the same department
SELECT  a.department_id, a.department_key AS key_a, b.department_key AS key_b,
        a.effective_from AS a_from, a.effective_to AS a_to, b.effective_from AS b_from, b.effective_to AS b_to
FROM    dw.dim_department a
JOIN    dw.dim_department b
  ON    a.department_id = b.department_id
 AND    a.department_key < b.department_key
 AND    a.effective_from < ISNULL(b.effective_to, '9999-12-31')
 AND    b.effective_from < ISNULL(a.effective_to, '9999-12-31');

-- More than one current row
SELECT department_id, COUNT(*) AS current_rows
FROM   dw.dim_department WHERE is_current = 1
GROUP BY department_id HAVING COUNT(*) > 1;

Overlaps matter because a fact table joins to the dimension on a date range. Two matching rows duplicate the fact, and every measure doubles for that department. Scenario two below shows the damage.

Check 9: Incremental load and late-arriving data

Incremental loads use a watermark, usually the highest change timestamp loaded so far. The watermark fails in two cases. The source updates a row after you passed its timestamp, or the timestamp column is not the one the application maintains.

-- Source rows changed inside the window that never reached the target
SELECT  s.PAT_ENC_CSN_ID, s.UPDATE_DATE
FROM    src.PAT_ENC_HSP s
WHERE   s.UPDATE_DATE >= DATEADD(day, -3, @run_date)
  AND   NOT EXISTS (SELECT 1 FROM dw.fact_encounter f
                    WHERE f.encounter_csn = s.PAT_ENC_CSN_ID
                      AND f.source_update_date >= s.UPDATE_DATE);

Reading three days back each run, with idempotent upserts, costs little and prevents most late-arrival losses. Choose the overlap from the longest delay you have seen, and test it by replaying a known late update.

Check 10: Data type, precision, and truncation

SELECT  s.PAT_ENC_CSN_ID, LEN(s.DISCH_COMMENT) AS src_len, LEN(f.discharge_comment) AS dw_len
FROM    src.PAT_ENC_HSP s
JOIN    dw.fact_encounter f ON f.encounter_csn = s.PAT_ENC_CSN_ID
WHERE   LEN(s.DISCH_COMMENT) > LEN(f.discharge_comment);          -- truncated text

SELECT  COLUMN_NAME, DATA_TYPE, NUMERIC_PRECISION, NUMERIC_SCALE, CHARACTER_MAXIMUM_LENGTH
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   TABLE_SCHEMA = 'dw' AND TABLE_NAME = 'fact_encounter';

Compare source and target column definitions before the first load. A varchar(50) target for a varchar(200) source silently cuts values if the loader does not raise an error. Decimal scale mismatches cause rounding that surfaces months later in a tie-out.

Check 11: Idempotency, or what happens on a rerun

A well-built load produces the same result when run twice on the same input. Test it. Run the job, take a hash of the target, run it again, and compare.

-- Snapshot-style fingerprint of the target for a window
SELECT COUNT(*) AS row_count,
       CHECKSUM_AGG(CHECKSUM(encounter_csn, admit_time, discharge_time, department_key)) AS fingerprint
FROM   dw.fact_encounter
WHERE  source_update_date >= @window_start AND source_update_date < @window_end;

Treat CHECKSUM_AGG as a quick fingerprint, not a proof. It can collide, and it ignores row order. Use Check 4 hashes when the result must be exact. If a rerun changes the fingerprint, the job appends instead of upserting, and a retry after any failure will duplicate data.

Scenario: Encounter Fact Table Missing 2.4% of Updated Rows

A health system loaded Clarity encounter data into a reporting warehouse every night. Quality analysts noticed that discharge times for some recent stays showed as blank in the warehouse while Hyperspace showed them correctly. The nightly row counts matched, and the load log reported no errors.

I was the QA lead asked to find out why before a length-of-stay dashboard went to the board. Check 1 passed, as the load log predicted. Check 2 with a change-window filter showed the gap.

The load log passed because the pipeline only counted the rows it chose to read. This is the most common ETL blind spot: the pipeline measures itself against its own selection logic. An independent count at the source exposes it. Teams that compare against a source query written by someone other than the pipeline developer catch these defects earliest.

Scenario: A Department Dimension That Doubled Revenue for One Clinic

A finance analyst reported that a surgical clinic’s revenue in the warehouse was exactly double the general ledger for the quarter. Other clinics tied out. The fact table passed uniqueness and count checks, so the cause had to sit in a join.

I ran the overlap query from Check 8 on the department dimension. The clinic had two type 2 rows with overlapping effective dates. A department rename and a cost center change had loaded on the same night.

Two open-ended rows for the same department. Every fact dated after June 14 matched both rows and doubled. The fact table itself had no duplicates because the duplication happened at query time, in the join from fact to dimension. This is why uniqueness checks on the fact table alone are not enough.

The before query

-- BEFORE: date-range join to the dimension returns two rows after Jun 14
SELECT  d.department_name, SUM(f.net_revenue) AS revenue
FROM    dw.fact_revenue f
JOIN    dw.dim_department d
  ON    d.department_id = f.department_id
 AND    f.service_date >= d.effective_from
 AND    f.service_date <  ISNULL(d.effective_to, '9999-12-31')
GROUP BY d.department_name;

The after query and the fix

-- Fix 1: close the older row
UPDATE dw.dim_department
SET    effective_to = '2026-06-14', is_current = 0
WHERE  department_key = 5102;

-- Guard: detect the problem before it reaches reports
SELECT department_id FROM dw.dim_department
GROUP BY department_id
HAVING SUM(CASE WHEN is_current = 1 THEN 1 ELSE 0 END) > 1;

The loader bug was a merge that inserted a new version without closing the previous one when two attributes changed in one batch. The developer fixed the merge logic, and the team added the overlap and current-row checks to the post-load script. Reports for the quarter then tied to the ledger.

A related guard belongs in the fact load: join on the dimension key captured at load time, not on a date range at query time. That removes the join-time multiplication risk, at the cost of a more complex load.

Decision Tree: A Check Failed. Where Do You Look?

Use the failing check to choose the stage to inspect. Fix the earliest failing checkpoint first, because later failures often follow from it.

Which check failed first?
Start at the earliest checkpoint

Counts or totals differ
Checks 1, 2, 3, 9

Values differ, counts match
Checks 4, 5, 10

Duplicates or fan-out
Checks 6, 7, 8

Different on every rerun
Check 11

Look at
Extract filter and watermark
Rejected rows (Check 1)
Source change column
Compare the pipeline to an
independent source query

Look at
Mapping and CASE logic
Column types, precision
Time zone and formats
Hash a canonical string,
replace nulls first

Look at
Grain and business keys
SCD overlaps, current flags
Join type in the load
Test the join, not only
the fact table

Look at
Insert vs upsert logic
Truncate-and-load steps
Non-deterministic functions
Rerun twice, compare
hashes

Many defects trigger more than one branch. The watermark scenario failed Check 2, 4, and 9 at once, and all pointed to the same cause. Resolve the earliest branch, rerun, and see which checks still fail.

Template: Source-to-Target Mapping for Testable ETL

The mapping document defines what each target column should contain. A mapping without a test is a wish. Add a test column to every row, and reviewers can see untested rules at a glance.

For length-of-stay logic, the definition choices in the Clarity LOS article apply to the warehouse too. Write the counting rule into the mapping row, and test the same four boundary cases there.

Load sign-off checklist

Automating ETL Validation After Every Load

Validation that runs only when someone remembers will stop running. Build the checks into the load as a final step, write results to a table, and fail the job when a check breaches its threshold.

CREATE TABLE etl.validation_result (
    batch_id     INT           NOT NULL,
    check_name   VARCHAR(60)   NOT NULL,
    rows_checked BIGINT        NOT NULL,
    violations   BIGINT        NOT NULL,
    threshold    BIGINT        NOT NULL DEFAULT 0,
    status       AS (CASE WHEN violations <= threshold THEN 'PASS' ELSE 'FAIL' END),
    run_at       DATETIME2     NOT NULL DEFAULT SYSDATETIME()
);

-- Example: record Check 6 (duplicates) for the current batch
INSERT INTO etl.validation_result (batch_id, check_name, rows_checked, violations)
SELECT @batch_id, 'fact_encounter_unique_key',
       (SELECT COUNT(*) FROM dw.fact_encounter),
       (SELECT COUNT(*) FROM (SELECT encounter_csn FROM dw.fact_encounter
                              GROUP BY encounter_csn HAVING COUNT(*) > 1) d);

The rows_checked column protects you from the empty-table false pass. A check that reports zero violations against zero rows is a failure, so add a rule that fails any check with no rows examined.

Set thresholds by risk, not by habit. Duplicate keys and overlapping dimension ranges get zero tolerance. Unknown-member rates may allow a small percentage with a named owner. Write each threshold into the mapping document and get the data steward’s signature.

Send failures to the people who can act. A failed Check 8 goes to the ETL developer who owns the merge. A failed Check 7 trend goes to the data steward. A message to a shared inbox no one reads is the same as no alert.

How to Triage a Failed ETL Validation Check

A failed check tells you where to look, not what broke. Work from the cheapest explanation to the most expensive one. Most failures come from the first three causes below, and each takes minutes to confirm.

Step three deserves a habit. A check that fails on day one is as likely to be a bad test as a bad load. Trace one key by hand before you raise a defect, because a false defect costs the developer a day and costs QA its credibility.

Group the failing keys before you read them. Counting failures by source date, department, and encounter status shows a pattern in seconds. Twelve thousand misses that all share one status code point to a mapping rule, not to random loss.

Designing Test Data for ETL Validation

Production-sized data proves volume. Small, designed data proves logic. Build a seed set where you know the expected result for every row, and keep it in version control beside the checks.

Synthetic rows protect patients and make results repeatable. If you must test on masked production data, confirm with the privacy office that the masking method fits the project. Never copy production identifiers into a test environment without that approval.

Keeping ETL Validation Queries Fast

Full-table comparisons on a large fact table can run for hours and compete with the load. Three habits keep them practical.

First, filter by the same change window on both sides, as in Check 2. Second, compare by partition or date slice, and run the slices in parallel when the platform allows. Third, run the cheap checks first. Counts and totals take seconds and often end the investigation before any hash runs.

Indexes on the key and the change-timestamp columns make most checks several times faster. Ask the DBA for read-only access to a replica or a reporting copy, so validation never slows the production load. Record each check’s runtime in the results table, and treat a sudden jump as a signal that data volume or the plan has changed.

Role Checklists for ETL Validation

Common Mistakes in ETL Validation

When I Would Use SQL Checks and When I Would Use Other Tools

I use SQL checks first because they teach the team what correct looks like. I move to a framework once the check list is stable and the team wants scheduling and reporting around it. Commercial tools earn their cost on large, mixed estates and add little value on a single warehouse with a dozen tables. For more query practice, see the SQL for IT Analysts hub and the Epic Clarity SQL guide.

Edge Cases That Break Clean ETL Checks

Validation output can itself carry patient identifiers. Keep results inside the secured environment, report keys and counts instead of values, and follow your privacy office’s guidance for evidence storage.

Questions Analysts Ask About ETL Validation SQL

Is matching row counts enough?

No. Counts prove volume only. Add control totals for completeness and row-level hashes for accuracy. Counts also fail to catch truncation, mapping errors, and duplicated dimension rows.

How do I test an incremental load?

Compare source and target by the source change timestamp, then replay a known late update and a known deletion. Confirm the overlap window picks up changes made after the first load.

How much of the data should I compare at row level?

Compare as much as the platform allows. Hash by partition when tables are large. Use a full comparison for the most recent window and for tables that feed financial or regulatory reports.

Who owns data quality thresholds?

The data steward owns them, with input from the report owners. Developers implement them, and QA verifies them against the signed mapping document.

Run This Before Your Next Load Sign-Off

Pick one fact table in production. Write an independent source query for yesterday’s changes, count both sides by the source update timestamp, and run the late-update anti-join from Check 9. If either shows a gap, your watermark is losing rows. Thirty minutes of checking now beats a quarter of reports built on stale data.

Sources and Further Reading

• Microsoft Learn: HASHBYTES (Transact-SQL). Supported hash algorithms and input limits.
• Kimball Group: Dimensional Modeling Techniques. Reference for fact grain, dimensions, and slowly changing dimension types.
• Your warehouse platform documentation and the signed source-to-target mapping for your pipeline.

Object names are generic and need mapping to your environment. Column names for Epic source tables follow standard Clarity and vary by version. Run every query in a non-production environment first.