How to create a Bridge Table for M:N in Data Modeling (Kimball)

João Barros 24 de August de 2026 5 min read

This tutorial shows how to create a Bridge Table to resolve a many-to-many (M:N) relationship between two dimensions in a Kimball star schema, useful for correct analytics and performance in tools like Power BI. We explain why it’s needed, common mistakes, and a practical SQL example to implement and consume the Bridge Table.

Prerequisites

Basic knowledge of Dimensional Modeling (Kimball) and SQL.
An environment with a relational database (e.g., SQL Server, PostgreSQL) to run queries.
Example of a fact table and two dimensions with an M:N relationship (e.g., Sales, Product, Promotion).

Step 1: Understand when you need a Bridge Table

A Bridge Table is necessary when a fact or two dimensions have a many-to-many relationship that causes measure duplication if you simply join the tables. A common example: a Product can be in multiple Promotions and a Promotion applies to multiple Products. Without a Bridge, sums can be inflated.

Step 2: Model the Bridge Table (keys and attributes)

Define the Bridge Table with foreign keys to each involved dimension and a surrogate key. Include attributes that describe the relationship (for example, weighting, start_date, end_date) to allow allocation and filtering. Do not place dimension attributes that belong to only one dimension.

-- Exemplo de DDL simplificado (SQL Server / PostgreSQL compatible)
CREATE TABLE bridge_product_promotion (
  bridge_id BIGSERIAL PRIMARY KEY,
  product_key BIGINT NOT NULL,
  promotion_key BIGINT NOT NULL,
  weight NUMERIC(18,6) DEFAULT 1.0,  -- para alocação se necessário
  start_date DATE,
  end_date DATE
);

-- Índices para performance
CREATE INDEX idx_bridge_product ON bridge_product_promotion(product_key);
CREATE INDEX idx_bridge_promotion ON bridge_product_promotion(promotion_key);

Step 3: Populate the Bridge Table from source data

Extract the M:N relationships from the transaction source or the reference ETL. Validate duplicates and calculate the weight if a promotion covers part of the product (e.g., share). If there is no share, use 1.0 by default. Example with mapping data.

-- Exemplo: popular a Bridge a partir de uma tabela fonte product_promo_map
INSERT INTO bridge_product_promotion (product_key, promotion_key, weight, start_date, end_date)
SELECT p.product_key, pm.promotion_key,
       COALESCE(pm.share, 1.0) AS weight,
       pm.start_date, pm.end_date
FROM product_dim p
JOIN product_promo_map pm ON p.product_code = pm.product_code
-- evitar duplicados: usar DISTINCT ou lógica de agregação
GROUP BY p.product_key, pm.promotion_key, pm.share, pm.start_date, pm.end_date;

Step 4: Integrate the Bridge Table into the Fact query

When querying the fact (e.g., Sales) and you want to analyze by Promotion and Product, join the Bridge Table and apply the weight to avoid double counting. If the fact already directly references a product_key and the promotion is derived, join the bridge to link the promotion_key.

-- Exemplo de query agregada que evita duplicação
SELECT pr.product_name,
       pm.promotion_name,
       SUM(sales.amount * b.weight) AS amount_allocated,
       COUNT(DISTINCT s.sale_id) AS distinct_sales_count
FROM sales_fact s
JOIN product_dim pr ON s.product_key = pr.product_key
JOIN bridge_product_promotion b ON pr.product_key = b.product_key
JOIN promotion_dim pm ON b.promotion_key = pm.promotion_key
-- considerar filtros por data usando b.start_date/end_date e s.sale_date
WHERE s.sale_date BETWEEN COALESCE(b.start_date, '1900-01-01') AND COALESCE(b.end_date, '9999-12-31')
GROUP BY pr.product_name, pm.promotion_name;

Step 5: Handle common cases and errors

Common mistakes: 1) Not applying weight leads to duplication; 2) Forgetting the temporal window (start/end) causes incorrect allocations; 3) Making wrong joins causing multiplication of the fact. Check cardinalities and use COUNT(DISTINCT ...) in validations. Document the weight logic and how the Bridge was populated.

Verify the result

Validate with these steps: 1) Compare total sums before/after the Bridge — without weight, totals can increase; 2) Test scenarios with a promotion affecting two products and confirm that the allocated sum of the promotion equals the original sum (when weight=1 and applicable rules); 3) Use diagnostic queries to count multiplicity in the bridge: SELECT product_key, COUNT(*) FROM bridge_product_promotion GROUP BY product_key HAVING COUNT(*) > 1.

Conclusion

A correct Bridge Table resolves M:N relationships in a Kimball star schema and prevents metric duplication. Next steps: automate the Bridge load in your ETL, include regression tests, and expose the Bridge to Power BI with measures that respect the weight. Tip: start with real business-case data and always check temporal windows and weights — which M:N relationship in your domain is causing you counting problems?