BI Engine and Materialized Views for BI

Configuring and managing BigQuery BI Engine (opens in a new tab) for accelerated analytics means provisioning in-memory capacity that sits between BigQuery storage and dashboard tools. Data flows from BigQuery storage into the BI Engine reservation, and queries that hit cached columns and partitions return with sub-second response and high concurrency. BI Engine requires no manual tuning or data tiering, but reservation sizing, table selection, and query shape determine how much acceleration occurs.

1.1How BI Engine Accelerates Queries

How BI Engine accelerates queries is by optimizing leaf-level subqueries that scan storage and perform filtering, computation, aggregation, sorting with ORDER BY, and supported joins. BigQuery divides each query plan into subqueries as the unit of execution, and BI Engine selectively accelerates the leaf stages that spend the most time processing raw data. Stages that BI Engine cannot accelerate automatically fall back to standard BigQuery slots without failing the query, so simpler dashboard queries benefit most.

BI Engine improves speed through a vectorized runtime and compressed in-memory storage. The vectorized engine operates on batches of data to use modern CPU architecture efficiently, and dictionary run-length encoding further compresses data held in memory. BI Engine integrates directly with the BigQuery API (opens in a new tab) so tools that use REST, client libraries, or JDBC and ODBC drivers work without modification while preserving authorized views, column-level security, and data masking.

BI Engine provides four operational advantages for interactive analytics:

• BigQuery API compatibility lets existing BI solutions and custom applications use acceleration without code changes.
• Vectorized runtime processes batches of data and compresses cached data with dictionary run-length encoding.
• Seamless integration preserves BigQuery metadata controls including authorized views, column-level security, and data masking.
• Reservation allocations isolate memory by project and region and cache only the queried parts of columns and partitions.

1.2Provisioning and Sizing BI Engine Reservations

A BI Engine reservation (opens in a new tab) is memory capacity purchased in gibibytes and attached to one billing project and one region where queries execute. The reservation is managed by billing project ID, which is not necessarily the project that contains the dataset, and the reservation location must match the location of the datasets being queried. Capacity ranges from 1 to 250 GiB per reservation, requires a supported BigQuery edition, and is billed per GiB per hour by region. Creating or deleting reservations requires the BigQuery Resource Admin (roles/bigquery.resourceAdmin) role, and reservations can be created in the console, with the ALTER BI_CAPACITY SET OPTIONS data definition language (DDL) statement, or with the bq update command.

To size a reservation from actual workload characteristics, estimate logical size first and then measure use under load:

1. Estimate logical size: Query INFORMATION_SCHEMA.TABLE_STORAGE for TOTAL_LOGICAL_BYTES for the target tables and use that total as the starting reservation size.
2. Run representative workload: Run all queries that need optimization from the same project and region as the reservation so BI Engine loads the working set into memory.
3. Compare reservation to use: Check reservation/used_bytes in the Cloud Monitoring bigquerybiengine metrics to see how much reserved RAM is actually consumed.
4. Adjust capacity: Increase the reservation if memory is full and eviction is frequent, or decrease it if large portions remain unused because queries touch only a subset of columns or partitions.

For example, a workload over tables totaling 200 GiB of logical bytes can start with a 200 GiB reservation, then start smaller if queries filter to a few fields or recent partitions. Reservation increases above the 250 GiB maximum can be requested and typically take three days to one week to process in most regions.

1.3Controlling Acceleration with Preferred Tables

Preferred tables limit BI Engine acceleration to a specified set of tables, while queries to all other tables use regular BigQuery slots. This control lets you protect high-visibility dashboards from interference by other traffic in the same project. To guarantee acceleration for a critical set of queries, create a separate project with a dedicated BI Engine reservation, estimate its capacity, and designate only those tables as preferred. If memory cannot hold all preferred data, BI Engine offloads least-recently accessed partitions and columns to free memory for new accelerated queries.

The choice between automatic caching and explicit control depends on isolation needs:

Preferred tables follow strict inclusion rules that determine whether a query can use BI Engine memory. The following limits control multi-table and materialized view behavior:

• Logical views cannot be added to the preferred tables list; only tables are supported.
• Queries to materialized views are accelerated only when both the materialized view and its base tables are in the preferred tables list.
• Partition-level or column-level selection is not supported.
• JSON type columns are not supported and are not accelerated.
• Queries that access multiple tables are accelerated only if every table is preferred, so a JOIN fails to accelerate if even one input is missing from the list.
• Public datasets cannot be added in the Google Cloud console and must be added with the API or DDL.

1.4Designing Tables and Queries for Memory Efficiency

