Technical Keys vs. Business Keys in Data Vault
Source systems keep the business key in the master record, but every relationship runs on technical keys. Four ways to deal with that, what each one costs, and the pattern we use: a PSA hub for the technical keys, mapped to the business key hub.
The data warehouse automation solution trusted by data teams across industries
Does this sound familiar?
- The order table only carries a system-internal customer ID, and the customer number lives in another table.
- Every source got its own customer hub, and the data never came together.
- Staging joins look up business keys in the source, and nobody can prove anymore what the source actually delivered.
Data Vault builds on business keys: they are stable, shared between systems, and survive migrations. The trouble starts when you look at how source systems actually store relationships.
The problem: relationships run on technical keys
Most source systems keep the business key in the entity it defines. The customer table holds the customer number. The relationships, however, use the system’s own technical keys:
| Table | Columns |
|---|---|
Customer |
customer_id = 10492, customer_number = CUST-9921, name, address |
Order |
order_id = 884102, order_number = SO-1018, customer_id = 10492 |
The order knows its customer only as 10492. To connect it to the customer hub, which is keyed on
CUST-9921, something has to translate one into the other.
In classical modeling terms: the source uses surrogate keys for its foreign keys, while Data Vault wants the natural key. The question is where the translation happens.
Four ways to solve it
| Approach | How it works | The catch |
|---|---|---|
| 1. A source vault per system | Separate hubs for CRM customers and invoicing customers, keyed on technical IDs | Technically feasible, but the data never gets integrated |
| 2. Source APIs with business keys | Ask each source to deliver every relationship with business keys | Very nice when it happens, which is seldom |
| 3a. Your own lookups while staging | Replace technical keys with business keys by querying the source while staging | It works and is under your control, but the data is modified before it is written, so it is no longer auditable, and the lookups put load on production systems where it does not belong |
| 3b. Lookups after staging | Replace the technical keys by joining after staging, either against staging tables or against the vault | Joining the staging tables prevents delta loading, because the join needs the full tables. Joining against the vault works, but the data is still modified before it is loaded |
| 4. PSA hubs | Load technical keys into their own hub and map them to the business key hub | One more generated join when querying. A Business Vault load can make it unnecessary. |
The pattern we use: PSA hubs
A PSA hub is a hub for the technical keys of the source systems. PSA stands for persistent staging area, but not the one people refer to in data lakes, a flat copy of the source tables: this one is already in vault format, with hubs and links, and satellites where they are needed. In the example below, the PSA holds no attributes at all, only the technical relationships: the descriptive data sits on the business key hub.
Each concept gets two hubs, never one per source system:
- The PSA hub holds the technical keys of all systems, each with its
Business Key Prefix:
CRM|10492,ERP|80012. - The Raw Vault hub holds the business keys without a prefix:
CUST-9921.
Loading then needs no translation at all:
- Transactions load as delivered. The order
884102relates to customerCRM|10492through a link between the order PSA hub and the customer PSA hub, using the technical keys exactly as the source holds them. - Master records do the mapping. The customer record carries both
10492andCUST-9921, so it relates the PSA key to the business key, many to one. Several systems’ technical keys can point to the same customer. - Satellites go on the business key hub. The customer’s name and address describe
CUST-9921, whichever system delivered them.
The same pattern repeats for every concept: orders, products, contracts.
Querying: one path, and a shortcut
To output orders with their integrated customer, the query walks from the order to the order PSA hub, along the technical relationship to the customer PSA hub, and from there to the customer:
Order → Order PSA → Customer PSA → Customer
That is the raw, fully traceable path. Where queries need to be faster, you configure a Business Vault load that creates an exploration link: it resolves the path once and stores the direct relationship between order and customer. Reports read the short path; the long one stays as the audit trail.
What you gain
- Nothing is modified before it is written. Technical keys are loaded as the source delivered them, so every row stays auditable.
- No lookups against production systems. The translation happens inside the warehouse.
- Fast writes. Loading needs no lookups at all, so every source loads as fast as it delivers, and the exploration link is created asynchronously afterwards.
- Integrated output. All customers come out once, keyed on the business key, with every system’s transactions attached.
- Migrations stay contained. When a source system is replaced, its new technical keys map to the same business keys, and the history still lines up.
What changes with Datavault Builder
Datavault Builder supports the PSA, the Raw Vault, and the Business Vault, and all three form one integrated model. Each layer can be developed in agile steps, as it is needed: start with the PSA hubs, add the business key mapping, configure an exploration link where a query asks for it.
The Business Key Prefix that keeps CRM|10492 and ERP|10492 apart is part of the model, and the
hubs, links and loads of the pattern are generated like any others. The modeling decision stays
with you: which concept, which business key, and where an exploration link is worth it.
See It Running on One of Your Sources
Book a free demo and bring the connector that costs you the most, in money or in time.
Three Steps from Technical to Business Keys
-
Load the Raw Vault as usual
Business key hubs and their satellites, loaded from the master records exactly as the literature describes.
-
Add PSA hubs for technical keys
PSA hubs hold the technical keys with their Business Key Prefix, and the relationships run between them on those keys.
-
Shorten the path if it is worth it
Where a query really needs speed, an exploration link stores the direct relationship once.
How Datavault Builder Takes the Friction Out of Ingestion
-
Ingestion is built in
Batch, delta and CDC loads from databases, files, REST APIs, NoSQL and Python sources, with streams such as Kafka arriving as micro-batches. Same platform that generates the warehouse, no second invoice.
-
Your schema, not the vendor's
Source tables are mapped to a Data Vault 2.0 model you designed. A new column or a renamed table changes a mapping, not a chain of post-load scripts.
-
Only deltas move
Hubs, links and satellites load what changed. Full reloads stay in staging instead of being reprocessed downstream every night.
-
History is kept by design
Every change is retained as it arrives, so as-was reporting works even where the source overwrites its own rows.
-
Code you never hand-write
Loading, historization and lineage are generated from the model in real time and run natively on Snowflake, Databricks, BigQuery, SQL Server, Fabric, Oracle or PostgreSQL.
-
One platform, up to nine tools fewer
Modeling, ETL, CI/CD, documentation and lineage in one place. That is what makes 14.7 minutes from requirement to production possible.
Recognized by BARC in The Data Fabric Survey 26
Meet Our Expert
Twenty minutes with our Sales Director, and an honest answer on whether this fits your stack.
Matt Collett
Sales Director
Great, pick a time that works for you:
Other Problems This Series Covers
-
What Is a Business Key in Data Vault?
A business key is the identifier your employees and customers actually use: the customer number, the invoice number, the contract ID. It is stable, it is shared across systems, and it is what every hub in a Data Vault is built on.
-
What Is a Satellite in Data Vault?
A satellite holds everything that describes a hub: names, statuses, amounts, and every change to them, appended and never updated. It is a slowly changing dimension type 2 without the UPDATE, with a full audit trail built in.
-
What Is a Link in Data Vault?
A link records that business keys belong together: this order belongs to this customer. Datavault Builder covers the classic Data Vault link, and adds a transaction link anchored on a grain hub, which fixes the grain of a transaction and lets transactions relate to each other.
-
What Is a Hub in Data Vault?
A hub represents one core business concept, such as customer, product or account, as the list of its keys: every one the warehouse has ever seen, each exactly once. It stores identity, not state, and that restraint makes it the point where source system silos collapse.
Questions and Answers
- Dan Linstedt. He developed the method in the 1990s and published it around 2000. Data Vault 2.0, which adds hash keys, a methodology and an architecture around the model, followed in 2013.
- The standard reference is Building a Scalable Data Warehouse with Data Vault 2.0 by Dan Linstedt and Michael Olschimke (Morgan Kaufmann, 2015).
- No. Bill Inmon sees Data Vault as an evolution of his third normal form view of the enterprise data warehouse, not as a competing approach.
- No. A dimensional output is often part of a Data Vault implementation: the vault keeps the integrated history, and star schemas are built on top of it for reporting. The same vault can also deliver flat tables or a Unified Star Schema.
- With automation, a third normal form view on top of a Data Vault can be generated completely deterministically. The vault holds the data once, and the 3NF layer is derived from the model. How that works.
- Trying it without automation. Data Vault is built on a small set of strict, repeating patterns, which is exactly what makes it tedious and error prone to write by hand and straightforward to generate. That is why you should use Datavault Builder.
- Yes. Splitting keys, relationships and history into hubs, links and satellites means more tables than a normalized or dimensional model. That is why the physical layer should be abstracted by a model-driven approach like Datavault Builder, where you work on the business model and the tables are generated.
- Book a demo and see a Data Vault built on your own sources, or order a training environment and try it yourself.
- Here. Datavault Builder is a Data Vault automation solution: see pricing or book a demo.
- Datavault Builder is licensed annually, as a subscription to use the software. Perpetual licenses are available on request. The editions and what they include are on the pricing page.