ServicesAboutNotesContact Get in touch →
EN FR
Note

BigQuery BI Engine

How BigQuery BI Engine provides in-memory acceleration for dashboard queries, what it supports, what it silently skips, and how to verify it's actually working.

Planted
bigquerygcpcost optimizationanalytics

BigQuery BI Engine is an in-memory analysis service that caches frequently accessed data in RAM. Every BigQuery project gets 1 GB of capacity for free. When a compatible query arrives, BI Engine processes it from cache in sub-second time instead of reading from storage. For dashboard workloads where the same underlying data gets queried with slightly different filters throughout the day, this significantly reduces query latency.

What BI Engine Actually Does

Regular BigQuery queries read from Colossus (distributed storage), shuffle data through Dremel, and return results. The round-trip through storage is fast, but it’s not RAM-fast. BI Engine intercepts compatible queries before they reach the storage layer and serves results directly from an in-memory cache in the same region as your data.

The result is latency measured in milliseconds rather than seconds, and zero bytes processed against your on-demand quota for queries served from cache.

Paid capacity costs about $30/month per GB, up to a maximum of 250 GB. For context: a well-designed pre-aggregated table powering a dashboard for 50 people might fit comfortably in 2-5 GB of BI Engine capacity. The economics are favorable compared to the compute costs of those same queries running repeatedly against raw data.

Setting Up a Reservation

Enable it through the Cloud Console:

  1. Go to BigQuery > BI Engine
  2. Create a reservation in the same project and region as your data
  3. Set the capacity in GB
  4. Optionally specify preferred tables

Looker Studio, Connected Sheets, and other Google tools pick it up automatically once a reservation exists. No code changes required on the client side.

The optional preferred tables setting tells BI Engine which tables to prioritize caching when memory is constrained. If you have 5 GB of capacity but 20 GB of data used by dashboards, preferred tables ensure the most important data stays hot.

What BI Engine Supports (and Doesn’t)

BI Engine is picky about what it accelerates. It works best with:

  • Pre-joined, pre-aggregated flat tables
  • Standard SQL aggregations (SUM, COUNT, AVG, MIN, MAX)
  • INNER and LEFT OUTER joins with a large fact table joining up to 4 smaller dimension tables
  • Queries on native BigQuery tables

It does not support:

  • Wildcard tables (the _TABLE_SUFFIX pattern common in GA4 raw exports)
  • External tables (BigLake, Bigtable, Cloud Storage)
  • Row-level security policies
  • JSON columns
  • Non-SQL UDFs
  • Queries spanning more than 4 joined tables on the dimension side

The practical implication: BI Engine rewards the same table design choices that materialized views and partitioning reward. A pre-aggregated flat table in a native BigQuery dataset is the ideal target. A query that joins 6 tables including an external CSV will get zero BI Engine benefit.

The Silent Fallback Problem

This is what catches most teams off guard: when BI Engine reservation capacity is exceeded, queries silently fall back to regular BigQuery on-demand pricing. There’s no error. There’s no queue. The query runs, the result comes back, and the only difference is that it cost money and took longer.

This means that if your dashboards suddenly get slow despite having BI Engine enabled, you’ve probably exceeded your reservation capacity. The reservation filled up and everything fell through to on-demand.

You can verify whether acceleration actually kicked in by querying INFORMATION_SCHEMA.JOBS:

SELECT
  job_id,
  creation_time,
  bi_reservation_usage,
  CASE
    WHEN bi_reservation_usage.slot_ms > 0 THEN 'ACCELERATED'
    ELSE 'NOT_ACCELERATED'
  END AS bi_engine_status
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND job_type = 'QUERY'
ORDER BY creation_time DESC
LIMIT 50;

The more precise check uses the INFORMATION_SCHEMA.JOBS bi_engine_statistics field, which shows the acceleration mode as FULL, PARTIAL, or DISABLED. PARTIAL means BI Engine handled part of the query but fell back to regular execution for the rest — usually because part of the query uses unsupported features.

BI Engine vs. Regular Caching

Looker Studio has its own built-in caching layer that stores query results for up to 12 hours. BI Engine is different: it caches data in memory, not results. This distinction matters because:

  • Looker Studio cache returns identical results for identical queries — same dimensions, same filters, same date range
  • BI Engine accelerates any compatible query against the cached data, including queries with different filter values

If someone opens a dashboard filtered to France and someone else opens it filtered to Germany, Looker Studio’s result cache doesn’t help the second person (different filter = cache miss). BI Engine helps both, because the underlying table data is in memory and both filter queries run fast.

In practice, you want both: BI Engine for acceleration and Looker Studio result caching to avoid running any query twice for the same result.

Interaction with Materialized Views

If you’re using materialized views as pre-aggregation layers for dashboards, BI Engine can accelerate both the materialized view queries and the base tables. However: if your setup uses preferred tables, you need to add both the materialized view and its base tables to the preferred tables list. BI Engine won’t automatically discover the relationship between a MV and its base tables for caching purposes.

When BI Engine Makes Sense

The ROI calculation is straightforward. BI Engine makes sense when:

  1. Dashboard query volume is high: A team of 20 people opening the same dashboard 5 times a day generates 100+ query executions per dashboard page, per day. BI Engine serves most of those from memory.
  2. Query patterns are repetitive: Same tables, same aggregations, different filter values. This is the canonical dashboard pattern.
  3. Tables fit in available memory: A pre-aggregated table with one row per day per segment is tiny. Fitting it in 1-2 GB of BI Engine capacity is trivial.
  4. You’re already using pre-aggregated tables: BI Engine and pre-aggregation are multiplicative, not redundant. Pre-aggregation reduces data volume; BI Engine serves the smaller dataset from RAM.

BI Engine is less useful for: exploratory queries across huge raw tables, ad-hoc SQL with unpredictable patterns, or workloads dominated by external tables and UDFs that BI Engine can’t accelerate.

The free 1 GB tier covers basic dashboard acceleration for most small teams without additional cost.