Wipro ETL Testing Interview Questions

Leave a Comment / Uncategorized / By

What Is ETL Testing? (Definition + Wipro Project Example)

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

The primary objective of ETL testing is to verify that data extracted from one or more source systems is correctly transformed according to business rules and successfully loaded into the target Data Warehouse without data loss, duplication, corruption, or performance issues. ETL testing also ensures that the final reports generated from the Data Warehouse are accurate, reliable, and meet business as well as regulatory requirements. 

Unlike functional application testing, ETL testing focuses entirely on validating data movement, transformation logic, data quality, reconciliation, and reporting accuracy. Since organizations rely on Data Warehouses for business intelligence, analytics, compliance, and executive reporting, even a small data inconsistency can lead to incorrect business decisions and regulatory risks. 

TCS Project Example 

In large-scale enterprise and BFSI (Banking, Financial Services, and Insurance) programs delivered by Tata Consultancy Services (TCS), ETL testing is typically performed in complex environments where data is integrated from multiple business applications into centralized Data Warehouses. 

A typical ETL testing project in such environments involves: 

  • Multiple heterogeneous source systems such as OLTP databases, ERP applications, CRM systems, and flat files.  
  • High volumes of transactional and historical data processed through daily or scheduled ETL jobs.  
  • Strict Service Level Agreements (SLAs) that require ETL jobs to complete within predefined time windows.  
  • Regulatory reporting requirements where data accuracy is essential for compliance and auditing.  

During the ETL lifecycle, data is extracted from different operational systems, transformed according to business rules, and loaded into fact and dimension tables in the Data Warehouse. 

A TCS ETL Tester is expected to validate every stage of this process using SQL and ETL testing techniques. Their responsibilities generally include: 

  • Validating source data before ETL execution.  
  • Comparing source and target record counts.  
  • Verifying Source-to-Target (S2T) mappings.  
  • Testing complex transformation logic.  
  • Validating joins, aggregations, and calculations.  
  • Testing Slowly Changing Dimension (SCD) implementations.  
  • Detecting duplicate and missing records.  
  • Performing data reconciliation between source and target systems.  
  • Validating audit fields and metadata.  
  • Ensuring Business Intelligence (BI) reports match the Data Warehouse.  
  • Investigating ETL failures and production defects using SQL.  

Strong SQL skills and the ability to solve scenario-based problems are essential because most ETL validation activities are performed directly on the database. 

Data Warehouse Flow 

A standard ETL architecture follows the flow: 

Source → Staging → Transform → Load → Reporting 

Each stage has a specific role in ensuring that high-quality data reaches business users. 

Source 

The source layer contains the original operational data that is extracted for ETL processing. 

Typical source systems include: 

  • OLTP databases.  
  • ERP systems.  
  • CRM applications.  
  • Flat files (CSV, Excel, TXT).  
  • XML and JSON files.  
  • Legacy systems.  
  • Cloud databases.  

These systems are optimized for transaction processing rather than reporting and analytics. 

Staging 

The staging area acts as a temporary landing zone where raw extracted data is stored before transformation. 

At this stage: 

  • Data is copied exactly from the source.  
  • No business rules are applied.  
  • Initial validation checks are performed.  
  • Data from multiple sources is consolidated.  
  • Corrupt or incomplete files can be identified before further processing.  

Staging helps isolate the extraction process from the transformation process and improves ETL performance. 

Transformation 

The transformation stage applies business rules to convert raw data into standardized, meaningful information. 

Typical transformation activities include: 

  • Data cleansing.  
  • Data standardization.  
  • Duplicate removal.  
  • Data type conversion.  
  • Currency conversion.  
  • Lookup validation.  
  • Join operations.  
  • Aggregations.  
  • Business calculations.  
  • Surrogate key generation.  
  • Slowly Changing Dimension (SCD) handling.  

This stage is the most critical because incorrect transformation logic directly impacts reporting accuracy. 

Load 

After transformation, the processed data is loaded into the target Data Warehouse. 

Data is generally loaded into: 

  • Fact tables.  
  • Dimension tables.  
  • Aggregate tables.  
  • Summary tables.  

The loading process may use: 

  • Full Load.  
  • Incremental Load.  
  • Delta Load.  

ETL testers verify that all required records are loaded successfully without duplicates, missing records, or data corruption. 

Reporting 

Once the data is loaded, Business Intelligence (BI) tools generate reports and dashboards for business users. 

Reporting typically supports: 

  • Business Intelligence dashboards.  
  • Management Information System (MIS) reports.  
  • Financial reporting.  
  • Regulatory compliance reports.  
  • Operational dashboards.  
  • Executive analytics.  
  • Customer and sales analysis.  

