Best practice Revenue
Preventing join fanout by declaring every relationship
Why do totals go up after a join?
The rule
Trust: InferredBefore you sum or count across a join, know how many rows on each side match. If one row on your side can match many on the other, aggregate the many side first, or declare the relationship so your tool can correct for it. Do not rely on a default you have not checked.
Why it matters
Trust: InferredEvery join should say how many rows on each side match. Without relationship, Looker assumes many_to_one. If the join is really one-to-many, sums and counts on the base view are multiplied by the number of matching rows.
A sum on the base view repeats once per matching row. Symmetric aggregates can correct it only when the joined views declare unique primary keys and the relationship is declared.
| Query | Rows after join | Reported messages sent | True value |
|---|---|---|---|
| user_metrics alone | 1 | 12 | 12 |
| joined to user_tenure_metrics (if its fields were exposed) | 3 | 36 | 12 |
Trust: Suggested Illustrative numbers, not from the source.
How to check your own model
Trust: Suggested- For every join, write down the relationship you expect and check that the join key is unique on the joined side.
- Record the relationship where your tool allows it (
relationshipin LookML, a uniqueness test in dbt, a comment in plain SQL), even when the default is right. - Make sure both sides have a unique primary key. Fanout corrections such as symmetric aggregates depend on it.
- Run a base-table count with and without the join. If it changes, the join fans out.
A safer shape
Aggregate the many side before you join
Trust: SuggestedBring items to one row per order first, then join. Each order total is counted once. For a breakdown by product category, sum item sales instead: an order total has no single category.
Show the suggested SQL: order totals without fanout
WITH items AS ( SELECT ti_orderid, SUM(ti_price) AS sales_value FROM transaction_items -- the derived table defined in the source GROUP BY ti_orderid)SELECT SUM(t.tr_total) AS order_value, SUM(i.sales_value) AS item_sales_valueFROM transactions AS t -- the derived table defined in the sourceLEFT JOIN items AS i ON i.ti_orderid = t.tr_orderidThe same fix in LookML
Trust: SuggestedIn LookML, write the relationship down even though it matches the default, so the reason these totals are right survives the next edit to the join.
Show the suggested LookML: event_tracker.model.lookml
- explore: transaction_items joins: - join: transactions sql_foreign_key: transaction_items.ti_orderid relationship: many_to_oneWhere it shows up in real projects 4
enterprise: Tenure rows joined by user id
Trust: Source-defineduser_tenure_metrics has one row per user per tenure day, yet the tests explore joins it by sql_foreign_key: user_metrics.id with no relationship. fields: [] keeps it out of queries today; exposing any field would triple every user_metrics measure.
Source: enterprise.model.lookml L65-67
- join: tests from: experiments sql_foreign_key: user_experiment_buckets.experiment_id - join: user_tenure_metrics sql_foreign_key: user_metrics.id fields: [] - join: users from: users sql_foreign_key: user_metrics.iddating-platform, enterprise.model.lookml, lines 65-67
propositions: Many messages per proposition
Trust: Source-definedThe propositions explore joins messages on proposition_id. fields: [] hides message fields, but message_response_type declares required_joins: messages, so the join can still enter queries.
Source: proposals.view.lookml L121-126
type: int hidden: true sql: ${TABLE}.user_id - dimension: message_response_type type: string hidden: true sql: messages.type required_joins: - messages - dimension: template_name type: string sql: ${TABLE}.template_namedating-platform, proposals.view.lookml, lines 121-126
churn: Fanout inside a derived table
Trust: InferredThe downgrades branch joins each subscription to every upgrade transaction of its user. A user with three upgrades contributes three downgrades.
Source: membership_activity.view.lookml L22-38
U.source, trunc(U.created_at) UNION ALL SELECT U.site, U.source, TRUNC(S.next_renewal_on) AS calendar_date, 0, COUNT(1) AS downgrades, 0, 0 AS renewals FROM subscriptions S JOIN users U ON S.user_id = U.id JOIN user_transactions T ON S.user_id = T.user_id AND T.type = 'upgrade' WHERE S.auto_renew = false AND S.next_renewal_on < GETDATE() GROUP BY U.site, U.source, trunc(S.next_renewal_on) UNION ALL SELECT U.site, U.source,dating-platform, membership_activity.view.lookml, lines 22-38
transaction_items: A correct default nobody recorded
Trust: InferredItems join orders by transaction_items.ti_orderid. Items to orders is many-to-one, so Looker's default is right, and the primary key on transactions.order_id lets it sum each order total once, provided that key is unique: the derived table also groups by user and session, so it is not guaranteed. Nothing records the decision, and the same question asked in plain SQL fans out.
Source: event_tracker.model.lookml L21-22
- explore: transactions- explore: transaction_items joins: - join: transactions sql_foreign_key: transaction_items.ti_orderid- explore: session_appsdating-platform, event_tracker.model.lookml, lines 21-22
The SQL this produces
Asked in plain SQL, order value by product category adds every order total once per matching item. Looker avoids it here only because of the default relationship and the primary key.
Show the SQL: order totals by product category
SELECT transaction_items.ti_category AS product_category, -- caution: SUM of transactions.transactions_value may be multiplied by join transactions (no relationship declared) SUM(transactions.tr_total) AS transactions_value, -- caution: SUM of transaction_items.sales_value may be multiplied by join transactions (no relationship declared) SUM(transaction_items.ti_price) AS sales_valueFROM transaction_items -- derived table, defined in the sourceLEFT JOIN transactions -- derived table, defined in the source ON transaction_items.ti_orderid = transactions.tr_orderidGROUP BY 1ORDER BY transactions_value DESCSuggested query. Fields translated to Redshift SQL from event_tracker.model.lookml L19-22, purchase_items.view.lookml L21-22, L27-30, L42-44 and event_transactions.view.lookml L32-34, L37-40, L65-67. Not run against a warehouse.