Infosys ETL Testing Interview Questions – Real-Time, SQL-Focused Preparation Guide

1. What is ETL Testing? 

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

The primary objective of ETL testing is to ensure data accuracy, completeness, consistency, historical integrity, and performance throughout the ETL process. It verifies that all business transformations are correctly implemented and that the final data supports accurate reporting, analytics, and business decision-making. 

In Infosys projects, ETL testing also focuses on validating large-scale enterprise data pipelines, ensuring regulatory compliance, maintaining data quality, and preventing business-critical reporting errors. 

Q2. Why is ETL Testing Critical in Infosys Projects? 

ETL testing is critical in Infosys projects because the company delivers enterprise data solutions for industries such as banking, insurance, healthcare, retail, and telecommunications, where data accuracy directly impacts business operations and regulatory compliance. 

Incorrect or inconsistent data can result in: 

• Incorrect financial reports. 

• Regulatory compliance violations. 

• Business decision failures. 

• Customer dissatisfaction. 

• Revenue loss. 

• Operational risks. 

A comprehensive ETL testing process ensures that enterprise data is reliable before it reaches reporting systems and business users. 

Q3. What is a Data Warehouse? 

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

Unlike OLTP databases, which are optimized for day-to-day transactions, a Data Warehouse is designed for reporting, business intelligence, and analytical processing. 

Key Characteristics of a Data Warehouse 

• Stores historical business data. 

• Integrates data from multiple source 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 serves as the 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 transformations are applied. 

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

Benefits of Staging Tables 

• Store raw extracted data. 

• Isolate source data from business transformations. 

• Support source-to-target reconciliation. 

• Enable ETL job restartability. 

• Simplify debugging and root cause analysis. 

• Reduce dependency on source systems during ETL execution. 

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

Data Warehouse & 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 columns are mapped to target columns, including all transformation rules, default values, lookup logic, rejection criteria, and business validations. 

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 logic. 

• Null handling. 

• Business validation rules. 

This document acts as the primary reference for ETL developers and testers throughout the project lifecycle. 

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 logic has been applied. 

Typical validation activities include: 

• Comparing source and target column values. 

• Verifying transformation logic. 

• Validating calculated and derived columns. 

• Checking lookup values. 

• Comparing record counts. 

• Verifying 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? 

Source-to-Target validation often becomes challenging because enterprise ETL processes involve multiple data sources and complex business rules. 

Some common challenges include: 

• Complex joins across multiple source systems. 

• Conditional and derived columns. 

• Lookup mismatches. 

• Null and default value handling. 

• Data type inconsistencies. 

• 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 during Infosys ETL interviews. Interviewers frequently ask candidates to write SQL queries and explain the business validation performed by each query. 

Record Count Validation 

Example Query 

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

Purpose 

This validation ensures that no data loss has occurred during the ETL process. 

Comparing record counts between the source and target tables helps identify incomplete data loads, filtering issues, or ETL execution 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; 

Purpose 

This query detects transformation or loading defects by comparing corresponding records between the source and target tables. 

It helps validate: 

• Business transformation logic. 

• Data accuracy. 

• Source-to-target consistency. 

• Correct implementation of ETL rules. 

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; 

Purpose 

This query identifies records that exist in the source but are missing in the target Data Warehouse. 

It is commonly used to detect: 

• Missing records. 

• Incorrect filtering. 

• Join-related issues. 

• Incomplete ETL loads. 

GROUP BY & Aggregation Validation 

Example Query 

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

Purpose 

This query validates aggregation logic in fact tables. 

The aggregated values should match the expected business calculations derived from the source data after all transformation rules have been applied. 

Window Function Example 

Example Query 

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

Purpose 

Window functions are used to validate running totals and partition-level calculations without losing row-level detail. 

Common use cases include: 

• Running totals. 

• Customer-wise spending. 

• Ranking. 

• Cumulative calculations. 

• Analytical reporting validations. 

Window functions are increasingly common in enterprise ETL projects and are frequently discussed during Infosys interviews. 

Performance Tuning SQL 

Example Query 

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

Purpose 

This query helps analyze slow SQL queries by displaying the execution plan. 

The execution plan helps identify: 

• Full table scans. 

• Missing indexes. 

• Expensive JOIN operations. 

• High-cost sorting. 

• Inefficient filtering. 

Performance tuning ensures ETL jobs complete within the agreed Service Level Agreement (SLA) and supports efficient processing of enterprise-scale datasets. 

