ETL testing using Python means writing small scripts that compare source and target data automatically, instead of running SQL by hand after every load. This beginner tutorial uses one real example and builds up to a script with 9 checks, then the same checks as a pytest suite. All the code and data are a free download, so you can run every step yourself.
What Is ETL in Python?
ETL in Python means using Python to Extract data (from CSV files, APIs or databases), Transform it (clean, join, calculate) and Load it into a target such as a data warehouse, usually with the pandas library. If you want to build a pipeline, see build an ETL pipeline with Python. ETL testing with Python is the other side: checking that what the pipeline produced is complete and correct.
Why Use Python for ETL Testing?
- Repeatable: the same checks run after every load in seconds.
- Works with any source: CSV, Excel, JSON, APIs and every major database.
- Free: pandas and pytest cost nothing, unlike commercial tools.
- In demand: “ETL testing with Python” appears in many Indian job descriptions for automation roles.
SQL is still the base skill: Python usually runs SQL or reads its results. See ETL testing SQL queries.
Setup (5 Minutes)
- Install Python 3 from python.org (tick “Add to PATH” on Windows).
- Download the free project download (ZIP) and unzip it.
- Install the two libraries:
pip install pandas pytest
The Example Data
An Indian retail company loads online orders daily. Source: src_orders.csv (20 orders, UTC timestamps). Target: fact_orders.csv and dim_customer.csv in the warehouse. The rules: exclude cancelled orders, net_amount = quantity × unit_price − discount, dates in India time, cities trimmed and in Title Case, emails lower-case. The target has 8 planted defects; your script should find them. The full scenario is in the ETL testing end-to-end project.
import pandas as pd src = pd.read_csv("data/source/src_orders.csv") fact = pd.read_csv("data/target/fact_orders.csv") dim = pd.read_csv("data/target/dim_customer.csv") expected = src[src["status"] != "CANCELLED"] # the ETL must exclude cancelled orders
The 7 Core ETL Checks in Python
1. Row count
print(len(expected), len(fact)) # 18 expected vs 19 loaded -> FAIL
2. Missing and extra records
m = expected[["order_id"]].merge(fact[["order_id"]].drop_duplicates(), on="order_id", how="outer", indicator=True) print(m[m["_merge"] != "both"]) # 1008 left_only (missing), 1010 right_only (extra)
3. Duplicates
print(fact[fact.duplicated("order_id", keep=False)]) # order 1012 twice
4. Transformation rule
calc = (fact["quantity"] * fact["unit_price"] - fact["discount"]).round(2) print(fact[calc != fact["net_amount"].round(2)]) # order 1013 ignores the discount
5. Mandatory key not NULL
print(fact[fact["customer_key"].isna()]) # order 1020
6. Date rule (UTC to India time)
ist = pd.to_datetime(src["order_ts_utc"], utc=True).dt.tz_convert("Asia/Kolkata").dt.date.astype(str) m = fact.merge(src.assign(expected_date=ist)[["order_id", "expected_date"]], on="order_id") print(m[m["order_date"] != m["expected_date"]]) # order 1015 lands on the wrong day
7. Total reconciliation
src_total = (expected["quantity"] * expected["unit_price"] - expected["discount"]).sum() print(round(src_total, 2), round(fact["net_amount"].sum(), 2)) # 34106.0 vs 35254.0
Run the Full Script
The download includes python/etl_checks.py, which runs all 7 checks plus 2 dimension rules and prints PASS or FAIL with the failing rows:
python3 python/etl_checks.py
[FAIL] 1. Row count matches
expected_rows actual_rows
18 19
[FAIL] 2. No missing or extra orders
order_id found_in
1008 left_only
1010 right_only
...
0 passed, 9 failed
Every failure is a real defect to log: the query or code, expected result, actual result and sample rows. See ETL testing defect examples.
The Same Checks in pytest
pytest gives you structure, clear failure messages and reports that CI tools understand. The download includes python/test_etl.py:
import pandas as pd import pytest @pytest.fixture(scope="module") def fact(): return pd.read_csv("data/target/fact_orders.csv") def test_no_duplicates(fact): dups = sorted(fact.loc[fact.duplicated("order_id"), "order_id"].unique()) assert not dups, f"duplicate order_id: {dups}"
pytest -v python/ ... FAILED python/test_etl.py::test_no_duplicates - AssertionError: duplicate order_id: [1012] 9 failed
For a full folder structure with conftest.py and stage-by-stage tests, see the Python ETL testing framework.
Automate It After Every Load
- Point the script at your real database with SQLAlchemy (
pd.read_sql) instead of CSVs. - Run
pytestas the last step of the ETL job in Jenkins, GitHub Actions or Azure DevOps. - Publish the report and fail the pipeline on critical checks.
More in ETL automation testing. Preparing for interviews? Practise the ETL automation testing interview questions (Python).
Next Steps
If you are new to Python, start with Python for testers. For a guided path, ETL Testing: From Beginners to Advanced includes a 17-lecture section, ETL With Python Hands-on Transformation, plus data quality, Power Query and AI tools. Compare options in ETL testing using Python course.
Python for ETL testing: ETL testing using Python · ETL testing using Python course · pytest framework · ETL automation testing · automation interview questions · Python interview questions · Python for testers
Frequently Asked Questions
How do I do ETL testing using Python?
Load source and target data with pandas, then write checks for row counts, missing and extra records, duplicates, transformation rules, NULL keys and total reconciliation. Run them as a script or as pytest tests after every load.
Is Python good for ETL testing?
Yes. Python with pandas and pytest is free, works with any data source and is widely used to automate ETL validations. SQL remains the core skill.
What is ETL in Python?
Using Python, usually with pandas, to extract data from sources, transform it and load it into a target such as a data warehouse.
Is there an ETL testing using Python example on GitHub?
This page offers a free downloadable project with the full Python script, a pytest suite and sample data with 8 planted defects, which you can also push to your own GitHub as a portfolio project.
Do I need to know Python for ETL testing?
Not to start. SQL and data warehouse concepts come first; Python is the next step for automation roles.
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.