Memory-efficient design for BI Engine keeps the working set small so more of the interactive workload fits in the reservation. Partitioning by time lets a dashboard that shows only the last quarter load only recent partitions into memory, and clustering further narrows the columns and rows scanned from large tables. BI Engine caches only the queried parts of columns and partitions, so selective filters directly reduce the memory needed for the same logical tables.

BI Engine works best for pre-joined or pre-aggregated data with few joins, especially a large fact table joined to much smaller dimension tables. A materialized view can perform the join once to produce a single large flat table, which avoids repeating the same join for every dashboard query, and stale materialized views are recommended for optimal performance. For join acceleration, BI Engine supports leaf-level INNER and LEFT OUTER joins where one large fact table joins up to four smaller dimension tables with fewer than 5 million rows each, with dimension size at or below 5 GiB for unpartitioned tables or 1 GiB of referenced partitions for partitioned tables.

1.5Integrating BI Engine with Dashboards and Reporting Tools

BI Engine integration with dashboards carries traffic from BI tools through the BigQuery API into accelerated in-memory execution. Supported paths include the BigQuery console, Looker, Looker Studio, Tableau, Power BI, client libraries, APIs, and ODBC or JDBC connectors. Because Data Studio (opens in a new tab) itself imposes no hard concurrency limit, report concurrency and responsiveness depend on BigQuery slot availability, query complexity, data size, and caching, which BI Engine improves by serving repeated interactive queries from memory.

To accelerate Data Studio reports with BI Engine, verify these four prerequisites for the project where queries execute:

• Reporting data is stored in standard BigQuery tables or views.
• A valid BigQuery billing project has active Google Cloud billing enabled.
• The BigQuery API (bigquery.googleapis.com) is enabled in the project.
• An active BI Engine capacity reservation with a SIZE_IN_GB value exists for the query region.

Two companion optimizations further reduce dashboard latency beyond BI Engine caching. Materializing complex views into tables or materialized views with scheduled refresh avoids re-running heavy run-time calculations for every Data Studio request, with hourly or daily refresh preferred because per-minute refresh can invalidate cache too frequently. Enabling the BigQuery Storage Read API (opens in a new tab), which Data Studio uses automatically for paginated results when helpful, improves transfer speed when the connected user has bigquery.readsessions.create and bigquery.readsessions.getData permissions.

1.6Monitoring and Verifying Acceleration

BI Engine monitoring shows whether reservations are sized correctly and whether queries actually accelerate. Cloud Monitoring exposes BI Engine utilization including reserved versus used bytes, while INFORMATION_SCHEMA.BI_CAPACITIES and INFORMATION_SCHEMA.BI_CAPACITY_CHANGES report capacity state and changes. Job metadata in INFORMATION_SCHEMA.JOBS reports bi_engine_statistics.acceleration_mode as FULL_INPUT when input stages accelerate or FULL_QUERY when the full query executes in BI Engine. To get an accurate before-and-after comparison, disable the Use cached results option in BigQuery so repeated runs do not return prior results instead of exercising BI Engine.

1.7Limits That Prevent Acceleration

BI Engine limits define which query shapes and features can stay in memory and which must use standard execution. Window functions, also called analytic functions, accelerate only for narrow shapes: the query scans exactly one unpartitioned table with fewer than 5 million rows, has no JOIN operators, uses only supported window functions, and keeps scanned table size multiplied by the number of window function operators at or below 300 MiB. Functions with identical OVER clauses and identical direct inputs share one operator, while different OVER clauses or different inputs count as separate operators.

BI Engine acceleration is unavailable for the following features and query patterns:

• JavaScript user-defined functions (UDFs) and remote functions.
• External tables, including BigLake tables.
• JSON data and RANGE data.
• Writing results to a permanent BigQuery table.
• Tables with upserts that use BigQuery change data capture ingestion, transactions, and row-level security.
• Queries returning more than 1 GiB of data, with less than 1 MiB recommended for latency-sensitive use.
• Search and vector search functions or queries optimized by search or vector indexes, recursive queries with RECURSIVE, BigQuery ML queries, and wildcard tables.

When a query needs an unsupported feature, the supported workaround is to isolate the unsupported logic from the interactive query. First write the complex query in BigQuery, then save its results to a table, schedule that query to refresh the table hourly or daily, and reference the materialized table from performance-critical dashboard queries so BI Engine can accelerate the final select.

Designing and implementing BigQuery (opens in a new tab) materialized views for performance optimization means precomputing expensive query results so repetitive workloads read a small stored result. Data flows from one or more base tables into the materialized view (opens in a new tab) through a background refresh, and queries then read the precomputed result instead of scanning the large base tables. This design reduces query latency and bytes scanned for patterns such as dashboard aggregates, filtered joins, and clustered rollups without requiring a manual data pipeline.