Slowly Changing Dimension (SCD) Questions (Frequently Asked) 

Q8. 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. 

Q9. 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 ensures that only one active record exists for the customer. 

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

Some frequently encountered production defects include: 

• Multiple active records. 

• Old records not expired. 

• Incorrect effective dates. 

• Missing historical records. 

• Duplicate history records. 

These issues can significantly impact reporting accuracy and historical analysis. 

Scenario-Based ETL Testing Interview 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 

Validation Query 

SELECT * 
FROM dim_customer 
WHERE email IS NULL; 

Action 

Verify the following: 

• Default value handling. 

• Reject logic. 

• Source data availability. 

• Transformation rules. 

• Null handling conditions. 

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

Scenario 3: ETL Job Missing 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 primarily 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. 

Candidates with strong ETL fundamentals and SQL expertise can adapt quickly to different ETL platforms. 

ETL Defect Examples and Sample Test Case 

Common ETL Defects in Infosys Projects 

These are among the most common production issues discussed during Infosys ETL interviews. 

Sample ETL Test Case 

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

Advanced Infosys ETL Interview Questions 

Q11. 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 

• source_system 

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

Q12. 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 large-scale validation faster and more efficient. 

Q13. How Do You Test Incremental Loads? 

Incremental loads are validated by verifying only the newly added or modified (delta) records. 

Common validation techniques include: 

• Comparing last_updated_date values. 

• Using watermark columns. 

• Validating batch IDs. 

• Comparing delta records between source and target. 

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 ensuring efficient ETL processing, reliable reporting, and successful enterprise data integration. 

12. FAQs – Infosys ETL Testing Interview Questions 

Q1. Does Infosys Ask Tool-Specific ETL Questions? 

In most Infosys ETL interviews, the focus is primarily on ETL concepts, SQL skills, and real-time project experience rather than memorizing tool-specific syntax. 

Interviewers generally expect candidates to explain how they have used ETL tools in real projects, how they validated data, handled production issues, and implemented business rules. Instead of asking detailed questions about tool menus or configurations, they usually assess your understanding of the ETL process and your ability to solve practical data-related problems. 

Infosys Interviewers Commonly Evaluate 

• Understanding of ETL architecture. 

• Data Warehouse concepts. 

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

• SQL-based data validation. 

• Business transformation logic. 

• Real-time production defect handling. 

• Performance optimization concepts. 

• Data reconciliation and audit validation. 

If you mention tools such as Informatica, Microsoft SSIS, Talend, Ab Initio, or Pentaho, be prepared to explain how you used those tools in your projects rather than simply listing their features. 

Q2. Is SQL Mandatory for Infosys ETL Interviews? 

Yes. Strong SQL skills are non-negotiable for Infosys ETL interviews because SQL is the primary language used to validate data throughout the ETL process. 

Interviewers frequently ask candidates to write SQL queries, explain the logic behind them, and describe how they would use those queries to identify data quality issues, transformation errors, missing records, or performance bottlenecks. 

SQL Topics You Should Master 

• INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN. 

• GROUP BY and aggregate functions. 

• Window Functions such as ROW_NUMBER(), RANK(), DENSE_RANK(), and SUM() OVER(). 

• Subqueries and Common Table Expressions (CTEs). 

• Record count validation. 

• Source-to-target data comparison. 

• Duplicate and missing record detection. 

• Performance analysis using EXPLAIN or EXPLAIN ANALYZE. 

Being able to explain both the SQL syntax and the business purpose of each query is essential for performing well in Infosys ETL interviews. 

Q3. Are Scenario-Based Questions Common? 

Yes. Scenario-based questions are very common in Infosys ETL interviews, especially for candidates with 3 or more years of experience. 

Rather than asking only theoretical questions, interviewers often present real-world situations and expect candidates to explain how they would investigate, validate, and resolve ETL-related issues. 

Common Scenario-Based Questions Include 

• Record count mismatch between source and target. 

• Missing records after an ETL load. 

• Incorrect business transformation results. 

• Duplicate records in fact or dimension tables. 

• Null values appearing in target tables. 

• SCD Type 2 history maintenance issues. 

• Incremental load failures. 

• ETL jobs missing Service Level Agreements (SLAs). 

• Production defect investigation and root cause analysis. 

When answering these questions, explain the validation steps, SQL queries you would use, the likely root cause, and how you would resolve the issue. Demonstrating a structured troubleshooting approach creates a strong impression during technical interviews. 

Q4. Is ETL Testing Manual or Automated in Infosys?