Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. What is a bridge table?

Data modeling · Keys & Relationships

What is a bridge table?

Mediummodel-20
bridge tablemany-to-manyweightingdimensional

Question

What is a bridge table in dimensional modeling?

Solution

A bridge table resolves many-to-many relationships between a fact and a multi-valued dimension (or between two dimensions) without destroying the fact grain.

fct_patient_visit          dim_diagnosis
        \                     /
         \                   /
          bridge_visit_diagnosis
            visit_sk
            diagnosis_sk
            weight          -- optional allocation

Classic use cases

  • Order ↔ multiple promotions / coupons
  • Patient visit ↔ multiple diagnoses
  • Account ↔ multiple holders
  • Employee ↔ multiple skill tags

Why not put arrays on the fact?

Some warehouses allow array columns, but BI tools and simple SQL often struggle. Bridges stay relational and explicit.

Weighted bridge

If one order uses two promotions equally, weight = 0.5 each so attributed revenue still totals the order amount.

Interview tip

> "Bridge = helper table for multi-valued dimensions. Mention fan-out risk and optional weighting."

PreviousNext