PwC ETL Testing Interview Questions

SQL is one of the most important skills for ETL testers because it is used to validate data extraction, transformation, loading, reconciliation, and performance. The following SQL examples demonstrate common validation techniques used in enterprise Data Warehouse (DW) projects. 

Sample Tables 

The following sample tables represent a simple ETL pipeline. 

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 dimension record is the current 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 stored in the Data Warehouse.  

• load_dt – Date when the record was loaded.  

1. Record Count Validation 

Record count validation is the most basic and important ETL validation. It verifies 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 ensures that: 

• All source records have been extracted successfully.  

• All expected records have been loaded into the target.  

• No records are missing during ETL processing.  

• No unexpected duplicate records are created.  

If the record counts differ, the ETL tester should 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 a value greater than zero, it indicates missing dimension records or failed lookups that should be investigated. 

3. GROUP BY Aggregation Validation 

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

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

Purpose 

This validation helps ensure that: 

• Customer-level totals are accurate.  

• Business calculations are preserved.  

• Revenue totals remain consistent.  

• Reports match the source data.  

Aggregation validation is commonly used in finance, banking, insurance, retail, and enterprise reporting 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 is used to 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 performance. 

ETL Tools Commonly Asked in PwC Interviews