In this article, we will thoroughly analyze the ETL testing of the data warehouse, its peculiarities, and problems that may appear during this procedure.

The concept of ETL testing should be considered as a complex of tests which aim for thorough studying of software performance features in case of potential fails, breaks, and loads.

Why Do the Companies Need Technical Data Warehouse?

Any organization with proper IT structure constantly aims for the improvement of its technical transformation according to the current web market needs. Nowadays there is an organization of simple and easy-to-use interaction with a huge amount of technical information (so-called Big Data).

Any data is a foundation of the work of any company, firm or organization. Information is the basis of people’s management decisions in their everyday activities.

In the modern information world, a lot of companies want to create a safe data warehouse that one can monitor online.

Data warehouse development is a quite difficult task. Many companies create special departments that create and support distributional virtual warehouses with valuable information.

In everyday practice, specialists use the ETL testing tool (along with traditional security testing) for qualitative integration of different data sources from departments. ETL tool will work as an integrator, get data from different sources, transform it into a necessary format according to business transformation rules, and upload it into the single database (also known as a data warehouse).

Business benefits from data warehouse testing based on the ETL tool only when independent experts test the product, and they can make sure that the tested virtual data warehouse is safe and protected.

There Are Four Parts of Data Warehouse Testing:

1. New data warehouse testing – the creation of a new base and execution of all the necessary tests from scratch. There is data input on the base of the client’s requests and provided ETL tools. The new warehouse is created, as well as various types and sources of processing information.
2. Migration testing – performance of base testing while it migrates from one warehouse to another.
3. Change request – there is testing of warehouse performance while new information comes to the already created warehouse.
4. Report testing – the test is based on special validators, their information and the provided client’s evaluating.

Basic Methods of ETL Testing

Data transformation testing. Verification of the rule, used in the logic of warehouse functioning (if it is processed correctly and according to all the client’s demands).

Source to target count testing. It is testing of record counts, information sections and other content types with their specification rating value.

Source to target data testing. Validation of the correct data uploading in the warehouse without the loss of quality and size.

Data quality testing. Verification that ETL application properly declines, replace default value and gives user incorrect data.

Performance testing. This is a testing of data uploading within the expected timeline, with a good performance index and the further scalability.

Production validation testing. This is a comparative statistics of warehouse data and initial data.

Application migration testing. It is verification that the ETL application works properly while migrating to a new platform.

Data & constraint check. There is a check of data types, size, index, and constraints.

Duplicate data check. This is a verification of duplicate data in target systems. Duplicate data can cause incorrect analytical reports in case of further usage of this warehouse.

There are many other tests except the ones, we described above. For example, system integration test, acceptance testing, regression testing, re-testing, navigation, and incremental testing in order to assure that everything is functioning properly and without fail.

ETL / Data Warehouse Testing

Like any other testing that is under independent verification and validation, ETL testing has similar phases.

• Requirement testing;
• Validation;
• Estimation of how difficult it is to add data to the warehouse based on several tables, rules, and a specified rate of digital warehouse performance;
• Test planning on the base of initial data received after evaluation of testing and business needs;
• Creation of new test cases on a base of provided initial data (work with SQL scripts);
• When all test cases are ready and approved, the testing team can proceed with pretest and prepare test data for testing;
• After the testing, the short report is created.

[highlight dark=”no”]Moreover, the first two phases from this list can be considered as preliminary stages of ETL testing. [/highlight]

In other words, the testing process looks like this:

You should determine the testing strategy which all stakeholders should approve before proceeding with testing. The thoroughly developed strategy assures that specialists have used the correct approach that meets test requirements.

The Main Difference Between the Testing of Database and Data Warehouse

There is a common misunderstanding that testing of database and data warehouse are similar in some things. But at the same time, there are different approaches to both processes.

Usually, one performs database testing with less data, in such bases as OLTP (Online Transaction Processing). But the data warehouse testing uses a lot of data on the base of OLAP databases (online analytical processing).

During database testing, as a rule, data comes from similar sources. But in the case of data warehouse testing most information comes from different data sources which are inconsistent.

There are a couple of common tests that everyone must use during testing of any data warehouse.

The Actual Problem of ETL Testing

This kind of testing differs from ordinary tests. Performing data warehouse testing, you may face some problems:

• Duplicate information;
• Data loss during testing;
• It is impossible to perform ETL testing independently;
• Instability of test environment;
• No testing ground for exhaustive testing.

Conclusion

Data is important for business while making key business decisions. ETL testing plays an important role in checking and assuring the accuracy, consistency, and safety of business information. Moreover, it reduces the risk to lose data during the production process.