SQL ETL Testing Interview Questions

Leave a Comment / Uncategorized / By

What Is ETL Testing? (Definition + Real-World Example) 

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

The primary objective of ETL testing is to verify that data extracted from multiple source systems is correctly transformed according to business rules and loaded into the target data warehouse without data loss, duplication, corruption, or performance issues. Since organizations rely on data warehouses for business intelligence, reporting, analytics, and decision-making, even a small data inconsistency can lead to incorrect reports and poor business decisions. 

Unlike application testing, which validates user interfaces and functionality, ETL testing focuses entirely on validating data movement, data quality, and database integrity throughout the ETL pipeline. 

Real-World Example 

Consider a banking project where millions of customer transactions are processed every day. 

The transaction data is first extracted from multiple OLTP (Online Transaction Processing) systems. During the transformation phase, several business rules are applied before the data is loaded into the enterprise data warehouse. 

These transformation rules may include: 

  • Currency conversion for international transactions. 
  • Removal of duplicate transaction records. 
  • Handling Slowly Changing Dimensions (SCD) for customer information. 
  • Validating mandatory fields. 
  • Applying business calculations such as fees, taxes, or interest. 
  • Aggregating transaction data for reporting purposes. 

Once the transformed data is loaded into fact tables and dimension tables, an ETL tester validates whether the entire ETL process has worked correctly. 

An ETL tester uses SQL ETL testing interview questions–level queries to perform validations such as: 

  • Compare source and target record counts. 
  • Validate transformations and aggregations. 
  • Verify duplicate removal. 
  • Check data integrity between fact and dimension tables. 
  • Validate Slowly Changing Dimension (SCD) implementation. 
  • Ensure reports match Data Warehouse data. 
  • Confirm business rules have been correctly applied. 
  • Verify that no data is lost during extraction or loading. 

These SQL validations help ensure that business users receive accurate reports and dashboards. 

Why SQL Skills Are Mandatory for ETL Testing Roles 

SQL is the most important skill for an ETL tester because almost every validation performed during ETL testing is database-driven. 

ETL testers spend a significant amount of their time writing SQL queries to compare source and target data, validate business rules, identify missing records, detect duplicates, and verify transformations. 

Without strong SQL knowledge, it becomes difficult to validate whether the ETL process has successfully moved and transformed the data. 

Some of the common SQL validation activities include: 

  • Comparing record counts between source and target tables. 
  • Validating column-level mappings. 
  • Checking NULL values. 
  • Detecting duplicate records. 
  • Validating joins between fact and dimension tables. 
  • Verifying aggregation logic. 
  • Testing Slowly Changing Dimensions (SCD). 
  • Comparing source reports with Data Warehouse reports. 
  • Validating incremental and full data loads. 

For this reason, SQL-based interview questions are among the most frequently asked questions in ETL testing interviews. 

Data Warehouse Flow 

A typical ETL architecture follows the following data flow: 

Source → Staging → Transform → Load → Reporting 

Each stage plays an important role in ensuring high-quality data reaches business users. 

Source 

The source layer contains the original operational data from where information is extracted. 

Common data sources include: 

  • OLTP databases 
  • Oracle 
  • SQL Server 
  • MySQL 
  • PostgreSQL 
  • Flat files (CSV, Excel, TXT) 
  • XML files 
  • JSON files 
  • SAP systems 
  • Salesforce 
  • Mainframe systems 
  • Cloud databases 

The source systems are optimized for transaction processing rather than analytics. 

Staging 

The staging area is a temporary storage location where raw extracted data is stored before transformation begins. 

The purpose of the staging area includes: 

  • Holding extracted data temporarily. 
  • Combining data from multiple source systems. 
  • Performing preliminary validations. 
  • Removing corrupt records. 
  • Improving ETL performance. 
  • Reducing the load on production systems. 

Since staging tables contain raw data, business users generally do not access them directly. 

Transformation 

The transformation layer applies all business rules to convert raw data into meaningful business information. 

