A junk dimension grew from 200 rows to 3 million after a column was added — what went wrong?

## Why the size exploded at all The grain of a junk dimension is one row per distinct combination of its columns, so its row count is bounded by the product of the columns' cardinalities. Four columns with 3, 3, 2 and 2 values cap the table at 36 rows. Add a fifth column with 20,000 distinct values and the cap becomes 720,000. The pattern's entire economy depends on every member being low cardinality, and it has no defence against a column that is not. ## The four causes, in the order you should check them **1. A high-cardinality identifier was admitted.** Someone had a leftover column — `order_number`, `session_id`, `external_reference` — and swept it into the junk dimension because it did not belong to any other dimension. Identifiers with no descriptive attributes belong in the fact row as degenerate dimensions, precisely because a table for them would have one row per document. **2. A continuous value was admitted.** A timestamp, an amount, a weight, a raw score. Each distinct value creates its own combination. If the value is a measure, it belongs on the fact table; if it is genuinely a grouping attribute, it must be banded into a handful of ranges first. **3. The values are dirty.** This one is nasty because the column looks legitimate. A flag whose source sends `Y`, `y`, `YES`, `Yes` and `Y ` contributes five values instead of two, and the effect multiplies against every other column. A null that is sometimes an empty string is the same problem. A mixed-locale or unit-suffixed code set likewise. **4. Key assignment is non-deterministic.** The load inserts a new surrogate row for a combination it fails to match — because the lookup compares differently-typed or differently-trimmed values, because it runs before deduplication, or because concurrent load tasks each insert the same new combination. Here the tell is different: not high cardinality per column, but duplicate rows with identical combination values. ## Diagnosing it Two queries settle it in a minute. First, per-column distinct counts, which immediately identifies whether one column dominates: ```sql SELECT COUNT(*) AS rows, COUNT(DISTINCT order_channel) AS n_channel, COUNT(DISTINCT payment_type) AS n_payment, COUNT(DISTINCT ship_mode) AS n_ship, COUNT(DISTINCT gift_wrap_flag) AS n_gift, COUNT(DISTINCT new_column) AS n_new FROM dim_order_indicator; ``` If `n_new` is in the thousands, cause 1, 2 or 3 is confirmed and the next step is to look at sample values to tell them apart. Second, a grain-uniqueness test on the combination columns, which catches cause 4: ```sql SELECT order_channel, payment_type, ship_mode, gift_wrap_flag, COUNT(*) AS dup FROM dim_order_indicator GROUP BY order_channel, payment_type, ship_mode, gift_wrap_flag HAVING COUNT(*) > 1; ``` Any rows returned mean the same combination was issued more than one surrogate key, which also means facts that should share a key are split across several, and any report grouping by the junk attributes will look right while any report counting distinct keys will not. ## Fixing it Re-home the offending column first, according to what it actually is: - An identifier goes into the fact row as a degenerate dimension. - A measure goes into the fact row as a measure. - A continuous grouping attribute gets banded, with the bands agreed with the business, and then it may come back in. - An attribute with its own descriptive attributes deserves a real dimension of its own. - A dirty code set gets normalized at the point of load — trim, case-fold, map synonyms to a canonical value — and a test that fails the load if an unexpected value appears. Then rebuild. If the combination space is bounded, enumerate the cross-product and reissue keys; if it is sparse, rebuild from the combinations actually observed. Either way the facts must be re-keyed, which is the expensive part and the reason this is a real incident rather than a tidy-up. Do it as a rebuild of the dimension plus a re-derivation of the key on affected fact rows, and reconcile counts before and after so you can prove nothing was lost. ## Preventing the next one Add a test that fails the build when the dimension exceeds an agreed row ceiling, and one that fails when the combination columns are not unique. Both are cheap, both catch the whole class of failure at load time rather than when an analyst notices a dashboard has gone slow, and both express the design intent — that this table is small by construction — as something the pipeline enforces rather than something a reviewer must remember. ## What an interviewer wants to hear That you know the size is a product of cardinalities, that you would look at per-column distinct counts before theorizing, that you can name the four causes and distinguish them by evidence, and that your fix includes re-keying the facts and adding a guard rather than just deleting rows.