Scenario Based ETL Testing Interview Questions

Answer 

Real-time ETL testing validates the complete data flow across the production ETL pipeline: 

• Source → Staging: Verify that staging data matches the extracted source data.  

• Staging → Transformation: Validate business rules, joins, calculations, lookups, and transformation logic.  

• Transformation → Target Data Warehouse: Confirm accurate loading of fact and dimension tables, including record counts, surrogate keys, referential integrity, and audit fields.  

• Data Warehouse → Reports: Reconcile report totals, KPIs, dashboards, and business metrics with the underlying Data Warehouse tables.  

Successful real-time ETL testing ensures that data remains accurate, complete, consistent, and reliable from the source systems through to the final business reports and analytics. 

Real SQL Query Examples for ETL Validation 

SQL is one of the most important skills for ETL testers because it is used to validate data extraction, transformation, loading, reconciliation, and performance. In scenario-based ETL testing, SQL helps verify that business rules are correctly implemented and that the Data Warehouse (DW) accurately reflects the source systems. 

The following SQL examples demonstrate common validation techniques used in enterprise ETL and Data Warehouse projects. 

Sample Tables 

The following sample tables represent a simple ETL pipeline used for validation. 

Source Table 

src_orders( 
   order_id, 
   cust_id, 
   amount, 
   order_dt 
) 

Description 

• order_id – Unique order identifier.  

• cust_id – Customer identifier from the source system.  

• amount – Order amount.  

• order_dt – Date on which the order was placed.  

Dimension Table 

dim_customer( 
   cust_sk, 
   cust_id, 
   current_flag 
) 

Description 

• cust_sk – Surrogate key generated in the Data Warehouse.  

• cust_id – Business (natural) customer identifier.  

• current_flag – Indicates whether the record is the current active version.  

Fact Table 

fact_sales( 
   order_id, 
   cust_sk, 
   amount, 
   load_dt 
) 

Description 

• order_id – Order identifier.  

• cust_sk – Customer surrogate key.  

• amount – Sales amount after transformation.  

• load_dt – Date when the record was loaded into the Data Warehouse.  

1. Record Count Validation 

Record count validation is the first and most fundamental ETL validation. It confirms that all expected records have been successfully loaded into the target after ETL processing. 

Source Query 

SELECT COUNT(*) 
FROM src_orders; 

Target Query 

SELECT COUNT(*) 
FROM fact_sales; 

Purpose 

This validation helps ensure that: 

• All source records are extracted successfully.  

• All expected records are loaded into the target.  

• No records are lost during ETL processing.  

• No unexpected duplicate records are created.  

If the source and target counts do not match, investigate: 

• Transformation filters.  

• Rejected records.  

• Incremental load logic.  

• ETL execution logs.  

• Source-to-Target (S2T) mappings.  

2. JOIN Validation 

JOIN validation verifies that every fact record has a corresponding dimension record. 

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 query validates: 

• Referential integrity.  

• Dimension lookup success.  

• Fact-to-dimension relationships.  

• Correct surrogate key mapping.  

If the query returns one or more rows, it indicates missing dimension records or failed lookups that should be investigated. 

3. GROUP BY Aggregation Validation 

Aggregation validation compares summarized business values between the source and target. 

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

Purpose 

This validation ensures that: 

• Customer-level totals are accurate.  

• Business calculations are preserved.  

• Revenue totals remain consistent.  

• Reports match the source data.  

Aggregation validation is widely used in finance, banking, insurance, retail, healthcare, and telecom Data Warehouse projects. 

4. Window Function – Deduplication 

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

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: 

• Detect duplicate records.  

• Keep the latest record for each order_id.  

• Validate deduplication logic.  

• Support incremental ETL processing.  

The ROW_NUMBER() function assigns a sequential number to records within each order_id, allowing only the latest record to be selected. 

5. Performance Tuning Validation 

Performance validation helps determine how efficiently SQL queries execute against large datasets. 

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

Purpose 

This query helps analyze: 

• Query execution plans.  

• Index utilization.  

• Table scans.  

• Partition pruning.  

• Estimated execution cost.  

• Overall SQL performance.  

Reviewing the execution plan helps identify optimization opportunities and improve ETL job execution time. 

ETL Tools Commonly Asked in Interviews