Aggregate Awareness with aggregate_table: The Biggest Performance Win You Are Not Using

Most Looker dashboards ask the same few questions of the same few tables: revenue by month, orders by status, sessions by channel. Every one of those queries hits the fact table, scans the same partitions, and produces a rollup that could have been computed once. Aggregate awareness lets you define that rollup in LookML with aggregate_table, and Looker then rewrites matching queries to use it, transparently, with no change to dashboards or to what users click. On a large BigQuery or Snowflake fact table it is routinely the biggest performance and cost win available, and it is the feature we most often find unused in projects we audit.

How it works

You declare one or more aggregate_table blocks inside an explore. Each describes a query (dimensions, measures, filters) and a materialization strategy. Looker builds the aggregate as a PDT. When a user runs a query against that explore, Looker checks whether the query can be answered from any aggregate table; if so, the generated SQL targets the small rollup instead of the base table. The SQL tab tells you which table was used and, if none, why not.

Defining a rollup

Given an orders explore on a large fact table:

datagroup: orders_daily {
  sql_trigger: SELECT MAX(loaded_at) FROM `proj.ds.orders` ;;
  max_cache_age: "24 hours"
}

explore: orders {
  persist_with: orders_daily

  join: users {
    sql_on: ${orders.user_id} = ${users.id} ;;
    relationship: many_to_one
  }

  aggregate_table: rollup__created_date__status__country {
    query: {
      dimensions: [orders.created_date, orders.status, users.country]
      measures: [orders.count, orders.total_revenue]
      filters: [orders.created_date: "2 years"]
      timezone: "America/Chicago"
    }
    materialization: {
      datagroup_trigger: orders_daily
      partition_keys: ["orders.created_date"]
      cluster_keys: ["orders.status"]
    }
  }
}

The rollup is keyed by day, status and country, stores the two measures, and covers the last two years. partition_keys and cluster_keys make the aggregate itself prunable on BigQuery (use indexes on warehouses that support them). Set timezone explicitly; date rollups built in one timezone will not match queries run in another.

Which queries match

Looker uses an aggregate table when all of these hold:

• Every dimension in the query is in the aggregate's dimensions, or is a coarser timeframe of one that is (a query by created_month matches a rollup by created_date, because Looker can re-aggregate days into months).
• Every measure in the query is in the aggregate's measures, or can be derived from them. Sums and counts roll up; averages work only if the aggregate also contains the underlying sum and count (Looker handles type: average by storing both). count_distinct does not roll up across dimensions, so a distinct count of users by day cannot serve a query by month; leave distinct counts out, or use a rollup at exactly the grain you need.
• Every filter in the query is on a dimension the aggregate contains, and the aggregate's own filters are a superset of the query's range (a query for "last 90 days" matches a rollup filtered to "2 years"; a query for "5 years" does not).
• The query's timezone matches the aggregate's.
• The query does not use features the aggregate cannot represent (sql_always_where with Liquid that varies by user, certain filtered measures, fields with required_access_grants that differ).

When a query does not match, Looker falls back to the base table and explains why in an SQL comment, which makes iteration fast: run the dashboard's query in an Explore, open the SQL tab, read the comment, adjust the rollup.

Designing a small set of rollups

Resist defining one aggregate per dashboard. Instead:

1. Pull the top twenty queries by runtime and frequency from System Activity's History explore for the target explore.
2. Group them by the set of dimensions they use. Usually three or four rollups cover most of the volume: a date-only rollup, a date-plus-status rollup, a date-plus-geography rollup.
3. Include the union of measures the grouped queries need in each rollup (storing an extra sum is cheap).
4. Keep count_distinct measures out of wide rollups.

Rollups at coarse grains (daily) are small; a two-year daily rollup by status and country is a few hundred thousand rows against a fact table with hundreds of millions. That size difference is the whole win.

Refresh strategy

Tie the materialization to the same datagroup as the explore so the rollup rebuilds once per source load, right after the cache clears. For large fact tables use incremental aggregates:

  aggregate_table: rollup__created_date__status {
    query: { ... }
    materialization: {
      datagroup_trigger: orders_daily
      increment_key: "created_date"
      increment_offset: 3
    }
  }

increment_key rebuilds only the most recent partitions (plus increment_offset days of late-arriving data) instead of the whole two-year window.

Measuring the win

Before enabling the rollup, record for the target dashboard: median runtime per tile, bytes scanned (BigQuery) or warehouse credits (Snowflake), and the History explore's Runtime and Query Count for the explore over a week. After the aggregate is built and the datagroup has cycled:

• SQL tab: confirm the dashboard tiles reference the aggregate PDT name.
• System Activity > History: filter to the explore and compare average runtime by day before and after.
• PDT Event Log: check the aggregate's build time; if a rebuild takes longer than the queries it saves, narrow the rollup or make it incremental.
• Warehouse billing: bytes billed or slot-milliseconds for the dashboard's queries, joined from INFORMATION_SCHEMA.JOBS via Looker's query labels.

A typical outcome on a large BigQuery fact table is tile runtimes dropping from tens of seconds to under two, and bytes scanned per dashboard view dropping by more than an order of magnitude. Measure yours; the numbers are what convinces the people paying the bill.

Common pitfalls

• Rollup never used: usually a timezone mismatch, a count_distinct, or a filter on a dimension the rollup lacks. Read the SQL comment.
• Stale numbers: the aggregate's datagroup is different from the explore's. Use the same one.
• Rebuild failures: the PDT scratch schema lacks permissions for partitioned tables; check the PDT Event Log.
• Too many rollups: each is a PDT with its own build; consolidate.

Aggregate awareness is one of the first things we implement in a performance rescue and one of the first things we check in a Looker health check. If your dashboards are slow and your warehouse bill is not, this is probably why. Contact us.