ETL testers validate that the reports accurately reflect the data stored in the Data Warehouse and that all business calculations and aggregations are correct. 

TCS ETL Testing Interview Questions & Answers 

Basic → Advanced | Real-World & Scenario-Based 

TCS ETL testing interviews typically include a combination of conceptual questions, SQL-based validations, and real-world production scenarios. Interviewers assess not only theoretical knowledge but also practical experience in handling enterprise ETL processes. 

Basic Level 

Basic questions evaluate a candidate’s understanding of ETL fundamentals and Data Warehouse concepts. 

Common topics include: 

  • ETL process overview.  
  • Data Warehouse architecture.  
  • Source-to-Target (S2T) mapping.  
  • Fact and dimension tables.  
  • Record count validation.  
  • NULL validation.  
  • Duplicate detection.  
  • Audit fields.  

Intermediate Level 

Intermediate questions focus on practical ETL testing activities performed in enterprise projects. 

Topics generally include: 

  • Mapping validation.  
  • Join validation.  
  • Transformation testing.  
  • Aggregation validation.  
  • Lookup validation.  
  • Incremental and full load testing.  
  • Data reconciliation.  
  • Slowly Changing Dimension (SCD) validation.  
  • Production defect analysis.  

Advanced Level 

Advanced interview questions evaluate production support experience, SQL expertise, and troubleshooting skills. 

Common areas include: 

  • Window functions.  
  • Query optimization.  
  • Performance tuning.  
  • Execution plan analysis.  
  • Large-volume data validation.  
  • Partition validation.  
  • Historical data reloads.  
  • Data reconciliation across multiple systems.  

Real-World Scenario-Based Questions 

TCS interviewers frequently ask scenario-based questions to understand how candidates approach practical ETL challenges. 

Examples include: 

  • Source and target record counts do not match.  
  • Duplicate records appear after an ETL load.  
  • Reports display incorrect totals.  
  • SCD Type 2 history is not maintained correctly.  
  • Incremental loads miss newly inserted records.  
  • ETL jobs fail to meet SLA timelines.  
  • Late-arriving data affects business reports.  
  • Currency conversion produces incorrect values.  
  • Fact records do not have matching dimension records.  
  • Production defects require immediate investigation.  

Candidates are generally expected to explain: 

  • How they would investigate the issue.  
  • How they would identify the root cause.  
  • The validation steps they would perform.  
  • How they would confirm that the issue has been resolved.  

Strong SQL knowledge, analytical thinking, attention to detail, and experience with real-world ETL validation scenarios are key skills evaluated during TCS ETL testing interviews. 

 Basic ETL Testing Questions (1–15) 

1. What is ETL testing? 

ETL testing is the process of validating the accuracy, completeness, consistency, and correctness of data during the Extract, Transform, and Load (ETL) process. 

The objective of ETL testing is to ensure that data is extracted correctly from source systems, transformed according to business rules, and loaded successfully into the target Data Warehouse without data loss, duplication, or corruption. It also verifies that reports generated from the Data Warehouse accurately represent the processed data. 

2. Why is ETL testing important in TCS projects? 

ETL testing is important in TCS projects because enterprise clients rely on accurate and reliable data for analytics, reporting, compliance, and business decision-making. 

In large-scale projects, incorrect ETL processing can lead to: 

  • Inaccurate business reports.  
  • Incorrect financial calculations.  
  • Regulatory compliance issues.  
  • Poor decision-making.  
  • Customer dissatisfaction.  
  • Production defects.  

By validating every stage of the ETL process, testers help ensure that enterprise analytics and compliance reports remain accurate and trustworthy. 

3. What is Source-to-Target (S2T) mapping? 

Source-to-Target (S2T) mapping is a document that defines how data moves from the source system to the target Data Warehouse. 

It typically includes: 

  • Source tables and columns.  
  • Target tables and columns.  
  • Transformation logic.  
  • Business rules.  
  • Data types.  
  • Lookup conditions.  
  • Default values.  
  • Validation rules.  

ETL testers use the S2T mapping document as the primary reference to verify that transformations are implemented correctly. 

4. What are the types of ETL testing? 

ETL testing consists of multiple validation stages, each focusing on a different part of the ETL process. 

The major types include: 

  • Source testing.  
  • Staging testing.  
  • Transformation testing.  
  • Target testing.  
  • Data reconciliation testing.  

Together, these testing types ensure that the ETL process works accurately, efficiently, and according to business requirements. 

5. What is staging area testing? 

Staging area testing verifies that the data extracted from the source systems is copied accurately into the staging area before any transformations are applied. 

Typical validations include: 

  • Record count comparison.  
  • Data value comparison.  
  • NULL validation.  
  • Duplicate checks.  
  • Data type verification.  

