BigQuery Materialized Views without Aggregation

New Google Feature is now generally available

If you rely on faster results the following update might be interesting for you, because now Google BigQuery allows materialized views without aggregation.

What are materialized Views and why using them?

In BigQuery materialized views are pre-computed views that cache the results of a query periodically. You can use that feature to improve performance and efficiency. BigQuery uses pre-computed results from materialized views and, when possible, reads only delta changes from the base table to compute actual results. Materialized views can be queried directly or used by BigQuery Optimization to process queries to the base tables.

Queries that use materialized views are typically faster and consume fewer resources than queries that retrieve the same data exclusively from the base table. Materialized views can significantly improve the performance of workloads that have frequent and repetitive queries [1].

How to create a Materialized View

Creating a materialized view is actually quite simple — you can use the following statement as a blueprint [2]:

CREATE MATERIALIZED VIEW  project.my_dataset.my_mv_table
AS SELECT product_id, SUM(clicks) AS sum_clicks
FROM project.my_dataset.my_base_table
GROUP BY 1

Read the full guide here [2]. Besides this update, you can also use inner joins with materialized views.

I find this feature extremely interesting and useful. The possibilities are high that Google will provide us with many great updates again this year and make BigQuery even more powerful. I can think of several good use cases, where I would use this feature and I’m looking forward to the next releases. Especially the three following benefits you should keep in mind:

• Reducing execution time and lowering the cost of queries with aggregate functions. This is great for when you want to prepare the data for further data processing.
• Automatic and transparent BigQuery query optimization.
• Real-time data aggregation if you need to access data to make real-time decisions.

Sources and Further Readings

[1] Google, Release notes (2022)

[2] Google, Create materialized views (2022)