BigQuery Materialized Views and Why You Should be Using Them

TL;DR BigQuery materialized views are great. You should use them!

In the database world, a materialized view is a database object that contains the results of a pre-computed query. Materialized views have been around for quite some time, first appearing with Oracle 8i Database and then proliferating across most proprietary and open source databases in some for or another. Materialized views are often used to support BI and OLAP workloads as part of a IO reduction strategy. By pre-computing common queries and caching the results, the database optimizer can re-write inflight queries to use the materialized view to reduce CPU and IO and reduce runtime.

A good example for using a materialized view is retail transaction data. Most business users don’t look at the granular transaction data itself. Instead, they want to look at the transaction data rolled up to particular dimension, like by store, product, date or all 3. When you’re working with billions of rows, doing that type computation for hundreds or thousands of queries can be extremely expensive from a compute and IO perspective. A materialized view can pre-aggregate those common business queries and cache the result. In doing so, a simple total sales by store by date doesn’t need to scan all the raw data to compute the result set. Instead, it uses the materialized view to retrieve the pre-computed data it needs to satisfy the query and this can dramatically reduce the amount of data processed by any given query.