Data modeling · Keys & Relationships

What is a bridge table?

Mediumbridge tablemany-to-manyweightingdimensional

Interview question

What is a bridge table in dimensional modeling?

How to answer

The short answer

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

Output
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."