Process of ETL Testing and Its Types | Devstringx Technologies

What is ETL?

What is a Data Warehouse?

Data warehouse is an electronic storage of a huge amount of information through business. It is designed for query and analysis instead of transaction processing. It is a process of transforming data into information and making it available to users in an appropriate manner to make a difference.

Why do we need ETL testing?

ETL Testing Process:

  • Understand business requirements.
  • Test Planning and estimation on the basis of number of tables and volume of the data.
  • Designing of test cases from all available inputs, design mapping data ,SQL scripts and preparation of test data.
  • Test execution and bug reporting, execution phase includes running ETL jobs, monitoring job runs, SQL script execution, defect logging, defect retesting and regression testing.
  • Summarising reports and result analysis, in this phase sign off is given to promote the job or move to the next phase.
  • Test closure

These are the five pillars of ETL Testing process:

  • Upstream data issues.
  • Timelines and accuracy of data.
  • Security of data.
  • History of data and its maintenance.
  • Availability if data for decision.

Difference between ETL Testing and Database Testing

It verifies whether the data is moved as per requirement or not.

It verifies whether the data is following the rules, as defined in the Data Model.

Diff 2-

It verifies if the counts in the source and target are equal.

It verifies if foreign and primary key relation is maintained and also check for any orphan records.

Diff 3-

It checks if foreign and primary key references are maintained during ETL process.

It verifies whether the database is ideally normalized and there are no redundant tables.

Diff 4-

It verifies that there is no duplicacy in the data loaded into the data warehouse.

It checks for any missing data in the required columns.

Types of ETL Testing:

  • Data transformation Testing: Multiple SQL queries are required to be run for each and every row to verify data is transformed correctly according to various business requirements and rules.
  • Source to Target Testing: It includes matching of the count of records and data validation at the source and target system.
  • End-User Testing: It includes report generation to verify if the data is as per expectation by cross checking the data in the target system for report validation.
  • Retesting: It is a process of data validation by running the reports again once the bugs and defects are fixed in the target system.
  • Production Validation Testing: It includes data validation in production system & compare it against the source data.
  • Data Integration Testing: It make sure that the data from different sources has been loaded properly into the target system and all the threshold values are checked.
  • Application Migration Testing: This testing ensures that the ETL application is working fine on moving to a new platform.

ETL Tools

Some of the ETL tools are:

  • RightData
  • Informatica Data Validation
  • QuerySurge
  • Datagaps ETL Validator
  • QualiDI
  • Talend Open Studio for Data Integration

Creating Test cases in ETL testing:

  • ETL Mapping sheets: This contains all information about the source and destination tables and its These sheets helps in creating SQL queries while doing testing.
  • Database Schemas of Source and Destination table: ETL mapping sheets kept up to date with Database Schema for data validation.

Types of ETL Bugs:

  • Calculation bugs: It is reported when the end result is deviated due to some mathematical error.
  • Input/Output bugs: Accept invalid values and rejects valid/ correct values.
  • User Interface bugs: It is related to the GUI of an application like color, font size , alignment etc
  • Hardware bugs: When device doesn’t respond properly to the application due to some hardware issues.
  • Load Condition bugs: When system doesn’t allow multiple user or customers expected load.

Originally published at on November 23, 2019.



Devstringx Technologies is highly recommended IT company for custom software development, mobile app development and automation testing services

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Devstringx Technologies

Devstringx Technologies is highly recommended IT company for custom software development, mobile app development and automation testing services