Every Looker instance eventually gets the same complaint: "the dashboard takes eleven seconds to load." The usual responses are caching (datagroups), pre-aggregation (PDTs and aggregate_table), or throwing slots at the reservation. There is a fourth lever that Looker teams on BigQuery routinely ignore: BI Engine.
BI Engine is an in-memory analytics accelerator that sits inside BigQuery. When a query is eligible, BigQuery serves it from a columnar in-memory representation of the underlying table instead of reading Storage. Sub-second tile loads on tables that otherwise take five seconds are normal. It is not magic, though: eligibility rules are real, and a LookML model written without them in mind will bypass the accelerator on most tiles.
This tutorial is the version we give clients during a performance engagement.
1. What BI Engine actually does
You buy capacity, not queries. A BI Engine reservation is an amount of memory (in GB) attached to a project in a region. BigQuery decides, per query, whether to use it:
- A query arrives that scans one or more tables.
- If the relevant columns and partitions of those tables can be held in the reservation, BigQuery loads them into memory (a cold miss pays the normal read once).
- Subsequent queries touching the same columns are served from memory with vectorised execution.
Two consequences matter for Looker:
- It caches columns, not results. Looker's own cache stores result sets keyed by the SQL string; BI Engine accelerates different queries over the same columns. A user changing a filter or drilling into a new dimension still benefits — the case where Looker's cache always misses.
- It is sized against your hot columns, not your warehouse. A 1 TB events table with a 90-day partition filter and eight referenced columns might need only a few GB resident.
BI Engine capacity is available with BigQuery Editions reservations (Enterprise and above) or as a standalone reservation; check current pricing and edition requirements for your region before you plan the spend, since Google has moved these terms more than once.
2. What makes a Looker query ineligible
This is where most of the value in this article sits. BI Engine silently falls back to standard execution when it cannot accelerate — you get no error, just your old latency. Common Looker-generated patterns that fall out of the accelerated path:
- External tables and federated queries. Sheets-backed or BigLake external connections are not accelerated. If your "targets" table is a Google Sheet joined into an Explore, every tile that touches it drops out.
- Very large joins. BI Engine handles joins, but a fan-out join across two large fact tables — the thing Looker emits when an Explore joins order items to shipments to events — is a frequent fallback. Symmetric aggregates make the SQL heavier still.
- Non-deterministic or unsupported functions. Some JavaScript UDFs, certain
SESSION/scripting constructs, and a few analytic edge cases disqualify a query. - Queries against views that expand into unsupported shapes. Looker's derived tables are subqueries; the accelerator evaluates the expanded plan, not your LookML.
- Reservation pressure. If the working set does not fit, BigQuery partially accelerates or skips entirely. A single 400 GB unpartitioned table queried without a date filter will evict everything else your dashboards rely on.
The practical rule: BI Engine loves wide-but-shallow scans of a small number of well-partitioned physical tables. That is exactly what an aggregate table or a well-built PDT looks like, and exactly what a five-way Explore join does not.
3. Measuring before you buy
Do not guess. Start from INFORMATION_SCHEMA.JOBS and find the queries your dashboards actually run:
SELECT
query,
COUNT(*) AS runs,
APPROX_QUANTILES(TIMESTAMP_DIFF(end_time, start_time, MILLISECOND), 100)[OFFSET(50)] AS p50_ms,
APPROX_QUANTILES(TIMESTAMP_DIFF(end_time, start_time, MILLISECOND), 100)[OFFSET(95)] AS p95_ms,
SUM(total_bytes_processed) / POW(1024, 4) AS tb_processed
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
AND job_type = 'QUERY'
AND statement_type = 'SELECT'
AND error_result IS NULL
GROUP BY query
ORDER BY runs DESC
LIMIT 50;
Looker tags its jobs, so you can narrow further using the query text comment Looker injects (the -- Looker Query Context block) or by filtering on the service account in user_email. Cross-reference with Looker's System Activity History Explore to map the heavy SQL back to specific dashboards and tiles: History Query Runtime percentiles by Dashboard Title tell you which four dashboards are generating 80% of the pain.
You are looking for queries that are: frequent, slow at p95, and scanning a modest set of columns. Those are your BI Engine candidates. A query that runs twice a month and scans 2 TB is a PDT problem, not a BI Engine problem.
4. Creating and sizing the reservation
Create a reservation in the region your datasets live in (BI Engine is regional; a reservation in US does nothing for data in europe-west2):
-- run in the administration project
CREATE BI_CAPACITY IF NOT EXISTS `my-project.region-us.default`
OPTIONS (
size_gb = 25,
preferred_tables = [
'my-project.analytics.agg_orders_daily',
'my-project.analytics.agg_sessions_daily'
]
);
Two settings do the work:
size_gb— start small (10–25 GB) and grow. Over-provisioning is pure waste; under-provisioning shows up as a falling hit rate, which you can see.preferred_tables— the single most underused option. Without it, BI Engine caches whatever is hot, and one analyst's ad-hoc scan of a raw table can evict the tables backing your executive dashboard. With it, the accelerator is pinned to the tables you care about. Pin your aggregate tables and your Looker-facing marts; never pin raw event tables.
5. Making LookML BI Engine-friendly
The modelling changes are the same ones that make a Looker instance good anyway — BI Engine just raises the payoff.
Materialise the shapes your dashboards use. A persistent derived table or, better, an aggregate_table gives BI Engine a small physical table to pin:
explore: orders {
aggregate_table: daily_revenue_by_region {
query: {
dimensions: [orders.created_date, customers.region]
measures: [orders.total_revenue, orders.count]
timezone: "America/New_York"
}
materialization: {
datagroup_trigger: nightly_etl
partition_keys: ["orders_created_date"]
}
}
}
Aggregate awareness rewrites eligible tile queries to hit daily_revenue_by_region; that table is small, partitioned, and pinnable. Tiles that previously joined three tables now read one.
Keep partition filters in the SQL. BI Engine works partition by partition. If your Explore lets users query all history, most of the table is resident and your reservation is mostly cold data. Use sql_always_where or a required filter:
explore: events {
sql_always_where: ${events.created_raw} >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY) ;;
}
Push heavy transformations out of the query path. Liquid-heavy sql: blocks, JavaScript UDFs, and giant CASE ladders computed at query time all make acceptance less likely. Compute them upstream (in dbt or a scheduled PDT) and expose plain columns.
Avoid external tables in Explores that back dashboards. If the business insists on a spreadsheet of targets, load it into a native table on a schedule instead of joining the Sheet live.
6. Proving it worked
After a day of traffic, check acceptance directly:
SELECT
DATE(creation_time) AS day,
bi_engine_statistics.bi_engine_mode AS mode,
COUNT(*) AS jobs,
APPROX_QUANTILES(TIMESTAMP_DIFF(end_time, start_time, MILLISECOND), 100)[OFFSET(95)] AS p95_ms
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 DAY)
AND job_type = 'QUERY'
GROUP BY day, mode
ORDER BY day DESC, jobs DESC;
bi_engine_mode returns FULL, PARTIAL, or DISABLED. When it is not FULL, bi_engine_statistics.bi_engine_reasons tells you why — UNSUPPORTED_SQL_TEXT, INPUT_TOO_LARGE, TABLE_EXCLUDED, OTHER_REASON. That field is the whole debugging loop: read the reason, change the model or the reservation, re-measure.
Then close the loop on the user-visible metric in System Activity: p95 History Query Runtime filtered to the dashboards you targeted, week over week. If tile latency did not move, BI Engine was not your bottleneck — the queue in front of your reservation, or Looker's own render path, probably was.
7. Where BI Engine is the wrong answer
Be honest about the failure modes:
- Slow PDT builds. BI Engine accelerates reads, not builds. Fix that with incremental PDTs.
- Dashboards with forty tiles. You have a design problem; acceleration just makes forty queries fire faster.
- Cost-driven work under on-demand billing. BI Engine reduces latency; it does not always reduce bytes billed on the cold path, and the reservation itself costs money. Model the trade-off.
- Non-BigQuery warehouses. If you are on Snowflake or Databricks, the equivalent conversation is warehouse sizing and result caching, not BI Engine.
Checklist
- Identify the top 20 dashboard queries by frequency and p95 from
INFORMATION_SCHEMA.JOBSplus System Activity. - Materialise the hot shapes as aggregate tables, partitioned on the dashboard's date field.
- Create a small regional BI Engine reservation with
preferred_tablespinned to those aggregates. - Enforce partition filters in Explores that back dashboards.
- Monitor
bi_engine_modeandbi_engine_reasonsdaily for a week; fix the top rejection reason. - Re-measure p95 tile runtime and resize the reservation up or down.
Done in that order, BI Engine is usually a one-day change that removes several seconds from the dashboards your executives actually open.
Vistelio's senior Looker developers do performance and cost work on Looker + BigQuery deployments every week. If your dashboards are slow and you want a second opinion on where the time is going, get in touch or read about our Looker health check and technical audit.