In this post we are going to cover Kimball vs Inmon, the two classic approaches to building a data warehouse, and how to compare them in an interview.
Bill Inmon's approach is top-down: model the whole enterprise first as one normalized (3NF) warehouse, then build data marts from it. Ralph Kimball's approach is bottom-up: build one dimensional star schema per business process, and connect the stars through shared, conformed dimensions.
We will look at both architectures, then store the same small set of orders both ways and ask each model the same question with SQL.
Question 41 of the Real DWH Question and Answers series on TechBrothersIT.
- The interview answer
- Inmon: top-down, normalized warehouse first
- Kimball: bottom-up, conformed dimensional marts
- Let's create some sample data
- Step 1: Inmon - sales by region and category
- Step 2: Kimball - the same question on the star
- Side by side
- Which one to choose today
- Follow-up questions interviewers ask
- The full script
The interview answer
Inmon is top-down: build a normalized (3NF) enterprise warehouse first, as the single integrated source, then derive dependent data marts from it. Kimball is bottom-up: build dimensional star schemas one business process at a time, tied together by conformed dimensions on a bus matrix. Inmon gives a strong integrated core but takes longer; Kimball delivers value faster and is easier to query.
Inmon: top-down, normalized warehouse first
In Inmon's architecture, data from every source (ERP, CRM, web) is integrated into one enterprise data warehouse (EDW). It is modeled in third normal form, like an operational database, but for the whole company and with history.
- The EDW is the single version of the truth.
- Business users do not query it directly. Each department gets a dependent data mart, often a star schema, built from the EDW.
- The strength is integration. The cost is that we must model the whole enterprise before the first report ships.
Kimball: bottom-up, conformed dimensional marts
In Kimball's architecture, we pick one business process, like sales, and build its star schema: a fact table and its dimensions. Users get value in weeks.
- Then we build the next process, like returns or inventory, with its own fact table.
- The new stars reuse the same conformed dimensions, like customer, product and date.
- The bus matrix plans which business process uses which dimension.
Together, those connected stars are the warehouse. There is no separate normalized layer in between.
Let's create some sample data
We store the same two orders twice: once as Inmon's normalized model (region, city, customer, category, product, orders, order_line) and once as Kimball's star (dim_customer, dim_product, fact_sales).
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS dim_product;
DROP TABLE IF EXISTS order_line;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS product;
DROP TABLE IF EXISTS category;
DROP TABLE IF EXISTS customer;
DROP TABLE IF EXISTS city;
DROP TABLE IF EXISTS region;
-- INMON: the enterprise warehouse in third normal form
CREATE TABLE region (region_id INT PRIMARY KEY, region_name VARCHAR(20));
CREATE TABLE city (city_id INT PRIMARY KEY, city_name VARCHAR(30), region_id INT REFERENCES region(region_id));
CREATE TABLE customer (customer_id INT PRIMARY KEY, name VARCHAR(30), city_id INT REFERENCES city(city_id));
CREATE TABLE category (category_id INT PRIMARY KEY, category_name VARCHAR(20));
CREATE TABLE product (product_id INT PRIMARY KEY, name VARCHAR(30), category_id INT REFERENCES category(category_id));
CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT REFERENCES customer(customer_id));
CREATE TABLE order_line (order_id INT REFERENCES orders(order_id), product_id INT REFERENCES product(product_id), qty INT, price DECIMAL(10,2));
INSERT INTO region VALUES (1, 'West'), (2, 'East');
INSERT INTO city VALUES (10, 'Seattle', 1), (20, 'Boston', 2);
INSERT INTO customer VALUES (100, 'Sara', 10), (200, 'Omar', 20);
INSERT INTO category VALUES (1, 'Office'), (2, 'Electronics');
INSERT INTO product VALUES (11, 'Pen', 1), (22, 'Headphones', 2);
INSERT INTO orders VALUES (1, 100), (2, 200);
INSERT INTO order_line VALUES (1, 11, 2, 5.00), (1, 22, 1, 60.00), (2, 11, 4, 5.00);
-- KIMBALL: the same data as a star schema
CREATE TABLE dim_customer (customer_key INT PRIMARY KEY, name VARCHAR(30), city VARCHAR(30), region VARCHAR(20));
CREATE TABLE dim_product (product_key INT PRIMARY KEY, name VARCHAR(30), category VARCHAR(20));
CREATE TABLE fact_sales (customer_key INT, product_key INT, qty INT, amount DECIMAL(10,2));
INSERT INTO dim_customer VALUES (1, 'Sara', 'Seattle', 'West'), (2, 'Omar', 'Boston', 'East');
INSERT INTO dim_product VALUES (1, 'Pen', 'Office'), (2, 'Headphones', 'Electronics');
INSERT INTO fact_sales VALUES (1, 1, 2, 10.00), (1, 2, 1, 60.00), (2, 1, 4, 20.00);In the normalized model, the order lines hold quantity and price, and everything else is reached through keys: order to customer, customer to city, city to region, and product to category.
SELECT * FROM order_line ORDER BY 1;| order_id | product_id | qty | price |
|---|---|---|---|
| 1 | 11 | 2 | 5.00 |
| 1 | 22 | 1 | 60.00 |
| 2 | 11 | 4 | 5.00 |
In the star, the customer dimension already carries the city and region on one row.
SELECT * FROM dim_customer ORDER BY 1;| customer_key | name | city | region |
|---|---|---|---|
| 1 | Sara | Seattle | West |
| 2 | Omar | Boston | East |
And the fact table stores the amount directly: 2 pens at 5.00 is 10.00, the headphones are 60.00, and 4 pens at 5.00 is 20.00.
SELECT * FROM fact_sales ORDER BY 1;| customer_key | product_key | qty | amount |
|---|---|---|---|
| 1 | 1 | 2 | 10.00 |
| 1 | 2 | 1 | 60.00 |
| 2 | 1 | 4 | 20.00 |
Step 1: Inmon - sales by region and category
Let's ask the normalized model a typical business question: sales by region and product category. The data is split across seven tables, so we need six joins to get from an order line to its region and its category.
SELECT r.region_name, cat.category_name,
SUM(ol.qty * ol.price) AS sales
FROM order_line ol
JOIN orders o ON o.order_id = ol.order_id
JOIN customer c ON c.customer_id = o.customer_id
JOIN city ci ON ci.city_id = c.city_id
JOIN region r ON r.region_id = ci.region_id
JOIN product p ON p.product_id = ol.product_id
JOIN category cat ON cat.category_id = p.category_id
GROUP BY r.region_name, cat.category_name
ORDER BY r.region_name, cat.category_name;| region_name | category_name | sales |
|---|---|---|
| East | Office | 20.00 |
| West | Electronics | 60.00 |
| West | Office | 10.00 |
The East sold 20.00 of Office products, and the West sold 60.00 of Electronics and 10.00 of Office. The answer is right, but the query is long, and a business user would struggle to write it.
Step 2: Kimball - the same question on the star
Now let's ask the star the same question. It is the fact table and two dimensions: three tables and two joins.
SELECT c.region, p.category,
SUM(f.amount) AS sales
FROM fact_sales f
JOIN dim_customer c ON c.customer_key = f.customer_key
JOIN dim_product p ON p.product_key = f.product_key
GROUP BY c.region, p.category
ORDER BY c.region, p.category;| region | category | sales |
|---|---|---|
| East | Office | 20.00 |
| West | Electronics | 60.00 |
| West | Office | 10.00 |
The result is identical: East Office 20.00, West Electronics 60.00 and West Office 10.00. Six joins versus two is the reason why, in both approaches, users end up querying star schemas. Inmon builds them as marts on top of the EDW. Kimball makes them the warehouse itself.
Side by side
Here is the comparison to remember for the interview.
| Inmon | Kimball | |
|---|---|---|
| Approach | top-down | bottom-up |
| Core model | 3NF enterprise DW | star schemas |
| First delivery | slower (months+) | fast (weeks) |
| Integration | in the 3NF core | conformed dimensions |
| Users query | dependent marts | the stars directly |
Inmon is slower to first delivery, but the core is very flexible for new questions, because it is not shaped for any one report. Kimball is fast and easy for business users, but it needs discipline with conformed dimensions, or the stars drift apart. Both aim at the same thing: one integrated, trusted version of the truth.
Which one to choose today
Most modern teams use a hybrid: a cleaned, integrated core layer (sometimes normalized, sometimes a Data Vault), with Kimball-style star schemas on top for reporting. That is the medallion pattern again: silver is the integrated core, and gold is the stars.
Don't pick a side. Explain both, then choose based on team size, timeline and how many sources need integrating.
Follow-up questions interviewers ask
Which approach delivers value faster?
Kimball: the first star schema can ship in weeks; Inmon needs the enterprise model first.
How does Kimball keep stars consistent?
Conformed dimensions shared across facts, planned on the enterprise bus matrix.
Do business users query the 3NF warehouse in Inmon?
Usually not; they query the dependent data marts built from it.
The full script
Here is everything from this post in one script. Copy it and run it top to bottom.
-- ========================================================================
-- Data Warehouse Interview Q&A #41 - Kimball vs Inmon
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example: CREATE SCHEMA dwh_q41; SET search_path TO dwh_q41;
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS dim_product;
DROP TABLE IF EXISTS order_line;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS product;
DROP TABLE IF EXISTS category;
DROP TABLE IF EXISTS customer;
DROP TABLE IF EXISTS city;
DROP TABLE IF EXISTS region;
-- INMON: the enterprise warehouse in third normal form
CREATE TABLE region (region_id INT PRIMARY KEY, region_name VARCHAR(20));
CREATE TABLE city (city_id INT PRIMARY KEY, city_name VARCHAR(30), region_id INT REFERENCES region(region_id));
CREATE TABLE customer (customer_id INT PRIMARY KEY, name VARCHAR(30), city_id INT REFERENCES city(city_id));
CREATE TABLE category (category_id INT PRIMARY KEY, category_name VARCHAR(20));
CREATE TABLE product (product_id INT PRIMARY KEY, name VARCHAR(30), category_id INT REFERENCES category(category_id));
CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT REFERENCES customer(customer_id));
CREATE TABLE order_line (order_id INT REFERENCES orders(order_id), product_id INT REFERENCES product(product_id), qty INT, price DECIMAL(10,2));
INSERT INTO region VALUES (1, 'West'), (2, 'East');
INSERT INTO city VALUES (10, 'Seattle', 1), (20, 'Boston', 2);
INSERT INTO customer VALUES (100, 'Sara', 10), (200, 'Omar', 20);
INSERT INTO category VALUES (1, 'Office'), (2, 'Electronics');
INSERT INTO product VALUES (11, 'Pen', 1), (22, 'Headphones', 2);
INSERT INTO orders VALUES (1, 100), (2, 200);
INSERT INTO order_line VALUES (1, 11, 2, 5.00), (1, 22, 1, 60.00), (2, 11, 4, 5.00);
-- KIMBALL: the same data as a star schema
CREATE TABLE dim_customer (customer_key INT PRIMARY KEY, name VARCHAR(30), city VARCHAR(30), region VARCHAR(20));
CREATE TABLE dim_product (product_key INT PRIMARY KEY, name VARCHAR(30), category VARCHAR(20));
CREATE TABLE fact_sales (customer_key INT, product_key INT, qty INT, amount DECIMAL(10,2));
INSERT INTO dim_customer VALUES (1, 'Sara', 'Seattle', 'West'), (2, 'Omar', 'Boston', 'East');
INSERT INTO dim_product VALUES (1, 'Pen', 'Office'), (2, 'Headphones', 'Electronics');
INSERT INTO fact_sales VALUES (1, 1, 2, 10.00), (1, 2, 1, 60.00), (2, 1, 4, 20.00);
-- Step 1: Inmon (3NF): sales by region and category needs 7 tables
SELECT r.region_name, cat.category_name,
SUM(ol.qty * ol.price) AS sales
FROM order_line ol
JOIN orders o ON o.order_id = ol.order_id
JOIN customer c ON c.customer_id = o.customer_id
JOIN city ci ON ci.city_id = c.city_id
JOIN region r ON r.region_id = ci.region_id
JOIN product p ON p.product_id = ol.product_id
JOIN category cat ON cat.category_id = p.category_id
GROUP BY r.region_name, cat.category_name
ORDER BY r.region_name, cat.category_name;
-- Step 2: Kimball (star): the same question needs 3 tables
SELECT c.region, p.category,
SUM(f.amount) AS sales
FROM fact_sales f
JOIN dim_customer c ON c.customer_key = f.customer_key
JOIN dim_product p ON p.product_key = f.product_key
GROUP BY c.region, p.category
ORDER BY c.region, p.category;
Key points
- Inmon: top-down, 3NF enterprise DW first
- Then dependent marts for users
- Kimball: bottom-up, star per business process
- Tied by conformed dimensions (bus matrix)
- Today: usually a hybrid
Question 42 — What is Data Vault? What is a Data Vault? Explain hubs, links and satellites
Every query and every result on this page was run on PostgreSQL 16 using the script above. The SQL is standard and runs on most databases with small changes (noted in the script). All tables and data are invented demo data for teaching.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.