ETL Testing Tutorial

What is ETL?

ETL stands for Extract-Transform-Load, and it describes how data moves from a source system into a data warehouse. Data is extracted from an OLTP database, transformed to match the data warehouse schema, and loaded into the warehouse database. Many warehouses also incorporate data from non-OLTP systems such as text files, legacy applications, and spreadsheets.

For example, a retail store may have separate departments such as sales, marketing, and logistics. Each department handles customer information independently, and the way each one stores that data differs. The sales department may store records by customer name, while the marketing department uses customer ID.

If business teams want to review the full purchase history of a customer across different marketing campaigns, the disconnected data makes that very tedious. The solution is to use a datawarehouse to store information from different sources in a uniform structure using ETL. ETL can transform dissimilar data sets into a unified structure, so BI tools can later derive meaningful insights and reports.

The following diagram shows the ETL Testing process flow and the core concepts you will use throughout this guide:

1) Extract

• Extract relevant data from one or more source systems.

2) Transform

• Transform data to the DW (Data Warehouse) format.
• Build keys: a key is one or more data attributes that uniquely identify an entity. Various types of keys are primary key, alternate key, foreign key, composite key, and surrogate key. The data warehouse owns these keys and never allows any other entity to assign them.
• Cleansing of data: after the data is extracted, it moves into the next phase of cleaning and conforming. Cleaning fixes omissions and identifies errors. Conforming resolves conflicts between incompatible data sets so they can be used in an enterprise data warehouse. The system also creates metadata that helps diagnose source system problems and improve data quality.

3) Load

• Load data into the DW (Data Warehouse).
• Build aggregates: an aggregate summarizes and stores data from a fact table to improve the performance of end-user queries.

What is ETL Testing?

ETL Testing is performed to ensure that the data loaded from a source to a destination, after business transformation, is accurate. It also involves the verification of data at the various middle stages between source and destination. Because ETL stands for Extract-Transform-Load, ETL Testing covers each of these three stages and the points where data crosses between them.

Why is ETL Testing Important?

Once you understand what ETL Testing is, the next question is why organizations invest so much effort into it. Business decisions rely on data that is correct, complete, and trustworthy, so a single transformation error can ripple into financial reports, customer analytics, and regulatory disclosures.

The following points explain the practical value of strong ETL Testing:

• Data accuracy: It confirms that values transformed by business rules match the documented mapping specifications, preventing silent corruption.
• Reliable reporting: Dashboards and BI tools depend on the warehouse, so verified ETL pipelines protect every downstream report and KPI.
• Regulatory compliance: Industries such as banking, healthcare, and insurance must prove that data lineage and integrity are preserved end to end.
• Reduced rework: Catching defects in lower environments avoids costly production reloads, manual reconciliations, and customer-facing errors.
• Performance assurance: ETL Testing measures load windows, throughput, and bottlenecks so the warehouse continues to scale as data volume grows.

With these motivations clear, the next section walks through the structured process that ETL testers follow on real projects.

ETL Testing Process

Similar to other testing processes, ETL also goes through different phases. The different phases of the ETL Testing process are as follows:

ETL Testing is performed in five stages:

1. Identifying data sources and requirements
2. Data acquisition
3. Implement business logic and dimensional modelling
4. Build and populate data
5. Build reports

With the high-level process in mind, let us look at the specific testing types that fit inside this lifecycle.

Types of ETL Testing

1. Production Validation Testing
   Testing Process: Also called “Table balancing” or “production reconciliation,” this type of ETL Testing is performed on data as it moves into production systems. To support business decisions, production data must be in the correct order. Informatica Data Validation Option provides ETL Testing automation and management capabilities so production systems are not compromised by bad data.
2. Source to Target Testing (Validation Testing)
   Testing Process: This type of testing validates whether the data values transformed match the expected target values.
3. Application Upgrades
   Testing Process: This type of ETL Testing can be automatically generated, saving substantial test development time. It checks whether data extracted from an older application or repository matches the data in a new application or repository.
4. Metadata Testing
   Testing Process: Metadata testing includes data type checks, data length checks, and index or constraint checks.
5. Data Completeness Testing
   Testing Process: Data completeness testing verifies that all expected data is loaded from the source into the target. Common tests include comparing and validating record counts, aggregates, and actual data between source and target columns when the transformation is simple or absent.
