"Tell me about a defect you found" is one of the most common ETL testing interview questions. These 15 ETL testing defects examples are typical of real projects. Each one has an example, the SQL that catches it and why it matters.
Completeness Defects
| # | Defect | Example | How to catch |
|---|---|---|---|
| 1 | Missing records | 12 of 50,000 orders absent from fact table | EXCEPT/MINUS on keys |
| 2 | Extra records | Test accounts loaded that should be filtered | EXCEPT in the other direction; check filter rule |
| 3 | Duplicate records | Job re-run inserted the same day twice | GROUP BY key HAVING COUNT(*) > 1 |
| 4 | Rejected rows not reported | Invalid dates silently dropped | Source = target + rejects reconciliation |
-- Defect 3: duplicates after a re-run SELECT order_id, COUNT(*) FROM dw.fact_orders WHERE order_date = '2026-10-01' GROUP BY order_id HAVING COUNT(*) > 1;
Transformation Defects
| # | Defect | Example | How to catch |
|---|---|---|---|
| 5 | Wrong rule | Discount applied at ₹4,999 instead of ≥ ₹5,000 | Recompute rule in SQL; compare |
| 6 | Untrimmed / wrong case | "pune " and "Pune" become two cities | GROUP BY UPPER(TRIM(col)) vs GROUP BY col |
| 7 | Wrong lookup | Product mapped to wrong category after code change | Join source code to target category; compare |
| 8 | Wrong date format | 03/04/2026 loaded as 4 March instead of 3 April | Compare parsed dates with source text |
| 9 | Wrong currency/units | Paise loaded as rupees | Aggregate totals reconciliation |
-- Defect 5: discount rule check (expect 0 rows) SELECT order_id, amount, discount_flag FROM dw.fact_orders WHERE discount_flag <> CASE WHEN amount >= 5000 THEN 'Y' ELSE 'N' END;
Data Quality and Structure Defects
| # | Defect | Example | How to catch |
|---|---|---|---|
| 10 | Truncation | Addresses cut at 50 characters | Compare MAX(LENGTH(col)) source vs target; metadata testing |
| 11 | NULL defaults wrong | Missing phone loaded as "0000000000" | Profile distinct values; data profiling |
| 12 | Orphan foreign keys | Sales rows with no matching customer | LEFT JOIN dimension WHERE key IS NULL |
Load and History Defects
| # | Defect | Example | How to catch |
|---|---|---|---|
| 13 | Incremental load misses updates | Changed addresses not picked up because only inserts are captured | Update source rows, run load, compare |
| 14 | SCD Type 2 broken | Two current rows for one customer, or date gaps | SCD testing checks |
| 15 | Late-arriving data lost | Orders for a new customer rejected because dimension row arrives later | Check unknown-member handling and reprocessing |
-- Defect 14: more than one current row per customer SELECT customer_id, COUNT(*) FROM dw.dim_customer WHERE is_current = 'Y' GROUP BY customer_id HAVING COUNT(*) > 1;
ETL Defect Report Template
| Field | Example |
|---|---|
| Summary | fact_orders: 12 orders missing for 2026-10-01 load |
| Environment / load | SIT, batch ID 4471, job wf_load_orders |
| Mapping rule | Rule 3.2: all PAID and SHIPPED orders load to fact_orders |
| Query to reproduce | EXCEPT query on order_id with the same filters |
| Expected vs actual | Expected 50,000 rows; actual 49,988 |
| Sample data | order_id 90012, 90377, 90561 … |
| Severity / priority | High / P1 (financial totals affected) |
| Suspected cause | Orders with NULL region filtered by an inner join |
Severity Guide
- Critical: money or regulatory numbers wrong, or load fails.
- High: missing or duplicate records, broken history.
- Medium: formatting or non-critical attribute errors.
- Low: cosmetic issues in descriptive fields.
To explain a defect in an interview: the rule, how you found it (the query), the root cause and the fix. See explaining an ETL project and ETL test cases.
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 · Snowflake ETL testing · AWS Glue testing · data lake testing
More guides for Indian students: ETL full form · ETL testing with example · ETL testing jobs in India · ETL testing course in India · ETL training in Hyderabad, Bangalore & Pune · best ETL testing course on Udemy · ETL testing roadmap · ETL tester vs ETL developer · interview questions for freshers · free ETL testing mock test · SQL for testers · QA to data engineer · ETL testing kya hai (Hindi) · ETL testing projects for practice · ETL testing notes · work from home ETL jobs
Frequently Asked Questions
What are common defects in ETL testing?
Missing, extra and duplicate records, wrong transformation rules, untrimmed or wrong-case values, wrong lookups, truncation, wrong date formats, orphan keys, incremental load gaps and broken SCD history.
How do you report an ETL defect?
Include the table, load ID, mapping rule, the SQL to reproduce, expected vs actual results, sample keys, severity and suspected cause.
How do I answer "explain a defect you found" in an interview?
Describe the rule, how your query found the issue, the impact, the root cause and the fix, in about a minute.
What is the most common ETL defect?
Duplicates and missing records from wrong joins, filters or re-runs are among the most common.
How do you prevent ETL defects?
Clear mapping documents, data profiling before development, automated reconciliation checks after every load and regression testing for changes.
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.