Bridge Tables and Many-to-Many Dimensions

A star schema is a promise: one fact table in the middle, a ring of clean dimensions around it, every join a single hop. Most of the time that promise holds. Then reality shows up with an order that has three different dates, a book with four co-authors, and a fistful of boolean flags nobody wants to model — and the plain star starts to strain.

This post is about the reinforcement that gets reached for most often and understood least: the bridge table, for when a dimension relates to another dimension as a many-to-many and a plain foreign key simply cannot carry it. (The other reinforcements — junk dimensions, mini-dimensions, role-playing, outriggers — get their own chapter next.)

The judgement that runs through all of them is the same: reach for these deliberately, when the shape of your data forces the issue, not because they look sophisticated on an architecture diagram. A star you can explain in one sentence is worth more than a clever schema you have to defend.

Bridge tables: when one-to-many becomes many-to-many

A book has an author. Except when it has two. Or a translator, an editor, and three contributors to an anthology. The moment a dimension relates to another dimension as a many-to-many, a foreign key can’t carry it — a single author_sk on dim_book can only name one person.

The bridge table sits between them and resolves the relationship:

dim_book  ──<  bridge_book_author  >──  dim_author

Each row of bridge_book_author is one book-author pairing. A book with three authors gets three rows; join dim_book through the bridge to dim_author and you can slice sales by author even though the sale happened at the book level.

There’s a trap here: if you sum book sales across authors, a three-author book counts three times. Sometimes that’s what you want — total sales an author was involved in. When it isn’t, the bridge carries an allocation factor, a weight per row that sums to 1.0 across each book (three equal authors get 0.333 each). Multiply the fact’s measure by the weight and the numbers reconcile. Deciding whether you want the double-count or the allocation is a business question, not a modeling one — the bridge just makes both answerable.

Building the bridge, for real

That diagram is where most treatments of the bridge stop — two crow’s feet and a promise. Let’s actually build it, because the allocation factor is a column you have to compute, and computing it correctly is the whole difficulty.

Start with the sources. The OLTP side already knows about multi-author books — it has to, or it couldn’t print a cover — and it stores the relationship the way applications always store a many-to-many: an associative table. book_authors has one row per pairing, (book_id, author_id, author_role, credit_order), and the shop’s authors table carries the people. Staging cleans both; the marts turn them into a dimension and a bridge.

dim_author is an ordinary dimension — the same shape as every other in this series, unknown member and all:

-- models/marts/dim_author.sql
{{ config(materialized='table') }}

with authors as (
    select * from {{ ref('stg_authors') }}
),

with_unknown as (
    select author_id, full_name, country, debut_year
    from authors

    union all

    select
        '-1' as author_id,                 -- the same sentinel every dimension uses
        'Unknown', 'Unknown',
        cast(null as number)  as debut_year
)

select
    {{ dbt_utils.generate_surrogate_key(['author_id']) }} as author_sk,
    author_id,
    full_name,
    country,
    debut_year
from with_unknown

Nothing new there — that’s the dimension pattern applied to a second noun. The bridge is where the interesting decision lives:

-- models/marts/bridge_book_author.sql
{{ config(materialized='table') }}

with book_authors as (
    select
        book_id,
        author_id,
        author_role          -- 'author', 'translator', 'editor', 'contributor'
    from {{ ref('stg_book_authors') }}
)

select
    {{ dbt_utils.generate_surrogate_key(['book_id']) }}   as book_sk,
    {{ dbt_utils.generate_surrogate_key(['author_id']) }} as author_sk,
    author_role,
    1.0 / count(*) over (partition by book_id) as allocation_factor
from book_authors

Read the grain off the select: one row per book-author pairing. A solo novel contributes one row; a three-author anthology contributes three. The two surrogate keys are computed with the same generate_surrogate_key expressions the dimensions used — so the bridge joins to dim_book on book_sk and to dim_author on author_sk by construction, no lookups, the determinism from the dimension chapter doing its job again. And the load-bearing column is the last one.

1.0 / count(*) over (partition by book_id) is the allocation factor, computed in a single window pass: count the authors on each book, and give every one of them an equal 1/n share. Three authors get 0.333… each; a solo author gets 1.0. By construction the shares on any one book sum to exactly one — which is the whole point, and the thing the test at the end of this section guards. Equal splits are the common default; if the business credits the lead author more heavily, you’d weight by credit_order or a stored percentage instead of 1/n — but the invariant never changes: the weights on a book sum to one.

The double-count, in actual numbers

Why the weight matters is easiest to see when you skip it. Take two books:

• Signal, written solo by Ableson — 100 copies at $20 = $2,000.
• Future Tense, an anthology by Ableson, Boyd, and Chen — 100 copies at $30 = $3,000.

Total book revenue: $5,000. Now ask “revenue by author” the naive way — join the fact through the bridge to the author and sum, ignoring the weight:

-- WRONG: every author gets full credit for the whole book
select
    a.full_name,
    sum(f.extended_price) as revenue
from fct_order_items f
join bridge_book_author b on b.book_sk = f.book_sk
join dim_author a         on a.author_sk = b.author_sk
group by a.full_name

