Workload-Aware ETL Pipeline Architecture for Enterprise Data Warehousing: A Mixed-Method Study of SQL Server Integration Services and Azure Data Factory

Abstract

This research focuses on scalable ETL pipeline architectures for enterprise data warehousing, especially SQL Server Integration Services and Azure Data Factory are two significant methods for enterprise data integration and warehouse efficiency. It discusses the challenges in managing large and diverse organizational data, and explores the scalability of ETL design for accurate data transformation, timely processing, reliable data access, and enhanced analytics in today's business landscape. A mixed method approach is used, based on literature-based analysis and exploratory analysis in R Studio with multiple event log datasets, which are used to identify patterns of workflow, temporal variation, distribution of resources and status related transaction behavior relevant for scalable pipeline design. The results show that enterprise workflows are not uniformly distributed in terms of processing demand; they have a large number of repeated processing activities; the intensity of processes changes over time; and there is a clear relationship between the workflow results and the transaction value, meaning that resources do not participate equally in the processing. From this finding, it can be concluded that the following elements are important for having an effective enterprise ETL architecture: transformation capabilities, orchestration flexibility, workload balancing, and adapting to complex and changing operational conditions. In general, the study reveals that scalable data warehousing systems need to be efficient and flexible enough in their architecture to meet the demands for enterprise decisions to be made reliably.