Question

A retail analytics team uses Looker on top of a very large BigQuery orders table (billions of rows). Most dashboards query high-level rollups such as daily revenue by region, but a few Explores still need row-level detail. Dashboards are slow and expensive because every tile scans the full detail table. The team wants Looker to automatically route summary queries to smaller pre-aggregated tables while still allowing drill-down to raw rows, without asking users to pick a different Explore. What should they implement?

Accepted Answer

Define aggregate_table specifications with aggregate awareness in the LookML Explore so Looker automatically rewrites qualifying queries to the smaller rollup tables and falls back to the detail table for drill-down.

Answer

Create a separate Explore for each aggregation level and instruct analysts to manually choose the correct Explore for their query grain.

Answer

Enable BigQuery BI Engine reservation on the project so all Looker queries are served from in-memory cache regardless of grain.

Answer

Replace the detail table with a single materialized view aggregated to daily-region grain and point all Explores at it.