Typical transformation activities include: 

  • Data cleansing. 
  • Duplicate removal. 
  • Data standardization. 
  • Currency conversion. 
  • Data type conversion. 
  • Aggregations. 
  • Filtering invalid records. 
  • Business calculations. 
  • Surrogate key generation. 
  • Slowly Changing Dimension (SCD) implementation. 
  • Lookup transformations. 
  • Sorting and merging datasets. 

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

Load 

Once the data has been transformed, it is loaded into the Data Warehouse. 

Data is typically loaded into: 

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

The loading process may be: 

  • Full Load 
  • Incremental Load 
  • Delta Load 

ETL testers validate whether all records have been loaded successfully without duplication, missing records, or data corruption. 

Reporting 

After successful loading, business intelligence tools access the Data Warehouse to generate reports and dashboards. 

Common reporting tools include: 

  • Power BI 
  • Tableau 
  • Qlik Sense 
  • SAP BusinessObjects 
  • IBM Cognos 
  • MicroStrategy 

Business users rely on these reports for: 

  • Financial reporting. 
  • Sales analysis. 
  • Customer analytics. 
  • Risk management. 
  • Regulatory compliance. 
  • Executive dashboards. 
  • Operational reporting. 

The accuracy of these reports depends entirely on successful ETL testing. 

SQL ETL Testing Interview Questions & Answers 

Basic → Advanced | SQL-Driven & Scenario-Based 

SQL-based ETL testing interview questions are generally categorized into multiple levels based on complexity and real-world project experience. 

Basic Level 

These questions evaluate the candidate’s understanding of ETL fundamentals and basic SQL validation techniques. 

Topics generally include: 

  • ETL process overview. 
  • Data Warehouse concepts. 
  • Source and target validation. 
  • Record count validation. 
  • NULL validation. 
  • Duplicate validation. 
  • Primary and foreign keys. 
  • Fact and dimension tables. 

Intermediate Level 

These questions focus on practical ETL testing performed in real projects. 

Topics generally include: 

  • Data mapping validation. 
  • Join validation. 
  • Transformation testing. 
  • Aggregate validation. 
  • Lookup validation. 
  • Incremental load testing. 
  • Full load testing. 
  • Reconciliation testing. 
  • Data integrity validation. 
  • Slowly Changing Dimension (SCD) testing. 

Advanced Level 

Advanced interview questions assess real-time project experience and problem-solving abilities. 

Topics generally include: 

  • Complex SQL queries. 
  • Window functions. 
  • Performance tuning. 
  • Query optimization. 
  • Large-volume data validation. 
  • Partitioned tables. 
  • Production defect analysis. 
  • Data reconciliation across multiple systems. 

SQL-Driven Interview Questions 

SQL-driven interview questions require candidates to write or explain SQL queries used for ETL validation. 

These questions commonly cover: 

  • Record count comparison. 
  • Source-to-target data validation. 
  • Duplicate detection. 
  • Missing record identification. 
  • Aggregation validation. 
  • Join validation. 
  • Business rule verification. 
  • Data quality checks. 
  • Date and timestamp validation. 
  • Fact and dimension table validation. 

Interviewers assess not only the correctness of SQL syntax but also the candidate’s ability to interpret query results and identify data issues. 

Scenario-Based Interview Questions 

Scenario-based questions evaluate how candidates would handle real-world ETL testing challenges encountered in production environments. 

Typical scenarios include: 

  • Source and target record counts do not match. 
  • Duplicate records appear in the fact table after loading. 
  • Business reports display incorrect totals. 
  • Slowly Changing Dimension (SCD) updates are not reflected correctly. 
  • Incremental load misses newly inserted records. 
  • Lookup transformation fails to map customer data. 
  • Data warehouse reports differ from source system reports. 
  • ETL jobs fail due to invalid data formats. 
  • Large-volume data loads exceed performance expectations. 
  • Production defects require immediate root cause analysis. 

Candidates are expected to explain: 

  • How they would investigate the issue. 
  • Which SQL queries they would execute. 
  • How they would identify the root cause. 
  • The validation steps they would perform. 
  • The expected outcome after the issue is resolved. 

These scenario-based discussions help interviewers assess practical ETL testing experience, SQL proficiency, analytical thinking, and the ability to troubleshoot real-world data validation problems. 

 Basic SQL ETL Testing Questions (1–15)  

