Joins and the Fan-Out Bug: How Revenue Doubles Without an Error

Every data team I have worked with has some version of the same postmortem in their history: a dashboard showed revenue up 40% overnight, someone got excited, and three hours later the cause turned out to be a join. Not a wrong join — the SQL was the same SQL that had run correctly for months. A join whose assumption broke.

The assumption is always grain. You joined orders to customers believing customer_id was unique in the customer table. Then a backfill, a botched SCD load, or a source system quirk produced two rows per customer, and every order silently became two orders. No error. No warning. Just numbers that are wrong in the most plausible-looking way.

This post is the mechanics of fan-out, the checks that catch it before a human does, and the two join forms — semi-joins and anti-joins — that make an entire class of it impossible.

Fan-out is arithmetic, not a bug in the engine

A join produces one output row per matching pair. If the left side has one row per order and the right side has one row per customer, an inner join on customer_id preserves the order grain. If the right side has two rows for a customer, every one of that customer’s orders appears twice. The engine did exactly what you asked.

flowchart LR
  orders["orders: 1 row per order_id"] --> join{JOIN on customer_id}
  cust["customers: 2 rows for C42"] --> join
  join --> out["Result: C42 orders duplicated"]
  out --> sum["SUM(amount) doubles for C42"]

One duplicate key on the dimension side multiplies every matching fact row.

The dangerous property is that fan-out scales with the damage. One duplicated customer among a million barely moves an average, so it survives eyeballing. A full-table duplication doubles everything and gets caught in an hour. The worst incidents live in between: a few percent of keys duplicated, aggregates drifting for weeks before anyone traced it.

Fan-out is also how DISTINCT gets abused as a patch, which I covered in the anti-patterns post. The patch hides the symptom and leaves the grain bug in production.

Grain checks: assert, do not assume

The fix is not being more careful. It is making the assumption executable. Before I trust a join in a model, I want two facts proven by the pipeline itself: the dimension key is unique, and the join did not change the fact row count.

-- 1. Key uniqueness on the dimension, as a hard gate
select customer_id, count(*) as n
from dim_customers
group by customer_id
having count(*) > 1;
-- Zero rows or the build fails.

-- 2. Row-count preservation across the join
with joined as (
  select o.order_id
  from fct_orders o
  left join dim_customers c
    on c.customer_id = o.customer_id
)
select
  (select count(*) from fct_orders) as before_rows,
  (select count(*) from joined)     as after_rows,
  (select count(*) from joined) -
  (select count(*) from fct_orders) as fanout_rows;
-- fanout_rows must be 0 for a 1:N-to-1 join.

In dbt this is a unique test on the dimension key plus an equal-rowcount test between the model and its driving table. Both run in CI on every PR — the same discipline that makes incremental models trustworthy. If a source system can produce duplicates, that belongs in the data contract with the producing team, because a test that fires weekly is a conversation you need to have once.

When the dimension legitimately carries history — an SCD2 table — the join must include the validity window, and the grain check asserts uniqueness of (customer_id, valid_from) instead:

select
  o.order_id,
  o.amount,
  c.segment
from fct_orders o
left join dim_customers_scd2 c
  on c.customer_id = o.customer_id
  and o.ordered_at >= c.valid_from
  and o.ordered_at <  c.valid_to;

Forget the window predicates and you have built a deliberate fan-out across every historical version. I have seen that one ship to production twice.

Semi-joins: filtering without joining

Half the joins in analytics code are not joins at all. They are filters: give me orders from customers in the enterprise segment. Nobody wants customer columns in the output — they want the customer table’s opinion about which orders qualify.

Written as a join, that filter inherits fan-out risk for no benefit. Written as EXISTS, it cannot fan out, because a semi-join returns each left row at most once regardless of how many right rows match:

-- Fan-out risk, and the intent is buried
select o.*
from fct_orders o
join dim_customers c
  on c.customer_id = o.customer_id
where c.segment = 'enterprise';

-- Semi-join: same filter, fan-out impossible
select o.*
from fct_orders o
where exists (
  select 1
  from dim_customers c
  where c.customer_id = o.customer_id
    and c.segment = 'enterprise'
);

The engine plans both as a join internally, so there is no performance penalty on any modern warehouse — the difference is semantic. EXISTS tells the reader and the optimizer that the right side contributes zero columns and zero row multiplication. When I review a model, a join whose right-side columns never appear in the SELECT is an automatic “make this EXISTS” comment.

Anti-joins: the NOT IN trap