Since no business rules are applied at this stage, the staging data should exactly match the source data. 

6. What are audit fields? 

Audit fields are metadata columns that help track ETL processing and data loading activities. 

Common audit fields include: 

  • load_date  
  • batch_id  
  • created_by  
  • updated_date  

These fields help testers verify: 

  • When the data was loaded.  
  • Which batch processed the data.  
  • Who created or updated the record.  
  • Whether the correct ETL execution processed the records.  

Audit fields are also valuable for troubleshooting and maintaining data lineage. 

7. How do you validate record counts? 

Record count validation is performed by comparing the number of records in the source and target tables after applying the required business filters. 

The tester checks: 

  • Source record count.  
  • Target record count.  
  • Rejected records.  
  • Filtered records.  
  • Duplicate elimination.  

If differences exist, further investigation is required to identify the cause. 

8. What is data reconciliation? 

Data reconciliation is the process of comparing data between the source system and the target Data Warehouse to ensure consistency. 

Typical reconciliation activities include: 

  • Record count comparison.  
  • Total amount comparison.  
  • Aggregate validation.  
  • Duplicate detection.  
  • Missing record identification.  
  • Business rule verification.  

Successful reconciliation confirms that the ETL process has transferred data correctly. 

9. Difference between fact and dimension tables? 

Fact tables store measurable business data, while dimension tables store descriptive information that provides context for analysis. 

Fact tables contain: 

  • Sales.  
  • Revenue.  
  • Quantity.  
  • Profit.  
  • Transaction counts.  

Dimension tables contain: 

  • Customer details.  
  • Product information.  
  • Employee data.  
  • Date information.  
  • Location details.  

Fact tables reference dimension tables using foreign keys to support analytical queries. 

10. What is data warehouse testing? 

Data Warehouse testing is the process of validating the Data Warehouse architecture, ETL logic, and reporting outputs. 

It includes testing: 

  • Database schemas.  
  • ETL transformations.  
  • Fact and dimension tables.  
  • Data quality.  
  • Aggregations.  
  • BI reports.  
  • Performance.  

The goal is to ensure that the Data Warehouse supports accurate reporting and analytics. 

11. What is full load vs incremental load? 

A full load reloads all available records from the source into the target Data Warehouse. 

An incremental load processes only the new or modified (delta) records since the previous ETL execution. 

Full loads are commonly used during initial migrations, whereas incremental loads are preferred for routine processing because they reduce execution time and system resource usage. 

12. What is reject data testing? 

Reject data testing validates records that fail ETL validation rules and are redirected to reject tables or error logs. 

The tester verifies: 

  • Rejected record count.  
  • Error reasons.  
  • Validation rule failures.  
  • Logging accuracy.  
  • Data correction process.  

This ensures that invalid records are handled appropriately without affecting valid data. 

13. What is mapping validation? 

Mapping validation ensures that the implemented ETL transformations exactly follow the Source-to-Target (S2T) mapping document. 

Validation includes: 

  • Column mappings.  
  • Business rules.  
  • Transformation logic.  
  • Data type conversions.  
  • Lookup rules.  
  • Default values.  

This ensures that the ETL process complies with documented business requirements. 

14. What is end-to-end ETL testing? 

End-to-end ETL testing validates the complete flow of data from the source system to the Data Warehouse and finally to the reporting layer. 

The process includes: 

  • Source validation.  
  • Staging validation.  
  • Transformation validation.  
  • Target validation.  
  • Report validation.  

This confirms that data is accurately reflected in the final business reports. 

15. Why is SQL mandatory for ETL testers? 

SQL is mandatory because ETL testers use SQL queries to validate data throughout the ETL process. 

SQL is used to: 

  • Compare source and target data.  
  • Validate transformations.  
  • Verify aggregations.  
  • Detect duplicates.  
  • Identify missing records.  
  • Test referential integrity.  
  • Validate business rules.  

Strong SQL skills enable testers to identify data issues quickly and ensure accurate ETL processing. 

Scenario-Based Intermediate Questions (16–35) 

16. Scenario: Source has 1M records, target has 9.7L. What do you check? 

If the target contains fewer records than the source, the tester investigates the discrepancy by reviewing the ETL process. 

The checks include: 

  • Applied filter conditions.  
  • Rejected rows.  
  • Duplicate elimination logic.  
  • Join conditions.  
  • Incremental load logic.  
  • Error logs.  

These validations help identify whether the missing records were intentionally filtered, rejected due to validation failures, or lost because of an ETL defect. 

17. How do you test NULL handling? 

NULL handling is tested by validating mandatory columns and checking that appropriate default values are assigned where required. 

The tester verifies: 

  • Mandatory fields do not contain NULL values.  
  • Default values are applied correctly.  
  • Business rules regarding NULL handling are followed.  
  • Invalid NULL records are rejected or logged.  

