Writing SQL to validate ETL (2026)

TL;DR

Writing SQL to validate ETL begins with an owned source-to-target mapping, a fixed batch boundary, and an agreed grain. Validate control counts, key coverage, transformations, dimensions, facts, incremental behavior, and aggregates in layers, then publish record-level exceptions with a rule ID and owner.

Key Takeaways

• Derive SQL checks from the source-to-target mapping and declare grain before comparing data.
• Pin every validation run to an immutable batch, watermark, or closed partition.
• Reconcile keys in both directions before comparing transformed attribute values.
• Recalculate important business transformations independently instead of copying pipeline code.
• Test incremental inserts, updates, deletes, retries, and late arrivals as separate behaviors.
• Validate slowly changing dimensions with overlap, current-row, and point-in-time rules.
• Preserve rule-level evidence and keep validation queries read-only, bounded, and rerunnable.

Writing SQL to validate ETL means proving that a pipeline extracted the intended source population, applied the specified transformations, and loaded complete, unique, and internally consistent target records. The work is not a single source-versus-target count. A reliable ETL test aligns snapshots and grain, checks missing keys in both directions, independently recalculates critical fields, and validates how inserts, updates, deletes, retries, and late events behave.

The examples below use PostgreSQL syntax and temporary tables. They are designed as small executable proofs, while the strategy also applies to warehouses and lakehouse SQL engines. Adapt date arithmetic, hashes, merge syntax, and query hints to the engine you actually use. Never copy a dialect-specific function into production validation without confirming its semantics.

TL;DR

Use cheap control checks to locate the failing partition, then drill down to record-level differences. A test should identify the batch, rule, business key, expected value, and actual value without exposing unnecessary sensitive data.

1. Writing SQL to validate ETL Starts With the Mapping

The source-to-target mapping is the test basis. For each target column, capture source fields, join conditions, filters, transformation expression, default behavior, null policy, data type, precision, timezone, and history rule. Also record the target grain. A fact row might represent one order line, while the source export has one order header plus nested items. Comparing those counts directly is meaningless.

Convert the mapping into testable claims. customer_email may be trimmed and lowercased. net_amount may equal quantity times unit price minus allocated discount. order_date_key may use the store's local day rather than UTC. An unknown dimension member may map to surrogate key zero instead of null. Each claim needs examples at boundaries and exceptions.

Ask who owns ambiguous logic. If documentation says "latest customer record" but two records share a timestamp, the pipeline needs a deterministic tie-breaker. QA should surface the ambiguity before writing ROW_NUMBER, not invent a hidden ordering that makes the test pass. Similar ambiguity appears with rounding stage, deleted records, daylight-saving transitions, and duplicate source events.

A compact mapping table makes review concrete:

Use ETL testing interview questions to practice explaining this mapping-first approach under interview pressure.

2. Build a Reproducible Source-to-Target Fixture

A runnable fixture lets you verify the validation SQL itself. This PostgreSQL transaction creates source and target tables with deliberate defects: one missing customer, one unexpected target customer, one incorrect normalization, and one incorrect order total. The rollback removes all temporary work at the end.

BEGIN;

CREATE TEMP TABLE src_customer (
    customer_id bigint PRIMARY KEY,
    email text,
    updated_at timestamptz NOT NULL,
    batch_id integer NOT NULL
);

CREATE TEMP TABLE dim_customer (
    customer_key bigint PRIMARY KEY,
    customer_id bigint NOT NULL UNIQUE,
    email_normalized text,
    source_updated_at timestamptz NOT NULL,
    load_batch_id integer NOT NULL
);

CREATE TEMP TABLE src_order_line (
    order_id bigint NOT NULL,
    line_number integer NOT NULL,
    customer_id bigint NOT NULL,
    quantity integer NOT NULL,
    unit_price numeric(12, 2) NOT NULL,
    discount numeric(12, 2) NOT NULL,
    batch_id integer NOT NULL,
    PRIMARY KEY (order_id, line_number)
);

CREATE TEMP TABLE fact_order_line (
    order_id bigint NOT NULL,
    line_number integer NOT NULL,
    customer_key bigint NOT NULL,
    net_amount numeric(12, 2) NOT NULL,
    load_batch_id integer NOT NULL,
    PRIMARY KEY (order_id, line_number)
);

