Looker, in depth: LookML, SQL generation, symmetric aggregates, datagroups, PDTs and aggregate awareness

Architecture: a query compiler over your warehouse

A user in an Explore picks dimensions, measures and filters. Looker's query compiler reads the LookML model, works out which views and joins those fields need, and generates one SQL statement in the database's dialect. The database executes it, and Looker caches the result set under the policy the model assigns. Dashboards are collections of saved queries, and scheduled deliveries and the API go through the same path.

The consequences follow directly. Query performance is your warehouse's performance plus Looker's overhead. Warehouse cost is driven by how often queries miss the cache and how much data each one scans. Metric consistency comes from defining each measure once in LookML rather than in each dashboard. For the BigQuery side of that equation, see BigQuery in depth.

LookML building blocks

LookML has a small set of core objects. A view maps to a table or a derived query, and declares dimensions, which are row-level attributes that become GROUP BY columns, and measures, which are aggregates. An explore is a starting view plus the joins users may traverse from it. A model file sets the database connection and lists the explores it exposes.

# views/orders.view.lkml
view: orders {
  sql_table_name: `shop.orders` ;;

  dimension: order_id {
    primary_key: yes
    type: number
    sql: ${TABLE}.order_id ;;
  }
  dimension: customer_id { type: number  sql: ${TABLE}.customer_id ;; }
  dimension_group: created {
    type: time
    timeframes: [raw, date, week, month]
    sql: ${TABLE}.created_at ;;
  }
  measure: order_count { type: count }
  measure: revenue {
    type: sum
    sql: ${TABLE}.amount ;;
    value_format_name: usd
  }
}

# shop.model.lkml
explore: orders {
  join: customers {
    type: left_outer
    relationship: many_to_one
    sql_on: ${orders.customer_id} = ${customers.customer_id} ;;
  }
  join: order_items {
    type: left_outer
    relationship: one_to_many
    sql_on: ${orders.order_id} = ${order_items.order_id} ;;
  }
}

Two lines carry more weight than they look. primary_key: yes tells Looker how to identify a row, and relationship tells it the cardinality of each join. The compiler uses both to produce correct aggregates, as the next section shows. A query that selects only orders.created_date and orders.revenue generates SQL with no joins at all. Joins are added only when a selected field needs them, so a wide explore costs nothing until a user touches its fields.

Joins, fanout and symmetric aggregates

Joining order_items onto orders repeats each order row once per item. A naive SUM(orders.amount) over that result double-counts every multi-item order. This is the fanout problem, and hand-written SQL gets it wrong all the time. Concretely: order 1001 has an amount of 100 and three items. After the join there are three rows for order 1001, so a plain SUM returns 300 and a plain COUNT reports three orders where there is one.

Looker handles it with symmetric aggregates. When a query includes a join that fans out the view a measure belongs to, the compiler renders sum, count and average as their distinct forms, keyed on the primary key. Each order then contributes its amount exactly once, however many item rows accompany it. In the example, Looker sums the amount once per distinct order_id and returns 100. This only works if the primary key is truly unique. A duplicated key silently produces wrong totals, so test key uniqueness as part of development.

Symmetric aggregates cost more SQL work than plain ones, and they matter for aggregate tables too. A fanned-out query is rendered with distinct aggregates, and that can stop Looker from answering it from a pre-built aggregate table, which the aggregate awareness section explains. Declaring relationship accurately is therefore both a correctness and a cost decision.

Caching, datagroups and PDTs

Looker caches query results, and a datagroup defines when those results go stale. A datagroup can set max_cache_age, the longest a result may be served, and sql_trigger, a query that returns one row and one column. When that value changes, the datagroup is triggered: cached results are invalidated and dependent persistent tables are rebuilt. Looker runs the trigger on the schedule set in the connection's datagroup and PDT maintenance setting. The model or explore opts in with persist_with.

datagroup: etl_daily {
  sql_trigger: SELECT MAX(loaded_at) FROM shop.etl_log ;;
  max_cache_age: "24 hours"
}

persist_with: etl_daily

Tying the trigger to the ETL log's load timestamp means the cache is reused all day and invalidated as soon as new data lands. Dashboards are neither stale nor re-scanning unchanged tables.

Per the documentation, cached results are used only if every aspect of the query is the same, including fields, filters, parameters and row limits. A per-user access_filter adds a filter, so dashboards with per-user row filters get one cache entry per tile per distinct attribute value, which is worth knowing when you estimate hit rates.

Persistent derived tables (PDTs) are views defined by a query whose results Looker writes into a scratch schema in your database and rebuilds on a schedule. The documentation flags a trap here: a PDT assigned to a datagroup that has only max_cache_age is built on first use and then never rebuilt. To rebuild on a timetable rather than on a data change, add interval_trigger to the datagroup. Give the scratch schema its own dataset, quota and cleanup policy, because it fills with rebuild generations.

Aggregate awareness

Aggregate awareness lets Looker answer a query from a smaller pre-aggregated table when the result would be identical. You declare aggregate_table on an explore with the query it covers and how it is materialised. Looker then routes any compatible query to it automatically.

