DWH Interview Question 28 - What are mini-dimensions and outriggers?

Data Warehouse Interview Q&A · Question 28

In this post we are going to cover mini-dimensions and outriggers, two techniques for keeping large dimensions under control.

A mini-dimension takes the attributes of a big dimension that change often, like a customer's age band or loyalty tier, and moves them into their own small table. That table holds one row per combination of bands, and the fact table stores its key next to the customer key. The big customer dimension stops growing every time a tier changes.

An outrigger is a dimension that is referenced by another dimension instead of by the fact, like a customer pointing to a shared geography dimension. We will build a mini-dimension in SQL, use it in a fact and a report, and then look at where outriggers fit.

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

On this page

1. The interview answer
2. The problem: a huge dimension that changes too often
3. Let's create some sample data
4. Step 1: Build the mini-dimension
5. Step 2: Load sales with two keys
6. Step 3: Sales by loyalty tier at the time of the sale
7. Outriggers
8. Follow-up questions interviewers ask
9. The full script

The interview answer

Say this first

A mini-dimension moves rapidly changing attributes (age band, income band, loyalty tier) out of a large dimension into a small dimension of banded combinations, with its own key on the fact. It stops a huge dimension from exploding with history rows. An outrigger is a dimension referenced by another dimension, like customer → geography: a controlled bit of snowflaking.

The problem: a huge dimension that changes too often

TB Shop has millions of customers. Marketing wants to analyze sales by loyalty tier and age band as they were at the time of each sale. If those attributes live on the customer dimension with full history, every tier change creates a new customer row. Here is what that looks like for one customer.

dim_customer (with history)5 rows · 4 columns

customer_key	name	loyalty_tier	valid_from
1	Sara Khan	Bronze	2025-01-01
57	Sara Khan	Silver	2025-04-01
203	Sara Khan	Bronze	2025-07-01
388	Sara Khan	Silver	2025-10-01
512	Sara Khan	Gold	2026-03-01

Sara Khan alone has five versions between 2025-01-01 and 2026-03-01, just because her tier moved between Bronze, Silver and Gold. Multiply that by millions of customers changing every few months, and the biggest dimension in the warehouse grows out of control and every load gets slower.

The fix is to split the fast-changing part out. We band the values first, so there are only a few of them, and store every combination once in a small dimension.

Let's create some sample data

We need a small dim_customer with only the slow, stable attributes, two lookup tables for the bands (age_bands and loyalty_tiers), an empty mini-dimension dim_customer_profile, and an empty fact_sales.

Sample data — run this first

DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_customer_profile;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS age_bands;
DROP TABLE IF EXISTS loyalty_tiers;

-- the big, slowly changing customer dimension (millions of rows in real life)
CREATE TABLE dim_customer (
  customer_key INT PRIMARY KEY,
  name         VARCHAR(50),
  city         VARCHAR(50)
);
INSERT INTO dim_customer VALUES (1, 'Sara Khan', 'Toronto'), (2, 'Omar Ali', 'Dallas');

-- the bands we want to analyze by
CREATE TABLE age_bands (age_band VARCHAR(10), sort_order INT);
CREATE TABLE loyalty_tiers (loyalty_tier VARCHAR(10), sort_order INT);
INSERT INTO age_bands VALUES ('18-34', 1), ('35-54', 2), ('55+', 3);
INSERT INTO loyalty_tiers VALUES ('Bronze', 1), ('Silver', 2), ('Gold', 3);

-- the mini-dimension: one row per COMBINATION of fast-changing bands
CREATE TABLE dim_customer_profile (
  profile_key  INT PRIMARY KEY,
  age_band     VARCHAR(10),
  loyalty_tier VARCHAR(10)
);

CREATE TABLE fact_sales (
  sale_date    DATE,
  customer_key INT REFERENCES dim_customer(customer_key),
  profile_key  INT REFERENCES dim_customer_profile(profile_key),
  amount       DECIMAL(10,2)
);

Let's look at the customer dimension.

Query

SELECT * FROM dim_customer ORDER BY 1;

Result2 rows · 3 columns

customer_key	name	city
1	Sara Khan	Toronto
2	Omar Ali	Dallas

Two customers, with only a name and a city. There is no loyalty tier and no age band here anymore. Those will live in the mini-dimension. The band tables hold three age bands (18-34, 35-54, 55+) and three loyalty tiers (Bronze, Silver, Gold), each with a sort order.

Step 1: Build the mini-dimension

Now let's build every combination of the bands with a CROSS JOIN, and give each combination a key with ROW_NUMBER().

Statement

INSERT INTO dim_customer_profile
SELECT ROW_NUMBER() OVER (ORDER BY a.sort_order, t.sort_order),
       a.age_band, t.loyalty_tier
FROM age_bands a
CROSS JOIN loyalty_tiers t;

Now let's check the result.

Query

SELECT * FROM dim_customer_profile ORDER BY profile_key;

Result9 rows · 3 columns

profile_key	age_band	loyalty_tier
1	18-34	Bronze
2	18-34	Silver
3	18-34	Gold
4	35-54	Bronze
5	35-54	Silver
6	35-54	Gold
7	55+	Bronze
8	55+	Silver
9	55+	Gold

Three age bands times three tiers gives 9 rows. Profile 2 is 18-34 and Silver, profile 3 is 18-34 and Gold. This table stays tiny forever: millions of customers, still nine profiles.

Step 2: Load sales with two keys

Now each sale stores two keys: the customer, and the customer's profile at the time of the sale. Sara bought in January as Silver and in March as Gold. Omar bought in February as 35-54 and Bronze.

Statement

