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, 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 extracting schema. It transforms each of these n' different messages into m' outgoing messages. Each outgoing message contains a sub-set of the incoming data objects, but describes them with a different schema, namely a CDM schema. For the mapping, METL requires a matrix that consists of m'xn' mapping blocks. There are three problems, namely the sparsity of the matrix, the adaption of the matrix to changes in schemata versions and time efficiency. We solve these problems by block-partitioning, sub-matrix formation and pattern generalization. In this process, we derive permutation matrices. We show that they can be used for automated updates, for parallel computation in near real-time and compacting. The permutation matrices form the dynamic mapping matrix. For the solution, we draw on research into matrix partitioning (Quinn, 2004) and dynamic networks (Haase et.al., 2021).