This ensures data quality and prevents incomplete records from reaching the Data Warehouse. 

18. What is SCD Type 1? 

Slowly Changing Dimension (SCD) Type 1 updates a dimension record by overwriting the existing value without maintaining historical information. 

It is used when only the most recent value is required and previous values do not need to be retained. 

19. What is SCD Type 2? 

SCD Type 2 preserves historical information by inserting a new record whenever a dimension attribute changes. 

History is maintained using: 

  • effective_from  
  • effective_to  
  • current_flag  

This allows organizations to analyze historical changes over time while keeping the latest record active. 

20. Scenario: Customer address changes. What do you test? 

When a customer’s address changes in an SCD Type 2 implementation, the tester validates that: 

  • A new dimension record is inserted.  
  • The previous record is expired.  
  • effective_from and effective_to dates are updated correctly.  
  • The new record has current_flag = ‘Y’.  
  • The old record has current_flag = ‘N’.  

This ensures that historical address information is preserved while the latest address remains active. 

21. What is surrogate key testing? 

Surrogate key testing verifies that surrogate keys generated for dimension tables are: 

  • Unique.  
  • Non-null.  
  • Properly assigned.  
  • Correctly mapped to business keys.  

Surrogate keys help maintain relationships between fact and dimension tables without relying on changing business keys. 

22. What is a late-arriving dimension? 

A late-arriving dimension occurs when a fact record is received before its corresponding dimension record. 

The tester validates that: 

  • An unknown or dummy surrogate key is assigned temporarily.  
  • Fact records are not rejected unnecessarily.  
  • The dimension reference is updated once the actual dimension data becomes available.  

This prevents data loss while maintaining referential integrity. 

23. Scenario: Duplicate rows appear in target but not in source. Why? 

Duplicate records in the target are commonly caused by: 

  • Incorrect join conditions.  
  • Missing deduplication logic.  
  • Multiple source records matching a single target record.  
  • Faulty incremental load processing.  

The tester investigates the ETL transformations and SQL joins to determine the root cause. 

24. How do you test aggregation logic? 

Aggregation testing compares summarized values calculated from the source data with the corresponding values stored in the target Data Warehouse. 

Common validations include: 

  • Total sales.  
  • Record counts.  
  • Average values.  
  • Minimum values.  
  • Maximum values.  

This ensures that transformation and aggregation rules are implemented correctly. 

25. What is hashing used for? 

Hashing is primarily used for: 

  • Change detection.  
  • Deduplication.  
  • Fast data comparison.  
  • Incremental loading.  

Comparing hash values enables ETL processes to efficiently identify changed records without comparing every column individually. 

26. How do you test CDC (Change Data Capture)? 

CDC testing verifies that only newly inserted, updated, or deleted records are processed during incremental ETL loads. 

The tester confirms that: 

  • New records are inserted.  
  • Updated records are refreshed.  
  • Deleted records are handled according to business rules.  
  • Unchanged records are not processed again.  

This improves ETL efficiency while maintaining data consistency. 

27. Scenario: Negative amount appears in report. What do you test? 

The tester first validates whether negative values are permitted according to the business rules. 

The investigation includes: 

  • Source data verification.  
  • Transformation logic review.  
  • Calculation validation.  
  • Business rule confirmation.  
  • Report reconciliation.  

If negative values are not expected, the issue may indicate an ETL transformation or calculation defect. 

28. What is referential integrity testing? 

Referential integrity testing verifies that every foreign key in the fact table has a matching primary key in the related dimension table. 

This prevents orphan records and ensures consistent relationships between fact and dimension tables. 

29. Scenario: Data type mismatch during load. What do you validate? 

When data type mismatches occur, the tester validates: 

  • Data type casting.  
  • Field length.  
  • Numeric precision.  
  • Numeric scale.  
  • Data truncation rules.  
  • Character encoding.  

Proper validation ensures that data is loaded accurately without conversion errors or data loss. 

30. How do you test date transformations? 

Date transformation testing verifies that date and time values are correctly converted during ETL processing. 

The tester checks: 

  • Time zone conversion.  
  • Timestamp accuracy.  
  • Effective dates.  
  • Calendar logic.  

This ensures consistency between the source system and the target Data Warehouse. 

31. What is soft delete testing? 

Soft delete testing verifies that records are logically deleted using a delete_flag instead of being physically removed from the database. 

The tester confirms that: 

  • Delete flags are updated correctly.  
  • Deleted records are excluded from active reports.  
  • Historical data remains available when required.  

32. Scenario: ETL job rerun creates duplicates. Why? 

Duplicate records after an ETL rerun usually indicate a failure in the ETL process’s restartability logic. 

