Loading lesson...
Shared dimensions are what make a data warehouse a warehouse instead of a collection of tables
Shared dimensions are what make a data warehouse a warehouse instead of a collection of tables
Topics covered: When Two Fact Tables Need the Same Dimension, Designing a Conformed Dimension, Role-Playing Dimensions, Outrigger and Mini-Dimensions, Cross-Functional Consistency
What They're Really Testing The Problem: Non-Conformed Dimensions Cite this in your answer: 'At a ride-sharing company, operations defined active driver as completed-a-ride-in-30-days. Finance defined it as has-a-valid-payment-method. The CEO asked how many active drivers we have and got two different numbers. Neither was wrong. Both were right within their own definition. The warehouse had no way to produce one answer.' This is the problem conformed dimensions solve. Tell this story in 15 secon
The interviewer will ask you to design a dimension that serves multiple fact tables. The trap: candidates over-stuff the dimension with domain-specific attributes that create unnecessary coupling. The signal they are looking for is whether you know what belongs in the conformed core versus what belongs in an extension. The Date Dimension: The Example Every Interviewer Expects Your conformance answer: 'Every fact table references the same dim_date via date_sk. When I GROUP BY dim_date.quarter, I
When the interviewer gives you a fact table with three date columns (order_date, ship_date, deliver_date), they are testing whether you create three dimension tables or one. The correct answer is one dim_date referenced three times. This is a role-playing dimension, and naming it unprompted shows Kimball-level fluency. The Schema the Interviewer Expects Your role-playing answer: 'Three foreign keys, all pointing to the same dim_date. Each one plays a different role: when ordered, when shipped, w
The interviewer will push back on your dimension design: 'This dimension has 50 columns and half of them change weekly. How do you handle that?' This tests whether you know the mini-dimension pattern. Candidates who say 'just apply Type 2 to everything' reveal they have never calculated the storage cost of that approach. The Problem the Interviewer Describes State the problem with numbers: 'A dim_customer with 50 columns, 12 of which change weekly. With Type 2 on all 12, that is 10 million custo
Technical conformance is necessary but not sufficient. Conformed dimensions fail in practice not because the schema is wrong, but because governance breaks down. This section covers the organizational patterns that make conformance sustainable, and the vocabulary that tells the interviewer you have dealt with this in production. The Bridge Move: From Schema to Governance Red Flag Phrases Vocabulary That Signals Seniority