ETL Testing Interview Questions Infosys – Complete Real-Time & SQL-Focused Guide

ETL Testing is the process of validating that data is correctly Extracted from source systems, Transformed according to business rules, and Loaded into a target Data Warehouse or Data Mart. 

The primary objective of ETL testing is to ensure that data remains accurate, complete, consistent, and reliable throughout the ETL process. It verifies that business transformations are correctly implemented and that the final data supports accurate reporting, analytics, and business decision-making. 

In enterprise environments such as Infosys projects, ETL testing also focuses on validating historical data, data quality, reconciliation, and ETL performance to ensure that business-critical applications receive accurate information. 

Q2. Why is ETL Testing Important in Infosys Projects? 

ETL testing is especially important in Infosys projects because the company works with large enterprise clients across industries such as banking, insurance, healthcare, retail, and telecommunications. 

These industries depend heavily on accurate data for regulatory compliance, financial reporting, operational monitoring, and strategic decision-making. 

Incorrect or incomplete data can result in: 

• Incorrect financial reports. 

• Regulatory compliance violations. 

• Poor business decisions. 

• Customer dissatisfaction. 

• Revenue loss. 

• Increased operational risk. 

ETL testing ensures that enterprise data remains accurate, complete, and trustworthy before it reaches business users and reporting systems. 

Q3. What is a Data Warehouse? 

A Data Warehouse (DW) is a centralized repository that stores historical and integrated data collected from multiple source systems. 

Unlike transactional databases, which support daily business operations, a Data Warehouse is designed specifically for reporting, business intelligence, and analytical processing. 

Key Characteristics of a Data Warehouse 

• Stores historical business data. 

• Integrates data from multiple systems. 

• Supports reporting and analytics. 

• Optimized for complex analytical queries. 

• Contains fact and dimension tables. 

• Enables business trend analysis and decision-making. 

A Data Warehouse provides a single source of truth for enterprise reporting. 

Q4. What is a Staging Table? 

A Staging Table is a temporary table used to store raw extracted data before business transformations are applied. 

The staging layer acts as an intermediate storage area between source systems and the Data Warehouse. 

Benefits of Staging Tables 

• Store raw extracted data. 

• Isolate source data from transformation logic. 

• Support source-to-target reconciliation. 

• Enable ETL job restartability after failures. 

• Simplify debugging and root cause analysis. 

• Reduce dependency on source systems during ETL execution. 

ETL testers frequently validate staging data before comparing it with transformed target data. 

Source-to-Target (S2T) Mapping Questions 

Q5. What is Source-to-Target (S2T) Mapping? 

Source-to-Target (S2T) Mapping is a document that defines how source fields are mapped to target fields along with all business transformation rules. 

A typical S2T mapping document contains: 

• Source table and column names. 

• Target table and column names. 

• Transformation logic. 

• Data type conversions. 

• Default values. 

• Lookup rules. 

• Rejection rules. 

• Null handling. 

• Business validations. 

It serves as the primary reference document for ETL developers and testers. 

Q6. How Do You Validate S2T Mapping in Infosys Projects? 

S2T mapping is validated by writing SQL queries that compare source, staging, and target data after all transformation rules have been applied. 

Typical validation activities include: 

• Comparing source and target column values. 

• Verifying transformation logic. 

• Validating lookup results. 

• Checking calculated fields. 

• Comparing record counts. 

• Validating rejected records. 

• Confirming default value handling. 

SQL is the primary tool used for practical Source-to-Target validation in enterprise ETL projects. 

Q7. What Challenges Do You Face During S2T Validation? 

Some common challenges encountered during Source-to-Target validation include: 

• Complex joins involving multiple source systems. 

• Derived and calculated columns. 

• Conditional transformation logic. 

• Lookup mismatches. 

• Data type inconsistencies. 

• Null value handling. 

• Frequently changing business requirements. 

These challenges require strong SQL skills and a clear understanding of business logic. 

SQL Query Examples (Very Important for Infosys) 

SQL is one of the most heavily tested topics in Infosys ETL interviews. Interviewers often ask candidates to write SQL queries and explain their purpose. 

Record Count Validation 

Example Query 

