Almost all the IT companies today, highly depend on data flow as a large amount of information is made available for access and one can get everything which is required.
And this is where the concept of ETL and ETL Testing comes into the picture. Basically, ETL is abbreviated as Extraction, Transformation, and Loading. Presently ETL Testing is performed using SQL scripting or using spreadsheets which may be a time-consuming and error-prone approach.
In this article, we will have detailed discussions on several concepts viz. ETL, ETL Process, ETL testing and different approaches used for it along with the most popular ETL testing tools.
Also read => ETL Testing Tips
What You Will Learn:
- ETL Testing Concepts
- ETL Testing Process
- Types of ETL Testing
- How to Create Test Cases in ETL Testing
- List of Most Popular ETL Testing Tools
- Points to Remember
- Recommended Reading
ETL Testing Concepts
#1) As mentioned previously ETL stands for Extraction, Transformation, and Loading is considered to be the three prime database functions.
- Extraction: Reading data from the database.
- Transformation: Converting the extracted data into the required form to store into another database.
- Loading: Writing the data into the target database.
#2) ETL is used to transfer or migrate the data from one database to another, to prepare data marts or data warehouses.
Following diagram elaborates the ETL Process in a precise way:
ETL Testing Process
ETL Testing Process is similar to other testing processes and includes some stages.
- Identifying business requirements
- Test Planning
- Designing test cases and test data
- Test execution and bug reporting
- Summarizing reports
- Test closure
Types of ETL Testing
ETL Testing can be classified into the following categories according to the testing process that is been followed.
#1) Production Validation Testing:
It is also called as Table balancing or product reconciliation. It is performed on data before or while being moved into the production system in the correct order.
#2) Source To Target Testing:
This type of ETL Testing is performed to validate the data values after data transformation.
#3) Application Upgrade:
It is used to check whether the data is extracted from an older application or new application or repository.
#4) Data Transformation Testing:
Multiple SQL queries are required to be run for each and every row to verify data transformation standards.
#5) Data Completeness Testing:
This type of testing is performed to verify if the expected data is loaded at the appropriate destination as per the predefined standards.
I would also like to compare ETL Testing with Database Testing but before that let us have a look at the types of ETL Testing with respect to database testing.
Given below are the Types of ETL Testing with respect to Database Testing:
1) Constraint Testing:
Testers should test whether the data is mapped accurately from source to destination while checking for it testers need to focus on some key checks (constraints).
- NOT NULL
- Primary Key
- Foreign Key
2) Duplicate Check Testing:
Source and target tables contain a huge amount of data with frequently repeated values, in such case testers follow some database queries to find such duplication.
3) Navigation Testing:
Navigation concerns with the GUI of an application. User finds an application friendly when he gets easy and relevant navigation throughout the entire system. The tester must focus on avoiding irrelevant navigation from the user point of view.
4) Initialization Testing:
Initialization Testing is performed to check the combination of hardware and software requirements along with platform it is installed
5) Attribute Check Testing:
This testing is performed for verifying if all the attributes of both the source and target system are the same
From the above listing one may consider that ETL Testing is quite similar to Database Testing but the fact is ETL Testing is concerned with Data Warehouse Testing and not Database Testing.
There are several other facts due to which ETL Testing differs from Database Testing.
Let’s have a quick look at what they are:
- The primary goal of Database Testing is to check if the data follows the rules and standards of the data model where on the other hand ETL Testing checks if data is moved or mapped as expected.
- Database Testing focuses on maintaining a primary key-foreign key relationship while ETL Testing verifies for data transformation as per the requirement or expectation and are same at source and target system.
- Database Testing recognizes missing data whereas ETL Testing determines duplicate data.
- Database Testing is used for data integration and ETL Testing for enterprise business intelligence reporting
- These are some major differences which make ETL Testing different from Database Testing.
Given below is the table showing the list of ETL Bugs:
|Type of bug||Description|
|Calculation Bugs||Final output wrong due to mathematical error|
|Input/output Bugs||Accepts invalid values and rejects valid values|
|H/W bugs||Device is not responding due to hardware issues|
|User Interface bugs||Related to GUI of an application|
|Load condition bugs||Denies multiple users|
How to Create Test Cases in ETL Testing
The primary goal of ETL testing is to assure whether the extracted and transformed data is loaded accurately from source to the destination system. ETL testing includes two documents, they are:
#1) ETL Mapping Sheets: This document contains information about the source & destination tables and their references. Mapping sheet provides help to create big SQL queries while performing ETL Testing.
#2) Database schema for Source and Destination table: It should be kept updated in the mapping sheet with database schema to perform data validation.
List of Most Popular ETL Testing Tools
Like automation testing, ETL Testing can be also automated. Automated ETL Testing reduces time consumption during the testing process and helps to maintain accuracy.
Few ETL Testing Automation Tools are used to perform ETL Testing more effectively and rapidly.
Given below is the list of the top ETL Testing Tools:
- Informatica Data Validation
- Datagaps ETL Validator
- Talend Open Studio for Data Integration
- Codoid's ETL Testing Services
- Data Centric Testing
- GTL QAceGen
- Zuzena Automated Testing Service
- 99 Percentage ETL Testing
=>> Contact us to suggest your listing here
#1) Informatica Data Validation
(Note: Click on any image for an enlarged view)
Informatica Data Validation is a GUI based ETL Testing tool which is used to extract,[ transform and Load (ETL). The testing includes a comparison of tables before and after data migration. This type of testing ensures data integrity, i.e. the volume of data is correctly loaded and is in the expected format into the destination system.
- Informatica Validation tool is a comprehensive ETL Testing tool which does not require any programming skill.
- It provides automation during ETL testing which ensures if the data is delivered correctly and is in the expected format into the destination system.
- It helps to complete data validation and reconciliation in the testing and production environment.
- It reduces the risk of introducing errors during transformation and avoid bad data to be transformed into the destination system.
- Informatica Data Validation is useful in Development, Testing and Production environment where it is necessary to validate the data integrity before moving into the production system.
- 50 to 90% of cost and efforts can be saved using Informatica Data Validation tool.
- Informatica Data Validation provides a complete solution for data validation along with data integrity.
- Reduces programming efforts and business risks due to an intuitive user interface and built-in operators.
- Identifies and prevents data quality issues and provides greater business productivity.
- Allows 64% free trial and 36% paid service that reduces time and cost required for data validation.
Visit official site here: Informatica Data Validation
QuerySurge tool is specifically built for testing of Big Data and Data warehouse. It ensures that the data extracted and loaded from the source system to the destination system is correct and is as per the expected format. Any issues or differences are identified very quickly by QuerySurge.
- QuerySurge is an automated tool for Big Data Testing and ETL Testing.
- It improves the data quality and accelerates testing cycles.
- It validates data using Query Wizard.
- It saves time & cost by automating manual efforts and schedules tests for a specific time.
- QuerySurge supports ETL Testing across the various platform like IBM, Oracle, Microsoft, SAP.
- It helps to build test scenarios and test suit along with configurable reports without specific knowledge of SQL.
- It generates email reports through an automated process.
- Reusable query snippet to generate reusable code.
- It provides a collaborative view of data health.
- QuerySurge can be integrated with HP ALM, TFS, IBM Rational Quality Manager.
- Verifies, converts, and upgrades data through the ETL process.
- It is a commercial tool that connects source and target data and also supports real-time progress of test scenarios.
Visit the official site here: QuerySurge
iCEDQ is an automated ETL Testing tool specifically designed for the issues faced in a data-centric project like a data warehouse, data migration etc. iCEDQ performs verification, validation, and reconciliation between the source and destination system. It ensures if the data is intact after migration and it avoids bad data to load into the target system.
- iCEDQ is a unique ETL Testing tool which compares millions of rows of databases or files.
- It helps to identify the exact row and column which contains data issue.
- It sends alerts and notifications to the subscribed users after execution.
- It supports regression testing.
- iCEDQ supports various databases and can read data from any database.
- iCEDQ connects with a relational database, any JDBC compliant database, flat files etc.
- Based on unique columns in the database, iCEDQ compares the data in memory.
- It can be integrated with HP ALM – Test Management Tool.
- iCEDQ is designed for ETL Testing, Data Migration Testing and Data Quality Verification.
- Identifies data integration errors without any custom code.
- Supports rule engine for ETL process, collaborative efforts and organized QA process.
- It is a commercial tool with 30 days trial and provides custom reports with alerts and notifications.
- iCEDQ Big Data Edition now uses the power of Hadoop Cluster
- BI Report Testing & Dashboard Testing with iCEDQ
Visit the official site here: iCEDQ
#4) Datagaps ETL Validator
ETL Validator tool is designed for ETL Testing and Big Data Testing. It is a solution for the data integration projects. The testing of such data integration project includes various data types, huge volume, and various source platforms. ETL Validator helps to overcome such challenges using automation which further helps to reduce the cost and to minimize efforts.
- ETL Validator has an inbuilt ETL engine which compares millions of records from various databases or flat files.
- ETL Validator is data testing tool specifically designed for automated data warehouse testing.
- Visual Test Case Builder with drag and drop capability.
- ETL Validator has features of Query Builder which writes the test cases without manually typing any queries.
- Compare aggregate data such as count, sum, distinct count etc.
- Simplifies the comparison of database schema across various environment which includes data type, index, length, etc.
- ETL Validator supports various platforms such as Hadoop, XML, Flat files etc.
- It supports email notification, web reporting etc.
- It can be integrated with HP ALM which results in sharing of test results across various platforms.
- ETL Validator is used to check Data Validity, Data Accuracy and also to perform Metadata Testing.
- Checks Referential Integrity, Data Integrity, Data Completeness and Data Transformation.
- It is a commercial tool with 30 days trial and requires zero custom programming and improves business productivity.
Visit the official site here: Datagaps ETL Validator
QualiDi is an automated testing platform which offers end to end testing and ETL Testing. It automates ETL Testing and improves the effectiveness of ETL Testing. It also reduces tthe esting cycle and improves the data quality. QualiDI identifies bad data and non-compliant data very easily. QualiDI reduces regression cycle and data validation.
- QualiDI creates automated test cases and it also provides support for automated data comparison.
- It offers data traceability and test case traceability.
- It has a centralized repository for requirements, test cases, and test results.
- It can be integrated with HPQC, Hadoop etc.
- QualiDI identifies a defect in the early stage which in turns reduces the cost.
- It supports email notifications.
- It supports continuous integration process.
- It supports Agile development and rapid delivery of sprints.
- QualiDI manages complex BI Testing cycle, eliminates human error and data quality maintained.
Visit the official site: QualiDi
#6) Talend Open Studio for Data Integration
Talend Open Studio for Data Integration is an open source tool which makes ETL Testing easier. It includes all ETL Testing functionality and additional continuous delivery mechanism. With the help of Talend Data Integration tool, a user can run the ETL jobs on the remote servers that too with a variety of operating system.
ETL Testing ensures that data is transformed from the source system to the target without any data loss and thereby adhering to transformation rules.
- Talend Data Integration supports any types of relational database, Flat files etc.
- Integrated GUI which simplifies design and developing of ETL processes.
- Talend Data Integration has inbuilt data connectors with more than 900 components.
- It detects business ambiguity and inconsistency in transformation rule quickly.
- It supports remote job execution.
- Identifies defects at an early stage to reduce cost.
- It provides quantitative and qualitative metrics based on the ETL best practices.
- Context switching is possible between
- ETL development, ETL testing, and ETL production environment.
- Real-time data flow tracking along with the detailed execution statistics.
Visit the official site here: Talend ETL Testing
#7) Codoid's ETL Testing Services
Codoid’s ETL and data warehouse testing service includes data migration and data validation from the source to the target system. ETL Testing ensures that there is no data error, no bad data or data loss while loading data from the source to the target system. It quickly identifies any data errors or any other general errors occurred during the ETL process.
- Codoid’s ETL Testing service ensures data quality in the data warehouse and data completeness validation from the source to the target system.
- ETL Testing and data validation ensure that the business information transformed from source to target system is accurate and reliable.
- The automated testing process performs data validation during and post data migration and prevents any data corruption.
- Data validation includes count, aggregates and spot checks between the target and actual data.
- Automated testing process verifies if data type, data length, indexes are accurately transformed and loaded into the target system.
- Data quality Testing prevents data errors, bad data or any syntax issues.
Visit the official site here: Codoid’s ETL Testing
#8) Data-Centric Testing
Data Centric testing tool performs robust data validation to avoid any glitches such as data loss or data inconsistency during data transformation. It compares data between systems and ensures that the data loaded into the target system is exactly matching with the source system in terms of data volume, data type, format, etc.
- Data Centric Testing is build to perform ETL Testing and Data warehouse testing.
- Data Centric Testing is the largest and the oldest testing practice.
- It offers ETL Testing, data migration and reconciliation.
- It supports various relational database, Flat files etc.
- Efficient Data validation with 100% data coverage.
- Data Centric Testing also support comprehensive reporting.
- The automated process of data validation generates SQL queries which result in the reduction of cost and efforts.
- It offers a comparison between heterogeneous databases like Oracle & SQL Server and ensures that the data in both systems is in the correct format.
Visit the official site here: Data-Centric Testing
SSISTester is a framework which helps in the unit and integration testing of SSIS packages. It also helps to create ETL processes in a test-driven environment which thereby helps to identify errors in the development process. There are a number of packages created while implementing ETL processes and these needs to be tested during unit testing. Integration test is also “Live tests”.
- Unit test creates and verify tests and once execution gets complete it performs clean-up job.
- Integration test verifies that all packages are satisfied post execution of the unit test.
- Tests are created in a simple way as the user creates it in Visual Studio.
- Real-time debugging of a test is possible using SSISTester.
- Monitoring of test execution with user-friendly GUI.
- Test results are exported in HTML format.
- It removes external dependencies by using fake source and destination addresses.
- For the creation of tests, it supports any .NET language.
Visit the official site here: SSISTester
TestBench is a database management and verification tool. It is a unique solution which addresses all issues related to the database. User managed data rollback improve testing productivity and accuracy. It also helps to reduce environment downtime. TestBench reports all inserted, updated and deleted transactions which are performed in a test environment and captures the status of the data before and after the transaction.
- It always maintains data confidentiality to protect data.
- It has a restoration point for an application when a user wants to return back to a specific point.
- It improves decision making knowledge.
- It customizes data sets to improve test efficiency.
- It helps for maximum tests coverage and helps to reduce time and money.
- Data privacy rule ensures that the live data is not available in the test environment.
- Results are compared with various databases. Results include differences in tables & operation performed on tables.
- TestBench analyzes the relationship between the tables and maintains the referential integrity between tables.
Visit the official site here: TestBench
Some more to the list:
#11) GTL QAceGen
QAceGen is specifically designed to generate complex test data, automate ETL regression suite and to validate business logic of applications. QAceGen generates test data based on the business rule which is defined in the ETL specification. It creates each scenario which includes data generation and data validation statement.
Visit the official site here: QAceGen
#12) Zuzena Automated Testing Service
Zuzena is an automated testing service developed for data warehouse testing. It is used to execute large projects such as data warehousing, business intelligence and it manages data and executes integration and regression test suite. It automatically manages ETL execution and result evaluation. It has a wide range of metrics which monitors QA objectives and team performance.
Visit the official site: Zuzena Automated Testing
DbFit is an open source testing tool which is released under GPL license. It writes unit and integration tests for any database code. These tests are easy to maintain and can be executed directly from the browser. These tests are written using tables and are executed using the command line or Java IDE. It supports major databases like Oracle, MySQL, DB2, SQL Server, PostgreSQL, etc.
Visit the official site here: DbFit
AnyDbTest is an automated unit testing tool specifically designed for DBA or database developer. AnyDbTest writes test cases with XML and allows using an excel spreadsheet as a source of the test case. Standard assertions are supported such as SetEqual, StrictEqual, IsSupersetOf, RecordCountEqual, Overlaps etc. It supports various types of databases like MySQL, Oracle, SQL Server, etc. Testing can include more than one database i.e. source database can be an Oracle server and target database in which data needs to be loaded can be SQL Server.
Visit the official site here: AnyDbTest
#15) 99 Percentage ETL Testing
'99 Percentage ETL Testing' ensures data integrity and production reconciliation for any database system. It maintains the ETL mapping sheet and validates source and target database mapping of rows and columns. It also maintains the DB Schema of the source and target database. It supports production validation testing, data completeness, and data transformation testing.
Visit the official site here: 99 Percentage ETL Testing
Points to Remember
While performing ETL testing several factors are to be kept in mind by the testers.
Some of them are listed below:
- Apply suitable business transformation logic.
- Execute backend data-driven tests.
- Create and execute absolute test cases, test plans, and test harness.
- Assure accuracy of data transformation, scalability and performance.
- Make sure E
- TL application reports invalid values.
- Unit tests should be created as targeted standards.
ETL Testing is not only a tester’s duty but it also involves developers, business analyst, database administrators (DBA) and even the users. ETL Testing process became vital as it is required to make strategic decisions at regular time intervals.
ETL Testing is being considered as Enterprise Testing as it requires a good knowledge of SDLC, SQL queries, ETL procedures etc.
=>> Contact us to suggest your listing here.
Let us know if we have missed out any tool on to the above list and also suggest the ones that you use for ETL Testing in your daily routine.