Automated ETL Testing on the Data Quality of a Data Warehouse

The Extract-Transform-Load (ETL) process in data warehousing involves extracting data from source databases, transforming it into a form suitable for research and analysis, and loading it into a data warehouse. ETL processes can use complex transformations involving sources and targets that use different schemas, databases, and technologies, which make ETL implementations fault-prone. In this paper, we present an approach for validating ETL processes using automated balancing tests that check for various types of discrepancies between the source and target data. We formalize three categories of properties, namely, completeness, consistency, and syntactic validity that must be checked during testing. Our approach uses the rules provided in the ETL specifications to generate source-to-target mappings, from which balancing test assertions are generated for each property. We evaluated the approach on a real-world health data warehouse project and revealed 11 previously undetected faults. Using mutation analysis, we demonstrated that our auto-generated assertions can detect faults in the data inside the target data warehouse.

… ACM twelfth international workshop on Data …, 2009

Many data quality projects are integrated into data warehouse projects without enough time allocated for the data quality part, which leads to a need for a quicker data quality process implementation that can be easily adopted as the first stage of data warehouse implementation. We will see that many data quality rules can be implemented in a similar way, and thus generated based on metadata tables that store information about the rules. These generated rules are then used to check data in designated tables and mark erroneous records, or to do certain updates of invalid data. We will also store information about the rules violations in order to provide analysis of such data. This could give a significant insight into our source systems. Entire data quality process will be integrated into ETL process in order to achieve load of data warehouse that is as automated, as correct and as quick as possible. Only small number of records would be left for manual inspection and reprocessing.

Procedia Computer Science

The accuracy and relevance of Business Intelligence & Analytics (BI&A) rely on the ability to bring high data quality to the data warehouse from both internal and external sources using the ETL process. The latter is complex and time-consuming as it manages data with heterogeneous content and diverse quality problems. Ensuring data quality requires tracking quality defects along the ETL process. In this paper, we present the main ETL quality characteristics. We provide an overview of the existing ETL process data quality approaches. We also present a comparative study of some commercial ETL tools to show how much these tools consider data quality dimensions. To illustrate our study, we carry out experiments using an ETL dedicated solution (Talend Data Integration) and a data quality dedicated solution (Talend Data Quality). Based on our study, we identify and discuss quality challenges to be addressed in our future research.

Commonly, DW development methodologies, paying little attention to the problem of data quality and completeness. One of the common mistakes made during the planning of a data warehousing project is to assume that data quality will be addressed during testing. In addition to the data warehouse development methodologies, we will introduce in this paper a new approach to data warehouse development. This proposal will be based on integration data quality into the whole data warehouse development phase, denoted by: integrated requirement analysis for designing data warehouse (IRADAH). This paper shows that data quality is not only an integrated part of data warehouse project, but will remain a sustained and ongoing activity

International Journal of …, 2010

Data quality is a critical factor for the success of data warehousing projects. If data is of inadequate quality, then the knowledge workers who query the data warehouse and the decision makers who receive the information cannot trust the results. In order to obtain clean and reliable data, it is imperative to focus on data quality. While many data warehouse projects do take data quality into consideration, it is often given a delayed afterthought. Even QA after ETL is not good enough the Quality process needs to be incorporated in the ETL process itself. Data quality has to be maintained for individual records or even small bits of information to ensure accuracy of complete database. Data quality is an increasingly serious issue for organizations large and small. It is central to all data integration initiatives. Before data can be used effectively in a data warehouse, or in customer relationship management, enterprise resource planning or business analytics applications, it needs to be analyzed and cleansed. To ensure high quality data is sustained, organizations need to apply ongoing data cleansing processes and procedures, and to monitor and track data quality levels over time. Otherwise poor data quality will lead to increased costs, breakdowns in the supply chain and inferior customer relationship management. Defective data also hampers business decision making and efforts to meet regulatory compliance responsibilities. The key to successfully addressing data quality is to get business professionals centrally involved in the process. We have analyzed possible set of causes of data quality issues from exhaustive survey and discussions with data warehouse groups working in distinguishes organizations in India and abroad. We expect this paper will help modelers, designers of warehouse to analyse and implement quality warehouse and business intelligence applications.

