Ab Initio ETL Testing Interview Questions – Real-Time QA Guide (40–150 Q&A)

SCD Type 1 

SCD Type 1 updates the existing record whenever a change occurs. The previous value is overwritten, and no historical information is maintained. 

Characteristics 

• Existing record is overwritten.  

• No history is preserved.  

• Simple implementation.  

• Less storage required.  

• Suitable for correcting inaccurate data.  

Example 

Before Update: 

After Update: 

The previous city (Delhi) is permanently replaced by Mumbai. 

SCD Type 2 

SCD Type 2 preserves historical information by inserting a new record whenever a business attribute changes. Historical records remain available for reporting and auditing. 

Each version typically contains: 

• Surrogate Key  

• Business Key  

• Effective Date  

• End Date  

• Current Flag  

Example 

The previous version is retained while a new active record is created. 

Key Differences 

17. How Do You Validate SCD Type 2? 

SCD Type 2 validation ensures that historical data is correctly maintained whenever a business attribute changes. 

ETL testers verify that a new version of the record is created while the previous version is properly expired. 

Validation Steps 

• Verify a new row is inserted for every business change.  

• Confirm the old record is marked as inactive.  

• Validate current_flag.  

• Check effective_date.  

• Verify end_date.  

• Confirm surrogate key generation.  

• Ensure business keys remain consistent.  

Sample SQL 

SELECT * 
FROM dim_customer 
WHERE current_flag = ‘Y’; 

Expected Result 

• Only one active record should exist for each business key.  

• Historical records should have current_flag = ‘N’.  

• Effective and end dates should not overlap.  

18. How to Validate Joins in Ab Initio? 

Join validation ensures that Ab Initio Join components correctly combine records from multiple datasets according to business rules. 

Incorrect joins may result in missing, duplicate, or incorrect data. 

Validation Steps 

• Verify join keys.  

• Validate primary and foreign key relationships.  

• Check join cardinality.  

• Verify NULL handling.  

• Compare source and target results.  

• Ensure expected number of output records.  

Common Join Types 

• Inner Join  

• Left Outer Join  

• Right Outer Join  

• Full Outer Join  

Expected Result 

Joined records should accurately reflect the relationships defined by the business requirements and S2T mapping. 

19. What is Rollup? 

A Rollup component in Ab Initio aggregates rows based on one or more grouping keys. 

It is commonly used to calculate summary information such as totals, counts, averages, minimums, and maximums. 

Common Aggregations 

• SUM  

• COUNT  

• AVG  

• MIN  

• MAX  

Example 

Rollup Output: 

Rollup helps generate summarized datasets for reporting and analytics. 

20. How Do You Test Aggregations? 

Aggregation testing verifies that ETL calculations such as totals, averages, and counts are accurate. 

ETL testers independently calculate expected values using SQL and compare them with the target data. 

Sample SQL 

SELECT customer_id, 
      SUM(order_amount) 
FROM fact_orders 
GROUP BY customer_id; 

Validation Steps 

• Recompute totals using SQL.  

• Compare source and target values.  

• Validate grouping logic.  

• Verify rounding rules.  

• Check decimal precision.  

Expected Result 

Aggregated values in the target should exactly match the expected business calculations. 

21. What is CDC? 

CDC (Change Data Capture) is a technique used to process only records that have changed since the previous ETL execution. 

Instead of reprocessing the entire dataset, CDC identifies: 

• New records.  

• Updated records.  

• Deleted records.  

Benefits 

• Faster ETL execution.  

• Lower system load.  

• Reduced processing time.  

• Improved performance.  

CDC is commonly implemented using timestamps, transaction logs, or hash comparisons. 

22. How Do You Test CDC? 

CDC testing verifies that only changed records are processed during incremental ETL execution. 

Validation Steps 

• Compare before and after datasets.  

• Verify inserted records.  

• Verify updated records.  

• Verify deleted records.  

• Compare counts by operation.  

• Validate watermark or timestamp values.  

Expected Result 

Only delta records should be processed while unchanged records remain untouched. 

23. What is a Surrogate Key? 

A surrogate key is a system-generated unique identifier assigned to records in a dimension table. 

Unlike business keys, surrogate keys have no business meaning and remain stable even if business attributes change. 

Benefits 

• Ensures uniqueness.  

• Supports SCD Type 2.  

• Simplifies joins.  

• Improves query performance.  

• Preserves historical data.  

Example 

Surrogate keys are widely used in dimensional modeling. 

24. How Do You Validate Surrogate Keys? 

Surrogate key validation ensures that generated keys are unique and correctly assigned. 

Validation Steps 

• Verify uniqueness.  

• Ensure values are not NULL.  

• Check sequence integrity.  

• Validate mapping logic.  

• Compare with business keys.  

Sample SQL 

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

Expected Result 

The query should return zero rows, indicating that every surrogate key is unique. 

25. What is a Late-Arriving Dimension? 

A late-arriving dimension occurs when a fact record reaches the data warehouse before its corresponding dimension record. 

For example, a sales transaction may arrive before the customer information has been loaded into the customer dimension. 

Common Handling Techniques 

• Create placeholder dimension records.  

• Use unknown members.  

• Update placeholder records later.  

• Reload affected fact records if necessary.  

Validation Steps 

• Verify placeholder record creation.  

• Check foreign key relationships.  

• Confirm later updates.  

• Validate reporting accuracy.  