INSERT INTO src_customer VALUES
    (10, ' ALICE@EXAMPLE.TEST ', '2026-07-13T01:00:00Z', 77),
    (20, 'bob@example.test', '2026-07-13T01:05:00Z', 77),
    (30, NULL, '2026-07-13T01:10:00Z', 77);

INSERT INTO dim_customer VALUES
    (100, 10, 'alice@example.test', '2026-07-13T01:00:00Z', 77),
    (200, 20, 'BOB@example.test', '2026-07-13T01:05:00Z', 77),
    (400, 40, 'extra@example.test', '2026-07-13T01:15:00Z', 77);

INSERT INTO src_order_line VALUES
    (5001, 1, 10, 2, 12.50, 5.00, 77),
    (5001, 2, 20, 1, 10.00, 0.00, 77);

INSERT INTO fact_order_line VALUES
    (5001, 1, 100, 20.00, 77),
    (5001, 2, 200, 9.00, 77);

-- Place validation queries here while practicing.
ROLLBACK;

Test queries with positive and negative fixtures. Correct the uppercase email and bad net amount, add customer 30, remove customer 40, then verify all corresponding exceptions disappear. Also seed one target row from another batch so a forgotten load_batch_id filter causes a visible false positive.

Production tests should use an immutable snapshot, cloned partition, or engine-supported time travel when available. A source that continues changing while the target comparison runs can never provide a stable oracle.

3. Validate Batch Boundaries and Control Totals First

Every comparison needs a shared boundary. Batch ETL may use an explicit batch_id, source extract file, or partition. Incremental pipelines may use a half-open watermark window such as [previous_watermark, current_watermark). Streaming systems may use offsets and a checkpoint. Store that boundary with the validation run. "Records loaded today" is not deterministic unless day, timezone, and completion state are defined.

Begin with control totals that explain population movement:

WITH source_control AS (
    SELECT
        batch_id,
        COUNT(*) AS source_rows,
        COUNT(DISTINCT customer_id) AS source_keys,
        MIN(updated_at) AS min_updated_at,
        MAX(updated_at) AS max_updated_at
    FROM src_customer
    WHERE batch_id = 77
    GROUP BY batch_id
),
target_control AS (
    SELECT
        load_batch_id AS batch_id,
        COUNT(*) AS target_rows,
        COUNT(DISTINCT customer_id) AS target_keys,
        MIN(source_updated_at) AS min_updated_at,
        MAX(source_updated_at) AS max_updated_at
    FROM dim_customer
    WHERE load_batch_id = 77
    GROUP BY load_batch_id
)
SELECT *
FROM source_control AS s
FULL OUTER JOIN target_control AS t USING (batch_id);

Different totals may be correct if the mapping filters invalid rows, deduplicates events, expands nested arrays, or writes rejects. Reconcile the movement equation: source read equals inserted plus updated plus unchanged plus intentionally rejected, adjusted for any documented fanout. For deletes, track whether they become physical removals, soft-delete flags, or expired dimension versions.

Control totals are triage, not proof. A duplicate and a missing record can cancel. Counts grouped by tenant, source system, business date, status, or file narrow the problem and prevent cancellation across partitions. Continue to key and value checks even when top-level counts match.

Validate the controls themselves. If the pipeline reports zero source records, confirm that the extract actually contains the expected partition. A green equality of zero to zero is a common silent failure.

4. Reconcile Business Keys in Both Directions

Compare keys before comparing attributes. A left anti-join finds source keys not represented in the target. Reverse it to find unexpected target keys. Use the complete business key, not a surrogate key assigned during loading. In the fixture, customer_id is the shared natural identifier.

-- Source records missing from the target batch.
SELECT s.customer_id
FROM src_customer AS s
WHERE s.batch_id = 77
  AND NOT EXISTS (
      SELECT 1
      FROM dim_customer AS t
      WHERE t.customer_id = s.customer_id
        AND t.load_batch_id = 77
  );

-- Target records with no source record in the same batch.
SELECT t.customer_id
FROM dim_customer AS t
WHERE t.load_batch_id = 77
  AND NOT EXISTS (
      SELECT 1
      FROM src_customer AS s
      WHERE s.customer_id = t.customer_id
        AND s.batch_id = 77
  );

An extra target key might be a real defect, a retained historical entity, a late record from a prior batch, or a valid union from another source. Write the expected population rule first. For a slowly changing dimension, compare source keys to current target versions, not only versions created by this load. For a fact, use the composite transaction grain such as (order_id, line_number).

