Once you have a few years on your CV, ETL testing interviews stop asking "what is a fact table?" and start asking "tell me how you tested it." Interviewers want proof that you have owned test cycles, found real defects and made decisions under pressure.
This guide gives you ETL testing interview questions for experienced testers with answers, split into the two levels most job ads ask for: 3 years experience (hands-on tester) and 5 years experience (senior tester who drives strategy and automation). If you are earlier in your career, start with the top 50 ETL testing interview questions first.
What Changes in Interviews for Experienced ETL Testers
| Level | What interviewers test | Typical format |
|---|---|---|
| Fresher | Definitions, basic SQL, ETL concepts | Theory questions, simple queries |
| 3 years | Hands-on execution: writing test cases from mapping documents, SCD and incremental testing, defect analysis | "How did you test…", live SQL, short scenarios |
| 5 years | Ownership: test strategy, automation, reconciliation frameworks, performance, mentoring | Design questions, long scenarios, trade-offs |
At both levels, answers that start from your own project ("On our claims data warehouse we…") beat textbook definitions every time.
ETL Testing Interview Questions and Answers for 3 Years Experience
1. Walk me through how you test a new ETL mapping from scratch.
Start from the mapping document: list every target column with its source, transformation rule and constraints. Then write tests in layers: record counts (source vs target, allowing for filters), data completeness (no missing keys with a source-minus-target query), transformation checks column by column, data quality checks (nulls, duplicates, formats, ranges), and referential integrity against dimensions. Finish with negative tests — bad dates, nulls in mandatory fields — and confirm they land in the reject table, not the target.
2. How do you test an incremental (delta) load?
Run the initial load and capture counts. Then insert, update and delete a known set of source rows and run the delta. Verify that only the changed rows were processed (check audit columns or the last-extract timestamp), inserts appear once, updates overwrite or version correctly, deletes are handled as the design says (hard delete or soft-delete flag), and unchanged rows keep their original load timestamps.
3. How do you test SCD Type 2?
Change a tracked attribute for an existing business key and run the load. Verify the old row is expired (end date set, current flag = N), a new row is inserted with a new surrogate key, start date = load date and current flag = Y, and exactly one current row exists per business key. Also check that changes to non-tracked attributes do not create a new version, and that fact rows loaded after the change point to the new surrogate key.
4. Source has 1,000,000 rows and the target has 998,750. Is that a defect?
Not necessarily. First check the mapping for filters, deduplication or aggregation that legitimately reduce rows, and check the reject/error table. If source rows minus filtered rows minus rejected rows equals the target count, the load reconciles. If not, run a source-minus-target query on the business key to find the exact missing rows and look for a pattern (one date, one region, nulls in a join column).
5. What do you check in a reject or error table?
That every rejected row has a clear error code and reason, that the rejected rows really violate a rule (no false rejects), that valid rows are not rejected, and that source count = target count + rejected count + intentionally filtered count. I also check that reprocessing a fixed reject does not create a duplicate.
6. How do you test a lookup transformation?
Cover three cases: a match (the correct value is returned), no match (the default or "unknown" member is used, as the spec says), and multiple matches (the rule for picking one is applied, or the row is flagged). I also check case and whitespace differences in the lookup key, because they are a common cause of silent misses.
7. How do you validate data type and format conversions?
Compare source and target metadata (type, length, precision), then test boundary values: maximum string lengths for truncation, decimals for rounding, and dates in different formats and time zones. A query that compares the converted source value to the target value for every row catches most issues.
8. A defect you raised was rejected by the developer as "working as designed." What do you do?
Go back to the mapping document and requirement and show the exact rule, the source row, the expected value and the actual value. If the document is ambiguous, involve the business analyst or data owner to confirm the intended behaviour, then update the document so the next tester doesn't hit the same question.
9. How do you test for duplicates in the target?
SELECT customer_id, COUNT(*) AS cnt FROM dw.dim_customer WHERE current_flag = 'Y' GROUP BY customer_id HAVING COUNT(*) > 1;
Group by the business key (plus the current flag for SCD2 dimensions) and expect zero rows. For facts, group by the grain columns defined in the design.
10. How do you test a full reload versus an incremental run of the same job?
Run both against the same source snapshot and compare the targets with a symmetric difference (source-minus-target in both directions). Any difference means the incremental logic is drifting from the full-load logic.
11. What test data do you prepare before testing?
A small, controlled set that covers every rule in the mapping: valid rows, nulls in each nullable and mandatory column, boundary values, duplicates, orphan foreign keys, special characters and late-arriving records. Production-like volume comes later for performance testing, usually with masked data.
12. Which metrics do you report at the end of a test cycle?
Test cases planned/executed/passed, defects by severity and status, reconciliation results per table (counts and sums), open risks, and a clear go/no-go recommendation.
ETL Testing Interview Questions and Answers for 5 Years Experience
13. How would you write a test strategy for a new data warehouse?
Cover scope (sources, layers, reports), test types per layer (staging: completeness; integration: transformations; marts: aggregates and business rules; reports: numbers match the warehouse), environments and test data, entry and exit criteria, automation approach, defect process, and risks. Prioritise by business impact — revenue and regulatory tables get the deepest coverage.
14. How do you design an automated reconciliation framework?
Keep the test definitions in metadata: for each table, a source query, a target query, the key columns and the checks (count, sum of key measures, column-level compare). A runner (Python or a tool) executes them after every load, writes results to a results table and alerts on failures. New tables are added by adding metadata, not code.
15. How do you test ETL performance?
Agree the SLA first (for example, nightly load under 2 hours). Test with production-like volume, measure each step, and compare against a baseline. Look for full scans, missing indexes, skewed joins and row-by-row processing. Re-test after fixes and at projected growth volume (for example 2x data).
16. How do you fit ETL tests into CI/CD?
Run fast checks (schema, unit tests on transformations with small fixtures) on every commit, full reconciliation in the test environment after deployment, and a smoke set of counts and key totals after each production load. Failures block promotion to the next environment.
17. How do you test a data migration from an old system to a new one?
Reconcile at three levels: counts per entity, sums of financial columns per period, and a full column-by-column compare on keys (or a statistically valid sample for very large tables). Run parallel periods where both systems process the same data and compare outputs before cut-over.
18. How do you decide what to automate?
Automate checks that repeat every load and are stable: counts, sums, duplicates, nulls, referential integrity, schema drift. Keep exploratory testing manual for new transformations and complex business rules until they settle, then automate them as regression.
19. How do you test data quality rules owned by the business?
Turn each rule into a measurable check with a threshold (for example "email valid for at least 98% of active customers"), run it on every load, trend the results and route breaches to the data owner. The tester's job is to make the rule testable and the results visible.
20. How do you validate a report against the warehouse?
Recreate the report's key numbers with SQL on the warehouse using the same filters and date logic, then compare. Most mismatches come from different filters, time zones, or how nulls and cancelled records are treated.
21. A production load silently loaded half the expected rows. How do you lead the response?
Stop downstream jobs if possible, confirm the scope with counts by date and source, find the cause (late source file, failed partition, filter change), agree a fix and a reload plan, verify the reload with reconciliation, and add an automated check (row-count threshold against history) so it is caught automatically next time.
22. How do you test in a cloud data warehouse?
The checks are the same; what changes is scale and cost. Push comparisons down to the warehouse instead of pulling data out, use partition filters, sample where full compares are too expensive, and test permissions and data masking because access is often managed separately in the cloud.
23. How do you mentor junior testers?
Pair on writing test cases from a real mapping document, review their SQL, share a checklist of standard checks, and give them ownership of one small table end to end before larger subject areas.
24. Where does AI help in ETL testing today?
Drafting test cases from mapping documents, writing first versions of validation SQL, explaining unfamiliar code and summarising defect patterns. A tester still reviews every output, because the AI does not know your business rules. See ETL testing with AI for examples.
How to Structure Answers as an Experienced Tester
- Situation: one sentence on the project — domain, volume, tools.
- Action: the exact checks and queries you used.
- Result: the defect found or risk avoided, ideally with a number.
- Learning: what you automated or changed afterwards.
Have two or three real defect stories ready. Interviewers for 3–5 year roles almost always ask "what's the most interesting defect you found?"
A 2-Week Practice Plan
- Days 1–4: SQL drills — joins, set operators, window functions. Use the SQL queries for ETL testing interview questions.
- Days 5–8: Scenarios — practise talking through the scenario-based ETL testing interview questions out loud.
- Days 9–12: Timed practice tests to find weak spots.
- Days 13–14: Prepare your project story, defect stories and questions for the interviewer.
For timed practice, the ETL Testing Interview Questions & Answers practice tests cover 500+ questions from fresher to experienced level, including SQL and scenario-based questions, each with a detailed answer. If you need to rebuild the fundamentals first, ETL Testing: From Beginners to Advanced is the full video course.
Prepare for your ETL testing interview: top 50 questions · in-depth answers · for 10 years experience · SQL query questions · scenario-based questions
Frequently Asked Questions
What ETL testing interview questions are asked for 3 years experience?
Expect hands-on questions: testing a mapping from scratch, incremental loads, SCD Type 2, reject tables, lookups, duplicate checks with SQL, and how you handled a real defect.
What is asked in an ETL testing interview for 5 years experience?
Senior questions focus on ownership: writing a test strategy, designing an automated reconciliation framework, performance testing, CI/CD, migration testing and leading production incident response.
How should experienced testers answer ETL interview questions?
Answer from a real project: the situation, the exact checks and SQL you used, the result, and what you automated afterwards. Concrete examples beat definitions.
Do experienced ETL testers need to write SQL in interviews?
Yes. Almost every ETL testing interview includes live SQL, such as source-minus-target comparisons, duplicate checks and window functions, at every experience level.
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.