IBM ETL Testing Interview Questions – Real-Time SQL & Data Warehouse Guide

1. What is ETL Testing? (Definition + Example)

ETL Testing is the process of validating data accuracy, completeness, transformation logic, and performance as data moves from source systems into a data warehouse.

Real-World Example (IBM Project Context)

• Source: Banking transaction system (DB2 / Oracle)
  
• Transformation: Currency conversion, deduplication, SCD handling
  
• Target: Enterprise Data Warehouse (EDW)
  
• Reporting: IBM Cognos / BI dashboards
  

Goal: Ensure business-critical reports use trusted, reconciled data.

2. Data Warehouse Flow: Source → Staging → Transform → Load → Reporting

DW Layer Responsibilities

Layer	Description
Source	OLTP systems, files, APIs
Staging	Raw data landing zone
Transformation	Business rules, joins, aggregations
Target (DW)	Fact & dimension tables
Reporting	BI tools, KPIs, analytics

---

3. ETL Architecture & Source-to-Target (S2T) Mapping

ETL Architecture Components

• Source systems (DB2, Oracle, Flat files)
  
• Staging schema
  
• ETL tool
  
• Data warehouse
  
• Reporting layer
  

S2T Mapping Validation Includes

• Column-to-column mapping
  
• Data type & length checks
  
• Transformation logic
  
• Default values & audit fields
  

---

4. IBM ETL Testing Interview Questions (Basic → Advanced)

Below are 75 interview-tested IBM ETL testing interview questions with concise answers.

---

A. Basic ETL Testing Interview Questions (1–20)

1. What is ETL testing?
   Validating data extraction, transformation, and loading.
   
2. Why is ETL testing important in IBM projects?
   IBM clients rely on EDW for regulatory and financial reporting.
   
3. What is a data warehouse?
   Central repository for analytical data.
   
4. What is staging area?
   Temporary storage for raw extracted data.
   
5. What is S2T mapping?
   Document defining source-to-target transformation rules.
   
6. Difference between ETL and ELT?
   ETL transforms before load; ELT after load.
   
7. What is data reconciliation?
   Comparing source and target data.
   
8. What is surrogate key?
   System-generated unique identifier.
   
9. Difference between fact and dimension tables?
   Fact = measures; Dimension = descriptive attributes.
   
10. What is full load?
    Loading entire dataset.
    
11. What is incremental load?
    Loading only changed data.
    
12. What is audit column?
    load_date, batch_id, updated_ts.
    
13. What is data profiling?
    Analyzing source data quality.
    
14. What is truncation testing?
    Ensuring data is not cut due to column size.
    
15. What is referential integrity?
    Fact foreign keys must exist in dimensions.
    
16. What is data validation?
    Business rule verification.
    
17. What is data verification?
    Data movement verification.
    
18. What is CDC?
    Change Data Capture.
    
19. What is reject table?
    Stores failed records.
    
20. What is data lineage?
    Tracking data from source to report.
    

---

B. SQL-Based ETL Testing Questions (21–45)

21. How do you validate record count?
    

SELECT COUNT(*) FROM source_orders;

SELECT COUNT(*) FROM target_fact_orders;

22. How to find duplicate records?
    

SELECT order_id, COUNT(*)

FROM staging_orders

GROUP BY order_id

HAVING COUNT(*) > 1;

23. How do you validate JOIN logic?
    

SELECT o.order_id, c.customer_name

FROM orders o

JOIN customers c

ON o.customer_id = c.customer_id;

24. How to validate aggregation logic?
    

SELECT customer_id, SUM(order_amount)

FROM fact_orders

GROUP BY customer_id;

25. How to detect missing records?
    

SELECT s.id

FROM source_table s

LEFT JOIN target_table t

ON s.id = t.id

WHERE t.id IS NULL;

26. What is GROUP BY used for in ETL testing?
    Validating summaries and totals.
    
27. How to validate null handling?
    

SELECT COUNT(*) FROM dim_customer WHERE email IS NULL;

28. What is Slowly Changing Dimension (SCD)?
    Managing dimension changes over time.
    
29. Difference between SCD Type 1 and Type 2?
    Type 1 overwrites, Type 2 preserves history.
    
30. SCD2 validation query
    

SELECT customer_id, COUNT(*)

FROM dim_customer

GROUP BY customer_id

HAVING COUNT(*) > 1;

31. How to validate current record in SCD2?
    Filter current_flag = ‘Y’.
    
