Dimension Design: Surrogate Keys, Conformed and Junk Dimensions

Fact tables get the attention. Dimensions do the work. They're what users filter, group and read, and a well-designed dimension makes the warehouse feel effortless while a poor one makes every report a struggle. We'll cover the four patterns you'll use on nearly every project (surrogate keys, conformed dimensions, junk dimensions and degenerate dimensions), plus the small "unknown" row that has saved more of our loads than any of them.

Surrogate keys

Every source system has its own identifier for a customer, product or store, the natural key (also called the business key), something like C-4471 or an SKU. It's tempting to use it as the dimension's primary key. Don't. The warehouse assigns its own meaningless integer, the surrogate key, and the fact table joins on that. Four reasons, and you'll meet all four on a real project.

• Multiple sources collide. The POS and the web shop both have a customer C-4471, and they're different people. A surrogate key gives each its own row while keeping both natural keys as columns.
• History needs several rows per natural key. When Sana moves city and you keep both versions (Lesson 10), the natural key is no longer unique. The surrogate key is.
• Sources change. An ERP upgrade reformats every product code. Only the natural-key column changes. Facts keep pointing at the same surrogate keys.
• Performance. A 4-byte integer joins faster and compresses better than a 20-character code, across hundreds of millions of fact rows.

The date dimension is the one exception. Its key is a readable integer like 20250814, because partition pruning and human debugging both benefit (Lesson 11).

The unknown row

Every dimension should contain a row with surrogate key 0 (or -1) meaning "unknown / not applicable". When a fact arrives with a customer the warehouse has never seen, the load assigns key 0 instead of dropping the row or storing NULL. Totals stay correct, inner joins still work, and a report can show "Unknown customer: PKR 1.2 million", which is itself useful for fixing the source. Skip this row and your first month's report will be short by exactly the sales your loyalty app failed to sync.

Conformed dimensions

A conformed dimension is one dimension table (or identical copies with identical keys and labels) shared by several fact tables. If sales, returns, stock and purchasing all use the same dim_product, a user can put sales amount and return amount on the same chart by category and the numbers line up. If each mart had its own product table with slightly different category names, that chart would be impossible or wrong.

Conformed dimensions are the "bus" in Kimball's bus architecture. Build them once, early, and every new fact table plugs into them. The bus matrix in Lesson 17 is the planning tool. The hardest ones in practice are customer (spread across many systems) and product (where marketing, finance and supply chain each have their own hierarchy). Agreeing on them is a business negotiation, and the warehouse team has to lead it, because nobody else will.

Junk dimensions

Transactions arrive with small flags and codes. Payment type, is-promotional, is-gift-wrapped, sales channel. Each has two to ten values. Give each its own dimension and you get a swarm of tiny tables; put them as text on the fact and it bloats. A junk dimension combines them into one table holding every combination that actually occurs. The fact carries one small flags_key. With 4 payment types, 2 promo values, 2 gift values and 2 channels, the junk dimension has at most 32 rows, and users can still filter by any flag.

Degenerate dimensions

A receipt number, invoice number or ticket ID identifies a transaction but has no descriptive attributes of its own (the date, store and customer are already dimensions). Building dim_receipt would give you a table with a key and a number and nothing else. Keep the number directly on the fact table instead, as a degenerate dimension. It groups line items into baskets, supports drill-through to the source, and forms part of the primary key.

Other dimension patterns you will meet

Pattern	What it is	Example
Role-playing dimension	One physical dimension used several times in one fact under different roles	dim_date as order date, ship date and delivery date
Outrigger	A small dimension hanging off another dimension	dim_customer.first_purchase_date_key to dim_date
Mini-dimension	Fast-changing attributes split from a big dimension	customer_demographics (age band, income band) separate from dim_customer
Bridge table	Handles many-to-many between fact and dimension	one bank account with several joint holders

dim_store with all the pieces

CREATE TABLE dw.dim_store (
  store_key     INT  GENERATED ALWAYS AS IDENTITY PRIMARY KEY, -- surrogate
  store_code    TEXT NOT NULL,          -- natural key from POS, e.g. LHR-DHA
  erp_branch_id TEXT,                   -- natural key from ERP, e.g. 0017
  store_name    TEXT NOT NULL,
  city          TEXT NOT NULL,
  region        TEXT NOT NULL,
  format        TEXT NOT NULL,          -- Mall / Street / Outlet
  open_date_key INT REFERENCES dw.dim_date(date_key),   -- outrigger
  manager_name  TEXT,
  is_active     BOOLEAN NOT NULL DEFAULT TRUE
);

-- The unknown row, inserted first with an explicit key
INSERT INTO dw.dim_store (store_key, store_code, store_name, city, region, format)
OVERRIDING SYSTEM VALUE
VALUES (0, 'UNKNOWN', 'Unknown store', 'Unknown', 'Unknown', 'Unknown');

-- Junk dimension for sale flags, populated from combinations actually seen
CREATE TABLE dw.dim_sale_flags (
  flags_key     INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  payment_type  TEXT NOT NULL,
  is_promo      BOOLEAN NOT NULL,
  is_gift_wrap  BOOLEAN NOT NULL,
  channel       TEXT NOT NULL,
  UNIQUE (payment_type, is_promo, is_gift_wrap, channel)
);

-- Lookup during the fact load: resolve natural key to surrogate, default to 0
SELECT COALESCE(st.store_key, 0) AS store_key
FROM stg.pos_sales s
LEFT JOIN dw.dim_store st ON st.store_code = TRIM(s.shop);

Two customer tables, one bank: a Karachi bank's fraud and marketing teams each built their own customer dimension. Fraud used the core banking customer ID. Marketing used the CRM contact ID with different segment names. Nobody could answer "what's the fraud rate in the Premium segment?" The warehouse team spent two months building one conformed dim_customer with both natural keys, one agreed segment list and a surrogate key, then repointed both fact tables at it. The question took ten seconds to answer once the dimension existed. Two months of meetings for a ten-second query is about the normal ratio.

Points worth keeping

• Join facts to dimensions on warehouse-made integer surrogate keys. Keep natural keys as ordinary columns.
• Every dimension has an unknown row (key 0) so unmatched facts are kept and visible.
• Conformed dimensions shared by all facts are the bus. They're what let you compare across processes.
• Small flags go in one junk dimension. Transaction numbers stay on the fact as degenerate dimensions.

Try it yourself

1. A product code is reformatted from "SKU0042" to "PK-000042" after an ERP upgrade. Describe what changes in the warehouse if facts join on the natural key versus the surrogate key.
2. List the flags on a JazzCash transaction (channel, is_reversal, is_international, initiator_type) and design the junk dimension. How many rows could it have at most?
3. Name two facts in a school warehouse that should share a conformed dim_student, and one question that only works because they share it.