Data Vault Conventions: Surrogate Identifiers (Modern Data Warehousing, Part 11)

This article is the eleventh part in an ongoing series on modern data warehousing using data vault.

The first part on data warehouse layers can be found here, the second part on kinds of time can be found here, the third part on number of timelines can be found here, the fourth part on hubs can be found here, the fifth part on links and satellites can be found here, the sixth part on satellite variations can be found here, the seventh part on other optional constructs can be found here, the eighth part on raw vault and business vault can be found here, the ninth part on conventions for construct usage can be found here and then tenth part on naming conventions can be found here.

Data Vault Conventions

To fully leverage the pattern-based nature of data vault, you have to pick and document some structural conventions before starting implementation. With documented conventions like that, it is easy to bring new team members up to speed and ensure consistency of implementation even in parts of the data vault that will be built when its original architects won’t be around anymore.

There are multiple kinds of structural conventions. Previous articles dealt with rules about construct usage and construct naming conventions. In this article, we look at different options for surrogate identifiers. And in a future article, we will discuss technical columns.

Types of Identifiers

Identifiers are what holds your data vault model together. Thanks to identifiers, you can designate instances of concepts uniquely across the whole data warehouse. Thanks to identifiers, you can join hubs, links and satellites together.

Sequence Keys

In data vault modeling, different types of identifiers have been used over time. In the early years, sequential numerical identifiers (or sequence keys for short) were the most common option. They had been used as surrogate identifiers in operational systems and data warehouses alike for a long time and therefore seemed like an obvious choice.

Numerical identifiers don’t use up a lot of storage and usually allow for fast joins, making it easier to get data out of the data vault. There is no obvious relationship between the number and the instance it identifies, making it easier to keep at least some data in your vault if you have to comply with the GDPR’s right to be forgotten (where GDPR designates the EU General Data Protection Regulation of 2016). And if your database management systems enforces foreign keys, numerical identifiers serve as automatic data integrity checks as well.

However, sequential keys might not be supported by the cloud data platform of your choice. And even if they are, they have one significant disadvantage: One man’s automatic data integrity checks are another man’s loading dependencies.

Before you load data about an instance of a concept into a satellite, you must make sure that there is already a sequence key for it in the hub (and query the hub to learn what the key is). Before you load data about a connection between concepts into a link, you must make sure that there are already sequence keys for all instances in the hubs involved. And if you have link satellites, you have to make sure that the necessary sequence keys exist in the parent link and in all the hubs involved in the parent link.

Hash Keys

For this reason, hashed human-readable identifiers (or hash keys for short) have become the most popular identifier type for data vault models in recent years.

As the name suggests, they are generated at load time by applying a hashing algorithm like SHA-1 to human-readable identifiers. Since the result of this computation is always the same for the same input, hubs, links and satellites can be loaded in parallel without having to look up identifiers in other tables. Hash keys have a predictable, relatively short length and can be stored efficiently in a single binary column, leading to joins that are almost as fast as the ones using sequence keys.

However, hash keys have some disadvantages of their own. Computing hash keys takes computing resources and time as well. There is a small but non-zero risk of hash collisions (two different human-readable identifiers resulting in the same hash key). There is an obvious relationship between the hash and the instance it identifies, making it harder to keep at at least some data in your vault if you have to comply with the GDPR’s right to be forgotten. And loading everything in parallel without any lookups or foreign key constraints, while potentially faster, might make your life harder when trying to deliver consistent data to your consumers.

Natural Keys

Given this mixed picture for hash keys, some people have decided to just use the human-readable identifiers (or natural keys for short) directly and not have any kind of surrogate identifier in their data vaults at all.

Natural keys don’t need any additional computing resources, they can’t collide with each other and also allow for parallel loading. They have their issues with the GDPR’s right to be forgotten but so do hash keys. In many cases, natural keys are even shorter than the hash keys computed from them (and therefore potentially better for join performance). Therefore, a data vault based on natural keys is an option that you should at least take into account.

What’s Your Type?

As you can see, all possible types of identifiers have their pros and cons.

You should weigh them carefully because once you have picked one, it’s hard to switch to another one later. If you have both personal and non-personal data, a hybrid approach might make sense (sequence keys for personal data, hash or natural keys for non-personal data).

If you use hash keys in some or all cases, you should consider implementing a hash collision detection mechanism to make sure you are not using the same surrogate identifier for two different human-readable identifiers.  

Future articles will discuss technical columns, the most important data vault modeling patterns and different presentation modes for the presentation layer. Stay tuned!