'Can you just store this data file in our data warehouse?' - how often does a data analyst or data engineer hear this question casually tossed their way? As if storing data in a data warehouse is the same as saving a file in the right folder on your computer. Structuring data in a data warehouse deserves attention. What are the best methods for storage? A hint: everything depends on the right structure. By Harmen, CTO & senior data scientist at Datalab

A data warehouse is a central repository for (potentially) all your organisation's data. Whether it concerns accounting in Exact Online, ERP data from Microsoft Dynamics or custom-built systems, unlocking data is a crucial first step to extracting valuable insights. There are many different types of data warehouses and techniques for getting data from sources into your data warehouse (ETL: Extract/Transform/Load) - see also Koen's blog.

An essential step that is often skipped - or taken too hastily - is thinking about how you store your data in the data warehouse. In other words: what structure do you give the data in your data warehouse?

'Explaining why data needs to be unlocked in a data warehouse is manageable, but due to time pressure - "my manager wants quick results" - sources are often unlocked ad hoc. Without properly considering what additional value you could extract from that data.'

Structuring data is of great importance

Structuring data is so essential that large organisations often create a dedicated role for it: the data architect. In this role, you are responsible for designing and safeguarding the structure of the data warehouse alongside any other storage locations, such as a data lake. Smaller organisations often employ only one or a few analysts who, besides designing dashboards and performing analyses, are also responsible for unlocking data, possibly in a data warehouse. The architect role often falls by the wayside as a result. Explaining why data should be unlocked in a data warehouse is still achievable, but the desire for quickly visible results means sources are often unlocked ad hoc, without taking the time to properly consider what additional value could be extracted from the data. A missed opportunity!

Kimball's star schemas and snowflakes, Inmon's normalisation, or Linstedt's Data Vaults?

How and what you store in your data warehouse depends on:

  • What you want to be able to do with it (specific analyses or flexibly deployable);
  • Whether there is data retention or mainly a copy of production systems;
  • And your own preferences regarding standards.

We will now cover the three most common standards. Each standard has its own pros and cons, and this blog is a good starting point but certainly not a final treatment of which standard is best.

Inmon's data normalisation

If anyone can be identified as the 'godfather' of data warehousing, it is Bill Inmon. In the late 1980s, he coined the term data warehousing and formulated a set of standards. These standards were dominant for years and are now, over 35 years after introduction, still relevant. At its core, a data warehouse according to Inmon is a place where data about a specific subject (department of a company such as sales and/or production) comes together:

  • In an integrated manner (multiple sources are linkable);
  • Where data is stored in normalised form (more on this below);
  • Is immutable (in SQL terms: you are mainly running INSERT queries to add data and generally stay away from UPDATE queries);
  • …so that you can see history over time.

Data normalisation is the core concept here. In essence, normalisation means you never store data twice. Look at the diagram above of a (fictitious and oversimplified) webshop. On the left side, you see an example of a non-normalised data model. Many analysts enjoy working with this type of data because it is easy to use in a dashboard tool or for a regression or machine learning analysis. You have just one table and this table contains everything you need. But… it is only suitable for that one analysis. The level of observation is the customer. For an analysis about customers (average order amount, location, order date), the data is very suitable. For an analysis about products, it becomes harder: the data is present (in the columns 'product_1', 'product_2' etc.), but an analysis of which products sell best is quite difficult. After all: any product can appear in any product column. Not convenient. It is much better to store data separately: products, customers and orders each get their own table. From orders, we draw lines to products and customers: an order is not much more than linking a customer to one or more products.

The example in the figure is an extreme simplification. In reality, there are often many more tables containing relevant information. This is also precisely why some analysts, despite the clear advantages of normalisation, still choose to store data non-normalised. It is quite a significant task to properly unlock data when it is spread across many different tables.

The key properties of the (amongst others) Inmon normalisation model:

  • Data is stored only once. Example: the customer's name is not stored with every order. Instead, you create a customers table and link from your orders to the customer.
  • All tables reference each other if there is any relationship between the tables.

Kimball's star and snowflake schemas

