The Great Big Data Vault Lie

I have recently been running half-day “Introduction to Hook” sessions for a client. When explaining that the Hook approach emerged from difficulties I had experienced with Data Vault, I was asked a question about how to improve the performance of Data Vault queries, given “all those tables”. I was able to restrain myself from answering “well, don’t use Data Vault”, but the question got me thinking about performance claims evangelised by Data Vault enthusiasts, and I realised “the big lie”.

We are told that loading data into a DV2 structure is “frictionless” and that we can load hub, link and satellite tables in parallel. The explanation goes something like this. When loading a source table into the raw vault, we will target one hub table and a satellite table, or multiple hub tables, a link, and a satellite table. Can we load these tables in parallel? Yes, we can. We can simultaneously load data into hub, link, and satellite tables without any contention, as there are no enforced referential integrity constraints between the tables. We say that any relationships are “eventually consistent”, so the order in which we load them is immaterial; we can choose to load them simultaneously if we wish.

It appears that we can load data in parallel with zero contention, but that isn’t entirely true. These sneaky evangelists have performed a sleight-of-hand, tricking you into thinking about the problem in a certain way, so allow me to offer an alternative perspective.

Rather than thinking about loading a single source table, think about loading 100 source tables. Can we load all of them in parallel? Let’s keep this simple and assume that all 100 tables contain a business key for a single hub (let’s say Customer). Therefore, we can load each of these 100 source tables to a separate hub satellite attached to the Customer hub. Think of the process of loading a single table:

1. Get a distinct list of business keys from the source table.
2. Check which of those business keys do not already exist in the hub table.
3. Insert new business keys into the hub.
4. Insert source data into the satellite table.

The question is, can we load new business keys from different source tables into the Customer hub at the same time? No, we can’t. We must load the source tables sequentially, as we can only determine if there are new business keys one source table at a time, because the results in step 2 of the process require exclusive access to the current list of business keys.

Imagine the complexity if we need to hit multiple hubs and link tables. We now need to devise creative solutions to prevent contention if we want to achieve any degree of parallelism and avoid deadlock or orchestration issues.

Of course, Hook does not suffer these constraints. Each target frame can be loaded entirely independently of any other frame. No contention, no risk of deadlocks, or the need for uniqueness checks. Just a straight INSERT INTO. Loading data to a Hook warehouse is as frictionless as it gets. You are more likely to need to throttle the throughput to avoid overwhelming your DBMS with multiple concurrent insert queries, but that is an engineering decision, not a modelling concern.

If you would like to learn more about Hook, please don’t hesitate to reach out. I am available to run half-day introduction sessions at your convenience.