A junk dimension is a small dimension table that groups loose low-cardinality flags, indicators, and status codes. It keeps the fact table narrow and avoids a swarm of tiny dimensions in a star schema.
A junk dimension is a small dimension table that groups loose low-cardinality attributes: yes/no flags, small status codes, channel indicators, type labels, and other bits of context that do not deserve their own dimension.
The word junk does not mean the data is bad. It means these fields are leftovers from the modelling process: useful for filtering and grouping, but too small and scattered to become separate dimensions.
In a star schema, the fact table then stores one surrogate key to the junk dimension instead of carrying many separate flag columns or joining to many tiny lookup tables.
A junk dimension solves two modelling problems at once.
It keeps the fact table narrow
A fact table should mainly contain keys and measures. If every order row carries ten descriptive flags, every query drags those columns around. One key to a small profile table is cleaner.
It avoids a swarm of mini-dimensions
Giving every flag its own dimension table creates clutter. The model becomes harder to read, relationship diagrams become noisy, and users have to navigate many tiny tables.
Kimball's guidance describes this as a way to combine low-cardinality flags and attributes into one dimension, often called a transaction profile dimension.
A junk dimension contains one row for each allowed or observed combination of the selected attributes. Each row gets a surrogate key. The fact table stores that key.
There are two common approaches.
Generate all possible combinations
If there are only a few attributes with only a few values, build every combination. Three attributes with 2, 3, and 3 values produce 18 rows. That is small and easy to maintain.
Load only combinations that occur
If the combination count can explode, create rows only for combinations found in the source data. Ten yes/no flags create 1,024 possible combinations, but perhaps only 40 appear in reality. In that case, the load process must detect new combinations and add them before facts are loaded.
The second approach is common, but it needs governance. If a new source value arrives without a description, the ETL should flag it rather than silently creating confusing rows.
Suppose each online order has three small descriptors:
gift order: yes or no
payment method: card, bank transfer, or cash on delivery
channel: web, phone, or store
You can create an Order Profile dimension with a surrogate key and those three attributes. Example rows:
1 = not a gift, card, web
2 = gift, card, web
3 = not a gift, bank transfer, phone
The sales fact table stores OrderProfileKey. A report can still filter revenue by gift orders, payment method, or channel, but the fact table stays lean.
Both patterns clean up a fact table, but they solve different problems.
A degenerate dimension is an identifier that stays in the fact table without a separate dimension table, usually because it has no extra attributes worth modelling. Order number, invoice number, and ticket number are common examples.
A junk dimension is the opposite shape: several low-cardinality attributes are moved out of the fact table into a small dimension because they do have descriptive value together.
Rule of thumb: nearly unique operational identifiers often stay as degenerate dimensions. Small flags and statuses often belong in a junk dimension.
Do not hide important dimensions
If an attribute has rich meaning, hierarchy, ownership, or many values, it probably deserves its own dimension. A customer status lifecycle may be more than junk.
Watch combination growth
Adding too many flags can make the dimension larger and less understandable. Keep the group focused.
Name it for users
Business users do not need to see a table called JunkDimension. Names such as Order Profile, Transaction Profile, or Admission Info are clearer.
Handle unknowns deliberately
Include rows for unknown, not applicable, or missing values where needed. Silent nulls in a fact key create reporting gaps.
Do not over-normalise
The purpose is to simplify the star schema. If the junk dimension turns into a network of lookup tables, it has missed the point.
A bridge table is an intermediate table that resolves a many-to-many relationship in a star schema. It stores the valid pairs between two ta...
Read definitionA business key is the identifier a business already uses to recognise something real, like an invoice number, VAT number, EAN or employee nu...
Read definitionA candidate key is any column or set of columns that can uniquely identify every row in a table, with no redundant column in it. A table can...
Read definitionCardinality in Power BI describes how rows in two tables relate: one-to-many, many-to-one, one-to-one, or many-to-many. It controls relation...
Read definitionA common table expression, or CTE, is a named temporary result set that exists only for the single SQL statement that defines it. You write ...
Read definition