IOSR Journal of Engineering, 2013

Ensuring Data Quality for an enterprise data repository various data quality tools are used that focus on this issue. The scope of these tools is moving from specific applications to a more global perspective so as to ensure data quality at every level. A more organized framework is needed to help managers to choose these tools so that that the data repositories or data warehouses could be maintained in a very efficient way. Data quality tools are used in data warehousing to ready the data and ensure that clean data populates the warehouse, thus enhancing usability of the warehouse. This research focuses on the on the various data quality tools which have been used and implemented successfully in the preparation of examination data of University of Kashmir for the preparation of results. This paper also proposes the mapping of data quality tools with the process which are involved for efficient data migration to data warehouse.

2004

Today’s informational entanglement makes it crucial to enforce adequate management systems. Data warehousing systems appeared with the specific mission of providing adequate contents for data analysis, ensuring gathering, processing and maintenance of all data elements thought valuable. Data analysis in general, data mining and on-line analytical processing facilities, in particular, can achieve better, sharper results, because data quality is finally taken into account. The available elements must be submitted to an intensive processing before being able to integrate them into the data warehouse. Each data warehousing system embraces extraction, transformation and loading processes which are in charge of all the processing concerning the data preparation towards its integration into the data warehouse. Usually, data is scoped at several stages, inspecting data and schema issues and filtering all those elements that do not comply with the established rules. This paper proposes an ag...

Data warehouse (DW) testing is a very critical stage in the DW development because decisions are made based on the information resulting from the DW. So, testing the quality of the resulting information will support the trustworthiness of the DW system. A number of approaches were made to describe how the testing process should take place in the DW environment. In this paper we will present briefly these testing approaches, and then a proposed matrix that structures the DW testing routines will be used to evaluate and compare these approaches. Afterwards an analysis of the comparison matrix will highlight the weakness points that exist in the available DW testing approaches. Finally, we will point out the requirements towards achieving a homogeneous DW testing framework. In the end, we will conclude our work.

International Journal of Recent Technology and Engineering, 2019

Data Quality, Database Testing, and ETL Testing are all different techniques for testing Data Warehouse Environment. Testing the data became very important as it should be guaranteed that the data is accurate for further manipulation and decision making. A lot of approaches and tools came up supporting and defining the test cases to be used, their functionality, and if they could be automated or not. The most trending approach was the automating of testing data warehouse using tools, the tools started firstly by supporting only the automation of running the scripts helping the developers to write the test case just once and run it multiple times, then the tools developed and modified to automate the creation of the testing scripts and offer their service as a complete application that supports the creation and running of the test cases claiming that the user can work without the need of expertise and high technicality and just by being an end user using the tool's GUI. Banking sector differs completely than any other industry, as data warehouse in banking sectors collects data from multiple sources and multiple branches with different data formats, and quality that should then be transformed and loaded in the data warehouse and classified into some data marts to be used in different dashboards and projects that depend on high quality and accurate data for further decision making and predictions. In this paper we propose a strategy for data warehouse testing, that automates all the test cases needed in banking environment

International Journal of Innovative Technology and Exploring Engineering, 2019

Data quality (DQ) is as old as the data is. In last few years it is found that DQ can’t be ignored during the process of data warehouse (DW) construction and utilization as it is the major and critical issue for knowledge experts, workers and decision makers who test and query the data for organizational trust and customer satisfaction. Low data quality will lead to high costs, loss in the supply chain and degrade customer relationship management. Hence to ensure the quality before using the data in DW, CRM (Customer Relationship Management), ERP (Enterprise Resource Planning)or business analytics application, it needs to be analyzed and cleansed. In this, we are going to find out the problem regarding dirty data and try to solve them.