Speeding Up BigQuery with Materialized Views

If you run the same aggregation query over and over, you are paying for the same work over and over. A dashboard that recalculates total sales per customer every time someone loads it scans the full orders table on every refresh, which is slow and expensive. Materialized views fix this. They precompute the result of a query, store it, and keep it up to date automatically, so you read from a fast, cheap cache instead of reprocessing raw data each time.

What a Materialized View Is

A materialized view is best understood as a cache for a SQL query. You define a query once, and BigQuery stores its results, refreshes them automatically as the underlying data changes, and serves answers from that stored copy whenever it can. The financial upside is significant: rather than paying to reprocess the entire query each time, you pay only for the incremental updates and for the data you actually query.

This is what separates a materialized view from a standard view. A standard view is just a saved query that runs from scratch every time you call it. A materialized view holds real, precomputed results.

Why They Are Worth Using

The benefits all stem from that precomputation. Query performance improves because the results are already calculated and optimised for reads. Costs drop because querying the view scans far less data than scanning the base table would. The data stays fresh because BigQuery refreshes the view automatically, typically within minutes. And they are easy to work with, because you query a materialized view exactly as you would a normal table.

The strongest fit is anywhere you repeatedly run the same aggregation. Dashboards that show the same metrics to many users benefit hugely, since you avoid rescanning the base data on every load. Daily summary tables stay current without you building an ETL pipeline to maintain them. Large fact tables with fixed aggregation logic become much cheaper to query. And frequently accessed metrics like top-N reports or rolling totals simply perform better.

How They Compare to Standard Views

The difference comes down to storage. A standard view stores no data; it computes on the fly every time, so its performance depends entirely on the size of the base table and its cost is incurred on every query. A materialized view stores its precomputed results, so it is consistently fast, cheaper to query because it scans the cached data rather than the full base table, and refreshed automatically in near real time. Both are read-only, so neither lets you write data through it. The trade-off is that a materialized view uses storage and comes with rules about what its defining query can contain.

Creating One

You create a materialized view with CREATE MATERIALIZED VIEW, followed by the query whose results you want to cache:

CREATE MATERIALIZED VIEW dataset.mv_name AS
SELECT
  customer_id,
  COUNT(*) AS orders,
  SUM(amount) AS total_spent
FROM dataset.orders
GROUP BY customer_id;

There are rules about what that query can contain, because BigQuery needs to be able to keep the results incrementally up to date. The view must be based on a single table. It must use deterministic aggregations like COUNT, SUM, MIN, and MAX. Constructs like JOIN, UNION, and LIMIT are not allowed in the classic form. What you can use freely are WHERE, GROUP BY, and HAVING clauses to filter and aggregate.

One thing worth knowing: BigQuery has been expanding what materialized views support over time, and newer versions allow some joins that the original feature did not. But the safest mental model, and the one that always works, is single-table aggregations. If you stick to that, you will never hit a surprise.

Querying One

Once created, a materialized view behaves like any other table. You select from it, filter it, and join to it just as you would a normal table:

SELECT *
FROM dataset.mv_name
WHERE total_spent > 1000

There is nothing special you need to do. The view looks and works like a regular table, and BigQuery handles the optimisation behind the scenes.

Confirming the View Was Used

One of the most useful features of materialized views is that BigQuery can use them automatically even when you query the base table directly, a behaviour called smart tuning. To confirm whether your query actually benefited from the view, check the query plan in the Execution Details of the BigQuery console after running the query, and look for a reference to the materialized view in the plan. If it appears, your query was served from the optimised, cached results rather than scanning the raw data.

A small correction worth making here: BigQuery does not have a standalone EXPLAIN statement the way some other databases do. Instead, you inspect the query plan through the Execution Details tab in the console, or programmatically via the job metadata. That is where you confirm the view was used.

How Refreshing Works

Materialized views keep themselves current without any effort on your part. BigQuery refreshes them automatically, typically every 30 minutes or sooner depending on activity. You do not need to trigger a manual refresh, since the whole process is managed for you. Importantly, refreshes are incremental: BigQuery processes only the rows that have changed since the last refresh rather than recomputing everything, which is what keeps the cost of maintaining the view low.

The one caveat is that this is not strictly real-time. The data in a materialized view is very fresh, usually within a few minutes of the base table, but if you need to-the-second accuracy, a materialized view is not the right tool.

What It Costs

The cost model has three parts. You pay for storage of the materialized view’s data, charged like any other table. You pay for the data scanned when you query the view, which is typically far less than scanning the base table. And you do not pay to scan the full base table on each query; that cost is only incurred during the incremental refreshes. The net effect is that querying a materialized view repeatedly is both cheaper and faster than running the same aggregation against raw data every time.

Monitoring Your Views

You can inspect the materialized views in a dataset using the INFORMATION_SCHEMA:

SELECT *
FROM `project.dataset.INFORMATION_SCHEMA.MATERIALIZED_VIEWS`

This shows useful metadata such as the last refresh time, last modified time, and size, which helps you keep track of how your views are behaving and when they were last updated.

The Limitations to Keep in Mind

Materialized views trade flexibility for speed, so there are constraints. In the classic form, they cannot use JOIN or UNION, so each view draws from a single table. Constructs like LIMIT, TOP, and DISTINCT are not supported, only aggregations. You cannot combine multiple data sources in one view. You cannot force a manual refresh, since the refresh is fully automatic. And the data is not real-time, usually lagging the base table by a few minutes. None of these are dealbreakers for the use cases materialized views are designed for, but they do define where the tool fits.

A Real-World Example

A sales dashboard is a textbook case. Suppose you want daily revenue per store, counting only completed transactions. Instead of recomputing this every time the dashboard loads, you cache it in a materialized view:

CREATE MATERIALIZED VIEW sales_summary AS
SELECT
  DATE(transaction_date) AS sale_date,
  store_id,
  SUM(total) AS daily_revenue
FROM dataset.sales_transactions
WHERE status = 'completed'
GROUP BY sale_date, store_id;

Now the dashboard reads pre-aggregated daily revenue per store from the view, which is fast and cheap, while BigQuery quietly keeps it up to date as new transactions land in the base table. Every dashboard load that would have scanned the full transactions table now scans a small, summarised result instead.

The Takeaway

A materialized view is a precomputed, automatically refreshed cache of a query that you read like a normal table. It makes repeated aggregations dramatically faster and cheaper, which is why it suits dashboards, daily summaries, and frequently accessed metrics so well. The cost is flexibility: you are limited to single-table aggregations without joins, unions, or limits, and the data is near real-time rather than instant. Define the view once over your heaviest recurring aggregation, let BigQuery handle the refreshes, and confirm it is being used through the query plan. For the right workload, few features give you a better return for so little effort.

See you soon.