Check duplicate keys independently on both sides. A join between duplicated source and target keys creates many-to-many output and makes attribute mismatches difficult to interpret. Use GROUP BY ... HAVING COUNT(*) > 1 or a window count, scoped to the relevant versions.

Null business keys need an explicit reject or default policy. SQL equality never matches null to null, and coalescing null to a made-up sentinel may collide with real data. Prefer a separate rule that reports missing required keys and exclude them from the normal key reconciliation.

The validating data integrity with SQL guide covers additional null, duplicate, and relationship patterns.

5. Independently Recalculate Transformations

Once key coverage is understood, compare mapped attributes. Recalculate expected values from raw source fields in a query that is clear enough to review. Do not call the same transformation function used by the pipeline for every critical rule, because a shared defect can make implementation and oracle agree incorrectly. Use an independent formula or trusted reference data.

Email normalization in the fixture is a deterministic field comparison:

SELECT
    s.customer_id,
    LOWER(BTRIM(s.email)) AS expected_email,
    t.email_normalized AS actual_email
FROM src_customer AS s
JOIN dim_customer AS t ON t.customer_id = s.customer_id
WHERE s.batch_id = 77
  AND t.load_batch_id = 77
  AND LOWER(BTRIM(s.email)) IS DISTINCT FROM t.email_normalized;

The fact transformation combines multiplication and discount. Calculate at line grain and preserve both values:

SELECT
    s.order_id,
    s.line_number,
    (s.quantity * s.unit_price - s.discount)::numeric(12, 2) AS expected_net,
    t.net_amount AS actual_net
FROM src_order_line AS s
JOIN fact_order_line AS t
  ON t.order_id = s.order_id
 AND t.line_number = s.line_number
WHERE s.batch_id = 77
  AND t.load_batch_id = 77
  AND (s.quantity * s.unit_price - s.discount)::numeric(12, 2)
      IS DISTINCT FROM t.net_amount;

Confirm rounding location and mode. Rounding every line and then summing may differ from summing unrounded values and rounding once. Decimal scale, currency conversion time, and negative adjustment policy belong in the mapping. Do not choose a tolerance only to suppress differences.

For code mappings, join to the owned reference table and report unmapped source values. For parsing, test malformed input and rejected records. For defaults, distinguish missing from explicit null. Each transformation query should make its assumptions visible rather than hiding them in nested expressions.

6. Test Dimensions, Surrogate Keys, and SCD Type 2 History

Warehouse dimensions add identity and time. Facts usually store surrogate keys, so validate that each source business key resolves to the intended dimension version. Unknown or late dimensions may map to a documented placeholder key. Null, zero, and minus one are not interchangeable unless the model defines them.

For a Type 2 slowly changing dimension, a business key has multiple versions with nonoverlapping effective intervals. Typically exactly one version is current. Define whether the end boundary is inclusive or exclusive. Half-open periods [effective_from, effective_to) avoid a shared instant belonging to two versions.

-- Detect overlapping Type 2 periods for the same customer.
SELECT
    a.customer_id,
    a.customer_key AS first_key,
    b.customer_key AS second_key,
    a.effective_from,
    a.effective_to,
    b.effective_from,
    b.effective_to
FROM dim_customer_history AS a
JOIN dim_customer_history AS b
  ON a.customer_id = b.customer_id
 AND a.customer_key < b.customer_key
 AND a.effective_from < b.effective_to
 AND b.effective_from < a.effective_to;

-- Exactly one current row per business key.
SELECT customer_id, COUNT(*) AS current_count
FROM dim_customer_history
WHERE is_current
GROUP BY customer_id
HAVING COUNT(*) <> 1;

If open-ended rows use a sentinel date instead of infinity, document it and test that the chosen date is outside supported business time. Validate agreement among is_current, effective end, and version number. Check that an unchanged source record does not create a new version and that a tracked attribute change expires the old version and inserts one new version.

Point-in-time fact lookup must select the dimension version effective when the fact occurred, not necessarily the current version. Test boundary instants and late facts explicitly. A fact arriving today for last month's order should normally link to the version valid last month, depending on the model.

7. Validate Incremental Loads, CDC, Deletes, and Late Data

A full refresh can hide incremental defects. Build stateful scenarios across at least two runs. First load baseline rows. Before the next run, add one source row, change one tracked field, change one untracked field, delete one row, resend an unchanged event, and introduce a late record whose event time precedes the watermark. Validate each outcome independently.