If a fully normalised model is too complex, analysts sometimes opt for the golden middle ground in the form of Ralph Kimball's star or snowflake schemas. This method of storing data is a limited form of Bill Inmon's normalisation. Instead of linking everything to everything, you store one central fact table. Around that fact table, you build dimension tables. In the figure above in the Inmon section, orders is the central fact table and products and customers are the dimension tables. The crucial difference from Inmon's model is that you do not also link other fact tables to the orders table. Information about, for example, marketing (how did the customer actually find the webshop?) is not linked to orders - at least not in the same dataset. You create a separate schema of tables for each fact ('order', 'found via', etc.).

A snowflake schema is nothing more than a further elaborated star schema. In the customers table in the figure above, you see postcode and city. In the Netherlands, postcodes, addresses and cities are inherently related. You could therefore further elaborate the model by creating a postcode-house number dimension table, which you link to from customers.

Linstedt's Data Vaults

Both Inmon and Kimball prescribe what a good method of data storage is. That is fine and useful because it makes collaboration between analysts easy, but it also takes a lot of time. For this reason, Dan Linstedt devised a data storage structure that is particularly suitable when you want to store raw data in a smart way, without already choosing one particular method of data storage. In that sense, it somewhat resembles a structured data lake or a data lakehouse.

Core concepts are hubs, links and satellites. Hubs are your data sources (for example, customers in your ERP and webshop system), links are the keys with which you (for example) link customers in one system to those in another system. In satellites, you store the characteristics of customers (name, address and whatever else you want to store about your customer).

Which data model should I choose?

As simple guidelines, I recommend the following:

  • Are you certain you want to use data exclusively for one analysis? Then store the data non-normalised;
  • Do you have some time and space to thoroughly unlock data? Then choose Inmon's normalisation model (also see the tip about views below);
  • Do you want a trade-off between time investment and flexibility? Then consider Kimball's star schema;
  • Do you have data, want to store it but do not yet know what you will ultimately do with it, and do not have time to store it normalised? Then consider the Data Vault model.

By far the most popular is Inmon's model, with Kimball's star schema as a good second. Data Vaults are rarely used, as new developments (data lakes) have overtaken this concept.

The 5 tips for unlocking data

'Normalised data + views = ultimate flexibility.'

  1. Look carefully at the source of your data. Do you have data from a relational database that you want to unlock? Then try to stay as close to that structure as possible. After all, the application designer has already thought carefully about it. Focus instead on creating good views on your data.
  2. Do you have data from an API? Then you often only have one particular view of the data. Some APIs neatly follow the underlying relational databases, while other APIs are strongly focused on unlocking data for specific purposes (for example, for building an app rather than a copy of all data). Create an overview of everything you can do with the data from your source. Focus on the 'quick wins', the low-hanging fruit with which you can get your colleagues excited. A dashboard that does provide that handy overview that the application itself cannot. Or an analysis that directly leads to cost savings or better-served customers. However, do not forget the medium term: what other projects do you want to undertake? And long term: what use do you see for the source in the longer term? The future is hard to predict. Therefore, focus on a considered strategy for at least the short and medium term.
  3. If you cannot unlock the data from your source 1:1 into your data warehouse (for example because it takes too much time to write all the ETL scripts), then at least ensure you store the raw data from your source (for example the JSON data from an API or raw database dumps) in a data lake. Storage there is fairly cheap, especially if you properly investigate how to store data as archive rather than on hot storage. This way, you can always go back into the data. What you do not store now may well be unavailable in a future version of the source application - or worse, deleted.
  4. Choose a normalised data structure. Datalab prefers to work according to the Inmon standard, because this gives the greatest flexibility. It takes slightly more time than the Data Vault standard, but for the (medium to) long term it is almost always a worthwhile investment. Moreover, you learn all aspects of your data source well and can quickly identify other 'low-hanging fruit'.
  5. The greatest advantage of a Kimball or Data Vault architecture is that you store data in a way that closely resembles how the analyst wants to work with it. In a normalised model of webshop data, you have many different tables (products, orders, shipping costs, customers, etc.). An analyst wants precisely one or a few tables with all information conveniently linked. It is very easy to create such derived datasets based on normalised source data. At Datalab, we always create views on the data for clients. These are essentially queries on the data that you store as if they were a table. So you write a query to unlock: customers linked to orders and the associated products, all in one overview. By saving this query as a view, an analyst (you or a colleague who may be less familiar with the source) can still easily work with the underlying data. In other words: Normalised data + views = ultimate flexibility.