Join tables in Looker

In this chapter, we are going to learn how to join tables in Looker using LookML in the data model. By joining views under the same Explore, we can make dashboard tiles that combine fields from related tables and ask cross-table business questions.

First, we will get to know the use case dataset that we are going to work with for our examples and demonstration. This is the last chapter in our five-part Looker 101 series.

Before you start, you need a Looker project with a working BigQuery connection and views for the four tables below. You also need developer access to edit LookML and permission to edit the dashboard. Work in Development Mode. This chapter covers Looker, not Looker Studio. Original screenshots show the historical sample and classic interface; new real Looker captures are separately labelled as synthetic-data demonstrations.

Commercial magical pony farming use case overview

In order to demonstrate joins in Looker, we are going to go through a use case of a commercial magical pony farm. Here is the entity relationship diagram (ERD) showing the relevant tables, fields, types and relationships:

(opens in a new tab)

Let’s go over the tables themselves in BigQuery, briefly. pony_fact is our main table: the original sample contains 15,000 ponies, with each pony’s name, height, weight, color and ID. Verify that this table has exactly one row per non-NULL PONY_ID.

(opens in a new tab)

pony_treatment_hist contains treatment history: the pony ID tells us which pony received a treatment, together with its type and date. The same pony can appear in many treatment rows, so PONY_ID is not a unique key for this table.

(opens in a new tab)

pony_rltn contains directed relationships: what the pony identified by SOURCE_PONY_ID feels towards the pony identified by TARGET_PONY_ID, described by RELATIONSHIP_TYPE and INTENSITY. A source pony can have several relationship rows.

(opens in a new tab)

pony_power contains power assignments: the pony ID, the power ID and the power name. One pony can have more than one power, and the same power type can belong to many ponies.

(opens in a new tab)

Joins

Now that we are familiar with the magical pony farm dataset, let’s go over to Looker. Connecting a database does not automatically model joins. In this project, the generated model initially exposes each view as a separate Explore. We will join the views under one Explore so that dashboard tiles can combine their fields.

I already created a project with a connection to the magical pony farm dataset in BigQuery and a dashboard named Joins_Pony_Farm. We learned to do this in previous chapters.

(opens in a new tab)

Edit the dashboard, then choose Add → Visualization. Under Commercial Magical Pony Farm, the original model lists Pony Fact, Pony Power, Pony Rltn and Pony Treatment Hist as separate Explores.

Let’s say we want a graph of the number of ponies that received at least one treatment, grouped by color. Color and treatment data are in different views: pony_fact and pony_treatment_hist. Selecting the standalone Pony Fact Explore gives us Color but not the treatment fields; selecting Pony Treatment Hist presents the reverse problem.

To make the join, go to the project’s LookML. From a relevant Explore, select Go to LookML. Switch to Development Mode before editing.

Open the model file. In the original project, each view is initially defined as an Explore. Check that the model includes the view files you want to join. An existing include such as include: "/views/**/*.view" is valid; the .view.lkml form is equivalent.

(opens in a new tab)

Our base view is pony_fact. Inside its Explore’s curly braces, declare a join to pony_treatment_hist:

(opens in a new tab)

First set the join type, as in SQL. We want left_outer: start with the ponies in Pony Fact and keep them even when they have no matching treatment. A filter on a joined field can still exclude those unmatched rows, so check the filters as well as the join type.

(opens in a new tab)

Next, set relationship. It describes cardinality from the base view to the joined view. Because one pony can have several treatment records, this join is one_to_many. Do not copy the original tutorial’s many_to_one setting. The relationship describes the data; it does not remove duplicates or create a database constraint.

Finally, sql_on supplies the matching condition. It does not declare a primary key. Here, the primary key in Pony Fact matches the repeated pony reference in treatment history. Remember the double semicolons:

explore: pony_fact {
  join: pony_treatment_hist {
    type: left_outer
    relationship: one_to_many
    sql_on: ${pony_fact.pony_id} = ${pony_treatment_hist.pony_id} ;;
  }
}

In the Pony Fact view, mark the ID dimension as a primary key only after checking that it is unique and non-NULL. For example, if the underlying column is PONY_ID:

dimension: pony_id {
  primary_key: yes
  type: number
  sql: ${TABLE}.PONY_ID ;;
}

Do not mark the child tables’ repeated Pony IDs (or SOURCE_PONY_ID) as primary keys. For event-level measures, each child table needs its own stable unique row key. The original treatment schema does not supply a treatment-event ID, so do not assume that pony, date and treatment type are a unique combination without checking.

Click Save Changes, validate LookML and reopen the Explore in Development Mode. Saving does not deploy changes for production users. Test first, then follow the project’s normal commit and deployment process.

