End-to-End ETL Testing Process: Explained With a Real Example

End-to-end ETL testing checks data through the whole journey: from the source system, through staging and transformation, into the data warehouse, and finally into the report a manager reads. Testing only one layer is how wrong numbers reach a dashboard. This guide walks the ETL testing end-to-end process with one real example and the SQL for every layer.

What Is End-to-End ETL Testing?

It is validating that every record and every number is correct at each hand-off point between systems, and that the final report matches the original source. Unit tests on a single ETL job are not enough: a correct job can still feed a wrong report if a lookup, filter or time-zone rule upstream is wrong.

The 5 Layers You Test

1. Source files / OLTP        ← test 1
        ↓
2. Staging                     ← test 2
        ↓
3. Transform (business rules)  ← test 3
        ↓
4. Data warehouse (dims+facts) ← test 4
        ↓
5. Reports / dashboards        ← test 5

The Example: Daily Online Orders

An Indian retail company loads online orders every day. Source files contain customers, products and orders with UTC timestamps. The ETL excludes cancelled orders, calculates net_amount = quantity × unit_price − discount, converts timestamps to India time, cleans city and email values, and loads dim_customer, dim_product and fact_orders. A dashboard shows revenue by city.

You can download this exact dataset, with 8 hidden defects, from the free project download (ZIP) and follow along. The full walkthrough is in the end-to-end project guide.

Layer 1: Source Checks

-- profile statuses
SELECT status, COUNT(*) FROM src_orders GROUP BY status;

-- orders whose customer does not exist in the source
SELECT o.order_id, o.customer_id FROM src_orders o
LEFT JOIN src_customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

In the example this finds an order for customer 107, who does not exist. That is a source data-quality issue to raise before the load, not after.

Layer 2: Staging Checks

Staging should be a faithful copy of the source. Compare counts and run a full EXCEPT/MINUS in both directions on all columns. Any difference here is an extraction defect (wrong file, truncated rows, encoding issues).

Layer 3: Transformation Checks

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

-- business rule: order_date is the India date (UTC + 5:30)
SELECT f.order_id, o.order_ts_utc, 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');

Date functions differ by database: the second query uses SQLite syntax. In Oracle use CAST(FROM_TZ(...) AT TIME ZONE 'Asia/Kolkata' AS DATE), in SQL Server AT TIME ZONE 'India Standard Time'.

Layer 4: Warehouse Checks

-- missing in target (non-cancelled source orders)
SELECT order_id FROM src_orders WHERE status <> 'CANCELLED'
EXCEPT SELECT order_id FROM fact_orders;

-- duplicates in target
SELECT order_id, COUNT(*) FROM fact_orders GROUP BY order_id HAVING COUNT(*) > 1;

-- cancelled orders that were loaded
SELECT f.order_id FROM fact_orders f
JOIN src_orders o ON o.order_id = f.order_id WHERE o.status = 'CANCELLED';

Layer 5: Report Checks

-- recalculate revenue by city from source and compare with the report
SELECT TRIM(c.city) AS city, ROUND(SUM(o.quantity * o.unit_price - o.discount), 2) AS expected_revenue
FROM src_orders o JOIN src_customers c ON c.customer_id = o.customer_id
WHERE o.status <> 'CANCELLED'
GROUP BY UPPER(TRIM(c.city));

Compare each city with the dashboard. In the example, four cities do not match, and every difference traces back to a defect found in an earlier layer. That is the point of end-to-end testing: the report check tells you that something is wrong; the layer checks tell you where.

The End-to-End ETL Testing Process, Step by Step

1. Understand the requirement and the report the business needs.
2. Review the mapping document rule by rule.
3. Write test cases per layer (source, staging, transformation, warehouse, report).
4. Prepare test data, including edge cases such as cancelled orders, time-zone boundaries and unknown customers.
5. Run the load and execute checks layer by layer.
6. Log defects with the query, expected and actual result.
7. Retest and run regression, including a rerun to prove the load does not duplicate data.
8. Sign off with evidence. More detail: ETL testing process (8 steps).

End-to-End ETL Testing Checklist

• ☐ Source profiled; data-quality issues raised before load
• ☐ Source vs staging: counts and full compare
• ☐ Every transformation rule has a test
• ☐ Target: missing, extra and duplicate rows checked
• ☐ Keys: no NULL or orphan foreign keys
• ☐ Dimensions: cleansing rules and SCD behaviour
• ☐ Totals reconciled source vs target
• ☐ Report numbers recalculated from source
• ☐ Rerun does not duplicate data

Want this as a PDF? Use your browser’s Print → Save as PDF, or download the free ETL Testing Interview Kit, which includes 15 of these SQL checks.

Is End-to-End ETL Testing Manual or Automation?

Both. Testers usually design and run the checks manually with SQL first, then automate the repeated ones (counts, duplicates, reconciliation) with Python or a tool so they run after every load. See is ETL testing manual or automation and ETL automation testing.

ETL Testing: From Beginners to Advanced teaches ETL and data warehouse testing, data quality and transformation checks, Python and AI tools, so you can run this whole process on your own projects.

End-to-end ETL testing series: end-to-end process · end-to-end project · end-to-end interview questions · ETL testing process · ETL testing with example

Frequently Asked Questions

What is end-to-end ETL testing?

Testing data through every layer of the pipeline, from source and staging through transformation and the data warehouse to the final report, and proving the report matches the source.

What is the ETL testing end-to-end process?

Understand requirements, review the mapping, write test cases per layer, prepare test data, run the load, validate each layer with SQL, log defects, retest and run regression, then sign off.

Can you give an end-to-end ETL testing example?

A daily orders load: check source orders, confirm staging matches, test the net amount and date rules, check missing and duplicate rows in the fact table, then recalculate revenue by city and compare with the dashboard.

Is there an end-to-end ETL testing PDF?

Save this page as a PDF from your browser, or download the free ETL Testing Interview Kit PDF linked on this page.

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