The advantages and disadvantages of the Data Vault methodology

Compliance and a Data Vault go hand in hand

As indicated, the Data Vault (DV) distinguishes between facts (raw perspective) and the truth (edited perspective). This can be useful to avoid losing any transactions or mutations. And it is sometimes a must from a compliance and legislative perspective. On all fields, you consistently keep history in the Data Vault. An ingenious construction of hub, link, and satellite tables provides flexibility in terms of storage.

Find the 4 differences between the third normal form and the Data Vault

Data Vault modeling is vastly different from regular data modeling (3NF). Below you can see the differences. From four tables (Figure 1) you go to nine tables (Figure 2). So a big difference is: there’s more tables. What other differences do you see? Leave a comment with your answer.

Figure 1: a company’s orders modeled according to the third normal form (3NF)

Figure 2: example of a company’s orders modeled using the Data Vault methodology

How do you model the transformation from 3NF to DV?

• You split each master table (reference table such as customer) into a hub table and a satellite table. The keys come in the hub table. The descriptive fields come in one or more satellite tables.
• This creates flexibility: you can easily attach multiple satellite tables to the hub. Hence the name “hub”. This allows you to easily link customer data from other systems or external sources. This keeps the existing tables intact.
• Joins between two master tables are created with a link table. This creates flexibility in the type of relationship that data has with each other.
• You split each transaction table into a link table and a satellite table. The keys come in the link table. The transaction details come in the satellite table.

Example: suppose orders can suddenly be placed not only by companies but also by consumers. Then a few additional tables will suffice: Person Order Link (Person RK, Order RK), Person Hub (Person RK, ID (BK)), and Person Satellite (Person RK, first name, last name, etc.). The current Data Vault data model remains intact in this process. That is, there is no need to modify existing tables.

How do you design a Data Vault architecture?

A Data Vault can be applied as a Central Data Warehouse (CDW), but also as a pre-CDW or Persistent Staging Area (PSA). If you apply the Data Vault as a CDW, then it is not a mature DWH architecture. After all, you build data marts from the Data Vault and these have a dimensional structure.

Figure 3: The Data Vault route and the traditional Business Analytics route. This figure shows that implementing a Data Vault involves extra work and makes the architecture more complex.

For the detailed data, you then have to go back to the Data Vault. That one has a different structure and has no transformations or different transformations. Moreover, it may also contain incorrect data. So a drilldown on a Data Vault is also dangerous. Therefore, a Data Vault can never replace the CDW in a mature DWH architecture. You want one version of the truth and then it cannot be enforced. Or you can only do that at a very high cost. Because, the data warehouse bus, which enforces that, is no longer there.

One specific indicator sometimes does require multiple aggregation tables. Each data mart can, in theory, thereby create an entirely separate truth. Thus, a Data Vault can easily degenerate into a collection of separate data silos, just like in the pre-data warehouse era.

A mature DWH architecture

In a mature DWH architecture, a number of functions are performed:

• you test the data quality
• you build up a history
• you combine data
• you enforce consistency between data

For the latter, you think about consistency between aggregations and the details but also across aggregation tables.

In an Enterprise DWH architecture, you can basically choose 2 routes: the traditional route and the Data Vault route.

The disadvantages of the Data Vault

1. More work: the Data Vault is more complex to create. In addition, you now have an extra component that you also have to maintain.
2. More transformations: in the Data Vault, you normalize entities beyond the source. Afterwards, you have to “work back” to a dimensional data model.
3. More knowledge required: with the Data Vault comes a 3rd modeling technique. Employees must master these techniques bit by bit.
4. No integrity: the data in the Data Vault lacks integrity and is not always correct.

What happens after the Data Vault in the loading process?

The main advantage of the Data Vault is the flexibility of data storage. In a mature DWH architecture, you need a CDW and possibly one or more data marts or cubes in addition to a Data Vault. Of course, it is wonderful when the structure changes in the source do not necessarily require the Data Vault to be “cracked open”. But this does not apply to the CDW and data marts, as these are dimensionally modeled (preferred) or 3NF. To illustrate this phenomenon, we give two examples.

By the way, you can also model the change regarding sales managers and consumers in a slightly different way but that doesn’t matter for the examples.

Source changes always lead to modification of data marts

Both examples show that even with a Data Vault it is necessary to adjust the data model of the CDW/data marts when the structure of the sources changes. What does this mean for the existing data in the CDW/data marts? The CDW can then either be completely rebuilt from the Data Vault (ETL/script modifications are necessary). Or a conversion ETL needs to be written that converts existing data from the CDW to the new structure and content.

Is the story of reloadability even true?

So the advantage of reloadability does not seem to be there. After all, in many, but not all, cases a conversion can suffice. In addition, a conversion can mark fact records from before the conversion. So that you can also determine (in retrospect) when the change took effect.

Situations in which a Data Vault has added value

1. From the point of view of compliance and traceability, a Data Vault can offer added value. After all, there is an accurate record of when a data element enters the DWH environment from which source system.
2. When the structure of source systems changes frequently to very frequently, or the organization operates in a very dynamic environment, a Data Vault can offer added value.
3. When a lot of accountability information needs to be generated with fixed reports. A so-called Business Data Vault (which has the role of CDW) can then suffice, without data marts, in which only correct data is loaded. The BI tool retrieves these directly from the Business Data Vault.