ETL Testing Tutorial: Learn Step by Step with SQL Examples

This tutorial teaches you ETL testing from scratch — the core ETL testing concepts, the ETL testing process, and 10 hands-on steps with SQL. By the end, you'll know how to read a mapping document, write validation queries, test transformations, and catch the data bugs that break business reports. No prior ETL experience required — just basic SQL knowledge.

Key ETL Testing Concepts

The ETL Testing Process

Every ETL testing project follows the same process. The 10 hands-on steps below walk through phases 3–5 in detail.

1. Understand requirements — study the business rules, mapping document, and data model.
2. Plan and design tests — write the test strategy, test cases, and expected results for each mapping rule.
3. Validate source data — profile source tables: counts, NULLs, duplicates, formats.
4. Validate the load — row counts, duplicates, NULLs, referential integrity, and aggregates in the target.
5. Validate transformations — recompute every business rule from source data and compare with the target.
6. Regression and performance testing — re-run the suite after changes, and confirm loads finish on time (see ETL performance testing).
7. Report defects and sign off — log defects with SQL evidence, retest fixes, and publish a summary report.

Step 1: Understand the Setup

Every ETL testing project has the same basic structure:

• Source system — where the data comes from (transactional database, CSV files, APIs)
• ETL pipeline — the process that extracts, transforms, and loads the data
• Target system — where the data lands (data warehouse, data lake)
• Mapping document — the spec that defines what goes where and how it transforms

Your job as an ETL tester is to verify that data travels from source to target correctly, following every rule in the mapping document.

Sample Scenario

For this tutorial, we'll work with a simple scenario: an e-commerce company moves customer and order data from its transactional database into a data warehouse for reporting.

Step 2: Validate Row Counts

The first and simplest check: did all the records arrive?

-- Source count
SELECT COUNT(*) AS source_customers FROM app.customers;

-- Target count
SELECT COUNT(*) AS target_customers FROM warehouse.dim_customer;

-- If using SCD Type 2, count distinct business keys instead
SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM warehouse.dim_customer;

If the counts don't match, you've already found a bug. Investigate whether records were dropped during extraction, filtered during transformation, or rejected during loading.

Step 3: Check for Duplicates

Duplicates are the most common ETL bug, especially with incremental loads. Run this check after every load:

-- Find duplicate customers by business key
SELECT customer_id, COUNT(*) AS occurrences
FROM warehouse.dim_customer
WHERE is_current = 1  -- only current records (SCD2)
GROUP BY customer_id
HAVING COUNT(*) > 1;

-- Find duplicate orders
SELECT order_id, COUNT(*) AS occurrences
FROM warehouse.fact_orders
GROUP BY order_id
HAVING COUNT(*) > 1;

Step 4: Validate NULLs and Data Quality

Check that required fields aren't NULL or empty, and that values fall within expected ranges:

-- NULLs in required fields
SELECT 'email' AS field, COUNT(*) AS null_count
FROM warehouse.dim_customer
WHERE email IS NULL OR email = ''
UNION ALL
SELECT 'full_name', COUNT(*)
FROM warehouse.dim_customer
WHERE full_name IS NULL OR full_name = '';

-- Values outside expected ranges
SELECT * FROM warehouse.fact_orders
WHERE order_amount < 0
   OR order_amount > 1000000
   OR order_date > GETDATE();

Step 5: Test Transformations

This is where ETL testing gets interesting. You're verifying that the business logic was applied correctly. For each transformation rule in the mapping document, write a query that compares expected vs. actual.

Example: Name Concatenation

Rule: target.full_name = source.first_name + ' ' + source.last_name

SELECT s.customer_id,
       s.first_name + ' ' + s.last_name AS expected_name,
       t.full_name AS actual_name
FROM app.customers s
JOIN warehouse.dim_customer t
  ON s.customer_id = t.customer_id
WHERE s.first_name + ' ' + s.last_name != t.full_name;

Example: Tax Calculation

Rule: target.tax_amount = source.subtotal * 0.08

SELECT s.order_id,
       ROUND(s.subtotal * 0.08, 2) AS expected_tax,
       t.tax_amount AS actual_tax
FROM app.orders s
JOIN warehouse.fact_orders t
  ON s.order_id = t.order_id
WHERE ROUND(s.subtotal * 0.08, 2) != t.tax_amount;

Example: Date Timezone Conversion

Rule: Convert order_date from EST to UTC (add 5 hours)

SELECT s.order_id,
       DATEADD(hour, 5, s.order_date) AS expected_utc,
       t.order_date_utc AS actual_utc
FROM app.orders s
JOIN warehouse.fact_orders t
  ON s.order_id = t.order_id
WHERE DATEADD(hour, 5, s.order_date) != t.order_date_utc;

Step 6: Check Referential Integrity

Every foreign key in a fact table must point to a valid record in the dimension table. Orphan records break reports.

-- Orders pointing to non-existent customers
SELECT f.order_id, f.customer_key
FROM warehouse.fact_orders f
LEFT JOIN warehouse.dim_customer d
  ON f.customer_key = d.customer_key
WHERE d.customer_key IS NULL;

If this returns rows, the dimension table was loaded after the fact table, or certain customer records were filtered out during ETL.

Step 7: Validate Aggregates

Compare summary-level numbers between source and target. This catches issues that row-level checks might miss:

-- Compare total revenue
SELECT 'Source' AS system,
       SUM(subtotal + tax) AS total_revenue,
       COUNT(*) AS order_count
FROM app.orders
UNION ALL
SELECT 'Target',
       SUM(order_amount) AS total_revenue,
       COUNT(*) AS order_count
FROM warehouse.fact_orders;

Step 8: Test Incremental Loads

After the initial full load is validated, test the incremental (delta) load process:

1. Insert a new record in the source, run ETL, verify it appears in target
2. Update an existing record, run ETL, verify the target reflects the change
3. Delete a record (if applicable), run ETL, verify target handles it (soft delete, hard delete, or SCD closure)
4. No change — run ETL with no source changes, verify no duplicates and no unnecessary updates

-- After incremental load: check for duplicates
SELECT customer_id, COUNT(*) AS cnt
FROM warehouse.dim_customer
WHERE is_current = 1
GROUP BY customer_id
HAVING COUNT(*) > 1;

-- Verify the update was applied
SELECT customer_id, full_name, email, modified_date
FROM warehouse.dim_customer
WHERE customer_id = 12345
ORDER BY modified_date DESC;

Step 9: Write Formal Test Cases

Document your tests so they're repeatable. Here's a template:

Step 10: Report Defects

When a test fails, document the defect with precision:

• Title: Clear and specific ("Tax calculation incorrect for orders with discount")
• Mapping rule violated: Reference the exact rule from the mapping document
• Expected result: What the data should look like
• Actual result: What you found, with specific record IDs
• Impact: How many records are affected and what downstream reports break
• SQL evidence: The query that proves the bug, so the developer can reproduce it

Next Steps

This tutorial covered the fundamentals. To go deeper:

• Learn SCD testing — how to validate Type 1, Type 2, and Type 3 slowly changing dimensions
• Practice performance testing — validate that pipelines handle production-scale data volumes (ETL performance testing guide)
• Explore AI-powered testing — use AI agents to auto-generate validation queries and profile data
• Build a reusable test framework — parameterized SQL queries that work across any project

The ETL Testing Course covers all of these topics with hands-on labs, 79 video lectures, and a career roadmap to help you land your first ETL testing role.

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