ETL Testing Interview Questions Capgemini – Complete Real-Time & SQL-Focused Guide

SQL is one of the most important topics in Capgemini ETL interviews. Candidates are expected to write SQL queries to validate data quality, business transformations, and ETL processing. 

1. Record Count Validation 

This query compares the total number of records between the source and target tables. 

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

Purpose 

This validation ensures that no data is lost during the ETL load. Differences in record counts may indicate filtering issues, rejected records, duplicate data, or loading failures. 

2. Data Validation Using JOIN 

This query compares corresponding records between the source and target tables. 

SELECT s.order_id, 
      s.amount AS src_amount, 
      t.amount AS tgt_amount 
FROM src_orders s 
JOIN fact_orders t 
 ON s.order_id = t.order_id 
WHERE s.amount <> t.amount; 

Purpose 

This query identifies transformation or loading defects by detecting records where the source and target values do not match. 

3. Finding Missing Records 

This query identifies records that exist in the source table but are missing from the target table. 

SELECT s.order_id 
FROM src_orders s 
LEFT JOIN fact_orders t 
 ON s.order_id = t.order_id 
WHERE t.order_id IS NULL; 

Purpose 

This validation detects records that were not loaded into the target because of ETL failures, filtering conditions, or rejected records. 

4. GROUP BY & Aggregation Validation 

Aggregation queries are commonly used to validate summarized business data. 

SELECT region, 
      SUM(sales_amount) 
FROM fact_sales 
GROUP BY region; 

Purpose 

This query validates aggregation logic and ensures that summarized values accurately represent the underlying transactional data stored in the fact table. 

5. Window Function Example 

Window functions are frequently asked in Capgemini interviews because they are widely used in analytical validations. 

SELECT customer_id, 
      SUM(amount) OVER (PARTITION BY customer_id) AS total_spend 
FROM fact_orders; 

Purpose 

This query is used for validating running totals and partition-level calculations while preserving individual transaction records. 

6. Performance Tuning SQL 

Performance optimization is important in enterprise ETL environments where large datasets must be processed within strict SLA timelines. 

EXPLAIN ANALYZE 
SELECT * 
FROM fact_orders 
WHERE order_date >= ‘2025-01-01’; 

Purpose 

The EXPLAIN ANALYZE statement helps analyze query execution plans, identify slow operations, evaluate index usage, detect full table scans, and assess potential SLA risks. 

Slowly Changing Dimension (SCD) Questions 

Slowly Changing Dimensions are one of the most frequently discussed topics in Capgemini ETL interviews because they play a key role in maintaining historical data within enterprise data warehouses. 

Q8. What is SCD Type 1? 

Answer: 

SCD Type 1 updates existing records by overwriting old values whenever changes occur. 

Key characteristics include: 

• Old data is overwritten.  

• Historical information is not maintained.  

• Only the latest value is retained.  

This approach is commonly used when maintaining historical data is not a business requirement. 

Q9. What is SCD Type 2? 

Answer: 

SCD Type 2 maintains historical information by inserting a new record whenever an attribute changes rather than updating the existing record. 

History is maintained using: 

• Start date.  

• End date.  

• Active flag.  

This approach allows organizations to preserve historical changes while identifying the current active record. 

SCD Type 2 Validation SQL 

SELECT customer_id, 
      start_date, 
      end_date, 
      is_active 
FROM dim_customer 
WHERE customer_id = 101; 

This query verifies that historical records are maintained correctly and ensures that only one active record exists for the specified customer. 

Q10. What common SCD Type 2 defects have you seen in Capgemini projects? 

Answer: 

Some of the most common SCD Type 2 defects encountered during enterprise ETL testing include: 

• Multiple active records.  

• Old records not expired correctly.  

• Incorrect effective dates.  

Additional issues may include duplicate historical records, incorrect surrogate key generation, and missing historical entries. ETL testers validate these scenarios to ensure accurate historical tracking and reliable reporting. 

Scenario-Based ETL Testing Interview Questions