Possible causes include: 

  • Missing duplicate checks.  
  • Incorrect incremental load logic.  
  • Improper transaction rollback.  
  • Lack of idempotent processing.  

The tester verifies that rerunning the ETL job does not create duplicate data. 

33. What is schema drift? 

Schema drift refers to changes in the source database structure that affect ETL processing. 

Examples include: 

  • New columns.  
  • Removed columns.  
  • Renamed columns.  
  • Data type changes.  
  • Constraint modifications.  

ETL testing ensures that such changes do not break existing data pipelines. 

34. How do you validate BI reports? 

BI report validation involves reconciling report results with the corresponding Data Warehouse tables. 

The tester compares: 

  • Record counts.  
  • Totals.  
  • Aggregations.  
  • Filters.  
  • Business calculations.  

This ensures that reports accurately represent the data stored in the Data Warehouse. 

35. What is data masking testing? 

Data masking testing verifies that sensitive information, especially Personally Identifiable Information (PII), is properly obfuscated before being displayed in non-production environments. 

Typical fields include: 

  • Customer names.  
  • Phone numbers.  
  • Email addresses.  
  • Account numbers.  
  • National identification numbers.  

The tester ensures that masked data remains usable for testing while protecting confidential information and complying with organizational security and regulatory requirements. 

 Advanced & Real-Time TCS Scenarios (36–55) 

36. Scenario: ETL job misses SLA. What do you analyze? 

When an ETL job fails to complete within its Service Level Agreement (SLA), the first step is to identify the performance bottleneck in the ETL process. 

The analysis typically includes: 

  • Reviewing SQL query execution plans.  
  • Checking whether indexes are being used effectively.  
  • Verifying partitioning strategies for large tables.  
  • Identifying full table scans or inefficient joins.  
  • Monitoring CPU, memory, and I/O utilization.  
  • Reviewing database statistics.  
  • Checking for blocking sessions or long-running queries.  

The objective is to determine why the ETL process exceeded the expected execution time and identify opportunities to improve overall performance. 

37. How do you test ETL performance tuning? 

ETL performance tuning is tested by validating that SQL queries and ETL workflows execute efficiently after optimization. 

The validation focuses on: 

  • Join strategy optimization.  
  • SQL execution plans.  
  • Index utilization.  
  • Partition pruning.  
  • Query execution time.  
  • Resource consumption.  
  • Reduced I/O operations.  

The tester compares performance metrics before and after optimization to ensure that tuning improves execution without affecting data accuracy. 

38. Scenario: Many-to-many join inflates data. What test? 

A many-to-many join can unintentionally multiply records, resulting in duplicate or inflated data in the target tables. 

To detect this issue, perform join cardinality validation by: 

  • Comparing record counts before and after the join.  
  • Checking duplicate business keys.  
  • Validating join conditions.  
  • Reviewing primary key and foreign key relationships.  
  • Identifying unexpected row multiplication.  

This validation helps confirm that the join logic produces the expected number of records. 

39. How do you validate window functions? 

Window functions are validated by comparing the SQL output with the expected business rules and analytical requirements. 

The tester verifies functions such as: 

  • ROW_NUMBER()  
  • RANK()  
  • DENSE_RANK()  
  • LAG()  
  • LEAD()  

Validation includes: 

  • Correct ranking.  
  • Proper partitioning.  
  • Expected ordering.  
  • Accurate deduplication.  
  • Compliance with business logic.  

This ensures that ranking and deduplication logic is implemented correctly. 

40. Scenario: Late data impacts aggregates. What do you do? 

When late-arriving data changes previously calculated totals, the affected aggregate values must be recalculated. 

The tester performs the following validations: 

  • Identify the impacted partitions.  
  • Reload or recalculate aggregate tables.  
  • Compare updated totals with the source data.  
  • Validate summary tables.  
  • Reconcile downstream reports.  

This ensures that business reports remain accurate even after delayed records are processed. 

41. How do you test audit/control tables? 

Audit and control tables track ETL execution and provide information about every batch processed. 

The tester validates: 

  • Batch status.  
  • Batch IDs.  
  • Source row counts.  
  • Target row counts.  
  • Success and failure indicators.  
  • Load timestamps.  
  • Error counts.  

These validations confirm that ETL jobs completed successfully and processed the expected volume of data. 

42. Scenario: Report mismatch with DW. First step? 

If a Business Intelligence (BI) report does not match the Data Warehouse, the first step is to verify the Source-to-Target (S2T) mapping and aggregation logic. 

The tester checks: 

  • S2T mapping document.  
  • Transformation rules.  
  • Aggregation logic.  
  • Filter conditions.  
  • Join conditions.  
  • Calculated fields.  

