How do you set a house standard for dimension denormalization — star or snowflake — across dozens of marts?
answer
- what do consumers see by default?
- exceptions must be evaluable, not vague
- which layer is allowed to normalize?
- how is the rule checked automatically?
- what happens if teams route around it?
basics
~20 sMake flat star dimensions the published default, allow normalization only in the layer beneath, and write the exceptions as testable criteria rather than taste. Enforce through review and automated model tests, and measure adoption instead of assuming compliance.
solid answer
~50 sSet a default plus a short exception list, not a philosophy. The default: everything published to consumers is a wide dimension at one row per key, so there is one join path to any attribute in any mart. The exceptions are stated as **criteria a team can evaluate** — a dimension above a size threshold with a wide repeated attribute block, or a hierarchy level mastered upstream that multiple marts must share — and normalization for those lives in the integration layer, with a flattened dimension still published. Then make it real: a naming convention that distinguishes published from internal tables, uniqueness tests on every published dimension key, and review of new marts against the standard. Finally, watch for the standard failing quietly — teams building shadow marts because the process to add an attribute is slow is a signal the standard costs more than it is worth, not that the teams are undisciplined.
go deeper
Know that platforms usually publish one wide dimension table per dimension so every mart looks the same to an analyst, whatever happens in the layers beneath.
Be able to state the default and one or two concrete exception criteria, and to explain why consistency across marts matters more than optimizing each one.
Show how the rule is enforced mechanically — naming that exposes layers, a key-uniqueness test on every published dimension, review at mart creation rather than after an incident.
Own the standard as a system with adoption dynamics: exceptions written as evaluable criteria, migration handled incrementally, and shadow marts read as a signal that the change process is too slow.
## Why this is a standard at all On a single mart, star versus snowflake is a design taste with modest consequences. Across dozens of marts consumed by hundreds of analysts, inconsistency is the actual cost: an analyst who learns that product attributes are one join away in the sales mart and four joins away in the supply-chain mart pays that tax on every query, and every wrongly-joined level becomes a metric that disagrees with another team's. The standard exists to make the shape predictable, not to make it optimal in each case. ## Structure of a workable standard ### One default, stated as a contract "Every dimension published to the consumption layer is a single table at one row per dimension key, carrying all attributes users group and filter by." That is short enough to remember and precise enough to test. It says nothing about layers beneath it, which is deliberate — the standard governs what consumers see. ### Exceptions as criteria, not as permission A standard that says "snowflake when appropriate" is not a standard. Write the exceptions as things a team can evaluate without a meeting: - the dimension exceeds a stated row count *and* carries a wide attribute block with few distinct combinations; - a hierarchy level is mastered by an upstream system and shared by multiple marts; - a level's change cadence or history requirement genuinely differs from the dimension's. Each exception normalizes in the integration layer and still publishes a flat dimension. In other words the exception changes where data is stored and governed, not what consumers query. ### Say what the standard does not cover Name the adjacent decisions the standard is silent on — how history is versioned, how models are tested, what the semantic layer exposes — so teams do not read a shape rule as an answer to a different question. A standard that overreaches gets ignored wholesale. ## Making it stick A document nobody can violate accidentally beats a document everybody agrees with. - **Naming makes the layer visible.** If published dimensions carry a distinct prefix from internal reference tables, a consumer querying an internal table is immediately obvious in query logs and in review. - **Automated tests carry the contract.** The published-dimension rule reduces to a uniqueness assertion on the dimension key, which every model pipeline can run on every build. A rule that is checked mechanically is a rule; one that lives only in a wiki is a preference. - **Review at creation, not at incident time.** New marts get read against the standard when they are proposed, which is when changing them is cheap. ```sql -- the published-dimension contract, expressed as a test SELECT dimension_key, COUNT(*) AS n FROM published_dimension GROUP BY dimension_key HAVING COUNT(*) > 1; -- must return no rows ``` ## The trade-offs you own as the person setting it **Consistency versus local optimality.** A uniform flat shape is occasionally wrong for a specific dimension. Accept that: the cost of a slightly suboptimal dimension is bounded, and the cost of every mart looking different is not. **Strictness versus shadow systems.** If getting an attribute added to a published dimension takes three weeks, teams build their own copies, and you now have divergence *plus* a standard. The throughput of the change process is part of the standard's design, not an operational detail. **Who writes the SQL.** If most consumption is hand-written SQL, join count and discoverability dominate and the flat default is unambiguous. If nearly all consumption flows through a governed semantic layer that generates joins, the analyst-usability argument weakens and the case for a normalized physical layer strengthens. Know which world you are in and revisit the standard if it changes. **Migration cost of the standard itself.** Applying a new shape rule retroactively to dozens of existing marts is a program, not a policy change. It is usually right to apply the standard to new work, publish flattened views over the worst existing offenders, and let genuine migrations follow demand rather than mandating a sweep. ## How to answer this in an interview Give a default, give evaluable exceptions, give an enforcement mechanism, and then give the failure mode you would watch for — teams routing around the standard. What distinguishes a principal-level answer is treating the standard as a system with adoption dynamics, not as a correct opinion to be published once.
- How would you apply a new shape standard to marts that already exist?Apply it to new work immediately and publish flattened views over the worst existing offenders, so consumers get the uniform interface without a rewrite. Then let real migrations follow demand rather than mandating a sweep. A retroactive program across dozens of marts costs more than the inconsistency it removes.
- Does a governed semantic layer that generates all joins change the calculation?It weakens the strongest argument for flat dimensions, since analysts stop hand-writing join paths and the tool cannot pick the wrong level. Physical normalization becomes more defensible. But it holds only while every consumer really goes through that layer — direct SQL access to the warehouse puts the original argument straight back.
- What signal would tell you the standard is doing more harm than good?Teams building shadow copies of published dimensions. That is almost never indiscipline; it means the process to extend a published dimension is slower than the work it blocks. The fix is to shorten that path, because the alternative is divergence plus an ignored standard.
saying these in an interview costs you the question
- Writes a standard that says snowflake when appropriate
- Enforces shape only through documentation and goodwill
- Mandates a retroactive rewrite of every existing mart
- Ignores whether consumers hand-write SQL or use a generating layer
- Treats teams routing around the standard as a discipline problem