Scenario questions are where ETL testing interviews are won or lost. "A dashboard shows zero revenue for yesterday — what do you do?" has no single right answer. The interviewer wants to hear a calm, structured investigation.
Here are 20 scenario based interview questions for ETL testing, each with a model answer you can adapt. They suit 2+ years of experience, and freshers who want to stand out. For pure theory, see the top 50 ETL testing interview questions.
A Simple Framework for Any Scenario Question
- Clarify: which table, which run, since when, how big is the gap?
- Scope: measure the problem with counts and totals by date, source and partition.
- Isolate: find which layer breaks first — source, staging, transformation or target.
- Root cause: confirm with specific rows, not assumptions.
- Fix and prevent: verify the fix with reconciliation and add an automated check.
Say the steps out loud. Interviewers score the process as much as the answer.
Data Mismatch Scenarios
1. The target has fewer rows than the source. How do you investigate?
Check the mapping for filters and deduplication, then the reject table. Reconcile source = target + rejected + filtered. If it still doesn't balance, run a source-minus-target query on the business key and look for a pattern in the missing rows: a date, a region, NULLs in a join key, or an inner join to a dimension that is missing members.
2. The target has more rows than the source.
Usually duplicates: a job that ran twice, a delta that re-processed old rows, or a join that fans out because the lookup key isn't unique. Group the target by business key with HAVING COUNT(*) > 1, then check the duplicated rows' load IDs and timestamps.
3. Counts match but the revenue total doesn't.
Compare totals by period and by category to narrow it down, then compare the column value per key. Typical causes: rounding or currency conversion, a sign change on returns, the wrong exchange-rate date, or cancelled orders included on one side.
4. Some customer names appear as question marks or broken characters.
A character-encoding problem: the source is UTF-8 but a file, connection or column is using a single-byte code page. Find affected rows, compare the source bytes, and check the encoding setting at each hop (file, staging table, target column type).
5. Dates are off by one day for some records.
A time-zone conversion issue. Records near midnight shift date when UTC timestamps are converted (or not converted) to local time. Test with timestamps just before and after midnight and around daylight-saving changes, and confirm which time zone the business expects.
6. A numeric column has values like 12345.6789 in the source but 12345.67 in the target.
Truncation instead of rounding, or a smaller target precision. Compare the target data type with the mapping, and check whether the rule says round or truncate — then test boundary values like .005.
7. The dimension has two current rows for the same customer.
The SCD Type 2 logic failed to expire the old row — often because the job ran twice, or a change arrived twice in one batch. Find affected keys, check the load history, and confirm how the job should handle multiple changes in one run.
8. Facts point to "Unknown" customer for new orders.
A late-arriving dimension: the order arrived before the customer record. Confirm the design — inferred members, or reprocessing facts when the dimension arrives — and test that facts are re-linked once the customer loads.
Load and Pipeline Scenarios
9. Yesterday's job loaded 1 million rows; today it loaded 10. What do you do?
Check whether the source actually had fewer rows (holiday, a partial file, an upstream failure) or the job read the wrong data (wrong file name or date parameter, a changed filter, an incremental watermark that jumped ahead). Compare against the source directly for today's date and check the job log and audit table.
10. The job failed halfway. How do you test the restart?
Restart it and verify no duplicates from the partially loaded batch, no missing rows, and audit counts that match. The job should be idempotent: running it twice for the same date gives the same result.
11. A source file arrives with an extra column.
Check how the job handles schema drift: fail with a clear error, ignore the column, or map it. Verify existing columns didn't shift position, a common problem with positional file parsing.
12. A source file arrives empty.
The job should not wipe the target or silently succeed. Check the expected behaviour: fail, alert, or load zero rows and alert. Add a minimum-row-count check against history.
13. The nightly load now misses its SLA.
Compare step timings with the baseline to find the slow step, check data volume growth, then look at the usual causes: full table scans, missing statistics or indexes, skewed joins, and row-by-row processing. Re-test after the fix at current and projected volumes.
14. The same file was accidentally loaded twice.
Check whether the job detects already-processed files (file name or checksum log). If not, duplicates appear in the target. Test the control: re-submit the same file and expect a rejection or a no-op.
15. Deleted records in the source still appear in the warehouse.
The incremental logic only captures inserts and updates. Check the design for deletes (CDC, soft-delete flags, full key compare) and test a delete end to end.
Business and Process Scenarios
16. A business user says the report doesn't match their spreadsheet.
Get the exact report, filters and date range. Reproduce the number in SQL on the warehouse, then compare definitions with the user's spreadsheet — for example, whether returns or cancelled orders are included. Many "defects" are definition differences, which should be documented.
17. There's no mapping document. How do you test?
Build one: interview the developer and business analyst, reverse-engineer rules from the code and sample data, and get the rules confirmed in writing. Then test against the confirmed rules. Mention that you'd raise the missing documentation as a project risk.
18. You have one day to test a release that normally takes a week.
Prioritise by risk: full reconciliation on high-impact tables, automated counts and key checks everywhere else, and a list of what wasn't tested and who accepted the risk. Recommend extra monitoring after release.
19. The developer says a defect is a data issue in the source, not the ETL.
Prove it either way with the source rows. If the source is wrong, the ETL may still need to reject or flag it according to data quality rules. Raise it with the source owner and agree how the ETL should handle it.
20. How would you test a pipeline you've never seen before, starting tomorrow?
Read the design and mapping documents, profile the source and target data (counts, nulls, distinct values, ranges), run standard checks (counts, duplicates, nulls, referential integrity, totals), then go deep on the riskiest transformations. Write down questions as you go and review them with the team.
How to Practise Scenario Questions
- Answer out loud using the five-step framework above, in under two minutes each.
- Back answers with SQL. Know the query you'd run for each step — see SQL queries for ETL testing interview questions.
- Turn your own defects into scenarios. Interviewers love real stories.
- Test yourself on many scenarios. The ETL Testing Interview Questions & Answers practice tests include scenario-based and SQL questions from fresher to experienced level, each with a detailed answer.
Prepare for your ETL testing interview: top 50 questions · in-depth answers · for experienced (3–5 years) · for 10 years experience · SQL query questions
Frequently Asked Questions
What are scenario based questions in ETL testing?
They describe a real problem, such as a row-count mismatch, duplicates, late-arriving data or a failed load, and ask how you would investigate and fix it. Interviewers score your structured approach as much as the final answer.
How do you answer scenario-based ETL testing questions?
Clarify the problem, measure its scope with counts and totals, isolate the layer where it breaks, confirm the root cause with specific rows, then verify the fix and add an automated check.
Are scenario questions asked for freshers in ETL testing?
Simple ones are, such as finding missing or duplicate rows. Complex scenarios like SCD issues, restarts and SLA problems are more common for candidates with 2+ years of experience.
What is the most common ETL testing scenario question?
A source and target count mismatch, usually followed by questions on filters, rejects, duplicates and how you would find the exact missing rows with SQL.
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.