26. How Do You Test Null Handling? 

Null handling validation ensures that NULL values are processed according to business rules. 

Different projects may require NULL values to be replaced, rejected, or passed through unchanged. 

Validation Steps 

• Verify default values.  

• Check reject records.  

• Validate pass-through logic.  

• Compare with S2T mapping.  

• Confirm mandatory fields are populated.  

Common Scenarios 

• Replace NULL with default values.  

• Reject invalid records.  

• Allow NULL values for optional fields.  

27. What is Data Skew? 

Data skew occurs when data is distributed unevenly across processing partitions. 

Instead of equal workloads, some partitions process significantly more records than others, reducing ETL performance. 

Effects 

• Slow execution.  

• Uneven CPU utilization.  

• Longer processing time.  

• Resource bottlenecks.  

Monitoring data distribution helps identify skew before it impacts production performance. 

28. How Do You Handle Data Skew? 

Several techniques can be used to reduce data skew and improve parallel processing efficiency. 

Common Techniques 

• Re-partition data.  

• Salting keys.  

• Use skew hints.  

• Redistribute partitions.  

• Optimize partition strategy.  

These methods help balance workloads across processing nodes and improve ETL performance. 

29. What is Restartability? 

Restartability is the ability of an ETL process to resume execution from the last successful checkpoint after a failure. 

Instead of restarting the entire workflow, the ETL job continues from the point where it stopped. 

Benefits 

• Prevents duplicate processing.  

• Saves execution time.  

• Improves ETL reliability.  

• Reduces downtime.  

Restartability is especially important for large ETL jobs processing millions of records. 

30. How Do You Test Restartability? 

Restartability testing verifies that ETL jobs recover correctly after unexpected failures. 

Validation Steps 

• Start the ETL job.  

• Intentionally fail the job during execution.  

• Restart the ETL process.  

• Verify processing resumes from the last checkpoint.  

• Confirm no duplicate records are created.  

• Validate idempotency.  

Expected Result 

The restarted ETL job should complete successfully without reprocessing previously completed records or introducing duplicate data. 

31. What is Metadata Testing? 

Metadata Testing is the process of validating that the metadata used in the ETL process matches the Source-to-Target (S2T) mapping document and database schema. Metadata describes the structure of the data rather than the actual data values. 

In Ab Initio projects, metadata defines record layouts, field names, data types, lengths, precision, and file structures. Incorrect metadata can lead to ETL failures, data truncation, or incorrect reporting. 

Metadata Validation Includes 

• Record formats.  

• Column names.  

• Data types.  

• Field lengths.  

• Precision and scale.  

• Nullable constraints.  

• Primary keys.  

• Foreign keys.  

• Record layouts.  

Validation Steps 

• Compare metadata with the S2T mapping document.  

• Verify database schema definitions.  

• Check file layouts.  

• Validate field lengths and data types.  

• Ensure metadata changes are reflected in ETL graphs.  

Benefits 

• Prevents schema mismatches.  

• Reduces ETL failures.  

• Improves data quality.  

• Ensures successful data loading.  

32. How Do You Test File Formats? 

File format testing verifies that input and output files conform to the required specifications before they are processed by the ETL workflow. 

Incorrect file formats may cause ETL jobs to fail or load inaccurate data. 

Validation Includes 

• Delimiter validation.  

• Header validation.  

• Footer validation.  

• File encoding.  

• Column order.  

• Column count.  

• Date formats.  

• Numeric formats.  

Common File Types 

• CSV  

• Pipe-delimited  

• Fixed-width  

• Tab-delimited  

• JSON  

• XML  

Validation Steps 

• Verify delimiter characters.  

• Check header and footer records.  

• Validate UTF-8 or required encoding.  

• Confirm record count.  

• Verify mandatory columns.  

Expected Result 

The file should match the agreed file specification exactly and be processed without errors. 

33. What is Referential Integrity? 

Referential Integrity (RI) ensures that every foreign key value in a fact table has a corresponding primary or surrogate key in the related dimension table. 

Maintaining referential integrity prevents orphan records and ensures accurate reporting. 

Example 

Fact Table: 

Dimension Table: 

Every foreign key in the fact table should exist in the dimension table. 

Benefits 

• Accurate joins.  

• Consistent reporting.  

• Improved data integrity.  

• Reliable analytics.  

34. How Do You Validate Referential Integrity? 

Referential Integrity validation ensures that all foreign keys in fact tables reference valid dimension records. 

One of the most common validation techniques is an anti-join, which identifies fact records that do not have matching dimension records. 

Sample SQL 

SELECT f.customer_sk 
FROM fact_sales f 
LEFT JOIN dim_customer d 
ON f.customer_sk = d.customer_sk 
WHERE d.customer_sk IS NULL; 

Validation Steps 

• Compare fact and dimension keys.  

• Identify orphan records.  

• Validate surrogate keys.  

• Confirm dimension availability.  

Expected Result 

The query should return zero rows, indicating that all foreign keys have valid dimension references. 

35. What is SLA Testing? 

Service Level Agreement (SLA) Testing verifies that ETL jobs complete within the agreed execution time. 

Large enterprise ETL jobs often have strict deadlines because reporting systems depend on timely data availability. 

Validation Includes 

• Batch completion time.  

• Throughput.  

• Resource utilization.  

• Performance benchmarks.  

• Load duration.  

Example 

Business Requirement: 

36. How Do You Test Performance Tuning?