1. What is SQL ETL testing? 

SQL ETL testing is the process of using SQL queries to validate the accuracy, completeness, consistency, and correctness of data during the Extract, Transform, and Load (ETL) process. 

An ETL tester writes SQL queries to compare source and target data, verify business rules, validate transformations, detect duplicate or missing records, and ensure that data is correctly loaded into the Data Warehouse. SQL forms the foundation of ETL testing because almost every validation activity is performed at the database level. 

2. Why is SQL important for ETL testers? 

SQL is important because it allows ETL testers to validate every stage of the ETL process. 

Using SQL, testers can: 

  • Validate record counts.  
  • Compare source and target data.  
  • Verify joins between tables.  
  • Validate aggregations and calculations.  
  • Detect duplicate records.  
  • Identify missing records.  
  • Check NULL values.  
  • Validate business rules.  
  • Ensure overall data quality.  

Without strong SQL skills, it is difficult to perform accurate ETL validations or troubleshoot production data issues. 

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

Source-to-Target (S2T) mapping is a document that defines how each source column is mapped to the corresponding target column in the Data Warehouse. 

The S2T mapping document includes: 

  • Source table and column names.  
  • Target table and column names.  
  • Data types.  
  • Transformation rules.  
  • Lookup logic.  
  • Business rules.  
  • Default values.  
  • Data validation requirements.  

ETL testers use this document as the primary reference while validating ETL transformations. 

4. How do you validate record count using SQL? 

Record count validation is performed by comparing the total number of records in the source table with the target table using the COUNT(*) function. 

This validation helps ensure that: 

  • No records are missing.  
  • No duplicate records are loaded.  
  • The ETL process has transferred the expected number of records.  

If the counts do not match, the tester investigates rejected records, filter conditions, or transformation logic to identify the root cause. 

5. What is staging area testing? 

Staging area testing ensures that the data extracted from the source systems is copied into the staging area exactly as received, without any unintended modifications. 

The validation typically includes: 

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

Since transformations are generally not applied in the staging layer, the staging data should match the source data exactly. 

6. What are audit fields? 

Audit fields are metadata columns that help track ETL execution 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 ETL batch loaded the data.  
  • Who created or updated the record.  
  • Whether the correct batch processed the data.  

Audit fields are also useful for troubleshooting ETL failures and maintaining data lineage. 

7. What is data reconciliation? 

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

Validation typically includes: 

  • 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 accurately. 

8. Difference between fact and dimension tables? 

Fact tables store measurable business data, while dimension tables store descriptive information used to analyze those measurements. 

Fact tables typically contain: 

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

Dimension tables typically contain: 

  • Customer details.  
  • Product information.  
  • Employee details.  
  • Store information.  
  • Date and time attributes.  

Fact tables are linked to dimension tables using foreign keys to support reporting and analytical queries. 

9. What is full load vs incremental load? 

A full load reloads all available data from the source into the target every time the ETL process runs. 

An incremental load transfers only the new or modified (delta) records since the last successful ETL execution. 

Full loads are generally used during initial data migration, while incremental loads are preferred for daily or periodic ETL jobs because they improve performance and reduce processing time. 

10. What is reject data testing? 

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

The tester verifies: 

  • Rejected row count.  
  • Error reason.  
  • Validation rule that caused rejection.  
  • Data correction process.  
  • Logging accuracy.  

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

11. What is data quality testing? 

Data quality testing verifies that the loaded data is accurate, complete, consistent, and reliable. 

Typical validations include: 

  • Accuracy checks.  
  • Completeness validation.  
  • Consistency verification.  
  • Duplicate detection.  
  • NULL validation.  
  • Format validation.  
  • Business rule verification.  
  • Range validation.  

High-quality data is essential for reliable reporting and decision-making. 

12. What is mapping validation? 

Mapping validation ensures that the SQL transformation logic implemented in the ETL process matches the Source-to-Target (S2T) mapping document. 

During mapping validation, testers verify: 

  • Correct source-to-target column mapping.  
  • Business rule implementation.  
  • Transformation logic.  
  • Data type conversion.  
  • Default value assignment.  
  • Lookup logic.  