This helps determine whether the discrepancy is caused by incorrect ETL transformations or reporting logic. 

43. How do you test ETL rollback? 

ETL rollback testing verifies that partial or failed data loads are completely reversed when an ETL process fails. 

Validation includes ensuring that: 

  • Partial loads are removed.  
  • Rollback transactions complete successfully.  
  • Duplicate records are not introduced.  
  • Audit tables reflect the rollback.  
  • Data consistency is maintained after recovery.  

Proper rollback testing ensures that failed ETL executions do not leave inconsistent data in the target system. 

44. Scenario: Parallel jobs cause deadlocks. What test? 

When multiple ETL jobs execute simultaneously, deadlocks can occur due to competing database transactions. 

The tester performs concurrency and locking validation by reviewing: 

  • Locking behavior.  
  • Transaction isolation levels.  
  • Blocking sessions.  
  • Table access order.  
  • Long-running transactions.  
  • Database deadlock logs.  

This analysis helps identify the cause of the deadlock and supports improvements in ETL job scheduling or transaction management. 

45. How do you test historical reloads? 

Historical reload testing verifies that historical data is reprocessed without creating duplicates or losing historical information. 

The tester validates: 

  • Duplicate records.  
  • Slowly Changing Dimension (SCD) handling.  
  • Effective dates.  
  • Historical record preservation.  
  • Current flag accuracy.  

This ensures that historical reporting remains accurate after data reloads. 

46. Scenario: Source file arrives late. What to test? 

When a source file arrives later than expected, the tester validates that the ETL process can still process the delayed data correctly. 

Validation includes: 

  • Dependency checks.  
  • ETL rerun logic.  
  • Incremental load validation.  
  • Missing data verification.  
  • Batch processing status.  
  • Report reconciliation.  

This ensures that delayed files do not result in missing or duplicate records. 

47. How do you validate checksum/hash totals? 

Checksum or hash validation compares source and target hash values to verify data integrity during ETL processing. 

The tester checks: 

  • Source hash totals.  
  • Target hash totals.  
  • Mismatched records.  
  • Change detection logic.  
  • Duplicate identification.  

Hash validation provides an efficient way to compare large datasets without checking every individual column. 

48. Scenario: Currency conversion mismatch. What to validate? 

When currency conversion results are incorrect, the tester validates both the reference data and the transformation logic. 

The validation includes: 

  • Exchange rate tables.  
  • Effective exchange dates.  
  • Currency codes.  
  • Join conditions.  
  • Conversion formulas.  
  • Business calculation rules.  

These checks help ensure that financial calculations are accurate and comply with business requirements. 

49. How do you test cloud ETL pipelines? 

Cloud ETL pipeline testing validates that cloud-based data integration processes are accurate, scalable, and cost-efficient. 

The tester evaluates: 

  • Data validation.  
  • Scalability.  
  • Performance.  
  • Cost optimization.  
  • SQL transformation accuracy.  
  • Incremental load behavior.  
  • Parallel processing.  
  • Resource utilization.  

The goal is to ensure that cloud ETL pipelines perform efficiently while maintaining data quality. 

50. Scenario: NULLs after LEFT JOIN. Why? 

NULL values returned after a LEFT JOIN usually indicate that matching records do not exist in the joined table. 

The most common cause is missing dimension records. 

The tester investigates: 

  • Join conditions.  
  • Foreign key values.  
  • Missing dimension data.  
  • Lookup failures.  
  • Business key mismatches.  

This helps determine whether the NULL values are expected or indicate a data quality issue. 

51. How do you test archival logic? 

Archival testing verifies that older records are correctly moved from active tables to historical or archive tables. 

Validation includes: 

  • Data movement to history tables.  
  • Record count comparison.  
  • Historical data integrity.  
  • Archive table consistency.  
  • Retention policy compliance.  

This ensures that archived data remains complete and accessible for reporting or regulatory purposes. 

52. Scenario: Incorrect SCD expiry date. Root cause? 

An incorrect Slowly Changing Dimension (SCD) expiry date is commonly caused by an effective date logic issue. 

Possible reasons include: 

  • Incorrect transformation logic.  
  • Invalid business rules.  
  • Wrong system date usage.  
  • Incorrect sequencing of updates.  
  • Improper handling of current_flag.  

The tester validates effective start dates, expiry dates, and current flag values to ensure historical records are maintained correctly. 

53. How do you validate metadata tables? 

Metadata tables contain information about ETL execution rather than business data. 

The tester validates: 

  • Batch IDs.  
  • Load timestamps.  
  • Source row counts.  
  • Target row counts.  
  • Job execution status.  
  • Error messages.  
  • Execution duration.  

Accurate metadata supports ETL monitoring, auditing, troubleshooting, and operational reporting. 

