How do you choose between a bridge table, an exploded fact grain and a primary-value column for a multi-valued dimension?

## The three options on the table A multi-valued dimension — several diagnoses per visit, several reps per deal, several segments per customer — admits three structural answers, and a mature model usually uses more than one of them in different layers. **Primary-value column.** Pick one member and store its key on the fact: `primary_diagnosis_key`, `owning_rep_key`. The fact grain is untouched, every query is trivial, and every non-primary member is gone. This is not automatically wrong — many businesses genuinely have a primary owner, and the operational system already records which one. It becomes wrong when the discarded members are the analysis. **Exploded grain.** Store one fact row per event-member pair. Querying is trivial too; there is no bridge to understand and no fan-out to explain. The price is that the original measure is no longer additive — either it repeats on every row and must never be summed, or it has been pre-allocated and the true event total is no longer directly available. You have also changed what a row *means*, which is a grain change with all the consequences that carries. **Bridge.** The fact keeps its grain and its group key; a bridge enumerates the members with weights. Totals are correct without the bridge, member analysis is correct through it, and both allocated and impact shapes are available. The price is fan-out on every bridged join and a model that some consumers will handle badly. ## The questions that decide it **Does anyone need to aggregate a measure by the multi-valued attribute?** This is the first cut. "Revenue by sales rep" needs the relationship in the model; "list the reps who touched this deal" only needs the detail to be retrievable, which a detail table or the source system already provides. Do not build a bridge for a lookup. **Must the event total stay trivially correct?** A finance mart whose revenue must reconcile to the general ledger cannot afford a shape where the naive `SUM` is wrong. That argues hard for keeping the atomic fact unexploded, with the bridge as the optional path. **Is the multiplicity bounded and small?** Two to five members per event behaves very differently from a hundred. High and unbounded multiplicity makes exploded grain expensive and makes fan-out surprising; it also usually signals that the relationship deserves its own fact table rather than being an attribute of this one. **Who are the consumers?** A model consumed only by SQL analysts who read the documentation can carry a bridge comfortably. A model consumed by self-service dashboards built by dozens of people, in tools that model many-to-many awkwardly, may be better served by an exploded, clearly-labelled mart on top of an unexploded atomic layer. **Is there an agreed allocation rule?** This is the political question hiding inside the technical one. Allocation makes revenue-by-rep add up, and it also decides who gets credit. If there is no owner for that rule, an equal split is the only defensible default, and the measure must be named so it announces itself as allocated. ## The layered answer These are not mutually exclusive, and the strongest answer in an interview is that they live at different layers. Keep the **atomic fact** at its natural grain, with the group key and the bridge — this is the layer that must be right, reconcilable and stable. Build **purpose-shaped marts** above it: a rep-performance mart may explode and pre-allocate, because that is exactly its subject and its measure is defined as allocated revenue; a finance mart may never touch the bridge at all. Publishing the exploded shape as a derived, named artefact rather than as the truth means the explosion is a documented decision instead of a lost original. ## Governance of what gets published Wherever the bridge is exposed, three rules keep consumers safe. Name the measures distinctly — `revenue_allocated` and `revenue_impact`, never two things called `revenue`. Document which measures are additive across which dimensions, because a bridged measure is additive over one dimension and not another, and that is not discoverable from a column name. And define the measure once in whatever semantic layer you have, so the weight multiplication is not something every analyst must remember to type. Most bridge incidents are naming and documentation failures, not modelling failures. ## Reuse across marts One more consideration is specific to a lead's remit: if three teams each need revenue by rep, they should share one bridge and one allocation rule, not three. A multi-valued relationship modelled independently in several marts produces several different revenue-by-rep numbers, all defensible, none reconcilable — the same failure mode as unconformed dimensions, arriving through a different door. Treat a bridge and its weighting rule as a shared, owned asset with a stated definition, and the argument about whose number is right stops happening. ## What interviewers listen for That you do not present the bridge as automatically correct, that you ask what has to remain summable and who consumes the model before choosing, that you separate the layer that must be reconcilable from the layer shaped for a question, and that you treat the allocation rule as something owned by the business rather than invented by the warehouse.