Data Vault Modeling

## What Data Vault is trying to solve Data Vault is a modelling pattern for the **integration layer** of an enterprise warehouse: the place where data from many source systems lands, is integrated on business keys, and is kept forever for audit. It is not a consumption layer — analysts do not query it directly. Its design goal is that adding a new source system, a new relationship or a new attribute should be an **additive** change, never a refactor of existing tables. To get that, it deliberately separates three things that both a normalized OLTP schema and a dimensional model bundle into one table: the **identity** of a business entity, the **relationships** between entities, and the **descriptive attributes** that change over time. Each gets its own table type. ## Hubs — identity A hub is the distinct list of business keys for one business concept. A `hub_customer` contains one row per distinct customer business key, and nothing else of substance: ```sql CREATE TABLE hub_customer ( customer_hk CHAR(32) NOT NULL PRIMARY KEY, customer_id VARCHAR(50) NOT NULL, -- the business key load_date TIMESTAMP NOT NULL, -- first time this key was seen record_source VARCHAR(100) NOT NULL ); ``` The hub carries a warehouse key (`customer_hk`), the business key itself, the load date at which the key first appeared, and the source that first supplied it. It has **no descriptive attributes and no effective dates**. A hub is insert-only and grows only when a genuinely new business key arrives. This is where integration happens. Customer `C-42` in the CRM and customer `C-42` in the billing system land on the *same* hub row, because they share the business key. That single fact — one hub row per business key across all sources — is what makes the vault an integration layer rather than a pile of source copies. ## Links — relationships A link table holds the association between business keys: one row per distinct combination of parent hub hash keys, plus its own hash key and load metadata. A `link_order_customer` says "this order belongs to this customer" and nothing more. Links are **always many-to-many by construction**, even when the source system today enforces one-to-many. That is intentional: cardinality is a business rule, and business rules change. Modelling the association as a link means that when one order later belongs to two accounts, you insert rows rather than restructure tables. Links can also model transactions and hierarchies. A transaction is a link between the hubs it touches, with the amount and status pushed onto a satellite of that link. A hierarchy — customer reports to customer — is a same-as or hierarchical link between one hub and itself. Links carry no descriptive data at all. ## Satellites — description and history A satellite hangs off **exactly one** parent, either a hub or a link, and carries the descriptive attributes. Its primary key is the parent hash key plus the load date: ```sql CREATE TABLE sat_customer_crm ( customer_hk CHAR(32) NOT NULL, load_date TIMESTAMP NOT NULL, hashdiff CHAR(32) NOT NULL, record_source VARCHAR(100) NOT NULL, name VARCHAR(200), tier VARCHAR(20), PRIMARY KEY (customer_hk, load_date) ); ``` Satellites are insert-only: when an attribute changes, a new row is inserted with a later load date and the old row is left untouched, so the full history of what each source said and when is preserved. One parent can have **many** satellites, and splitting them is a normal design act. The usual splits are by source system (one satellite per source, so sources never overwrite each other), by rate of change (volatile attributes separated from stable ones so a fast-changing column does not multiply rows carrying stable ones), and by classification (personal data in its own satellite so it can be masked or deleted independently). The reverse never holds: a satellite with two parents is a modelling error, because its history semantics and its load pattern both assume a single parent key. ## Why the split pays off Each kind of change becomes additive. A new source system contributes a new satellite and, at most, new hub rows. A newly discovered relationship is a new link table. A new attribute is a column on a new satellite or an existing one. None of these force you to rewrite a fact table, restate a dimension, or backfill existing rows — which is precisely the pain of evolving a published star schema under schema churn from a dozen upstream systems. ## What it costs The cost is table count and joins. A model that would be five dimensional tables becomes twenty-plus hubs, links and satellites, and answering even a simple business question means joining a hub to several satellites and picking the right version of each. That is why a Data Vault warehouse is a **layered** architecture: the vault integrates and retains, and dimensional marts are built on top for consumption. Nobody hands a BI tool a raw vault.