DWH Interview Question 24 - What is a conformed dimension, and what is the bus matrix?

Data Warehouse Interview Q&A · Question 24

In this post we are going to cover conformed dimensions and the bus matrix, two ideas that turn a set of separate star schemas into one data warehouse.

A conformed dimension is a dimension that is shared by several fact tables, with the same keys, the same attributes and the same meaning. One dim_product used by sales, inventory and returns is the classic example. Because every fact points to the same rows, you can compare processes side by side, which is called drilling across.

The bus matrix is the planning tool that goes with it: a simple grid of business processes against dimensions. We will see the problem conformed dimensions solve, build one shared product dimension with two facts, and then compare sales and stock by category the wrong way and the right way.

Question 24 of the Real DWH Question and Answers series on TechBrothersIT.

On this page

1. The interview answer
2. The problem: dimensions that don't match
3. Let's create some sample data
4. Step 1: The wrong way - join two facts directly
5. Step 2: The right way - drill across
6. The bus matrix
7. Shrunken dimensions
8. Follow-up questions interviewers ask
9. The full script

The interview answer

Say this first

A conformed dimension is shared by several fact tables with identical keys, attributes and meaning, like one dim_date or dim_product for sales, inventory and returns. That lets you drill across business processes. The bus matrix is a grid of business processes by dimensions, showing which conformed dimensions each fact uses.

The problem: dimensions that don't match

Imagine the sales team built their own product table and the inventory team built theirs. Here is what the category column looks like in each.

sales_product3 rows · 2 columns

product	category
Laptop Stand	Accessories
USB-C Cable	Accessories
Wireless Mouse	Electronics

inventory_product3 rows · 2 columns

product	category
Laptop Stand	ACC
USB-C Cable	Acc.
Wireless Mouse	ELEC

Sales writes Accessories. Inventory writes ACC for one product and Acc. for another. Now a manager asks: for each category, how much did we sell and how much is in stock? The categories don't match, so the two answers can't be lined up, and every comparison becomes a manual mapping exercise.

Conformed dimensions fix this with one product dimension: one set of keys and one spelling, maintained in one place and used by every fact table.

Let's create some sample data

We need one shared dim_product and two fact tables that both point to it: fact_sales for sales and fact_inventory for the daily stock snapshot.

Sample data — run this first

DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS fact_inventory;
DROP TABLE IF EXISTS dim_product;

-- ONE product dimension, shared (conformed) by both fact tables
CREATE TABLE dim_product (
  product_key  INT PRIMARY KEY,
  product_name VARCHAR(50),
  category     VARCHAR(50)
);
INSERT INTO dim_product VALUES
  (1, 'Laptop Stand',   'Accessories'),
  (2, 'USB-C Cable',    'Accessories'),
  (3, 'Wireless Mouse', 'Electronics');

-- business process 1: sales (transaction grain)
CREATE TABLE fact_sales (
  sale_date   DATE,
  product_key INT REFERENCES dim_product(product_key),
  amount      DECIMAL(10,2)
);
INSERT INTO fact_sales VALUES
  ('2026-01-05', 1, 35.00), ('2026-01-05', 2, 18.00),
  ('2026-01-06', 1, 70.00), ('2026-01-06', 3, 25.00);

-- business process 2: inventory (daily snapshot grain)
CREATE TABLE fact_inventory (
  snapshot_date DATE,
  product_key   INT REFERENCES dim_product(product_key),
  on_hand       INT
);
INSERT INTO fact_inventory VALUES
  ('2026-01-05', 1, 11), ('2026-01-05', 2, 52), ('2026-01-05', 3, 9),
  ('2026-01-06', 1, 10), ('2026-01-06', 2, 50), ('2026-01-06', 3, 8);

Let's look at the shared dimension.

Query

SELECT * FROM dim_product ORDER BY 1;

Result3 rows · 3 columns

product_key	product_name	category
1	Laptop Stand	Accessories
2	USB-C Cable	Accessories
3	Wireless Mouse	Electronics

Three products and two categories, each spelled exactly one way.

Query

SELECT * FROM fact_sales ORDER BY 1;

Result4 rows · 3 columns

sale_date	product_key	amount
2026-01-05	1	35.00
2026-01-05	2	18.00
2026-01-06	1	70.00
2026-01-06	3	25.00

Four sales over January 5 and 6, each storing a product_key from the dimension.

Query

SELECT * FROM fact_inventory ORDER BY 1;

Result6 rows · 3 columns

snapshot_date	product_key	on_hand
2026-01-05	1	11
2026-01-05	2	52
2026-01-05	3	9
2026-01-06	1	10
2026-01-06	2	50
2026-01-06	3	8

The inventory fact has one row per product per day, six rows in total, using the same product_key. Now category means exactly the same thing in both facts.

Step 1: The wrong way - join two facts directly

Now let's try the tempting approach: join the sales fact straight to the inventory fact on product, then sum sales by category.

Query

SELECT p.category, SUM(s.amount) AS sales
FROM fact_sales s
JOIN fact_inventory i ON i.product_key = s.product_key
JOIN dim_product p    ON p.product_key = s.product_key
GROUP BY p.category
ORDER BY p.category;

Result2 rows · 2 columns

category	sales
Accessories	246.00
Electronics	50.00

Accessories shows 246.00 and Electronics 50.00. Both are double the truth. The inventory fact has two rows per product, one for each day, so every sale matches two inventory rows and is counted twice. This is called fan-out: when two facts are joined directly, every row on one side is repeated once for each matching row on the other. Nothing errors, the numbers are just wrong.

Step 2: The right way - drill across

