More ETL testing roles in India now ask for "ETL testing automation using Python". These questions cover what interviewers ask, from basics to automation frameworks, with short answers and code you should be able to write.
Python Basics for ETL Testers (Q1–Q6)
1. Why use Python in ETL testing?
To automate repetitive SQL checks, compare data across different databases and files, handle large data with pandas or PySpark, and run tests in CI.
2. List vs dictionary vs set?
Lists keep ordered items; dictionaries map keys to values; sets hold unique items and are handy for finding missing keys with set difference.
3. How do you find keys in source but not target with plain Python?
Convert both key lists to sets and use set difference: set(src_keys) - set(tgt_keys).
4. What are *args and **kwargs?
They let a function accept any number of positional and keyword arguments.
5. How do you read JSON or CSV?
json.load() for JSON; csv module or pandas.read_csv() for CSV.
6. How do you handle bad records without stopping the run?
Wrap processing in try/except, log the record and error, and continue.
pandas for ETL Testing (Q7–Q14)
7. How do you compare source and target DataFrames for missing and extra records?
Outer merge on the key with indicator=True; left_only rows are missing in target, right_only rows are extra.
cmp = src.merge(tgt, on="order_id", how="outer", indicator=True) missing = cmp[cmp["_merge"] == "left_only"] extra = cmp[cmp["_merge"] == "right_only"]
8. How do you find duplicates?
df[df.duplicated(subset=["order_id"], keep=False)] shows all duplicated rows.
9. How do you count nulls per column?
df.isna().sum().
10. How do you validate a transformation rule?
Recompute the expected column from source columns and compare with the target column, for example (expected != actual).sum() == 0.
11. How do you compare two DataFrames exactly?
pandas.testing.assert_frame_equal(), after sorting both and resetting the index; libraries like datacompy give a readable mismatch report.
12. merge vs concat vs join?
merge joins on columns like SQL; concat stacks frames; join is a merge on the index.
13. How do you aggregate like GROUP BY?
df.groupby("city")["amount"].sum().
14. How do you read a file too big for memory?
Use read_csv(chunksize=...) and process in chunks, select only needed columns, or switch to PySpark or the database for heavy comparisons.
Working with Databases (Q15–Q18)
15. How do you run SQL from Python?
Create a connection or SQLAlchemy engine, then use pandas.read_sql(query, engine) or a cursor.
from sqlalchemy import create_engine import pandas as pd engine = create_engine("postgresql://user:password@host:5432/dw") tgt = pd.read_sql("SELECT order_id, amount FROM dw.fact_orders WHERE load_date = '2026-10-01'", engine)
16. Where do you keep credentials?
In environment variables or a secrets manager, never hard-coded in scripts or committed to Git.
17. How do you compare tables in two different databases?
Read both into DataFrames (or push aggregates to each database), then compare in Python.
18. Should you compare in pandas or in SQL?
Push heavy work (counts, sums, filters) to the database; pull keys or samples to Python for cross-system comparison.
pytest and Automation Frameworks (Q19–Q24)
19. How do you structure an ETL test framework?
Config (connections, table list), reusable check functions (count, duplicates, nulls, reconciliation), pytest tests, reports and CI scheduling. Example: Python ETL testing framework.
20. What is a pytest fixture?
A reusable setup function, for example a database connection shared across tests, injected by naming it as a test argument.
21. What is parametrisation?
@pytest.mark.parametrize runs one test with many inputs, for example the same count check for every table.
import pytest TABLES = ["customers", "orders", "products"] @pytest.mark.parametrize("table", TABLES) def test_counts_match(table, src_db, tgt_db): # src_db, tgt_db are fixtures assert src_db.count(table) == tgt_db.count(table)
22. How do you report results?
pytest-html or JUnit XML for CI tools, plus a results table in the database for trend dashboards.
23. How do you run tests after each load?
Trigger pytest from the scheduler or CI pipeline (Jenkins, GitHub Actions, Azure DevOps) after the ETL job finishes.
24. Which Python data quality libraries do you know?
Great Expectations, datacompy and PySpark testing utilities. See Great Expectations tutorial.
Coding Tasks Asked in Interviews (Q25–Q27)
25. Write a function that returns rows where a mandatory column is empty.
df[df[col].isna() | (df[col].astype(str).str.strip() == "")]
26. Count records per day and flag days with zero loads.
Group by date, reindex against a full date range with fill_value=0, and filter for 0.
27. Check that every order has a valid customer.
~orders["customer_id"].isin(customers["customer_id"]) gives orphan orders.
Practise many more ETL, SQL and scenario questions with the ETL Testing Interview Questions & Answers – Practice Tests. New to Python? Start with Python for testers.
Prepare for your ETL testing interview: top 50 questions · in-depth answers · for experienced (3–5 years) · for 10 years experience · SQL query questions · scenario-based questions · data warehouse questions · questions for freshers · free mock test · database testing questions · Informatica questions · SQL questions for freshers · DBMS questions · data validation questions · service-company questions
Data testing skills: SQL for testers · data migration testing · Power BI testing · big data testing · data quality checks · ETL tools list · ADF & Databricks testing · database testing interview questions · Python for testers · data reconciliation · SQL joins · test data management · ETL defects examples · Snowflake ETL testing · AWS Glue testing · data lake testing
Frequently Asked Questions
What Python questions are asked in ETL testing interviews?
Python basics, pandas comparisons (merge with indicator, duplicates, nulls, groupby), running SQL from Python, handling large files, pytest fixtures and parametrisation, and how you would structure an automation framework.
Is Python mandatory for ETL testing?
Not for every role, but ETL automation roles increasingly require it. SQL remains the most important skill.
Which Python libraries are used in ETL testing?
pandas, SQLAlchemy or database drivers, pytest, Great Expectations, datacompy and PySpark for big data.
How do you automate ETL testing using Python?
Write reusable check functions, wrap them in pytest tests parametrised by table, connect to source and target databases, and run the suite after every load from a scheduler or CI tool.
How should I prepare for ETL testing Python interviews?
Write the pandas comparisons and pytest examples yourself, build a small framework on sample data and practise explaining it.
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.