Friday, 20 June 2025

When Does BigQuery Keep Materialized Views Fresh?

BigQuery Materialized Views (MVs) can boost performance by storing pre-computed query results. But to keep those results accurate and up-to-date, the freshness of an MV depends on how your base tables are modified.

 

Here are the scenarios where BigQuery keeps MVs fresh automatically:

 

1. Append-Only Inserts (Best Case Scenario)

Materialized views stay fresh when new rows are simply appended to the base table, without modifying or deleting existing ones.

 

BigQuery supports incremental refresh, processing only the new data instead of recomputing everything.

 

For example, you have an MV summarizing daily sales. As new transactions arrive every hour, BigQuery just processes the recent rows to keep the MV up-to-date.

 

2. Deletes and Updates (Breaks Freshness)

Materialized views do not stay fresh automatically when:

 

·      Rows are deleted

·      Rows are updated

 

Why it breaks?

MVs store cached results based on previous data. Once rows are removed or changed, BigQuery cannot track the exact delta and must recompute the MV.

 

To ensure your materialized views stay fresh without manual intervention, design your pipeline to follow an append-only pattern wherever possible.

Previous                                                    Next                                                    Home

No comments:

Post a Comment