Inmon vs Kimball vs Data Vault

A data warehouse is a database built for reports. It collects data from many source systems, such as the shop database.

The sources do not agree. One system writes a country as IN, and another writes India. Integration means making them agree in one place.

A warehouse architecture decides where integration happens. Three well-known methods give three answers: Kimball, Inmon and Data Vault.

You know the pieces already. Third normal form (3NF) stores each piece of data once. A star puts a fact table in the middle with flat dimensions around it.

This chapter builds the same shop three ways. Then it asks one question of all three, and compares the cost.

What you will learn

1. Why every method must integrate, and where each one does it.
2. Kimball: one star at a time, joined by a bus matrix.
3. Inmon: one 3NF core first, then marts.
4. Data Vault: hubs, links and satellites, with stars on top.
5. One query, three models, one answer. Then a trade-off table.
6. When each one fits, how to mix them, and what to say in an interview.

Take Asha. The shop database stores Asha as customer 1, with country IN. A support tool stores Asha as A-77, with country India.

Someone must decide two things. Is A-77 the same person as customer 1? Is India the same as IN?

That work is integration.

A data mart is a small set of tables for one subject, such as orders. Marts are usually stars. The three methods differ in where integration happens, and what they build first:

No method skips integration. The choice is where you do it, and when you spend the work.

Ralph Kimball’s method is bottom-up. You build one star for one business process, then the next. A business process is one activity the company measures, such as orders or refunds.

The stars join up through conformed dimensions. Recap: a conformed dimension is one dimension table that many stars share, with the same keys and meaning.

The shop as one star

Here is the orders star. Its dimension uses SCD Type 2 (slowly changing dimension, Type 2), so it keeps history. Asha has two rows, one for IN and one for DE.

Order 101 points at key 1001, the IN row. Order 102 points at key 1006, the DE row. The star remembers where Asha lived at order time.

The bus matrix

To add more stars, plan the shared dimensions first. A bus matrix lists business processes down the side and shared dimensions across the top. A yes means that star uses that dimension:

Read it by column. The dim_customer column has three yes marks, so three stars must agree on what a customer is. That column needs one owner.

Bottom-up delivery

Delivery follows the matrix, one row at a time:

1. Pick one business process, and write its grain (what one row means).
2. List its dimensions in the matrix, then build its star.
3. Ship reports from that star.
4. Build the next star. Reuse the shared dimensions, and add only what is new.

The first report waits for one star, not for the whole warehouse. The cost is discipline. If one team changes dim_customer alone, every star that shares it changes too.

Bill Inmon’s method is top-down. You first build one enterprise data warehouse (EDW). It is a 3NF model of the whole company, and marts come after it.

Inmon describes this core as integrated and time-variant. Time-variant means it keeps history, with dates.

The shop in the core

Here are the first two EDW tables. The table edw_customer says only who the customer is. The country sits in its own history table, with dates.

Dev has no country, so Dev has no row in edw_customer_country. The orders table keeps only order facts:

A full EDW has more tables, such as products, order items and country names. Each piece of data lives in one place.

Marts come second

Analysts should not query the core. It needs many joins.

Instead you build dependent data marts from it. A dependent data mart is a star built from the EDW, not from the sources.

Every mart reads the same core, so the marts agree with each other. The cost is time. The first report waits until the core models the subjects it needs.

Dan Linstedt created Data Vault. This chapter uses the Data Vault 2.0 ideas. Data Vault splits data by what each part does.

A business key is the id the business uses, such as customer_id 1. Data Vault has three table types:

• A hub holds one row for each business key.
• A link holds one row for each relationship between hubs, such as a customer placing an order.
• A satellite holds the descriptive columns of a hub or link, and their history.

Every vault table also has a load_date and a record_source. The load_date is when the warehouse loaded the row. The record_source says which system sent it.

Look at the picture. The link joins two hubs. The satellite belongs to one hub.

Hub: the keys

A hub holds the business key and no details. A hash turns any text into a fixed-length code. The hub’s hash key (customer_hk) is a hash of the business key.

MD5 and SHA-1 are common choices. Two different keys can give the same hash, which is called a collision. A collision is very rare, but some teams pick a longer hash such as SHA-256 to lower the risk.

Here is the MD5 of the text of each customer_id, cut to 8 characters:

The hash comes from the key alone. A loader needs no lookup to compute it, so many loaders can run at the same time.

Link: the relationships

A link holds the hash keys of the hubs it joins. This one says which customer placed which order.

A real link also has its own hash key, left out here. Order hashes are written as words to keep the table short:

Satellite: the details

The satellite holds the details about a customer, such as name and country. This is the state on 2026-01-01, before Asha moves:

Insert only

In a Data Vault, loaders never update or delete a row. On 2026-01-08 the loader sees Asha in DE, so it inserts one new row:

INSERT INTO sat_customer_details
  (customer_hk, load_date, record_source, name, country)
VALUES
  ('c4ca4238a0b923820dcc509a6f75849b', '2026-01-08', 'shop_db', 'Asha', 'DE');

SELECT load_date, country
FROM sat_customer_details
WHERE customer_hk = 'c4ca4238a0b923820dcc509a6f75849b'
ORDER BY load_date;

Compare this with SCD Type 2. There you also set valid_to on the old row. Here nothing changes, so you can always prove what the warehouse knew on any day.

