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

ETL Testing is the process of validating data during Extract, Transform, Load operations to ensure data accuracy, completeness, consistency, and performance when data moves from source systems to a data warehouse (DW) or reporting layer.

Simple Example

Source: OLTP system (Orders table)
Transform: Currency conversion, data cleansing, deduplication
Target: Fact_Orders table in DW

ETL testing validates:

Record counts
Data transformations
Business rules
Data integrity
Performance & load time

2. Data Warehouse (DW) Flow

Source → Staging → Transform → Load → Reporting

DW Layer Explanation

Source Layer: ERP, CRM, flat files, APIs
Staging Layer: Raw data load, no transformation
Transformation Layer: Business rules applied
Load Layer: Fact & dimension tables
Reporting Layer: BI tools (Power BI, Tableau)

3. ETL Test Lead Role – Interview Focus

An ETL Test Lead is expected to:

Own ETL test strategy
Review S2T mappings
Manage test cycles & defects
Optimize SQL validations
Coordinate with ETL developers & BI teams

4. ETL Test Lead Interview Questions & Answers (Basic → Advanced)

Basic ETL Interview Questions

1. What is ETL?

ETL stands for Extract, Transform, Load – used to move and transform data from source systems into a data warehouse.

2. What is ETL testing?

It validates data accuracy, transformations, integrity, and performance during ETL processing.

3. Difference between ETL testing and database testing?

ETL testing focuses on data movement and transformation, while DB testing focuses on schema, constraints, and CRUD operations.

4. What are source-to-target (S2T) mappings?

S2T mappings define how each source field maps to target fields with transformation rules.

Intermediate ETL QA Questions

5. What validations do you perform in ETL testing?

Source vs target record count
Data type & length validation
Transformation logic validation
Null & default value checks
Duplicate handling

6. What is a staging table?

A temporary table holding raw extracted data before transformation.

7. What is data reconciliation?

Comparing source and target data to ensure completeness and correctness.

Advanced ETL Test Lead Interview Questions

8. Explain SCD Type 1 and Type 2

SCD Type 1: Overwrites old data (no history)
SCD Type 2: Maintains historical records using effective dates & flags

9. What are audit fields in ETL?

load_date
batch_id
created_by
updated_timestamp

10. How do you validate hash keys?

By recalculating hash values in SQL and comparing with target hash keys.

5. Real SQL Query Examples for ETL Validation

Sample Dataset

Source_Orders

order_id

customer_id

amount

currency

JOIN Validation

SELECT s.order_id, t.order_key

FROM source_orders s

LEFT JOIN target_fact_orders t

ON s.order_id = t.order_key

WHERE t.order_key IS NULL;

➡️ Finds missing records in target.

GROUP BY – Aggregation Validation

SELECT customer_id, SUM(amount)

FROM source_orders

GROUP BY customer_id;

Compare with target aggregation.

Window Function – Deduplication Check

SELECT *

FROM (

SELECT order_id,

ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY load_date DESC) rn

FROM target_fact_orders

) t

WHERE rn > 1;

Performance Tuning SQL

EXPLAIN PLAN FOR

SELECT * FROM target_fact_orders WHERE load_date = SYSDATE;

6. Scenario-Based ETL Testing Questions (Most Asked)

11. What if record count mismatches?

Validate filters
Check rejected records
Verify joins & conditions

12. How do you handle NULL values?

Default values
Reject records
Use NVL/COALESCE

13. ETL job running slow – what do you do?

Check indexes
Partition tables
Reduce transformations
Analyze execution plan

14. How do you test incremental loads?

Validate last_run_date
Check delta records only
Compare with previous batch

7. ETL Architecture & Mapping Validation

Mapping Validation Checklist

Column mapping
Transformation logic
Data type compatibility
Mandatory vs optional fields
Business rules

8. ETL Tools – Interview Perspective

Commonly Used Tools

Informatica
Microsoft SSIS
Ab Initio
Pentaho
Talend

9. ETL Defect Examples

Defect Type	Description
Data Mismatch	Incorrect transformation
Truncation	Data length mismatch
Duplicate Records	Missing dedup logic
Load Failure	Job aborted
Performance	SLA breach

10. Sample ETL Test Case

Test Case: SCD Type 2 Validation

Source: Customer update
Expected: Old record expired, new record inserted
SQL Validation: Effective date & active flag

11. Quick Revision Sheet (ETL Test Lead)

✔ S2T mapping
✔ Record count check
✔ Transformation validation
✔ SCD types
✔ Incremental vs full load
✔ Performance tuning
✔ Audit fields

12. FAQs – ETL Test Lead Interview

Q1. What is the most important skill for an ETL test lead?

Strong SQL, DW concepts, and stakeholder communication.

Q2. How much SQL is required for ETL testing?

Advanced SQL including joins, subqueries, window functions.

Q3. Is ETL testing manual or automated?

Primarily manual, with automation using SQL, Python, shell scripts.