Every ETL testing tool — QuerySurge, Informatica DVO, Great Expectations, a Python script — runs SQL underneath. That's why ETL testing SQL is the first skill interviewers check and the one you'll use every day. This guide gives you the 15 queries that cover most real-world ETL testing, grouped by what they prove.
Why SQL Is the Core ETL Testing Skill
An ETL tester's job is to prove the target data matches the source after the business rules are applied. SQL is how you ask both systems the same question and compare the answers. Tools can automate the comparison, but when a test fails you still need SQL to find out why. If you're brand new, start with ETL testing for beginners.
The Example Tables
All queries below use a simple orders load: src_orders (the source system), stg_orders (staging), fact_orders (the warehouse fact table) and dim_customer (a Type 2 customer dimension). Rename to match your project. Syntax is standard SQL; where databases differ, it's noted.
Completeness Checks: Did All the Data Arrive?
1. Row count reconciliation
SELECT (SELECT COUNT(*) FROM src_orders WHERE order_date = '2026-10-01') AS source_rows, (SELECT COUNT(*) FROM fact_orders WHERE order_date = '2026-10-01') AS target_rows;
Counts should match, minus any rows the rules deliberately filter out. Always compare the same date window.
2. Missing records (source minus target)
SELECT order_id FROM src_orders EXCEPT -- use MINUS in Oracle SELECT order_id FROM fact_orders;
Run it both ways. Target minus source finds extra rows that shouldn't be there.
3. Full row compare
SELECT order_id, customer_id, amount, status FROM src_orders EXCEPT SELECT order_id, customer_id, amount, status FROM fact_orders;
Any row returned has at least one column that changed in transit. Only use it on columns that aren't transformed.
4. Aggregate (sum) reconciliation
SELECT 'source' AS side, SUM(amount) AS total, COUNT(DISTINCT customer_id) AS customers FROM src_orders UNION ALL SELECT 'target', SUM(amount), COUNT(DISTINCT customer_id) FROM fact_orders;
Counts can match while values are wrong. Sums catch truncated decimals and swapped columns.
Accuracy Checks: Were the Rules Applied?
5. Transformation rule check
-- Rule: amount_usd = amount * exchange_rate, rounded to 2 decimals SELECT s.order_id, ROUND(s.amount * s.exchange_rate, 2) AS expected, f.amount_usd AS actual FROM src_orders s JOIN fact_orders f ON f.order_id = s.order_id WHERE ROUND(s.amount * s.exchange_rate, 2) <> f.amount_usd;
Recompute the rule yourself and compare. Zero rows means the rule holds.
6. Column-level mismatch finder
SELECT s.order_id, CASE WHEN s.status <> f.status THEN 'status' END AS status_diff, CASE WHEN UPPER(TRIM(s.city)) <> f.city THEN 'city' END AS city_diff FROM src_orders s JOIN fact_orders f ON f.order_id = s.order_id WHERE s.status <> f.status OR UPPER(TRIM(s.city)) <> f.city;
This tells developers exactly which column is wrong, which makes bug reports much faster to fix.
7. Default and lookup values
SELECT status, COUNT(*) FROM fact_orders GROUP BY status HAVING status NOT IN ('NEW', 'SHIPPED', 'CANCELLED', 'UNKNOWN');
Catches codes that missed the lookup mapping.
Data Quality Checks
8. Duplicate check
SELECT order_id, COUNT(*) AS copies FROM fact_orders GROUP BY order_id HAVING COUNT(*) > 1;
9. NULL check on mandatory columns
SELECT COUNT(*) AS bad_rows FROM fact_orders WHERE order_id IS NULL OR customer_id IS NULL OR order_date IS NULL;
10. Length and format check
SELECT customer_id, email FROM dim_customer WHERE email NOT LIKE '%_@_%._%' OR LENGTH(phone) <> 10;
11. Range and date sanity check
SELECT order_id, amount, order_date FROM fact_orders WHERE amount < 0 OR order_date > CURRENT_DATE OR order_date < '2000-01-01';
For more checks like these, read data validation in ETL.
Data Warehouse Checks
12. Referential integrity (orphan rows)
SELECT f.order_id, f.customer_key FROM fact_orders f LEFT JOIN dim_customer d ON d.customer_key = f.customer_key WHERE d.customer_key IS NULL;
Every fact row must point at a real dimension row.
13. SCD Type 2: one current row per customer
SELECT customer_id, COUNT(*) AS current_rows FROM dim_customer WHERE is_current = 'Y' GROUP BY customer_id HAVING COUNT(*) <> 1;
14. SCD Type 2: no overlapping date ranges
SELECT a.customer_id FROM dim_customer a JOIN dim_customer b ON a.customer_id = b.customer_id AND a.customer_key < b.customer_key WHERE a.valid_from < b.valid_to AND b.valid_from < a.valid_to;
15. Incremental load check
-- Only rows changed since the last successful load should arrive SELECT COUNT(*) AS expected_delta FROM src_orders WHERE updated_at > (SELECT last_loaded_at FROM etl_control WHERE job = 'orders') AND updated_at <= (SELECT current_run_at FROM etl_control WHERE job = 'orders');
Compare this with the number of rows the job actually inserted or updated. The DWH testing guide covers these warehouse patterns in more depth.
Tips for Writing ETL Testing SQL
- Filter both sides the same way. Most false failures come from comparing different date windows.
- Normalise before comparing. Apply the same TRIM, UPPER and ROUND on both sides.
- Save every query. A folder of parameterised queries becomes your regression suite — see ETL automation testing.
- Different databases? If source and target can't be joined, export both results and compare in Python or a tool like QuerySurge.
Our course ETL Testing: From Beginners to Advanced covers database and data warehouse basics, ETL testing concepts, and Python-based validation, with a real-time testing scenario to practise on.
ETL testing tool tutorials: ETL testing SQL queries · QuerySurge ETL testing · Informatica ETL testing · ETL testing tools · ETL automation testing · DWH testing · Python ETL testing framework
Frequently Asked Questions
What SQL is needed for ETL testing?
You need SELECT, WHERE, GROUP BY, HAVING, JOINs (especially LEFT JOIN), set operators like EXCEPT or MINUS, aggregate functions like COUNT and SUM, and CASE expressions. Window functions help for advanced checks.
What are the most common SQL queries in ETL testing?
Row count reconciliation, source-minus-target compares, sum checks, duplicate checks, NULL checks, transformation rule checks, referential integrity (orphan) checks and SCD Type 2 checks.
How do you compare source and target data in SQL?
Use EXCEPT (MINUS in Oracle) in both directions to find missing and extra rows, and join on the business key to compare individual columns.
Is SQL enough for ETL testing?
SQL is enough to get started and to do most manual ETL testing. For automation, testers add Python or a tool such as QuerySurge, which still run SQL underneath.
How do you test an SCD Type 2 dimension with SQL?
Check that each business key has exactly one current row, that date ranges do not overlap, and that a change in a tracked column creates a new row while closing the old one.
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.