DWH Interview Question 29 - What is a bridge table, and how do you handle many-to-many in a star schema?

Data Warehouse Interview Q&A · Question 29

In this post we are going to cover the bridge table, and how to handle a many-to-many relationship in a star schema without double counting.

A star schema expects each fact row to point to one row in each dimension. Real life does not always work that way. One order can be won by several sales reps, one bank account can have several owners, and one hospital visit can have several diagnoses. A bridge table sits between the fact and the dimension and lists every member of each group, usually with a weight that says how much of the measure each member gets.

We will build a bridge between orders and sales reps, report the sales two ways (full credit and allocated), and add a data quality check on the weights.

Question 29 of the Real DWH Question and Answers series on TechBrothersIT.

On this page

1. The interview answer
2. The problem: one order, several sales reps
3. How the bridge table works
4. Let's create some sample data
5. Step 1: Impact view - full credit to every rep
6. Step 2: Allocated view - multiply by the weight
7. Step 3: Check that the weights add up to 1
8. Where bridges show up
9. Follow-up questions interviewers ask
10. The full script

The interview answer

Say this first

A bridge table resolves a many-to-many relationship between a fact and a dimension, like one order credited to several sales reps. It holds one row per group and member, often with a weighting factor that adds up to 1 per group, so measures can be allocated without double counting.

The problem: one order, several sales reps

TB Shop's business sales team sells as a team. Order 1001 was won by Ana and Ben together, order 1003 by Ana and Chen, and order 1002 by Ben alone.

A star schema wants one rep key on each fact row, but order 1001 has two reps and another order might have three. Adding columns like rep1, rep2 and rep3 does not work either. The number of reps varies, and every query would need to check every column.

How the bridge table works

Here is how the bridge table solves it.

• Each distinct team becomes a group. Ana and Ben are group 10, Ben alone is group 20, Ana and Chen are group 30.
• The fact stores one group key, so every fact row still has exactly one key.
• The bridge lists the members of each group, one row per rep, with a weight. The weights in every group add up to 1.

Let's create some sample data

We need three tables: dim_sales_rep with the reps, bridge_rep_group with the members and weights of each group, and fact_orders that points to a group instead of a single rep.

Sample data — run this first

DROP TABLE IF EXISTS fact_orders;
DROP TABLE IF EXISTS bridge_rep_group;
DROP TABLE IF EXISTS dim_sales_rep;

CREATE TABLE dim_sales_rep (rep_key INT PRIMARY KEY, rep_name VARCHAR(50));
INSERT INTO dim_sales_rep VALUES (1, 'Ana'), (2, 'Ben'), (3, 'Chen');

-- bridge: one row per (group, rep) pair, with an allocation weight
CREATE TABLE bridge_rep_group (
  rep_group_key INT,
  rep_key       INT REFERENCES dim_sales_rep(rep_key),
  weight        DECIMAL(4,2),
  PRIMARY KEY (rep_group_key, rep_key)
);
INSERT INTO bridge_rep_group VALUES
  (10, 1, 0.50), (10, 2, 0.50),   -- group 10: Ana + Ben, split 50/50
  (20, 2, 1.00),                   -- group 20: Ben alone
  (30, 1, 0.60), (30, 3, 0.40);   -- group 30: Ana + Chen, split 60/40

-- the fact points to a GROUP of reps, not to one rep
CREATE TABLE fact_orders (
  order_id      INT PRIMARY KEY,
  rep_group_key INT,
  amount        DECIMAL(10,2)
);
INSERT INTO fact_orders VALUES (1001, 10, 100.00), (1002, 20, 60.00), (1003, 30, 90.00);

Let's look at the reps.

Query

SELECT * FROM dim_sales_rep ORDER BY 1;

Result3 rows · 2 columns

rep_key	rep_name
1	Ana
2	Ben
3	Chen

Three reps: Ana, Ben and Chen.

Query

SELECT * FROM bridge_rep_group ORDER BY 1;

Result5 rows · 3 columns

rep_group_key	rep_key	weight
10	1	0.50
10	2	0.50
20	2	1.00
30	1	0.60
30	3	0.40

Group 10 is Ana and Ben, split 0.50 and 0.50. Group 20 is Ben alone with 1.00. Group 30 is Ana and Chen, split 0.60 and 0.40.

Query

SELECT * FROM fact_orders ORDER BY 1;

Result3 rows · 3 columns

order_id	rep_group_key	amount
1001	10	100.00
1002	20	60.00
1003	30	90.00

Three orders worth 100.00, 60.00 and 90.00, so the company sold 250.00 in total. Each order stores one rep_group_key.

Step 1: Impact view - full credit to every rep

Now let's query it without the weights. We join the fact to the bridge and the bridge to the rep, then sum the amount per rep.

Query

SELECT r.rep_name, SUM(f.amount) AS credited
FROM fact_orders f
JOIN bridge_rep_group b ON b.rep_group_key = f.rep_group_key
JOIN dim_sales_rep r    ON r.rep_key = b.rep_key
GROUP BY r.rep_name
ORDER BY r.rep_name;

Result3 rows · 2 columns

rep_name	credited
Ana	190.00
Ben	160.00
Chen	90.00

Ana shows 190.00, Ben 160.00 and Chen 90.00. Every rep gets full credit for every order they touched. That answers a valid question, which orders was each rep involved in, and it is called an impact report.

But look at the total. The reps add up to 440.00, while the company only sold 250.00. Each order is repeated once for every member of its group. Never put this column next to company revenue.