2.1What Materialized Views Are and When They Help

What materialized views are is precomputed tables that physically store the result of a SQL query, while logical views store only query text. A materialized view stores computed rows on disk and refreshes in the background, while a logical view re-executes against base tables on every call. Choose a materialized view when query patterns are stable and expensive, and choose a logical view when the goal is abstraction, row-level security, or rapidly changing logic.

Materialized views are most valuable for four peer workloads:

• Pre-process data by preparing aggregates, filters, joins, and clusters ahead of query time.
• Dashboard acceleration for business intelligence (BI) tools like Looker that request the same metrics, such as daily active users.
• Real-time analytics on large streams by serving faster responses over tables that receive high-velocity streaming inserts.
• Cost management by replacing repeated full scans of large base tables with reads of a small precomputed result.

2.2Incremental and Non-Incremental Materialized Views

Incremental and non-incremental materialized views in BigQuery differ in SQL flexibility and optimizer support. Incremental materialized views support a limited set of GoogleSQL features but enable automatic incremental maintenance and smart tuning. Non-incremental materialized views support most of the syntax that incremental views do not support, but they cannot take advantage of smart tuning.

2.3Fresh Results Through Automatic Refresh and Merge

Fresh results from a BigQuery materialized view come from combining cached precomputed rows with recent changes in base tables. BigQuery precomputes the view in the background when base tables change and automatically adds incremental data changes with no user action required. Querying the view does not trigger a refresh, yet every query remains consistent with the base tables because BigQuery compensates at query time.

The freshness merge follows an ordered decision. First, BigQuery checks whether changes to the base tables since the last refresh would invalidate the cached result. Then, if the changes do not invalidate the view, data flows from both the materialized view and the delta in the base tables into the query result. Only after invalidation is detected does BigQuery abandon the cached result and read data directly from the base tables.

Incremental maintenance depends on the type of change and the position of tables in joins. A single-table view can be incrementally updated when its base table is unchanged or has only appended rows. A join view can be incrementally updated only when appended data arrives on tables on the left side of the JOIN; any change to a table on the right side prevents incremental use. Operations that prevent incremental updates include DML UPDATE, MERGE, and DELETE statements, truncation, partition expiration, and certain metadata operations, in which case BigQuery reverts to the original query against base tables.

2.4Smart Tuning and Direct Querying

Smart tuning (opens in a new tab) in BigQuery is the optimizer capability that reroutes a query against base tables to use a materialized view when any part of the query can be answered more efficiently from the view. The query flows from the client to the base tables, the optimizer matches it to an eligible incremental materialized view, and the execution reads precomputed rows plus any delta instead of the full base data. No application rewrite is needed because the original query still targets the base tables.

You can also query a materialized view directly the same way you query a regular table or standard view. Direct queries and smart-tuned queries both require the BigQuery Data Viewer (roles/bigquery.dataViewer) role on both the materialized view and its base tables, which grants bigquery.tables.get and bigquery.tables.getData. If a base table is deleted without first deleting the materialized view, queries over the view fail and the view must be recreated after the base table is recreated.

2.5Comparison of Logical and Materialized Views

The logical view versus materialized view choice trades storage and maintenance cost for speed and scan savings:

Logical views are optimized for security and abstraction, such as renaming columns into business-friendly terms, consolidating sources for visualization, and iterating without storage overhead. Materialized views are optimized for speed and cost reduction on large, frequently queried aggregates. The max_staleness option allows controlled staleness to improve performance and limit refresh cost on large, frequently changing datasets.

2.6Implementation Workflow

The implementation workflow for a BigQuery materialized view moves from pattern analysis to creation to automatic reuse. Analyze repetitive query logs for stable GROUP BY keys, common filters, and join keys, then weigh data freshness requirements against refresh and storage cost before building. A view that matches dashboard rollups and append-only streams pays back quickly, while ad hoc or constantly updated logic does not.

1. Design and verify query: Draft the aggregation query in GoogleSQL and test it against base tables to confirm correct grouping, joins, and filters.
2. Create materialized view: Execute the CREATE MATERIALIZED VIEW statement from the Google Cloud console, bq command-line tool, or BigQuery API into the dataset that will own refresh costs.
3. Let smart tuning run: Leave existing queries pointed at base tables while BigQuery automatically redirects eligible queries to use the materialized view.

2.7Storage and Query Costs