This helps ensure that the ETL process accurately follows the documented requirements. 

13. 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 validation includes: 

  • Source data verification.  
  • Staging validation.  
  • Transformation validation.  
  • Data loading validation.  
  • Report validation.  

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

14. What is data lineage? 

Data lineage is the process of tracking the complete journey of data from its source system through every ETL stage until it appears in reports or dashboards. 

Data lineage helps organizations understand: 

  • Where the data originated.  
  • How it was transformed.  
  • Which ETL jobs processed it.  
  • Which tables stored it.  
  • How it reached the final report.  

It plays a crucial role in data governance, compliance, auditing, and troubleshooting. 

15. What SQL clauses are most used in ETL testing? 

The most commonly used SQL clauses and functions in ETL testing include: 

  • JOIN  
  • GROUP BY  
  • HAVING  
  • WHERE  
  • ORDER BY  
  • DISTINCT  
  • CASE  
  • Window functions (ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD())  

These clauses are frequently used for validating joins, aggregations, duplicate records, ranking, business rules, and overall data quality. 

Intermediate SQL ETL Testing Questions (16–35) 

16. Scenario: Source has 1M rows, target has 980K. What SQL do you run? 

The first step is to compare the total record counts between the source and target tables to confirm the discrepancy. Next, validate reject tables to identify records that failed during processing and review any filter conditions applied during transformation. Finally, compare the missing records between the source and target to determine whether they were rejected, filtered intentionally, or lost due to an ETL issue. 

17. How do you test NULL handling using SQL? 

NULL handling is tested by checking mandatory fields using WHERE column IS NULL. 

The validation ensures that: 

  • Mandatory columns do not contain NULL values.  
  • Business rules regarding NULL handling are correctly implemented.  
  • Default values are assigned where required.  
  • Invalid NULL records are rejected or logged appropriately.  

18. What is SCD Type 1? 

Slowly Changing Dimension (SCD) Type 1 updates dimension records by overwriting old values with new values. 

It does not maintain historical data. Whenever a change occurs, the existing record is updated, and the previous value is permanently lost. 

This approach is suitable when historical tracking is not required. 

19. What is SCD Type 2? 

SCD Type 2 preserves historical data by creating a new record whenever a dimension value changes. 

History is maintained using: 

  • Effective start date.  
  • Effective end date.  
  • Current flag.  
  • Version number (optional).  

This allows organizations to track changes over time without losing previous information. 

20. How do you validate SCD2 using SQL? 

SCD Type 2 validation involves checking that multiple records exist for the same business key whenever changes occur. 

The tester verifies: 

  • Effective start and end dates.  
  • Current flag values.  
  • Historical record preservation.  
  • Only one active record exists for each business key.  

This ensures that historical changes are stored correctly. 

21. What is surrogate key testing? 

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

Validation includes: 

  • Uniqueness.  
  • Non-NULL values.  
  • Correct sequencing (if applicable).  
  • Proper mapping to business keys.  

Surrogate keys help maintain relationships between fact and dimension tables. 

22. What is a late-arriving dimension scenario? 

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

In such cases, ETL processes often assign a dummy or unknown surrogate key until the actual dimension record becomes available. 

Testing ensures that: 

  • The dummy key is assigned correctly.  
  • Facts are not rejected unnecessarily.  
  • The dimension reference is updated when the actual record arrives.  

23. Scenario: Duplicate rows in target. What SQL do you use? 

Duplicate records are identified using GROUP BY along with HAVING COUNT(*) > 1. 

After identifying duplicates, the tester investigates: 

  • Join conditions.  
  • Missing primary key constraints.  
  • Incorrect transformation logic.  
  • Multiple source records mapping to the same target record.  

24. How do you test aggregation logic? 

Aggregation testing verifies that summarized values in the target match calculations performed on the source data. 

Typical validations include comparing: 

  • SUM values.  
  • COUNT values.  
  • AVG values.  
  • MIN values.  
  • MAX values.  

Any mismatch indicates an issue with transformation logic or aggregation rules. 

25. What is hashing used for in ETL? 

