Fan-out join

You meet it when#

You join a fact table to anything you have not personally verified is unique on the join key. Tags, addresses, a dimension that gained a second row per customer during a migration, a "current" table that turned out not to be.

The version that looks right#

select sum(o.revenue)
from orders o
join order_tags t on t.order_id = o.order_id;

Orders has one row per order. order_tags has one row per tag. An order with three tags contributes its revenue three times. On a real table this inflates revenue by 20–60% and reads as a good quarter.

The pattern#

Aggregate the many side to the grain of the one side before joining.

select sum(o.revenue)
from orders o
left join (
    select order_id,
           count(*)              as tag_count,
           string_agg(tag, ',')  as tags
    from order_tags
    group by order_id
) t on t.order_id = o.order_id;

If you only need existence, do not join at all — use a semi-join, which cannot multiply rows:

select sum(o.revenue)
from orders o
where exists (select 1 from order_tags t
              where t.order_id = o.order_id and t.tag = 'gift');

The check that catches it#

Two queries, both fast, and worth making reflexive:

-- Is the key actually unique on the side I think is "one"?
select order_id, count(*) from order_tags group by 1 having count(*) > 1 limit 5;

-- Did the row count survive the join?
select count(*) from orders;                       -- 7000
select count(*) from orders o join dim d on ...;   -- must still be 7000

Why it keeps happening#

The failure has no symptom. The query is idiomatic, it returns, and the answer is in the range a person would expect. The only defence is the habit: before writing any join, say out loud which side is one and which is many.