LookML — Explores, Joins & Persistent Derived Tables

Views are nouns; explores are verbs

A LookML view is a table. A LookML explore is a navigable graph of joined views — the unit of queryability. Business users never query a view directly; they query an explore.

explore: orders {
  label: "Order Facts"
  description: "Orders joined to customers, products and campaigns. The canonical revenue surface."

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

  access_filter: {
    field: customers.tenant_id
    user_attribute: tenant_id
  }
}

What an explore enforces

• Discoverability — users see exactly the dimensions and measures Looker exposes through this explore, not the raw tables.
• Join correctness — relationship: many_to_one is what lets Looker detect and warn on fanouts (the silent root cause of inflated SUM measures).
• Row-level security — access_filter ties a column to a Looker user attribute, so every query carries the filter automatically.

Persistent Derived Tables (PDTs)

When SQL costs spike, Looker's classic answer is the Persistent Derived Table — a query whose results are materialised in the warehouse on a schedule (daily, trigger-based, datagroup-based). PDTs are the LookML ancestor of Cube's pre-aggregations, and they live in your warehouse (not in Looker), which is both their power (unified storage) and their constraint (you pay for warehouse compute and storage).

A view is a room; an explore is a floor plan with doorways drawn in. Visitors don't wander between rooms looking for a passage — they walk the doorways the architect intended, and only into rooms their badge permits. The architect (LookML developer) decides which rooms can be entered from which others (joins), which rooms are off-limits (access_filter), and where the soundproofed VIP room is (PDT — pre-built, locked, very fast).