explore: orders {
  aggregate_table: daily_sales_by_region {
    query: {
      dimensions: [orders.created_date, customers.region]
      measures: [orders.revenue, orders.order_count]
      timezone: "UTC"
    }
    materialization: {
      datagroup_trigger: etl_daily
    }
  }
}

Materialisation takes datagroup_trigger, sql_trigger_value or the less recommended persist_for, and the table must be persisted to be usable. Because the table is daily, a query for monthly revenue by region can be rolled up from it. That only works for measures that roll up correctly. Per the documentation, sum, count, min, max and average are supported, while count_distinct, median and percentile fall back to the base table. The exception is an exact match, where the query and the aggregate table use the same fields, filters and timezone.

Set the timezone explicitly. An aggregate table with no timezone does no conversion and uses the database timezone, and a mismatch with users' query timezone stops the table from matching. To confirm a hit, open the SQL tab in development mode: Looker leaves a comment such as use existing orders::daily_sales_by_region when it used the table, or explains why it did not.

Row-level access

Looker connects to the database with its own credentials, so row-level security has to be expressed in the model or in the database. In the model, access_filter adds a filter to every query on an explore based on a user attribute:

explore: orders {
  access_filter: {
    field: customers.region
    user_attribute: allowed_region
  }
}

A user whose allowed_region attribute is EMEA can then only ever see EMEA rows, whatever they select. User attributes are typically synced from groups in your identity provider. Combine this with model sets and roles that limit which explores a group can open, and keep the database service account's grants narrow, because that account bounds what any model mistake can expose. On Google Cloud, manage those grants with IAM like any other workload identity.

Development workflow

A LookML project is a git repository. Each developer works in development mode on a personal branch, sees their changes only in their own session, and deploys to production by merging. Treat it like application code:

• Run the LookML validator on every change; it catches broken references before users do.
• Run the content validator before deploying renames, because saved Looks and dashboards reference fields by name.
• Write LookML data tests that assert invariants, such as primary key uniqueness or a known total for a closed month.
• Require pull requests for production, and review join relationships as carefully as SQL.

Worked example: cutting a dashboard's BigQuery bill

A sales dashboard on BigQuery has twelve tiles, is opened about 400 times a day, and every tile scans a 1.5 TB orders table partitioned by date. With no persistence policy, Looker's documented default cache retention is one hour, so a busy dashboard re-runs each tile about once an hour. Suppose partition filters already cut each tile to about 50 GB scanned. That is 12 × 24 × 50 GB, roughly 14 TB scanned a day, for data that changes once. BigQuery's own result cache does not rescue this when the generated SQL contains relative dates such as today, which make queries ineligible for it. Three changes fix it.

1. Add the etl_daily datagroup and persist_with. The data loads once a day, so after the first viewer each tile is served from cache until the next load: one scan per tile per day, about 600 GB, instead of about 14 TB.
2. Add the daily_sales_by_region aggregate table. It holds one row per day per region, megabytes instead of terabytes, so the first viewer after a load is fast too, and month and quarter tiles roll up from it.
3. Make sure tile filters on orders.created_date reach the partition column, so that queries the aggregate cannot serve, such as count_distinct of customers, still prune partitions. See BigQuery partitioning and clustering.

Check the result in the SQL tab and in BigQuery's job history: bytes billed per day should fall by orders of magnitude. If interactive latency still matters after that, BI Engine can accelerate the queries that remain.

Failure modes

• Wrong totals after a join. A missing or non-unique primary key, or a wrong relationship, defeats symmetric aggregates.
• PDTs that never refresh. The datagroup has only max_cache_age. Add a trigger.
• Aggregate tables that are never hit. Timezone mismatch, unsupported measure types, extra filters or fanned-out joins. Read the SQL tab comment.
• Trigger queries that cost money. A sql_trigger that scans a large table every maintenance cycle. Point it at a small log table.
• Broken content after a rename. Deployed without running the content validator.
• Data leaking across tenants. Security enforced only in dashboard filters rather than with access_filter or database policies.

Trade-offs

In-database versus extracts. Running everything live keeps data fresh and governed in one place, but it makes the warehouse the bottleneck and the bill. Caching and aggregate tables are how you buy back extract-like economics.

Central model versus agility. A governed LookML model gives one definition of revenue, but changes go through code review. Teams that want ad hoc metrics feel that friction. Splitting models by domain, with shared views, is the usual compromise.

PDTs versus warehouse transformations. PDTs are convenient, but they hide transformation logic inside the BI layer. Logic other tools need belongs in the warehouse's transformation pipeline. Keep PDTs for presentation-specific shapes.

What to do next

1. Check that every view has a unique primary key and every join an accurate relationship.
2. Define datagroups whose sql_trigger follows your ETL completion, and apply them with persist_with.
3. Audit PDT datagroups for max_cache_age-only policies and add sql_trigger or interval_trigger.
4. Add aggregate tables for your busiest dashboards, with explicit timezones, and confirm hits in the SQL tab.
5. Enforce row-level access with access_filter and user attributes, not dashboard filters.
6. Run the LookML and content validators and data tests on every pull request.
7. Track warehouse bytes billed by Looker's service account per day as a cost KPI.