SELECT COUNT(*) FROM src_orders; 
 
SELECT COUNT(*) FROM fact_orders; 

Q8. What Does This Validation Ensure? 

Record count validation ensures that no records are unintentionally lost during the ETL process. 

It helps verify that the expected number of records has been successfully loaded into the target system after applying business rules. 

Differences in record counts may indicate: 

• Extraction failures. 

• Incorrect filtering. 

• Join issues. 

• ETL loading failures. 

Data Validation Using JOIN 

Example Query 

SELECT s.order_id, 
      s.amount AS src_amount, 
      t.amount AS tgt_amount 
FROM src_orders s 
JOIN fact_orders t 
 ON s.order_id = t.order_id 
WHERE s.amount <> t.amount; 

Q9. Why Is This Query Important? 

This query compares matching records between the source and target tables to identify differences in data values after ETL processing. 

It helps detect: 

• Incorrect transformations. 

• Data loading issues. 

• Business rule violations. 

• Source-to-target data mismatches. 

This is one of the most frequently used SQL queries during ETL validation. 

Finding Missing Records 

Example Query 

SELECT s.order_id 
FROM src_orders s 
LEFT JOIN fact_orders t 
 ON s.order_id = t.order_id 
WHERE t.order_id IS NULL; 

Q10. Which JOIN Is Most Used for Missing Record Checks? 

A LEFT JOIN or RIGHT JOIN is commonly used to identify records that exist in one table but are missing from another. 

These joins help ETL testers verify that all expected records have been successfully loaded into the target Data Warehouse. 

GROUP BY and Aggregation Validation 

Example Query 

SELECT region, 
      SUM(sales_amount) 
FROM fact_sales 
GROUP BY region; 

Q11. What Are You Validating Here? 

This query validates that aggregation logic has been correctly applied within the fact table. 

The calculated totals should match the expected aggregation results derived from the source data after applying all business transformation rules. 

Window Function Example 

Example Query 

SELECT customer_id, 
      SUM(amount) OVER (PARTITION BY customer_id) AS total_spend 
FROM fact_orders; 

Q12. Why Are Window Functions Important in ETL Testing? 

Window functions allow analytical calculations without reducing row-level detail. 

They are commonly used to validate: 

• Running totals. 

• Customer-wise totals. 

• Rankings. 

• Partition-level calculations. 

• Cumulative values. 

These functions are frequently used in enterprise reporting and ETL validation. 

Performance Tuning SQL 

Example Query 

EXPLAIN ANALYZE 
 
SELECT * 
FROM fact_orders 
WHERE order_date >= ‘2025-01-01’; 

Q13. Why Is Performance Testing Important in Infosys ETL Projects? 

Infosys delivers ETL solutions for large enterprise clients that process millions of records every day. 

Performance testing helps ensure that ETL jobs complete within the agreed Service Level Agreement (SLA) and do not delay downstream reporting or business operations. 

Analyzing execution plans helps identify: 

• Full table scans. 

• Missing indexes. 

• Expensive JOIN operations. 

• High-cost sorting. 

• Inefficient filtering. 

Optimizing these issues improves ETL performance and system scalability. 

Slowly Changing Dimension (SCD) Questions (Frequently Asked) 

Q14. What is SCD Type 1? 

SCD Type 1 updates an existing record by overwriting the previous value. 

Historical information is not maintained, making it suitable when only the latest value is required. 

Q15. What is SCD Type 2? 

SCD Type 2 preserves historical data by creating a new record whenever tracked attributes change. 

History is maintained using: 

• Start Date 

• End Date 

• Active Flag 

SCD Type 2 Validation SQL 

SELECT customer_id, 
      start_date, 
      end_date, 
      is_active 
FROM dim_customer 
WHERE customer_id = 101; 

This query verifies that historical records are maintained correctly and that only one active record exists for the customer. 

Q16. What Common SCD Type 2 Defects Are Seen in Infosys Projects? 

Some of the most common production defects include: 

• Multiple active records. 

• Old records not expired. 

• Incorrect effective dates. 

• Missing history records. 

• Duplicate historical records. 

These issues can significantly impact reporting accuracy and business analytics. 