54. Scenario: Unexpected data spike. What test? 

A sudden increase in data volume may indicate duplicate processing, unexpected business activity, or ETL defects. 

The tester performs: 

  • Source anomaly validation.  
  • Business rule validation.  
  • Historical trend comparison.  
  • Duplicate record checks.  
  • ETL filter validation.  
  • Load volume analysis.  

These validations help determine whether the spike is expected or caused by an issue in the ETL process. 

55. How do you perform end-to-end ETL testing in TCS projects? 

End-to-end ETL testing validates the complete flow of data across all stages of the ETL process. 

The validation follows the sequence: 

Source → Data Warehouse (DW) → Report reconciliation 

The tester performs: 

  • Source data validation.  
  • Staging data verification.  
  • Transformation validation.  
  • Fact and dimension table validation.  
  • Record count reconciliation.  
  • Business rule verification.  
  • Audit and metadata verification.  

This comprehensive validation ensures that data flows accurately from the source systems through the Data Warehouse and is correctly reflected in business reports, dashboards, and regulatory outputs. 

Real SQL Query Examples for ETL Validation 

Sample Tables 

Assume the following tables are part of an ETL testing project. 

Source Table 

src_orders( 
   order_id, 
   cust_id, 
   amount, 
   order_dt 
) 

This source table stores customer order information extracted from operational systems before ETL processing begins. 

Dimension Table 

dim_customer( 
   cust_sk, 
   cust_id, 
   current_flag 
) 

This dimension table stores customer details, including surrogate keys and Slowly Changing Dimension (SCD) information used for historical tracking. 

Fact Table 

fact_sales( 
   order_id, 
   cust_sk, 
   amount, 
   load_dt 
) 

This fact table contains transactional sales data that has been transformed and loaded into the Data Warehouse. 

1. Record Count Validation 

Record count validation ensures that all expected records are successfully loaded from the source system into the target Data Warehouse. 

SQL Query 

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

Purpose 

This validation is used to: 

  • Compare the total number of records between the source and target tables.  
  • Identify missing or extra records after the ETL process.  
  • Verify that data extraction and loading completed successfully.  

If the record counts do not match, the tester investigates rejected records, transformation filters, incremental load logic, or ETL job failures. 

2. JOIN Validation 

JOIN validation ensures that every record in the fact table has a corresponding record in the related dimension table. 

SQL Query 

SELECT COUNT(*) AS missing_dim 
FROM fact_sales f 
LEFT JOIN dim_customer d 
ON f.cust_sk = d.cust_sk 
WHERE d.cust_sk IS NULL; 

Purpose 

This validation helps identify: 

  • Missing dimension records.  
  • Broken foreign key relationships.  
  • Orphan fact records.  
  • Lookup failures during ETL processing.  

If the query returns any records, it indicates that some fact table entries do not have matching customer records in the dimension table and require further investigation. 

3. GROUP BY Aggregation Validation 

Aggregation validation verifies that summarized values generated during ETL processing are accurate. 

SQL Query 

SELECT cust_id, 
      SUM(amount) 
FROM src_orders 
GROUP BY cust_id; 

Purpose 

This validation ensures that: 

  • Aggregation logic is implemented correctly.  
  • Customer-level totals are accurate.  
  • Business calculations match the source data.  

The aggregated results are compared with the target tables or Business Intelligence (BI) reports to confirm correctness. 

4. Window Function – Deduplication 

Window functions are commonly used in ETL testing to identify duplicate records and retain only the required version of each record. 

SQL Query 

SELECT * 
FROM ( 
   SELECT order_id, 
          ROW_NUMBER() OVER ( 
              PARTITION BY order_id 
              ORDER BY order_dt DESC 
          ) rn 
   FROM src_orders 
) t 
WHERE rn = 1; 

Purpose 

This query is used to: 

  • Detect duplicate records.  
  • Retain the latest record for each order_id.  
  • Validate deduplication logic.  
  • Support Slowly Changing Dimension (SCD) implementations.  
  • Improve overall data quality.  

Window functions such as ROW_NUMBER() are widely used for ranking, deduplication, and change detection in ETL testing. 

5. Performance Tuning Validation 

Performance tuning validation ensures that SQL queries execute efficiently, especially when processing large volumes of enterprise data. 

SQL Query 

EXPLAIN 
SELECT * 
FROM fact_sales 
WHERE load_dt >= CURRENT_DATE – 1; 

Purpose 

The EXPLAIN statement displays the query execution plan, allowing testers to analyze how the database processes the SQL statement. 

The execution plan helps verify: 

  • Index utilization.  
  • Full table scans.  
  • Join strategies.  
  • Estimated query cost.  
  • Filter efficiency.  
  • Query execution order.  

