BigQuery Materialized Views and BI Engine: Accelerating Analytical Queries

Google BigQuery's query engine is fast by design — columnar storage, massively parallel execution, automatic query optimisation. But for analytical workloads with complex aggregations and dashboards serving many concurrent users, even BigQuery's base performance can be insufficient. Materialized views and BI Engine are BigQuery's two primary mechanisms for sub-second query performance at scale, and they work through different mechanisms that suit different use cases.

BigQuery Materialized Views

A materialized view in BigQuery is a precomputed query result that is stored persistently and refreshed automatically when the underlying base tables change. Unlike a standard view — which recomputes its query every time it is queried — a materialized view returns its stored result directly, bypassing the full aggregation at query time.

The performance benefit is significant. A query that aggregates 10 billion rows daily to a daily summary can return in milliseconds from a materialized view, rather than the several seconds required to aggregate the full table. The aggregation is done once (when the view refreshes), not on every query.

Creating a materialized view:

CREATE MATERIALIZED VIEW dataset.daily_revenue_mv AS

SELECT

DATE(order_timestamp) as order_date,

product_category,

SUM(revenue) as total_revenue,

COUNT(*) as order_count

FROM dataset.orders

WHERE order_timestamp >= '2024-01-01'

GROUP BY 1, 2;

BigQuery refreshes the materialized view automatically within 5 minutes of changes to the base table. Refresh is incremental — only the changed data is reprocessed, not the entire view. This makes materialized views practical even for large base tables with frequent updates.

**Query rewriting.** When you run a query against the base table that BigQuery determines can be satisfied more efficiently from a materialized view, it rewrites the query automatically to read from the materialized view instead. This means existing queries can benefit from materialized views without any modification — BigQuery determines whether the view satisfies the query and routes accordingly.

**Limitations.** Materialized views support a subset of SQL: SELECT with aggregations, GROUP BY, and JOINs to other tables (with restrictions). Complex window functions, UNION ALL, and subqueries are not supported in materialized view definitions. For complex transformations, materialized views do not replace dbt models — they complement them by caching the output of aggregation-heavy queries.

**Cost considerations.** Materialized views have storage costs (the stored result) and refresh costs (the compute to reprocess changes). For views with infrequent base table changes and high query volume, the economics are strongly positive. For views with extremely frequent updates and low query volume, the refresh cost may exceed the query cost savings.

BigQuery BI Engine

BigQuery BI Engine is an in-memory analytics service that caches data in memory on BigQuery's compute infrastructure. Queries against BI Engine-accelerated tables bypass the standard disk-based execution path and resolve from memory — typically sub-second even for moderately complex queries.

BI Engine is optimised for dashboard workloads: many users running similar queries against a moderately sized dataset, expecting fast responses. It is the right tool when your Looker, Tableau, or Power BI dashboards are hitting BigQuery and users experience 3–10 second load times.

**Configuration.** BI Engine is configured at the project or reservation level. You allocate a number of gigabytes of BI Engine memory (minimum 1 GB). BI Engine automatically determines which tables to cache based on query patterns. You can also explicitly pin specific tables to BI Engine memory.

**Pricing.** BI Engine is priced per GB-hour of memory allocated, billed at a fixed rate regardless of actual usage. It is most cost-effective when the cache is well-utilised — heavily queried dashboards with a dataset that fits in the allocated memory.

**Preferred tables.** For predictable BI Engine behaviour, configure preferred tables — explicitly telling BI Engine to cache specific datasets. This prevents BI Engine from caching tables based on query patterns that may not reflect your priority data.

**Limitations.** BI Engine accelerates queries that can be satisfied from its cache. Queries that join cached tables to uncached tables, or that use functions not supported by BI Engine's execution engine, fall back to standard BigQuery execution. BI Engine acceleration is most reliable for aggregations and filters against pre-joined, wide tables — exactly the pattern produced by a good dimensional model.

Partitioning and Clustering: The Foundation

Before reaching for materialized views and BI Engine, ensure your tables are correctly partitioned and clustered. These are free optimisations (unlike BI Engine's memory costs) that often deliver most of the performance improvement needed.

**Partitioning.** BigQuery partitioning divides a table into segments based on a column value — typically a date or timestamp. A query that filters on the partition column reads only the relevant partitions, not the full table. For a 3-year events table partitioned by day, a query for "last 30 days" reads 30 of 1,095 partitions — roughly 3% of the data.

Partition on the column most commonly used as a filter. For time-series data, partition on event timestamp or date. Ensure queries always include a partition filter — a query without a partition filter reads the full table and is expensive.

**Clustering.** Clustering sorts data within each partition by up to four columns. A query that filters on a clustered column skips the blocks within the partition that do not match. For a table clustered on (country, product_category), a query filtering to "US, Electronics" reads only the blocks containing US Electronics rows.

Cluster on columns that are frequently used as filters or GROUP BY columns after the partition filter. High-cardinality columns (customer_id, session_id) are poor clustering choices because the blocks are too granular. Medium-cardinality columns (country, category, status) cluster well.

Query Optimisation Patterns

Even with correct partitioning, clustering, and materialized views, query writing patterns significantly affect performance:

**Avoid SELECT *.** Always specify the columns you need. BigQuery's columnar storage only reads the columns your query requests. SELECT * reads every column, including large text columns you do not need.

**Push filters early.** Filter conditions in WHERE clauses should eliminate as much data as possible before joins and aggregations. Filter to the partition first, then apply additional column filters.

**Use approximate aggregation functions.** For count distinct at scale, APPROX_COUNT_DISTINCT is orders of magnitude faster than COUNT(DISTINCT) with acceptable accuracy for most analytics. APPROX_QUANTILES replaces PERCENTILE_CONT.

**Avoid data skew in joins.** When joining two tables where one has a highly uneven distribution (most rows have the same join key value), BigQuery's distributed execution creates hot nodes. Use broadcast hints for small lookup tables, or pre-aggregate before joining.

**Use INFORMATION_SCHEMA to understand query costs.** The INFORMATION_SCHEMA.JOBS view in BigQuery logs all job executions including bytes processed. Audit expensive queries against the actual cost, and prioritise optimisation effort on the highest-cost workloads.

For BigQuery architecture and performance optimisation as part of a cloud data platform engagement, our data architecture consulting team works with organisations on Google Cloud — contact us to discuss your BigQuery performance requirements.

A former Microsoft data architect audits your data foundation, identifies your top priorities, and sends you a written plan. Free. No pitch.