Nothing errors. No pipeline turns red. Revenue in the weekly board deck is up 40% and nobody can explain why, so somebody clicks into an order and checks it against the source system. It matches. They check another. It matches too. The number is wrong and every row is right.

This is a fan-out join, the most common silent inflation bug in analytics. A join that was one-to-one for two years starts matching more than one row on the right, and every aggregate built on top of it quietly multiplies.

What actually happens

Take the join every warehouse has some version of:

SELECT o.order_id, o.order_value, s.carrier, s.shipped_at
FROM orders o
LEFT JOIN shipments s ON s.order_id = o.order_id;

For as long as anyone can remember, shipments held exactly one row per order. Then operations turned on split shipments — an order with three items shipping from two warehouses now writes two rows. Nobody told the data team, because from where operations sit this was a feature launch, not a schema change.

The join is now one-to-many. An order worth 100 with two shipments appears twice, at 100 each. SUM(order_value) for that order returns 200. Nothing in the SQL changed; the cardinality of the source did.

The dimension side is subtler. A customers dimension gets converted to slowly-changing type 2 so history is preserved. Every customer who has ever changed address now has two or three rows, each with its own validity window. The fact table still joins on customer_id, which is no longer that table’s primary key, so every fact row fans out once per version of the customer.

Both cases share a shape: the join key stopped being unique on one side, and the SQL had no way to notice.

Why sums inflate while spot checks pass

This is the property that makes fan-out so expensive to find. The duplication is invisible at the grain people check at.

  • Row-level checks pass. Every duplicated row is genuine and accurate. Pull up order 8842 and it reconciles perfectly with the source system.
  • COUNT(*) and SUM() inflate. Aggregates count the duplicates because the duplicates exist. Revenue, order count, units, conversion denominators — all multiplied by the average fan-out factor.
  • COUNT(DISTINCT ...) stays correct. Which is why some dashboards on the same model look fine and others are wildly wrong, and why the first instinct — “it’s a dashboard bug” — wastes a day.
  • Averages move less than totals. If numerator and denominator inflate together, ratios can look untouched, so a metric like average order value stays plausible while total revenue doubles. That masks the problem rather than surfacing it.
  • The inflation is fractional. Only some orders split. If 30% of orders now have two shipments, revenue is up 30%, which is exactly the kind of number a growing business will happily believe.

How to detect it

Three checks, cheapest first. Run them on the join key, on the side you are joining to.

1. Count versus count distinct. The one-liner that answers “is my key still unique?”

SELECT
  COUNT(*)                     AS total_rows,
  COUNT(DISTINCT order_id)     AS distinct_keys,
  COUNT(*) - COUNT(DISTINCT order_id) AS extra_rows
FROM shipments;

If extra_rows is anything other than zero, any join on order_id fans out.

2. Find the offenders, not just the count. The count tells you there is a problem; this tells you what kind.

SELECT order_id, COUNT(*) AS n
FROM shipments
GROUP BY order_id
HAVING COUNT(*) > 1
ORDER BY n DESC
LIMIT 20;

Look at the top of that list. Two rows per order across many orders means a legitimate grain change. One order with 4,000 rows means a broken backfill or a loop that ran twice.

3. Compare the join output to its left side. The direct test of whether a specific query fans out.

SELECT
  (SELECT COUNT(*) FROM orders)                           AS left_rows,
  (SELECT COUNT(*)
     FROM orders o
     LEFT JOIN shipments s ON s.order_id = o.order_id)    AS joined_rows;

A LEFT JOIN that preserves grain returns exactly the left row count. Any excess is fan-out, and the ratio between the two numbers is your inflation factor. Run it on every model whose join is supposed to be one-to-one: it encodes an assumption people otherwise carry in their heads.

If your transformation tool supports tests, the same assumption belongs in the repo as a uniqueness assertion on the key column of every table you join to. It costs one line and it fails the build rather than the board deck.

Fix the grain, not the symptom

