ETL Testing Tutorial: Validate Data Pipelines End to End

A revenue dashboard was short by 1.7 million cents after a routine load. The pipeline was green, source and target row counts matched, and no records were rejected. The defect was a currency join that used the transaction date for most rows but the load date for late arrivals. Counts proved movement; only business-level reconciliation exposed changed meaning.

ETL testing must establish lineage from a source fact through transformation to a target measure. The examples below use PostgreSQL-compatible SQL and a small retail pipeline. Adapt date functions and hashing for a different warehouse engine.

Freeze the mapping and the load contract

Before running a pipeline, obtain the source-to-target mapping, schedule, time zone, extraction watermark, reject policy, key strategy, and transformation rules. Resolve vague statements such as “latest exchange rate” into an exact effective-date rule.

For a sales fact load, a reviewable mapping might contain:

Mark the grain of every table. “One row per sale” is not enough if a sale can split by item, shipment, or tender. Grain errors produce plausible duplicate totals and make reconciliation queries misleading.

Build a diagnostic source dataset

Random production-like volume is useful later. Begin with a small dataset where each row has a reason to exist. Include a normal same-day order, a late arrival, duplicate source delivery, null optional field, unmapped currency, cancelled order, boundary timestamp, and corrected record.

Store expected values independently of target code. If the expected spreadsheet repeats the implementation formula, both can share the same defect. Manually calculate a few sentinel rows from the signed-off rule and keep their derivation with the test evidence.

Establish pre-load controls

Record source counts and control totals inside the extraction window before the run. Counts alone cannot detect a missing £100 row replaced by an extra £20 row. Use multiple measures: row count, distinct business keys, null counts, minimum and maximum timestamps, and monetary sums by currency.

SELECT currency_code,
       count(*) AS source_rows,
       count(DISTINCT order_id) AS distinct_orders,
       sum(net_minor) AS net_minor,
       min(updated_at) AS first_update,
       max(updated_at) AS last_update
FROM stage_orders
WHERE updated_at >= TIMESTAMPTZ '2026-07-09 02:00:00+00'
  AND updated_at <  TIMESTAMPTZ '2026-07-10 02:00:00+00'
GROUP BY currency_code
ORDER BY currency_code;

This PostgreSQL sql query creates pre-load controls for a half-open watermark window. The upper boundary belongs to the next batch. Save the result with the batch ID so a later rerun can be compared to the same source snapshot.

Reconcile completeness by disposition

Every extracted row should have a disposition: inserted, updated, intentionally filtered, rejected, or held for later processing. A target count that equals source count can still hide both a duplicate and an omission.

Create a reconciliation equation at business-key grain:

distinct extracted keys
= inserted keys
 + updated existing keys
 + policy-filtered keys
 + rejected keys
 + deferred keys

The text block is an accounting rule, not executable code. The categories must be mutually exclusive and traceable by batch ID. If one source key appears in both “updated” and “rejected,” the operational metadata is unreliable.

Use an anti-join to find unexplained keys:

SELECT s.order_id
FROM stage_orders s
LEFT JOIN fact_sales f
  ON f.source_order_id = s.order_id
LEFT JOIN etl_rejects r
  ON r.source_key = s.order_id AND r.batch_id = 'B20260710'
WHERE s.batch_id = 'B20260710'
  AND f.source_order_id IS NULL
  AND r.source_key IS NULL;

The sql query should return zero rows after filtered and deferred keys are represented in their own auditable tables or added to the comparison. Returned IDs are missing dispositions, not automatically missing facts.

Validate transformations with expected-value queries

Test transformations at the level where the rule applies. For currency conversion, join the rate by currency and transaction date, aggregate at order grain, round once as specified, then compare with target values. Do not reuse the target table’s stored rate to compute the expected result.