author	revenue
Ableson	$5,000
Boyd	$3,000
Chen	$3,000
total	$11,000

Eleven thousand dollars of revenue in a shop that sold five. The anthology’s $3,000 got counted once per author — three times — and the grand total is now fiction. This is the bridge’s original sin: the join fans out the fact, one fact row becoming three, and sum faithfully adds up all three copies. Any dashboard that trusts that total is wrong, and wrong in the worst way — plausibly, not obviously.

Now multiply by the weight:

-- RIGHT: each author gets their allocated share
select
    a.full_name,
    sum(f.extended_price * b.allocation_factor) as allocated_revenue
from fct_order_items f
join bridge_book_author b on b.book_sk = f.book_sk
join dim_author a         on a.author_sk = b.author_sk
group by a.full_name

author	allocated_revenue
Ableson	$3,000
Boyd	$1,000
Chen	$1,000
total	$5,000

Ableson keeps all $2,000 of Signal (solo, weight 1.0) plus a third of Future Tense ($1,000); Boyd and Chen split the rest of the anthology at $1,000 apiece. The total is $5,000 — it reconciles with the shop’s books, because allocation_factor sums to one per title and so the fan-out nets back to the original measure. The single-character difference between the two queries — * b.allocation_factor — is the difference between a number you can put in front of finance and a number you can’t.

Both queries are legitimate. “Total revenue an author was involved in” (the $11,000 shape) is a real question — it’s how you’d size an author’s catalog reach — and for that you want the fan-out and you drop the weight. “How do we split the royalty pool” is the $5,000 shape and needs the weight. The bridge answers both; the modeler’s job is to make sure each query picks the one its question meant, and to never let the unweighted total masquerade as company revenue.

When authorship changes: the time-variant bridge

The bridge so far is a snapshot — it says who the authors are, present tense. But authorship credit moves. A second edition adds a translator; a ghostwriter is finally credited; an estate reassigns rights. If the bridge only ever holds today’s pairings, then last year’s sales get re-attributed under today’s credits, and the royalty history quietly rewrites itself.

The fix is the one the slowly-changing-dimensions chapter applied to dimensions: make the bridge Type 2. Give each pairing an effective-date window, and let the bridge carry the history of who was credited when:

-- models/marts/bridge_book_author.sql  (time-variant)
{{ config(materialized='table') }}

with versions as (
    select
        book_id,
        author_id,
        author_role,
        valid_from,
        coalesce(valid_to, cast('9999-12-31' as date)) as valid_to
    from {{ ref('stg_book_author_history') }}
)

select
    {{ dbt_utils.generate_surrogate_key(['book_id']) }}   as book_sk,
    {{ dbt_utils.generate_surrogate_key(['author_id']) }} as author_sk,
    author_role,
    valid_from,
    valid_to,
    1.0 / count(*) over (
        partition by book_id, valid_from
    ) as allocation_factor
from versions

Two changes carry the weight. The grain gains a version: it’s now one row per book-author-window. And the allocation factor partitions by book_id, valid_from, so the shares sum to one within each authorship era rather than across all of history. A book credited to one author until June and to two after has a 1.0 row in the first window and two 0.5 rows in the second, and each window balances on its own.

The join then has to name a point in time. Instead of matching the fact to the bridge on book_sk alone, you constrain it to the window the sale fell in. And here the one apparently-redundant column on fct_order_items earns its keep: the fact carries order_date as a plain date, not only as a date_sk. That means you can express the temporal predicate directly, with no trip through the date dimension at all:

select
    a.full_name,
    sum(f.extended_price * b.allocation_factor) as allocated_revenue
from fct_order_items f
join bridge_book_author b
       on b.book_sk = f.book_sk
      and f.order_date >= b.valid_from        -- the sale fell inside
      and f.order_date <  b.valid_to          -- this authorship window
join dim_author a on a.author_sk = b.author_sk
group by a.full_name

A surrogate key is deliberately opaque — you cannot put date_sk on either side of a >= and get a meaningful answer, because a hash has no order. Any time a fact needs to be compared against a range rather than joined to a dimension, it needs the real value, and that is the whole reason the fact-table chapter kept order_date alongside date_sk. Keys join; values compare.

Now a sale in May attributes to May’s authors and a sale in August to August’s — the credit follows the calendar, and the allocation test just moves to grouping by book_id, valid_from. This is more machinery than most bridges need, and you should reach for it only when the relationship genuinely has history worth keeping. But when it does — royalties, ownership, reporting lines — a static bridge silently launders the past, and the time-variant one is the only honest answer.

The hierarchy bridge: variable depth without a schema change

The dimension chapter flattened a category tree into fixed level_1/2/3 columns and admitted the limits: the depth is baked into the schema, and “total sales under this node, wherever it sits” is awkward. When the tree is genuinely unbounded — or the roll-up must work from any node to any ancestor — the dimensional answer is a bridge, built as a closure table: one row for every ancestor-descendant pair in the tree, plus the distance between them.

A recursive CTE walks the adjacency list and emits every pair, including each node paired with itself at depth 0:

