METL: a modern ETL pipeline with a dynamic mapping matrix

Canonical data models (CDM) have gained traction as a pattern for data integration in streaming pipelines that extract, transform and load data (ETL). CDMs are in particular useful for integrating microservice systems. (Villaca et al 2020, Oliveira et al 2019) However, the transformation to a CDM is complex. (Lemcke et al 2012) In this paper, we present a new solution that is based on a new dynamic mapping matrix (DMM). The DMM has been implemented into an app called Message ETL (METL). METL is the key part of a new ETL streaming pipeline at EOS. EOS is part of the Otto-Group, the second-largest e-commerce provider in Europe. The pipeline is based on Kafka streams. METL transforms Kafka messages, that contain a set of data objects described by one of n' different extracting schemata. It transforms each of these incoming messages into several outgoing ones. Each outgoing message contains a sub-set of the incoming data objects, but describes them with a different schema, namely one of m' different CDM schemata. For the mapping, METL requires a matrix that consists of m'xn' sub-matrix mapping blocks. There are three problems, namely the sparsity of the matrix, the adaption of the matrix to changes in the schemata and time efficiency. We solve these problems by block-partitioning, sub-matrix formation and pattern generalization. In this process, we derive sets of permutation matrices. We show that they can be used for automated updates, for parallel computation in near real-time and compacting. The set of all permutation matrices forms the dynamic mapping matrix. For the solution, we draw on research into matrix partitioning (Quinn 2004) and dynamic networks (Haase et al 2021).