1. What is ETL Testing? (Definition + Example)
ETL Testing is the process of validating that data is correctly Extracted, Transformed, and Loaded (ETL) from various source systems into a target data warehouse (DW). The objective is to ensure that the ETL process accurately transfers data while applying all required business rules, transformation logic, data quality standards, and performance requirements.
ETL Testing verifies that the data available in the data warehouse is complete, consistent, accurate, and reliable for reporting and analytics. It helps identify issues such as missing records, duplicate records, incorrect transformations, data truncation, referential integrity violations, and performance bottlenecks before the data is consumed by business users.
A comprehensive ETL Testing process typically includes:
- Validating data extraction from source systems.
- Verifying transformation logic and business rules.
- Comparing source and target data.
- Validating Source-to-Target (S2T) mappings.
- Checking record counts and aggregate values.
- Identifying duplicate and missing records.
- Validating Slowly Changing Dimensions (SCD).
- Performing data reconciliation.
- Verifying audit columns.
- Ensuring ETL jobs meet performance Service Level Agreements (SLAs).
ETL Testing plays a critical role in maintaining the integrity of enterprise reporting systems and ensuring that business decisions are based on trustworthy data.
Real-World Example (CGI Project Context)
Consider a healthcare or banking data warehouse project handled by CGI, where data from multiple operational systems is consolidated into an Enterprise Data Warehouse for reporting and analytics.
The ETL process extracts data from various sources, applies business transformations, and loads the processed data into fact and dimension tables. ETL testers validate each stage of this process to ensure data quality and correctness.
Source Systems
The project receives data from multiple business systems, including:
- OLTP databases.
- Flat files.
- APIs.
These systems generate transactional and master data that serve as input for the ETL process.
Target System
The transformed data is loaded into an Enterprise Data Warehouse (EDW) that supports reporting and analytical applications.
The data warehouse typically contains:
- Fact tables.
- Dimension tables.
- Historical business data.
- Aggregated metrics.
This centralized repository provides consistent and integrated data for business intelligence.
Source vs Target Record Count Validation
One of the primary ETL validations is ensuring that the number of records extracted from the source matches the number of records loaded into the target after applying the required business rules.
ETL testers verify:
- Source record counts.
- Staging record counts.
- Target record counts.
- Reject record counts.
- Missing or duplicate records.
Any mismatch is investigated by reviewing filters, joins, transformation logic, and reject tables.
Transformation Logic Validation
Transformation testing ensures that all business rules defined in the Source-to-Target (S2T) mapping are correctly implemented.
Typical transformation validations include:
- Business rule implementation.
- Calculated fields.
- Data standardization.
- Currency conversion.
- Tax calculations.
- Derived columns.
- Lookup transformations.
ETL testers compare expected values with the transformed values stored in the target warehouse.
SCD Type 1 & Type 2 Handling
Dimension data such as customer information or account details often changes over time.
ETL testers validate:
SCD Type 1
- Existing records are updated.
- Historical values are overwritten.
- No history is maintained.
SCD Type 2
- New records are inserted for changes.
- Historical records are preserved.
- Effective dates and end dates are maintained.
- Current flags are updated correctly.
Proper SCD validation ensures accurate historical reporting and trend analysis.
Audit Field Validation
Audit columns help track ETL execution and provide complete traceability.
Common audit fields include:
- batch_id
- load_date
- checksum
ETL testers verify that these columns are populated correctly for every batch execution and accurately reflect the ETL process.
Performance Tuning for Nightly Loads
Enterprise ETL jobs typically execute during scheduled batch windows. Performance testing ensures that nightly ETL jobs complete within the defined SLA.
ETL testers validate:
- Batch completion time.
- Resource utilization.
- Throughput.
- Parallel execution.
- Query performance.
Meeting SLA requirements ensures that business reports are available on schedule.
CGI Interview Expectations
For ETL Testing roles, CGI interviewers typically assess both technical knowledge and practical project experience.
Candidates are generally expected to demonstrate:
- Strong ETL fundamentals.
- Advanced SQL skills.
- Source-to-Target (S2T) mapping knowledge.
- Data warehouse concepts.
- SCD Type 1 and Type 2.
- Change Data Capture (CDC).
- Data reconciliation.
- Performance tuning concepts.
- Real-time project scenarios.
- Production support experience.
Interviewers often focus on how candidates have solved real ETL issues and validated complex business transformations in previous projects.
Data Warehouse Flow – Source → Staging → Transform → Load → Reporting
A typical enterprise ETL architecture consists of several layers, each performing a specific role in moving data from operational systems to business reports.
Source Layer
The Source layer contains operational systems where business transactions originate.
Common source systems include:
- OLTP systems.
- CRM applications.
- ERP systems.
- CSV files.
- XML files.
- JSON files.
- External APIs.
These systems provide raw business data for ETL processing.
Staging Layer
The Staging layer serves as a temporary landing area where extracted data is stored before transformations are applied.
Its responsibilities include:
- Temporary data storage.
- Initial validation.
- Record count verification.
- Data reconciliation.
- ETL restartability.
Only minimal transformations are performed at this stage to preserve the original source data.
Transformation Layer
The Transformation layer is where business rules are applied to convert raw data into meaningful business information.
Common transformations include:
- Business rule implementation.
- JOIN operations.
- Lookup transformations.
- Aggregations.
- Data cleansing.
- Data standardization.
- Derived columns.
- SCD handling.
ETL testers verify that all transformations comply with the Source-to-Target mapping document.
Load Layer
After transformation, processed data is loaded into the Enterprise Data Warehouse.
The warehouse typically contains:
- Fact tables.
- Dimension tables.
- Historical records.
- Aggregated datasets.
The load process also enforces constraints such as primary keys, foreign keys, and referential integrity.
Reporting Layer
The Reporting layer provides business users with dashboards, reports, and analytical insights.
Common reporting platforms include:
- Power BI.
- Tableau.
- Cognos.
- SAP BusinessObjects.
These tools use warehouse data to support operational and strategic decision-making.
Key Testing Focus
Throughout the ETL lifecycle, testers perform several critical validation activities.
Source-to-Target (S2T) Mapping Validation
Verifies that every source field is correctly mapped to the target according to business requirements.
Data Reconciliation
Ensures that:
- Record counts match.
- Totals match.
- No missing records exist.
- No duplicate records exist.
Referential Integrity Validation
Confirms that every foreign key in fact tables references a valid primary or surrogate key in the corresponding dimension table.
Aggregate Validation
Verifies calculations such as:
- SUM.
- COUNT.
- AVG.
- MIN.
- MAX.
These calculations must match expected business results.
Performance Validation
Ensures that ETL jobs complete within defined Service Level Agreements (SLAs) while efficiently utilizing available system resources.
ETL Architecture – Tester’s Perspective (CGI)
From a tester’s perspective, the ETL architecture consists of several interconnected components that work together to move and validate enterprise data.
Understanding each component helps testers perform accurate validation and identify defects efficiently.
ETL Tool Layer
The ETL Tool Layer is responsible for extracting, transforming, and loading data between source systems and the data warehouse.
Common ETL tools used in CGI projects include:
- Informatica.
- Microsoft SSIS.
- Ab Initio.
Although these tools differ in implementation, ETL testing principles remain the same.
ETL testers validate:
- Data movement.
- Transformation logic.
- Business rules.
- Workflow execution.
- Error handling.
Control Tables
Control tables store metadata related to ETL execution and provide operational monitoring.
Typical information includes:
- Job status.
- Batch ID.
- Start time.
- End time.
- Source row counts.
- Target row counts.
- Reject counts.
- Execution status.
ETL testers verify that control tables accurately reflect every ETL execution.
Audit & Reconciliation Framework
The audit framework captures execution details that help monitor ETL quality and simplify troubleshooting.
Typical audit information includes:
- Source record counts.
- Target record counts.
- Reject counts.
- Duplicate counts.
- Hash totals.
- Batch identifiers.
- Load timestamps.
Audit validation ensures complete traceability and supports production issue analysis.
Parallel Processing & Partitioning
Enterprise ETL workflows often process millions of records every day.
To improve performance, ETL tools divide large datasets into multiple partitions that are processed simultaneously.
Benefits include:
- Faster ETL execution.
- Improved CPU utilization.
- Better scalability.
- Reduced batch processing time.
ETL testers validate:
- Partitioning strategy.
- Parallel execution.
- Data distribution.
- Record completeness.
- Absence of duplicate or missing records.
Tester Responsibilities
From a CGI ETL testing perspective, testers are responsible for ensuring that the entire ETL process meets functional, data quality, and performance requirements.
Key responsibilities include:
- Validating data accuracy.
- Ensuring data completeness.
- Verifying Source-to-Target mappings.
- Performing data reconciliation.
- Checking referential integrity.
- Validating transformation logic.
- Testing ETL restartability.
- Monitoring audit and control tables.
- Ensuring SLA compliance.
- Supporting production issue analysis.
A strong understanding of ETL architecture, SQL, data warehousing, and real-world validation scenarios enables testers to effectively verify enterprise ETL solutions and deliver high-quality data for business reporting and analytics.
4. CGI ETL Testing Interview Questions & Answers (Basic → Advanced)
Basic ETL Testing Questions (1–15)
1. What is ETL Testing?
ETL (Extract, Transform, Load) testing is the process of validating data extraction, transformation, and loading processes to ensure data moves correctly from source systems to the target data warehouse. The objective of ETL testing is to verify that data is extracted accurately, transformed according to business rules, and loaded into the target system without any data loss, duplication, or corruption.
ETL testing focuses on ensuring data quality, consistency, completeness, and integrity throughout the entire ETL pipeline. It also verifies that business logic defined during the transformation phase is correctly implemented.
Key objectives of ETL testing include:
- Validating data extraction from source systems.
- Verifying transformation rules.
- Ensuring accurate data loading into the target.
- Checking data completeness and consistency.
- Detecting duplicate, missing, or invalid records.
- Validating business rules and calculations.
2. Why is ETL Testing Important in CGI Projects?
ETL testing is important in CGI projects because CGI works with enterprise clients across industries such as banking, healthcare, insurance, retail, manufacturing, and government. These organizations rely heavily on accurate data for reporting, analytics, compliance, and strategic decision-making.
If incorrect or incomplete data is loaded into the data warehouse, it can lead to poor business decisions, inaccurate reports, financial losses, and regulatory compliance issues. ETL testing minimizes these risks by ensuring data accuracy before it reaches business users.
Importance of ETL testing in CGI projects includes:
- Prevents incorrect business reports.
- Ensures regulatory compliance.
- Improves data quality.
- Detects transformation defects early.
- Prevents duplicate and missing records.
- Builds confidence in enterprise reporting.
3. What is a Data Warehouse?
A data warehouse is a centralized repository optimized for reporting and analytics. It stores historical and integrated data collected from multiple operational systems, making it easier for organizations to analyze business performance and generate reports.
Unlike transactional databases, a data warehouse is designed for read-intensive analytical queries rather than frequent updates.
Characteristics of a data warehouse include:
- Stores historical data.
- Integrates data from multiple source systems.
- Supports business intelligence reporting.
- Optimized for analytical queries.
- Provides a single version of business data.
4. What is a Staging Area?
A staging area is a temporary storage location where raw extracted data is stored before transformation and loading into the target data warehouse.
The staging area isolates source systems from transformation logic, allowing ETL processes to perform validation, cleansing, filtering, and formatting before loading data into the warehouse.
Functions of the staging area include:
- Temporary storage of extracted data.
- Data validation.
- Data cleansing.
- Data standardization.
- Error identification.
- Preparing data for transformation.
5. What is S2T Mapping?
S2T (Source-to-Target) Mapping is a document that defines column-level mappings and transformation rules between source and target systems.
It serves as the primary reference document for ETL developers and testers, describing how each source column should be transformed and loaded into the target database.
An S2T mapping document typically includes:
- Source table names.
- Source column names.
- Target table names.
- Target column names.
- Data types.
- Transformation rules.
- Business rules.
- Lookup information.
- Default values.
- Validation conditions.
6. What is a Fact Table?
A fact table stores measurable business metrics and transactional data within a data warehouse.
Fact tables typically contain numeric values that represent business events, such as sales amount, quantity sold, revenue, profit, or transaction count. They also contain foreign keys that link to related dimension tables.
Examples of measures stored in a fact table include:
- Sales Amount
- Quantity Sold
- Revenue
- Profit
- Order Count
- Transaction Amount
7. What is a Dimension Table?
A dimension table stores descriptive attributes that provide context to the numerical data stored in fact tables.
Dimension tables help users analyze business metrics by categories such as customer, product, location, department, or time.
Examples of dimension tables include:
- Customer Dimension
- Product Dimension
- Employee Dimension
- Store Dimension
- Time Dimension
- Geography Dimension
8. What are Audit Fields?
Audit fields are metadata columns used to track ETL processing and maintain data lineage.
These fields provide information about when, how, and from which source the data was loaded into the target system.
Common audit fields include:
- batch_id – Identifies the ETL execution batch.
- load_date – Records the date and time when data was loaded.
- source_system – Specifies the originating source system.
- checksum – Helps verify data integrity and detect changes.
Audit fields are useful for troubleshooting, monitoring ETL jobs, and performing data reconciliation.
9. What is Data Reconciliation?
Data reconciliation is the process of comparing source and target data to ensure accuracy, completeness, and consistency after ETL processing.
The purpose of reconciliation is to verify that all expected records have been successfully extracted, transformed, and loaded without any data loss or corruption.
Data reconciliation checks include:
- Record count comparison.
- Duplicate record validation.
- Missing record detection.
- Data value comparison.
- Aggregate value validation.
- Business rule verification.
10. What is Full Load?
Full load refers to reloading all available data from the source system into the target database regardless of whether the data has changed.
This method is commonly used during:
- Initial data warehouse implementation.
- Data migration.
- Complete database refresh.
- Disaster recovery.
Although full loads are simple to implement, they consume more processing time and system resources.
11. What is Incremental Load?
Incremental load is the process of loading only new or changed records since the previous ETL execution.
Instead of processing the entire dataset, incremental loading improves performance by transferring only the delta data.
Benefits of incremental loading include:
- Faster execution.
- Reduced database load.
- Lower network usage.
- Improved ETL performance.
- Reduced storage requirements.
12. What is Primary Key Testing?
Primary key testing validates that primary key values remain unique and non-null after ETL processing.
Since primary keys uniquely identify each record, any duplicate or null value indicates a serious data integrity issue.
Primary key testing verifies:
- Uniqueness.
- Non-null values.
- No duplicate records.
- Correct primary key mapping.
13. What is Reject Data?
Reject data consists of invalid records that fail predefined validation rules during ETL processing.
Instead of loading incorrect data into the warehouse, ETL jobs move these records into reject tables or error files for further analysis.
Common reasons for rejected data include:
- Null mandatory fields.
- Invalid date formats.
- Duplicate primary keys.
- Invalid reference values.
- Data type mismatches.
- Business rule violations.
14. What is Data Profiling?
Data profiling is the process of analyzing source data patterns before ETL execution.
It helps identify data quality issues that may affect ETL processing and assists developers in designing appropriate transformation rules.
Data profiling analyzes:
- Null values.
- Duplicate records.
- Data distributions.
- Data types.
- Value ranges.
- Missing values.
- Pattern consistency.
15. What is Null Validation?
Null validation ensures that null values are handled according to business rules during ETL processing.
Some columns require mandatory values, while others allow nulls. ETL testing verifies that these rules are correctly enforced.
Null validation includes checking:
- Mandatory fields are not null.
- Optional fields allow null values.
- Default values are assigned where applicable.
- Transformation logic handles null values correctly.
Intermediate ETL QA Interview Questions
16. Explain SCD Type 1.
Slowly Changing Dimension (SCD) Type 1 overwrites existing data with new values whenever changes occur.
No historical information is maintained, and only the latest value is stored.
Characteristics of SCD Type 1:
- No history maintained.
- Existing records are updated.
- Simple implementation.
- Less storage requirement.
- Suitable for correcting errors.
17. Explain SCD Type 2.
SCD Type 2 maintains complete historical data by inserting a new record whenever attribute values change.
The previous record is marked as inactive using effective dates, expiry dates, or active flags.
Characteristics include:
- Preserves historical data.
- Inserts new rows.
- Uses surrogate keys.
- Maintains effective dates.
- Supports historical reporting.
18. How do you Test SCD Type 2?
Testing SCD Type 2 involves verifying that history is preserved correctly whenever changes occur.
The tester validates:
- A new row is inserted after attribute changes.
- Old record expiry date is updated.
- Current record flag is maintained.
- Surrogate key generation is correct.
- Historical records remain unchanged.
19. What is a Surrogate Key?
A surrogate key is a system-generated unique identifier used in dimension tables.
Unlike business keys, surrogate keys have no business meaning and remain stable even if business identifiers change.
Advantages include:
- Unique identification.
- Better performance.
- Easier joins.
- Supports SCD Type 2.
- Simplifies warehouse design.
20. How do you Validate Surrogate Keys?
Surrogate key validation ensures:
- Every surrogate key is unique.
- No null surrogate keys exist.
- Proper mapping between business key and surrogate key.
- Correct key generation during inserts.
21. What is CDC?
CDC (Change Data Capture) is a mechanism used for delta processing.
It captures only inserted, updated, and deleted records since the previous ETL execution, reducing processing time.
CDC improves ETL efficiency by avoiding unnecessary full data loads.
22. How do you Test CDC Logic?
CDC testing compares before and after snapshots of source and target data.
The validation confirms that:
- New records are inserted.
- Updated records are modified.
- Deleted records are handled correctly.
- No unchanged records are processed unnecessarily.
23. What is Referential Integrity Testing?
Referential integrity testing ensures that every foreign key in the fact table has a matching primary key in the corresponding dimension table.
This prevents orphan records and maintains consistency between related tables.
Validation includes:
- Foreign key existence.
- Parent-child relationship verification.
- Missing dimension record detection.
- Correct joins between tables.
24. What is Aggregation Testing?
Aggregation testing validates calculations such as SUM, COUNT, AVG, MIN, and MAX during ETL processing.
The tester compares aggregated values between source and target systems to ensure calculation accuracy.
Common aggregation validations include:
- Total sales.
- Customer count.
- Average revenue.
- Product quantities.
- Monthly summaries.
25. What is Lookup Testing?
Lookup testing verifies that reference data mappings are correctly applied during ETL transformations.
It ensures that source values are matched with the appropriate reference values stored in lookup tables.
Typical lookup validations include:
- Product code mapping.
- Country code mapping.
- Customer category mapping.
- Department mapping.
- Status code conversion.
26. What is Deduplication?
Deduplication is the process of removing duplicate business keys before loading data into the target system.
It ensures that only unique and valid records are stored in the warehouse.
Benefits include:
- Improved data quality.
- Reduced storage.
- Accurate reporting.
- Better business analytics.
27. How do you Test Deduplication Logic?
Deduplication logic is tested by validating that duplicate business keys are correctly identified and removed.
Common validation techniques include:
- Using GROUP BY queries.
- Using ROW_NUMBER() window functions.
- Comparing record counts.
- Verifying unique business keys.
28. What is a Late-Arriving Dimension?
A late-arriving dimension occurs when a fact record reaches the data warehouse before its corresponding dimension record.
ETL processes must handle these situations carefully to maintain referential integrity without losing transactional data.
29. What is Data Skew?
Data skew refers to uneven data distribution across partitions during ETL processing.
Some processing nodes receive significantly more data than others, resulting in poor performance and longer execution times.
Common causes include:
- Uneven business key distribution.
- Large customer datasets.
- Partition imbalance.
- Poor hashing strategies.
30. What is Restartability Testing?
Restartability testing ensures that an ETL job resumes correctly after failure without creating duplicate records, missing data, or inconsistent results.
It validates that checkpoint mechanisms work correctly and the ETL process can continue safely from the point of failure instead of restarting the entire workflow.
Restartability testing verifies:
- Failed jobs resume successfully.
- No duplicate records are created.
- No data is lost.
- Checkpoints work correctly.
- Batch recovery is successful.
- Data consistency is maintained throughout the restart process.
31. What is Metadata Testing?
Metadata testing is the process of validating the metadata used during ETL processing. Metadata defines the structure of the data, including column names, data types, column lengths, constraints, and other schema-related information. The objective is to ensure that the source and target structures are consistent and that the ETL process correctly maps data according to the metadata specifications.
Metadata testing helps prevent data truncation, type conversion errors, and schema mismatches that could affect data quality.
Metadata testing validates:
- Column names.
- Data types.
- Column lengths.
- Precision and scale.
- Nullable and non-nullable columns.
- Primary and foreign key definitions.
- Default values.
- Table structures.
Proper metadata validation ensures that the ETL process functions correctly even after database schema changes.
32. What is Data Lineage?
Data lineage is the process of tracking data from its original source through every stage of transformation until it reaches the final reports, dashboards, or analytical systems.
It provides complete visibility into how data moves across different systems and how it is transformed during ETL processing. Data lineage is especially important for troubleshooting, auditing, compliance, and impact analysis.
Data lineage helps to:
- Track data from source to report.
- Understand data transformations.
- Identify the origin of data.
- Support regulatory compliance.
- Perform impact analysis.
- Simplify root cause investigation.
- Improve data governance.
Maintaining accurate data lineage enables organizations to trust their reporting and quickly identify issues in complex ETL pipelines.
33. What is Threshold Testing?
Threshold testing verifies that an ETL job behaves correctly when the number of rejected records exceeds a predefined limit.
Most enterprise ETL applications define acceptable rejection thresholds. If the reject count crosses the configured limit, the ETL job should automatically fail or trigger alerts instead of loading unreliable data into the target system.
Threshold testing verifies:
- Reject count calculation.
- Configured rejection limits.
- Job failure conditions.
- Alert generation.
- Error logging.
- Batch rollback, if applicable.
This testing ensures that poor-quality data does not enter the data warehouse.
34. What is Hashing in ETL?
Hashing is a technique used in ETL processes to efficiently detect data changes between source and target records.
Instead of comparing every individual column, ETL systems generate a hash value based on selected columns. If the hash values differ, the record is identified as changed and processed accordingly.
Hashing significantly improves ETL performance, especially during incremental loads and Change Data Capture (CDC).
Common uses of hashing include:
- Detecting changed records.
- Supporting incremental loads.
- Improving ETL performance.
- Simplifying record comparison.
- Reducing processing time.
- Identifying duplicate records.
Hashing is widely used in large enterprise ETL environments where millions of records must be compared efficiently.
35. What is SLA Testing?
SLA (Service Level Agreement) testing verifies that ETL jobs complete within the agreed time limits defined by business or operational requirements.
Many organizations have strict processing windows, especially for overnight batch jobs. ETL testing ensures that all extraction, transformation, and loading activities finish before business reporting begins.
SLA testing validates:
- Job completion time.
- Batch execution duration.
- Processing within scheduled windows.
- Performance under peak loads.
- Resource utilization.
- Compliance with operational SLAs.
Failure to meet SLA timelines can delay reporting, impact business operations, and violate contractual commitments.
Advanced / Scenario-Based ETL Interview Questions
36. How do you Handle Record Count Mismatch?
A record count mismatch occurs when the number of records in the source system does not match the number of records loaded into the target system.
When this happens, the tester systematically investigates each stage of the ETL process to identify where records were lost, duplicated, or filtered.
The investigation typically includes checking:
- Source record count.
- Target record count.
- Applied filters.
- Join conditions.
- Reject records.
- CDC logic.
- Duplicate elimination.
- Transformation rules.
After identifying the root cause, the tester validates that the ETL logic correctly processes all expected records.
37. How do you Validate Null Handling?
Null handling validation ensures that null values are processed according to business requirements throughout the ETL workflow.
Different business rules may require null values to be accepted, replaced with default values, or rejected entirely.
Null validation includes verifying:
- Mandatory fields do not contain null values.
- Optional fields allow null values.
- Default values are correctly assigned.
- Invalid null records are rejected.
- Transformation logic handles nulls correctly.
- Reports display expected values.
Proper null validation prevents data quality issues and ensures consistent reporting.
38. How do you Test ETL Performance?
ETL performance testing verifies that the ETL process completes efficiently while handling large volumes of data within acceptable time limits.
Performance testing identifies bottlenecks and validates that optimization techniques improve processing speed.
Common optimization techniques include:
- Partitioning large tables.
- Creating appropriate indexes.
- Parallel execution of ETL jobs.
- Query optimization.
- Efficient join strategies.
- Optimized transformation logic.
- Resource monitoring.
The goal is to ensure that ETL jobs complete successfully within business-defined SLA windows.
39. How do you Test Incremental Loads?
Incremental load testing validates that only new or modified records are processed during each ETL execution.
The tester verifies that watermark logic correctly identifies records that have changed since the previous load.
Validation activities include:
- Checking watermark values.
- Verifying inserted records.
- Validating updated records.
- Ensuring unchanged records are skipped.
- Confirming deleted records are handled correctly.
- Comparing source and target snapshots.
Successful incremental load testing ensures efficient processing while maintaining data accuracy.
40. How do you Test Multi-Source Joins?
Many enterprise ETL processes combine data from multiple source systems. Multi-source join testing verifies that these joins produce accurate and complete results.
The tester validates that join conditions are correctly implemented and that the relationships between datasets remain consistent.
Testing includes:
- Validating join keys.
- Checking join cardinality.
- Verifying inner and outer joins.
- Detecting duplicate records.
- Identifying missing records.
- Confirming business rule implementation.
Incorrect join logic can significantly impact reporting accuracy and downstream analytics.
41. How do you Test Aggregation Failures?
Aggregation testing ensures that summary calculations generated during ETL processing are accurate.
If aggregation failures occur, the tester independently recalculates totals using SQL and compares them with the target values.
Validation typically includes:
- SUM calculations.
- COUNT validation.
- AVG calculations.
- MIN and MAX verification.
- Grouping logic.
- Business rule validation.
Accurate aggregation is critical for financial reporting and business intelligence dashboards.
42. How do you Test Timezone Conversions?
Timezone conversion testing verifies that timestamps are correctly converted between different geographic regions and time zones.
Organizations operating globally often store timestamps in UTC and display them in local time zones.
Testing verifies:
- UTC conversion.
- Local timezone conversion.
- Daylight Saving Time handling.
- Timestamp consistency.
- Date boundary validation.
- Historical timestamp accuracy.
Proper timezone validation prevents reporting errors across international business operations.
43. How do you Test Re-runs?
Re-run testing verifies that an ETL job can be executed multiple times without creating duplicate records or corrupting existing data.
This is especially important when ETL jobs fail and need to be restarted.
Validation includes:
- No duplicate record creation.
- Correct checkpoint recovery.
- Data consistency.
- Successful batch restart.
- Proper audit logging.
- Accurate record counts.
Effective re-run testing ensures reliable ETL recovery during production failures.
44. How do you Test Historical Data Loads?
Historical data load testing validates that backdated records are loaded correctly into the data warehouse.
Historical loads often occur during system migrations or when recovering missing data.
Testing includes:
- Backdated record validation.
- Effective date verification.
- Historical dimension updates.
- SCD validation.
- Record count comparison.
- Business rule verification.
Historical data must remain accurate to support trend analysis and long-term reporting.
45. How do you Validate Audit Tables?
Audit table validation ensures that ETL execution statistics accurately represent the processing results.
Audit tables provide important information for monitoring ETL jobs and troubleshooting failures.
The tester compares:
- Source record count.
- Target record count.
- Insert count.
- Update count.
- Reject count.
- Error count.
- Batch execution status.
- Load timestamps.
One common validation is comparing source_count against target_count to confirm successful data loading.
46. What Causes ETL Performance Bottlenecks?
ETL performance bottlenecks occur when processing time increases due to inefficient data processing or resource limitations.
Common causes include:
- Large joins.
- Data skew.
- Missing indexes.
- Poor SQL queries.
- Inefficient transformations.
- Network latency.
- Insufficient system resources.
- Large data volumes.
- Improper partitioning.
Identifying these bottlenecks helps optimize ETL performance and improve batch execution times.
47. How do you Test File-Based ETL?
File-based ETL testing validates data extracted from files such as CSV, TXT, XML, or JSON before it is loaded into the target system.
The tester verifies that the file structure and contents meet the expected format.
Validation includes:
- Header validation.
- Footer validation.
- Record count.
- File encoding.
- Delimiter verification.
- Mandatory columns.
- Data format.
- Duplicate records.
- Invalid records.
Proper file validation prevents downstream ETL failures.
48. How do you Test Schema Changes?
Schema change testing validates that ETL processes continue to function correctly after modifications to database structures.
Schema changes may include adding columns, removing columns, changing data types, or renaming fields.
Testing includes:
- Backward compatibility checks.
- Metadata validation.
- S2T mapping updates.
- ETL job execution.
- Data validation.
- Report verification.
- Regression testing.
The objective is to ensure that schema modifications do not break existing ETL workflows.
49. How do you Validate Reporting Layer Data?
Reporting layer validation ensures that reports and dashboards accurately reflect the data stored in the data warehouse.
The tester compares report results with warehouse data and verifies that business calculations remain accurate.
Validation includes:
- BI totals versus data warehouse aggregates.
- Report filters.
- Drill-down results.
- KPI calculations.
- Dashboard metrics.
- Data consistency.
- Aggregation accuracy.
This ensures business users receive reliable analytical reports.
50. Explain a Critical ETL Defect You Found.
One example of a critical ETL defect is data loss caused by incorrect transformation logic.
During testing, the source system contained 500,000 customer records, but only 495,000 records were loaded into the target warehouse. Investigation revealed that an incorrect filter condition excluded valid customer records.
Another example is wrong aggregation, where sales totals in the reporting layer did not match source transactions due to incorrect grouping logic.
A third example is SCD Type 2 failure, where historical records were overwritten instead of inserting new versions, resulting in the loss of historical data.
Typical critical ETL defects include:
- Data loss during loading.
- Incorrect aggregation results.
- Duplicate records.
- SCD Type 2 failures.
- Incorrect lookup mappings.
- Missing dimension records.
- Referential integrity violations.
- Incorrect incremental load processing.
Identifying and resolving such defects is essential to maintaining accurate enterprise reporting and ensuring business users can trust the data.
5. Real SQL Query Examples for ETL Validation
SQL plays a major role in ETL testing because it helps validate data movement, transformation logic, record counts, aggregations, duplicate records, and ETL performance. In CGI ETL testing interviews, interviewers often expect candidates to explain SQL queries along with the business purpose behind them rather than simply writing the syntax.
The following examples demonstrate some of the most commonly used SQL validations in ETL projects.
Sample Tables
Assume the following source and target tables are used during ETL validation.
Source Table
src_orders
| Column Name | Description |
| order_id | Unique order identifier |
| cust_id | Customer ID |
| amount | Order amount |
| order_date | Date of the order |
Target Table
fact_sales
| Column Name | Description |
| order_sk | Surrogate key for order |
| cust_sk | Customer surrogate key |
| sales_amt | Sales amount |
| order_date | Order date |
| batch_id | ETL batch identifier |
SQL Example 1: JOIN Validation
JOIN validation ensures that every source record has been successfully loaded into the target table.
SQL Query
SELECT COUNT(*)
FROM src_orders s
LEFT JOIN fact_sales f
ON s.order_id = f.order_sk
WHERE f.order_sk IS NULL;
Purpose
This query identifies source records that are missing in the target table.
What the query validates
- Missing records after ETL.
- Failed data loads.
- Incorrect join conditions.
- Data loss during transformation.
Expected Result
The query should return 0 rows, indicating that all source records have been successfully loaded into the target table.
SQL Example 2: GROUP BY Aggregation Validation
Aggregation validation ensures that summary calculations remain accurate after ETL processing.
Source Query
SELECT order_date,
SUM(amount)
FROM src_orders
GROUP BY order_date;
Target Query
SELECT order_date,
SUM(sales_amt)
FROM fact_sales
GROUP BY order_date;
Purpose
These queries compare daily sales totals between the source and target systems.
What the query validates
- SUM calculations.
- Data completeness.
- Aggregation logic.
- Transformation accuracy.
Expected Result
The aggregated sales amount for each order date should be identical in both the source and target tables.
SQL Example 3: Window Function – Deduplication
Window functions are commonly used to remove duplicate business records before loading data into the warehouse.
SQL Query
SELECT *
FROM
(
SELECT *,
ROW_NUMBER() OVER
(
PARTITION BY order_id
ORDER BY load_date DESC
) rn
FROM stage_orders
) t
WHERE rn = 1;
Purpose
This query keeps only the most recent record for each order while eliminating duplicate records.
What the query validates
- Duplicate record removal.
- Latest record selection.
- Business key uniqueness.
- Correct deduplication logic.
Expected Result
Only one record should exist for every order_id, and it should be the latest record based on load_date.
SQL Example 4: Performance Validation
Performance validation helps determine whether SQL queries are optimized for large datasets.
SQL Query
EXPLAIN ANALYZE
SELECT cust_sk,
SUM(sales_amt)
FROM fact_sales
GROUP BY cust_sk;
Purpose
This query analyzes the execution plan and identifies performance bottlenecks.
What the query validates
- Query execution time.
- Index usage.
- Table scans.
- Query optimization.
- Resource utilization.
Expected Result
The execution plan should use appropriate indexes and complete within the acceptable SLA time.
6. Scenario-Based ETL Testing Use Cases
Scenario-based questions are commonly asked in CGI ETL interviews because they evaluate practical problem-solving skills rather than theoretical knowledge.
Below are some frequently encountered ETL testing scenarios.
Scenario 1: Record Count Mismatch
Scenario
The source table contains 100,000 records, but the target table contains only 99,850 records after ETL execution.
Validation
The tester compares source and target counts and investigates:
- Missing records.
- Applied filters.
- Rejected records.
- Join conditions.
- Incremental load logic.
- Transformation rules.
The root cause should be identified before approving the ETL job.
Scenario 2: Null Values
Scenario
Mandatory columns in the source contain null values.
Validation
The tester verifies whether the ETL process:
- Replaces null values with defaults.
- Rejects invalid records.
- Allows nulls where permitted.
- Applies business rules correctly.
This ensures proper handling of incomplete data.
Scenario 3: Duplicate Data
Scenario
Duplicate customer records are present in the staging table.
Validation
The tester validates the deduplication logic using:
- Window functions.
- ROW_NUMBER().
- Business key validation.
- GROUP BY queries.
Only unique records should be loaded into the target.
Scenario 4: Late-Arriving Data
Scenario
A fact record arrives before the corresponding dimension record.
Validation
The ETL process should correctly handle the late-arriving dimension by:
- Creating placeholder records if required.
- Performing SCD Type 2 backdated inserts.
- Updating dimension relationships later.
- Maintaining referential integrity.
Scenario 5: Slow ETL Job
Scenario
An ETL job exceeds its SLA execution time.
Validation
The tester investigates:
- Partitioning strategy.
- Index availability.
- Query optimization.
- Parallel processing.
- Large joins.
- Data skew.
- Resource utilization.
Performance tuning recommendations are then provided.
7. ETL Tools Commonly Asked in CGI Interviews
Although CGI primarily evaluates ETL concepts, candidates are expected to be familiar with commonly used ETL tools in enterprise projects.
Some frequently discussed ETL tools include:
Informatica
Informatica is one of the most widely used enterprise ETL tools for data integration, transformation, and warehouse loading. It provides graphical workflows for building ETL pipelines and supports large-scale enterprise data processing.
Microsoft SSIS
Microsoft SQL Server Integration Services (SSIS) is Microsoft’s ETL platform used for data extraction, transformation, migration, and loading. It integrates seamlessly with SQL Server and is commonly used in Microsoft-based environments.
Ab Initio
Ab Initio is a high-performance ETL platform designed for processing massive volumes of enterprise data. It supports parallel execution and is widely used in banking, insurance, and financial services.
Pentaho
Pentaho is an open-source ETL and Business Intelligence platform that provides tools for data integration, reporting, and analytics. It is suitable for organizations looking for flexible and cost-effective ETL solutions.
Talend
Talend is an open-source ETL platform that supports cloud integration, big data processing, and enterprise data quality management. It offers a wide range of connectors for integrating multiple data sources.
Important Interview Tip
CGI interviewers emphasize conceptual clarity over tool-specific syntax. Candidates should understand ETL architecture, data validation, SQL, and business scenarios rather than focusing solely on a particular ETL tool.
8. ETL Defect Examples and Sample Test Case
Real-world ETL defects are frequently discussed during interviews to assess troubleshooting and defect analysis skills.
ETL Defect Example
Defect
SCD Type 2 record not expiring correctly.
Expected Result
The old dimension record should have:
- current_flag = ‘N’
- Appropriate expiry date.
A new record should be inserted with:
- current_flag = ‘Y’
- Updated attribute values.
- New effective date.
Actual Result
Two active records exist simultaneously for the same business key because the old record was not expired.
Severity
High
This defect can lead to incorrect historical reporting, inaccurate business analytics, and duplicate active records.
Sample Test Case
Test Scenario
Update a tracked attribute in a dimension table.
Test Steps
- Update the source dimension attribute.
- Execute the ETL job.
- Verify that a new dimension record is inserted.
- Verify that the previous record is expired.
- Validate the effective and expiry dates.
- Check the current_flag values.
Expected Result
- A new row is inserted.
- The previous row is expired.
- Only one active record exists.
- Historical data is preserved.
9. ETL Testing Quick Revision Sheet
Before attending a CGI ETL interview, candidates should revise the following topics thoroughly.
ETL Fundamentals
- ETL architecture.
- ETL workflow.
- Source systems.
- Staging area.
- Data warehouse.
- Data marts.
Mapping Validation
- S2T mapping.
- Business rules.
- Column mapping.
- Data type validation.
- Metadata validation.
Slowly Changing Dimensions
- SCD Type 1.
- SCD Type 2.
- Surrogate keys.
- Effective dates.
- Current flags.
SQL Concepts
- INNER JOIN.
- LEFT JOIN.
- RIGHT JOIN.
- GROUP BY.
- HAVING.
- Aggregate functions.
- Window functions.
- ROW_NUMBER().
- RANK().
- COUNT().
- SUM().
Performance and Data Validation
- Record reconciliation.
- Performance tuning.
- Partitioning.
- Indexing.
- Parallel execution.
- Incremental loads.
- CDC validation.
- Audit table validation.
10. FAQs – Snippet-Ready
Q1. Does CGI ask SQL in ETL testing interviews?
Answer:
Yes, SQL validation is mandatory in CGI ETL testing interviews. SQL is one of the most important skills expected from ETL testers because it is used extensively to validate data throughout the ETL process.
Interviewers commonly ask candidates to write SQL queries for validating:
- Record count comparisons.
- JOIN validations.
- Data reconciliation.
- Duplicate record detection.
- Aggregation validation using SUM(), COUNT(), and AVG().
- Null value validation.
- Incremental load verification.
- Referential integrity checks.
- Window functions such as ROW_NUMBER() and RANK().
Candidates should have a strong understanding of SQL concepts and be able to explain the business purpose of each query rather than simply writing the syntax.
Q2. Is Informatica mandatory for CGI ETL roles?
Answer:
No. Informatica is not mandatory for most CGI ETL testing roles. While familiarity with popular ETL tools like Informatica, Microsoft SSIS, Ab Initio, Pentaho, or Talend can be an added advantage, CGI interviewers primarily focus on a candidate’s conceptual understanding of ETL testing.
They expect candidates to demonstrate knowledge of:
- ETL architecture.
- Data warehouse concepts.
- Source-to-Target (S2T) mapping.
- SQL query writing.
- Data validation techniques.
- Slowly Changing Dimensions (SCD Type 1 and Type 2).
- Change Data Capture (CDC).
- Data reconciliation.
- Performance testing.
- Scenario-based ETL problem-solving.
A candidate with strong ETL fundamentals and SQL skills can perform well even without extensive experience in a specific ETL tool.
Q3. How many ETL interview rounds are typically conducted at CGI?
Answer:
CGI typically conducts one to two technical interview rounds for ETL testing positions, although the exact interview process may vary depending on the project, client requirements, and candidate experience.
The technical rounds generally assess:
- ETL testing fundamentals.
- SQL query writing and optimization.
- Data warehouse concepts.
- ETL architecture and workflow.
- SCD Type 1 and Type 2 scenarios.
- Data reconciliation techniques.
- Record count and aggregation validation.
- Performance tuning concepts.
- Real-time ETL testing scenarios.
- Defect analysis and troubleshooting.
For experienced candidates, interviewers may also discuss project-specific challenges, production issues, optimization techniques, and practical ETL testing experience to evaluate problem-solving and analytical skills.