Fan-Out Joins: The Silent Cause of Inflated Metrics

Here's a bug that has quietly wrecked more dashboards than almost anything else: you join a fact
table to a dimension, someone later lets a duplicate sneak into that dimension, and every fact
row that matches gets multiplied. Your SUM() inflates. Nothing errors. The dimension looks
fine. The numbers are just… bigger than reality, and nobody's sure why.

What a fan-out actually is

A join multiplies rows whenever the key you join on isn't unique on the other side. Join a fact
to a dimension that has two rows for the same key, and every matching fact row comes back
twice. Aggregate that, and totals double. Three duplicate dimension rows? Triple. The fact
table didn't change — the join invented rows.

Why it's so hard to spot

• The duplicate dimension rows are often identical on the visible attributes, so eyeballing the dimension reveals nothing.
• If only a few keys are affected, the grand total still looks plausible.
• It usually appears right after a routine load that "shouldn't have changed anything," so it's easy to wave off.

The classic source is a Slowly Changing Dimension that ended up with two "current" rows for one
key (see SCD Type-2 Without the Duplicate-Current-Row Footgun). But any non-unique join key —
a mapping table with a dupe, a dimension built from a fan-out of its own — does it.

How to catch it

1. Assert the grain. The join key must be unique on the dimension side. Make it a test:

   select join_key, count(*)
   from dim
   group by join_key
   having count(*) > 1;   -- must return zero rows

1. Watch the ratio. Compare a metric to a trusted baseline. A number that's suspiciously
   close to exactly 2× or 4× is a duplication fingerprint, not real growth.

2. Count rows across the join. If a fact table's row count jumps after a join step, the join
   is adding rows that shouldn't exist.

How to prevent it

• Guarantee uniqueness before you join. Dedupe the dimension to one row per key, or select the current version explicitly.
• Be defensive at read time. A view can enforce one row per key with qualify row_number() over (partition by key order by valid_from desc) = 1, so one upstream glitch can't fan out every metric downstream.
• Test the invariant in CI. A uniqueness test on the join key turns a silent production disaster into a failed build.

Takeaways

• A non-unique join key multiplies facts and inflates every aggregate built on them.
• Duplicate dimension rows are often invisible and total-preserving — don't trust eyeballing.
• Assert key uniqueness as a test, watch for round-number ratios, and dedupe before joining.

Comments

[Om Yaduvanshi]

the exactly-2x-or-4x fingerprint is the sharpest bit. same shape of bug shows up in event analytics too, a double-fired event looks exactly like a traffic spike until you check it against a second source. nothing errors, the numbers just get quietly bigger.