Conquering the challenges of Data Warehouse “ETL� Testing
ETL stands for Extract-Transform-Load and is a typical process of loading data from a source system to the actual data warehouse and other data integration projects. It is important to know that independent verification and validation of data is gaining huge market potential. Many organizations and companies are now thinking of implementing ETL and Data warehouse processes as they realize that valid data in production is critical for correct business decision making. Importance of Data Warehouse for Organizations Organizations with already well defined IT practices are at an innovative stage to create the next level of technology transformation, by constructing their own data warehouse to store and monitor real-time data. They have realized the need to test this data to ensure data completeness and data integrity. They have also realized the fact that comprehensive testing of data at every point throughout the ETL process is important and inevitable, as more of this data is being collected and used for strategic decision-making that affects their business forecasts. But, certain current strategies being followed are time-consuming, resource-intensive, and inefficient. Thus, a wellplanned, well defined and effective ETL testing scope guarantees smooth conversion of
http://gallop.net/
the project to the final production phase. Now, let us see some of the issues that are common with ETL and Data Warehouse testing. Some of the important ETL Testing Challenges are:
Unavailability of inclusive test bed at times Lack of proper flow of business information Loss of data might be there during the ETL process Existence of many ambiguous software requirements Existence of apparent trouble acquiring and building test data Production sample data is not a true representation of all possible business processes
Some of the important issues with Data Warehouse Testing are: Data Warehouse/ETL testing requires SQL programming: This has become a major issue as most of the testers are manual testers and have limited SQL coding skills, thus making data testing very difficult Performing Data completeness checks for transformed columns is tricky Certain testing strategies used are time consuming Types of ETL Testing Data is important for all businesses to make critical decisions. ETL testing plays a significant role in verifying, validating, and ensuring that the business information is exact, consistent, and reliable. ETL Testing is data centric testing, which involves comparing large volumes of data across heterogeneous data sources. This data centric testing helps in achieving high quality data by getting the erroneous processes fixed quickly and effectively.
Data-Centric Testing: Data-centric testing revolves around testing the quality of the data. The objective of the data-centric testing is to ensure that valid and correct data is in the system. It ensures that proper ETL processes are applied on source database and transform and load data in the target database. It further ensures that proper system migration and upgrades are performed.
Data Accuracy Testing: This type of testing ensures that the data is accurately transformed and loaded as expected. Through this testing, we can identify errors
http://gallop.net/
obtained due to truncation of characters, improper mapping of columns, implementation errors in logic etc.
Data Completeness Testing: These tests help to verify that all the expected data is loaded in target from the source. It helps to verify the count of rows in driving table matches with the counts in the target table.
Data Integrity Testing: This type of testing helps to check for counts of ‘unspecified’ or ‘unmatched’ rows with respect to foreign keys and to compare the percentage of foreign key matches by running queries on data.
Business Testing: This testing ensures that the data fulfills the critical business requirements. The data is evaluated against the business rules stated. This test also checks whether data has been moved, copied, or loaded completely and accurately.
Data Transformation Testing: It is done in many cases as it cannot be achieved by writing on source SQL query and comparing the output to the target.
Production Validation Testing: This type of testing is done on data that is being moved to production. In order to achieve effective business decisions, data in the production systems should have valid and correct order.
From the above methods, it is obvious that with the ever changing needs of the business and similar changes in the source systems, it effectively drives continuous change in the data warehouse schema and the data being loaded. Hence, it is necessary that development and testing processes are clearly defined. ETL and Data Warehouse testing should be followed by impact-analysis and should focus on strong alignment between development, operations, and the business teams. Let us see below some of the ETL testing tools that can be used.
ETL Testing Tools
http://gallop.net/
ETL testing can be performed either manually, or by using tools like Informatica, QuerySurge etc. However, much of the ETL testing is done by SQL scripting or eyeballing of data on spreadsheets. The usage of automated testing tools ensures that only trusted data will be delivered in your production system. The types of testing that can be achieved with ETL tools include unit, functional, regression, continuous integration, operational monitoring and more. Coming to the benefits, you can reduce testing time by about 50% to 90% and also reduce resource utilization. ETL testing lowers business risks and instils confidence in the data. The two documents that will be used by an ETL tester are ETL mapping sheets and the DB Schema of source and target. Gallop’s Comprehensive Capabilities with Respect to ETL/Data Warehouse Testing Services
Gallop Solutions has helped many clients with ETL/Data warehousing testing that produced effective results of quick test cycles and achieved no defect leak to production and significantly met the production go timelines. For any of your ETL/Data Warehousing Testing requirements, Gallop’s testing experts are available to help you out with your projects. Tags: BI Testing, Data Migration, Data Warehouse Testing, DWH Testing, ETL Processing, ETL Testing, Informatica
http://gallop.net/