Automated data pipelines for processing IoT data

What is a data pipeline? A definition

A data pipeline is an automated process that transports, transforms and cleanses data from a source to a destination. It consists of a series of steps or components that are executed in a specific order to convert raw data into usable information.

Core components of a data pipeline:

1. Data sources: The starting points of the data, which can come from sensors, databases, APIs or other systems.
2. Ingestion: The process of collecting and importing the raw data into the pipeline system.
   Transformation: The conversion of the raw data into a format suitable for analysis or further processing. This may include cleansing, filtering, aggregating or enriching the data.
3. Storage: Storing the processed data in a data store, such as a database, data warehouse or data lake.
4. Analysis and visualization: The final step in which the processed data is used for reports, dashboards or machine learning.

Why automated data pipelines are important

Data in a company is often stored in different places, such as in various databases (SQL, NoSQL, etc.), in internal file systems or in cloud storage (e.g. Azure, AWS, Google, etc.). Over the years, so-called “data islands” can form, where the data is stored in different formats but has no proper versioning or standardization. As a result, the added value of the data is often not fully exploited, as raw data cannot be equated with clean analysis data.

Robust extraction and transformation of data is an important foundation for a company to achieve a certain level of data maturity. If a data pipeline process is not implemented for many existing data landscapes in a company, the company runs the risk of not recognizing the added value from this data. This makes it very difficult, if not impossible, to achieve higher data quality and data maturity. Achieving the right level of data maturity in a company often requires a lot of effort and a stable data pipeline process, because only with the right data maturity can a company achieve higher data utilization, more data potential and better data integration throughout the company [1].

Advantages of a data pipeline:

• Automation: data pipelines reduce manual effort and minimize errors through automated processes.
• Efficiency: A data pipeline accelerates data flow and processing, which leads to faster insights.
• Scalability: Large volumes of data can be processed and expanded as required.
• Reliability: Data pipelines ensure that data is processed consistently and accurately.

In order to obtain a solid overview of data landscapes (data domains) and to be able to transform, cleanse and enrich data in a controlled manner, a central ETL process is often used as a data pipeline process so that standardization and versioning of the analysis data is guaranteed at the end.

What does ETL mean?

ETL stands for “Extract”, “Transform” and “Load” and describes a data integration process. As part of this process, data from several internal or external data sources is transferred to standardized data storage [2], which is then stored in a data management system (e.g. data warehouse). The ETL process is often seen as a general procedure for processing data in as controlled a manner as possible. The term ETL pipeline, on the other hand, is often used when talking about the technical perspective and implementation.

The ETL process consists of three main phases:

1. Extract: the extraction of data from a wide variety of data sources (internal or external).
2. Transform: The transformation of the data structures and data content into a predefined target schema of the target storage location.
3. Load: The loading or storing of the data at a target location (e.g. in a database).

The data records in the source system are selected first, whereby a connection and access to these source systems must be guaranteed. This data is then loaded (extract) from the source system (e.g. via a REST API, database access or SFTP) and stored in a temporary storage location for the transformation. The transformation to the target schema is then carried out.

The three main ETL phases in detail

The three main ETL phases are defined as follows:

Extraction

The ETL process begins with the extraction step, in which the data is first selected from the connected data sources and then transferred to a cache. The cache can, for example, simply be a directory on a system where files are stored, such as CSV files, or a database where only semi-structured data is temporarily stored for the further transformation step. A distinction is made between internal and external data sources. Internal data sources are, for example, queries to an internal database or files from internal servers. External data sources include API queries to an external interface with an API key.

Transformation

As part of the transformation, the previously stored temporary data is loaded into a program and transformation rules are applied to it. Before this step takes place, the transformation rules and the target schema of the data must be defined in advance. This step also requires more resources for large amounts of data. The necessary computing power for larger amounts of data can be achieved using external “jobs” (schedulers) and the transformation can therefore also be carried out on powerful virtual machines.For example, an internal virtual machine triggers an “external job” in the cloud, waits only for the response from the job and receives the file path. The file path can then be used to access the transformed data. During the transformation, it should always be kept in mind that the complexity can quickly increase due to the many transformation rules and parallelization of the “jobs”.

Loading

Once the data has been successfully transformed, another job or a load process can store the data in a target location. A destination is often a data warehouse, i.e. a database intended for analysis purposes. The transformed data can be stored immediately after the transformation by starting a program routine, for example by means of an event, after the file has been stored in the temporary target location following the transformation.However, loading can also be carried out by means of a defined time-based program routine, such as daily or weekly program routines.

Relevance of the ETL process in IoT

An ETL process is often a very important part of a company when it comes to evaluating data with downstream analyses and deriving added value from this data. In the area of IoT (Internet of Things) and IIoT (Industrial Internet of Things), a particularly large amount of log data is often generated with information about communication protocols and network parameters. These network parameters are often read out when testing IoT devices with so-called “sniffers” and often stored in unstructured form in loose log files. The log files can become very large and not all network information is always relevant for analysis purposes. An ETL process is often necessary to prepare the relevant information in such a way that it can be used for root cause analysis, for example, in which anomalies in the network are to be detected (e.g. intrusion detection). This processes the data specifically for each data domain and stores it in the defined target data format for analysis purposes. In times of big data and machine learning, data pipeline processes such as the ETL process have become increasingly relevant in the IoT/IIoT sector. Furthermore, a great deal of telemetry data is processed in the IoT context, which should ideally also be analyzed automatically in order to gain quick insights into the measured values and behaviour of the field end devices.

Challenges when implementing an ETL process

Implementing a fully automated ETL process in a company and integrating all data landscapes can involve a great deal of effort. Setting up and testing the ETL pipeline can take a lot of time until the entire ETL process is fully operational and stable. Since an ETL pipeline processes the data according to time-defined routines, an ETL pipeline can be slow and also make troubleshooting more difficult. Before an ETL pipeline is implemented in a company, the ETL architecture must be well thought out. In addition, traceability, i.e. the traceability of the individual steps in an ETL pipeline, must be guaranteed if errors occur and data deviates from the defined target format. Furthermore, there should be a concept for an error log from the outset to simplify error handling and maintenance steps.

Conclusion: Data pipelines are essential for efficient data utilization

Data pipelines are essential for modern data processing architectures, especially in the context of IoT, where large amounts of data are continuously generated. They enable companies to use this data efficiently to make informed decisions and gain valuable insights. At ithinx, we are happy to advise you on your IoT data management and support you in the smooth integration of automated data pipelines.

– Ismar Halilcevic, Systems Engineer (ithinx)

Quellen

[1] Reis, J. und Housley, M. (2023). Handbuch Data Engineering – Robuste Datensysteme planen und erstellen, Seite 40. O’Reilly Media, Inc., Sebastopol, CA 95472. ISBN: 9783960107682, 3960107684.

[2] Reichle, F. (2014). Datenaustausch zwischen SAP BW und relationalen Datenbanken: Entwurf und Entwicklung eines ETL-Prozesses, Seite 6. Diplomica Verlag, Hermannstal 119 k, 22119 Hamburg. ISBN: 9783958506022, 395850602X.