Hashing is primarily used for: 

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

By comparing hash values instead of every column, ETL processes can efficiently identify records that have changed since the previous load. 

26. How do you test CDC using SQL? 

Change Data Capture (CDC) testing verifies that only newly inserted, updated, or deleted records are processed during an incremental load. 

The tester validates: 

  • New records are loaded.  
  • Updated records reflect the latest values.  
  • Deleted records are handled according to business rules.  
  • Unchanged records are not reprocessed unnecessarily.  

27. Scenario: Negative amount in report. What SQL do you run? 

The tester filters records with negative values and validates them against the applicable business rules. 

The investigation includes: 

  • Source data verification.  
  • Transformation logic review.  
  • Business rule validation.  
  • Currency conversion checks.  
  • Calculation verification.  

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

28. What is referential integrity testing? 

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

This validation prevents orphan records and maintains consistency between fact and dimension tables. 

29. How do you test data type mismatches? 

Data type validation verifies that source and target columns have compatible data types. 

The tester checks: 

  • Precision.  
  • Scale.  
  • Length.  
  • Truncation.  
  • Numeric conversions.  
  • Character encoding.  

This prevents data loss and conversion errors during loading. 

30. How do you validate date transformations? 

Date transformation validation compares source and target date values after transformation. 

Typical checks include: 

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

This ensures that date-related business rules are correctly implemented. 

31. What is soft delete testing? 

Soft delete testing verifies records that are logically deleted using indicators such as a delete_flag rather than being physically removed from the database. 

The tester ensures that: 

  • Delete flags are updated correctly.  
  • Deleted records are excluded from reports where applicable.  
  • Historical data remains available if required.  

32. What is schema drift? 

Schema drift refers to changes in the source database schema that impact ETL processes. 

Examples include: 

  • New columns.  
  • Removed columns.  
  • Renamed columns.  
  • Data type changes.  
  • Modified constraints.  

Testing ensures that ETL jobs continue to function correctly after such changes. 

33. How do you validate BI reports using SQL? 

BI report validation involves comparing report results with the corresponding Data Warehouse tables using SQL queries. 

The tester verifies: 

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

This ensures that reports accurately represent the underlying warehouse data. 

34. What is partition testing? 

Partition testing verifies that data is stored in the correct database partition based on the partitioning strategy, such as date, region, or business key. 

Proper partition placement improves query performance and simplifies data management. 

35. What is incremental load validation? 

Incremental load validation ensures that only new or modified (delta) records are loaded during each ETL execution. 

The tester verifies: 

  • No duplicate records are loaded.  
  • Existing records are updated correctly.  
  • Unchanged records are not reprocessed.  
  • The delta logic works as expected.  

This approach improves ETL efficiency while maintaining data accuracy. 

 Advanced SQL ETL Testing Scenarios (36–55)  

36. Scenario: ETL job misses SLA. How do you analyze SQL performance? 

When an ETL job misses its Service Level Agreement (SLA), the first step is to identify which SQL queries or transformations are taking the most time. Performance analysis focuses on locating bottlenecks that delay the ETL process. 

An ETL tester or developer should: 

  • Review SQL execution plans.  
  • Check whether indexes are being used efficiently.  
  • Identify full table scans.  
  • Analyze expensive joins and aggregations.  
  • Check for blocking sessions or locks.  
  • Review database statistics.  
  • Monitor CPU, memory, and I/O usage.  
  • Identify long-running SQL statements.  

The goal is to determine why the ETL job exceeded its expected execution time and optimize the SQL queries or database objects to improve performance. 

37. How do you test performance tuning using SQL? 

SQL performance tuning is tested by analyzing how efficiently a query executes before and after optimization. 

The most common approach is to use: 

  • EXPLAIN  
  • EXPLAIN PLAN  

These commands provide information about: 

  • Execution plans.  
  • Join order.  
  • Index usage.  
  • Estimated query cost.  
  • Table scans.  
  • Sort operations.  
  • Filter conditions.  

After optimization, the tester compares execution time and resource utilization to ensure that performance has improved without affecting data accuracy. 

38. Scenario: Many-to-many join inflates data. How do you detect it? 

