What is a Dimension Table?

How it works

Every filter, group-by and report label comes from a dimension, so good dimensions are designed for people reading reports:

• Wide and descriptive. Dozens of attributes are normal; if an analyst might filter on it, it belongs here.
• Readable. Source codes are decoded into words (Express delivery, not E) and flags into labels.
• Complete. Special members such as “Unknown” and “Not applicable” have their own keys, so every fact row finds a dimension row.
• Hierarchies as columns. Product, category and department sit in one row, giving drill-down paths without extra tables.

Several patterns come up often. A conformed dimension is shared by many fact tables (one dim_customer, one dim_date), so metrics from different processes can be compared. A role-playing dimension is one table used several times in one fact, such as dim_date as order date and ship date. A junk dimension combines low-cardinality flags into one small table. A degenerate dimension is an identifier like order_id kept on the fact with no table of its own.

When attributes change, a slowly changing dimension strategy decides whether to overwrite them (Type 1) or keep history as new versions (Type 2).

Example

Two special members let inner joins keep every fact: one for a missing customer (a data problem to investigate) and one for guest checkouts (no customer by design).

CREATE TABLE dim_customer (customer_key int PRIMARY KEY, customer_id text, name text, tier text);
INSERT INTO dim_customer VALUES
  (-2, 'GUEST', 'Guest checkout', 'Not applicable'),
  (-1, 'UNKNOWN', 'Unknown customer', 'Unknown'),
  (1, 'C1', 'Asha Rao', 'Gold');

CREATE TABLE fact_orders (order_id text, customer_key int, amount numeric(10,2));
INSERT INTO fact_orders VALUES ('O-1', 1, 40.00), ('O-2', -2, 15.00), ('O-3', -1, 22.00);

SELECT c.tier, count(*) AS orders, sum(f.amount) AS revenue
FROM fact_orders f JOIN dim_customer c USING (customer_key)
GROUP BY c.tier ORDER BY c.tier;

      tier      | orders | revenue
----------------+--------+---------
 Gold           |      1 |   40.00
 Not applicable |      1 |   15.00
 Unknown        |      1 |   22.00

Common mistakes

• Leaving NULL attributes, which show up as an unlabelled bucket in every GROUP BY.
• Keeping cryptic source codes that only the source team understands.
• Building a separate customer dimension per fact table, so customer counts never agree between reports.
• Storing measures such as lifetime value in a dimension and then summing them across a fact join.