Lesson 5 of 15 · Lab

Join fan-out: how joins inflate metrics

Fan-out is when a join multiplies rows because a key repeats in the lookup table, which inflates any total built on them. A safe lookup is many-to-one, with many rows joining to exactly one match. Reconcile groups to check a join. A total or row count can hide the damage.

Orders for customer 7, joined to a table that lists 7 twice
Orders inRows for 7 in lookupRows outTotal
3 orders, total 901 (many-to-one)390
3 orders, total 902 (fan-out)6180

A key missing from the lookup does the opposite. An inner join drops the row, and the total falls.

Sydney Swell in Finance at fictional Vandelay Coffee Supply joined 13 invoices to the customer register and got 13 rows back. Her €10,168 is still €1,310 short of the correct €11,478 (worked out below).

The total is €1,310 short with the row count intact. Is that one small error, or something else?

Something else. If rows went missing and the count still held, other rows must have doubled. Two large errors are pointing in opposite directions.

Reconcile by customer

  1. This live chart in Vista, Oplema's browser app for dashboards, opens on Sydney's lookup. Read each customer's bar.
  2. Select Repaired register.
  3. Compare. Two customers shouldn't move at all.
  4. Add up the changes. They should come to Sydney's €1,310.

What moves, and what stays put

Starhopper and Wonka don't move. Dunder Mifflin falls from €3,380 to €1,690, because the register lists it twice. Initech appears at €3,000, because it wasn't in the register. Take off €1,690 and add €3,000, and that's the whole gap.

A close total can still be wrong

Sydney's total looked plausible. It's closer than Don's, and it names the wrong region, because both errors landed on South. Dunder Mifflin's copy added €1,690 there, and losing Initech took away €3,000.

So reconcile by group, using whatever grouping your reader cares about. Here, four customers were enough.

Check the key before you join

Before any join, ask what one row of the lookup table is. That relationship is called cardinality, and it's usually one of three cases.

Cardinality in plain words
RelationshipExampleEffect on rows
Many-to-oneInvoices to customersSame count, the safe lookup
One-to-manyCustomers to invoicesMore rows, expected at the new grain
Many-to-manyInvoices to a register with repeatsFan-out, usually a fault

Use a left join when the first table defines what you're counting, so an unmatched row stays visible. Inner and left joins has the mechanics, including one trap in Strata, Oplema's browser app for data prep, where the Join block's Left and Right outputs hold only unmatched rows.

Common fan-out mistakes

  • Trusting the row count. Lost and doubled rows can cancel out, as they did here.
  • Summing after a one-to-many join. A customer's credit limit repeated on each invoice adds up to nonsense.
  • Assuming a duplicate is an error. Dunder Mifflin's two rows are two dated versions, and star schemas deal with that.

Fan-out in SQL and Excel

In SQL, GROUP BY customer_id HAVING COUNT(*) > 1 on the lookup table finds the repeats before they do harm. In Excel, VLOOKUP returns the first match, so it hides a repeated key rather than multiplying rows, while Power Query's Merge multiplies as SQL does. In Power BI, a one-to-many relationship needs unique values on the one side, so a repeat surfaces when you build the model.