6. Data Accuracy Testing
   Testing Process: This testing ensures that the data is accurately loaded and transformed as expected.
7. Data Transformation Testing
   Testing Process: Testing data transformation often cannot be achieved with a single source SQL query and an output comparison. Multiple SQL queries may be needed for each row to verify the transformation rules.
8. Data Quality Testing
   Testing Process:

   Data quality tests include syntax tests and reference tests. They prevent business process errors caused by incorrect dates or order numbers.

   Syntax tests report dirty data based on invalid characters, character patterns, and incorrect upper or lower case order.

   Reference tests check the data against the data model. For example: Customer ID.

   Data quality testing also includes number checks, date checks, precision checks, data checks, and null checks.

9. Incremental ETL Testing
   Testing Process: This testing checks the data integrity of old and new data with the addition of new data. Incremental testing verifies that inserts and updates are processed as expected during the incremental ETL process.
10. GUI/Navigation Testing
    Testing Process: This testing checks the navigation and GUI aspects of the front-end reports.

How to Create ETL Test Case

ETL Testing is a concept that can be applied to different tools and databases in the information management industry. The objective of ETL Testing is to assure that the data loaded from a source to a destination after business transformation is accurate. It also involves the verification of data at the various middle stages between source and destination.

While performing ETL Testing, two documents are always used by an ETL tester:

1. ETL mapping sheets: An ETL mapping sheet contains all the information of source and destination tables, including each column and its look-up in reference tables. ETL testers must be comfortable with SQL queries because ETL Testing may involve writing big queries with multiple joins to validate data at any stage. ETL mapping sheets give significant help while writing queries for data verification.
2. DB schema of source and target: It should be kept handy to verify any detail in the mapping sheets.

ETL Test Scenarios and Test Cases

1. Mapping doc validation
   Test Cases: Verify whether the corresponding ETL information is provided in the mapping doc. A change log should be maintained in every mapping doc.
2. Validation
   Test Cases:

   1) Validate the source and target table structure against the corresponding mapping doc.
   2) Source data type and target data type should be the same.
   3) Length of data types in both source and target should be equal.
   4) Verify that data field types and formats are specified.
   5) Source data type length should not be less than the target data type length.
   6) Validate the names of columns in the table against the mapping doc.

3. Constraint Validation
   Test Cases: Ensure constraints are defined for the specific table as expected.
4. Data consistency issues
   Test Cases:

   1) The data type and length for a particular attribute may vary across files or tables, even when the semantic definition is the same.
   2) Misuse of integrity constraints.

5. Completeness Issues
   Test Cases:

   1) Ensure that all expected data is loaded into the target table.
   2) Compare record counts between source and target.
   3) Check for any rejected records.
   4) Check that data is not truncated in the columns of target tables.
   5) Check boundary value analysis.
   6) Compare unique values of key fields between data loaded to the warehouse and source data.

6. Correctness Issues
   Test Cases:

   1) Data that is misspelled or inaccurately recorded.
   2) Null, non-unique, or out-of-range data.

7. Transformation
   Test Cases: Validate that every business rule and transformation logic in the mapping document is correctly applied to the source data before it lands in the target.
8. Data Quality
   Test Cases:

   1) Number check: validate numeric formats and values.
   2) Date check: dates must follow a single format and be consistent across records.
   3) Precision check.
   4) Data check.
   5) Null check.

9. Null Validate
   Test Cases: Verify the null values where “Not Null” is specified for a specific column.
10. Duplicate Check
    Test Cases:

    1) Validate the unique key, primary key, and any other column that should be unique per business requirements to confirm there are no duplicate rows.
    2) Check if any duplicate values exist in any column extracted from multiple source columns and combined into one column.
    3) Per client requirements, ensure no duplicates exist in a combination of multiple columns within the target.

11. Date Validation
    Test Cases: Date values are used in many areas of ETL development:

    1) To know the row creation date.
    2) Identify active records from the ETL development perspective.
    3) Identify active records from the business requirements perspective.
    4) Sometimes, based on the date values, updates and inserts are generated.

12. Complete Data Validation
    Test Cases:

    1) Validate the complete data set in the source and target tables using a minus query as the best solution.
    2) You need to perform source minus target and target minus source.
    3) If the minus query returns any value, those rows should be considered mismatching.
    4) Match rows between source and target using an intersect statement.
    5) The count returned by intersect should match the individual counts of source and target tables.
    6) If a minus query returns rows and the intersect count is less than the source or target count, duplicate rows exist.

