ETL Testing Interview Questions for 6 Years Experienced – Complete Expert Guide

Restartability testing verifies that an ETL job can recover correctly after an unexpected failure without causing duplicate records or data loss. 

A typical approach includes: 

• Intentionally failing the ETL job during execution.  

• Restarting or re-executing the job.  

• Verifying that processing resumes from the correct checkpoint.  

• Confirming that no duplicate records are created.  

• Ensuring no records are lost.  

• Validating audit fields and batch information after recovery.  

Successful restartability testing confirms that the ETL process can recover safely from failures while maintaining data integrity and meeting operational requirements. 

Real SQL Validation Examples (With Sample Data) 

SQL is the primary validation tool used by ETL QA engineers to verify data accuracy, transformation logic, reconciliation, duplicate records, and ETL performance. In interviews for professionals with 6+ years of experience, candidates are expected to explain SQL validation techniques and write efficient queries for real-world ETL scenarios. 

Sample Source Table 

orders_src 

This table represents the operational source system from which order data is extracted. 

Sample Target Fact Table 

fact_orders 

The ETL process transforms the source data and loads it into the target fact table for reporting and analytics. 

Record Count Validation 

Record count validation verifies that the expected number of records has been successfully loaded from the source into the target system. 

Source Query 

SELECT COUNT(*) 
FROM orders_src 
WHERE order_date >= ‘2024-01-01’; 

Target Query 

SELECT COUNT(*) 
FROM fact_orders 
WHERE order_dt >= ‘2024-01-01’; 

Purpose 

This validation compares the number of records extracted from the source with the number of records loaded into the target for the specified date range. 

ETL testers use this validation to identify: 

• Missing records.  

• Duplicate records.  

• Filtering issues.  

• ETL load failures.  

If the counts do not match, further investigation is required to determine whether the difference is expected or caused by an ETL defect. 

JOIN Validation 

JOIN validation compares corresponding records in the source and target systems to ensure that transformed values are loaded correctly. 

SQL Query 

SELECT s.order_id, 
      s.amount, 
      t.total_amount 
FROM orders_src s 
JOIN fact_orders t 
ON s.order_id = t.order_key 
WHERE s.amount <> t.total_amount; 

Purpose 

This query identifies records where the source amount differs from the transformed amount stored in the target table. 

ETL QA engineers use JOIN validation to verify: 

• Transformation accuracy.  

• Currency conversion results.  

• Business rule implementation.  

• Data consistency.  

Any records returned by this query indicate potential transformation defects that require investigation. 

GROUP BY Aggregation Validation 

Aggregation validation ensures that summarized business values match between the source and target systems. 

Source Query 

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

Target Query 

SELECT cust_key, 
      SUM(total_amount) 
FROM fact_orders 
GROUP BY cust_key; 

Purpose 

The aggregated totals generated from the source system are compared with those stored in the target Data Warehouse. 

This validation helps verify: 

• Aggregation logic.  

• Transformation accuracy.  

• Missing records.  

• Duplicate records.  

Aggregation validation is commonly used for financial reporting and business intelligence projects. 

Window Function – Duplicate Detection 

Window functions are commonly used in ETL testing to identify duplicate records while preserving the latest or required version of each record. 

SQL Query 

SELECT order_id 
FROM ( 
   SELECT order_id, 
          ROW_NUMBER() OVER 
          (PARTITION BY order_id ORDER BY order_date) rn 
   FROM fact_orders 
) x 
WHERE rn > 1; 

Purpose 

This query assigns a row number to each record within the same order_id group. 

Any record with rn > 1 represents a duplicate record that should be reviewed according to the business rules. 

Window functions such as ROW_NUMBER() are frequently used in ETL testing for: 

• Duplicate detection.  

• Deduplication validation.  

• Latest record identification.  

• SCD processing.  

Performance Tuning Validation 

Performance validation ensures that SQL queries execute efficiently and ETL jobs complete within the agreed Service Level Agreements (SLAs). 

SQL Query 

EXPLAIN ANALYZE 
 
SELECT * 
FROM fact_orders 
WHERE order_dt = ‘2024-01-01’; 

Purpose 

EXPLAIN ANALYZE provides detailed information about how the database executes the query. 

ETL testers and developers use the execution plan to identify: 

• Full table scans.  

• Missing indexes.  

• Expensive joins.  

• Poor query optimization.  

• Performance bottlenecks.  

Reviewing execution plans helps improve ETL performance and reduce overall job execution time. 

Scenario-Based ETL Testing Questions with Answers