People rarely query the hubs, links and satellites directly. They need many joins. A Data Vault warehouse has three layers:

1. Raw vault: the data exactly as the sources sent it. No business rules.
2. Business vault: new tables that apply business rules on top of the raw vault.
3. Information marts: stars built on top. These are the tables people query.

The business vault also holds helper tables, such as point-in-time (PIT) and bridge tables. They make the joins that build the marts faster. The next chapter shows them.

A rule in the business vault

Suppose the business says a missing country means UNKNOWN. That is a rule, so it does not go in the raw vault. It goes in a business vault satellite:

Dev is NULL in the raw vault and UNKNOWN here. If the rule changes, you add new business vault rows. The raw vault keeps what the source said.

Build a mart on top

After the insert, the satellite has six rows. An information mart turns it into a dimension.

Each load_date becomes valid_from. LEAD reads a value from the next row, so the next load_date becomes valid_to:

SELECT name, country,
       load_date AS valid_from,
       LEAD(load_date, 1, DATE '9999-12-31')
         OVER (PARTITION BY customer_hk ORDER BY load_date) AS valid_to
FROM sat_customer_details
ORDER BY name, valid_from;

The question is: paid revenue by country, using the country at order time. Check it by hand first:

• Order 101 on 2026-01-03: Asha was in IN, so IN gets 40.00.
• Order 102 on 2026-01-10: Asha had moved to DE, so DE gets 25.00.
• Order 104: Carla is in US, so US gets 15.50.
• Order 106: Dev has no country, so NULL gets 80.00.

Order 103 is refunded and order 105 is pending, so neither counts. IN is 40.00, not 65.00, because Asha’s second order belongs to DE.

Kimball: join the star

The fact row already points at the right customer version. One join is enough:

SELECT c.country, SUM(f.amount) AS revenue
FROM fact_orders  AS f
JOIN dim_customer AS c ON c.customer_key = f.customer_key
WHERE f.status = 'paid'
GROUP BY c.country
ORDER BY revenue DESC;

Inmon: join by date range

The table edw_order has no customer_key. You match each order date to the country row that was valid that day:

SELECT cc.country_code AS country, SUM(o.amount) AS revenue
FROM edw_order AS o
LEFT JOIN edw_customer_country AS cc
  ON  cc.customer_id = o.customer_id
  AND o.order_date >= cc.valid_from
  AND o.order_date <  cc.valid_to
WHERE o.status = 'paid'
GROUP BY cc.country_code
ORDER BY revenue DESC;

In practice analysts query the mart built from the EDW. Its query looks like the star query above.

Data Vault: build the ranges first

The amounts sit in a satellite on the order hub. Assume each order was loaded on its order date:

Here each order has one satellite row. A real order satellite can have two, such as paid and then refunded. Then you first keep only the latest row per order.

Querying the vault directly takes three steps. Build the date ranges, follow the link, then join by date:

WITH cust AS (
  SELECT customer_hk, country,
         load_date AS valid_from,
         LEAD(load_date, 1, DATE '9999-12-31')
           OVER (PARTITION BY customer_hk ORDER BY load_date) AS valid_to
  FROM sat_customer_details
)
SELECT c.country, SUM(o.amount) AS revenue
FROM sat_order_details AS o
JOIN link_order_customer AS l ON l.order_hk = o.order_hk
LEFT JOIN cust AS c
  ON  c.customer_hk = l.customer_hk
  AND o.order_date >= c.valid_from
  AND o.order_date <  c.valid_to
WHERE o.status = 'paid'
GROUP BY c.country
ORDER BY revenue DESC;

All three models give the same answer here. The vault gets DE for order 102 only because the move was loaded on 2026-01-08, the day it happened.

If the file had come on 2026-01-12, order 102 would count under IN, and IN would show 65.00. Building the ranges from moved_on, not load_date, avoids this. A Kimball load has the same risk if the fact row is loaded before the move.

The models differ in where the at-order-time work happens:

• Kimball: once, at load time, when the fact row picks its customer key.
• Inmon core: at query time, in the date join. A mart can do it once.
• Data Vault: at query time, or once in the mart build.

Each row is a question you can ask about a method. Here are the five that matter most:

A source adds a column

One example makes the cost of change clear. The source adds a column, loyalty_tier, to customers. Here is what you touch:

The vault is cheapest to change. It also has the most tables to build and read. That is the price.

Match the method to the situation. Here are four common ones:

The reverse cases matter too. Inmon is a poor fit when the business needs results in weeks. Data Vault is a poor fit for a small, stable, single-source warehouse.

Most real warehouses mix methods. Most teams serve stars to analysts. So the open choice is what sits under the stars.

Vault core, Kimball marts

A common mix has four steps. Each step feeds the next:

1. Sources send data.
2. The raw vault stores it, insert-only.
3. The business vault applies the rules.
4. Kimball stars serve the reports.

The vault takes new sources easily and keeps every source row. The stars are easy to query. Inmon’s own plan also ends in marts, and those marts are often stars.

Medallion layers, loosely

Many modern platforms use medallion layers: bronze, silver and gold. They describe how clean the data is, not how the tables are shaped. So the match is loose:

The chapter Modern Warehouse Modeling covers medallion layers in full.

What to say in an interview

Each exercise opens a canvas for an entity-relationship diagram (ERD). You draw the tables, and the review checks them against sample rows.

Start at the top. The last two are harder, so they are optional.