1. What is ETL Testing? (Definition + Example)
ETL Testing is the process of validating whether data is correctly Extracted, Transformed, and Loaded (ETL) from source systems into a data warehouse while ensuring that all defined business rules, transformations, and source-to-target mappings are followed accurately.
The primary objective of ETL Testing is to verify that the data stored in the target data warehouse is complete, accurate, consistent, and reliable for reporting and business decision-making. Testers validate each phase of the ETL process to ensure there is no data loss, duplication, corruption, or incorrect transformation during data movement.
An ETL tester typically verifies:
- Data extraction from multiple source systems
- Business transformation logic
- Data quality and integrity
- Source-to-target mappings
- Fact and dimension table loading
- Data reconciliation
- Reporting accuracy
Real-Time Example (Informatica Project)
Consider a banking or retail data warehouse project where customer transaction data is collected from multiple operational databases.
Source
Data is extracted from:
- Customer table (Oracle)
- Orders table (Oracle/DB2)
These source systems contain transactional data used by business applications.
Transformation
The extracted data is processed using Informatica PowerCenter by applying various business rules, including:
- Deduplication
- Currency conversion
- Slowly Changing Dimensions (SCD1 / SCD2)
These transformations ensure that only accurate, standardized, and business-ready data is loaded into the warehouse.
Target
After transformation, the processed data is loaded into:
- Fact tables
- Dimension tables
- Enterprise Data Warehouse (EDW)
The warehouse becomes the single source of truth for analytics.
Reporting
Business Intelligence tools generate reports and dashboards using warehouse data, such as:
- Cognos
- Tableau
Business users rely on these reports for operational monitoring and strategic decision-making.
Goal
The ultimate goal of ETL Testing is to ensure accurate, complete, transformed, and reconciled data reaches reporting systems without any data quality issues.
Data Warehouse Flow: Source → Staging → Transform → Load → Reporting
A typical ETL process follows multiple layers before data becomes available for reporting. Each layer has a specific responsibility that ensures data quality and consistency throughout the pipeline.
Data Warehouse Layers and Responsibilities
| Layer | Purpose |
| Source | OLTP databases, Flat Files, APIs |
| Staging | Raw extracted data |
| Transformation | Business rules, joins, SCD implementation |
| Target (Data Warehouse) | Fact and Dimension tables |
| Reporting | Analytics and dashboards |
1. Source Layer
The Source layer contains operational systems where business transactions are generated.
Examples include:
- OLTP databases
- Oracle
- SQL Server
- DB2
- Flat files
- CSV files
- Excel files
- REST APIs
- ERP applications
This layer serves as the starting point of the ETL process.
2. Staging Layer
The Staging layer stores raw extracted data exactly as it is received from source systems.
Its purpose includes:
- Temporary data storage
- Initial data validation
- Data extraction verification
- Performance optimization
No business transformations are generally applied at this stage.
3. Transformation Layer
This is the core ETL processing layer where business rules are implemented.
Typical transformations include:
- Business rule validations
- Data cleansing
- Joins
- Aggregations
- Lookup transformations
- Deduplication
- Data standardization
- Slowly Changing Dimensions (SCD)
- Derived column calculations
This layer converts raw data into business-ready information.
4. Target (Data Warehouse)
After successful transformation, data is loaded into the Enterprise Data Warehouse (EDW).
The target typically contains:
- Fact tables
- Dimension tables
- Historical data
- Aggregated business information
This structured data supports enterprise reporting and analytics.
5. Reporting Layer
The Reporting layer is where business users access data through Business Intelligence tools.
Common reporting tools include:
- Cognos
- Tableau
Reports generated from this layer help organizations monitor KPIs, analyze trends, and make informed business decisions.
Informatica ETL Architecture & S2T Mapping Validation
Informatica ETL Architecture
Informatica PowerCenter follows a structured ETL architecture that moves data from operational systems into the enterprise data warehouse while applying business transformations.
A typical Informatica ETL architecture consists of the following components:
Source Systems
The ETL process begins with extracting data from various source systems, such as:
- Oracle databases
- DB2 databases
- SQL Server
- Flat files
- APIs
- ERP systems
These systems provide the raw business data required for processing.
Staging Schema
Extracted data is initially loaded into the staging area.
The staging schema is used for:
- Temporary data storage
- Initial validation
- Data reconciliation
- Performance optimization before transformation
This layer preserves the original extracted data.
Informatica PowerCenter Mappings
The staging data is processed through Informatica PowerCenter mappings, where all business transformation logic is implemented.
Typical mapping activities include:
- Source qualifier
- Expression transformation
- Lookup transformation
- Joiner transformation
- Aggregator transformation
- Router transformation
- Update strategy
- Sequence generator
- Slowly Changing Dimension (SCD) logic
These mappings ensure that business rules are consistently applied before loading data into the warehouse.
Target Data Warehouse
Once transformations are complete, the processed data is loaded into the target Enterprise Data Warehouse.
The target generally consists of:
- Fact tables
- Dimension tables
- Historical data
- Business-ready datasets
This data serves as the foundation for enterprise reporting and analytics.
BI / Reporting Layer
Business Intelligence tools retrieve data from the warehouse to generate reports, dashboards, and analytical insights.
Examples include:
- Cognos
- Tableau
These reports enable business users to analyze performance, identify trends, and support strategic decision-making.
Source-to-Target (S2T) Mapping Validation
Source-to-Target (S2T) Mapping is one of the most important documents in ETL Testing. It defines how data moves from the source system to the target data warehouse and specifies every transformation that must be applied during the ETL process.
ETL testers validate the implementation against the S2T mapping document to ensure data accuracy and business rule compliance.
Source & Target Column Mapping
The S2T document specifies the relationship between source columns and target columns.
Validation ensures that:
- Every source column is correctly mapped.
- Data is loaded into the appropriate target column.
- No required mapping is missing.
Data Type and Length
The mapping document defines the expected data type and field length for every column.
ETL testing verifies that:
- Source and target data types are compatible.
- Field lengths are maintained.
- Data truncation does not occur.
- Numeric precision and scale are preserved.
Transformation Logic
Business transformation rules are documented in the S2T mapping.
Examples include:
- Data cleansing
- Calculated fields
- Currency conversion
- Conditional logic
- Lookup rules
- Data standardization
ETL testers validate that every transformation is correctly implemented according to the mapping specification.
Default Values
Some target columns may require default values when source data is unavailable.
Examples include:
- Default status values
- Default country codes
- Default flags
- System-generated values
Testing ensures that these defaults are populated correctly whenever applicable.
Audit Fields
Audit columns help track ETL execution and data lineage.
Common audit fields include:
- load_date
- batch_id
ETL testers verify that these fields are correctly populated during every ETL execution.
Slowly Changing Dimension (SCD) Rules
The S2T mapping also defines how historical changes should be managed in dimension tables.
Common implementations include:
- SCD Type 1
- SCD Type 2
ETL Testing validates that the appropriate SCD rules are correctly applied so that historical data is maintained according to business requirements.
4. Informatica ETL Testing Interview Questions (Basic → Advanced)
Below are 80+ Informatica ETL testing interview questions frequently asked in real interviews.
A. Basic Informatica ETL Testing Interview Questions (1–20)
1. What is ETL Testing?
ETL Testing is the process of validating whether data is correctly Extracted, Transformed, and Loaded (ETL) from source systems into a target data warehouse while following all business rules and transformation logic.
The primary objective of ETL Testing is to ensure that the data loaded into the target system is accurate, complete, consistent, and reliable for business reporting and analytics. During ETL Testing, testers verify that data has been extracted from the correct source, transformed according to business requirements, and loaded into the target tables without any data loss or corruption.
ETL Testing typically includes:
- Validating data extraction from source systems
- Verifying transformation logic
- Comparing source and target data
- Checking data completeness and accuracy
- Ensuring no duplicate or missing records
- Validating business rules and calculations
A successful ETL Testing process ensures that reports, dashboards, and business intelligence applications display accurate information for decision-making.
2. Why is ETL Testing Important in Informatica Projects?
ETL Testing is important in Informatica projects because it ensures that data flowing through Informatica mappings is accurate, complete, and compliant with business requirements. Since organizations rely on data warehouses for reporting and decision-making, even a small error in ETL processing can lead to incorrect reports and business losses.
In Informatica projects, ETL Testing helps to:
- Ensure reliable reporting
- Validate business transformation rules
- Prevent data loss during ETL execution
- Detect duplicate records
- Verify data quality
- Support regulatory compliance
- Improve confidence in business intelligence reports
Without ETL Testing, incorrect data may reach dashboards, affecting financial reports, customer analytics, and operational decisions.
3. What is Informatica PowerCenter?
Informatica PowerCenter is an enterprise ETL tool used for extracting, transforming, and loading data between different systems. It allows organizations to integrate data from multiple sources into a centralized data warehouse while applying complex business rules.
PowerCenter provides graphical development tools for creating ETL workflows without extensive programming.
Major components include:
- Source Analyzer
- Target Designer
- Mapping Designer
- Workflow Manager
- Workflow Monitor
- Repository Manager
Organizations use Informatica PowerCenter for:
- Data migration
- Data warehousing
- Data integration
- Data cleansing
- Business Intelligence projects
Its ability to process large volumes of data efficiently makes it one of the most widely used ETL tools in enterprise environments.
4. What is a Mapping in Informatica?
A mapping in Informatica defines how data moves from the source system to the target system. It contains the transformation logic required to convert raw source data into meaningful business data.
A mapping consists of:
- Source definitions
- Transformations
- Business rules
- Target definitions
Common transformations used inside mappings include:
- Source Qualifier
- Expression
- Lookup
- Joiner
- Aggregator
- Router
- Update Strategy
- Sequence Generator
Mappings are designed based on business requirements and the Source-to-Target (S2T) mapping document to ensure correct data flow and transformation.
5. What is a Staging Table?
A staging table is a temporary storage area where raw data extracted from source systems is stored before applying business transformations.
The staging layer acts as an intermediate step between the source system and the target data warehouse.
The main purposes of staging tables are:
- Store extracted source data
- Improve ETL performance
- Support data validation
- Enable data reconciliation
- Simplify ETL processing
- Facilitate error recovery
Generally, no complex business transformations are applied in staging tables. The data remains close to its original form until it is processed further.
6. What is S2T Mapping?
S2T (Source-to-Target) Mapping is a document that defines how each source field is mapped to the corresponding target field during the ETL process.
It serves as the blueprint for ETL developers and testers, ensuring that data movement and transformations follow business requirements accurately.
An S2T Mapping document typically includes:
- Source table and column names
- Target table and column names
- Data types and lengths
- Transformation logic
- Default values
- Business rules
- Primary keys
- Foreign keys
- Audit columns
- Slowly Changing Dimension (SCD) rules
ETL testers use the S2T Mapping document to validate that the implemented ETL logic matches the specified requirements.
7. Difference Between ETL and ELT?
Both ETL and ELT are data integration approaches, but they differ in the sequence of transformation and loading.
ETL (Extract, Transform, Load)
In ETL:
- Data is extracted from source systems.
- Transformations are applied before loading.
- Cleaned and transformed data is then loaded into the target warehouse.
ETL is suitable when business rules must be applied before data reaches the warehouse.
ELT (Extract, Load, Transform)
In ELT:
- Data is extracted from source systems.
- Raw data is loaded directly into the target.
- Transformations are performed inside the data warehouse.
ELT is commonly used with modern cloud-based data warehouses that provide high processing power.
Key Difference:
- ETL: Transforms data before loading.
- ELT: Transforms data after loading.
8. What is Data Reconciliation?
Data reconciliation is the process of comparing data between the source system and the target system to verify that the ETL process has transferred data accurately.
The goal of reconciliation is to ensure:
- Record counts match
- Data values are correct
- No records are missing
- No duplicate records exist
- Aggregated totals are accurate
- Business rules have been applied correctly
Common reconciliation activities include:
- Record count validation
- Sum validation
- Duplicate record checks
- Missing record identification
- Data comparison
Data reconciliation is one of the most critical activities in ETL Testing because it confirms the overall accuracy of the ETL process.
9. What is a Surrogate Key?
A surrogate key is a system-generated unique identifier assigned to records in dimension tables. Unlike natural keys, surrogate keys have no business meaning and are created solely for maintaining relationships within the data warehouse.
Advantages of surrogate keys include:
- Ensuring uniqueness
- Supporting Slowly Changing Dimensions (SCD)
- Improving query performance
- Maintaining historical records
- Simplifying joins between fact and dimension tables
For example:
| Customer ID (Business Key) | Customer SK (Surrogate Key) |
| C101 | 1001 |
| C102 | 1002 |
Surrogate keys are widely used in dimensional modeling because they remain stable even if business keys change.
10. Difference Between Fact and Dimension Tables?
Fact and Dimension tables are the core components of a data warehouse schema.
Fact Table
A Fact table stores measurable business data, also known as facts or metrics.
Examples include:
- Sales Amount
- Order Quantity
- Revenue
- Profit
- Transaction Count
Characteristics:
- Contains numeric measures
- Includes foreign keys referencing dimension tables
- Can contain millions of records
- Used for calculations and reporting
Dimension Table
A Dimension table stores descriptive information that provides context to the facts.
Examples include:
- Customer Name
- Product Name
- City
- Department
- Date
- Region
Characteristics:
- Contains descriptive attributes
- Supports filtering and grouping
- Usually smaller than fact tables
- Used for reporting and analysis
Key Difference:
- Fact Table: Stores measurable business metrics.
- Dimension Table: Stores descriptive business attributes that provide context for analysis.
11. What is Full Load?
A Full Load is an ETL process that loads the entire dataset from the source system into the target database, regardless of whether the records are new, updated, or unchanged. During a full load, all existing data in the target table is usually deleted or overwritten before loading the complete dataset again.
Full load is generally used during the initial data warehouse setup, system migrations, or when rebuilding a target table after major structural changes.
Characteristics of Full Load
- Loads the complete dataset.
- Does not differentiate between old and new records.
- Suitable for initial data loading.
- Simple to implement.
- Requires more execution time for large datasets.
- Consumes more system resources.
Example
Suppose the Customer table contains 5 million records. During a full load, all 5 million records are extracted from the source and loaded into the target warehouse, even if only a few records have changed.
Advantages
- Easy to implement.
- Ensures complete data consistency.
- Useful for initial ETL execution.
Disadvantages
- Longer execution time.
- Higher resource consumption.
- Not suitable for daily large-volume processing.
12. What is Incremental Load?
An Incremental Load is an ETL process that loads only the newly inserted or modified records since the previous ETL execution. Instead of processing the entire dataset, incremental loading transfers only the changed data, making the ETL process faster and more efficient.
Incremental loads are commonly used in production environments where data is updated regularly.
Characteristics of Incremental Load
- Loads only new or changed records.
- Uses timestamp or watermark columns.
- Reduces ETL execution time.
- Improves performance.
- Minimizes system resource usage.
Example
If yesterday’s ETL loaded 1 million records and today only 2,000 records were added or modified, the incremental load processes only those 2,000 records instead of reloading the entire dataset.
Advantages
- Faster execution.
- Lower database load.
- Better performance.
- Efficient use of storage and network resources.
Disadvantages
- More complex implementation.
- Requires accurate change detection logic.
13. What are Audit Columns?
Audit columns are system-generated fields used to track ETL execution details and maintain data lineage. These columns provide information about when and how data was loaded into the target system.
Audit columns help ETL developers and testers monitor data movement, troubleshoot failures, and verify successful ETL execution.
Common Audit Columns
- load_date – Date on which the record was loaded.
- batch_id – Unique identifier for the ETL batch.
- updated_ts – Timestamp indicating the last update time.
Other commonly used audit fields include:
- created_date
- created_by
- modified_date
- modified_by
- source_system
Benefits of Audit Columns
- Track ETL execution.
- Support debugging.
- Improve data traceability.
- Simplify reconciliation.
- Maintain historical information.
14. What is Data Profiling?
Data profiling is the process of analyzing source data to understand its quality, structure, content, and relationships before ETL processing begins.
It helps identify potential data quality issues that may affect the ETL process or reporting accuracy.
Data Profiling Checks
- Null value analysis
- Duplicate records
- Invalid data formats
- Data type consistency
- Range validation
- Pattern analysis
- Missing values
- Unique key validation
Benefits
- Improves data quality.
- Detects anomalies early.
- Reduces ETL failures.
- Supports accurate business reporting.
Data profiling is usually performed before designing ETL mappings and transformation logic.
15. What is Truncation Testing?
Truncation testing verifies that no data is lost or truncated while loading data into the target database due to insufficient column size.
If the target column length is smaller than the source column length, part of the data may be cut off, leading to incorrect or incomplete information.
Example
Source column:
Customer_Name = Christopher Johnson
VARCHAR(30)
Target column:
Customer_Name
VARCHAR(15)
Result:
Christopher Jo
The remaining characters are truncated.
Validation Steps
- Compare source and target column lengths.
- Validate S2T mapping.
- Check target schema.
- Verify long text records.
Importance
- Prevents data loss.
- Maintains data integrity.
- Ensures accurate reporting.
16. What is Referential Integrity?
Referential Integrity ensures that relationships between fact tables and dimension tables remain valid.
Every foreign key in the fact table must reference an existing primary or surrogate key in the corresponding dimension table.
Example
Fact Table
| Customer_SK | Sales |
| 101 | 500 |
| 102 | 700 |
Dimension Table
| Customer_SK | Customer_Name |
| 101 | John |
| 102 | David |
Here, every Customer_SK in the fact table exists in the dimension table.
ETL Validation
- Check missing foreign keys.
- Validate dimension loading.
- Verify orphan records.
- Ensure referential consistency.
Maintaining referential integrity prevents incorrect joins and inaccurate reports.
17. What is CDC?
CDC stands for Change Data Capture.
It is a technique used to identify and capture only the records that have changed since the last ETL execution.
Instead of processing the entire dataset, CDC captures:
- Newly inserted records
- Updated records
- Deleted records
Benefits
- Faster ETL processing.
- Reduced system load.
- Efficient incremental loading.
- Improved performance.
CDC is commonly implemented using:
- Timestamp columns
- Log-based capture
- Trigger-based capture
- Sequence numbers
18. What is a Reject Table?
A Reject Table stores records that fail validation during the ETL process.
Instead of stopping the entire ETL job, invalid records are redirected to the reject table for further analysis while valid records continue to load successfully.
Reasons for Record Rejection
- Invalid data type
- Null values in mandatory columns
- Duplicate primary keys
- Foreign key violations
- Business rule failures
- Data format mismatch
Benefits
- Prevents ETL failures.
- Supports error analysis.
- Improves data quality.
- Simplifies debugging.
ETL testers often validate reject tables to ensure that all failed records are captured correctly.
19. What is Lookup Transformation?
A Lookup Transformation is used to retrieve related reference data from another table during the ETL process.
It allows Informatica to compare incoming data with existing records and fetch additional information required for transformations.
Common Uses
- Retrieving surrogate keys.
- Validating master data.
- Detecting duplicate records.
- Performing data enrichment.
- Applying business rules.
Example
Source Table
| Customer_ID |
| C101 |
Lookup Table
| Customer_ID | Customer_SK |
| C101 | 1001 |
The lookup transformation retrieves Customer_SK = 1001, which is then loaded into the fact table.
Lookup transformations improve data consistency and reduce redundant processing.
20. What is Data Lineage?
Data Lineage is the process of tracing the complete journey of data from its original source to the final report or dashboard.
It provides visibility into how data moves through different ETL stages and how each transformation affects the data.
A typical data lineage flow is:
Source System
↓
Staging Area
↓
Informatica Mapping
↓
Transformation
↓
Fact / Dimension Tables
↓
Data Warehouse
↓
BI Reports / Dashboards
Benefits of Data Lineage
- Tracks data movement.
- Supports impact analysis.
- Simplifies troubleshooting.
- Improves regulatory compliance.
- Enhances auditability.
- Increases confidence in business reports.
Data lineage is an essential concept in ETL Testing because it enables testers to verify that data has been processed correctly at every stage of the ETL pipeline.
SQL-Based ETL Testing Interview Questions
21. Record Count Validation
Record count validation is one of the most fundamental checks in ETL Testing. It verifies that the number of records extracted from the source matches the number of records loaded into the target, after considering applicable business rules.
Source Record Count
SELECT COUNT(*)
FROM src_orders;
Target Record Count
SELECT COUNT(*)
FROM tgt_fact_orders;
If the counts differ unexpectedly, ETL testers investigate whether the difference is due to valid transformations (such as filtering or deduplication) or indicates an ETL defect. Record count validation helps identify missing records, duplicate records, and incomplete data loads at an early stage.
22. How Do You Validate Record Count?
Record count validation is one of the most common and important activities in ETL Testing. It ensures that the number of records extracted from the source system matches the number of records loaded into the staging area and finally into the target data warehouse.
The purpose of this validation is to confirm that no records have been lost or duplicated during the ETL process. If there is a mismatch, ETL testers investigate whether it is caused by business rules, filtering logic, duplicate removal, or ETL defects.
Validation Process
- Count records in the source table.
- Count records in the staging table.
- Count records in the target table.
- Compare all counts.
- Investigate any unexpected differences.
Example
Source Count
SELECT COUNT(*)
FROM src_orders;
Target Count
SELECT COUNT(*)
FROM tgt_fact_orders;
Expected Result
The record counts should match unless business rules intentionally exclude certain records, such as inactive customers or duplicate data.
23. How to Find Duplicate Records?
Duplicate record validation ensures that the ETL process does not load the same business record multiple times into the target system.
Duplicate records can occur because of:
- Incorrect joins
- Faulty primary key logic
- Missing deduplication rules
- Multiple source feeds
- ETL mapping defects
The SQL query below identifies duplicate order_id values.
SELECT order_id,
COUNT(*)
FROM stg_orders
GROUP BY order_id
HAVING COUNT(*) > 1;
Explanation
- GROUP BY groups records based on order_id.
- COUNT(*) counts how many times each order appears.
- HAVING COUNT(*) > 1 returns only duplicate records.
Expected Result
The query should return no rows unless duplicate business records actually exist in the source.
24. How Do You Validate JOIN Logic?
JOIN validation ensures that ETL mappings correctly combine data from multiple source tables according to the defined business rules.
Incorrect joins can result in:
- Missing records
- Duplicate records
- Incorrect business data
- Reporting errors
Example SQL:
SELECT o.order_id,
c.customer_name
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id;
Validation Steps
- Verify join keys.
- Validate primary and foreign key relationships.
- Compare source and target results.
- Check for missing customer information.
- Verify one-to-one and one-to-many relationships.
Expected Result
Each order should be associated with the correct customer based on the defined join condition.
25. How Do You Validate Aggregation Logic?
Aggregation validation ensures that ETL transformations correctly calculate totals, averages, counts, minimum values, and maximum values.
Aggregation testing is commonly used for:
- Sales reports
- Revenue calculations
- Monthly summaries
- KPI dashboards
Example SQL:
SELECT customer_id,
SUM(order_amount)
FROM fact_orders
GROUP BY customer_id;
Validation Steps
- Calculate totals in the source.
- Calculate totals in the target.
- Compare aggregated values.
- Verify business rules.
- Validate rounding and precision.
Expected Result
The aggregated values in the target should exactly match the expected business calculations.
26. How Do You Identify Missing Records?
Missing record validation ensures that every expected record from the source is successfully loaded into the target.
A common technique is to use a LEFT JOIN.
SELECT s.id
FROM source_table s
LEFT JOIN target_table t
ON s.id = t.id
WHERE t.id IS NULL;
Explanation
- Source table is used as the base.
- LEFT JOIN compares source and target.
- Records with NULL values in the target indicate missing data.
Validation Purpose
- Detect ETL failures.
- Verify complete data migration.
- Ensure data reconciliation.
Expected Result
The query should return zero rows if all records have been loaded successfully.
27. What is GROUP BY Used for in ETL Testing?
The GROUP BY clause groups rows with similar values together so aggregate functions can be applied.
In ETL Testing, GROUP BY is commonly used for validating:
- Record totals
- Sales summaries
- Customer totals
- Product totals
- Department-wise reports
Example:
SELECT customer_id,
SUM(order_amount)
FROM fact_orders
GROUP BY customer_id;
Common Aggregate Functions
- COUNT()
- SUM()
- AVG()
- MIN()
- MAX()
GROUP BY validation ensures that business reports display accurate summarized information.
28. How to Validate Null Handling?
Null handling validation ensures that mandatory fields are populated correctly and optional fields follow business requirements.
Example SQL:
SELECT COUNT(*)
FROM dim_customer
WHERE email IS NULL;
Validation Steps
- Identify mandatory columns.
- Verify NULL replacement rules.
- Validate default values.
- Compare with S2T mapping.
Expected Result
Mandatory columns should not contain NULL values unless explicitly allowed by business rules.
29. What is Slowly Changing Dimension (SCD)?
A Slowly Changing Dimension (SCD) is a technique used in data warehousing to manage changes in dimension data over time.
Dimension tables store descriptive information such as:
- Customer Name
- Address
- Product Category
- Employee Department
When these values change, organizations must decide whether to overwrite the old value or preserve historical information.
SCD techniques help manage these changes efficiently.
Common SCD Types
- Type 1
- Type 2
- Type 3
Among these, Type 1 and Type 2 are the most frequently used in ETL projects.
30. Difference Between SCD Type 1 and Type 2
SCD Type 1
Type 1 overwrites the existing record with the new value.
Characteristics:
- No history maintained.
- Old values are lost.
- Simpler implementation.
- Less storage required.
Example:
Customer City changes from Mumbai to Pune.
The ETL process updates the existing row directly.
SCD Type 2
Type 2 preserves historical information by creating a new record whenever changes occur.
Characteristics:
- History maintained.
- New surrogate key generated.
- Effective dates maintained.
- Current flag maintained.
Example:
| Customer | City | Current Flag |
| John | Mumbai | N |
| John | Pune | Y |
This approach allows historical reporting and auditing.
31. SCD Type 2 Validation Query
The following query checks whether multiple historical versions exist for the same customer.
SELECT customer_id,
COUNT(*)
FROM dim_customer
GROUP BY customer_id
HAVING COUNT(*) > 1;
Purpose
The query identifies customers with multiple records, indicating that historical versions have been preserved.
Validation Points
- Historical records exist.
- Current record is identified correctly.
- Effective dates do not overlap.
- Surrogate keys are unique.
32. How Do You Validate the Current Active Record?
In SCD Type 2 implementations, the current active record is usually identified using a flag column.
Example:
SELECT *
FROM dim_customer
WHERE current_flag = ‘Y’;
Validation Checks
- Only one active record exists for each business key.
- Historical records have current_flag = ‘N’.
- Effective dates are correct.
- End dates are populated for historical records.
Expected Result
Each customer should have exactly one current active record.
33. What is Hashing in Informatica ETL?
Hashing is a technique used to detect data changes efficiently during incremental ETL processing.
Instead of comparing every column individually, Informatica generates a hash value based on multiple columns.
If the hash value changes, Informatica treats the record as updated.
Benefits
- Faster change detection.
- Improved ETL performance.
- Reduced comparison overhead.
- Simplifies SCD implementation.
Hashing is commonly used for:
- Incremental loading
- Change Data Capture (CDC)
- SCD Type 1
- SCD Type 2
34. How Do You Validate Derived Columns?
Derived column validation ensures that calculated columns are populated correctly according to business rules.
Example SQL:
SELECT amount * tax_rate AS expected_tax
FROM stg_sales;
Validation Steps
- Calculate expected values manually.
- Compare with target values.
- Validate rounding rules.
- Verify precision and scale.
Common Derived Columns
- Tax Amount
- Total Price
- Discount
- Profit
- Commission
The calculated values should match the expected business logic.
35. How Do You Validate Date Transformations?
Date transformation validation ensures that ETL processes correctly convert, format, and calculate date values.
Example SQL:
SELECT *
FROM fact_orders
WHERE order_date > CURRENT_DATE;
Validation Checks
- Date format conversion.
- Time zone conversion.
- Date arithmetic.
- Future date validation.
- Historical date validation.
Expected Result
Dates should comply with business rules and should not contain unexpected future or invalid values.
36. What is Lookup Cache Testing?
Lookup cache testing verifies that Informatica lookup transformations retrieve correct values from cached reference data.
Validation Steps
- Validate lookup table data.
- Verify cache refresh.
- Compare lookup results with the source.
- Check cache performance.
- Ensure correct surrogate key retrieval.
Proper lookup cache validation improves ETL performance while ensuring data accuracy.
37. What is a Control Table?
A control table stores metadata related to ETL execution.
It typically contains:
- Batch status
- Batch ID
- Start time
- End time
- Source record count
- Target record count
- Reject count
- Load status
Control tables help monitor ETL execution, support restartability, and simplify operational reporting.
38. What is a Watermark Column?
A watermark column is a timestamp or sequence column used to identify records that have changed since the last ETL execution.
It enables efficient incremental loading by processing only new or modified records.
Common Watermark Columns
- updated_date
- modified_timestamp
- created_date
- transaction_date
- sequence_number
Using a watermark column reduces ETL processing time and avoids reloading unchanged data.
39. What is a Late Arriving Dimension?
A late arriving dimension occurs when a fact record arrives before its corresponding dimension record.
For example, a sales transaction may be loaded into the fact table before the related customer information is available in the customer dimension.
ETL processes typically handle this by creating placeholder dimension records or updating the fact table once the dimension data becomes available.
40. What is a Late Arriving Fact?
A late arriving fact occurs when a fact record reaches the data warehouse after the expected reporting period has already closed.
Examples include delayed sales transactions, late payment records, or delayed shipment data.
ETL testing verifies that these late-arriving facts are correctly loaded and reflected in historical reports without compromising data accuracy.
41. Difference Between TRUNCATE and DELETE
Both TRUNCATE and DELETE are used to remove data from a table, but they behave differently.
| DELETE | TRUNCATE |
| Removes selected rows or all rows | Removes all rows from the table |
| Supports WHERE clause | Does not support WHERE clause |
| Can be rolled back (depending on the database and transaction) | Typically cannot be rolled back once committed |
| Slower because rows are deleted individually | Faster because it deallocates data pages |
| Generates more transaction log entries | Generates minimal logging |
Key Difference:
TRUNCATE is generally faster and is used to quickly remove all records from a table, whereas DELETE provides more flexibility by allowing selective row removal.
42. How Do You Validate Decimal Precision?
Decimal precision validation ensures that numeric values are stored accurately in the target database without rounding errors, truncation, or loss of precision. Financial, banking, insurance, and retail applications particularly depend on precise decimal values because even a small discrepancy can affect reports and business calculations.
ETL testers verify that the source data, transformation logic, and target table maintain the required precision and scale defined in the Source-to-Target (S2T) mapping document.
SQL Example
SELECT CAST(amount AS DECIMAL(10,2))
FROM stg_sales;
Validation Steps
- Compare source and target decimal values.
- Verify precision and scale defined in the database schema.
- Check for unexpected rounding.
- Validate currency calculations.
- Compare calculated values with business expectations.
Example
If the source amount is:
1250.5678
Expected target value with DECIMAL(10,2):
1250.57
Importance
Decimal precision validation helps:
- Prevent financial calculation errors.
- Ensure accurate reporting.
- Maintain compliance with business rules.
- Avoid data truncation during ETL.
43. What is Metadata Testing?
Metadata Testing is the process of validating that the database schema, table structures, column definitions, and data types in the target system match the specifications defined in the Source-to-Target (S2T) mapping document.
Instead of validating the actual data, metadata testing focuses on the structure of the database.
Metadata Validation Includes
- Table names
- Column names
- Data types
- Column lengths
- Precision and scale
- Primary keys
- Foreign keys
- Constraints
- Indexes
- Default values
Example
Suppose the S2T mapping specifies:
| Column | Data Type |
| Customer_ID | INT |
| Customer_Name | VARCHAR(100) |
| Salary | DECIMAL(10,2) |
ETL testers verify that the target database has the same schema definition.
Benefits
- Prevents schema mismatches.
- Ensures successful ETL execution.
- Reduces data loading errors.
- Maintains database consistency.
44. What is a Factless Fact Table?
A Factless Fact Table is a fact table that stores business events or transactions without containing any measurable numeric values.
Unlike regular fact tables, which store measures such as sales amount or quantity, a factless fact table only records that an event occurred.
Examples
- Student attendance
- Employee training completion
- Website login events
- Product promotion participation
- Insurance policy enrollment
Example
| Customer_SK | Product_SK | Date_SK |
| 101 | 2001 | 20260110 |
| 102 | 2005 | 20260110 |
Notice that there is no sales amount or quantity column.
Uses
- Event tracking
- Coverage analysis
- Eligibility tracking
- Business process monitoring
Factless fact tables are commonly used when the occurrence of an event itself is important rather than a measurable value.
45. What is Idempotent ETL?
An Idempotent ETL process produces the same result every time it is executed, regardless of how many times the job runs using the same input data.
This property is essential in production environments because ETL jobs may need to be restarted after failures.
Characteristics
- No duplicate records.
- No inconsistent data.
- Safe job restart.
- Repeatable ETL execution.
- Consistent business results.
Example
Suppose yesterday’s ETL loaded 10,000 customer records.
If the ETL job accidentally runs again with the same source data:
- Total records should still be 10,000, not 20,000.
Benefits
- Reliable ETL recovery.
- Easier maintenance.
- Prevents duplicate data.
- Improves production stability.
46. What is Data Balancing?
Data Balancing is the process of verifying that business totals remain consistent across different stages of the ETL pipeline.
Instead of comparing individual records, data balancing compares aggregated values such as totals, averages, and counts.
Common Balancing Metrics
- Record count
- Sales amount
- Transaction count
- Revenue
- Quantity
- Customer count
Example
Source Sales Total:
₹12,50,000
Target Sales Total:
₹12,50,000
Both values should match after ETL processing.
Benefits
- Detects missing records.
- Identifies duplicate loads.
- Ensures reporting accuracy.
- Simplifies reconciliation.
Advanced Informatica & Performance Interview Questions
47. Window Function Example
Window functions perform calculations across a set of related rows while preserving each individual row in the result set. In ETL Testing, they are commonly used for ranking, deduplication, and Slowly Changing Dimension (SCD) validation.
SQL Example
SELECT customer_id,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY updated_ts DESC
) AS rn
FROM dim_customer;
Explanation
- PARTITION BY customer_id groups records by customer.
- ORDER BY updated_ts DESC sorts the newest records first.
- ROW_NUMBER() assigns a sequence number within each customer group.
The record with rn = 1 represents the latest version of the customer record.
48. Why Are Window Functions Used in ETL Testing?
Window functions are used because they simplify complex analytical operations without requiring multiple joins or subqueries.
They are especially useful for:
- Deduplication
- SCD Type 2 validation
- Ranking records
- Identifying latest records
- Historical data validation
- Top-N analysis
Example
Finding the latest customer record:
SELECT *
FROM (
SELECT customer_id,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY updated_ts DESC
) AS rn
FROM dim_customer
) t
WHERE rn = 1;
Benefits
- Faster SQL execution.
- Cleaner queries.
- Better performance.
- Easier historical analysis.
49. What is ETL Performance Testing?
ETL Performance Testing measures how efficiently an ETL process extracts, transforms, and loads data within the expected time and resource limits.
The objective is to ensure that ETL jobs complete within the defined Service Level Agreements (SLAs).
Performance Metrics
- Load time
- Throughput
- CPU usage
- Memory utilization
- Disk I/O
- Network usage
Typical Validation
- Large-volume data loading.
- Batch processing time.
- Database response time.
- Parallel execution efficiency.
Performance testing ensures that production ETL jobs can process large datasets without delays.
50. How Do You Tune Slow Informatica Jobs?
Slow ETL jobs can negatively impact reporting schedules and business operations. ETL tuning focuses on improving job performance while maintaining data accuracy.
Common Optimization Techniques
- Create proper database indexes.
- Use table partitioning.
- Enable pushdown optimization.
- Optimize lookup transformations.
- Reduce unnecessary transformations.
- Use bulk loading.
- Tune SQL queries.
- Increase session buffer sizes.
- Optimize source queries.
- Use incremental loading where possible.
Benefits
- Faster ETL execution.
- Lower resource usage.
- Improved scalability.
- Better production performance.
51. What is Pushdown Optimization?
Pushdown Optimization is an Informatica feature that pushes transformation logic from Informatica to the database engine.
Instead of processing transformations inside Informatica, the database executes them using its own optimized SQL engine.
Advantages
- Faster processing.
- Reduced network traffic.
- Lower Informatica server load.
- Better database utilization.
Pushdown optimization is particularly useful for large datasets where the database can process transformations more efficiently than the ETL engine.
52. What is Partitioning?
Partitioning is the process of dividing large datasets into smaller partitions so that multiple portions of the data can be processed simultaneously.
Instead of processing one large dataset sequentially, Informatica executes multiple partitions in parallel.
Types of Partitioning
- Hash partitioning
- Key range partitioning
- Round-robin partitioning
- Pass-through partitioning
Benefits
- Faster ETL execution.
- Improved CPU utilization.
- Better scalability.
- Reduced processing time.
Partitioning is widely used in enterprise ETL jobs that process millions of records.
53. How Do You Validate Data Freshness?
Data freshness validation ensures that the latest source data has been successfully loaded into the target warehouse.
A common approach is to check the most recent load date.
SQL Example
SELECT MAX(load_date)
FROM fact_sales;
Validation Steps
- Compare the latest source update time.
- Verify the target load timestamp.
- Confirm batch completion.
- Ensure no delayed records exist.
Expected Result
The maximum load_date should correspond to the latest successful ETL execution according to the project schedule.
54. What is ETL Regression Testing?
ETL Regression Testing verifies that recent changes to ETL mappings, workflows, or business rules have not affected existing functionality.
It ensures that previously working ETL processes continue to produce the expected results after enhancements or bug fixes.
Regression Testing Includes
- Record count validation.
- Data comparison.
- Transformation validation.
- Lookup validation.
- Aggregation testing.
- Performance verification.
Regression testing is usually performed before every production release.
55. How Do You Test Error Handling?
Error handling testing verifies that the ETL process correctly identifies, logs, and manages invalid data without causing the entire ETL job to fail.
Validation Steps
- Check reject tables.
- Review session logs.
- Verify error messages.
- Validate exception handling.
- Confirm failed records are isolated.
- Ensure valid records continue processing.
Benefits
- Improves ETL reliability.
- Simplifies troubleshooting.
- Prevents data loss.
- Ensures stable production execution.
56. What is Data Skew?
Data skew occurs when data is distributed unevenly across partitions or processing nodes, causing some partitions to process significantly more data than others.
Effects of Data Skew
- Slow ETL jobs.
- Uneven CPU utilization.
- Longer execution times.
- Resource bottlenecks.
ETL developers analyze data distribution and adjust partitioning strategies to minimize skew.
57. What is Bulk Load?
Bulk Load is a high-performance technique used to load very large volumes of data into the target database efficiently.
Instead of inserting one row at a time, bulk loading inserts large batches of records in a single operation.
Advantages
- Faster data loading.
- Reduced transaction overhead.
- Better database performance.
- Efficient processing of millions of records.
Bulk loading is commonly used during initial data warehouse loads and large migration projects.
58. How Do You Validate Historical Accuracy?
Historical accuracy validation ensures that historical records remain correct after updates, particularly in Slowly Changing Dimension (SCD) Type 2 implementations.
Validation Steps
- Verify effective_date ranges.
- Check end_date values.
- Validate current_flag.
- Ensure historical records are not overwritten.
- Compare historical data with business requirements.
This validation guarantees that reports generated for previous periods remain accurate and auditable.
59. What is Schema Evolution Testing?
Schema Evolution Testing validates that changes made to the source schema are correctly handled by the ETL process without breaking existing functionality.
Examples of Schema Changes
- New columns added.
- Columns removed.
- Data type changes.
- Column length modifications.
- Table restructuring.
Validation
- Verify ETL mappings.
- Check metadata updates.
- Validate target schema.
- Ensure successful ETL execution.
60. What is Data Latency?
Data latency is the time delay between data being generated in the source system and becoming available in the target data warehouse or reporting system.
Lower latency means users receive more up-to-date information.
Causes of Data Latency
- Slow ETL processing.
- Network delays.
- Database bottlenecks.
- Large batch sizes.
- System failures.
Reducing data latency is important for timely reporting and operational decision-making.
61. How Do You Validate Negative Scenarios?
Negative scenario testing verifies that the ETL process correctly handles invalid, incomplete, or unexpected data.
Examples
- NULL values.
- Invalid dates.
- Duplicate keys.
- Negative amounts.
- Invalid formats.
- Boundary values.
Validation
- Check reject tables.
- Verify error logs.
- Confirm business rules.
- Ensure invalid records are handled appropriately.
62. What is a Reconciliation Report?
A Reconciliation Report summarizes the results of ETL validation by comparing data between the source and target systems.
It typically includes:
- Source record count.
- Target record count.
- Rejected record count.
- Total amounts.
- Aggregated values.
- Load status.
- Batch information.
Reconciliation reports provide evidence that the ETL process has completed successfully and that the data loaded into the warehouse is accurate, complete, and ready for reporting.
63. What is ETL Restartability?
ETL Restartability is the ability of an ETL process to resume execution from the point of failure instead of restarting the entire job. This feature is essential in production environments where ETL jobs process millions of records and may occasionally fail due to network issues, database failures, server crashes, or unexpected system interruptions.
A restartable ETL process ensures that already processed data is not loaded again, preventing duplicate records and reducing processing time. Informatica supports restartability through workflow recovery options, checkpoints, control tables, and batch management mechanisms.
Benefits of ETL Restartability
- Resumes the ETL job after failure.
- Prevents duplicate data loading.
- Saves processing time.
- Reduces production downtime.
- Improves ETL reliability.
- Supports large-volume batch processing.
Example
Suppose an ETL workflow is processing 10 million records and fails after successfully loading 7 million records. Instead of restarting from the beginning, restartability allows the ETL process to continue from record 7,000,001.
This capability is critical for maintaining high availability and operational efficiency in enterprise data warehouse projects.
64. What is Data Anonymization Testing?
Data Anonymization Testing verifies that Personally Identifiable Information (PII) and other sensitive data are properly masked, encrypted, or anonymized before being used in non-production environments such as development, testing, or training systems.
Organizations must protect confidential information to comply with data privacy regulations such as GDPR, HIPAA, and other organizational security policies.
Common PII Data
- Customer Name
- Aadhaar Number
- PAN Number
- Social Security Number
- Email Address
- Phone Number
- Credit Card Number
Validation Steps
- Verify masking rules.
- Validate encryption logic.
- Ensure original values cannot be recovered.
- Compare production and masked data.
- Verify regulatory compliance.
Example
| Original Data | Masked Data |
| John Smith | J*** S**** |
| 9876543210 | 98******10 |
Benefits
- Protects customer privacy.
- Meets compliance requirements.
- Prevents sensitive data exposure.
- Enables secure testing.
65. How Do You Validate Surrogate Key Uniqueness?
Surrogate keys are system-generated identifiers used in dimension tables. Every surrogate key must be unique because fact tables reference these keys to establish relationships with dimension records.
Duplicate surrogate keys can lead to incorrect joins, inconsistent reporting, and data integrity issues.
SQL Query
SELECT sk,
COUNT(*)
FROM dim_customer
GROUP BY sk
HAVING COUNT(*) > 1;
Explanation
- GROUP BY sk groups records by surrogate key.
- COUNT(*) counts the occurrences of each key.
- HAVING COUNT(*) > 1 returns duplicate surrogate keys.
Validation Steps
- Verify uniqueness of surrogate keys.
- Ensure no duplicate key generation.
- Validate sequence generators.
- Check primary key constraints.
Expected Result
The query should return zero rows, indicating that every surrogate key is unique.
66. What is Parallel Processing?
Parallel Processing is a technique in Informatica where multiple sessions or partitions are executed simultaneously instead of sequentially. This significantly reduces ETL execution time and improves overall system performance.
Instead of processing one dataset at a time, Informatica distributes the workload across multiple CPU cores or processing nodes.
Benefits
- Faster ETL execution.
- Better CPU utilization.
- Reduced batch window.
- Improved scalability.
- Higher throughput.
Common Uses
- Large fact table loading.
- Bulk data processing.
- Partitioned workflows.
- High-volume ETL jobs.
Parallel processing is particularly beneficial for enterprise data warehouses that process millions of records daily.
67. What is Audit Trail Testing?
Audit Trail Testing verifies that ETL processes correctly populate audit-related information for every data load. Audit trails provide a complete history of ETL execution and help organizations trace data movement throughout the ETL lifecycle.
Typical audit information includes batch identifiers, timestamps, load status, and source details.
Common Audit Columns
- batch_id
- load_date
- created_timestamp
- updated_timestamp
- source_system
- job_name
Validation Steps
- Verify batch IDs.
- Validate load timestamps.
- Confirm successful job execution.
- Compare audit records with ETL logs.
- Ensure complete traceability.
Benefits
- Supports auditing.
- Simplifies troubleshooting.
- Improves compliance.
- Tracks ETL execution history.
68. What is Data Archival Testing?
Data Archival Testing verifies that old or inactive data is moved from operational or warehouse tables to archival storage according to business and retention policies.
Archiving improves database performance while preserving historical data for future reference or regulatory requirements.
Validation Steps
- Verify archive criteria.
- Confirm successful data movement.
- Ensure no data loss.
- Validate archive tables.
- Compare source and archive record counts.
Benefits
- Improves database performance.
- Reduces storage costs.
- Meets retention policies.
- Preserves historical records.
69. What is Transformation Logic Testing?
Transformation Logic Testing ensures that all business rules defined in the ETL mappings are implemented correctly before data is loaded into the target system.
ETL testers compare the implemented transformation logic with the Source-to-Target (S2T) mapping document and business requirements.
Common Transformations
- Data cleansing.
- Currency conversion.
- Lookup transformation.
- Aggregation.
- Data standardization.
- Derived column calculation.
- SCD implementation.
Validation Steps
- Review business rules.
- Execute SQL validations.
- Compare expected and actual results.
- Verify calculations.
- Validate target data.
Transformation logic testing ensures that business reports display accurate and meaningful information.
70. What is End-to-End ETL Testing?
End-to-End ETL Testing validates the complete data flow from the source system to the final business reports or dashboards.
Instead of testing individual components, it verifies the entire ETL pipeline.
Typical Data Flow
Source System
↓
Staging Area
↓
ETL Transformation
↓
Data Warehouse
↓
Fact & Dimension Tables
↓
Reports / Dashboards
Validation Includes
- Source extraction.
- Record count validation.
- Data transformation.
- Target loading.
- Data reconciliation.
- Report validation.
End-to-end testing ensures that business users receive accurate and consistent information.
71. Difference Between OLTP and OLAP
OLTP (Online Transaction Processing) and OLAP (Online Analytical Processing) serve different purposes in enterprise systems.
| OLTP | OLAP |
| Supports day-to-day business transactions | Supports business analysis and reporting |
| Highly normalized database | Denormalized data warehouse |
| Frequent INSERT, UPDATE, DELETE operations | Mostly read-only queries |
| Small transactions | Large analytical queries |
| Optimized for transaction speed | Optimized for reporting performance |
Examples
OLTP
- Banking systems
- Online shopping
- ATM transactions
- Order management
OLAP
- Sales dashboards
- Business intelligence reports
- Revenue analysis
- Executive reporting
72. What is Data Drift?
Data Drift refers to unexpected changes in the structure, distribution, or characteristics of source data over time.
Even though the ETL process remains unchanged, the incoming data may gradually differ from historical patterns, affecting business reports and analytics.
Examples
- New product categories.
- Missing values.
- Increased duplicate records.
- Different data formats.
- Changed customer behavior.
Validation Steps
- Compare historical data.
- Monitor distributions.
- Analyze trends.
- Review ETL outputs.
Data drift detection helps maintain long-term data quality.
73. What is Reject Analysis?
Reject Analysis is the process of identifying the root causes of records that fail during ETL execution and are redirected to reject tables.
Instead of simply counting rejected records, ETL testers investigate why each record failed.
Common Reasons
- Invalid data types.
- Missing mandatory fields.
- Duplicate keys.
- Foreign key violations.
- Business rule failures.
- Invalid date formats.
Validation Steps
- Review reject tables.
- Analyze session logs.
- Verify source data.
- Compare with business rules.
- Confirm corrective actions.
Reject analysis helps improve data quality and reduce ETL failures.
74. How Do You Validate Currency Conversion?
Currency conversion validation ensures that ETL transformations correctly convert monetary values using the appropriate exchange rates.
SQL Example
SELECT local_amt * rate AS expected_usd_amt
FROM stg_sales;
Validation Steps
- Verify exchange rate.
- Compare calculated values.
- Validate decimal precision.
- Check currency codes.
- Confirm business rules.
Example
| Local Amount | Rate | USD Amount |
| 1000 | 0.012 | 12.00 |
The calculated USD amount should match the value loaded into the target system.
75. What is Data Mart Testing?
Data Mart Testing validates subject-specific data marts that are created from the enterprise data warehouse.
Unlike an Enterprise Data Warehouse (EDW), which stores organization-wide data, a data mart focuses on a specific business function.
Examples
- Sales Data Mart.
- Finance Data Mart.
- HR Data Mart.
- Marketing Data Mart.
Validation Includes
- Record count.
- Business rules.
- Aggregations.
- Data reconciliation.
- Report accuracy.
Data mart testing ensures that departmental reports are accurate and consistent.
76. What is the Most Important Informatica Testing Skill?
The most important skill for an Informatica ETL tester is a combination of strong SQL knowledge and a thorough understanding of ETL mappings.
Essential Skills
- Advanced SQL.
- S2T mapping validation.
- Data warehouse concepts.
- Informatica transformations.
- Data reconciliation.
- ETL workflows.
- Performance testing.
- Defect analysis.
A tester with strong SQL skills and mapping knowledge can efficiently validate data movement, identify defects, and ensure accurate reporting.
77. What is a Session in Informatica?
A Session is an executable task in Informatica that runs a mapping and controls how data is extracted, transformed, and loaded.
A session contains execution-related configurations such as:
- Source connections.
- Target connections.
- Commit intervals.
- Error handling.
- Performance settings.
- Logging options.
Responsibilities
- Executes mapping logic.
- Loads data into the target.
- Generates session logs.
- Reports execution status.
Sessions are managed through Informatica Workflow Manager.
78. What is Workflow Testing?
Workflow Testing verifies that Informatica workflows execute in the correct sequence and complete successfully according to the designed ETL process.
A workflow may contain multiple tasks such as sessions, command tasks, email tasks, decision tasks, and event wait tasks.
Validation Includes
- Task execution order.
- Dependency validation.
- Success and failure handling.
- Batch scheduling.
- Workflow completion.
- Restartability.
Workflow testing ensures smooth orchestration of the entire ETL process.
79. What is Session Log Analysis?
Session Log Analysis involves reviewing Informatica session logs to identify execution details, errors, warnings, and performance bottlenecks.
Session logs provide valuable information about the ETL execution process.
Information Available in Session Logs
- Record counts.
- Rejected records.
- SQL execution.
- Transformation statistics.
- Execution time.
- Error messages.
- Warning messages.
Analyzing session logs helps testers quickly identify the root cause of ETL failures and optimize job performance.
80. What is Recovery Strategy?
A Recovery Strategy defines how an ETL process should recover after an unexpected failure while maintaining data integrity.
Instead of restarting the entire workflow, Informatica can resume processing from the last successful checkpoint.
Recovery Techniques
- Restart from the last checkpoint.
- Session recovery.
- Workflow recovery.
- Control table updates.
- Batch restart.
Benefits
- Minimizes downtime.
- Prevents duplicate data.
- Reduces processing time.
- Improves ETL reliability.
81. What is Lookup Override Testing?
Lookup Override Testing verifies that customized SQL queries (lookup overrides) used in Informatica lookup transformations return the correct data.
Instead of using the default lookup query, developers may write custom SQL to improve performance or implement complex business logic.
Validation Steps
- Review the overridden SQL query.
- Execute the SQL independently.
- Compare results with the lookup transformation.
- Verify filtering and join conditions.
- Validate performance improvements.
Proper lookup override testing ensures both correctness and efficiency of the ETL process.
82. What is the Biggest Challenge in Informatica ETL Testing?
One of the biggest challenges in Informatica ETL Testing is validating complex transformations while processing large volumes of data. Enterprise ETL projects often involve millions of records, multiple source systems, intricate business rules, and strict performance requirements.
Common Challenges
- Complex transformation logic.
- Large data volumes.
- Multiple source systems.
- Data quality issues.
- Incremental loading.
- Slowly Changing Dimensions (SCD).
- Performance optimization.
- Data reconciliation.
- Production defect analysis.
To address these challenges, ETL testers need strong SQL skills, a deep understanding of S2T mappings, knowledge of Informatica transformations, and the ability to analyze ETL logs and business rules effectively.
ETL Tools Used in Informatica Testing Projects
ETL Testing projects involve various ETL tools that help organizations extract, transform, and load data from multiple source systems into enterprise data warehouses. Although Informatica PowerCenter is one of the most widely used ETL tools, many organizations also use other ETL platforms depending on their business requirements, project complexity, and technology stack.
An ETL tester should have a basic understanding of these tools because interviewers often ask about the ETL platforms used in previous projects.
Common ETL Tools Used in Informatica Testing Projects
1. Informatica PowerCenter
Informatica PowerCenter is one of the most popular enterprise ETL tools used for large-scale data integration projects. It provides a graphical interface for designing ETL mappings, workflows, and transformations without requiring extensive programming.
Key Features
- Enterprise-grade ETL platform
- Graphical mapping development
- High-performance data integration
- Supports multiple databases and file systems
- Workflow automation
- Metadata management
- Advanced transformation capabilities
Common Use Cases
- Enterprise Data Warehouse (EDW)
- Data Migration
- Data Integration
- Master Data Management (MDM)
- Business Intelligence (BI)
2. Microsoft SSIS (SQL Server Integration Services)
Microsoft SSIS is Microsoft’s ETL solution used primarily with SQL Server environments. It enables organizations to build high-performance data integration and workflow solutions.
Key Features
- Drag-and-drop ETL development
- SQL Server integration
- Data cleansing
- Workflow automation
- Error handling
- Logging and monitoring
Common Use Cases
- SQL Server Data Warehouses
- Financial Reporting
- Data Migration
- Scheduled Batch Processing
3. Ab Initio
Ab Initio is a high-performance ETL platform commonly used by banking, insurance, telecommunications, and financial organizations for processing massive volumes of data.
Key Features
- Parallel processing
- High scalability
- Graphical development environment
- Metadata management
- Strong performance optimization
- Enterprise-grade data processing
Common Use Cases
- Banking
- Capital Markets
- Insurance
- Telecom
- Large-scale Data Warehousing
4. Pentaho Data Integration (PDI)
Pentaho Data Integration, also known as Kettle, is an open-source ETL tool used for data integration, transformation, and migration.
Key Features
- Open-source platform
- Drag-and-drop interface
- Data transformation
- Job scheduling
- Reporting integration
- Big Data support
Common Use Cases
- Business Intelligence
- Data Migration
- Reporting
- Analytics
- Small and Medium Enterprise (SME) projects
5. Talend
Talend is an open-source ETL and data integration platform that supports cloud, big data, and enterprise integration.
Key Features
- Open-source ETL
- Cloud integration
- Data quality management
- Metadata repository
- API integration
- Big Data processing
Common Use Cases
- Cloud Data Integration
- Data Warehousing
- Data Quality Projects
- API Integration
- Enterprise Analytics
Comparison of Popular ETL Tools
| ETL Tool | Primary Usage | Major Strength |
| Informatica PowerCenter | Enterprise ETL | Rich transformations and enterprise scalability |
| Microsoft SSIS | SQL Server ETL | Tight integration with Microsoft technologies |
| Ab Initio | High-volume enterprise ETL | Parallel processing and exceptional performance |
| Pentaho Data Integration | Open-source ETL | Cost-effective and easy to use |
| Talend | Cloud and enterprise integration | Data quality and cloud-native capabilities |
ETL Defect Examples and Sample Test Case
ETL Testing aims to identify defects that may occur during data extraction, transformation, or loading. Understanding common ETL defects helps testers quickly identify root causes and implement effective solutions.
ETL Defect Example
Defect
Duplicate customer records in the Dimension table.
Root Cause
Missing hash comparison during incremental load caused existing customer records to be inserted again instead of being updated.
Fix
Implement MD5 hashing on business keys to detect data changes accurately before loading records into the target dimension table.
Impact
- Duplicate customer records
- Incorrect reporting
- Wrong aggregation results
- Increased storage usage
- Data quality issues
Validation Steps
- Execute duplicate record queries.
- Compare business keys.
- Verify hash generation.
- Validate SCD logic.
- Confirm only one active customer record exists.
Sample ETL Test Case
The following sample test case validates duplicate records in an SCD Type 2 dimension table.
| Field | Value |
| Scenario | SCD2 duplicate check |
| SQL | GROUP BY HAVING COUNT(*) > 1 |
| Expected Result | One active record for each business key |
Sample SQL
SELECT customer_id,
COUNT(*)
FROM dim_customer
GROUP BY customer_id
HAVING COUNT(*) > 1;
Validation Objective
The purpose of this test case is to ensure:
- Only one active record exists for each business key.
- Historical records are maintained correctly.
- No duplicate active records are created.
- SCD Type 2 implementation follows business requirements.
Quick Revision Sheet (Interview Ready)
Before attending an Informatica ETL Testing interview, review the following key topics to strengthen your understanding of ETL concepts and SQL validation techniques.
Must-Know ETL Validation Techniques
- Validate record counts between source, staging, and target tables.
- Compare aggregate values such as sums and averages.
- Identify duplicate records.
- Detect missing records.
- Verify NULL handling.
- Perform data reconciliation.
- Validate referential integrity.
- Check audit columns.
- Verify metadata and schema.
Understand SCD Type 1 vs SCD Type 2
Interviewers frequently ask questions about Slowly Changing Dimensions.
SCD Type 1
- Overwrites existing data.
- Does not maintain history.
- Suitable for correcting inaccurate data.
SCD Type 2
- Preserves historical data.
- Creates a new record for every change.
- Uses surrogate keys, effective dates, and current flags.
Practice Important SQL Concepts
Strong SQL skills are essential for Informatica ETL Testing.
Focus on practicing:
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- GROUP BY
- HAVING
- Aggregate Functions
- Window Functions
- Subqueries
- Common Table Expressions (CTEs)
- Record Count Validation
- Duplicate Detection
- Missing Record Validation
Focus on Performance Tuning and Logs
Interviewers also expect candidates to understand ETL performance optimization and troubleshooting.
Important topics include:
- Session log analysis
- Workflow monitoring
- Pushdown optimization
- Partitioning
- Bulk loading
- Lookup optimization
- Indexing
- ETL restartability
- Reject table analysis
- Error handling
Reviewing these areas will help you answer both technical and scenario-based interview questions confidently.
Frequently Asked Questions (FAQs)
Q1. Is SQL mandatory for Informatica ETL Testing?
Yes. SQL is the primary validation skill required for Informatica ETL Testing.
Almost every ETL Testing activity depends on SQL, including:
- Record count validation
- Duplicate record detection
- Data reconciliation
- Aggregation validation
- Join validation
- SCD verification
- Data quality checks
- Report validation
Strong SQL knowledge is considered one of the most important skills for ETL testers because it enables efficient validation of source and target data throughout the ETL process.
Q2. Which SCD type is most commonly asked in ETL interviews?
SCD Type 2 is the most frequently asked Slowly Changing Dimension concept in Informatica ETL interviews.
Interviewers commonly expect candidates to explain:
- How SCD Type 2 works
- Historical data preservation
- Effective date handling
- End date logic
- Current flag usage
- Surrogate key generation
- SQL queries to validate historical records
- Real-time project scenarios involving SCD Type 2
A clear understanding of SCD Type 2 is considered essential for ETL Testing roles.
Q3. What is the key focus in Informatica Testing interviews?
The primary focus in Informatica Testing interviews is a combination of mapping logic and SQL validation.
Interviewers typically evaluate whether candidates can:
- Understand Source-to-Target (S2T) mappings.
- Validate transformation logic.
- Write SQL queries for ETL validation.
- Compare source and target data.
- Identify ETL defects.
- Validate Slowly Changing Dimensions.
- Perform data reconciliation.
- Analyze Informatica session logs.
- Explain real-time ETL testing scenarios.
Candidates who demonstrate strong SQL skills along with a solid understanding of ETL mappings, business rules, and Informatica workflows are generally well-prepared for Informatica ETL Testing interviews.