The inverse filter — orders from customers not in a table — is where SQL hands you a loaded footgun. NOT IN against a subquery that can return NULL matches nothing. Not “excludes the NULLs” — returns zero rows, silently, because x NOT IN (1, 2, NULL) evaluates to UNKNOWN for every x.

-- Broken the day churned_customers gets one NULL customer_id
select o.*
from fct_orders o
where o.customer_id not in (
  select customer_id from churned_customers
);

-- Correct anti-join, NULL-safe by construction
select o.*
from fct_orders o
where not exists (
  select 1
  from churned_customers ch
  where ch.customer_id = o.customer_id
);

-- Equivalent and sometimes clearer in a model
select o.*
from fct_orders o
left join churned_customers ch
  on ch.customer_id = o.customer_id
where ch.customer_id is null;

I use NOT EXISTS by default and the LEFT JOIN ... IS NULL form when I also want to inspect the matched rows during development. NOT IN stays banned in review. It reads fine, tests fine on clean data, and fails closed on the first NULL — the worst failure mode available.

flowchart TD
  need{What do you need from the right table?}
  need -->|its columns| join["JOIN, plus a grain check"]
  need -->|only whether a match exists| semi["EXISTS semi-join"]
  need -->|only whether no match exists| anti["NOT EXISTS anti-join"]
  join --> test["Assert key uniqueness and row counts"]

Most joins that fan out never needed to be joins.

The postmortem pattern

The fan-out incidents I have run all reduce to the same sequence, which is worth writing down because the fix targets the sequence, not the individual bug.

A dimension gained duplicate keys — from a backfill run twice, a deleted dedup step, or an upstream system that started sending updates as new rows. No uniqueness test existed, or it existed but only warned. A downstream join multiplied fact rows. Aggregates moved plausibly, so dashboards absorbed it. A human eventually noticed a number that could not be true, and the trace took hours because the join was three models upstream of the symptom.

The durable fixes, in order of value:

1. unique and not_null tests on every dimension key, failing the build, not warning.
2. Row-count assertions after every fact-to-dimension join in a model that feeds reporting.
3. Semi-join and anti-join forms wherever the right table contributes no columns.
4. A grain statement in every model’s header comment — one row per what? If the author cannot finish that sentence, the review is not done.

You can rehearse the whole failure in five minutes: duplicate one key in a toy dimension in the /playground/, run the join, watch SUM double, then add the HAVING count(*) > 1 gate and watch it catch. Cheaper than the real incident.

Pitfalls

Left joins treated as safe. LEFT JOIN protects you from losing rows, not from gaining them. Fan-out applies identically.

Testing uniqueness once, manually. Grain rots. The test has to run on every build, because the duplicate arrives on a Tuesday eighteen months after you checked.

COUNT(DISTINCT order_id) as a permanent fix. It corrects one aggregate and leaves every other measure in the model wrong. Fix the grain, not the metric.

Joining on expressions. on trim(upper(a.code)) = b.code hides mismatches and blocks pruning. Normalize keys upstream in staging, join on clean columns.

Assuming the SCD2 window is closed. Overlapping validity windows are duplicates wearing a costume. Test that (key, valid_from) is unique and windows do not overlap.

FAQ

How do I find existing fan-outs in a warehouse I inherited? Run key-uniqueness checks on every table that anything joins to. Then compare fact row counts before and after each reporting model’s join chain. The offenders surface in an afternoon.

Is EXISTS slower than a JOIN? No. Snowflake, BigQuery, DuckDB, and Postgres all plan a semi-join for it, which is the same hash-join machinery with an early-out. Measure if you doubt it; I have never seen a regression.

When is fan-out intentional? When you are deliberately moving to a finer grain — exploding orders to order lines, or joining a date spine to expand sparse data. The difference is that you can name the output grain. If you can name it, it is a grain change; if you cannot, it is a bug.

Why not just put DISTINCT on the final SELECT? Because it collapses rows only when every column matches, costs a full sort or hash of the fanned-out result, and hides the bug from the person who has to find it later. The anti-patterns post covers why that patch fails.

Do window functions help here? For deduplicating a dimension, yes — QUALIFY ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC) = 1 states the grain and the tiebreaker explicitly. The window functions post walks through it.

What this means for data engineers

Joins do not have bugs; grains do. Every join in a pipeline carries an implicit claim about key uniqueness on one side, and the only question is whether that claim is enforced by a test or by luck.

Make the claim executable: uniqueness tests on dimension keys, row-count assertions around joins that feed money numbers, and EXISTS/NOT EXISTS wherever the join was really a filter. Ban NOT IN against subqueries outright.

The revenue-doubling incident is not a rite of passage. It is a missing test with good PR.