Many-to-many joins can unintentionally multiply records, resulting in duplicate or inflated data in the target tables or reports. 

To detect this issue, perform join cardinality checks by: 

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

If the number of joined records is significantly higher than expected, it usually indicates an incorrect join condition or a many-to-many relationship that requires additional filtering or deduplication. 

39. How do you validate window functions? 

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

The tester verifies functions such as: 

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

Validation includes: 

  • Correct ranking order.  
  • Proper partitioning.  
  • Accurate ordering.  
  • Correct handling of ties.  
  • Business rule compliance.  

The output should exactly match the expected analytical calculations defined in the business requirements. 

40. Scenario: Late data affects aggregates. What SQL do you run? 

When late-arriving data impacts summary reports, the affected aggregates must be recalculated. 

The validation process includes: 

  • Identifying the impacted partitions.  
  • Recalculating aggregate values.  
  • Comparing updated totals with source data.  
  • Verifying summary tables.  
  • Validating downstream reports.  

This ensures that 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 batch processing. 

Testing includes validating: 

  • Batch IDs.  
  • Load timestamps.  
  • Source record counts.  
  • Target record counts.  
  • Success and failure status.  
  • Error counts.  
  • ETL execution duration.  

These tables help verify that each ETL batch completed successfully and processed the expected number of records. 

42. Scenario: Report mismatch with DW. First SQL check? 

If a Business Intelligence (BI) report does not match the Data Warehouse, the first SQL validation should focus on the GROUP BY logic used in the report query. 

The tester should verify: 

  • Aggregation logic.  
  • Filter conditions.  
  • Join conditions.  
  • Grouping columns.  
  • Calculated fields.  

Many reporting discrepancies are caused by incorrect aggregation or grouping logic rather than incorrect data loading. 

43. How do you test ETL rollback using SQL? 

ETL rollback testing ensures that partially loaded or failed transactions do not remain in the target database after an ETL failure. 

Validation includes checking that: 

  • Partial loads are completely removed.  
  • Rollback transactions are successful.  
  • No duplicate records remain.  
  • Audit tables reflect the rollback status.  
  • Data consistency is maintained.  

This prevents incomplete or corrupted data from appearing in reports. 

44. Scenario: Parallel jobs cause deadlocks. What should you analyze? 

When multiple ETL jobs run simultaneously, database deadlocks may occur. 

The investigation should include: 

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

Analyzing these factors helps identify why competing transactions are blocking one another and causing ETL failures. 

45. How do you test historical reloads? 

Historical reload testing validates that historical data is reloaded correctly without creating duplicate records or losing history. 

The tester verifies: 

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

This ensures that historical reporting remains consistent after data reloads. 

46. Scenario: Source file arrives late. What SQL validation do you perform? 

When a source file arrives later than expected, the tester validates whether the delayed data has been processed correctly. 

Typical validations include: 

  • Missing data identification.  
  • Incremental load verification.  
  • Batch processing validation.  
  • Record count comparison.  
  • Report reconciliation.  

This ensures that delayed records are eventually loaded without duplication or omission. 

47. How do you validate checksum/hash totals? 

Checksum or hash validation compares source and target hash values to ensure that the data has not changed unexpectedly during the ETL process. 

Validation includes: 

  • Comparing source hashes.  
  • Comparing target hashes.  
  • Identifying mismatched records.  
  • Verifying change detection logic.  
  • Confirming successful data migration.  

Hash comparisons provide a fast and efficient way to validate large datasets. 

48. Scenario: Currency conversion mismatch. What SQL test do you perform? 

When converted currency values do not match expected results, the tester validates the exchange rate transformation logic. 

This includes checking: 

  • Exchange rate joins.  
  • Effective exchange dates.  
  • Conversion formulas.  
  • Currency codes.  
  • Source exchange rate tables.  
  • Business calculation rules.  

Any mismatch usually indicates incorrect joins, outdated exchange rates, or transformation errors. 

49. How do you test cloud ETL pipelines with SQL? 

Cloud ETL pipeline testing validates both data accuracy and system performance in cloud environments. 