Storage and query costs for BigQuery materialized views come from three components: querying, maintenance, and storage. Query cost covers bytes processed from the materialized view plus any necessary portions of base tables read for freshness compensation. Maintenance cost covers bytes or slots processed during automatic refresh, billed to the project where the view resides, while manual refresh is billed to the project running the refresh job. Storage cost covers bytes stored in the materialized view table under both on-demand and capacity-based pricing.

Storage size depends on how aggregates are physically kept. For AVG, ARRAY_AGG, and APPROX_COUNT_DISTINCT, BigQuery does not store the final displayed value directly and instead stores an intermediate sketch in a proprietary BYTES format used to produce the final value. A column defined as AVG(net_paid) may therefore appear as NUMERIC or FLOAT64 but is charged as BYTES for data size calculation. Materialized views over BigLake tables carry added limits, including no partitioning of the view, full refresh after base-table deletions, a max_staleness value greater than the base table, no joins between managed and BigLake tables, and no BigQuery BI Engine acceleration.

Comparing and selecting appropriate caching and acceleration strategies means choosing among BI Engine, materialized views, and cached results based on cost, speed, freshness, and complexity. BigQuery BI Engine (opens in a new tab) is a fast, in-memory analysis service that caches frequently used columns and partitions to deliver sub-second response with high concurrency. Materialized views and materialized tables precompute joins and aggregations so dashboards do not repeat heavy work on every query. Cached query results and freshness controls reuse prior answers to avoid scanning data again.

3.1BigQuery BI Engine In-Memory Acceleration

BigQuery BI Engine in-memory acceleration caches the data used most frequently in memory without manual tuning or data tiering. BI tools such as Looker, Looker Studio, Tableau, and Power BI connect through the BigQuery API via REST, client libraries, or JDBC and ODBC drivers, and queries flow from the tool through the BigQuery API to the BI Engine in-memory layer when the data is cached. BI Engine uses a vectorized runtime that operates on batches of data and dictionary run-length encoding to compress cached data, and it integrates with authorized views, column-level security, and data masking.

BI Engine divides each query plan into subqueries and selectively accelerates leaf-level subqueries that scan storage and filter, compute, aggregate, sort with ORDER BY, and perform supported joins. Stages that BI Engine cannot accelerate automatically fall back to standard BigQuery slots without failing the query, so simpler dashboard queries that spend most time scanning raw data benefit the most. To isolate critical workloads, create a separate project with a dedicated BI Engine reservation, estimate capacity from the logical size of the tables, and designate those tables as preferred tables so other traffic does not evict them.

BI Engine reservations (opens in a new tab) manage memory separately for each project and region and are charged per GiB per hour, with a size from 1 to 250 GiB and a supported BigQuery edition required. BI Engine caches only the queried parts of columns and partitions, so partitioning by time and clustering large tables reduces the memory needed when dashboards use only recent data. If memory is insufficient, BI Engine offloads partitions and columns that have not been accessed recently to free space for new accelerated queries.

3.2Preferred Tables and When BI Engine Fits

BI Engine preferred tables limit acceleration to a specified set of tables, while queries to all other tables use regular BigQuery slots. Queries that access multiple tables are only accelerated when every table in the query is a preferred table, so all tables in a JOIN must be listed or the query cannot use BI Engine. Queries to materialized views are only accelerated when both the materialized view and its base tables are in the preferred tables list.

Preferred tables work best for frequently queried tables and high-visibility dashboards accessed through BI tools, the console, APIs, or connectors. BI Engine is not appropriate for queries over wildcard tables, external tables including BigLake tables, JSON or RANGE data, row-level security, JavaScript user-defined functions (UDFs), remote functions, non-SQL UDFs, transactions, change data capture upserts, BigQuery ML, recursive RECURSIVE queries, or search and vector search. Results written to a permanent BigQuery table and queries returning more than 1 GiB are also not accelerated, with less than 1 MiB recommended for latency-sensitive applications.

Preferred tables have the following restrictions:

• Logical views cannot be added to the preferred tables list; only tables are supported.
• Materialized view queries require both the view and its base tables in the list.
• Specific partitions or columns cannot be selected for acceleration.
• JSON type columns are not supported and are not accelerated.
• All tables in a multi-table query must be preferred tables, including every side of a JOIN.
• Public datasets cannot be added as preferred tables in the Google Cloud console and require the API or data definition language (DDL).

3.3Joins, Aggregations, and Window Function Limits

