As a data engineer, a common task is building ETLs: processes that extract, transform and load data. A logical use case for such a process is importing data from a source into a data warehouse, so that the imported data can be combined with other knowledge in the data warehouse.

Such an import typically consists of three steps: 1: data is retrieved from the source (extracted), 2: data is transformed so that it fits the data warehouse structure (transformed), and finally step 3: the data is stored in the data warehouse (loaded).

This blog post focuses primarily on the second step: the transformation of data.

Why is it necessary?

To understand the approach we will discuss, it is worth first answering the question of why we do this at all.

When we retrieve data from a system, it often provides us with useful insights. Usually, data from a specific system can only answer a limited number of questions that are moreover often bound to a specific domain. Useful, but limited in scope.

Take a specific Google advertising campaign, for example. Google Ads gives you access to a variety of reports and statistics, which in themselves are certainly useful. But by the nature of the service itself, the reports and statistics relate only to the advertisements. They provide no insight into, for example, our stock levels, margins, or the relationship with customers beyond the advertisements. To make optimal use of the data that Google Ads offers, we should combine that data with data from other sources. To do so, we would need to import the Google Ads data into our own system - preferably a data warehouse - which also contains data from other (operational) systems. Only then can insights go beyond a specific domain.

Some considerations

We could now opt for a simple approach and 'just' download all the data that Google Ads makes available and store it unprocessed in our data warehouse. That gets us a step further, but imagine that Google Ads uses a different definition and a different identification number to identify customers than our own Customer Relationship Management system, for example. That also means we cannot combine our knowledge about our customer with Google data. What a waste!

What we prefer is for Google Ads data in our data warehouse to be stored in such a way that it aligns with the other data we have from other systems, so that it can easily be combined. To make this possible, we need to translate and transform the data into a particular structure.

Suppose we have a data warehouse with data from multiple sources. Let us further assume that the data warehouse stores data in a relational model and that the data is normalised to a certain degree. Then we have structure for further analysis purposes. We want the new data to be stored efficiently, and moreover, we may not want to store all data from the source system - because we do not plan to use all the data, because we already obtain the same data from another source, or because we only use part of the software and not all functionalities through which it is made available.

An important step in translating the source data model into ours is therefore to first ask what kind of questions we want to answer with this data. Based on that, we can make a selection of what we want to import. It may also be that we are interested in the data at a lower frequency than the source offers, or that we prefer some of the data in aggregated rather than raw form. Numerous reasons, then, not to download all data continuously.

To make the storage and use of data efficient, we want the data in a relational model, even if this may not be the case at the source, or if it is not always entirely consistent.

When importing data from, for example, a REST API, the data may be relational, but there is often no mechanism that guarantees consistency at a particular point in time. Moreover - even if the data is consistent at a particular time - we probably cannot retrieve it fast enough to ensure it remains so at the moment we process it. In practice, we are often unable to retrieve all data in a single request simply because the data is too voluminous. The consequence is that we must make multiple requests, with the risk that data becomes inconsistent.

Besides identifying entities and their relationships, considerations must therefore be made about timing and potential inconsistency of data.

There are various approaches to dealing with inconsistency in ETL processes - too many for this article.

To make optimal use of the new data source, we want to map entities we import data about to entities we already store in our data warehouse. For example, customer IDs to uniquely identify our customers, stored as a normalised customers table in our data warehouse. We can assume that Google also uses customer IDs. We then want the customer IDs from Google Ads to match the customer IDs we have in our system.

To achieve this, we can perform a 'mapping' during our transformation step and store the data with the assigned customer ID (while keeping the original Google Ads customer ID alongside) or we can store the data with the Google Ads customer ID and provide a mapping table in our data warehouse.

In the same way, for all entities we find in the Google Ads data, we want to be able to match them with already existing entities in our data warehouse. This enables us to combine newly imported data and gain new insights. In this way, we identify all our business entities from all our sources.

Summary

  • Determine which data is available
  • Determine which data you want to import (be conservative here - if you cannot think of a question this data could answer, do not import it)
  • Model the entities and relationships of the source data
  • Translate the model into a model that fits your data warehouse structure
  • Ensure all entities are mapped to existing entities