Testing focuses on: 

  • Data validation.  
  • SQL transformation accuracy.  
  • Scalability.  
  • Query performance.  
  • Incremental loading.  
  • Resource utilization.  
  • End-to-end reconciliation.  

The objective is to ensure that cloud-based ETL pipelines process large volumes of data efficiently and accurately. 

50. Scenario: NULLs after LEFT JOIN. Why? 

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

The most common reason is missing dimension records. 

The tester should verify: 

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

Understanding the cause of NULL values helps determine whether they are expected or indicate a data quality issue. 

51. How do you test archival logic? 

Archival testing ensures that old data is moved correctly from active tables to historical or archive tables according to business rules. 

Validation includes: 

  • Data movement verification.  
  • Record count comparison.  
  • Historical data integrity.  
  • Archive table validation.  
  • Retention policy compliance.  

This ensures that archived data remains accurate and accessible for future reporting or compliance purposes. 

52. Scenario: Incorrect SCD expiry date. What is the root cause? 

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

Possible causes include: 

  • Incorrect ETL transformation logic.  
  • Invalid business rules.  
  • Wrong system date usage.  
  • Improper update sequencing.  
  • Incorrect current flag handling.  

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

53. How do you validate metadata tables? 

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

Validation includes checking: 

  • Load timestamps.  
  • Batch IDs.  
  • Source row counts.  
  • Target row counts.  
  • ETL job status.  
  • Execution duration.  
  • Error messages.  

Accurate metadata helps monitor ETL processes, troubleshoot failures, and support auditing activities. 

54. Scenario: Unexpected data spike. What SQL test do you perform? 

A sudden increase in data volume may indicate source system issues, duplicate processing, or business changes. 

The tester performs source anomaly detection by: 

  • Comparing historical record counts.  
  • Identifying duplicate records.  
  • Reviewing recent source changes.  
  • Validating ETL filters.  
  • Comparing daily load trends.  
  • Investigating unexpected business events.  

This analysis helps determine whether the spike is expected or caused by an ETL defect. 

55. How do you perform real-time SQL ETL testing? 

Real-time SQL ETL testing validates data continuously as it flows through the ETL pipeline. 

The validation follows the complete data flow: 

Source → Target → Report reconciliation 

Typical activities include: 

  • Comparing source and target record counts.  
  • Verifying incremental data loads.  
  • Reconciling Data Warehouse tables with BI reports.  
  • Monitoring data quality.  
  • Checking audit and control tables.  
  • Confirming business rule implementation.  

Real-time ETL testing ensures that live data is processed accurately, transformed correctly, and reflected consistently in reporting systems without delays or data discrepancies. 

Real SQL Query Examples for ETL Validation 

Sample Tables 

Assume the following tables are used in an ETL testing project: 

Source Table 

src_orders( 
   order_id, 
   cust_id, 
   amount, 
   order_dt 
) 

This table stores customer order data extracted from the source system. 

Dimension Table 

dim_customer( 
   cust_sk, 
   cust_id, 
   current_flag 
) 

This dimension table stores customer information, including surrogate keys and Slowly Changing Dimension (SCD) attributes. 

Fact Table 

fact_sales( 
   order_id, 
   cust_sk, 
   amount, 
   load_dt 
) 

This fact table stores sales transactions loaded into the Data Warehouse after ETL transformations. 

1. Record Count Validation 

Record count validation ensures that all expected records have been successfully loaded from the source into the target table. 

SQL Query 

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

Purpose 

This validation helps confirm that: 

  • All source records have been loaded into the target.  
  • No records are missing during the ETL process.  
  • No unexpected duplicate records have been introduced.  

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

2. JOIN Validation 

JOIN validation checks whether every fact table record has a matching dimension record. 

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 is used to: 

  • Detect missing dimension records.  
  • Verify referential integrity.  
  • Identify orphan fact records.  
  • Ensure foreign key relationships are maintained.  

A result greater than zero indicates that some fact records do not have corresponding customer records in the dimension table and require investigation. 

3. GROUP BY Aggregation Validation 

Aggregation validation verifies that summarized values in the Data Warehouse match the source data. 

SQL Query 

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

Purpose 