Reviewing execution plans helps identify performance bottlenecks and optimize ETL processing. 

ETL Tools Commonly Asked in TCS Interviews 

Although TCS ETL interviews emphasize SQL and ETL concepts, candidates are also expected to have familiarity with widely used ETL tools. 

Some commonly discussed ETL tools include: 

  • Informatica – A leading enterprise ETL platform used for extracting, transforming, and loading data from multiple sources into Data Warehouses.  
  • Microsoft SQL Server Integration Services (SSIS) – Microsoft’s ETL solution used for data integration, migration, and workflow automation within SQL Server environments.  
  • Pentaho – An open-source ETL and Business Intelligence platform used for data integration, reporting, and analytics.  
  • Talend – A cloud-based ETL and data integration platform that supports databases, APIs, cloud services, and big data ecosystems.  

While tool knowledge is beneficial, interviewers generally place greater emphasis on SQL proficiency, ETL concepts, and real-world data validation experience. 

ETL Defect Examples + Test Case Sample 

Defect Example 

Issue 

Duplicate records are found in the fact table after the ETL process completes. 

Root Cause 

The duplication is caused by incorrect join logic during the transformation stage, resulting in multiple source records being loaded into the target. 

Fix 

The issue can be resolved by: 

  • Correcting the join keys.  
  • Applying proper join conditions.  
  • Adding deduplication logic using window functions or hashing.  
  • Revalidating the ETL transformation.  

After implementing the fix, the ETL process should be rerun to ensure that duplicate records are eliminated. 

Sample Test Case 

Test Case 

Validate SCD Type 2 update 

Test Objective 

Verify that historical changes to dimension records are handled correctly according to SCD Type 2 rules. 

Validation Steps 

  • Modify an existing customer record in the source system.  
  • Execute the ETL process.  
  • Verify that the previous record is expired.  
  • Confirm that a new record is inserted.  
  • Validate the effective start and end dates.  
  • Check the current_flag values.  

Expected Result 

The ETL process should produce the following outcome: 

  • Old record expired.  
  • New record inserted with current_flag = ‘Y’.  
  • Previous record updated with current_flag = ‘N’.  
  • Effective dates maintained correctly.  
  • Historical data preserved without duplication.  

TCS ETL Testing – Quick Revision Sheet 

Before attending a TCS ETL Testing interview, review the following key topics: 

  • Validate source and target record counts.  
  • Compare aggregate values such as sums, averages, and counts.  
  • Check NULL values in mandatory columns.  
  • Detect duplicate records using SQL queries.  
  • Review Source-to-Target (S2T) mappings line by line.  
  • Test Slowly Changing Dimensions (SCD Type 1 and SCD Type 2).  
  • Validate audit fields such as load_date, batch_id, and timestamps.  
  • Verify hashing and checksum logic for change detection.  
  • Use SQL clauses such as JOIN, GROUP BY, HAVING, and window functions.  
  • Validate referential integrity between fact and dimension tables.  
  • Review SQL execution plans for performance optimization.  
  • Always reconcile source data, target Data Warehouse data, and final reports.  

FAQs (Snippet-Friendly) 

Q1. What are TCS ETL testing interview questions? 

TCS ETL testing interview questions focus on real-time ETL scenarios, SQL-based data validation, data reconciliation, transformation logic, and enterprise-grade data quality. Candidates are typically assessed on their ability to troubleshoot ETL issues, validate business rules, perform Data Warehouse testing, and solve scenario-based problems using SQL. 

Q2. Which SQL is important for TCS ETL roles? 

SQL plays a critical role in TCS ETL projects. The most important SQL concepts include: 

  • JOIN operations.  
  • GROUP BY.  
  • HAVING.  
  • Window functions (ROW_NUMBER(), RANK(), DENSE_RANK()).  
  • Aggregate functions (SUM, COUNT, AVG).  
  • Subqueries.  
  • Common Table Expressions (CTEs).  
  • Performance tuning using EXPLAIN or EXPLAIN PLAN.  

Strong SQL skills enable ETL testers to validate data movement, transformations, and reporting accuracy efficiently. 

Q3. How do you test SCD2 in real projects? 

SCD Type 2 testing is performed by validating that historical changes are maintained correctly in the dimension table. 

The tester verifies that: 

  • Multiple versions of the same business key exist when changes occur.  
  • Effective start and end dates are populated correctly.  
  • Only one record has current_flag = ‘Y’.  
  • Previous records are expired with current_flag = ‘N’.  
  • Historical information is preserved without overwriting existing records.  

These validations ensure that historical reporting remains accurate while maintaining the latest active version of each dimension record. 

Leave a Comment

Your email address will not be published. Required fields are marked *

Copyright © 2026