Building Scalable ETL Pipelines for HR Data

Аннотация

The article is devoted to the development and experimental validation of scalable ETL pipelines for HR data, aimed at bridging the gap between the volume of OPEN ACCESS heterogeneous workforce events and the capabilities of SUBMITED 22 April 2025 traditional nightly processes. The relevance of the study ACCEPTED 19 May 2025 is determined by the exponential growth of the HR PUBLISHED 10 June 2025 VOLUME Vol.10 Issue 06 2025 technology market to USD 40.45 billion in 2024 and its forecasted doubling by 2032 at a 9.2% CAGR, as well as CITATION by the fragmentation of corporate systems, which leads to data incompleteness, inconsistency, and latency in Chetan Urkudkar. (2025). Building Scalable ETL Pipelines for HR Data. The American Journal of Engineering and Technology, 7(06), 88–95. turnover metrics and talent-development program https://doi.org/10.37547/tajet/Volume07Issue06-09. effectiveness analysis. The work is aimed at formalizing COPYRIGHT requirements for Extraction, Transformation, Loading, © 2025 Original content from this work may be used under the terms Scalability, and Observability; at designing a of the creative commons attributes 4.0 License. containerized architecture based on Kubernetes, Apache Airflow, Spark, and Flink-CDC; and to ensure low latency, exactly-once semantics as well as linear scaling up to 32 worker pods with an efficiency η of 0.78 or greater. The novelty of the work lies in the first formal model that integrates adaptive API-request throttling with idempotent SCD-attribute transformations for a hybrid Iceberg/Snowflake storage layer and a complete observability system using Prometheus and OpenTelemetry with real-time alerts. An experimental evaluation on a private Kubernetes cluster under load up to 10⁸ records per day demonstrated end-to-end latency ≤ 15 min in batch mode and p95 latency reduction to 48s in near-real-time mode, throughput up to 18.7k records/min with linear worker scaling (η = 0.82), and full lineage-graph traceability in compliance with GDPR. The main conclusions confirm that the proposed architecture provides reliable and reproducible HR-data integration with minimal latency and predictable cost, paving the way for practical deployment in large enterprises. This article will be helpful to data engineers, cloud-architecture designers, and project managers in HR analytics automation. Keywords: ETL pipeline, HR data, scalability, Observability, Iceberg, Snowflake, Kubernetes, Airflow. The American Journal of Engineering and Technology 88 https://www.theamericanjournals.com/index.php/tajet Introduction: The growing interest in data-driven HR ● Extraction (E) formalized by ε: S × τ → P(R), decision-making faces a disparity between the volume supporting full- and delta-load modes, with of heterogeneous information generated by dozens of extraction latency λ(E) ≤ 3 min and throughput μ(E) specialized systems and the legacy integration ≥ 50,000 records/s per source. Adaptive HTTP- architectures designed for periodic, relatively small request throttling (@adaptive_rate_limit) handles batches. Consequently, attempts to build end-to-end HTTP 429/503 responses. analytics across the employee lifecycle encounter data incompleteness, inconsistency, and latency, ● Transformation (T) is defined by τ̂: P(R) → P(R′), undermining the reliability of turnover metrics, talent- performing idempotent, deterministic cost analyses, and development-program effectiveness. normalization according to the global schema χ and correct handling of SCD attributes. HR digitalization exacerbates this issue: the global HR- ● Loading (L) as λ: P(R′) → Χ into Iceberg/Snowflake technology market is estimated at USD 40.45 billion in column stores, guaranteeing exactly-once semantics 2024 and is projected to grow at a 9.2% CAGR to USD 81.84 billion by 2032 [1]. Already, 91% of organizations with bulk-overwrite and continuous-merge with at least 100 employees have adopted at least one strategies, and materialization latency λ(L) ≤ 5 min specialized HR application, and one in four enterprises for batches up to 10⁶ records. uses five or more systems concurrently, forming a ● Scalability (S) requiring ∂Q/∂p ≈ const. up to p = 32 fragmented source ecosystem [2]. Thus, the volume and worker pods, efficiency η ≥ 0.78, confirmed velocity of events—training registrations, grade experimentally (η = 0.82 at p = 30). changes, time-clock entries—far exceed the throughput of traditional nightly ETL processes. ● Observability (O) via 50+ Prometheus metrics per pod, span-ID correlation for OpenTelemetry tracing, Three-factor groups complicate HR-data integration. and centralized JSON logging for real-time alerts First is schema heterogeneity: identical entities (e.g., (t_alert ≤ 60 s). “position”) are represented by inconsistent attributes To validate the architecture, a prototype pipeline was and encodings, with evolving APIs lacking version deployed on a private Kubernetes cluster using Airflow notifications. Sec