Mindtree ETL Testing Interview Questions

Answer 

End-to-end ETL testing validates the complete data flow across all stages of the ETL pipeline: 

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

• Staging → Transformation: Validate business rules, joins, calculations, lookups, and data cleansing.  

• Transformation → Data Warehouse: Confirm that fact and dimension tables are loaded correctly with accurate record counts, surrogate keys, and audit fields.  

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

Successful end-to-end ETL testing ensures that data remains accurate, complete, consistent, and traceable from the original 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. 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 ensures that all expected records are 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 confirms 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, and ETL execution logs. 

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 any rows, 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. 

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 widely used in finance, banking, insurance, retail, and enterprise reporting projects. 

4. Window Function – Deduplication 

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

• 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 partition, allowing only the latest record to be selected. 

5. Performance Tuning Validation 

Performance validation helps analyze 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 usage.  

• 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 Mindtree Interviews