Use materialized views

This document provides additional information about materialized views and howto use them. Before you read this document, familiarize yourself withIntroduction to materialized viewsandCreate materialized views.

Query materialized views

You can query your materialized views directly, the same way you query a regulartable or standard view. Queries against materialized views are always consistentwith queries against the view's base tables, even if those tables have changedsince the last time the materialized view was refreshed. Querying does notautomatically trigger a materialized refresh.

Note: If you delete a base table without first deleting the materialized view,queries over the materialized view fail. You must recreate the materialized view after you recreate the base table to query it again.

Required roles

To get the permissions that you need to query a materialized view, ask your administrator to grant you theBigQuery Data Viewer (roles/bigquery.dataViewer) IAM role on the base table of the materialized view and the materialized view itself. For more information about granting roles, seeManage access to projects, folders, and organizations.

This predefined role contains the permissions required to query a materialized view. To see the exact permissions that are required, expand theRequired permissions section:

Required permissions

The following permissions are required to query a materialized view:

  • bigquery.tables.get
  • bigquery.tables.getData

You might also be able to get these permissions withcustom roles or otherpredefined roles.

These permissions are required for queries in order to benefit fromsmart tuning.

For more information aboutIAM roles in BigQuery, seeIntroduction to IAM.

Incremental updates

Incremental updates occur when BigQuery combines the cachedview's data with new data to provide consistent query results while still usingthe materialized view. For single-table materialized views, this is possible ifthe base table is unchanged since the last refresh, or if only new data wasadded. ForJOIN views, only tables on the left side of theJOIN can haveappended data. If one of the tables on the right side of aJOIN has changed,then the view cannot be incrementally updated.

If the base table had updates or deletions since the last refresh, or if thematerialized view's base tables on the right side of theJOIN have changed,then BigQuery doesn't use incremental updates and insteadautomatically reverts to the original query. For more information about joinsand materialized views, seeJoins. The following areexamples of Google Cloud console, bq command-line tool, and API actions that can cause anupdate or deletion:

  • Data manipulation language (DML)UPDATE,MERGE, orDELETE statements
  • Truncation
  • Partition expiration

The following metadata operations also prevent a materialized view from being incrementally updated:

  • Changing partition expiration
  • Updating or dropping a column

If a materialized view can't be incrementally updated, then its cached data isnot used by queries until the view is automatically or manually refreshed. Fordetails about why a job didn't use materialized view data, seeUnderstand whymaterialized views wererejected.Additionally, materialized views cannot be incrementally updated if its basetable has accumulated unprocessed changes for a time period greater than thetable'stime travelinterval.

Partition alignment

If a materialized view is partitioned, BigQuery ensures that itspartitions are aligned with the partitions of the base table's partitioningcolumn.Aligned means that the data from a particular partition of thebase table contributes to the same partition of the materialized view. Forexample, a row from partition20220101 of the base table would contribute onlyto partition20220101 of the materialized view.

When a materialized view is partitioned, the behavior described inIncremental updates occurs for each individualpartition independently. For example, if data is deleted in one partition ofthe base table, then BigQuery can still use the materializedview's other partitions without requiring a full refresh of the entirematerialized view.

Materialized views with inner joins can only be aligned with one of their basetables. If one of the non-aligned base tables changes, it affects the entireview.

Smart tuning

BigQuery automatically rewrites queries to use materializedviews whenever possible. Automatic rewriting improves query performance andreduces costs without changing query results. Querying does not automaticallytrigger a materialized refresh. For a query to be rewritten using smart-tuning,the materialized view must meet the following conditions:

  • Belong to the same project as one of its base tables or the project thatthe query is running in.
  • Use the same set of base tables as the query.
  • Include all columns being read.
  • Include all rows being read.

Smart tuning isn't supported for the following:

Smart tuning examples

Consider the following materialized view query example:

SELECTstore_id,CAST(sold_datetimeASDATE)ASsold_dateSUM(net_profit)ASsum_profitFROMdataset.store_salesWHERECAST(sold_datetimeASDATE)>='2021-01-01'ANDpromo_idISNOTNULLGROUPBY1,2

The following examples show queries and why those queries are or aren'tautomatically rewritten using this view:

QueryRewrite?Reason
SELECT
SUM(net_paid) AS sum_paid,
SUM(net_profit) AS sum_profit
FROM dataset.store_sales
WHERE
CAST(sold_datetime AS DATE) >= '2021-01-01' AND
promo_id IS NOT NULL		NoThe view must include all columns being read. The view does not include 'SUM(net_paid)'.
SELECT SUM(net_profit) AS sum_profit
FROM dataset.store_sales
WHERE
CAST(sold_datetime AS DATE) >= '2021-01-01' AND
promo_id IS NOT NULL		Yes
SELECT SUM(net_profit) AS sum_profit
FROM dataset.store_sales
WHERE
CAST(sold_datetime AS DATE) >= '2021-01-01' AND
promo_id IS NOT NULL AND
customer_id = 12345		NoThe view must include all columns being read. The view does not include 'customer'.
SELECT SUM(net_profit) AS sum_profit
FROM dataset.store_sales
WHERE
sold_datetime= '2021-01-01' AND
promo_id IS NOT NULL
NoThe view must include all columns being read. 'sold_datetime' is not an output (but 'CAST(sold_datetime AS DATE)' is).
SELECT SUM(net_profit) AS sum_profit
FROM dataset.store_sales
WHERE
CAST(sold_datetime AS DATE) >= '2021-01-01' AND
promo_id IS NOT NULL AND
store_id = 12345		Yes
SELECT SUM(net_profit) AS sum_profit
FROM dataset.store_sales
WHERE
CAST(sold_datetime AS DATE) >= '2021-01-01' AND
promo_id = 12345		NoThe view must include all rows being read. 'promo_id' is not an output so the more restrictive filter can't be applied to the view.
SELECT SUM(net_profit) AS sum_profit
FROM dataset.store_sales
WHERECAST(sold_datetime AS DATE) >= '2020-01-01'
NoThe view must include all rows being read. The view filter for dates in 2021 and after, but the query reads dates from 2020.
SELECT SUM(net_profit) AS sum_profit
FROM dataset.store_sales
WHERE
CAST(sold_datetime AS DATE) >= '2022-01-01' AND
promo_id IS NOT NULL		Yes