This validation helps ensure that: 

  • Aggregation logic is correct.  
  • Business calculations are accurate.  
  • Customer totals match source transactions.  
  • Reporting values are reliable.  

ETL testers compare these aggregated values with the corresponding results in the target tables or BI reports. 

4. Window Function – Deduplication 

Window functions are commonly used to identify and remove duplicate records while retaining the latest or most relevant 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 helps: 

  • Identify duplicate records.  
  • Keep only the latest order for each order_id.  
  • Validate deduplication logic.  
  • Support Slowly Changing Dimension (SCD) implementations.  
  • Ensure accurate reporting by eliminating duplicate data.  

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

5. Performance Tuning Validation 

Performance validation helps determine whether SQL queries are optimized for large datasets. 

SQL Query 

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

Purpose 

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

Typical checks include: 

  • Index usage.  
  • Full table scans.  
  • Join methods.  
  • Query cost.  
  • Filter efficiency.  
  • Execution order.  

Analyzing execution plans helps improve ETL performance and reduce processing time. 

ETL Tools Commonly Asked in SQL ETL Interviews 

Although SQL is the primary skill required for ETL testing, interviewers often ask about popular ETL tools used in enterprise projects. 

Some of the most commonly used ETL tools include: 

  • Informatica – A widely used enterprise ETL platform for extracting, transforming, and loading data across multiple systems.  
  • Pentaho – An open-source ETL and Business Intelligence tool used for data integration, reporting, and analytics.  
  • Talend – A cloud-based ETL and data integration platform supporting various databases, APIs, and big data technologies.  

Most interviews focus more on ETL concepts, SQL skills, and data validation techniques than on tool-specific syntax. 

ETL Defect Examples + Test Case Sample 

Defect Example 

Issue 

Duplicate records appear in the fact table after the ETL load. 

Root Cause 

The issue is caused by incorrect join logic during the transformation process, resulting in multiple source records mapping to the same target record. 

Fix 

The defect can be resolved by: 

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

Sample Test Case 

Test Case 

Validate SCD Type 2 (SCD2) update 

Test Objective 

Verify that historical changes are maintained correctly when a dimension record is updated. 

Validation Steps 

  • Update a customer record in the source system.  
  • Execute the ETL process.  
  • Verify that the old dimension record is expired.  
  • Confirm that a new record is inserted.  
  • Check the effective start and end dates.  
  • Verify the current_flag values.  

Expected Result 

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

SQL ETL Testing – Quick Revision Sheet 

Before attending an ETL testing interview, quickly revise the following key concepts: 

  • Validate record counts between source and target tables.  
  • Compare aggregate values such as sums, averages, and counts.  
  • Check for NULL values in mandatory fields.  
  • Detect duplicate records using SQL queries.  
  • Review the Source-to-Target (S2T) mapping document carefully.  
  • 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.  
  • Perform complete reconciliation between the source system, Data Warehouse, and BI reports.  
  • Validate referential integrity between fact and dimension tables.  
  • Review query execution plans to identify SQL performance issues.  

FAQs (Snippet-Friendly) 

Q1. What are SQL ETL testing interview questions? 

SQL ETL testing interview questions focus on validating ETL logic using SQL queries. They assess a candidate’s ability to verify record counts, transformations, joins, aggregations, duplicate records, NULL handling, referential integrity, and overall data quality within a Data Warehouse environment. Interviewers also evaluate how candidates troubleshoot ETL issues using SQL in real-world scenarios. 

Q2. Which SQL is mandatory for ETL testing? 

ETL testers are expected to have strong knowledge of SQL features commonly used for data validation. The most important SQL concepts include: 

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

Mastering these SQL techniques enables testers to validate ETL logic efficiently and accurately. 

Q3. How do you test SCD2 using SQL? 

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 correctly populated.  
  • Only one record has current_flag = ‘Y’.  
  • Previous records are expired with current_flag = ‘N’.  
  • Historical data is preserved without overwriting existing records.  

These validations ensure that Slowly Changing Dimension Type 2 logic is implemented correctly and supports accurate historical reporting. 

Leave a Comment

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

Copyright © 2026