A Time-constrained Algorithm for Integration Testing in a Data Warehouse Environment

Abstract

A data warehouse should be tested for data quality on regular basis, preferably as a part of each ETL cycle. That way, a certain degree of confidence in the data warehouse reports can be achieved, and it is generally more likely to timely correct potential data errors. In this paper, we present an algorithm primarily intended for integration testing in the data warehouse environment, though more widely applicable. It is a generic, time-constrained, metadata driven algorithm that compares large database tables in order to attain the best global overview of the dataset’s differences in a given time frame. When there is not enough time available, the algorithm is capable of producing coarse, less precise estimates of all datasets differences, and if allowed enough time, the algorithm will pinpoint exact differences. This paper presents the algorithm in detail, presents algorithm evaluation on the real project’s data and TPC-H dataset, and comments on its usability. The tests show that algorithm outperforms the relational engine when the percentage of differences in the database is relatively small, which is typical for data warehouse ETL environments. DOI: http://dx.doi.org/10.5755/j01.itc.47.1.18171