When we return to the dashboard’s tile editor and select the Pony Fact Explore in the same mode, the Pony Treatment Hist fields should now be available alongside Pony Fact.

Now select Color from Pony Fact and create a custom Count distinct measure on Pony ID in Pony Treatment Hist, using its three-dot field menu → Aggregate. This counts treated ponies: a pony with five treatment records still contributes one. Ponies with no matching treatment do not contribute because their joined ID is NULL.

(opens in a new tab)

Run the query and save a tile titled Treated Ponies by Color. The original label “Treatments per Color” was misleading: a distinct count of Pony ID does not count treatment events. If you want event counts instead, count a verified unique treatment-event ID. A raw row count can overcount after joins.

For a reusable measure, add the following inside the existing pony_treatment_hist view instead of relying on an ad hoc custom field:

measure: treated_ponies {
  type: count_distinct
  sql: ${pony_id} ;;
}

Another useful change is to give the combined Explore a clearer display label. Go back through Go to LookML, or choose Develop and open the project from the navigation menu.

Inside the existing Explore block, add label: "Pony Facts Explore", using double quotes, then save. This changes the displayed label; the Explore’s internal name remains pony_fact, and the Pony Fact view label does not change.

# Add inside the existing explore: pony_fact block:
label: "Pony Facts Explore"

(opens in a new tab)

In this use case we also want the power and relationship views available in the same Explore. Each can have multiple rows for one base pony, so each direct join is one_to_many. Copy the structure of the corrected treatment join and change its view and field references. Join powers on pony_id, and outgoing relationships on source_pony_id:

explore: pony_fact {
  label: "Pony Facts Explore"

  join: pony_treatment_hist {
    type: left_outer
    relationship: one_to_many
    sql_on: ${pony_fact.pony_id} = ${pony_treatment_hist.pony_id} ;;
  }
  join: pony_power {
    type: left_outer
    relationship: one_to_many
    sql_on: ${pony_fact.pony_id} = ${pony_power.pony_id} ;;
  }
  join: pony_rltn {
    type: left_outer
    relationship: one_to_many
    sql_on: ${pony_fact.pony_id} = ${pony_rltn.source_pony_id} ;;
  }
}

(opens in a new tab)

The relationship join describes outgoing relationships only. To inspect the target pony’s own attributes, add a separate alias of Pony Fact using from: pony_fact and match its ID to pony_rltn.target_pony_id; that lookup is many-to-one from the relationship row to the target pony. Avoid treating source and target as interchangeable.

Joining several one-to-many tables can multiply rows when their fields are used together. For example, one pony with two treatments, two powers and two outgoing relationships can contribute eight joined rows. Looker uses declared relationships and valid primary keys to protect supported measures through symmetric aggregates; correct keys and metric definitions still matter. Looker normally adds only the joins required by a query.

(opens in a new tab)

Check the generated SQL and compare each measure with its source-table aggregation before adding more joined dimensions. Counting distinct pony IDs protects this particular pony-count metric against repeated child rows. It does not make every sum, average or event count safe, and a pony may legitimately appear in more than one group when grouping by a multi-valued child attribute.

You do not need to delete the standalone Explores. Existing Looks, tiles and shared URLs may depend on them. If the goal is to simplify the menu, consider hidden: yes on the standalone Explore while retaining its definition; hiding is a presentation choice, not access control. Use Content Validator and review dependencies before removing an Explore.

# Optional: retain this Explore for existing content, but hide it in the menu.
explore: pony_power {
  hidden: yes
}

After saving and validating, the combined Pony Facts Explore should show all four view sections. The original field-picker example illustrates that organization:

Let’s finish with another dashboard tile. Suppose we want the number of ponies with at least one power, grouped by the food they eat. Add a tile, select Pony Facts Explore, choose Food from Pony Fact and make a custom Count distinct measure on Pony ID from Pony Power.

(opens in a new tab)

Alternatively, put this reusable definition inside pony_power:

measure: ponies_with_powers {
  type: count_distinct
  sql: ${pony_id} ;;
}

Run and save a bar-chart tile titled Ponies with Powers by Food. This counts ponies with power assignments, not the number of powers or unique power types. A pony with several powers counts once per food group. The chart is descriptive; different group sizes can drive the counts, so it does not establish that food causes powers.

(opens in a new tab)

Before deploying, check both queries with known examples: ponies with no child rows, ponies with several treatments or powers, and combined child-table selections. Confirm the keys, zero counts and distinct counts, validate LookML and existing content, then deploy through the normal project workflow. Save each tile and the dashboard separately where required.

This sums up this chapter and our Looker 101 Introduction series. Hope you enjoyed it and found it helpful!

All Looker 101 chapters→