LookML joins that inflate sums (fan-out)

For each explore with a join declared one_to_many or many_to_many, this rule checks the base view's measures. It flags sum, average, count and similar measures when the view has no primary key. It also flags a type: number measure that aggregates in its own SQL.

The total is right when the explore is queried alone. It is inflated as soon as a user adds a field from the joined view. Both numbers reach dashboards under the same name.

Stop one-to-many joins inflating Looker totals. Declare primary_key: yes on the base view's key, so Looker can apply symmetric aggregates. Rewrite a type: number measure as a native aggregate type, or pre-aggregate it in dbt.

Before:

view: orders {
  dimension: order_id {
    sql: ${TABLE}.order_id ;;
  }
  measure: total_revenue {
    type: number
    sql: sum(${TABLE}.amount) ;;
  }
}

After:

view: orders {
  dimension: order_id {
    primary_key: yes
    sql: ${TABLE}.order_id ;;
  }
  measure: total_revenue {
    type: sum
    sql: ${TABLE}.amount ;;
  }
}

A base view whose measures are all distinct aggregates, such as count_distinct or sum_distinct, is safe: a fan-out cannot inflate them, and we do not flag them. If the join is really many_to_one, correct the label instead.

The .lkml files alone. Only joins that declare one_to_many or many_to_many are examined. An undeclared or mislabelled join is not; the relationship rule covers that. A key possibly declared in an unsupplied file is not treated as missing.