BigQuery BI Engine

BI Engine is an in-memory cache for BigQuery tables that you rent by the gibibyte. What it speeds up, what it can't (joins, window functions, JSON, row-level security), how it changes the bill, how to size a reservation, and how to check that a query used it.

BI Engine keeps the columns and partitions you query most in memory and runs part of each query on that cache with a vectorized engine. Queries need no changes: anything that talks to the BigQuery API, such as the console, Looker Studio, Tableau, a JDBC driver or your own code, benefits automatically. You rent the memory with a reservation measured in GiB, attached to one project and one region. It is for dashboards and similar queries that scan the same tables again and again and return small results. It is not for big joins, long-running transformations or tables that change in ways BI Engine can’t cache.

This page comes from Google’s documentation. We did not create a BI Engine reservation to test it, because a reservation is billed capacity and the numbers depend on a workload we don’t have. What we did run is described where it appears.

What it accelerates

BigQuery splits a query plan into subqueries. BI Engine works best on the leaf-level ones, the stages that scan a table and then filter, compute, aggregate, sort or do a supported join. A stage it can’t accelerate falls back to normal BigQuery slots, and the query still succeeds. So a simple dashboard query, where most of the time goes into reading and aggregating raw data, gains the most. A query whose time goes into a large join or a later stage gains little.

Each query ends in one of four modes, reported in the job statistics:

What it costs

You pay for the capacity per GiB-hour, at a price that depends on the region, whether or not you use it. The amount can be from 1 to 250 GiB, and you can ask Google for more. The rate is on the BigQuery pricing page and is not repeated here.

The bill for the queries changes in a way worth knowing:

• With on-demand pricing, the stages that BI Engine accelerates are charged for 0 scanned bytes, and later stages add no on-demand charge.
• With editions pricing, the first stage uses no slots from your reservation, and later stages do.

An organization with editions commitments of 100 to 2,000 slots gets some BI Engine capacity at no extra cost, in a project of the same organization: 5 GiB for 100 slots, 25 for 500, 50 for 1,000, 75 for 1,500, and 100 GiB at 2,000, the most per organization. The reservation has to be in a project that has slots assigned to it. One in an on-demand project doesn’t count towards the free amount.

Set up a reservation

Create a reservation in the billing project that runs the queries and in the region of the datasets. It is not necessarily the project that holds the data. Creating or deleting one needs roles/bigquery.resourceAdmin. In SQL:

ALTER BI_CAPACITY `my-project.region-us.default`
SET OPTIONS (
  size_gb = 4,
  preferred_tables = ['my-project.shop.orders', 'my-project.shop.customers']
);

We dry-ran this statement against a project and BigQuery accepted it, without applying it. There is one capacity per project and region, and its name is always default. Setting size_gb = 0 deletes it, and BI Engine is then off for the project. Changes to preferred tables can take up to ten seconds.

In Querylab.io, the project’s SQL Templates have BI Engine capacity, which writes this ALTER with a SELECT on BI_CAPACITIES above it so you see the current size and list first, and a table’s SQL Templates have Add to BI Engine preferred tables. preferred_tables replaces the whole list, and both templates say so.

Preferred tables limit acceleration to the tables you list, so a stray heavy query can’t push your dashboard’s data out of memory. Without a list, BI Engine decides what to cache from the project’s usage. The list has rules:

• A query that reads several tables is accelerated only if every one of them is preferred. One table outside the list and the whole query runs on slots.
• Only tables can be listed, not logical views. A materialized view is accelerated only if both the view and its base tables are listed.
• You can’t choose columns or partitions. BI Engine caches the ones that queries touch.
• Public tables can’t be added in the console. Use the API or the DDL above.

Size the reservation

Google’s starting rule is the logical size of the tables. For queries over 200 GiB of tables, start with 200 GiB, and with less if the queries read only some columns or partitions. Get the size from TABLE_STORAGE, which needs bigquery.tables.get and bigquery.tables.list on the project:

SELECT SUM(total_logical_bytes) / POW(1024, 3) AS logical_size_gb
FROM `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE
WHERE table_name IN ('orders', 'customers');

Then run the real workload from the same project and region and compare the reservation with the bytes in use, reservation/used_bytes in the Cloud Monitoring bigquerybiengine metrics, and shrink it if most of it is idle. BI Engine caches only the columns and partitions queries read. When memory runs out it drops what was used least recently. If several heavy queries hit the same data, it keeps extra copies to spread the load, so the size you need can be above the size of the data.

Partitioning and clustering help BI Engine too: if the dashboard shows the last quarter, only those partitions are loaded.

What it can’t do

Joins are the main limit. BI Engine accelerates INNER and LEFT OUTER joins in a leaf-level subquery where a large fact table joins up to four small dimension tables. Each dimension table must have fewer than 5 million rows, and be 5 GiB or less if unpartitioned or 1 GiB or less in the referenced partitions. Google’s advice is to pre-join and pre-aggregate, for example with a materialized view, and let BI Engine serve that.

Window functions run in BI Engine only if all of these hold: the query scans exactly one table that is not partitioned and has under 5 million rows, there is no join, and the table size times the number of window operators is at most 300 MiB. Two window functions with the same OVER clause and the same input count as one operator. If a query breaks these, Google says you might see Analytic function is incompatible with other operators or its inputs are too large.

These are not accelerated at all:

• JavaScript UDFs and remote functions
• External tables, including BigLake tables
• JSON and RANGE columns, with JSON native type is not supported. or RANGE native type is not supported. as the reason
• Queries that write to a permanent table, and transactions
• Tables with upserts from change data capture
• Row-level security. Authorized views, column-level security and data masking work.
• SEARCH and vector search, and queries that use search or vector indexes
• Recursive queries and BigQuery ML queries
• Queries that return more than 1 GiB. For dashboards Google suggests under 1 MiB.
• Wildcard tables

If you need an unsupported feature, Google’s workaround is to run the query on a schedule, write the result to a table, and point the dashboard at that table. Refresh hourly or daily: a refresh every minute can invalidate the cache too often. See scheduled queries.

Check that it is being used

Turn off Use cached results when you compare, or the query cache will answer instead. Then look at the job. The statistics have a biEngineStatistics field with the mode and the reasons, and INFORMATION_SCHEMA.JOBS has the same as bi_engine_statistics:

SELECT
  creation_time,
  job_id,
  bi_engine_statistics.bi_engine_mode,
  bi_engine_statistics.acceleration_mode,
  bi_engine_statistics.bi_engine_reasons
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND job_type = 'QUERY';

Querylab.io shows the result on the query’s execution plan as BI Engine Accelerated (with the mode) or BI Engine Not Used (with the reasons), when the job reports BI Engine statistics (see Execution plan).

In our project, which has no reservation, bi_engine_statistics was NULL on all 3,755 query jobs of the last day, and INFORMATION_SCHEMA.BI_CAPACITIES returned no rows. The field appears once the project is enabled for BI Engine. Slots that BI Engine stages use are reported in slot metrics but not counted against your slot reservation.

INFORMATION_SCHEMA.BI_CAPACITIES shows the current size and preferred tables, and BI_CAPACITY_CHANGES the history with the user who made each change. Cloud Monitoring has three metrics: total reserved bytes, used bytes, and cached bytes for the top tables.

When to use it

BI Engine pays when many people open dashboards over the same few tables and the queries are scan-and-aggregate. Check that the tables are partitioned or clustered so the cache holds what the dashboards show. If the dashboard queries join several large tables, fix the model first, or precompute the join. The capacity bill runs whether or not queries use it, so look at acceleration_mode after a week.