Joins, aggregations, and window function limits in BI Engine define which shapes stay in memory. BI Engine works best for pre-joined or pre-aggregated data with few joins, especially a large fact table joined to much smaller dimension tables. Acceleration applies to leaf-level INNER and LEFT OUTER joins where one large fact table joins up to four smaller dimension tables, with each small table under 5 million rows and 5 GiB or less if unpartitioned or 1 GiB or less of referenced partitions if partitioned. Combining BI Engine with materialized views that join data into one large flat table avoids repeating the same joins, and stale materialized views are recommended for optimal performance.

Window functions, also called analytic functions, run fully in BI Engine only under narrow conditions where the query scans exactly one unpartitioned table with fewer than 5 million rows, has no JOIN, and the scanned table size multiplied by the number of window function operators does not exceed 300 MiB. Two functions with identical OVER clauses and the same direct inputs share one operator, while different OVER clauses or different nested inputs count as separate operators. Input stages without window functions can still report FULL_INPUT acceleration in INFORMATION_SCHEMA.JOBS via bi_engine_statistics.acceleration_mode, with FULL_QUERY when the full query is accelerated.

3.4Materialized Views and Materialized Tables

BigQuery materialized views and materialized tables precompute the results of joins, filters, and aggregations so BI queries read one flat result instead of recomputing complex logic at run time. Data flows from base tables into the materialized view definition, and dashboard queries then read from the materialized view rather than re-executing the joins for every viewer or filter change. This pattern is critical when a logical view performs heavy per-row work over millions of records, which can cause timeouts when connected directly to Looker Studio.

When a query uses SQL features that BI Engine cannot accelerate, materialize the result into a regular table as a workaround through an ordered refresh:

1. Write the complex query in BigQuery.
2. Save the query results to a table.
3. Schedule the query to refresh the table hourly or daily, since refreshing every minute can invalidate the cache too frequently.
4. Point performance-critical dashboard queries at the new table and optionally accelerate that table with BI Engine.

Materialized tables trade freshness for speed and simplicity because viewers see data as of the last refresh rather than live base tables. Choose hourly or daily scheduled refresh when dashboards can tolerate stale data, and reserve live queries or BI Engine over base tables when viewers need up-to-the-minute results and the query shape is supported.

3.5Cached Results, Freshness, and Extracted Data

BigQuery cached query results, freshness controls, and extracted data reuse prior answers or snapshots to avoid rescanning data. BigQuery cached query results reuse the answer of a prior identical query so BigQuery does not rescan data, while BI Engine reuses cached columns and partitions to compute new filter and aggregation combinations quickly. Cached results give the fastest repeat of the exact same dashboard query but do not help when filters, date ranges, or groupings change, whereas BI Engine accelerates many different queries over the same preferred tables. To measure BI Engine accurately with Cloud Monitoring or INFORMATION_SCHEMA.BI_CAPACITIES and BI_CAPACITY_CHANGES, disable the Use cached results option so repeated runs do not appear faster than the in-memory layer alone.

Looker Studio adds its own freshness and snapshot controls that sit in front of BigQuery. The data freshness option balances up-to-date information against report performance, query cost, and quotas, so daily refresh may suit ad performance while social analytics may need multiple updates per day. An extracted data source stores a static snapshot of up to 100 MB, and report requests flow from the report to the snapshot instead of to the underlying dataset until the next scheduled update. Improve Data Studio performance (opens in a new tab) guidance and Optimize BigQuery data source performance (opens in a new tab) practices both emphasize tuning these freshness intervals alongside BI Engine and materialization.

3.6Choosing Between the Strategies

Choosing between the strategies requires comparing what each option reuses, how fresh results stay, and what each costs to operate:

Select BI Engine when many concurrent users interactively filter and aggregate the same frequently queried tables and need sub-second response on fresh data. Select materialized views or scheduled tables when queries repeat expensive joins, calculations, or unsupported SQL over large datasets and can tolerate scheduled staleness. Select cached results, longer freshness intervals, or extracted snapshots when dashboards repeat identical queries or serve large audiences where reducing BigQuery scans matters more than showing live rows.

Key Facts

• BigQuery BI Engine is an in-memory service that caches queried columns and partitions using reservations of 1 to 250 GiB per project and region billed per GiB per hour.
• A BI Engine multi-table query is accelerated only when every table is listed as a preferred table, and a materialized view query requires both the view and its base tables to be listed.
• Only incremental BigQuery materialized views support smart tuning that automatically reroutes base-table queries to the precomputed result.
• BI Engine accelerates leaf-level INNER and LEFT OUTER joins of one large fact table with up to four dimension tables under 5 million rows each.
• BigQuery cached results reuse only identical prior queries, while BI Engine accelerates many different filter and aggregation combinations over the same cached columns.