SQL Fanouts in Looker: Why Your Measures Are Wrong and How to Fix Them

You built a dashboard. The total revenue number looks right. Then someone joins a second table and suddenly revenue doubles. Welcome to the fanout problem — the most common source of incorrect data in Looker, and the hardest to detect because nothing actually breaks.

---

What Is a Fanout?

A fanout happens when a join creates duplicate rows. When you aggregate on those duplicated rows, your counts and sums are wrong.

Here's a concrete example.

Customers table:

id	name	visits
1	Amelia	2
2	Charles	2
3	Wilbur	4

Orders table:

id	customer_id	amount
1	1	25.00
2	1	50.00
3	2	75.00
4	3	100.00

If we join customers to orders:

SELECT *
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id

We get:

customer_name	visits	order_id	amount
Amelia	2	1	25.00
Amelia	2	2	50.00
Charles	2	3	75.00
Wilbur	4	4	100.00

Now SUM(visits) returns 10 instead of the correct 8. Amelia's 2 visits got counted twice because she has 2 orders. That's a fanout.

---

The Three Join Types and Their Risk

1. One-to-One (Safe)

One customer has one DNA record. No duplication possible.

explore: customers {
  join: dna_records {
    relationship: one_to_one
    sql_on: ${customers.id} = ${dna_records.customer_id} ;;
  }
}

2. Many-to-One (Safe)

Many orders belong to one customer. Starting from orders and joining customers won't duplicate order rows.

explore: orders {
  join: customers {
    relationship: many_to_one
    sql_on: ${orders.customer_id} = ${customers.id} ;;
  }
}

3. One-to-Many (Dangerous)

One customer has many orders. Starting from customers and joining orders will duplicate customer rows.

explore: customers {
  join: orders {
    relationship: one_to_many  # This causes fanouts on customer measures
    sql_on: ${customers.id} = ${orders.customer_id} ;;
  }
}

The rule: aggregations on the "one" side of a one-to-many join are almost always wrong.

---

How to Detect Fanouts

The simplest test: count rows before and after the join.

-- Before join
SELECT COUNT(*) FROM customers;  -- Returns 3

-- After join
SELECT COUNT(*) FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;  -- Returns 4

If the count increases, you have a fanout. In Looker, you can verify by:

1. Running a query with just customers.count — note the number
2. Adding any field from the joined orders table
3. If customers.count changes, you have a fanout

---

Three Patterns to Prevent Fanouts

Pattern 1: Start From the Right Base Table

The easiest fix is choosing the correct base table for your Explore. If you need order-level analysis with customer attributes, start from orders:

explore: orders {
  join: customers {
    type: left_outer
    relationship: many_to_one
    sql_on: ${orders.customer_id} = ${customers.id} ;;
  }
}

Now customer attributes are available without duplicating rows, because each order matches exactly one customer.

Pattern 2: Pre-Aggregate in dbt

When you need customer-level metrics alongside order-level metrics, pre-aggregate the order data in dbt:

-- dbt model: customer_order_summary
SELECT
  customer_id,
  COUNT(*) AS total_orders,
  SUM(amount) AS total_revenue,
  MIN(created_at) AS first_order_date,
  MAX(created_at) AS last_order_date
FROM orders
GROUP BY customer_id

Then join this summary table instead of the raw orders table:

explore: customers {
  join: customer_order_summary {
    type: left_outer
    relationship: one_to_one
    sql_on: ${customers.id} = ${customer_order_summary.customer_id} ;;
  }
}

One-to-one join, no fanout, correct aggregations.

Pattern 3: Use type: count_distinct Instead of type: count

If you must use a one-to-many join, at minimum use count_distinct instead of count:

measure: customer_count {
  type: count_distinct
  sql: ${customer_id} ;;
  # Instead of type: count, which counts all rows including duplicates
}

This won't fix SUM fanouts, but it fixes the most common incorrect metric.

---

Real-World Example: The Triple Fanout

We audited a Looker instance where an Explore joined customers → orders → order_items → shipments. Three one-to-many joins deep. The total_customers measure was 47x higher than the actual number.

Nobody noticed for months because the dashboard only showed percentage-based metrics — and the percentages happened to look reasonable.

The fix: We restructured into three Explores, each starting from the correct base table, with pre-aggregated summary tables for cross-domain metrics. Total audit and fix time: 2 days. The client had been building dashboards on wrong data for over a year.

---

A Decision Framework

Question	Answer	Action
Does the join increase row count?	No	Safe — no fanout
Does the join increase row count?	Yes	Fanout risk — check measures
Do you aggregate on the "one" side?	No	No impact even with fanout
Do you aggregate on the "one" side?	Yes	Fix required
Can you change the base table?	Yes	Pattern 1: Flip the Explore
Is the data available in dbt?	Yes	Pattern 2: Pre-aggregate
Neither option works?	—	Pattern 3: Use count_distinct + document the limitation

---

Key Takeaways

1. Fanouts are silent. Nothing breaks — numbers are just wrong.
2. Always verify row counts before and after joins during development.
3. Start from the "many" side when building Explores.
4. Pre-aggregate in dbt when you need metrics from both sides of a one-to-many relationship.
5. Declare relationships correctly in your LookML — many_to_one vs one_to_many matters for Looker's symmetric aggregation logic.

---

Labs4Change has trained 16,000+ analytics engineers on Looker. Book a free LookML audit to find out if your Explores have fanout issues.