Data transformation testing checks that every business rule in an ETL job turns source data into the right target values. It is where most ETL defects hide, because counts can match perfectly while values are wrong.
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 Data Transformation Testing in ETL?
For each transformation in the mapping document, you recalculate the expected value independently (in SQL, Python or Excel) and compare it with what the ETL produced. Any difference is a defect in the rule, the code or the data.
Types of Transformations in ETL Testing
| Transformation | Example |
|---|---|
| Filter | Exclude cancelled orders |
| Derivation | net_amount from quantity, price, discount |
| Cleansing | Trim spaces, fix case |
| Standardisation | Map “M”/“Male” to one code |
| Lookup | Business key to surrogate key |
| Aggregation | Daily sales per store |
| Join / merge | Orders with customers |
| Split / concatenate | Full name into first and last |
| Type conversion | Text to date or number |
| Date / time zone | UTC to India time |
| Deduplication | Keep latest record per key |
| Pivot / unpivot | Months as columns to rows |
How to Test a Transformation: 5 Steps
- Read the rule in the mapping document and confirm unclear points.
- Prepare test data with normal values and edge cases (zero, NULL, boundary dates).
- Recalculate the expected result independently.
- Compare with the target and list mismatching rows.
- Log defects with the rule, query, expected and actual values.
SQL Examples for 6 Transformation Types
Filter: Cancelled orders excluded
SELECT f.order_id FROM fact_orders f JOIN src_orders o ON o.order_id = f.order_id WHERE o.status = 'CANCELLED';
Derivation: net_amount = quantity × unit_price − discount
SELECT order_id FROM fact_orders WHERE ROUND(net_amount,2) <> ROUND(quantity*unit_price-discount,2);
Cleansing: City trimmed and Title Case; email lower-case
SELECT customer_id FROM dim_customer WHERE city <> TRIM(city) OR email <> LOWER(email);
Date / time zone: UTC timestamp converted to India date
SELECT f.order_id 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');
Lookup: customer_id mapped to a valid customer_key
SELECT order_id FROM fact_orders WHERE customer_key IS NULL OR TRIM(customer_key) = '';
Deduplication: One row per order
SELECT order_id FROM fact_orders GROUP BY order_id HAVING COUNT(*) > 1;
Every query above returns the failing rows (empty = pass) and finds a real planted defect in the free ETL testing end-to-end project.
Data Conversion Testing
Data conversion testing checks that data moved from an old system to a new one (often during a migration) keeps its meaning: formats, codes, precision and history. Strategy: map old to new codes, reconcile counts and totals per entity, compare samples field by field, and verify no truncation or rounding. See data migration testing.
Learn Data Transformation Hands-On
You test transformations best when you have built them. ETL Testing: From Beginners to Advanced has a 17-lecture section, ETL With Python Hands-on Transformation, plus ETL With Power Query Hands-on Transformation, so you learn both sides. Free alternative: ETL testing using Python.
ETL testing fundamentals: ETL testing scenarios · types of ETL testing · ETL testing techniques · ETL testing challenges · data integrity testing · ETL test cases · ETL testing process
Frequently Asked Questions
What is data transformation testing in ETL?
Verifying that each business rule in the ETL turns source data into the correct target values, by recalculating the expected result independently and comparing.
What are the types of transformation in ETL testing?
Filter, derivation, cleansing, standardisation, lookup, aggregation, join, split/concatenate, type conversion, date/time-zone conversion, deduplication and pivot/unpivot.
What is data conversion testing?
Testing that data converted from an old system to a new one keeps its meaning, format, precision and history, usually during a migration.
Can counts match while transformations are wrong?
Yes. Row counts can be perfect while calculated values are wrong, which is why every rule needs its own test.
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.