WITH expected AS (
  SELECT s.order_id,
         r.rate_to_usd,
         round(sum(s.net_minor) * r.rate_to_usd)::bigint AS expected_usd_minor
  FROM stage_order_lines s
  JOIN fx_rates r
    ON r.currency_code = s.currency_code
   AND r.effective_date = (s.ordered_at AT TIME ZONE s.store_timezone)::date
  WHERE s.batch_id = 'B20260710'
  GROUP BY s.order_id, r.rate_to_usd
)
SELECT e.order_id, e.rate_to_usd, e.expected_usd_minor,
       f.fx_rate, f.net_usd_minor
FROM expected e
JOIN fact_sales f ON f.source_order_id = e.order_id
WHERE e.expected_usd_minor <> f.net_usd_minor
   OR e.rate_to_usd <> f.fx_rate;

This PostgreSQL sql query should return zero rows. A row with matching rates but different amounts indicates grain or rounding logic; a rate difference points to effective-date selection.

Also test trimming, code mappings, Unicode, default values, derived dates, precision, and null semantics. An empty string converted to zero is not the same as unknown. Expected outcomes must state whether the pipeline rejects, defaults, or preserves missing values.

Test incremental loads and corrections

Full loads hide watermark defects. Run consecutive batches with controlled timestamps. Place records one microsecond before the boundary, exactly on it, and just after it. Verify the half-open interval neither loses nor repeats the boundary record.

Then update an existing source order without changing its creation time. The target should update if extraction is based on updated_at. Deliver the same change twice and confirm idempotency. Simulate a failed batch after staging but before publish, restart it, and check that facts, rejects, and audit counts remain correct.

For a corrected transaction, define whether history is overwritten, versioned, or compensated. A fact table that silently overwrites a value can make yesterday’s published report impossible to reproduce. QA must validate the chosen history policy, not impose one.

Verify dimensions and late-arriving relationships

Dimension processing needs separate tests for new members, attribute changes, unknown members, and facts arriving before their dimension. For a type 2 customer dimension, an attribute change should close the old row and create a new current row without overlapping validity.

SELECT customer_source_id
FROM dim_customer
GROUP BY customer_source_id
HAVING count(*) FILTER (WHERE is_current) <> 1
   OR max(valid_from) > max(COALESCE(valid_to, valid_from));

The PostgreSQL sql example is a quick current-row control, but it is not a full overlap detector. Reviewers should expect zero rows, then add a self-join interval check for the warehouse’s exact inclusive or exclusive validity convention.

When a fact arrives before its customer dimension, verify the specified behavior: use an unknown surrogate temporarily, defer the fact, or create an inferred member. Then load the dimension and confirm the repair process updates the relationship without duplicating the fact.

Inspect rejects, schema drift, and observability

Reject testing is successful only if bad data is visible and recoverable. Validate reason code, source key, safe payload, batch ID, detection timestamp, retry eligibility, and final disposition. Correct the missing ZZZ exchange rate, replay ETL-104, and prove the reject closes while exactly one fact appears.

Introduce controlled schema changes in a test source: an added nullable column, renamed field, wider value, and changed type. The pipeline should follow its contract, whether that means accepting a compatible addition or stopping before publishing partial data. A green orchestration task with null-filled targets is a failure.

Dashboards should expose extracted, loaded, updated, rejected, deferred, and filtered counts plus watermark and batch duration. Alerts need actionable identifiers. “Load failed” without batch, stage, and cause lengthens recovery.

Report a release decision from reconciled evidence

The final evidence pack should identify mapping version, code version, batch IDs, source snapshot window, time zones, diagnostic dataset, reconciliation outputs, reject replay, and known exclusions. Retain actual mismatch rows, not only screenshots of green counts.

Approve when every extracted key has one valid disposition, sentinel transformations match independent calculations, incremental boundaries and restarts are safe, dimension history follows policy, rejects are traceable, and downstream totals reconcile at their declared grain. Pipeline completion is an operational signal. ETL quality is proof that business meaning survived the trip.