13. Data Cleanness
    Test Cases: Unnecessary columns should be deleted before loading into the staging area.

Types of ETL Bugs

Even with strong test cases, ETL pipelines can fail in distinct ways. The image below summarizes the bug categories you should watch for, and the table that follows describes each one.

Type of Bugs	Description
User interface bugs/cosmetic bugs	• Related to GUI of application • Font style, font size, colors, alignment, spelling mistakes, navigation, and so on
Boundary Value Analysis (BVA) related bug	• Minimum and maximum values
Equivalence Class Partitioning (ECP) related bug	• Valid and invalid type
Input/Output bugs	• Valid values not accepted • Invalid values accepted
Calculation bugs	• Mathematical errors • Final output is wrong
Load Condition bugs	• Does not allow multiple users • Does not allow customer-expected load
Race Condition bugs	• System crash and hang • System cannot run client platforms
Version control bugs	• No logo matching • No version information available • Occurs usually in Regression Testing
H/W bugs	• Device is not responding to the application
Help Source bugs	• Mistakes in help documents

Data Warehouse Testing

Data Warehouse Testing is a testing method in which the data inside a data warehouse is tested for integrity, reliability, accuracy, and consistency to comply with the company’s data framework. The main purpose of data warehouse testing is to ensure that the integrated data inside the warehouse is reliable enough for the company to make decisions on. While ETL Testing focuses on data movement, Data Warehouse Testing covers the broader storage and reporting layer that ETL ultimately feeds.

Difference between Database Testing and ETL Testing

Although both disciplines work with structured data, they answer different questions. The table below highlights the practical contrast:

ETL Testing	Data Base Testing
Verifies whether data is moved as expected.	The primary goal is to check if the data is following the rules and standards defined in the data model.
Verifies whether counts in the source and target are matching, and that the data transformed is as expected.	Verifies that there are no orphan records and that foreign-primary key relations are maintained.
Verifies that foreign primary key relations are preserved during the ETL.	Verifies that there are no redundant tables and that the database is optimally normalized.
Verifies for duplication in loaded data.	Verifies if data is missing in columns where required.

Performance Testing in ETL

Performance Testing in ETL is a testing technique that ensures an ETL system can handle the load of multiple users and transactions. The primary goal of ETL Performance Testing is to optimize and improve session performance by identifying and eliminating performance bottlenecks. The source and target databases, mappings, sessions, and the system itself can all contain bottlenecks.

One of the best tools used for Performance Testing and Tuning is Informatica.

Responsibilities of an ETL Tester

Key responsibilities of an ETL tester are segregated into three categories:

• Stage table / SFS or MFS
• Business transformation logic applied
• Target table loading from stage file or table after applying a transformation

Some of the day-to-day responsibilities of an ETL tester are:

• Test ETL software
• Test components of the ETL data warehouse
• Execute backend data-driven tests
• Create, design, and execute test cases, test plans, and test harnesses
• Identify problems and provide solutions for potential issues
• Approve requirements and design specifications
• Validate data transfers and test flat files
• Write SQL queries for various scenarios such as count tests

Automation of ETL Testing

The general methodology of ETL Testing is to use SQL scripting or visual “eyeballing” of data. These approaches are time-consuming, error-prone, and seldom provide complete test coverage. To accelerate execution, improve coverage, reduce costs, and improve defect detection in production and development environments, automation is the need of the hour. One such tool is Informatica.

Modern teams also blend traditional automation with AI-assisted helpers that suggest transformation tests, generate synthetic source data, and flag schema drift, freeing testers to focus on complex business logic rather than repetitive script maintenance.

Best Practices for ETL Testing

1. Make sure data is transformed correctly.
2. Without any data loss or truncation, projected data should be loaded into the data warehouse.
3. Ensure the ETL application appropriately rejects invalid data, replaces it with default values where applicable, and reports it.
4. Confirm that data is loaded into the warehouse within prescribed and expected time frames to validate scalability and performance.
5. All methods should have appropriate unit tests regardless of visibility.
6. To measure their effectiveness, all unit tests should use appropriate coverage techniques.
7. Strive for one assertion per test case.
8. Create unit tests that target exceptions.

Checkout – ETL Testing Interview Questions & Answers

FAQs