Recruiters ask freshers and manual testers the same question: “Have you worked on an ETL project?” This free ETL testing end-to-end project gives you a realistic one to practise on: source data, a loaded data warehouse with 8 hidden defects, a mapping document, 20 test cases and 19 SQL checks. Work through it, find the defects, and you will have a real story to tell.
Get the Project (Free Download)
Download the free project download (ZIP), unzip it and run the checks:
unzip etl-testing-end-to-end-project.zip cd etl-testing-end-to-end-project python3 run_checks.py
Prefer to read first? Open the mapping document, the 20 test cases or the SQL checks in your browser.
No database install is needed: run_checks.py loads the CSV files into SQLite, which comes with Python. You can also import the CSVs into MySQL, PostgreSQL, SQL Server or Oracle.
The Scenario
An Indian retail company loads online orders daily: 6 customers, 5 products and 20 orders with UTC timestamps. The ETL must exclude cancelled orders, compute net_amount, convert timestamps to India time, trim and title-case cities, lower-case emails and look up surrogate keys. The process is explained layer by layer in end-to-end ETL testing.
What Is Inside
| File | Purpose |
|---|---|
data/source/*.csv | src_customers, src_products, src_orders |
data/target/*.csv | dim_customer, dim_product, fact_orders (loaded, with defects) |
docs/mapping.csv | Source-to-target mapping and rules |
docs/test-cases.csv | 20 test cases across 5 layers |
sql/validation_queries.sql | 19 SQL checks; empty result = pass |
run_checks.py | Runs every check and prints failing rows |
Step-by-Step Walkthrough
- Read the mapping and write your own test cases before opening ours. This is the skill interviewers test most.
- Profile the source: 20 orders, 2 cancelled, one order for a customer who does not exist.
- Reconcile counts: 18 orders expected in the fact table. How many are there?
- Find missing, extra and duplicate rows with EXCEPT/MINUS and GROUP BY.
- Test every transformation rule: net_amount, India date, city and email cleansing.
- Check keys for NULL or orphan values.
- Reconcile totals and the report: recalculate revenue by city from source.
- Log defects and map each one back to the layer where it was introduced.
-- example: one of the 19 checks SELECT order_id FROM src_orders WHERE status <> 'CANCELLED' EXCEPT SELECT order_id FROM fact_orders;
The 8 Defects (Try Before Reading!)
Show the answer key
| # | Defect | Layer |
|---|---|---|
| D1 | Order 1008 missing in fact_orders | Warehouse load |
| D2 | Order 1013 net_amount ignores the discount | Transformation |
| D3 | Cancelled order 1010 was loaded | Transformation (filter) |
| D4 | City “ Mumbai ” not trimmed | Dimension cleansing |
| D5 | Email not lower-cased for customer 101 | Dimension cleansing |
| D6 | Order 1012 loaded twice | Warehouse load |
| D7 | Order 1015 uses the UTC date instead of the India date | Transformation (date rule) |
| D8 | Order 1020 has an unknown customer, so customer_key is blank | Source data quality / lookup |
Write the Defect Reports
For each defect write: title, the query you ran, expected result, actual result, sample rows, severity and the layer. Example: “fact_orders: cancelled order 1010 loaded. Query Q08 returns 1 row; expected 0. Severity: High (inflates Hyderabad revenue by ₹2,398).” See ETL testing defect examples.
How to Explain This Project in Interviews
“I practised on a retail orders ETL project: three source files loading two dimensions and a fact table with a revenue-by-city report. I wrote 20 test cases from the mapping document and 19 SQL checks across source, transformation, warehouse and report layers. I found 8 defects, including a missing order, a duplicate, a cancelled order that was loaded and a time-zone bug that moved an order to the wrong day. The revenue report was off in four cities, and I traced each difference to its root cause.”
Say clearly that it is a practice project; interviewers respect honesty and will test your understanding with follow-up questions. More: explaining a real-time project and end-to-end process interview questions.
Next Steps
- Automate the checks with Python and pytest: Python ETL testing framework.
- Try more datasets: ETL testing projects for practice.
- Learn the full skill set with ETL Testing: From Beginners to Advanced.
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
Where can I find an ETL testing end-to-end project?
This page has a free downloadable project with source and target data, a mapping document, 20 test cases, 19 SQL checks and 8 hidden defects.
Do I need a database to do this project?
No. The run_checks.py script loads the CSVs into SQLite, which is built into Python. You can also import them into any database.
Can I put a practice project on my resume?
Yes, if you label it as a practice or personal project and can explain every test and defect in detail.
Is this a real company project?
No. All data is fictional and designed for practice, modelled on common retail ETL scenarios.
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.