Enhancing ETL Performance in Retail Data Warehousing through Partitioning, Materialization, and Temporary Staging Approaches

Abstract

The accelerating accumulation of transactional records across the retail sector has placed considerable pressure on data infrastructures, particularly in supporting time-sensitive business analytics. Within the construction of a retail data warehouse, the Extract, Transform, Load (ETL) stage frequently emerges as the principal performance bottleneck whenever data volume scales upward. The present study seeks to examine and contrast the performance behaviour of three commonly applied ETL optimisation techniques, namely Partitioned Table, Materialized View, and Temporary Table, in order to identify which approach is most appropriate for handling large-volume data integration. To address this objective, an experimental investigation was conducted using five years of accumulated sales transaction data, which were processed through three independent ETL pipelines built in Pentaho Data Integration. Each pipeline was assessed in terms of execution time, CPU utilisation, and RAM consumption. The empirical results reveal that the Partitioned Table approach consistently yields superior performance across every observed metric, achieving the shortest average execution time of 26.4 seconds, the lowest CPU usage at 42 percent, and the smallest memory footprint of 385 MB. These outcomes suggest that segmenting data along temporal attributes provides substantial benefits for ETL workloads, positioning partitioning as the most viable optimisation strategy for retail data warehouse environments. Beyond the technical findings, the study also offers practical guidance for selecting ETL methods that align with data growth trajectories and the constraints of available system resources.