1. What is ETL Testing? (Definition + Real-Time Example)
ETL Testing is the validation of data during Extract, Transform, and Load processes to ensure data accuracy, completeness, consistency, historical correctness, and performance when data is moved into a Data Warehouse (DW).
Real-Time Example (Experienced Perspective)
- Source: Multiple OLTP systems (Orders, Customers, Payments)
- Transform: Deduplication, currency conversion, SCD handling, hashing
- Target: Fact_Sales, Dim_Customer, Dim_Date
- Testing Focus: Business rules, reconciliation, incremental loads, performance SLAs
For experienced professionals, interviews focus on deep SQL, S2T mappings, ETL defects, and troubleshooting.
2. Data Warehouse (DW) Flow
Source → Staging → Transform → Load → Reporting
Layer Responsibilities
- Source Layer: ERP, CRM, APIs, flat files
- Staging Layer: Raw, untransformed data
- Transformation Layer: Business logic, cleansing, SCD rules
- Load Layer: Fact & Dimension tables
- Reporting Layer: BI tools (Power BI, Tableau)
3. ETL Testing Interview Questions and Answers for Experienced
Below are 70+ most-asked ETL testing interview questions and answers for experienced professionals, ranging from core concepts to advanced scenarios.
A. Core ETL & DW Interview Questions
1. Why is ETL testing critical in data warehousing?
Because business decisions depend on accurate, reconciled, and historical data.
2. Difference between ETL testing and data validation?
ETL testing includes validation plus performance, error handling, and end-to-end data flow checks.
3. What is a staging area?
A temporary layer that holds extracted raw data before transformation.
4. What is source-to-target (S2T) mapping?
A document defining how each source column maps to target columns with transformation logic.
B. SCD, History & Audit Questions
5. Explain SCD Type 1 and Type 2
- SCD Type 1: Overwrites old values
- SCD Type 2: Preserves history using effective_date, expiry_date, active_flag
6. How do you test SCD Type 2?
Ensure:
- Old record expired
- New record inserted
- Only one active record exists
7. What are audit fields?
load_date, batch_id, record_source, created_ts, updated_ts
8. What is hashing in ETL testing?
Hashing detects changes efficiently by comparing hash values of records.
C. Advanced SQL for ETL Validation
Sample Tables
Source_Orders
| order_id | cust_id | amount | currency |
Target_Fact_Orders
| order_key | cust_key | amount_usd | load_date |
JOIN – Missing Record Validation
SELECT s.order_id
FROM source_orders s
LEFT JOIN target_fact_orders t
ON s.order_id = t.order_key
WHERE t.order_key IS NULL;
GROUP BY – Aggregation Validation
SELECT cust_id, SUM(amount)
FROM source_orders
GROUP BY cust_id;
Window Function – Duplicate Detection
SELECT *
FROM (
SELECT order_key,
ROW_NUMBER() OVER (PARTITION BY order_key ORDER BY load_date DESC) rn
FROM target_fact_orders
) x
WHERE rn > 1;
Performance Tuning – Explain Plan
EXPLAIN PLAN FOR
SELECT * FROM target_fact_orders WHERE load_date >= SYSDATE – 1;
D. Scenario-Based ETL Testing Questions
9. Source and target record count mismatch – how do you debug?
- Validate extraction filters
- Check rejected records
- Review joins & transformation rules
10. How do you handle NULL values?
- Replace with default values
- Reject records
- Allow nulls per business rules
11. How do you test incremental loads?
Validate delta records using last_run_date and batch_id.
12. ETL job failed mid-run – what steps do you take?
- Analyze logs
- Identify failed component
- Validate restartability
13. ETL performance issue – how do you fix it?
- Index optimization
- Partition pruning
- SQL tuning
- Parallel processing review
E. Advanced ETL QA Questions
14. What is data reconciliation?
Comparing aggregated and detailed source vs target data.
15. How do you test surrogate keys?
Ensure uniqueness and correct mapping with natural keys.
16. What are reject tables?
Tables capturing records that fail validation rules.
17. What is late-arriving data?
Data arriving after scheduled load windows; requires special handling.
4. ETL Architecture & Mapping Validation
Mapping Validation Checklist
✔ Column mapping
✔ Transformation rules
✔ Data types & length
✔ Mandatory vs optional fields
✔ Business logic alignment
5. ETL Tools – Interview Knowledge
Common ETL Tools
- Informatica
- Microsoft SSIS
- Ab Initio
- Pentaho
- Talend
6. ETL Defect Examples
| Defect Type | Example |
| Data Mismatch | Incorrect transformation |
| Duplicate Records | Missing dedup logic |
| History Issues | SCD Type 2 failure |
| Load Failure | Job aborted |
| Performance | SLA breach |
7. Sample ETL Test Case (Experienced Level)
Test Case: Incremental Load with Hashing
- Validate delta extraction
- Compare hash values
- Ensure only changed records updated
8. Quick Revision Sheet (Experienced ETL Tester)
✔ ETL architecture
✔ S2T mapping
✔ Advanced SQL (joins, window functions)
✔ SCD Type 1 & 2
✔ Incremental & full loads
✔ Performance tuning
✔ Defect life cycle
9. FAQs – ETL Testing Interview Questions for Experienced
Q1. What SQL level is expected for experienced ETL testers?
Advanced SQL with performance tuning and window functions.
Q2. Is automation used in ETL testing?
Yes, using SQL scripts, shell scripts, and scheduling tools.
Q3. What differentiates a senior ETL tester?
Strong SQL, DW expertise, defect analysis, and business understanding.