-- models/marts/bridge_category.sql
{{ config(materialized='table') }}

with recursive tree as (

    -- depth 0: every category is its own ancestor
    select
        category_id as ancestor_id,
        category_id as descendant_id,
        0           as depth
    from {{ ref('stg_categories') }}

    union all

    -- descend one level at a time, extending each known path
    select
        t.ancestor_id,
        c.category_id  as descendant_id,
        t.depth + 1    as depth
    from tree t
    join {{ ref('stg_categories') }} c
      on c.parent_category_id = t.descendant_id
)

select
    {{ dbt_utils.generate_surrogate_key(['ancestor_id']) }}   as ancestor_category_sk,
    {{ dbt_utils.generate_surrogate_key(['descendant_id']) }} as descendant_category_sk,
    depth
from tree

For a branch Fiction → Science Fiction → Space Opera, the closure table holds Fiction→Fiction (0), Fiction→Science Fiction (1), Fiction→Space Opera (2), Science Fiction→Space Opera (1), plus each node paired with itself — every ancestor reachable from every descendant, hop count attached.

Rolling up is then one join and a group by, and it works from any level regardless of how deep the tree runs. (This assumes dim_book carries the book’s leaf category_sk — the surrogate of the most specific category it’s filed under — alongside the denormalized names.)

-- total sales under 'Fiction', however many levels deep the books actually sit
select
    anc.category_name as rollup_category,
    sum(f.extended_price) as revenue
from fct_order_items f
join dim_book        bk  on bk.book_sk = f.book_sk
join bridge_category br  on br.descendant_category_sk = bk.category_sk
join dim_category    anc on anc.category_sk = br.ancestor_category_sk
group by anc.category_name

Because the bridge holds the transitive closure, a book filed under “Space Opera” contributes to the Space Opera, Science Fiction, and Fiction totals from the one join — no level_N columns, no depth cap, no schema change when merchandising adds a fifth tier next quarter. The same structure flattens an org chart (every manager to every report, at any distance), a bill of materials, a threaded comment tree — any parent-child hierarchy where the depth varies or the roll-up must start anywhere. The depth column earns its keep too: where depth = 1 gives you direct children only, and where depth > 0 drops the self-pair when a node shouldn’t count itself.

The cost is honest. A closure table is bigger than the tree it encodes — a single chain of depth d generates on the order of d²/2 pairs — and it rebuilds whenever the hierarchy moves. For a stable category tree of a few hundred nodes that’s nothing; for a churning million-node graph it’s a real materialization to size. As with every pattern in this post, the closure bridge answers a specific question — variable-depth roll-up from any node — and is overkill for the fixed three-level ladder that columns handle for free.

Testing the weights

Every bridge with an allocation factor owes one test, and it’s the same shape as a fact-total reconciliation: the weights sum to one per parent. A singular test returns any book whose shares don’t:

-- tests/assert_bridge_allocation_sums_to_one.sql
select
    book_sk,
    sum(allocation_factor) as total_allocation
from {{ ref('bridge_book_author') }}
group by book_sk
having abs(sum(allocation_factor) - 1.0) > 0.001

dbt fails the build if the query returns a single row. The tolerance absorbs floating-point dust — 1.0/3 + 1.0/3 + 1.0/3 doesn’t land on exactly 1.0 in binary — while still catching a real leak, like a pairing that slipped in twice or an allocation computed against the wrong partition. (For the time-variant bridge, group by book_id, valid_from so each authorship window is checked on its own.) It’s three lines, and it’s the difference between “the allocation math is right” as a hope and as a guarantee the build re-enforces on every run. The testing chapter files this alongside the other reconciliation tests — because a bridge whose weights have quietly stopped summing to one is a revenue number that has quietly stopped being true.

Where the other patterns live

Bridges are the heaviest of the star’s reinforcements, and the only one that changes how you aggregate. The lighter ones — a junk dimension for the fistful of boolean flags, a mini-dimension for the attributes that won’t sit still, role-playing so one dim_date can serve as order date and ship date and delivery date, an outrigger for the rare dimension-on-a-dimension, and the degenerate dimension that needs no table at all — are all in the next chapter, built end to end.

Final thoughts

A bridge is not a default, and it is the one reinforcement you should feel the weight of before you reach for it. It trades the plain star’s one-sentence clarity — every join a single hop, every measure safe to sum() — for a capability the plain star genuinely cannot provide: a real many-to-many, with the arithmetic to keep it honest. The price is that the fan-out is now yours to manage. Every query that crosses the bridge either allocates or double-counts, and nothing in the schema will stop an analyst from picking wrong.

So the skill isn’t knowing the pattern; it’s knowing which problem in front of you actually justifies it. A modeler who builds a bridge because a book might someday have two authors is gold-plating. A modeler who builds one because half the catalog is anthologies — and who ships the allocation factor and the test that guards it in the same commit — is doing the job. The star schema earns its keep by being boring. Spend its simplicity only where the data makes you, and when you do spend it, buy the tests too.

Next: Junk, Mini, and Role-Playing Dimensions