Introduction to materialized views

Materialized views are precomputed views that periodically store the results of a SQL query. In some use cases, materialized views reduce the total processing time and related charges by reducing the amount of data to be scanned for each query. You can query materialized views as you would other data resources.

The following use cases highlight the value of materialized views:

• Pre-process data. Improve query performance by preparing aggregates, filters, joins, and clusters.
• Dashboard acceleration. Empower BI tools like Looker that frequently query the same aggregate metrics—for example, daily active users.
• Real-time analytics on large streams. Can provide faster responses on tables that receive high-velocity streaming data.
• Cost management. Reduce the cost of repetitive, expensive queries over large datasets.

Key characteristics of materialized views include the following:

• Zero maintenance. Materialized views are precomputed in the background when the base tables change. Any incremental data changes from the base tables are automatically added to the materialized views, with no user action required.
• Fresh data. Materialized views return fresh data. If changes to base tables might invalidate the materialized view, then data is read directly from the base tables. If the changes to the base tables don't invalidate the materialized view, then rest of the data is read from the materialized view and only the changes are read from the base tables.
• Smart tuning. If any part of a query against a base table can be resolved by querying the materialized view, then BigQuery reroutes the query to use the materialized view for improved performance and efficiency. For information about how and when smart tuning can improve queries, see Use materialized views.

Incremental and non-incremental materialized views

There are two basic kinds of materialized views:

• Incremental materialized views support a limited set of features. To learn more about supported SQL syntax for materialized views, see Create materialized views. Only incremental materialized views can take advantage of smart tuning.
• Non-incremental functions support most of the syntaxes that incremental materialized views don't support.

When you create materialized views, by default BigQuery only lets you create views based upon incremental queries. To create a non-incremental view, you can specify allow_non_incremental_definition = true in the materialized view's definition.

The best type of materialized view to use depends on your situation. The following table compares the features of incremental and non-incremental materialized views:

Authorized materialized views

You can create an authorized materialized view to share a subset of data from a source dataset to a view in a secondary dataset. You can then share this view to specific users and groups (principals) who can view the data you share. Principals can query the data you provide in a view, but they can't access the source dataset directly.

Authorized views and authorized materialized views are authorized in the same way. For details, see Authorized views.

Interaction with other BigQuery features

The following BigQuery features work transparently with materialized views:

• Query plan explanation: The query plan reflects which materialized views are scanned (if any), and shows how many bytes are read from the materialized views and base tables combined.

• Query caching: The results of a query that BigQuery rewrites using a materialized view can be cached subject to the usual limitations (using of deterministic functions, no streaming into the base tables, etc.).

• Cost restriction: If you have set a value for maximum bytes billed, and a query would read a number of bytes beyond the limit, the query fails without incurring a charge, whether the query uses materialized views, the base tables, or both.

• Cost estimation using dry run: A dry run repeats query rewrite logic using the available materialized views and provides a cost estimate. You can use this feature as a way to test whether a specific query uses any materialized views.

Materialized views pricing

Costs are associated with the following aspects of materialized views:

• Querying materialized views.
• Maintaining materialized views, such as when materialized views are refreshed. The cost for automatic refresh is billed to the project where the view resides. The cost for manual refresh is billed to the project in which the manual refresh job is run. For more information about controlling maintenance cost, see Refresh job maintenance.
• Storing materialized view tables.

¹Where possible, BigQuery reads only the changes since the last time the view was refreshed. For more information, see Incremental updates.

Storage cost details

For AVG, ARRAY_AGG, and APPROX_COUNT_DISTINCT aggregate values in a materialized view, the final value is not directly stored. Instead, BigQuery internally stores a materialized view as an intermediate sketch, which is used to produce the final value.

As an example, consider a materialized view that's created with the following command:

CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table AS
SELECT date, AVG(net_paid) AS avg_paid
FROM project-id.my_dataset.my_base_table
GROUP BY date

While the avg_paid column is rendered as NUMERIC or FLOAT64 to the user, internally it is stored as BYTES, with its content being an intermediate sketch in proprietary format. For data size calculation, the column is treated as BYTES.

What's next

• Overview of logical and materialized views
• Create materialized views
• Use materialized views
• Manage materialized views