ETL Data Validation – Better decision making through improved data quality

Today most business decisions are made based on collected data. It is critical for businesses to collect data and sanitize the same to achieve meaningful insights. Information can be extracted from data just as energy is extracted from sunlight or wind.

Data comes from multiple sources and must go through an ETL (Extract, Transform and Load) process to make sense. It is extremely important to test the data at every stage of the ETL process to ensure that it passes various validity checks and adheres to proper transformation rules. Of course, this must be done while the data is being transferred from heterogeneous sources to a central database/data warehouse. Early identification of data errors not only reduces application errors but also helps keep the business from making wrong decisions.

The varied sources and sheer size of data make the data-validation process labor-intensive, time-consuming, and expensive. Also, the ever-increasing use of DevOps practices and cloud deployments make data testing even more challenging. Industry research suggests that only a few organizations consider their data reliable. This is because most businesses validate less than 10% of their data, leaving 90% of their data unvalidated. Data-related problems can result in annual losses worth millions of dollars. Due to the possibility of bad data, enhancing testing coverage is essential. Validating data quality is one of the main goals of the ETL testing process. However, the large volume of data makes test coverage difficult to achieve and makes it difficult to understand what the quality of data really is.

ETL testing (that is validating that the data moved to the destination is complete, correct, and in the desired format) can be performed manually or with automation tools. Testing the ETL process is not as simple as testing a typical web or mobile application. ETL testing requires quick test results as source data is not always available for testing. Extra effort is required to understand the data mapping of each column of the tables and comprehend complex data dimensions and attributes. Huge volumes of data coming from different sources with varied formats and structures need to be verified against various transformation rules. To achieve all the above manually is time-consuming and tedious.

To overcome these issues, automated ETL testing is required. Numerous commercial automation tools are available in the market for ETL testing like Informatica, QuerySurge, etc. With an automated solution, ETL testing can be accomplished swiftly and effectively across multiple databases and flat files. Since commercial tools can be costly, an alternative approach is to develop your own in-house automation framework or solution to satisfy your ETL testing needs. Developing an in-house framework gives you the liberty to design and enhance a solution based on your data requirements and may save significant costs compared to commercial tools.

ETL validation is done at different levels and stages.

Stage 1-Structure Validation: Data sources like databases and flat files (including XML, JSON, CSV, and fixed width) are validated for their correctness in terms of the header/footer/sequencing information or schema. Only data that passes the validation makes it to a temporary database.

Stage 2 – Data Validation: Validation is performed between various data sources and the staging database to test whether the data has been moved correctly with the correct schema. At every stage, mapping files play an important role.

Stage 3 – Rule Validation: Validation is done between the staging database and the transformed target database to check whether the transformation rules have been applied correctly to the data.

Stage 4 – Reporting: ETL data testing results are collected and saved in an HTML report that can be viewed in any browser.

As organizations take fuller advantage of their business data, automated ETL testing will quickly provide them quality information with complete coverage and minimal effort. Working with a variety of clients over the years, Celsior has developed an automated ETL testing framework that can seamlessly fit ETL testing into clients’ existing CI/CD processes. While no solution will satisfy all needs, Celsior’s ETL test automation framework generally provides organizations with the following benefits:

• Easy setup and use
• Data quality at speed
• Reduced cost of testing
• Up to 100% test data coverage
• Improved defect detection ratio
• Accurate data validation–even at the record level
• Reliable, repeatable, and reusable processes
• File as well as schema validation
• Data checks and transformation rule validation
• Flexibility to validate sample data
• Comprehensive test reports with database queries
• Integration with CI/CD tools
• Quick data validation using distributed execution

Summary

The best practice for ETL testing is to employ an automation testing approach. Automated data validation gives organizations better confidence of data quality compared to manual validation. To get the best ROI (Return on Investment), it is important to select the right tool and framework to support your data testing needs. Having vast experience in data testing, Celsior can help you in selecting the right tools with a robust framework.

ABOUT THE AUTHORS

Vikas Shukla
Director – Quality Engineering
Celsior

Himanshu Gosain
Senior QE Architect
Celsior

Read other blogs of the series to get more insights on Quality Engineering, QE services, and QE Automation:

Moving from Quality Assurance to Quality Engineering
Test Automation – Getting the most out of open-source
Adding AI capabilities to a Test Automation Framework
Mobile Test Automation – How to get the most bang for your buck
Role of Quality Engineering within DevOps and CI/CD