Understand whether a query was rewritten

To understand if a query was rewritten by smart tuning to use a materializedview, inspect thequery plan. If thequery was rewritten, then the query plan contains aREAD my_materialized_viewstep, wheremy_materialized_view is the name of the materialized view used. Tounderstand why a query didn't use a materialized view, seeUnderstand whymaterialized views were rejected.

Understand why materialized views were rejected

If you have disabled automatic refresh for your materialized view and the tablehas unprocessed changes, then the query might be faster for several days butthen start revert to the original query resulting in slower processing speed.To benefit from materialized views, enable automatic refresh or manually refreshregularly, and monitor materialized view refresh jobs to confirm they succeed.

The steps to understand why a materialized view was rejecteddepend on the type of query that you used:

  • Direct query of the materialized view
  • Indirect query in whichsmart tuning might choose to use thematerialized view

The following sections provide steps to help you understand why a materializedview was rejected.

Direct query of materialized views

Direct queries of materialized views might not use cached data in certaincircumstances. The following steps can help you understand why the materializedview data was not used:

  1. Follow the steps inMonitor materialized viewuseand find the target materialized view in thematerialized_view_statistics fieldfor the query.
  2. Ifchosen is present in the statistics and its value isTRUE, then thematerialized view is used by the query.
  3. Review therejected_reason field to find next steps. In most cases, you canmanually refreshthe materialized view or wait for the nextautomaticrefresh.

Query with smart tuning

  1. Follow the steps inMonitor materialized viewuseand find the target materialized view in thematerialized_view_statisticsfor the query.
  2. Review therejected_reason to find next steps. For example, if therejected_reason value isCOST, then smart tuning identified moreefficient data sources for cost and performance.
  3. If the materialized view is not present, try a direct query of thematerialized view and follow the steps inDirect query of materializedviews.
  4. If the direct query does not use the materialized view, then the shape of thematerialized view does not match the query. For more information about smarttuning and how queries are rewritten using materialized views, seeSmart tuning examples.

Frequently asked questions

When should I use scheduled queries versus materialized views?

Scheduled queries are a convenient way torun arbitrarily complex calculations periodically. Each time the query runs, itruns fully, with no benefit from previous results, and you pay the full computecost for the query. Scheduled queries are ideal when you don't need the freshestdata and have a high tolerance for data staleness.

Materialized views are best suited for when you need to query the latest datawith minimized latency and cost by reusing the previously computed result.You can use materialized views aspseudo-indexes, accelerating queries to thebase table without updating any existing workflows. The--max_stalenessoptionlets you define acceptable staleness for your materialized views, providingconsistently high performance with controlled costs when processinglarge, frequently changing datasets.

As a general guideline, whenever possible and if you are not running arbitrarilycomplex calculations, use materialized views.

Some queries over materialized views are slower than same queries over manually materialized tables. Why is that?

In general, a query over a materialized view isn't always as performant as aquery over the equivalent materialized table. The reason is that materializedviews always return fresh results, and must account forchanges to their base tables since the last view refresh.

Consider this scenario:

CREATEMATERIALIZEDVIEWmy_dataset.my_mvASSELECTdate,customer_id,region,SUM(net_paid)astotal_paidFROMmy_dataset.salesGROUPBY1,2,3;CREATETABLEmy_dataset.my_materialized_tableASSELECTdate,customer_id,region,SUM(net_paid)astotal_paidFROMmy_dataset.salesGROUPBY1,2,3;

For example, this query:

SELECT*FROMmy_dataset.my_mvLIMIT10
typically runs much more slowly than this query:
SELECT*FROMmy_dataset.my_materialized_tableLIMIT10
In order to provide consistently up-to-dateresults, BigQuery must query new rows in the base table andmerge them into the materialized view before applying the 'LIMIT 10' predicate.As a result, slowness remains, even if the materialized view is fullyup-to-date.

On the other hand, aggregations over materialized viewsare typically as fast as queries againstthe materialized table. For example, the following:

SELECTSUM(total_paid)FROMmy_dataset.my_mvWHEREdate >'2020-12-01'
Should be as fast as this:
SELECTSUM(total_paid)FROMmy_dataset.my_materialized_tableWHEREdate >'2020-12-01'

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.