What Is ETL Testing with Example? The ETL Testing Process Step by Step

Definitions of ETL testing are easy to memorise and hard to understand. So instead of another definition, this guide walks through one realistic example from start to finish — the kind of task you would get in your first week as an ETL tester at an Indian IT company. If you're brand new, first read ETL full form and meaning.

What Is ETL Testing? The Short Answer

ETL testing is checking that data moved from a source system to a target system is complete, correct, and clean. You compare the source with the target using SQL, and you confirm every business rule (transformation) was applied properly. For the full concept guide, see what is ETL testing.

What Is ETL in Data Warehousing?

A data warehouse is a central database built for reporting. Companies don't run reports directly on their live app databases — that would slow down the app. Instead, ETL jobs copy data into the warehouse every night or every hour. ETL is the "delivery truck" of a data warehouse: it extracts data from many sources, transforms it into one consistent shape, and loads it into fact and dimension tables. Read more in our DWH testing guide.

The Example Scenario: An E-Commerce Orders Load

Imagine an online store in India. Every night, an ETL job moves yesterday's orders into the data warehouse.

Source table: src_orders (from the shopping app)

Business rules (the "T" in ETL) given in the mapping document:

1. Load only delivered orders (status is case-insensitive).
2. Convert order_date from DD-MM-YYYY to a proper DATE.
3. Add 18% GST: total_amount = amount_inr * 1.18, rounded to 2 decimals.
4. Status must be stored in uppercase.

Expected target: fact_orders

The ETL Testing Process (7 Steps)

SQL Checks with Example

1. Count check (completeness) — expected target count equals source delivered count:

SELECT COUNT(*) FROM src_orders WHERE UPPER(status) = 'DELIVERED';  -- expect 2
SELECT COUNT(*) FROM fact_orders;                                     -- expect 2

2. Missing records (source minus target):

SELECT order_id FROM src_orders WHERE UPPER(status) = 'DELIVERED'
EXCEPT
SELECT order_id FROM fact_orders;   -- expect 0 rows

3. Transformation check (GST rule):

SELECT s.order_id, ROUND(s.amount_inr * 1.18, 2) AS expected, f.total_amount AS actual
FROM src_orders s JOIN fact_orders f ON s.order_id = f.order_id
WHERE ROUND(s.amount_inr * 1.18, 2) <> f.total_amount;   -- expect 0 rows

4. Duplicate check:

SELECT order_id, COUNT(*) FROM fact_orders
GROUP BY order_id HAVING COUNT(*) > 1;   -- expect 0 rows

5. Data quality check:

SELECT * FROM fact_orders
WHERE customer_id IS NULL OR order_date IS NULL OR status <> UPPER(status);   -- expect 0 rows

More queries like these are in our ETL testing tutorial and data validation in ETL guide.

Bugs You Would Catch in This Example

• Case-sensitivity bug: the developer filtered status = 'DELIVERED', so order 1003 ("Delivered") is missing. The count check finds it.
• Wrong GST: GST applied as 1.8 instead of 1.18, or rounding skipped. The transformation check finds it.
• Date swap: 05-09-2026 loaded as 9 May instead of 5 September. A date-range check finds it.
• Duplicates on re-run: running the job twice doubles the rows. The duplicate check finds it.

Each of these would make a sales report wrong — which is exactly why companies hire ETL testers.

Practice Next

Recreate these two tables in any free database (MySQL, PostgreSQL, or SQLite), break the data on purpose, and see if your queries catch it. Then try the same checks in Python with pandas — see ETL pipeline testing with Python. If you want a guided path, read the ETL testing beginner's guide.

More guides for Indian students: ETL full form · ETL testing with example · ETL testing jobs in India · ETL testing course in India · ETL training in Hyderabad, Bangalore & Pune

Frequently Asked Questions

What is ETL testing with a simple example?

Suppose an ETL job loads delivered orders into a data warehouse and adds 18% GST. ETL testing checks that every delivered order arrived, cancelled ones were filtered out, the GST amount is correct, and there are no duplicates or NULLs.

What is the ETL testing process?

The ETL testing process is: understand requirements, study source and target, write test cases, prepare test data, run the ETL job, validate with SQL, and log defects and sign off.

What is ETL in data warehousing?

ETL is the process that fills a data warehouse. It extracts data from source systems, transforms it into a consistent format, and loads it into fact and dimension tables used for reporting.

Which SQL queries are used in ETL testing?

Common queries are row count checks, source-minus-target (EXCEPT or MINUS) checks, transformation checks with JOINs, duplicate checks with GROUP BY and HAVING, and NULL checks.

Can beginners learn ETL testing?

Yes. If you can write basic SQL, you can start ETL testing. Most beginners become comfortable with the full process in 6–8 weeks of regular practice.

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