The right way is to summarize each fact on its own by the conformed attribute, category, and only then join the small summaries. For stock we take the snapshot for January 6, because stock levels should not be added across days.

Query

WITH s AS (
  SELECT p.category, SUM(f.amount) AS sales
  FROM fact_sales f
  JOIN dim_product p ON p.product_key = f.product_key
  GROUP BY p.category),
i AS (
  SELECT p.category, SUM(f.on_hand) AS stock
  FROM fact_inventory f
  JOIN dim_product p ON p.product_key = f.product_key
  WHERE f.snapshot_date = '2026-01-06'
  GROUP BY p.category)
SELECT s.category, s.sales, i.stock
FROM s JOIN i ON i.category = s.category
ORDER BY s.category;

Result2 rows · 3 columns

category	sales	stock
Accessories	123.00	60
Electronics	25.00	8

Accessories sold 123.00 and has 60 units in stock. Electronics sold 25.00 and has 8 in stock. These are correct, because each fact was aggregated at its own grain first. The join only works because both facts use the same product dimension, so category lines up perfectly.

The bus matrix

The bus matrix is Kimball's planning tool for a whole warehouse. Each row is a business process, and each one becomes a fact table. Each column is a dimension, and a tick means that process uses that dimension.

Business process	Date	Product	Customer	Store	Warehouse	Promotion
Sales	✓	✓	✓	✓		✓
Inventory	✓	✓			✓
Returns	✓	✓	✓	✓
Shipments	✓	✓	✓		✓

Columns with many ticks, like date and product, are the conformed dimensions. They must be designed once, agreed across teams and shared by everyone. Then you build the warehouse one process at a time, and every new fact plugs into the same dimensions, like devices on a bus. That is where the name comes from.

Shrunken dimensions

One more term that comes up: the shrunken dimension. Some facts live at a higher grain. A budget is planned per month per category, not per day per product.

• dim_month is derived from dim_date, one row per month.
• dim_category is derived from dim_product, one row per category.
• fact_budget then uses month and category.

Because they are cut from the same source, with the same values and spelling, the budget can still be compared with actual sales rolled up to month and category.

Follow-up questions interviewers ask

Why not join two fact tables directly?

Different grains multiply rows (fan-out) and inflate totals; aggregate each fact by the conformed attributes, then join the results.

Who owns conformed dimensions?

A central data team or data governance: they are designed once and shared by all marts.

What is a shrunken dimension?

A conformed dimension at a coarser grain (month from date, category from product) for higher-grain facts like budgets.

The full script

Here is everything from this post in one script. Copy it and run it top to bottom.

Full script — copy and run top to bottom

-- ========================================================================
-- Data Warehouse Interview Q&A #24 - Conformed dimensions and the bus matrix
-- 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_q24;  SET search_path TO dwh_q24;
-- ========================================================================

-- Step 0: create and load the sample data
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS fact_inventory;
DROP TABLE IF EXISTS dim_product;

-- ONE product dimension, shared (conformed) by both fact tables
CREATE TABLE dim_product (
  product_key  INT PRIMARY KEY,
  product_name VARCHAR(50),
  category     VARCHAR(50)
);
INSERT INTO dim_product VALUES
  (1, 'Laptop Stand',   'Accessories'),
  (2, 'USB-C Cable',    'Accessories'),
  (3, 'Wireless Mouse', 'Electronics');

-- business process 1: sales (transaction grain)
CREATE TABLE fact_sales (
  sale_date   DATE,
  product_key INT REFERENCES dim_product(product_key),
  amount      DECIMAL(10,2)
);
INSERT INTO fact_sales VALUES
  ('2026-01-05', 1, 35.00), ('2026-01-05', 2, 18.00),
  ('2026-01-06', 1, 70.00), ('2026-01-06', 3, 25.00);

-- business process 2: inventory (daily snapshot grain)
CREATE TABLE fact_inventory (
  snapshot_date DATE,
  product_key   INT REFERENCES dim_product(product_key),
  on_hand       INT
);
INSERT INTO fact_inventory VALUES
  ('2026-01-05', 1, 11), ('2026-01-05', 2, 52), ('2026-01-05', 3, 9),
  ('2026-01-06', 1, 10), ('2026-01-06', 2, 50), ('2026-01-06', 3, 8);


-- Step 1: WRONG: joining two fact tables directly multiplies rows
SELECT p.category, SUM(s.amount) AS sales
FROM fact_sales s
JOIN fact_inventory i ON i.product_key = s.product_key
JOIN dim_product p    ON p.product_key = s.product_key
GROUP BY p.category
ORDER BY p.category;

-- Step 2: RIGHT: drill across - summarize each fact by the conformed attribute, then join the summaries
WITH s AS (
  SELECT p.category, SUM(f.amount) AS sales
  FROM fact_sales f
  JOIN dim_product p ON p.product_key = f.product_key
  GROUP BY p.category),
i AS (
  SELECT p.category, SUM(f.on_hand) AS stock
  FROM fact_inventory f
  JOIN dim_product p ON p.product_key = f.product_key
  WHERE f.snapshot_date = '2026-01-06'
  GROUP BY p.category)
SELECT s.category, s.sales, i.stock
FROM s JOIN i ON i.category = s.category
ORDER BY s.category;

Key points

• Conformed: one shared dimension, same keys, attributes and meaning
• Lets you drill across processes (sales vs stock)
• Never join two facts directly: aggregate each, then join
• Bus matrix: processes × dimensions, the warehouse blueprint
• Shrunken dimensions conform at a coarser grain

Next in this series

Question 25 — What is a degenerate dimension? What is a degenerate dimension?

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.