32. What is hashing in ETL?
    Detecting data changes using hash keys.
    
33. How to validate derived columns?
    

SELECT amount * tax_rate AS expected_tax FROM staging_sales;

34. How to validate date transformations?
    

SELECT * FROM fact_orders WHERE order_date > CURRENT_DATE;

35. What is lookup validation?
    Ensure lookup values exist and match.
    
36. What is control table?
    Tracks batch status and load counts.
    
37. What is watermark column?
    Used for incremental loads.
    
38. What is late arriving dimension?
    Fact arrives before dimension.
    
39. What is late arriving fact?
    Fact arrives after reporting window.
    
40. What is data balancing?
    Matching totals across systems.
    
41. How do you validate decimal precision?
    

SELECT CAST(amount AS DECIMAL(10,2)) FROM staging;

42. Difference between truncate and delete?
    Truncate is faster, no rollback.
    
43. What is metadata testing?
    Validating schema and data types.
    
44. What is factless fact table?
    Tracks events without measures.
    
45. What is idempotent ETL?
    Same result on multiple runs.
    

---

C. Advanced & Performance ETL Questions (46–75)

46. Window function example
    

SELECT customer_id,

ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY updated_ts DESC) rn

FROM dim_customer;

47. Why use window functions in ETL?
    For ranking, deduplication, SCD logic.
    
48. What is ETL performance testing?
    Measuring load time and throughput.
    
49. How to tune slow ETL jobs?
    Indexing, partitioning, parallelism.
    
50. What is pushdown optimization?
    Running transformations in DB.
    
51. What is partitioning?
    Dividing tables to improve performance.
    
52. How do you validate data freshness?
    

SELECT MAX(load_date) FROM fact_sales;

53. What is ETL regression testing?
    Ensuring changes don’t break existing flows.
    
54. How do you test error handling?
    Validate reject tables and logs.
    
55. What is data skew?
    Uneven data distribution.
    
56. What is bulk load?
    High-volume loading strategy.
    
57. How do you validate historical accuracy?
    Check effective_date ranges.
    
58. What is schema evolution testing?
    Validating source schema changes.
    
59. What is data latency?
    Delay between source and DW availability.
    
60. How do you validate negative scenarios?
    Invalid, null, boundary data.
    
61. What is reconciliation report?
    Counts, sums, rejects summary.
    
62. What is ETL restartability?
    Resume after failure.
    
63. What is data anonymization testing?
    Validate masking of PII.
    
64. How do you validate surrogate key uniqueness?
    

SELECT sk, COUNT(*) FROM dim_customer GROUP BY sk HAVING COUNT(*)>1;

65. What is parallel processing?
    Running ETL jobs concurrently.
    
66. What is audit trail testing?
    Validate batch_id and timestamps.
    
67. What is data archival testing?
    Old data moved correctly.
    
68. What is transformation logic testing?
    Validate business rules.
    
69. What is end-to-end ETL testing?
    Source → report validation.
    
70. What is OLTP vs OLAP?
    Transactions vs analytics.
    
71. What is data drift?
    Unexpected data changes.
    
72. What is reject analysis?
    Root cause of rejected records.
    
73. How do you validate currency conversion?
    

SELECT local_amt * rate = usd_amt FROM staging;

74. What is data mart testing?
    Validating subject-specific DW subsets.
    
75. What is most critical ETL testing skill for IBM interviews?
    Strong SQL + business understanding.
    

---

5. ETL Tools Used in IBM Projects

• Informatica
  
• Microsoft SSIS
  
• Ab Initio
  
• Pentaho
  
• Talend
  

---

6. ETL Defect Examples + Sample Test Case

Defect: Duplicate records in Fact table
Root Cause: Missing business key join
Fix: Correct JOIN + hashing

Sample Test Case

Field	Value
Scenario	Duplicate detection
SQL	GROUP BY HAVING COUNT > 1
Expected	No duplicates

---

7. Quick Revision Sheet (IBM Interview Ready)

• Validate counts, sums, duplicates
  
• Focus on SCD1 / SCD2
  
• Practice JOIN, GROUP BY, window functions
  
• Understand performance tuning
  

---

8. FAQs (Featured Snippet Friendly)

Q1. What SQL skills are required for IBM ETL testing?
JOIN, GROUP BY, subqueries, window functions.

Q2. Is Informatica mandatory for IBM ETL roles?
Not mandatory, but widely used.

Q3. What is most asked ETL topic in IBM interviews?
SCD, reconciliation, SQL validation.