Kimball vs Data Vault: how to choose (and why the answer is usually both)
This is the comparison people search for most and the one most often framed wrongly, because the two are not competing for the same job.
Count your source systems, and ask whether you must be able to prove what arrived.
One or two systems, reporting questions, a team that needs results this quarter: Kimball, and you will be finished far sooner. Several systems that disagree about the same customer, schemas that keep changing, or an auditor: Data Vault as the integration layer — and a star schema on top of it anyway, because nobody reports off a raw vault.
They are answers to different questions
Kimball answers: how should this data be shaped so that people can ask business questions of it? Facts and dimensions, a declared grain, one join per dimension, and every BI tool already knows the shape.
Data Vault answers: how do we take in data from many changing systems, keep all of it, and be able to say later exactly what we were told and when? Hubs, links and satellites, insert-only, and a shape no analyst should query directly.
Put that way, the framing “Kimball or Data Vault” is mostly a category error. One is a presentation model; the other is an integration and history model. The real decision is whether you need the second one at all — because you always need the first.
The five questions that decide it
1. How many source systems, and do they disagree?
One system: nothing to integrate; a star schema straight from staging. Several systems that each hold part of the same customer under different keys: something has to reconcile them before a dimension can exist, and that job is precisely what a vault (or an Inmon core) does. Kimball does not do it for you.
2. Must you be able to prove what arrived?
If “what did we know about this account on the 3rd of March, and when did we learn it” is a question a regulator or an auditor will ask, the raw vault’s insert-only satellites answer it by construction. A star schema can approximate it with type 2 dimensions and never quite gets there, because facts and non-tracked attributes are overwritten.
3. How often does the source schema change?
Rarely: any model works. Constantly: a vault absorbs a new column or a new source by adding tables, and nothing loaded has to migrate. A star absorbs it by being altered, and a changed grain means a rebuild. This is the property people buy Data Vault for.
4. How much history, and of what?
History of a few grouping attributes — region, segment — is a type 2 dimension, and Kimball handles it fine. History of everything, including things nobody reports on yet, is the vault: it keeps all of it whether or not anybody asked.
5. How big is the team, and how soon do they need something?
A vault is three tables where a star has one, plus a mart layer that is not optional. A two-person team with a quarter to deliver should not start one. A team of ten with a two-year mandate across nine systems probably should.
Side by side
| Kimball (star schema) | Data Vault 2.0 | |
|---|---|---|
| Primary job | Presentation and reporting | Integration and auditable history |
| Queried by analysts | Yes — designed for it | No — through marts only |
| Tables per entity | 1 (a dimension) | 3+ (hub, satellites, links) |
| History | Per dimension, decided up front (SCD) | Everything, automatically, insert-only |
| A new source system | Conform it into existing dimensions | Add hubs/satellites; touch nothing existing |
| Schema change | Alter the star; rebuild if the grain moves | Add a satellite |
| Time to first report | Weeks | Months (vault + marts) |
| Build cost | Moderate | High |
| Load complexity | Lookups and SCD logic | Hash keys; loads are order-free and parallel |
| Silent failure to fear | Wrong grain | Two hash expressions that differ by a trim |
| BI tool support | Native | None — needs the star on top |
The architecture most real warehouses end up with
Sources -> Staging -> Raw Vault -> Business Vault -> Star schema marts -> BI
(integrate, (derive, (present:
keep everything) conform) facts & dimensions)Where the vault is warranted, this is what it looks like: the vault integrates and remembers, the marts present. The marts are ordinary Kimball — grain, conformed dimensions, type 2 where the past must stay right — built from the vault rather than from staging, and rebuildable whenever the questions change.
So “Kimball vs Data Vault” usually resolves to: Kimball, certainly; Data Vault underneath it, if questions 1 to 3 say so. Choosing the vault instead of a star only makes sense if nobody needs to report yet, which is almost never true.
Where does staging go in this? Before both, and it is the same layer either way — see what a staging layer is for. A vault does not replace staging; the raw vault reads from it.
The mistakes each side makes
Choosing Data Vault as “a better Kimball”
It is not one. It is slower to deliver, harder to query, and three times the tables, in exchange for integration, audit and change-absorption. A team that adopts it for a single well-behaved source has paid for all of that and received none of it.
Building a vault with the marts as “phase two”
Phase two gets cut. The team is left with a beautifully auditable structure nobody can get an answer out of, and the vault gets blamed for what was a project-planning failure. The marts are part of the build.
Building a star over six systems that disagree
The opposite mistake. With no integration layer, every conflict between systems — two customer numbers for one company, two spellings, two hierarchies — gets resolved inside the dimension load, in CASE expressions nobody documents, differently by each developer. Within a year the star is an integration layer that was never designed to be one.
Treating the raw vault as a data source for analysts
They will conclude the warehouse is broken, and from where they sit it is. Every question is five joins and a newest-row filter per satellite. Marts, or a business vault with views, or both.
Moving from one to the other
Star to vault is the common direction and it is not a rewrite: the existing star becomes the mart layer, a vault is built underneath from staging, and the star’s loads are re-pointed from staging to the vault one dimension at a time. The reports do not change.
Vault to star alone — dropping the vault — is rarer, and usually means the vault was never warranted. It is also harmless: the marts are already Kimball; re-point their loads at staging and let the vault go. Nothing about the presentation layer has to move.
Questions people ask
Is Data Vault a replacement for a star schema?
No. A raw vault is an integration and history layer that is not built to be queried; the readable layer on top of it is a star schema. Where a vault is warranted, you build both. Where it is not, you build the star alone.
When is Data Vault worth it?
Several source systems that disagree about the same entities, a schema that changes often, or a genuine audit requirement to prove what arrived and when. One or two of those and it starts paying for itself; none of them and it is overhead.
Can a small team use Data Vault?
It can, with generated loads and a clear plan for the marts, but it should ask whether it needs to. A vault is three tables per entity plus a mart layer, and a small team with a one-source warehouse gets the same reports from a star in a fraction of the time.
Which handles history better?
Data Vault keeps every version of everything by construction. Kimball keeps the history you opt into, per dimension, with type 2. If the question is 'what did we know and when', the vault; if it is 'keep last year's region on last year's sales', type 2 in a star is enough and much cheaper.
Can I migrate from Kimball to Data Vault later?
Yes, and it is the usual direction. The existing star becomes the mart layer, a vault is built underneath it from staging, and the dimension loads are re-pointed at the vault one at a time. The reports keep working throughout.
Let the database answer the five questions
Netune measures how many systems feed a SQL Server database, whether the same entity arrives under different keys, how often the schema appears to change and whether real relationships exist — then scores Kimball and Data Vault against each other on that evidence, reasons shown.
Download the free beta