1. Introduction
Capgemini ETL testing interview questions are commonly asked for ETL QA, Data Warehouse Testing, BI Testing, and Data Validation roles at Capgemini. Capgemini works extensively with banking, insurance, retail, healthcare, and telecom clients, where data accuracy, transformation logic, and SLA compliance are business-critical.
In Capgemini interviews, ETL testers are evaluated on:
- Strong ETL & Data Warehouse fundamentals
- Hands-on SQL query writing
- Clear understanding of Source-to-Target (S2T) mapping
- Ability to handle real-time ETL defects
- Knowledge of SCD1, SCD2, audit fields, hashing
- Awareness of performance tuning & batch processing
This article is written as a Capgemini-specific ETL interview preparation guide, suitable for freshers, 2–5 years experienced, and senior ETL testers.
2. What is ETL Testing? (Definition + Capgemini-Style Example)
ETL Testing is the process of validating that data is:
- Extracted correctly from source systems
- Transformed accurately using business rules
- Loaded completely into the target data warehouse
Real-Time Capgemini Project Example
- Source: Banking transactions from OLTP systems
- Transform:
- Deduplication
- Currency conversion
- Daily & monthly aggregation
- SCD2 handling for customer dimension
- Deduplication
- Target: Enterprise Data Warehouse (EDW)
- Reporting: Power BI / Tableau dashboards
At Capgemini, ETL testers are expected to validate data correctness + business impact, not just ETL job success.
3. Data Warehouse Flow – Source → Staging → Transform → Load → Reporting
Typical ETL Architecture in Capgemini Projects
- Source Systems – OLTP databases, flat files, APIs
- Staging Layer – Raw extracted data
- Transformation Layer – Cleansing, enrichment, business logic
- Target Layer (DW/Data Mart) – Fact & Dimension tables
- Reporting Layer – BI tools & analytics
👉 Interview Tip: Capgemini interviewers often ask how you handle reconciliation, restartability, and audit validation.
4. Capgemini ETL Testing Interview Questions & Answers (Basic → Advanced)
A. Basic ETL Testing Interview Questions (Capgemini)
Q1. What is ETL testing?
ETL testing validates extraction, transformation, and loading of data to ensure accuracy, completeness, and performance.
Q2. Why is ETL testing important in Capgemini projects?
Because Capgemini handles enterprise-scale clients where incorrect data can cause financial loss and compliance issues.
Q3. What is a data warehouse?
A centralized repository storing historical and integrated data for analytics and reporting.
Q4. What is a staging table?
A temporary table used to store raw extracted data before transformations are applied.
B. Data Warehouse & S2T Mapping Questions
Q5. What is Source-to-Target (S2T) mapping?
A document defining how source columns map to target columns, including transformation rules, default values, and rejection logic.
Q6. How do you validate S2T mapping in Capgemini projects?
By writing SQL queries to compare source, staging, and target data after applying transformation logic.
Q7. What challenges do you face during S2T validation?
- Complex joins across multiple sources
- Derived & conditional columns
- Lookup mismatches
- Null and default value handling
5. SQL Query Examples (Very Important for Capgemini)
Record Count Validation
SELECT COUNT(*) FROM src_orders;
SELECT COUNT(*) FROM fact_orders;
✔ Ensures no data loss during ETL.
Data Validation Using JOIN
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;
✔ Detects transformation or load defects.
Finding Missing Records
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;
✔ Identifies records missing in target.
GROUP BY & Aggregation Validation
SELECT region, SUM(sales_amount)
FROM fact_sales
GROUP BY region;
✔ Validates aggregation logic in fact tables.
Window Function Example
SELECT customer_id,
SUM(amount) OVER (PARTITION BY customer_id) AS total_spend
FROM fact_orders;
✔ Used to validate running totals and partition-level calculations.
Performance Tuning SQL
EXPLAIN ANALYZE
SELECT *
FROM fact_orders
WHERE order_date >= ‘2025-01-01’;
✔ Helps analyze slow queries and SLA risks.
6. Slowly Changing Dimension (SCD) Questions (Frequently Asked)
Q8. What is SCD Type 1?
Overwrites old data; history is not maintained.
Q9. What is SCD Type 2?
Maintains history using:
- Start date
- End date
- Active flag
SCD2 Validation SQL
SELECT customer_id, start_date, end_date, is_active
FROM dim_customer
WHERE customer_id = 101;
Q10. Common SCD2 defects seen in Capgemini projects?
- Multiple active records
- Old record not expired
- Incorrect effective dates
7. Scenario-Based ETL Testing Interview Questions (Capgemini Style)
Scenario 1: Record Count Mismatch
Possible Causes:
- Filter condition mismatch
- Wrong join type
- Duplicate source data
Scenario 2: Null Values in Target
SELECT *
FROM dim_customer
WHERE email IS NULL;
Action: Verify default value handling or reject logic.
Scenario 3: ETL Job Misses SLA
Resolution Steps:
- Analyze execution plan
- Optimize SQL
- Partition large tables
- Tune parallelism
8. ETL Tools Asked in Capgemini Interviews
Capgemini focuses more on conceptual clarity and real project experience than tool syntax.
Common ETL tools:
- Informatica
- Microsoft SSIS
- Ab Initio
- Talend
- Pentaho
9. ETL Defect Examples + Test Case Sample
Common ETL Defects in Capgemini Projects
| Defect Type | Example |
| Data loss | Missing records |
| Transformation error | Wrong revenue calculation |
| Duplicate data | Incorrect join |
| SCD defect | Multiple active records |
| Performance issue | Job misses SLA |
Sample ETL Test Case
| Field | Value |
| Test Case ID | CG_ETL_TC_01 |
| Scenario | Validate SCD Type 2 |
| Source | src_customer |
| Target | dim_customer |
| Expected | Only one active record |
10. Quick Revision Sheet (Capgemini ETL Interviews)
- ETL = Extract + Transform + Load
- Always validate count + data + transformation
- Strong SQL is mandatory (JOIN, GROUP BY, window functions)
- SCD2 questions are very common
- Performance & SLA awareness is critical
11. FAQs – Capgemini ETL Testing Interview Questions
Q1. Does Capgemini ask tool-specific ETL questions?
Mostly conceptual, with examples from your project experience.
Q2. Is SQL mandatory for Capgemini ETL interviews?
Yes, strong SQL skills are non-negotiable.
Q3. Are scenario-based questions common?
Yes, especially for 2+ years experienced roles.
Q4. Is ETL testing manual or automated in Capgemini?
Primarily SQL-driven manual testing with partial automation.