Non-Incremental Materialized Views Now Available in BigQuery

Non-incremental materialized views in BigQuery are a type of materialized view that do not rely on incremental recalculation. Instead of only considering new and updated rows, every refresh of a non-incremental materialized view is a full refresh. This approach is useful for scenarios such as reporting or frequently accessed large fact tables with aggregate functions.

With the latest update to BigQuery materialized views, the restrictions on the query have been mostly relaxed. Now, you can create materialized views that include:

• FULL OUTER JOIN
• HAVING clauses
• Analytic functions

These enhancements allow for more complex queries to be pre-computed, improving performance for a wider range of use cases.

To optimize the refresh schedule and max_staleness options for non-incremental materialized views, you need to consider the tradeoff between data staleness and cost optimization. The allow_non_incremental_definition option will trigger a full refresh every time, so you should plan your refresh schedule according to how often you need the most up-to-date data versus how much you are willing to spend on refreshing the views.

The BigQuery materialized view recommender is a new tool currently in preview that analyzes your past query jobs to help identify potential opportunities for using materialized views. It suggests where materialized views can be applied to improve performance, based on your historical querying patterns.