What is a junk dimension, and when do you collapse fact-table flags into one?

## What is left over after dimension design When you design a fact table you pull the meaningful context out into dimensions — customer, product, date, store, promotion. What is frequently left behind is a residue of small indicators that came off the source transaction and describe the transaction itself: an order channel of web/phone/store, a payment type of card/cash/invoice, a ship mode of standard/express, a gift-wrap flag, a rush flag, a return-eligible flag. Each has a handful of values, none has descriptive attributes of its own, and none belongs to an existing dimension. Two bad answers present themselves. Leave them as columns on the fact table, and the fact row grows a tail of narrow codes that analysts must memorize (`payment_type = 'C'` — card or cash?). Or give each one its own dimension table, and you end up with six two-row tables and six joins in every query, which nobody enjoys reading. ## The junk dimension The third answer is to put them all in one dimension whose rows are the distinct **combinations** of the flag values, with a single surrogate key. The fact table then carries one extra key instead of six columns. ```sql CREATE TABLE dim_order_indicator ( order_indicator_key INTEGER PRIMARY KEY, order_channel VARCHAR(20) NOT NULL, -- Web / Phone / Store payment_type VARCHAR(20) NOT NULL, -- Card / Cash / Invoice ship_mode VARCHAR(20) NOT NULL, -- Standard / Express gift_wrap_flag VARCHAR(3) NOT NULL -- Yes / No ); ``` Two things happen that are worth more than the saved bytes. First, the codes become labels: the dimension is where you decode `C` into `Card`, so every report reads the same words and nobody re-invents a CASE expression. Second, the combinations become browsable — an analyst can look at the dimension and see which combinations actually occur, and correlated filters ("express, gift-wrapped, paid by card") land on one join. ## Two ways to populate it **Full cross-product.** If the columns are bounded and small, enumerate every combination up front. Three columns with 3, 3 and 2 values plus a yes/no flag give 3 x 3 x 2 x 2 = 36 rows; loading facts is then a straight lookup that can never miss. The cost is rows that never occur, which is irrelevant at this size. **Observed combinations.** If the cross-product is large but the real data is sparse — say 4,000 theoretical combinations of which 200 ever appear — build it incrementally: during each load, find combinations not yet present, insert them with new surrogate keys, then look up. This keeps the dimension honest about the business but adds a step that must be deterministic, so that the same combination always resolves to the same key. A sensible instinct: keep the row count in the hundreds or low thousands. A junk dimension is meant to be small enough that reading the whole thing is trivial. ## What belongs, and what does not Belongs: low cardinality (a few values each), bounded and slowly changing value sets, descriptive with no attributes of their own, and useful as filters or groupings. Does not belong: high-cardinality identifiers (those are degenerate dimensions and stay in the fact row), continuous values such as amounts, weights or timestamps (either a measure or a banded attribute), anything with its own descriptive attributes (it deserves a real dimension), and anything that already exists in a proper dimension (do not duplicate the customer's segment here). It is also fine to have more than one junk dimension on a fact when the flags cluster into unrelated groups — for example one for order-handling indicators and one for audit/quality indicators — if merging them would multiply the combinations without helping anyone. ## Trade-offs to name in an interview - **Filtering.** A filter on one flag now requires the join. That is one join instead of none, but the dimension is tiny. - **Adding a flag later.** A new column changes the combination set, so keys must be reissued or the dimension rebuilt and the facts re-keyed. Adding flags is not free, which argues for enumerating a bounded cross-product up front where you can. - **Cardinality discipline.** The pattern only works while the combination count stays small; one careless high-cardinality column destroys it. - **Naming.** "Junk" is the textbook term but a poor name to ship; teams usually name the table for its subject, such as `dim_order_indicator`. ## The confusion to avoid A junk dimension is not a degenerate dimension. Degenerate is one high-cardinality identifier with no table at all; junk is several low-cardinality flags gathered into one table. They frequently appear on the same fact row, and interviewers use the pair to check that the vocabulary is genuinely understood rather than memorized.