1. What is ETL Testing? (Definition + Real-Time Example)

ETL Testing is the process of validating data during Extract, Transform, and Load operations to ensure data accuracy, completeness, consistency, and performance in a data warehouse (DW) environment.

Real-Time Example (5+ Years Perspective)

Source: Order data from OLTP (MySQL / Oracle)
Transform: Currency conversion, deduplication, SCD handling, hashing
Target: Fact_Orders & Dim_Customer tables in DW
ETL Testing Focus: Business rules, historical data, incremental loads, performance SLAs

At 5 years experience, interviewers expect strong SQL, DW concepts, S2T mapping, and scenario handling.

2. Data Warehouse (DW) Flow

Source → Staging → Transform → Load → Reporting

DW Layer Responsibilities

Source Layer: ERP, CRM, APIs, flat files
Staging Layer: Raw data snapshot
Transformation Layer: Business logic, cleansing
Load Layer: Fact & Dimension tables
Reporting Layer: BI tools (Power BI, Tableau)

3. ETL Testing Interview Questions & Answers (5 Years Experience)

This section contains 60+ most-asked ETL testing interview questions and answers for 5 years experience, from basics to advanced real-time scenarios.

A. Core ETL & DW Interview Questions

1. What is ETL testing and why is it critical?

ETL testing ensures accurate data movement, correct transformations, historical consistency, and reliable reporting for business decisions.

2. Difference between ETL testing and database testing?

ETL testing validates data flow and transformation logic, while DB testing focuses on schema, constraints, and CRUD operations.

3. What is a staging area?

A temporary layer storing raw extracted data before transformations.

4. What is source-to-target (S2T) mapping?

S2T mapping defines how source fields map to target fields along with transformation rules.

B. SCD, History & Audit Questions

5. Explain SCD Type 1 and Type 2

SCD Type 1: Overwrites old data (no history)
SCD Type 2: Preserves history using effective_date, expiry_date, active_flag

6. How do you test SCD Type 2?

Validate:

Old record expired
New record inserted
Only one active record

7. What are audit fields?

load_date, batch_id, created_ts, updated_ts, record_source

8. What is hashing in ETL?

Used to detect changes by comparing hash values of source and target records.

C. SQL-Based ETL Validation (Must for 5 Years)

Sample Tables

Source_Orders

order_id

cust_id

amount

currency

JOIN – Missing Records 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;

D. Scenario-Based ETL Testing Questions (Most Important)

9. What if source and target record counts mismatch?

Validate filters
Check rejected records
Review joins & where clauses

10. How do you handle NULL values?

Default values (NVL/COALESCE)
Reject records
Allow nulls based on business rules

11. How do you test incremental loads?

Validate delta records using last_run_date and batch_id.

12. ETL job failed – what steps do you take?

Analyze logs
Identify failed transformation
Validate restartability

13. ETL job is slow – how do you troubleshoot?

Index checks
Partition pruning
SQL optimization
Parallelism review

E. Advanced ETL QA Questions (5+ Years)

14. Difference between full load and incremental load?

Full load loads all data; incremental load loads only changed/new data.

15. What is data reconciliation?

Comparing aggregated and detailed data between source and target.

16. How do you test surrogate keys?

Ensure uniqueness and correct mapping with natural keys.

17. What are reject tables?

Tables storing records that failed validation rules.

4. ETL Architecture & Mapping Validation

Mapping Validation Checklist

✔ Column mapping
✔ Transformation logic
✔ Data type & length
✔ Mandatory vs optional fields
✔ Business rules

5. ETL Tools – Interview Perspective

Common ETL Tools

Informatica
Microsoft SSIS
Ab Initio
Pentaho
Talend

6. ETL Defect Examples

Defect Type	Example
Data Mismatch	Wrong transformation
Duplicate Records	Missing dedup logic
History Issues	SCD Type 2 failure
Load Failure	Job aborted
Performance	SLA breach

7. Sample ETL Test Case (Senior Level)

Test Case: Incremental Load with SCD Type 2

Validate delta extraction
Ensure history preservation
Validate audit fields & hash comparison

8. Quick Revision Sheet (5 Years Experience)

✔ ETL architecture
✔ S2T mapping
✔ Advanced SQL (joins, window functions)
✔ SCD Type 1 & 2
✔ Incremental loads
✔ Performance tuning
✔ Defect management

9. FAQs – ETL Testing Interview (5 Years)

Q1. What level of SQL is expected for 5 years experience?

Advanced SQL including window functions, performance tuning, and complex joins.

Q2. Do ETL testers write automation?

Mostly SQL-based automation using scripts and scheduling tools.

Q3. What makes a strong ETL tester at senior level?

Strong SQL, DW knowledge, debugging skills, and business understanding.