In this post we are going to cover the junk dimension: a small dimension that collects the little flags and indicators that don't belong anywhere else.
Every order in a shop arrives with a handful of small flags: was it online or in store, how was it paid, is it a gift, was a coupon used? Each flag has only two or three possible values. A junk dimension combines them into one table with one row per combination, and the fact table stores a single key that points to that combination.
We will look at the problem these flags cause, build a junk dimension from staging data, load the fact with one key, and then analyze sales by the flags.
Question 26 of the Real DWH Question and Answers series on TechBrothersIT.
The interview answer
A junk dimension combines several low-cardinality flags and indicators, like channel, payment method, gift and coupon flags, into one small dimension that stores each combination once. The fact table then holds one junk key instead of many flag columns or many tiny dimensions.
The problem: too many little flags
There are three ways to store these flags, and the first two both have problems.
- Option 1: text flags on every fact row. With billions of rows, that is a lot of repeated text, and the fact table gets wide and messy.
- Option 2: four tiny dimensions. Each has two or three rows, and the fact table gets four extra keys. The model fills up with tables nobody wants to see.
- Option 3: one junk dimension. One small table of flag combinations, and one key on the fact.
The junk dimension is cleaner than both, so let's build one.
Let's create some sample data
We need three tables: stg_orders, where orders arrive with their four flags, an empty junk dimension dim_order_profile, and an empty fact_orders.
DROP TABLE IF EXISTS fact_orders;
DROP TABLE IF EXISTS dim_order_profile;
DROP TABLE IF EXISTS stg_orders;
-- staging: each order arrives with four low-cardinality flags
CREATE TABLE stg_orders (
order_number VARCHAR(10),
amount DECIMAL(10,2),
channel VARCHAR(10), -- Online / Store
payment VARCHAR(10), -- Card / Cash / PayPal
is_gift CHAR(1), -- Y / N
coupon_used CHAR(1) -- Y / N
);
INSERT INTO stg_orders VALUES
('SO-1', 53.00, 'Online', 'Card', 'N', 'N'),
('SO-2', 25.00, 'Store', 'Cash', 'N', 'N'),
('SO-3', 74.00, 'Online', 'Card', 'Y', 'N'),
('SO-4', 30.00, 'Online', 'PayPal', 'N', 'Y'),
('SO-5', 27.00, 'Store', 'Card', 'N', 'N'),
('SO-6', 40.00, 'Online', 'Card', 'N', 'N');
-- the junk dimension: one row per COMBINATION of flags
CREATE TABLE dim_order_profile (
profile_key INT PRIMARY KEY,
channel VARCHAR(10),
payment VARCHAR(10),
is_gift CHAR(1),
coupon_used CHAR(1)
);
CREATE TABLE fact_orders (
order_number VARCHAR(10),
profile_key INT REFERENCES dim_order_profile(profile_key),
amount DECIMAL(10,2)
);Let's look at the staging data.
SELECT * FROM stg_orders ORDER BY 1;| order_number | amount | channel | payment | is_gift | coupon_used |
|---|---|---|---|---|---|
| SO-1 | 53.00 | Online | Card | N | N |
| SO-2 | 25.00 | Store | Cash | N | N |
| SO-3 | 74.00 | Online | Card | Y | N |
| SO-4 | 30.00 | Online | PayPal | N | Y |
| SO-5 | 27.00 | Store | Card | N | N |
| SO-6 | 40.00 | Online | Card | N | N |
Six orders, each with a channel, a payment method, a gift flag and a coupon flag. Notice that SO-1 and SO-6 have exactly the same flags: Online, Card, N, N.
Step 1: Build the junk dimension
Now let's take the distinct combinations of the four flags from staging and give each combination a surrogate key with ROW_NUMBER().
INSERT INTO dim_order_profile
SELECT ROW_NUMBER() OVER (ORDER BY channel, payment,
is_gift, coupon_used),
channel, payment, is_gift, coupon_used
FROM (SELECT DISTINCT channel, payment, is_gift, coupon_used
FROM stg_orders) d;Now let's check the result.
SELECT * FROM dim_order_profile ORDER BY profile_key;| profile_key | channel | payment | is_gift | coupon_used |
|---|---|---|---|---|
| 1 | Online | Card | N | N |
| 2 | Online | Card | Y | N |
| 3 | Online | PayPal | N | Y |
| 4 | Store | Card | N | N |
| 5 | Store | Cash | N | N |
Six orders, but only five different combinations. Online, Card, no gift, no coupon appears twice in staging, but it is stored once, as profile 1. That is the whole idea: a small table of profiles. Even with millions of orders, the number of real combinations stays small.
Step 2: Load the fact with one key
Now let's load the fact table. Each order looks up its combination by matching all four flags, and stores just the profile key.
INSERT INTO fact_orders
SELECT s.order_number, p.profile_key, s.amount
FROM stg_orders s
JOIN dim_order_profile p
ON p.channel = s.channel AND p.payment = s.payment
AND p.is_gift = s.is_gift AND p.coupon_used = s.coupon_used;Now let's check the result.
SELECT * FROM fact_orders ORDER BY order_number;| order_number | profile_key | amount |
|---|---|---|
| SO-1 | 1 | 53.00 |
| SO-2 | 5 | 25.00 |
| SO-3 | 2 | 74.00 |
| SO-4 | 3 | 30.00 |
| SO-5 | 4 | 27.00 |
| SO-6 | 1 | 40.00 |
Four flag columns became one small integer. SO-1 and SO-6 both point to profile 1, because they share the same flags. SO-2 points to profile 5, Store and Cash.
Step 3: Analyze by any flag
Now let's use it like any other dimension. We want sales by payment method and gift flag, so we join the fact to dim_order_profile and group by its columns.
SELECT p.payment, p.is_gift, SUM(f.amount) AS sales
FROM fact_orders f
JOIN dim_order_profile p
ON p.profile_key = f.profile_key
GROUP BY p.payment, p.is_gift
ORDER BY p.payment, p.is_gift;| payment | is_gift | sales |
|---|---|---|
| Card | N | 120.00 |
| Card | Y | 74.00 |
| Cash | N | 25.00 |
| PayPal | N | 30.00 |
Card orders that were not gifts add up to 120.00, Card gift orders to 74.00, Cash to 25.00 and PayPal to 30.00. The flags behave exactly like normal dimension attributes.
Design choices
A few design choices come up in interviews.
- Observed or all combinations? We stored only the combinations we have actually seen. You can also generate every possible combination up front with a
CROSS JOIN. Here that would be 2 × 3 × 2 × 2 = 24 rows. Both work, and pre-generating means the load never has to insert new profiles. - Only small, unrelated flags. Anything with many values, or anything that describes the customer or the product, belongs in that dimension instead.
- A meaningful name. Call it
dim_order_profile, notdim_junk. Nobody wants to explain a table called junk to the business.
Junk vs degenerate vs mini-dimension
These three dimension types are easy to mix up, so here they are side by side.
| Type | What it holds | Has a table? |
|---|---|---|
| Degenerate | A single operational ID (order number) | No, lives on the fact |
| Junk | Small, unrelated flags, combined | Yes, small |
| Mini | Fast-changing attributes split from a big dimension | Yes, small |
A degenerate dimension is a single ID with no table. A junk dimension is a table of small, unrelated flags combined into profiles. A mini-dimension is a small table of fast-changing attributes split off from a big dimension, like a customer's age band and income band, and it gets its own post later in this series.
Follow-up questions interviewers ask
Junk vs degenerate dimension?
Degenerate: a single operational ID on the fact with no table. Junk: a small table of combined, unrelated low-cardinality flags.
Store observed or all combinations?
Either; pre-generating all combinations with a CROSS JOIN means the load never inserts new profiles.
What should not go in a junk dimension?
High-cardinality attributes, or attributes that describe an existing dimension (customer, product).
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 #26 - What is a junk dimension?
-- 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_q26; SET search_path TO dwh_q26;
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS fact_orders;
DROP TABLE IF EXISTS dim_order_profile;
DROP TABLE IF EXISTS stg_orders;
-- staging: each order arrives with four low-cardinality flags
CREATE TABLE stg_orders (
order_number VARCHAR(10),
amount DECIMAL(10,2),
channel VARCHAR(10), -- Online / Store
payment VARCHAR(10), -- Card / Cash / PayPal
is_gift CHAR(1), -- Y / N
coupon_used CHAR(1) -- Y / N
);
INSERT INTO stg_orders VALUES
('SO-1', 53.00, 'Online', 'Card', 'N', 'N'),
('SO-2', 25.00, 'Store', 'Cash', 'N', 'N'),
('SO-3', 74.00, 'Online', 'Card', 'Y', 'N'),
('SO-4', 30.00, 'Online', 'PayPal', 'N', 'Y'),
('SO-5', 27.00, 'Store', 'Card', 'N', 'N'),
('SO-6', 40.00, 'Online', 'Card', 'N', 'N');
-- the junk dimension: one row per COMBINATION of flags
CREATE TABLE dim_order_profile (
profile_key INT PRIMARY KEY,
channel VARCHAR(10),
payment VARCHAR(10),
is_gift CHAR(1),
coupon_used CHAR(1)
);
CREATE TABLE fact_orders (
order_number VARCHAR(10),
profile_key INT REFERENCES dim_order_profile(profile_key),
amount DECIMAL(10,2)
);
-- Step 1: Build the junk dimension from the distinct combinations
INSERT INTO dim_order_profile
SELECT ROW_NUMBER() OVER (ORDER BY channel, payment,
is_gift, coupon_used),
channel, payment, is_gift, coupon_used
FROM (SELECT DISTINCT channel, payment, is_gift, coupon_used
FROM stg_orders) d;
-- check the result
SELECT * FROM dim_order_profile ORDER BY profile_key;
-- Step 2: Load the fact with ONE junk key instead of four flag columns
INSERT INTO fact_orders
SELECT s.order_number, p.profile_key, s.amount
FROM stg_orders s
JOIN dim_order_profile p
ON p.channel = s.channel AND p.payment = s.payment
AND p.is_gift = s.is_gift AND p.coupon_used = s.coupon_used;
-- check the result
SELECT * FROM fact_orders ORDER BY order_number;
-- Step 3: Analyze by any flag through the junk dimension
SELECT p.payment, p.is_gift, SUM(f.amount) AS sales
FROM fact_orders f
JOIN dim_order_profile p
ON p.profile_key = f.profile_key
GROUP BY p.payment, p.is_gift
ORDER BY p.payment, p.is_gift;
Key points
- Combines low-cardinality flags into one small dimension
- One row per combination, with a surrogate key
- The fact stores one key instead of many flags
- Observed combinations, or all of them via CROSS JOIN
- Give it a meaningful name, like order profile
Question 27 — What is a role-playing dimension? What is a role-playing 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.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.