Data Reconciliation in ETL Testing: Meaning, Types and SQL Examples

Data reconciliation is how ETL testers prove that the data in the target matches the source after every load. Banks reconcile money to the paisa, and every data warehouse needs the same discipline. This guide explains the meaning, the levels of reconciliation and the SQL to run each one.

What Does Data Reconciliation Mean?

Data reconciliation is the process of comparing source and target data to confirm they agree, and explaining every difference. In ETL testing it happens after each load and at each layer: source to staging, staging to warehouse, warehouse to report.

The Four Levels of Reconciliation

Run levels 1–2 on every load, and levels 3–4 on critical tables or when 1–2 fail.

Reconciliation SQL Examples

-- 1. Count reconciliation by load date
SELECT 'source' AS side, COUNT(*) FROM src.payments WHERE pay_date = '2026-10-01'
UNION ALL
SELECT 'target', COUNT(*) FROM dw.fact_payments WHERE pay_date = '2026-10-01';

-- 2. Aggregate reconciliation by a business dimension
SELECT branch_code, SUM(amount) AS total FROM src.payments
WHERE pay_date = '2026-10-01' GROUP BY branch_code
EXCEPT
SELECT branch_code, SUM(amount) FROM dw.fact_payments
WHERE pay_date = '2026-10-01' GROUP BY branch_code;   -- rows returned = branches that do not match

-- 3. Record reconciliation: missing in target
SELECT payment_id FROM src.payments WHERE pay_date = '2026-10-01'
EXCEPT
SELECT payment_id FROM dw.fact_payments WHERE pay_date = '2026-10-01';

Field-Level (Hash) Reconciliation

Comparing every column is easiest by hashing each row on both sides and comparing hashes by key. Function names differ by database (MD5 in PostgreSQL and MySQL, HASHBYTES in SQL Server, STANDARD_HASH in Oracle).

-- PostgreSQL example
WITH s AS (
  SELECT payment_id, MD5(CONCAT_WS('|', amount, branch_code, status)) AS h FROM src.payments
), t AS (
  SELECT payment_id, MD5(CONCAT_WS('|', amount, branch_code, status)) AS h FROM dw.fact_payments
)
SELECT s.payment_id FROM s JOIN t ON t.payment_id = s.payment_id
WHERE s.h <> t.h;

Apply the same transformations (trim, formats, data types) to the source side first, or every row will differ.

A Simple Reconciliation Report

Automate it: store each run in a results table and alert on FAIL. See ETL automation testing.

Why Source and Target Do Not Match

• Different filters or time zones between the source query and the load.
• Rejected rows (bad data) not counted. Always reconcile source = target + rejects.
• Duplicates from joins or re-runs.
• Rounding and data type truncation.
• Late-arriving data loaded in the next batch.
• Intended business rules (filtered test accounts) not applied to the source query.

Data Reconciliation Tools

Related: data migration testing and data validation in ETL.

Data warehouse basics: data warehouse concepts · star vs snowflake schema · fact and dimension tables · SCD types · data warehouse testing · data warehouse interview questions

Data testing skills: SQL for testers · data migration testing · Power BI testing · big data testing · data quality checks · ETL tools list · ADF & Databricks testing · database testing interview questions · Python for testers · SQL joins · test data management · ETL defects examples · Snowflake ETL testing · AWS Glue testing · data lake testing

Frequently Asked Questions

What is data reconciliation in ETL testing?

It is comparing source and target data after a load, at count, aggregate, record and field level, to confirm they match and explain any difference.

What are the types of data reconciliation?

Count reconciliation, aggregate (sum) reconciliation, record-level reconciliation on keys, and field-level reconciliation, often using row hashes.

How do you do data reconciliation in SQL?

Compare COUNT(*) and SUM() on both sides with the same filters, use EXCEPT/MINUS to find missing keys, and compare row hashes to find changed values.

What is a reconciliation report?

A summary per table and load showing source and target counts and totals, differences and pass/fail status, used as evidence for sign-off.

Why do source and target counts differ?

Common reasons are rejected rows, different filters or time zones, duplicates, late-arriving data and intended business rules.

AN

Written by

Asim Noaman Lodhi

Certified Google Partner · QA Consultant · 12+ Years IT

QA consultant specializing in ETL testing and data quality. Trained 913+ students to transition into data testing roles through hands-on, real-world instruction.

4.5 Rating 913+ Students Google Partner 79 Lectures