The tempting fix is SELECT DISTINCT, or a GROUP BY over every column in the select list. Resist it. DISTINCT collapses rows that happen to be identical, so it silently discards real shipments when they differ and silently keeps duplicates when a timestamp differs by a second. You get a model that is wrong in a new way and no longer complains.

There are three honest fixes, and which one you want depends on what the extra rows mean.

Aggregate before joining. If you want one row per order and the shipment detail is incidental, collapse the right side to the grain you need first:

LEFT JOIN (
  SELECT order_id,
         COUNT(*)       AS shipment_count,
         MIN(shipped_at) AS first_shipped_at,
         MAX(shipped_at) AS last_shipped_at
  FROM shipments
  GROUP BY order_id
) s ON s.order_id = o.order_id

The join is one-to-one again by construction, and you have gained a shipment_count column that makes the change visible instead of hiding it.

Pick one row deliberately. If you genuinely want a single shipment — the latest, the first, the one that delivered — say so with a window function rather than leaving it to chance:

LEFT JOIN (
  SELECT *
  FROM (
    SELECT s.*,
           ROW_NUMBER() OVER (
             PARTITION BY order_id ORDER BY shipped_at DESC
           ) AS rn
    FROM shipments s
  ) ranked
  WHERE rn = 1
) s ON s.order_id = o.order_id

This is deduplication with a stated tie-break, which is reviewable. DISTINCT is deduplication with a hidden one.

Join on the real key. For the slowly-changing dimension, customer_id was never the fix — the key became customer_id plus the validity window. Join on both:

LEFT JOIN customers c
  ON c.customer_id = f.customer_id
 AND f.event_at >= c.valid_from
 AND f.event_at <  COALESCE(c.valid_to, TIMESTAMP '9999-12-31')

Now each fact row matches exactly one customer version, which is what the dimension was designed to give you.

The duplicates usually start upstream

Worth saying plainly, because it changes where you look: most fan-out is not a modelling mistake. The model was correct when it was written. Somebody upstream changed the grain of a source table — enabled split shipments, converted a dimension to type 2, replayed a Kafka topic, re-ran a backfill without truncating, added a row per currency or per region — and the assumption baked into a join two layers down stopped holding.

That is why reviewing your own SQL rarely finds it: the SQL is unchanged, the data underneath it isn’t. It also means the useful place to check uniqueness is the raw and staging tables, at the boundary where data arrives, not only the polished models at the end.

And it means detection has to be continuous rather than one-off. Fan-out has a specific, unhelpful timeline: it starts on a Tuesday when a feature flag flips, it inflates every number from that moment forward, and it is usually found weeks later when someone senior refuses to believe a chart. By then you are not just fixing a join, you are re-stating numbers people have already acted on. A uniqueness check that runs every night turns a month of quiet inflation into one morning’s work, because you learn about it while the cause is still the most recent thing that changed.

How Sentry handles it

Duplicates is one of Sentry’s six nightly checks, and this is exactly the failure it exists for.

  • Uniqueness of key columns, checked every night. Sentry learns which columns have historically been unique on each table you monitor, and raises a finding the night that stops being true — the day the grain changed, not the week the board deck did.
  • It watches the tables, so it catches source-side changes. The join lives in your transformation code; the duplicate rows arrive in a table. Monitoring the tables means the split-shipment launch and the type 2 conversion show up whether or not anyone updated the model.
  • Evidence, not just an alert. Each finding comes with a plain-English diagnosis, the queries Sentry ran, sample offending values, and the historical baseline it was judged against — enough to confirm in a minute whether it is a broken backfill or a deliberate grain change.
  • Expected changes stop being noise. If the second row per order is intentional, mark the finding as expected and it folds into the baseline, so the next real regression is not buried under a known one.
  • Read-only, and it never touches your models. Sentry diagnoses. Choosing between aggregating first, ranking with a window function, or fixing the join key stays with the person who knows what the extra rows mean.

Want to know whether any of your key columns are already fanning out? Pick a plan and get started — read-only credentials, first digest tomorrow morning.