Every number on a dashboard passed through a pipeline that somebody had to prove correct. ETL testers and data QA engineers are those people, and their main tool isn't a test framework. It's SQL, written to compare what came in with what came out.
This path follows a real ETL test cycle: profile the source, validate every rule in the source-to-target mapping, reconcile counts and totals, hunt for duplicates and NULLs, test slowly changing dimensions and incremental loads, compare environments for regressions, and trace a wrong number back to its root cause. It's a hands-on skill set that's in steady demand in services companies, banks and insurers, and almost nobody teaches it in a structured way.
You can't write good test cases for data you haven't looked at. Start every test cycle by profiling the source: row counts, distinct counts on keys, NULL and blank counts, min/max ranges and value frequencies. It tells you which tests matter and stops you blaming the ETL for problems that were already in the source.
SELECT COUNT(*) AS total_rows,
COUNT(DISTINCT policy_id) AS distinct_keys,
SUM(CASE WHEN premium IS NULL THEN 1 ELSE 0 END) AS null_premiums,
MIN(start_date) AS min_start, MAX(start_date) AS max_start
FROM src_policies;The mapping document is your test specification. Each row says how a target column is derived from the source. Turn every rule into a query that applies the rule to the source independently and compares the result with what the ETL actually loaded. Any mismatch is a defect.
-- Rule: target.gender = 'Male'/'Female' from source codes 'M'/'F'
SELECT s.emp_no, s.gndr, t.gender
FROM src_emp s
JOIN dim_employee t ON t.employee_id = CAST(SUBSTR(s.emp_no, 2) AS INTEGER)
WHERE t.gender <> CASE s.gndr WHEN 'M' THEN 'Male' WHEN 'F' THEN 'Female' END;Reconciliation proves nothing was lost or duplicated in transit. Compare row counts, then totals of key measures, then the actual rows with EXCEPT in both directions. Documented exclusions (rejected rows, filtered records) must explain every difference exactly.
-- Rows in source that never reached the target
SELECT policy_id, premium FROM src_policies
EXCEPT
SELECT policy_id, premium FROM tgt_policies;
-- Rows in target that don't exist in source (unexpected inserts)
SELECT policy_id, premium FROM tgt_policies
EXCEPT
SELECT policy_id, premium FROM src_policies;Keys must be unique and mandatory columns must be filled. Write a standard set of checks for every target table: duplicate business keys, NULLs in not-null columns, orphaned foreign keys and values outside the accepted list. These catch most loading defects before business users do.
SELECT policy_id, COUNT(*) AS copies
FROM tgt_policies
GROUP BY policy_id
HAVING COUNT(*) > 1;History and incremental logic hide the subtlest bugs. For SCD Type 2, check that each business key has exactly one current row, validity windows don't overlap and there are no gaps. For incremental loads, re-run the same batch and confirm nothing changes, and confirm late-arriving or updated rows are picked up.
-- Each customer must have exactly one current version
SELECT customer_id, COUNT(*) AS current_rows
FROM dim_customer
WHERE is_current = 1
GROUP BY customer_id
HAVING COUNT(*) <> 1;When the ETL code changes, everything that shouldn't change must stay identical. Run the new code in a test environment and compare its output with production: counts per partition, totals per key dimension, then a row-level diff that shows exactly which columns changed.
SELECT COALESCE(d.region, p.region) AS region,
p.total_premium AS prod_total, d.total_premium AS dev_total
FROM (SELECT region, SUM(premium) AS total_premium FROM dev.fct_policies GROUP BY region) d
FULL OUTER JOIN
(SELECT region, SUM(premium) AS total_premium FROM prod.fct_policies GROUP BY region) p
ON p.region = d.region
WHERE COALESCE(d.total_premium, 0) <> COALESCE(p.total_premium, 0);Manual test cycles catch problems once; automated checks catch them every day. Turn your best test queries into a suite that runs after every load and records results in a table, so failures alert the team and you build a history of data quality over time.
INSERT INTO qa_results (run_date, check_name, failing_rows)
SELECT CURRENT_DATE, 'tgt_policies duplicate keys',
(SELECT COUNT(*) FROM (SELECT policy_id FROM tgt_policies
GROUP BY policy_id HAVING COUNT(*) > 1) d);"The report total is wrong" is where a tester earns their keep. Work backwards layer by layer: report, mart, staging, source. Compare the number at each hop until you find the first layer where it changes. The cause is almost always a filter, a join fan-out, a NULL, or a grain mismatch.
Practice the full test cycle on realistic data, the way you would on a real ETL project.
Use the 4-step reconciliation framework on a source and target that look identical but aren't.
Find and fix duplicates, NULLs and bad formats interactively.
Write a test plan and SQL test cases for every layer of a sample pipeline.
ETL testing interviews are practical: expect "write a query to find records missing in the target", duplicate detection, SCD scenarios and how you would test a given mapping rule.
25 questions covering everything above. Score 80% or higher to earn your Data QA / ETL Test Engineer badge on your dashboard.
25 questions · pass at 80%+ to earn your Data QA / ETL Test Engineer badge on your dashboard. One-time payment, lifetime access.