Step 2: Allocated view - multiply by the weight

Now let's use the weights. We multiply each amount by the rep's weight in that group before summing.

Query

SELECT r.rep_name,
       CAST(SUM(f.amount * b.weight) AS DECIMAL(10,2)) AS allocated
FROM fact_orders f
JOIN bridge_rep_group b ON b.rep_group_key = f.rep_group_key
JOIN dim_sales_rep r    ON r.rep_key = b.rep_key
GROUP BY r.rep_name
ORDER BY r.rep_name;

Result3 rows · 2 columns

rep_name	allocated
Ana	104.00
Ben	110.00
Chen	36.00

Order 1001's 100.00 becomes 50.00 for Ana and 50.00 for Ben. Order 1003's 90.00 becomes 54.00 for Ana and 36.00 for Chen. So Ana shows 104.00, Ben 110.00 and Chen 36.00, and together they add up to exactly 250.00, the real total.

Step 3: Check that the weights add up to 1

The allocation is only correct if the weights in every group add up to 1. Let's add a data quality check that returns any group where they don't.

Query

SELECT rep_group_key, SUM(weight) AS total_weight
FROM bridge_rep_group
GROUP BY rep_group_key
HAVING SUM(weight) <> 1;

Result0 rows · 2 columns

rep_group_key	total_weight
(no rows)

No rows came back, so every group's weights add up to 1 and the allocated totals will match the fact. Run a check like this after every bridge load.

Where bridges show up

Bridge tables appear in many industries, and they can also sit between two dimensions.

Domain	Many-to-many
Banking	joint accounts with several owners
Healthcare	one visit, several diagnoses
Retail	a product in several categories
HR	an employee in several departments

Interview tip

Mention both views: the impact view without weights and the allocated view with weights, and say that the allocated view is the one that matches finance.

Follow-up questions interviewers ask

Why do totals double count without weights?

Each fact row joins to every member of its group, so the amount is repeated once per member.

What must weights satisfy?

They must add up to exactly 1 per group; check it with a GROUP BY query that returns any group whose weights do not add up to 1.

When do you NOT use weights?

For impact analysis (which orders involved each rep), where full credit per member is the point.

The full script

Here is everything from this post in one script. Copy it and run it top to bottom.

Full script — copy and run top to bottom

-- ========================================================================
-- Data Warehouse Interview Q&A #29 - Bridge tables for many-to-many
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example:  CREATE SCHEMA dwh_q29;  SET search_path TO dwh_q29;
-- ========================================================================

-- Step 0: create and load the sample data
DROP TABLE IF EXISTS fact_orders;
DROP TABLE IF EXISTS bridge_rep_group;
DROP TABLE IF EXISTS dim_sales_rep;

CREATE TABLE dim_sales_rep (rep_key INT PRIMARY KEY, rep_name VARCHAR(50));
INSERT INTO dim_sales_rep VALUES (1, 'Ana'), (2, 'Ben'), (3, 'Chen');

-- bridge: one row per (group, rep) pair, with an allocation weight
CREATE TABLE bridge_rep_group (
  rep_group_key INT,
  rep_key       INT REFERENCES dim_sales_rep(rep_key),
  weight        DECIMAL(4,2),
  PRIMARY KEY (rep_group_key, rep_key)
);
INSERT INTO bridge_rep_group VALUES
  (10, 1, 0.50), (10, 2, 0.50),   -- group 10: Ana + Ben, split 50/50
  (20, 2, 1.00),                   -- group 20: Ben alone
  (30, 1, 0.60), (30, 3, 0.40);   -- group 30: Ana + Chen, split 60/40

-- the fact points to a GROUP of reps, not to one rep
CREATE TABLE fact_orders (
  order_id      INT PRIMARY KEY,
  rep_group_key INT,
  amount        DECIMAL(10,2)
);
INSERT INTO fact_orders VALUES (1001, 10, 100.00), (1002, 20, 60.00), (1003, 30, 90.00);


-- Step 1: Impact view: every rep gets full credit (totals double count)
SELECT r.rep_name, SUM(f.amount) AS credited
FROM fact_orders f
JOIN bridge_rep_group b ON b.rep_group_key = f.rep_group_key
JOIN dim_sales_rep r    ON r.rep_key = b.rep_key
GROUP BY r.rep_name
ORDER BY r.rep_name;

-- Step 2: Allocated view: multiply by the weight
SELECT r.rep_name,
       CAST(SUM(f.amount * b.weight) AS DECIMAL(10,2)) AS allocated
FROM fact_orders f
JOIN bridge_rep_group b ON b.rep_group_key = f.rep_group_key
JOIN dim_sales_rep r    ON r.rep_key = b.rep_key
GROUP BY r.rep_name
ORDER BY r.rep_name;

-- Step 3: Data quality check: weights must add up to 1 per group
SELECT rep_group_key, SUM(weight) AS total_weight
FROM bridge_rep_group
GROUP BY rep_group_key
HAVING SUM(weight) <> 1;

Key points

• A bridge table resolves many-to-many in a star schema
• The fact stores one group key; the bridge lists the members
• Weights add up to 1 per group
• Impact view: full credit; allocated view: amount × weight
• Check weights with a data quality query

Next in this series

Question 30 — Late-arriving dimensions How do you handle late-arriving dimensions?

Every query and every result on this page was run on PostgreSQL 16 using the script above. The SQL is standard and runs on most databases with small changes (noted in the script). All tables and data are invented demo data for teaching.