Scenario-Based ETL Testing Questions (Infosys Style) 

Scenario 1: Record Count Mismatch 

Possible Causes 

• Filter condition mismatch. 

• Wrong JOIN type. 

• Duplicate source data. 

• Missing incremental load logic. 

• ETL job failure. 

Compare record counts across the source, staging, and target layers to identify the root cause. 

Scenario 2: Null Values in Target Table 

Validation Query 

SELECT * 
FROM dim_customer 
WHERE email IS NULL; 

Action 

Check the following: 

• Default value handling. 

• Reject logic. 

• Source data availability. 

• Transformation rules. 

• Null handling conditions. 

These checks help determine why null values appear in the target table. 

Scenario 3: ETL Job Misses SLA 

Resolution Steps 

• Analyze the execution plan. 

• Optimize SQL queries. 

• Partition large tables. 

• Tune parallel processing. 

• Improve indexing strategies. 

These optimization techniques help ETL jobs complete within the required SLA. 

ETL Tools Asked in Infosys Interviews 

Infosys generally evaluates candidates based on conceptual understanding and practical experience rather than tool-specific syntax. 

Commonly discussed ETL tools include: 

• Informatica 

• Microsoft SSIS 

• Ab Initio 

• Talend 

• Pentaho 

Strong ETL concepts enable candidates to work effectively with any of these tools. 

ETL Defect Examples and Sample Test Case 

Common ETL Defects in Infosys Projects 

These production defects are frequently discussed during Infosys ETL interviews. 

Sample ETL Test Case 

This test case verifies that SCD Type 2 logic is correctly implemented and that historical records are maintained without creating multiple active records. 

Advanced ETL Interview Questions (Infosys) 

Q17. What Are Audit Fields? 

Audit fields are metadata columns used to track ETL processing and data movement. 

Common audit fields include: 

• created_date 

• updated_date 

• batch_id 

These fields improve traceability, simplify debugging, and help identify which ETL batch processed each record. 

Q18. What Is Hashing in ETL Testing? 

Hashing is a technique used to compare large datasets efficiently by generating checksum or hash values for records. 

Instead of comparing every column individually, testers compare hash values to quickly identify differences between source and target datasets, making validation faster and more efficient. 

Q19. How Do You Test Incremental Loads? 

Incremental loads are validated using timestamp-based tracking mechanisms. 

Common approaches include: 

• last_updated_date columns. 

• Watermark columns. 

• Batch IDs. 

• Timestamp comparisons. 

The objective is to ensure that only new or modified records are processed while previously loaded records remain unchanged. 

Quick Revision Sheet (Infosys ETL Interviews) 

Before attending an Infosys ETL interview, remember these key points: 

• ETL stands for Extract, Transform, and Load. 

• Always validate record counts, data accuracy, and transformation logic. 

• Strong SQL skills, including JOINs, GROUP BY, and Window Functions, are mandatory. 

• SCD Type 2 is one of the most frequently asked interview topics. 

• Performance optimization and SLA awareness are critical for enterprise ETL projects and are commonly discussed during Infosys interviews. 

12. FAQs – ETL Testing Interview Questions Infosys 

Q1. Does Infosys Ask Tool-Specific ETL Questions? 

In most Infosys ETL interviews, the primary focus is on your conceptual understanding of ETL testing rather than memorizing tool-specific syntax. Interviewers generally evaluate how well you understand ETL processes, data validation techniques, SQL, and Data Warehouse concepts. 

If you have worked with ETL tools in previous projects, you should be prepared to explain how you used them in practical scenarios instead of simply listing their features. 

Infosys Interviewers Usually Focus On: 

• ETL architecture and Data Warehouse concepts. 

• Source-to-Target (S2T) mapping validation. 

• SQL-based data validation. 

• Business transformation logic. 

• Production defect handling. 

• Real-time ETL testing scenarios. 

• Performance optimization concepts. 

If you mention tools such as Informatica, Microsoft SSIS, Talend, Ab Initio, or Pentaho, interviewers may ask project-based questions to understand how you used those tools in actual ETL implementations. 

Q2. Is SQL Mandatory for Infosys ETL Interviews?