Dimension Table Techniques: Surrogate Keys, Role-Playing, Junk and More

Dimension tables hold the descriptive context of the facts: the names, categories, regions, segments and flags people filter and group by. Good dimensions are wide (dozens of attributes), denormalised (hierarchies flattened into one table), readable ("Express shipping" rather than Y) and keyed by a surrogate key the warehouse controls. Most of the craft of dimensional modeling is in the dimensions, because that's where business meaning lives.

Keys: surrogate, natural, durable

Facts store the surrogate key of the dimension row that was valid when the event happened. That single rule is what lets a Type 2 slowly changing dimension keep history: an order placed in 2025 points forever at the 2025 version of the customer.

Interviewers often ask "why not just use the source id?" Three answers: history needs more than one row per entity, several sources may use different or colliding ids, and source systems sometimes reuse or change ids.

The date dimension

Every warehouse has a calendar date dimension: one row per day for decades, built once, with attributes like day of week, week number, month name, quarter, fiscal period, holiday flag and weekday or weekend. Reports group by these attributes instead of computing calendars in SQL, and fiscal calendars or regional holidays become simple joins. The key is often a smart integer (20261024) because dates are stable and it makes partitioning easy. A separate time-of-day dimension (minute or hour grain) handles intraday analysis without exploding the date table.

Keep the precise event timestamp on the fact as well when you need it for ordering or durations; the date key is for grouping.

Role-playing dimensions

One physical dimension can be referenced several times by the same fact under different roles. An order has an order date, a ship date and a delivery date, all pointing at dim_date. A trip has a pickup location and a drop-off location, both pointing at dim_location. Expose each role as a view with renamed columns so queries and BI tools stay unambiguous.

create view dim_ship_date as
select date_key as ship_date_key, full_date as ship_date, month_name as ship_month, fiscal_quarter as ship_fiscal_quarter
from dim_date;

Degenerate dimensions

Transaction identifiers such as an order number, invoice number or ticket id have no descriptive attributes of their own, since everything about the order is already in other dimensions. Rather than create an empty dimension, keep the identifier directly on the fact table. It is still useful: it groups lines into an order and lets users trace a row back to the source.

Junk dimensions

Transactions often carry a handful of low-cardinality flags and codes: gift wrap yes or no, express shipping, order channel, payment type. Putting each on the fact as a column clutters it; giving each its own dimension adds many tiny joins. A junk dimension combines them into one small table with a row for each combination that actually occurs, and the fact stores one key.

Two flags with three values each give at most a few dozen rows, so the dimension stays tiny no matter how many orders exist.

Flags, indicators and nulls in dimensions

• Store flags as readable values ("Subscribed" / "Not subscribed") rather than Y/N or 1/0, so reports read correctly without decoding.
• Replace null attributes with explicit labels ("Unknown", "Not provided") so grouping doesn't produce a confusing blank bucket and filters behave predictably.
• Every dimension gets special rows for "Unknown" and "Not applicable" (often keys -1 and -2) that facts can point at when the real member is missing.

Snowflaking and outriggers

Snowflaking normalises a dimension's hierarchy into separate tables (product → brand → category). It saves a little space and adds joins, so the default is to flatten. An outrigger is a narrower, deliberate exception: a dimension that references another dimension, such as a customer dimension pointing at a date dimension for first_purchase_date. Use outriggers sparingly; each one is a join users must understand.

Mini-dimensions for fast-changing attributes

Some attributes change too often for Type 2 history on the main dimension: a customer's age band, income band, loyalty tier or credit score band. Tracking each change as a new row would multiply a 20-million-row customer dimension many times over. A mini-dimension moves those volatile attributes into their own small table of banded combinations, referenced directly from the fact. The fact then records both who the customer is and what profile they had at the time of each event. This is SCD Type 4.

-- Mini-dimension: one row per combination of bands, not per customer
create table dim_customer_profile (
  profile_key    int primary key,
  age_band       varchar(10),   -- '25-34'
  income_band    varchar(15),   -- '50k-75k'
  loyalty_tier   varchar(10)    -- 'Gold'
);
-- The fact carries both keys
-- fact_order_line(customer_key, customer_profile_key, ...)

Audit dimension

An audit dimension describes how each fact row was produced: the pipeline run id, source system, load timestamp, code version, and data quality status. Attaching an audit_key to every fact lets you find all rows from a bad run, exclude rows that failed a quality check, and answer "where did this number come from?" without digging through logs.

Late-arriving data

• Late-arriving dimension (early-arriving fact): a fact references a customer the warehouse hasn't seen yet. Insert an inferred member, a placeholder dimension row with the natural key and "Unknown" attributes, so the fact loads with a valid key. When the real customer arrives, update the placeholder in place (Type 1), since it was never a real historical version.
• Late-arriving fact: an event from last week arrives today. Look up the dimension version valid at the event time, not the current one, and write it to last week's partition. Loads must be idempotent so a re-sent file doesn't duplicate rows.

Less common techniques worth recognising