Watermarks need precise semantics. With [low, high), a record equal to high belongs to the next window. Store the extraction boundary and source timezone. If updated_at has insufficient precision or multiple rows share a value, use a compound cursor such as (updated_at, primary_key) or another supported ordering. Otherwise, a crash between pages can skip or duplicate rows.

Change data capture adds operation type and ordering. Confirm that inserts, updates, and deletes are applied once, transaction order is preserved where required, and replay is idempotent. A duplicated delivery should not create two facts. An update arriving before its insert needs a documented buffer, upsert, or reject behavior. Tombstones need retention long enough for consumers to observe them.

Late data should have a policy, not an arbitrary cutoff hidden in SQL. It may update an old partition, enter a correction table, or be rejected after a business deadline. Validate both within-lateness and beyond-lateness examples. Reconcile by event time and load time so you can distinguish a late business event from a delayed pipeline.

Retry the exact same batch and compare the target before and after. Stable keys and measures should not change unless the design intentionally records a new audit run. This idempotency test catches append-on-retry defects that a one-pass reconciliation cannot reveal.

8. Validate Facts, Aggregates, and Cross-Table Integrity

A fact table must have the expected grain, valid dimension references, correct measures, and consistent dates. Start with duplicate composite keys. Then check foreign keys, including whether placeholder dimension keys are allowed. Database constraints may be absent in analytical stores, so detective SQL remains necessary.

Aggregate reconciliation should use business partitions. For the fixture, compare source and target counts and net totals by order after independently applying the mapping:

WITH expected AS (
    SELECT
        order_id,
        COUNT(*) AS line_count,
        SUM(quantity * unit_price - discount)::numeric(14, 2) AS net_total
    FROM src_order_line
    WHERE batch_id = 77
    GROUP BY order_id
),
actual AS (
    SELECT
        order_id,
        COUNT(*) AS line_count,
        SUM(net_amount)::numeric(14, 2) AS net_total
    FROM fact_order_line
    WHERE load_batch_id = 77
    GROUP BY order_id
)
SELECT
    COALESCE(e.order_id, a.order_id) AS order_id,
    e.line_count AS expected_count,
    a.line_count AS actual_count,
    e.net_total AS expected_total,
    a.net_total AS actual_total
FROM expected AS e
FULL OUTER JOIN actual AS a USING (order_id)
WHERE e.line_count IS DISTINCT FROM a.line_count
   OR e.net_total IS DISTINCT FROM a.net_total;

Group further by tenant, currency, tax jurisdiction, business date, or source system when those dimensions affect logic. Never sum amounts across currencies unless the mapping includes a defined conversion. Compare the target's date key with the source instant translated through the stated business timezone, especially around midnight and daylight-saving changes.

Validate derived aggregates against facts, not against the same report endpoint that reads those aggregates. If a materialized view is eventually refreshed, capture the refresh version and wait for a supported completion signal. A fixed sleep produces intermittent results and makes slow failures hard to distinguish from wrong totals.

9. Scale ETL SQL Without Weakening the Oracle

Full row-by-row comparison may be expensive, but performance shortcuts should narrow investigation, not redefine correctness. Start with partition metadata and control totals. Compute deterministic hashes at a documented grain to locate mismatched partitions, then perform record comparisons only within those partitions. Both sides must serialize fields identically, including field order, delimiter escaping, null marker, timestamp precision, and numeric formatting.

Sampling is useful for exploratory checks and cannot prove completeness. Random samples may miss rare but severe defects, while first-row samples overrepresent one source file. Use stratified samples across partitions and boundary cases when cost prevents exhaustive value comparison, and clearly label residual risk. Always perform exhaustive key and critical control checks when feasible.

Write sargable filters on partition and indexed columns. Avoid wrapping the filtered source timestamp in a function when a direct range predicate can prune data. Inspect the query plan for unintended full scans, data shuffles, or many-to-many joins. Run heavy checks on approved compute and schedules so validation does not harm production workloads.

Persist exceptions rather than repeatedly rescanning to display them. Store validation run ID, rule ID, batch, safe business key, expected and actual summaries, and query version. Apply access control because warehouse evidence may contain personal or financial data.

Keep detection separate from repair. A test query should be read-only. Quarantine, replay, correction, or backfill requires an owned runbook with approval, audit, and post-repair validation. Silent auto-fixes can destroy the evidence needed to understand a systemic mapping defect.

10. Operationalize Writing SQL to validate ETL

