ETL Tools and Data Cleaning

[Data quality problems are first addressed at the Extract, Transform, Load (ETL) stage, where heterogeneous data is staged and processed for data inconsistencies and schema related transformations. However, data cleaning is multi phase activity and there is no single tool that comprehensively cleans the data and enables the schema transformations. Unfortunately, data inspection and cleaning is still largely a manual process which demand user defined codes for identification of inconsistencies in instance of data. The need of the hour is for a tool that will limit manual inspection and automate the process of Extract, transform and load (ETL)]

Data cleaning or scrubbing is a process of identifying the errors and inconsistencies in data and removing them using ETL tools that are off the shelf or homegrown. Single data collections can present “dirty data” that needs to be cleaned of misspellings, data entry errors, missing information and other invalid data. Heterogeneous data drawn from multiple sources present a number of errors and inconsistencies, which need to be resolved before the data can be meaningfully integrated for use by the organization.

When the data integration requirement scale increases in data warehouses, federated database systems or global web based information systems, the quality of data assumes paramount importance and the need for cleaning such data increases multifold. Redundancy of data, accuracy of data, consistency of data and issues relating to consolidation of data and de-duplication of information become urgent in the face of mission critical decisions that need to be taken on the basis of such data.

Every data warehouse project begins with a process of Extract, Transform and Load (ETL) wherein data drawn from heterogeneous sources are first staged, then cleaned and transformed before it is loaded for use in the data warehouse. The structure, representation or content of the data, so staged, will demand data transformation. The transformation may deal with schema evolution, migration of a legacy system or integration of multiple data sources. The data differences could lie in the attributes, data types, component structures, integrity constraints and a host of other factors at the schema level or in differences in value representations, gender representations or availability of information at different aggregation levels in an instance.

Schema integration and data cleaning are both to be done before solutions can be found. While schema related problems of data could be addressed by improving schema design, schema integration or schema translation, instance level problems will require removal of errors and inconsistencies in data content. The problem of data cleaning is further compounded if multiple data sources have to be integrated and these disparate data sources contain dirty data that is differently represented, overlapping or contradicting. Naming convention differences and conflicts can make the process difficult or structural variations may render the whole a nightmarish experience.

Data cleaning, therefore, is a long drawn process that is done in several phases and maybe in several iterations. A detailed data analysis is followed by a manual inspection of data or data samples. The data samples are then used to gain metadata and define data properties and also evaluate data quality. Transformation workflow and mapping rules are used to describe the “dirtiness” of data and the number of steps that may be required to clean and transform the data. Schema translations are used to map sources to a common data model using relational representations. The query and mapping language used are designed to enable the automatic generation of transformation code and invoke the necessary user defined cleaning codes and any special purpose ETL tools designed. The correctness and efficiency of the transformation workflow and definitions are then tested and evaluated. Finally, the data warehouse is refreshed and the cleaned data replaces the dirty data in the original sources in order to ensure that the legacy applications are loaded with the cleaned data and the wheel is not reinvented again and again.

Unfortunately, large portions of this ETL data cleaning process is achieved manually or by the use of low-level programs that allow the user specify cleaning functionality through a proprietary API. These programs are extremely difficult to write and maintain and there is no automatic data analysis support to auto detect data errors and inconsistencies. A few transformation libraries provide for data transformation and cleaning needs such as data type conversions, string functions, arithmetic functions and statistical functions. Extraction values from free form attributes are also not automatic and support for instance matching is restricted to the use of the join construct. The few ETL tools available for data cleaning are focused upon specific types of data cleaning such as duplicate elimination or identification of errors in names and addresses. Hence it becomes necessary to support these tools with other complementary tools for a more effective data cleaning process. However, this effort too, proves ineffective as many of these ETL tools suffer from interoperability problems.

The need of the hour is for ETL tools that are designed to limit the manual inspection of data and is extensible enough to be reused with other data sources. The tool should be capable of performing data cleaning activities in conjunction with schema related data transformations based on comprehensive metadata. Declarative mapping functions for data cleaning should also be components that can be used with more than one data source or query function. The tool should support workflow infrastructure and execute data transformation steps for multiple sources efficiently and reliably.