Logo
ScienceStack
Table of Contents
Fetching ...
Sign In

Mathematical Foundations of Modeling ETL Process Chains

Levin Maier, Lucas Schulze, Robert Lilow, Lukas Hahn, Nikola Krasowski, Arnulf Barth, Sebastian Gaebel, Ferdi Güran, Oliver Hanau, Giovanni Wagner, Falk Borgmann, Oleg Arenz, Jan Peters

Abstract

Extract-Transform-Load (ETL) processes are core components of modern data processing infrastructures. The throughput of processed data records can be adjusted by changing the amount of allocated resources, i.e.~the number of parallel processing threads for each of the three ETL phases, but also depends on stochastic variations in the per-record processing times. In chains of multiple consecutive ETL processes, the relation between allocated resources and overall throughput is further complicated, for example by the occurrence of bottlenecks affecting all subsequent ETL processes. We develop a mathematical model of ETL process chains that is accurate at the level of time-aggregated throughput and suitable for efficient simulation. The process chain is represented as a controlled discrete-time Markov process on a directed acyclic graph whose edges are individual ETL processes. We model the mean throughput as a bounded, monotone function of the number of parallel threads, to capture the diminishing benefit of allocating more threads. We furthermore introduce a Flow Balance postulate linking number of threads, mean throughput, and mean processing time. The stochastic processing times are then modeled by non-negative heavy-tailed distributions around the mean processing time. This framework provides a principled simulator for ETL networks and a foundation for learning- and control-based resource allocation.

Mathematical Foundations of Modeling ETL Process Chains

Abstract

Extract-Transform-Load (ETL) processes are core components of modern data processing infrastructures. The throughput of processed data records can be adjusted by changing the amount of allocated resources, i.e.~the number of parallel processing threads for each of the three ETL phases, but also depends on stochastic variations in the per-record processing times. In chains of multiple consecutive ETL processes, the relation between allocated resources and overall throughput is further complicated, for example by the occurrence of bottlenecks affecting all subsequent ETL processes. We develop a mathematical model of ETL process chains that is accurate at the level of time-aggregated throughput and suitable for efficient simulation. The process chain is represented as a controlled discrete-time Markov process on a directed acyclic graph whose edges are individual ETL processes. We model the mean throughput as a bounded, monotone function of the number of parallel threads, to capture the diminishing benefit of allocating more threads. We furthermore introduce a Flow Balance postulate linking number of threads, mean throughput, and mean processing time. The stochastic processing times are then modeled by non-negative heavy-tailed distributions around the mean processing time. This framework provides a principled simulator for ETL networks and a foundation for learning- and control-based resource allocation.

Paper Structure

This paper contains 10 sections, 20 equations, 2 figures.

Table of Contents

  1. Introduction
  2. Outline of the article.
  3. Acknowledgments.
  4. Problem Setting: ETL Process Chains
  5. Goal.
  6. Mathematical Modeling of ETL processes
  7. Discrete-Event Simulation
  8. Future directions.
  9. ETL Processing Times
  10. Future directions.

Figures (2)

  • Figure 1: Directed acyclic graph $\mathcal{G} = (\mathcal{N}, \mathcal{E})$ representation of an ETL system. Circles represent data pools and gears represent ETL pipelines. This example has one overall source pool S (yellow node) and target pool T (green node), as well as multiple intermediate pools (blue nodes).
  • Figure 2: Observed data compared with fitted models.

Theorems & Definitions (5)

  • Definition 1
  • Definition 2
  • Definition 3
  • Example 1
  • Example 2