Thursday, 19 June 2025

Manually Refreshing Materialized Views in BigQuery: When and How to Do It

Materialized views in BigQuery are powerful for performance optimization, they precompute and store query results and enable faster analytics and lower costs. But what if you need the latest data immediately, before the automatic refresh kicks in? 

In such cases, BigQuery offers a built-in procedure to manually trigger a refresh of your materialized views.

 

Why Manually Refresh?

While automatic refreshes are convenient, there are situations where manual refresh makes sense:

 

·      You want to immediately reflect recent changes in base tables.

·      You're running performance or regression tests.

·      You're dealing with data anomalies and want to verify a fix.

 

How to Refresh a Materialized View Manually?

BigQuery provides a system procedure called BQ.REFRESH_MATERIALIZED_VIEW to manually refresh a materialize view.

 

Syntax

CALL BQ.REFRESH_MATERIALIZED_VIEW("project_id.dataset_id.materialized_view_name");

For example, I have ‘mv_total_sales_per_person’, and its last refresh time is Apr 6, 2025, 2:32:35 PM UTC+5:30.

 


Let’s refresh this view manually by calling the system procedure REFRESH_MATERIALIZED_VIEW.

CALL BQ.REFRESH_MATERIALIZED_VIEW("project_id.dataset_id.materialized_view_name");

 


 

You can observe that the last refresh time is updated after I trigger manual refresh.

 

How to see the last refresh time of a Materialized view?

SELECT last_refresh_time 
FROM my_project.my_dataset.INFORMATION_SCHEMA.MATERIALIZED_VIEWS 
WHERE table_name = 'your_view_name';

 

In summary, Materialized views are great for performance, and now you know how to take full control of their refresh lifecycle when needed.

  

Previous                                                    Next                                                    Home

No comments:

Post a Comment