Organize checks into gates. Before a load, validate input presence, schema, partition, and duplicate files. During the load, capture extraction watermark, rows read, transformed, rejected, inserted, updated, and deleted. After the load, run key coverage, transformation, relationship, history, aggregate, and freshness checks. After publication, monitor timeliness and downstream usability.

Version validation SQL beside mappings or transformation code. Require review when a target column, filter, join, rounding rule, or history policy changes. Give each rule a severity and owner. Critical missing keys or tenant leakage may block publication. A known optional attribute issue may quarantine a partition or warn. Time-bound every exception and record who accepted the residual risk.

Make runs deterministic and rerunnable. Supply parameters rather than embedding CURRENT_DATE; record batch IDs and as-of times; keep validation queries idempotent. Compile or parse queries in a lower environment, seed controlled defects, and ensure each critical rule actually fails before relying on it in a release gate.

A useful pipeline report answers three questions: Did the intended data arrive? Is it correct according to the mapping? Can downstream users trust the published partition? One green row-count badge cannot answer all three. Link control totals to record-level exceptions and to the code version that created them.

For adjacent preparation, SQL queries for QA interviews provides compact exercises, while data warehouse testing guide expands dimensional-model scenarios.

Interview Questions and Answers

Q: How do you validate an ETL pipeline end to end?

I pin a source and target boundary, confirm the mapping and grain, then validate extraction controls, key coverage, transformations, rejects, dimensions, facts, and aggregate reconciliation. I also test incremental inserts, updates, deletes, late events, replay, and freshness. Each exception identifies the batch, rule, key, expected value, and actual value.

Q: Why are equal source and target counts insufficient?

A missing record and a duplicate can cancel, transformations can be wrong, and the target can contain orphaned or stale data. I compare distinct business keys in both directions, mapped values, relationships, and partitioned totals after checking counts.

Q: How do you validate a Type 2 dimension?

I verify one current row per business key, legal nonoverlapping effective periods, stable history for unchanged records, and a new version for tracked changes. I also test fact lookup at boundary times and late-arriving facts.

Q: What is a good incremental-load test?

Across two controlled runs, I add a record, update tracked and untracked fields, delete a record, resend an event, and deliver a late record. I verify the watermark boundary, operation handling, target state, control counts, and idempotent replay.

Q: How do you compare transformation logic independently?

I calculate the expected value from raw source fields using a clear specification or trusted reference, not blindly reusing the production transformation function. I test nulls, boundaries, precision, timezones, and unmapped values. The query reports expected and actual values at the target grain.

Q: How do you make large ETL reconciliations efficient?

I prune by closed partitions, compare control totals and deterministic hashes to locate mismatches, then drill down only where needed. I preserve exhaustive checks for keys and critical controls when feasible, inspect execution plans, and never present sampling as proof of completeness.

Common Mistakes

• Comparing source and target without pinning an equivalent batch, watermark, or snapshot.
• Comparing row counts at different grains, such as order headers versus order lines.
• Using the target surrogate key as if it were the shared source business key.
• Checking only source-to-target missing rows and ignoring unexpected target records.
• Joining duplicated keys and then interpreting many-to-many mismatches as field defects.
• Reusing the exact production transformation as the only test oracle.
• Ignoring rounding stage, currency, timezone, and null-default semantics.
• Testing a full refresh but never exercising incremental updates, deletes, replay, or late data.
• Treating matching aggregate totals as proof of record-level correctness.
• Running unbounded validation scans or exposing sensitive values in CI artifacts.
• Allowing known exceptions to remain permanent without an owner and expiry.

Conclusion

Writing SQL to validate ETL is a mapping-driven engineering discipline. Fix the comparison boundary and grain, reconcile business keys in both directions, calculate critical transformations independently, and test dimensions, facts, incremental behavior, and aggregates as distinct layers. Preserve the evidence needed to locate a defect instead of reducing every result to one count.

Start with the temporary two-table fixture and one batch ID. Seed a missing key, an extra key, and a transformed-value defect, then prove that your queries isolate all three. Once the oracle is trustworthy, parameterize it for a controlled pipeline partition and connect its severity, ownership, and remediation policy to the delivery workflow.

Interview Questions and Answers

Frequently Asked Questions

Related Guides

• How to Add accessibility checks to CI (2026)
• How to Add CI to a test framework (2026)
• How to Add logging to a test framework (2026)
• How to Add parallel execution to a framework (2026)
• How to Add reporting to a test framework (2026)
• How to Scroll to an element in Cypress (2026)