ETL Testing Scenarios: 18 Test Scenarios With SQL and Expected Results

These ETL testing scenarios cover what you check on every data warehouse load, from structure and completeness to transformations, duplicates, dates and reconciliation. Most tutorials give one-line definitions; here each scenario has what to check, an SQL query and the expected result, so you can run it.

Examples use one retail-orders load: source src_orders and src_customers, target fact_orders and dim_customer. You can download this exact data, with 8 planted defects, in the free ETL testing end-to-end project and run every query yourself.

What Is an ETL Test Scenario?

A test scenario is what you need to verify (for example “no duplicate orders in the target”). Test cases are the detailed steps and data for each scenario; see 35 ETL test cases. Good ETL scenarios come from the source-to-target mapping document and the business rules.

The 18 ETL Test Scenarios

1. Structure validation

What to check: Target tables have the expected columns, data types and lengths from the design document. Expected: Matches the data model exactly.

SELECT column_name, data_type, character_maximum_length
FROM information_schema.columns
WHERE table_name = 'fact_orders';

2. Mapping document validation

What to check: Every target column has a source and a rule in the mapping document, and the rule is testable. Expected: No column without a source or rule.

3. Constraint validation

What to check: Primary keys are unique and not null; foreign keys exist in the parent table. Expected: 0 rows.

SELECT f.order_id FROM fact_orders f
LEFT JOIN dim_customer d ON d.customer_key = f.customer_key
WHERE f.customer_key IS NOT NULL AND f.customer_key <> '' AND d.customer_key IS NULL;

4. Data consistency check

What to check: The same attribute has the same meaning and format everywhere (for example city names in every table). Expected: 0 rows.

SELECT customer_id, city FROM dim_customer
WHERE city <> TRIM(city);

5. Data completeness validation

What to check: Every eligible source record reached the target, and nothing extra arrived. Expected: 0 rows (both directions).

SELECT order_id FROM src_orders WHERE status <> 'CANCELLED'
EXCEPT
SELECT order_id FROM fact_orders;

6. Data correctness validation

What to check: Values in the target match the source for the same key. Expected: 0 rows.

SELECT f.order_id FROM fact_orders f
JOIN src_orders o ON o.order_id = f.order_id
WHERE f.quantity <> o.quantity OR f.unit_price <> o.unit_price;

7. Data transformation validation

What to check: Business rules are applied correctly to derived columns. Expected: 0 rows.

SELECT order_id, net_amount FROM fact_orders
WHERE ROUND(net_amount, 2) <> ROUND(quantity * unit_price - discount, 2);

8. Data quality validation

What to check: Values are in valid ranges and formats (no negative amounts, valid emails). Expected: 0 rows.

SELECT order_id, net_amount FROM fact_orders WHERE net_amount < 0;

9. NULL validation

What to check: Mandatory columns are never NULL or blank. Expected: 0 rows.

SELECT order_id FROM fact_orders
WHERE customer_key IS NULL OR TRIM(customer_key) = '';

10. Duplicate validation

What to check: Each business key appears once in the target. Expected: 0 rows.

SELECT order_id, COUNT(*) FROM fact_orders
GROUP BY order_id HAVING COUNT(*) > 1;

11. Date validation

What to check: Dates are valid, in range and converted to the right time zone. Expected: 0 rows.

SELECT f.order_id, f.order_date FROM fact_orders f
JOIN src_orders o ON o.order_id = f.order_id
WHERE f.order_date <> DATE(o.order_ts_utc, '+5 hours', '+30 minutes');

12. Full data validation with MINUS / EXCEPT

What to check: All columns match, not just keys: compare full rows in both directions. Expected: 0 rows.

SELECT order_id, quantity, unit_price, discount FROM src_orders WHERE status <> 'CANCELLED'
EXCEPT
SELECT order_id, quantity, unit_price, discount FROM fact_orders;

13. Filter validation

What to check: Rows that the rules exclude (for example cancelled orders) are not loaded. Expected: 0 rows.

SELECT f.order_id FROM fact_orders f
JOIN src_orders o ON o.order_id = f.order_id
WHERE o.status = 'CANCELLED';

14. Data cleansing validation

What to check: Cleansing rules (trim, case, standard codes) were applied. Expected: 0 rows.

SELECT customer_id, email FROM dim_customer WHERE email <> LOWER(email);

15. Incremental load validation

What to check: Only new and changed rows since the last run are loaded; unchanged rows stay untouched. Expected: Changed rows applied once; others unchanged.

16. SCD Type 2 validation

What to check: Old versions are closed and exactly one current row exists per business key. Expected: 0 rows.

SELECT customer_id, COUNT(*) FROM dim_customer
WHERE current_flag = 'Y' GROUP BY customer_id HAVING COUNT(*) > 1;

17. Total reconciliation

What to check: Source and target totals agree after applying the same filters. Expected: source_total = target_total.

SELECT (SELECT ROUND(SUM(quantity * unit_price - discount), 2) FROM src_orders WHERE status <> 'CANCELLED') AS source_total,
       (SELECT ROUND(SUM(net_amount), 2) FROM fact_orders) AS target_total;

18. Report validation

What to check: Dashboard numbers equal a recalculation from source with the same filters and grouping. Expected: Every report figure matches.

Summary Table

Real-Time ETL Testing Scenarios

On real projects these scenarios appear as incidents: a cancelled order inflating revenue, a time-zone rule moving an order to the wrong day, or a rerun duplicating a batch. Practise explaining them with scenario-based ETL testing interview questions and the real-time project explanation.

Practise and Learn

Download the free end-to-end project and run all of these scenarios against data with 8 hidden defects. For a guided path, ETL Testing: From Beginners to Advanced covers ETL concepts, data quality, transformation with Python and Power Query, and a real-time testing scenario. Interview soon? ETL Testing Interview Questions & Answers – Practice Tests has 500+ questions including scenario-based ones.

ETL testing fundamentals: types of ETL testing · ETL testing techniques · ETL testing challenges · data transformation testing · data integrity testing · ETL test cases · ETL testing process

Frequently Asked Questions

What are ETL testing scenarios?

The things you must verify on an ETL load: structure, mapping, constraints, completeness, correctness, transformations, data quality, NULLs, duplicates, dates, full-row comparison, filters, cleansing, incremental loads, SCDs, totals and reports.

What is the difference between an ETL test scenario and a test case?

A scenario is what to verify; a test case gives the steps, test data, query and expected result for one scenario.

Which ETL testing scenario is most important?

Completeness and reconciliation (nothing lost, nothing extra, totals match), followed by transformation rules, because these protect the numbers the business uses.

Can I practise ETL testing scenarios for free?

Yes. Download the free end-to-end project on this site and run the scenarios against data with planted defects.

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