1. What is ETL Testing? (Definition + Example)
ETL (Extract, Transform, Load) Testing is the process of validating data during the Extract, Transform, and Load operations to ensure data accuracy, completeness, consistency, and performance when data moves from source systems to a Data Warehouse (DW) or reporting layer. The primary objective of ETL testing is to verify that data is extracted correctly, transformed according to business rules, and loaded into the target system without data loss, duplication, or corruption.
ETL testing is an essential part of data warehouse projects because business reports, dashboards, and analytics depend on accurate and reliable data. ETL QA engineers validate source-to-target mappings, transformation logic, business rules, data integrity, historical data, and performance to ensure that the final data supports informed business decisions.
Real-Time Example
Consider a banking organization that processes millions of financial transactions every day. Data from operational systems is consolidated into a central data warehouse for reporting and regulatory compliance.
Source
The source system is a Banking Transaction System, where customer transactions, account information, and payment details are stored.
Transform
Before loading the data into the warehouse, several business transformations are applied, including:
- Currency conversion
- Data cleansing
- Deduplication
- SCD logic
These transformations standardize and enrich the data while ensuring compliance with business rules.
Target
The processed data is loaded into:
- Fact_Transactions table
- Dim_Customer table
These tables are used by reporting tools to generate business dashboards and analytical reports.
ETL Testing Focus
During ETL testing, the QA engineer validates that:
- Business rules are correctly implemented.
- Data transformations produce the expected results.
- Historical data is maintained accurately.
- Incremental and full loads function correctly.
- Reports built on Data Warehouse data are reliable.
The goal is to ensure that the warehouse contains trusted data that business users can confidently use for reporting and decision-making.
2. Data Warehouse (DW) Flow
A Data Warehouse follows a structured pipeline where data moves through multiple processing layers before it becomes available for reporting and analytics. ETL QA engineers validate each layer to ensure data quality and consistency throughout the process.
DW Flow:
Source → Staging → Transform → Load → Reporting
Each layer has a specific role in the ETL process.
Source Layer
The Source Layer contains operational business data collected from various systems.
Common source systems include:
- OLTP databases
- Flat files
- APIs
These systems generate transactional data that serves as the input for ETL processing.
Staging Layer
The Staging Layer is a temporary storage area where raw extracted data is placed before any business transformations are applied.
Characteristics include:
- Raw extracted data
- Temporary storage
- Minimal validation
- Recovery point for ETL failures
This layer isolates source systems from transformation processing.
Transformation Layer
The Transformation Layer applies business rules to convert raw data into standardized information suitable for reporting.
Typical transformation activities include:
- Business rules
- Calculations
- SCD logic
Additional transformations may include lookups, deduplication, format conversions, and data cleansing.
Load Layer
After transformation, the processed data is loaded into the target warehouse.
The Load Layer primarily contains:
- Fact tables
- Dimension tables
ETL QA engineers validate successful data loading, referential integrity, and correct incremental processing.
Reporting Layer
The Reporting Layer provides business users with access to validated data through Business Intelligence platforms.
Common BI tools include:
- Power BI
- Tableau
These tools generate dashboards, reports, KPIs, and analytical insights based on the data warehouse.
3. ETL Testing Interview Questions Cognizant – What Interviewers Expect
ETL testing interviews at companies such as Cognizant generally evaluate both conceptual understanding and practical experience. Interviewers expect candidates to demonstrate strong SQL skills, knowledge of data warehouse architecture, and the ability to troubleshoot real-world ETL issues.
Common areas of focus include:
- Strong SQL validation
- S2T mapping understanding
- Data warehouse concepts
- Real-time ETL defect scenarios
- Tool knowledge (Informatica, SSIS, Ab Initio)
Candidates should also be prepared to discuss production support experience, ETL performance tuning, Slowly Changing Dimensions (SCD), Change Data Capture (CDC), and data reconciliation.
4. ETL Testing Interview Questions & Answers (Basic → Advanced)
The following section contains frequently asked ETL testing interview questions commonly discussed in Cognizant-style ETL interviews, covering topics from basic concepts to advanced SQL validation and real-time scenarios.
A. Basic ETL Interview Questions
Q1. What is ETL?
ETL stands for Extract, Transform, and Load. It is a data integration process used to collect data from multiple source systems, apply business transformations, and load the processed data into a target Data Warehouse (DW) or reporting system.
The ETL process consists of three stages:
- Extract: Retrieve data from source systems.
- Transform: Apply business rules, cleansing, calculations, and standardization.
- Load: Store the processed data in the target warehouse.
ETL enables organizations to consolidate data from multiple sources into a centralized repository for analytics and reporting.
Q2. What is ETL Testing?
ETL Testing ensures that data extracted from source systems is correctly transformed according to business requirements and successfully loaded into the target system.
ETL testing validates:
- Data accuracy.
- Data completeness.
- Business rule implementation.
- Transformation logic.
- Data integrity.
- Performance.
- Source-to-target reconciliation.
Unlike functional testing, ETL testing focuses on validating data rather than application user interfaces.
Q3. What is a Data Warehouse?
A Data Warehouse (DW) is a centralized repository that stores integrated, historical, and structured data collected from multiple operational systems.
It is designed for:
- Reporting.
- Analytics.
- Business Intelligence (BI).
- Decision-making.
Unlike OLTP systems, data warehouses are optimized for complex analytical queries rather than transaction processing.
Q4. What are Source Systems?
Source systems are operational systems from which data is extracted during ETL processing.
Examples include:
- OLTP databases.
- CRM systems.
- ERP systems.
- Flat files.
- APIs.
These systems generate the business data that is later transformed and loaded into the warehouse.
Q5. What is a Staging Table?
A staging table is a temporary database table used to store raw extracted data before business transformations are applied.
The staging layer helps:
- Hold extracted data.
- Separate extraction from transformation.
- Support ETL restart and recovery.
- Perform preliminary data validation.
Data stored in staging tables is generally temporary and removed after successful ETL processing.
B. Data Warehouse Interview Questions
Q6. What is a Fact Table?
Fact tables store measurable or quantitative business metrics used for reporting and analysis.
Examples include:
- Sales amount.
- Transaction amount.
- Quantity sold.
- Revenue.
Fact tables typically contain foreign keys that reference related dimension tables.
Q7. What is a Dimension Table?
Dimension tables store descriptive attributes that provide context for the measurements stored in fact tables.
Examples include:
- Customer.
- Product.
- Time.
- Store.
These tables allow users to filter, group, and analyze business data from different perspectives.
Q8. What is Star Schema?
A Star Schema is a Data Warehouse schema consisting of one central fact table connected directly to multiple dimension tables.
Characteristics include:
- Central fact table.
- Multiple surrounding dimension tables.
- Simple relationships.
- Fast analytical queries.
- Easy reporting.
Because of its simplicity and performance, the star schema is widely used in Data Warehouse projects.
Q9. Difference between OLTP and OLAP?
| OLTP | OLAP |
| Handles transactions | Supports analytics |
| Frequent INSERT, UPDATE, DELETE operations | Mostly READ operations |
| Optimized for transactional processing | Optimized for analytical queries |
| Highly normalized | Often denormalized |
| Used in operational systems | Used in Data Warehouses |
In simple terms, OLTP handles transactions, while OLAP supports analytics.
C. ETL QA & Mapping Questions
Q10. What is Source-to-Target (S2T) Mapping?
Source-to-Target (S2T) mapping is a document that defines source fields, target fields, and the transformation rules applied during ETL processing.
An S2T mapping document typically includes:
- Source columns.
- Target columns.
- Data types.
- Transformation logic.
- Lookup rules.
- Business validations.
- Default values.
ETL QA engineers use this document as the primary reference during validation.
Q11. What Validations are Done in ETL Testing?
ETL testing includes several validation activities to ensure data quality throughout the ETL process.
Common validations include:
- Record count validation.
- Data type and length validation.
- Transformation logic validation.
- Null and default value checks.
- Duplicate record checks.
Additional validations may include referential integrity, audit field verification, data reconciliation, and performance testing.
Q12. What is Data Reconciliation?
Data reconciliation is the process of comparing source and target data to ensure completeness, consistency, and accuracy after ETL processing.
Typical reconciliation activities include:
- Record count comparison.
- Aggregate validation.
- Column-level comparison.
- Missing record identification.
- Duplicate detection.
Successful reconciliation confirms that the target warehouse accurately reflects the source data.
D. SCD & History Questions (Very Important)
Q13. What is SCD Type 1?
Slowly Changing Dimension (SCD) Type 1 updates an existing record by replacing the old value with the new value.
Characteristics include:
- Overwrites old data.
- No historical information maintained.
This approach is suitable when historical tracking is not required.
Q14. What is SCD Type 2?
Slowly Changing Dimension (SCD) Type 2 preserves historical information by inserting a new record whenever tracked attributes change.
Historical tracking typically uses:
- effective_date
- expiry_date
- active_flag
This enables historical reporting and trend analysis.
Q15. How do you Test SCD Type 2?
During SCD Type 2 validation, the ETL tester verifies:
- The old record is expired.
- A new record is inserted.
- Only one active record exists.
Additional validation includes checking effective dates, expiry dates, active flags, and preservation of historical records.
Q16. What are Audit Fields?
Audit fields are metadata columns that track ETL execution and record history.
Common audit fields include:
- load_date
- batch_id
- created_ts
- updated_ts
- record_source
These fields support monitoring, troubleshooting, compliance, and data lineage.
5. Real SQL Query Examples for ETL Validation
Sample Dataset
Source Table: Source_Orders
- order_id
- cust_id
- amount
- currency
Target Table: Target_Fact_Orders
| Column |
| order_key |
| cust_key |
| amount_usd |
| load_date |
JOIN – Missing Records Validation
SELECT s.order_id
FROM source_orders s
LEFT JOIN target_fact_orders t
ON s.order_id = t.order_key
WHERE t.order_key IS NULL;
Purpose: Identifies records that exist in the source system but are missing from the target table.
GROUP BY – Aggregation Validation
SELECT cust_id,
SUM(amount)
FROM source_orders
GROUP BY cust_id;
Purpose: Compares aggregated totals from the source with the target warehouse to verify transformation accuracy.
Window Function – Duplicate Detection
SELECT *
FROM (
SELECT order_key,
ROW_NUMBER() OVER
(PARTITION BY order_key ORDER BY load_date DESC) rn
FROM target_fact_orders
) x
WHERE rn > 1;
Purpose: Identifies duplicate records based on the business key.
Performance Tuning – Explain Plan
EXPLAIN PLAN FOR
SELECT *
FROM target_fact_orders
WHERE load_date = SYSDATE;
Purpose: Reviews the SQL execution plan to identify performance bottlenecks, inefficient scans, or missing indexes.
6. Scenario-Based ETL Testing Questions (Cognizant Focus)
Q17. What if Source and Target Record Counts Mismatch?
When source and target record counts do not match, I investigate the ETL process systematically to identify the root cause.
Typical validation steps include:
- Check extraction filters.
- Review rejected records.
- Verify joins.
- Validate transformation logic.
I also review ETL logs, audit tables, and the S2T mapping document to determine whether the mismatch is caused by expected business rules or an ETL defect.
Q18. How do you Handle NULL Values?
NULL values are handled according to the business rules defined in the Source-to-Target (S2T) mapping.
Possible approaches include:
- Replace with default values.
- Reject records.
- Allow NULL values per business rules.
Functions such as NVL() or COALESCE() are commonly used to replace NULL values during transformation.
Q19. How do you Test Incremental Loads?
Incremental load testing verifies that only new or modified records are processed during each ETL execution.
Validation activities include:
- Validate delta records.
- Verify last_run_date.
- Verify batch_id.
Additional checks ensure that unchanged records are not reprocessed and duplicate records are not created.
Q20. ETL Job is Slow – How do you Troubleshoot?
When an ETL job is running slower than expected, I begin by analyzing the SQL execution plan and identifying performance bottlenecks.
Typical optimization activities include:
- Check indexes.
- Review execution plans.
- Optimize joins.
- Enable partition pruning.
I also review query performance, system resource utilization, and transformation logic to ensure that the ETL process completes within the agreed Service Level Agreement (SLA).
7. ETL Architecture & Mapping Validation
ETL architecture defines how data moves from source systems through different processing layers before being loaded into the target Data Warehouse (DW). One of the most important responsibilities of an ETL QA engineer is validating that the ETL implementation matches the Source-to-Target (S2T) mapping document and business requirements.
Mapping validation ensures that every source column is correctly transformed and loaded into the target system. It also verifies that business rules, data quality standards, and metadata definitions are implemented accurately. Proper mapping validation helps prevent data inconsistencies, reporting errors, and production defects.
Mapping Validation Checklist
The following checklist is commonly used by ETL QA engineers during mapping validation.
Column Mapping
Column mapping validation ensures that every source column is correctly mapped to the corresponding target column according to the S2T mapping document.
The ETL tester verifies:
- Correct source table and column mapping.
- Correct target table and column mapping.
- Proper mapping of business keys and surrogate keys.
- No missing or incorrect column mappings.
Incorrect column mappings can result in inaccurate reports and inconsistent warehouse data.
Transformation Logic
Transformation validation confirms that all business rules defined in the S2T mapping document have been implemented correctly.
Typical transformation validations include:
- Data cleansing.
- Currency conversion.
- Mathematical calculations.
- Lookup transformations.
- Date conversions.
- String manipulation.
- Derived column generation.
The transformed data should match the expected business results defined in the requirements.
Data Types & Length
The ETL tester validates that source and target columns use compatible data types and field lengths.
Validation includes:
- Numeric data types.
- Character data types.
- Date and timestamp formats.
- Decimal precision and scale.
- Column lengths.
Proper validation prevents data truncation, conversion errors, and data corruption during ETL processing.
Mandatory Fields
Mandatory field validation ensures that all required business fields contain valid values before data is loaded into the warehouse.
The ETL tester verifies:
- Mandatory fields are never NULL.
- Default values are assigned when required.
- Invalid records are rejected according to business rules.
- Optional fields allow NULL values only where permitted.
This validation ensures high-quality data within the target warehouse.
Business Rules
Business rule validation confirms that all transformation logic aligns with the documented business requirements.
Examples include:
- Discount calculations.
- Tax calculations.
- Customer categorization.
- Product classification.
- Status mapping.
- Currency conversion.
The ETL tester compares the transformed output with the expected business results to ensure that every rule has been correctly implemented.
8. ETL Tools – Cognizant Interview Perspective
Organizations use a variety of ETL tools to integrate data from multiple source systems into enterprise data warehouses. While the tools may differ between projects, the core ETL testing concepts remain the same.
In Cognizant ETL interviews, candidates are generally expected to have working knowledge of commonly used ETL tools along with a strong understanding of ETL concepts, SQL, and Data Warehouse architecture.
Commonly Asked Tools
Informatica
Informatica is one of the most widely used enterprise ETL tools. It supports data extraction, transformation, workflow development, scheduling, monitoring, and enterprise-scale data integration.
Microsoft SSIS
Microsoft SQL Server Integration Services (SSIS) is Microsoft’s ETL platform used for data migration, transformation, and integration. It is widely used in Microsoft SQL Server environments.
Ab Initio
Ab Initio is a high-performance ETL platform designed for processing very large volumes of enterprise data. It is known for its scalability, parallel processing capabilities, and efficient execution of complex ETL workflows.
Pentaho
Pentaho is an open-source ETL and Business Intelligence platform that provides data integration, reporting, and analytics capabilities. It is often used by organizations looking for flexible and cost-effective ETL solutions.
Talend
Talend is a widely adopted ETL platform that supports data integration, data quality, cloud integration, and big data processing. It is available in both open-source and enterprise editions.
Cognizant Interview Perspective
Although tool knowledge is beneficial, interviewers typically focus more on:
- ETL concepts.
- Data Warehouse architecture.
- SQL skills.
- Source-to-Target (S2T) mapping.
- Business rule validation.
- Data reconciliation.
- ETL troubleshooting.
- Real-time production scenarios.
Strong conceptual understanding and practical SQL experience generally carry more weight than expertise in a specific ETL tool.
9. ETL Defect Examples
ETL QA engineers are expected to identify, investigate, and report defects that impact data quality, ETL processing, and business reporting. Understanding common ETL defect types demonstrates practical project experience during interviews.
| Defect Type | Example |
| Data Mismatch | Wrong transformation logic |
| Duplicate Records | Dedup logic missing |
| History Issues | SCD Type 2 not applied |
| Load Failure | Job aborted |
| Performance | SLA breach |
Data Mismatch
A data mismatch occurs when the values in the target system differ from the expected values after ETL processing.
Common causes include:
- Incorrect transformation logic.
- Invalid lookup mappings.
- Calculation errors.
- Incorrect joins.
- Missing business rules.
The ETL tester compares source and target data to identify discrepancies and determine the root cause.
Duplicate Records
Duplicate records occur when multiple records with the same business key are loaded into the target system because deduplication logic is missing or implemented incorrectly.
The ETL tester validates:
- Business key uniqueness.
- Deduplication rules.
- Window function results.
- Source-to-target consistency.
SQL functions such as ROW_NUMBER() are commonly used to identify duplicate records.
History Issues
History-related defects commonly occur when Slowly Changing Dimension (SCD) Type 2 processing is not applied correctly.
Examples include:
- Old record not expired.
- New record not inserted.
- Multiple active records.
- Incorrect effective dates.
- Missing historical information.
These defects can lead to inaccurate historical reporting and incorrect business analysis.
Load Failure
A load failure occurs when an ETL job terminates before successfully loading data into the target warehouse.
Common causes include:
- Database connection failures.
- Constraint violations.
- Invalid source data.
- Workflow failures.
- Resource shortages.
Experienced ETL testers analyze ETL logs, workflow reports, and audit tables to identify the cause and ensure successful recovery.
Performance
Performance defects occur when ETL jobs exceed the agreed Service Level Agreement (SLA) or consume excessive system resources.
Common causes include:
- Missing indexes.
- Inefficient SQL queries.
- Large table joins.
- Data skew.
- Excessive transformations.
Performance testing helps identify bottlenecks and optimize ETL execution.
10. Sample ETL Test Case
Experienced ETL testers are expected to validate incremental processing, audit tracking, and change detection. A common interview scenario involves validating Incremental Loads using audit fields and hashing.
Test Case: Incremental Load Validation
Test Scenario
New and modified records are available in the source system after the previous ETL execution. The ETL process should process only the changed records while leaving unchanged records untouched.
Validation Steps
Validate Delta Extraction
Verify that only records changed since the previous ETL execution are extracted using the configured incremental load mechanism, such as last_run_date, timestamps, or batch identifiers.
Verify Audit Fields
Confirm that audit fields such as:
- load_date
- batch_id
- record_source
- created_ts
- updated_ts
are populated correctly for every processed record.
Check Hash Values for Changes
Recalculate hash values using the same hashing algorithm applied during ETL and compare them with the stored hash values in the target system.
Only records with changed hash values should be updated.
Expected Result
The ETL process should:
- Process only new or modified records.
- Populate audit fields correctly.
- Detect changes accurately using hashing.
- Avoid duplicate records.
- Maintain data integrity throughout the ETL process.
11. Quick Revision Sheet (ETL Testing – Cognizant)
The following topics are among the most frequently discussed during Cognizant ETL interviews. Reviewing these concepts helps strengthen technical knowledge and improve interview readiness.
Important Topics to Revise
ETL Architecture
Understand the complete ETL workflow, including extraction, staging, transformation, loading, and reporting layers, along with the purpose of each layer.
Source-to-Target (S2T) Mapping
Review source-to-target mappings, transformation logic, metadata validation, lookup rules, data type conversions, and business validations.
SQL Joins & Aggregations
Practice SQL concepts such as:
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL JOIN
- GROUP BY
- Aggregate functions
- Window functions such as ROW_NUMBER(), RANK(), and DENSE_RANK()
These SQL skills are essential for ETL validation and are frequently tested in interviews.
SCD Type 1 & Type 2
Understand how Slowly Changing Dimensions manage changes to dimension data, including historical tracking, effective dates, expiry dates, and active flags.
Incremental Loads
Review incremental loading concepts such as:
- Delta extraction.
- Change Data Capture (CDC).
- Watermark logic.
- Batch processing.
- Incremental load validation.
Performance Tuning
Study ETL optimization techniques, including:
- Indexing.
- Query optimization.
- Partitioning.
- Execution plan analysis.
- Parallel processing.
These techniques help ensure ETL jobs complete within defined Service Level Agreements (SLAs).
Defect Lifecycle
Understand the ETL defect lifecycle, including:
- Defect identification.
- Root cause analysis.
- Defect logging.
- Severity and priority assignment.
- Retesting.
- Regression testing.
- Defect closure.
Being able to discuss real production defects and their resolution demonstrates practical ETL project experience.
12. FAQs – ETL Testing Interview Questions Cognizant
Q1. What SQL level is expected in Cognizant ETL interviews?
Cognizant ETL interviews typically expect candidates to have advanced SQL skills. Interviewers often assess the ability to write efficient queries for validating data, troubleshooting ETL jobs, and optimizing query performance.
Important SQL topics include:
- Complex JOIN operations.
- Aggregate functions.
- Subqueries and Common Table Expressions (CTEs).
- Window functions such as ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), and LAG().
- Data reconciliation queries.
- Performance tuning using execution plans.
Strong SQL knowledge is considered one of the most important technical skills for ETL QA professionals.
Q2. Is ETL testing manual or automated?
ETL testing is mostly manual using SQL, particularly for validating business rules, source-to-target mappings, and data quality. However, automation is commonly used for repetitive validation tasks and regression testing.
Automation is often implemented using:
- SQL scripts.
- Shell scripts.
- Scheduling tools.
- Python (where applicable).
- CI/CD pipelines.
Knowledge of automation techniques is considered an advantage because it improves testing efficiency and reduces manual effort.
Q3. What makes a strong ETL tester at Cognizant?
A strong ETL tester combines technical expertise with analytical thinking and practical project experience. Interviewers typically look for candidates who can independently validate ETL processes, troubleshoot production issues, and communicate effectively with development and business teams.
Key qualities include:
- Strong SQL skills.
- Deep Data Warehouse (DW) knowledge.
- Defect analysis and root cause investigation.
- Real-time project experience.
- Understanding of S2T mapping and ETL architecture.
- Ability to validate complex business rules and data transformations.
These skills help ETL testers deliver accurate, reliable, and high-quality data that supports enterprise reporting and business decision-making.