How Do Join Relationships Work in Looker Models?

Defining the right join relationships in your Looker model is the foundation for accurate and flexible data exploration. Get it wrong, and you can end up with incorrect counts and misleading metrics. This guide will walk you through how join relationships work in LookML, explaining the concepts you need to build reliable data models for your team.

What Are Joins and Why Do They Matter in Looker?

At its core, a join is a database operation that combines rows from two or more tables based on a related column, often called a "foreign key". Think of it like connecting two spreadsheets. One sheet has a list of customers with a customer_id, and another has a list of orders, also with a customer_id. A join allows you to link these two sets of data to see which customer placed which order.

In Looker, you define these connections within the "model" part of your LookML project, specifically inside an explore block. A well-defined explore tells Looker how different views (which represent your database tables) relate to each other. This is what allows business users to drag and drop dimensions and measures from multiple views into a report and get a single, accurate result without having to write any SQL themselves.

Key Components of a Looker Join

When you add a join to an explore in Looker, you're primarily working with a few key parameters that tell Looker how to stitch the data together. Let's break them down.

join: <view_name>

This is the first line of any join. You start by declaring which view you want to connect to your base view (the view listed in the explore parameter).

type: <join_type>

The join type determines how Looker handles records that don’t have a match in the other view. While there are a few types, you'll use left_outer most of the time, which is Looker's default.

• left_outer (default): Keeps all records from the base (left) view and only the matching records from the new (right) view. If there’s no match for a record from the left, the fields from the right view will be null. This is the safest and most common type.
• inner: Only includes records where there is a match in both views. If you're joining users and orders, an inner join would exclude any users who haven't placed an order.
• full_outer: Includes all records from both views, regardless of whether they have a match. This is less common and can generate very large result sets.
• cross: Matches every row from the base view with every row from the joined view. Use this with extreme caution, as it can create computationally expensive queries.

relationship: <relationship_type>

This is arguably the most important parameter in a Looker join. It describes the relationship between the two views you are joining. It not only clarifies the model but also enables one of Looker's most powerful features: symmetric aggregates.

This parameter tells Looker how to avoid incorrect calculations, especially when dealing with one-to-many relationships that can cause data "fanouts." We'll cover this in depth in the next section.

sql_on: <sql_condition> or foreign_key: <field_name>

This tells Looker which columns to use for the join. You have two options:

• sql_on: The explicit method. You write the exact SQL condition for the join.
• foreign_key: A convenient shortcut. You use this when you are joining a view based on its primary key. Looker assumes you're joining the foreign key in your base view to the primary key of the joined view. This is cleaner and often used in conjunction with "extends" or in simple join cases. For this to work best, the primary_key needs to be defined in the view you are joining.

Understanding the Four Types of Join Relationships

Defining the relationship correctly is what separates a frustrating Looker experience from a seamless one. It tells Looker how to handle aggregations (like SUMs, or COUNTs) correctly so a business user can't accidentally break a report.

many_to_one

This is one of the most common and safest relationships. It means that many rows in your base view can be associated with one row in the joined view.

• When to use it: When your explore base is a transactional table, and you are joining to a lookup or dimension table.
• Example: Your explore base is orders, and you are joining to the users view. Many orders can belong to one user. Thus, from the perspective of the orders explore, the relationship to users is many-to-one.

explore: orders {
  join: users {
    type: left_outer
    relationship: many_to_one
    sql_on: ${orders.user_id} = ${users.id}
  }
}

This is safe because adding dimensions from the users view (like user's name or city) will not change the grain of the orders table. Your count of orders will remain accurate.

one_to_many

This relationship is where Looker's real power shines. It means that one row in your base view can be associated with many rows in the joined view.

• When to use it: When your explore base is a dimension table, and you're joining a transactional table to it.
• Example: Your explore base is users, and you are joining orders. One user can have many orders. So, from the users explore perspective, the relationship is one-to-many.

explore: users {
  join: orders {
    type: left_outer
    relationship: one_to_many
    sql_on: ${users.id} = ${orders.user_id}
  }
}

This is where symmetric aggregates come in. If you just did a standard SQL join here and tried to calculate something like Total Revenue (which lives in the orders view), it would work fine. But if you tried to bring in a measure from the users table, like User Count, a simple join would multiply that user for every order they have, leading to a wildly incorrect count. By declaring the relationship as one_to_many (and ensuring your primary keys are defined), you signal Looker to use a special SQL pattern that performs calculations correctly, preventing these fanouts and ensuring metrics are always trustworthy.

one_to_one

This is the simplest relationship. It signifies that for every one row in your base view, there is exactly one matching row in the joined view.

• When to use it: This is often used to join supplementary data.
• Example: You have a users table and a separate user_details table that contains extra information like bio or phone_number. Each user has only one set of details.

explore: users {
  join: user_details {
    type: left_outer
    relationship: one_to_one
    sql_on: ${users.id} = ${user_details.user_id}
  }
}

These joins are very safe and will not cause any fanouts or incorrect aggregations.

many_to_many

This is the most complex relationship and requires a third table, known as a "join table" or "bridge table," to function properly.

• When to use it: When an item from the base view can relate to many items in the joined view, and vice versa.
• Example: An orders and products relationship. One order can contain many products, and one product can be part of many different orders. You would model this through an order_items join table, which has both an order_id and a product_id.

To model this in Looker, you would chain together a one_to_many and a many_to_one join through your intermediary table.

explore: orders {
  join: order_items {
    type: left_outer
    relationship: one_to_many
    sql_on: ${orders.id} = ${order_items.order_id}
  }

  join: products {
    type: left_outer
    sql_on: ${order_items.product_id} = ${products.id}
  }
}

Notice we don't declare the relationship in the final products join directly in this model. Looker is smart enough to infer the path through the intermediary order_items table. To make many_to_many work seamlessly, it's very important to have primary keys defined on all your views.

Best Practices for Looker Joins

Following a few simple guidelines will keep your LookML clean and your data accurate.

• Be Explicit with Relationships: Always define the relationship parameter. Don't leave it to guesswork. This is the key to leveraging symmetric aggregates and building a model your team can trust.
• Define Primary Keys: Go into each of your view files and declare a primary_key: yes on the unique identifier for that table (e.g., id, user_id). This is what enables symmetric aggregates to function correctly for one_to_many and many_to_many joins.
• Join from the "Many" Side When Possible: If you are looking at transactional data, it's often best to make the transactional table (like orders) your explore's base view. Then you can use safe many_to_one joins to bring in dimensional data from tables like users or products.
• Start Small and Test: Build one join at a time and validate the results in an Explore. If something looks off, use the "SQL" tab in your data panel to see the generated query. This can help you debug if you've chosen the wrong join condition or relationship.
• Use Clean Naming Conventions: Use consistent foreign key names (e.g., user_id, product_id) to make joins more intuitive and enable the use of the foreign_key: shortcut.

Final Thoughts

Mastering joins is what transforms Looker from a simple query tool into a powerful business intelligence platform. By carefully defining your view relationships - especially many-to-one and one_to_many - you empower your team to ask complex questions of the data with confidence, knowing the metrics are both reliable and accurate.

While Looker is fantastic for data-savvy teams to build these detailed models, the challenge often remains in making that data accessible to everyone else. At Graphed, we address this by connecting directly to your raw data sources like Google Analytics, Salesforce, and Shopify. This allows marketing, sales, and leadership teams to create real-time dashboards and get instant answers simply by asking questions in plain English, with no technical expertise or predefined Looker models required.