Loading lesson...
Low-cardinality flags and transaction IDs need homes; junk and degenerate dims provide them
Low-cardinality flags and transaction IDs need homes; junk and degenerate dims provide them
Topics covered: Orphan Attributes with No Natural Home, Building a Junk Dimension, Degenerate Dimensions in the Fact Table, When Junk Dimensions Grow, Defending Your Junk/Degenerate Design
The Problem Set up the scenario: 'The fact table has is_gift, is_prime, is_expedited, payment_method, and invoice_number. Where do these go? Leaving all five on the fact adds columns that every scan reads even when unused. Creating a dimension for each one is dimension explosion. The answer: consolidate the flags into a junk dimension, keep invoice_number as a degenerate dimension.' Deliver this in 15 seconds. It shows you know both patterns. What They're Really Testing These patterns are rarely
When the interviewer points to five boolean flags on your fact table and asks 'where do these go?', they are testing whether you know the consolidation pattern. Saying 'leave them on the fact table' is a weak answer. Saying 'create five separate dimensions' is worse. The strong answer names the junk dimension pattern and designs one in 30 seconds. The Schema You Should Be Able to Write in 60 Seconds 32 rows How You Load It: The Detail Interviewers Probe Pre-population is the strong answer. It ma
Your degenerate dimension answer: 'A degenerate dimension stays in the fact table. No separate table. No surrogate key. invoice_number is unique per row with no additional attributes worth storing. Creating dim_invoice with one column and a surrogate key doubles storage for zero analytical benefit.' The key phrase is 'zero analytical benefit.' That is the Kimball justification for degeneration. The Decision: Which Attributes Stay on the Fact Table State the rule: 'If the attribute is unique per
The follow-up the interviewer uses to probe depth: 'What happens when the business adds three more flags next quarter?' This tests whether you have thought about the combinatorial growth of junk dimensions and know when to split them. The Math the Interviewer Will Make You Do When to Split: The Threshold You Should Name The Follow-Up Trap The interview-winning answer for explosion: 'Junk dimensions work when the product of cardinalities stays under about 1,000. Beyond that, I would split into mu
The interview signal for junk and degenerate dimensions is not drawing the schema. It is explaining why this design is correct. The interviewer will challenge your choices. Having the rationale ready is what separates pattern-appliers from pattern-defenders. Defense Playbook Vocabulary That Signals Seniority The Bridge Move Red Flag Phrases The closing move that ties the whole fact table together: 'So the final schema is: dimension FKs (customer_sk, product_sk, date_sk), one junk dim FK (order_f