1. What is ETL Testing? (Definition + Example)
ETL Testing is the process of validating data during Extract, Transform, and Load operations to ensure data accuracy, completeness, consistency, and performance when data moves from source systems to a data warehouse (DW) or reporting layer.
Real-Time Example
- Source: Banking transaction system
- Transform: Currency conversion, data cleansing, deduplication, SCD logic
- Target: Fact_Transactions and Dim_Customer tables
ETL testing verifies that business rules are applied correctly and that reports built on DW data are reliable.
2. Data Warehouse (DW) Flow
Source → Staging → Transform → Load → Reporting
DW Layer Explanation
- Source Layer: OLTP databases, flat files, APIs
- Staging Layer: Raw extracted data
- Transformation Layer: Business rules, calculations, SCD logic
- Load Layer: Fact & dimension tables
- Reporting Layer: BI dashboards (Power BI, Tableau)
3. ETL Testing Interview Questions Cognizant – What Interviewers Expect
In companies like Cognizant, ETL testing interviews focus on:
- Strong SQL validation
- S2T mapping understanding
- Data warehouse concepts
- Real-time ETL defect scenarios
- Tool knowledge (Informatica, SSIS, Ab Initio)
4. ETL Testing Interview Questions & Answers (Basic → Advanced)
Below are 70+ frequently asked ETL testing interview questions and answers for Cognizant-style interviews.
A. Basic ETL Interview Questions
1. What is ETL?
ETL stands for Extract, Transform, Load.
2. What is ETL testing?
ETL testing ensures that data extracted from sources is correctly transformed and loaded into the target system.
3. What is a data warehouse?
A centralized system that stores historical and integrated data for reporting and analytics.
4. What are source systems?
Operational systems like OLTP databases, CRM, ERP, or flat files.
5. What is a staging table?
A temporary table used to store raw data before transformation.
B. Data Warehouse Interview Questions
6. What is a fact table?
Stores measurable business data (sales amount, quantity).
7. What is a dimension table?
Stores descriptive attributes (customer, product, time).
8. What is star schema?
A schema with one fact table connected to multiple dimension tables.
9. Difference between OLTP and OLAP?
OLTP handles transactions; OLAP supports analytics.
C. ETL QA & Mapping Questions
10. What is source-to-target (S2T) mapping?
A document defining source fields, target fields, and transformation rules.
11. What validations are done in ETL testing?
- Record count validation
- Data type & length validation
- Transformation logic validation
- Null & default value checks
- Duplicate record checks
12. What is data reconciliation?
Comparing source and target data to ensure completeness and accuracy.
D. SCD & History Questions (Very Important)
13. What is SCD Type 1?
Overwrites old data without maintaining history.
14. What is SCD Type 2?
Maintains history using effective_date, expiry_date, and active_flag.
15. How do you test SCD Type 2?
- Old record expired
- New record inserted
- Only one active record
16. What are audit fields?
load_date, batch_id, created_ts, updated_ts, record_source
5. Real SQL Query Examples for ETL Validation
Sample Dataset
Source_Orders
| order_id | cust_id | amount | currency |
Target_Fact_Orders
| order_key | cust_key | amount_usd | load_date |
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;
6. Scenario-Based ETL Testing Questions (Cognizant Focus)
17. What if source and target record counts mismatch?
Check extraction filters, rejected records, joins, and transformation logic.
18. How do you handle NULL values?
- Replace with default values
- Reject records
- Allow nulls per business rules
19. How do you test incremental loads?
Validate delta records using last_run_date and batch_id.
20. ETL job is slow – how do you troubleshoot?
- Check indexes
- Review execution plans
- Optimize joins
- Enable partition pruning
7. ETL Architecture & Mapping Validation
Mapping Validation Checklist
✔ Column mapping
✔ Transformation logic
✔ Data types & length
✔ Mandatory fields
✔ Business rules
8. ETL Tools – Cognizant Interview Perspective
Commonly Asked Tools
- Informatica
- Microsoft SSIS
- Ab Initio
- Pentaho
- Talend
9. ETL Defect Examples
| Defect Type | Example |
| Data Mismatch | Wrong transformation logic |
| Duplicate Records | Dedup logic missing |
| History Issues | SCD Type 2 not applied |
| Load Failure | Job aborted |
| Performance | SLA breach |
10. Sample ETL Test Case
Test Case: Incremental Load Validation
- Validate delta extraction
- Verify audit fields
- Check hash values for changes
11. Quick Revision Sheet (ETL Testing – Cognizant)
✔ ETL architecture
✔ S2T mapping
✔ SQL joins & aggregations
✔ SCD Type 1 & 2
✔ Incremental loads
✔ Performance tuning
✔ Defect lifecycle
12. FAQs – ETL Testing Interview Questions Cognizant
Q1. What SQL level is expected in Cognizant ETL interviews?
Advanced SQL including joins, window functions, and performance tuning.
Q2. Is ETL testing manual or automated?
Mostly manual using SQL; automation via scripts is a plus.
Q3. What makes a strong ETL tester at Cognizant?
Strong SQL, DW knowledge, defect analysis, and real-time project experience.