Many companies today are heavily dependent on data for optimising various (internal) processes. A well-organised and consistent data warehouse is an absolute necessity for this. Usually, a data warehouse comprises different datasets originating from very diverse sources. This involves not only datasets from internal services and sources such as your own customer directory, but also datasets from third parties - and therefore different types of sources.

To make the most of your data, companies often face the challenge of integrating data from a source so that it fits the structure in their domain and aligns with their organisation's business rules.

Part of this challenge was already covered in the blog post about translating data sources so they fit into the main structure of our data warehouse. Besides the task of translating the data source, we also regularly deal with potential inconsistency in places where our business logic and model do not allow it.

Let us illustrate with an example:

The practice

Suppose you are a retailer using a third-party service to manage your sales process. Often, with such a third-party service, you can retrieve data about your customers, sales processes and more using an API. You load the retrieved data into your data warehouse so that you can analyse it further.

Let us assume for now that you import a dataset about your customers with contact details such as their home address. Additionally, you import a dataset about sales orders.

In your own business model, you probably do not have sales orders that are not linked to a known customer, because otherwise you cannot actually fulfil and invoice the order. But what should you do if the sales order dataset you retrieve from the API contains an order that you cannot link to a customer from the customer dataset from the same API?

This can happen for various reasons. In your data warehouse, you would ideally want to avoid such cases, as they could potentially skew your analysis.

Approach to making potentially inconsistent data consistent

It is clear that we cannot apply magic and restore missing data if we do not know what we are missing. But we can ensure that we only store data that is actually consistent and meets our business rules. And we can ensure that we only use consistent data in our analyses.

Returning to the example: we have a relationship between customer and sales order, and although the data model at the source may not meet our business rules, we want our data warehouse to do so. In this case, it means we only want to store sales orders that we can link to a customer in our customer dataset.

To guarantee this, the logical order of steps could look as follows:

  1. Import the customer dataset into a production schema in the data warehouse;
  2. Retrieve the sales order dataset (it is important that we do this after retrieving the dataset on which the sales orders depend - in this case, the customer dataset);
  3. Store the sales order dataset in a staging schema in the data warehouse;
  4. For each sales order in the staging schema, only insert the record into a production schema if you find a corresponding customer in your customer dataset in your data warehouse;
  5. Any sales order that cannot be successfully linked to an existing customer in the customer dataset remains in the staging schema.

Advantages

The above approach may seem complicated at first glance, but it has several advantages:

1. Coherence

Data stored in the production schema is guaranteed to be consistent, and you can rely on every record in your dataset being correctly linked if there are related records in other datasets.

2. Completeness

Although your datasets may not be complete in your production schema, you can still always perform analyses on the full dataset by including data from the staging schema. Moreover, the distinction between two schemas makes any inconsistencies in the source data visible and quantifiable, potentially allowing you to resolve inconsistencies at the source - or at least better understand their nature and cause.

3. Self-healing process

Sometimes inconsistencies may only be temporary. For example: if data updates or changes occur between retrieving data for different datasets, the inconsistency may be resolved the next time you query the datasets.

In our example above, it may be that a new customer was created between retrieving the customer dataset and the sales order dataset, and that this customer also placed an order. When the sales order dataset is retrieved and stored, there is then an order from a customer who did not yet exist at the moment you retrieved and stored the customer dataset. In that case, with the approach described, you store the sales order in the staging schema but do not insert it into your production schema, because the linked customer does not exist.

The next time you import data from that source again, the customer will exist and be inserted into your production schema. When you then retrieve the sales orders, you can insert the sales order for the new customer into your production schema.

Summary

By following the approach described, you get the best of both worlds: on the one hand, you always have a complete dataset by combining data from the production and staging schemas, while on the other hand you can trust that you only have consistent data in your system.