Performance Optimization Of ETL Pipelines In Distributed Data Warehouse Environments: A Network-Aware Scheduling Approach

Abstract

The basic data integration infrastructure in enterprise data warehousing systems is the Extract, Transform and Load (ETL) pipeline. Quite often, as the volume of data in the organization grows and the variety of data sources increases, traditional ETL architectures suffer from serious performance problems due to sequential processing bottlenecks, poor network resource utilization, and limited parallelization of the transformation workload on distributed computing nodes. This paper presents a network-aware ETL scheduling framework that dynamically schedules extraction, transformation, and load tasks among different distributed processing nodes considering the available network bandwidth, the capability of processing nodes, and an estimation of the data transfer cost. The proposed framework embeds SQL Server Integration Services (SSIS) architectural patterns and presents a cost-based task dispatcher which decreases end-to-end execution time of pipelines by streaming data from the data source to the data target in parallel, minimizes the amount of data transferred between nodes by placing transformations in an area of the pipeline where the data is stored in the node, and distributes the workload of the pipeline across the different processing nodes. Experimental tests on enterprise data warehouse workloads show that the average execution time for pipelines is reduced by 31.4% and network resource use is improved by 26.8% compared with a traditional sequential execution of ETL pipelines. The outcomes are relevant to enterprise reporting scenarios with a big number of distributed source systems that need to provide reporting data with high throughput and low latency.