INSERT INTO fact_sales VALUES
  ('2026-01-15', 1, 2, 53.00),  -- Sara: 18-34, Silver
  ('2026-02-10', 2, 4, 25.00),  -- Omar: 35-54, Bronze
  ('2026-03-20', 1, 3, 74.00);  -- Sara: 18-34, Gold

Now let's check the result.

Query

SELECT * FROM fact_sales ORDER BY sale_date;

Result3 rows · 4 columns

sale_date	customer_key	profile_key	amount
2026-01-15	1	2	53.00
2026-02-10	2	4	25.00
2026-03-20	1	3	74.00

Sara's January sale has profile 2 and her March sale has profile 3. The customer dimension did not change at all. Only the profile key on the new sale is different, so no history rows were added to the big dimension.

Step 3: Sales by loyalty tier at the time of the sale

Now let's report sales by loyalty tier, joining the fact to the mini-dimension.

Query

SELECT p.loyalty_tier, SUM(f.amount) AS sales
FROM fact_sales f
JOIN dim_customer_profile p
  ON p.profile_key = f.profile_key
GROUP BY p.loyalty_tier
ORDER BY sales DESC;

Result3 rows · 2 columns

loyalty_tier	sales
Gold	74.00
Silver	53.00
Bronze	25.00

Gold has 74.00, Silver 53.00 and Bronze 25.00. Sara's January purchase stays Silver and her March purchase counts as Gold, which is exactly what marketing asked for, with zero new rows in dim_customer.

Outriggers

Normally only fact tables point to dimensions. An outrigger is the exception: a dimension that points to another dimension.

• Shared geography - both dim_customer and dim_store need city, region and country. Instead of copying it into both, each can reference one shared dim_geography.
• A date on a dimension - a customer's first purchase date can point to dim_date.

Use outriggers sparingly. They are a small, controlled bit of snowflaking, and every one adds a join. Kimball recommends them only when the shared dimension is large or reused a lot.

Mini vs junk

Both are small tables of combinations. A junk dimension holds unrelated transaction flags. A mini-dimension holds fast-changing attributes of one entity, like a customer.

Follow-up questions interviewers ask

Mini-dimension vs junk dimension?

Junk: unrelated transaction flags. Mini: rapidly changing (banded) attributes of one entity, such as a customer.

How do you get the current profile of a customer?

Add a current profile key to the customer dimension, overwritten on change (Type 1).

When is an outrigger acceptable?

When a large dimension is shared by several dimensions (geography) or for a date on a dimension (first purchase date).

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 #28 - Mini-dimensions and outriggers
-- 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_q28;  SET search_path TO dwh_q28;
-- ========================================================================

-- Step 0: create and load the sample data
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_customer_profile;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS age_bands;
DROP TABLE IF EXISTS loyalty_tiers;

-- the big, slowly changing customer dimension (millions of rows in real life)
CREATE TABLE dim_customer (
  customer_key INT PRIMARY KEY,
  name         VARCHAR(50),
  city         VARCHAR(50)
);
INSERT INTO dim_customer VALUES (1, 'Sara Khan', 'Toronto'), (2, 'Omar Ali', 'Dallas');

-- the bands we want to analyze by
CREATE TABLE age_bands (age_band VARCHAR(10), sort_order INT);
CREATE TABLE loyalty_tiers (loyalty_tier VARCHAR(10), sort_order INT);
INSERT INTO age_bands VALUES ('18-34', 1), ('35-54', 2), ('55+', 3);
INSERT INTO loyalty_tiers VALUES ('Bronze', 1), ('Silver', 2), ('Gold', 3);

-- the mini-dimension: one row per COMBINATION of fast-changing bands
CREATE TABLE dim_customer_profile (
  profile_key  INT PRIMARY KEY,
  age_band     VARCHAR(10),
  loyalty_tier VARCHAR(10)
);

CREATE TABLE fact_sales (
  sale_date    DATE,
  customer_key INT REFERENCES dim_customer(customer_key),
  profile_key  INT REFERENCES dim_customer_profile(profile_key),
  amount       DECIMAL(10,2)
);


-- Step 1: Build the mini-dimension: every combination of bands
INSERT INTO dim_customer_profile
SELECT ROW_NUMBER() OVER (ORDER BY a.sort_order, t.sort_order),
       a.age_band, t.loyalty_tier
FROM age_bands a
CROSS JOIN loyalty_tiers t;

-- check the result
SELECT * FROM dim_customer_profile ORDER BY profile_key;

-- Step 2: Each sale stores the customer AND the profile at the time of the sale
INSERT INTO fact_sales VALUES
  ('2026-01-15', 1, 2, 53.00),  -- Sara: 18-34, Silver
  ('2026-02-10', 2, 4, 25.00),  -- Omar: 35-54, Bronze
  ('2026-03-20', 1, 3, 74.00);  -- Sara: 18-34, Gold

-- check the result
SELECT * FROM fact_sales ORDER BY sale_date;

-- Step 3: Sales by loyalty tier at the time of the sale
SELECT p.loyalty_tier, SUM(f.amount) AS sales
FROM fact_sales f
JOIN dim_customer_profile p
  ON p.profile_key = f.profile_key
GROUP BY p.loyalty_tier
ORDER BY sales DESC;

Key points

• Mini-dimension: fast-changing, banded attributes split from a big dimension
• One row per combination; the fact stores its key
• The big dimension stops exploding with history
• Outrigger: a dimension referenced by another dimension
• Use outriggers sparingly: each one adds a join

Next in this series

Question 29 — Bridge tables for many-to-many What is a bridge table, and how do you handle many-to-many in a star schema?

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.