Max_Staleness parameters

[jbibi]

Hi Team,

How can i learn more related to max_staleness in relation to materialized view as i am trying to create a materialized view with refresh interval of 15 mins but I want to have updated data in the view when user query that view so want to set max_staleness but i am not aware how it works and what parameter works and how and i am not able to find any explanations regarding this so it will be great help if any link can be provided.

Thanks in Advance.

[ms4446]

Understanding max_staleness in BigQuery Materialized Views

Functionality:

• Controls data freshness when querying a materialized view.
• Balances data freshness with query performance.

How It Works:

1. Set a maximum staleness duration (e.g., 5 minutes) using max_staleness.
2. When the view is queried:
   • If the last refresh is within the max_staleness duration, BigQuery uses cached data in the view for faster performance.
   • If the last refresh exceeds the max_staleness duration, BigQuery queries the base tables for the latest data, potentially affecting performance.

Parameters:

• max_staleness: Specifies the maximum staleness interval (e.g., INTERVAL 5 MINUTES).

Creating a Materialized View with max_staleness:

CREATE MATERIALIZED VIEW my_view AS
SELECT * FROM my_table
WITH MAX_STALENESS 5 MINUTES;

Key Considerations:

• Check if max_staleness is still in preview or fully released (as of April 2023).
• Not compatible with partitioning in BigQuery-managed storage.
• Particularly beneficial for large,frequently changing datasets.

For More Information:

• BigQuery documentation on materialized views:https://cloud.google.com/bigquery/docs/materialized-views
• Blog post with examples:https://medium.com/codex/using-the-max-staleness-materialized-view-option-in-bigquery-d5977a8ed8fc

[jbibi]

Thanks for your response please help me if my below set up is correct. As per my requirement I need to create Materialize view which should refresh data every 15 mins saying that what should be max staleness and is the below statement is correct to say that materialize view refresh frequency is set to every 15 mins and the max staleness is 30 mins that means if the data is not refreshed in materialized view in last 30 mins and user is querying(say its around at 35 mins) data from materialized view then bigquery will combine data from cheche data and updated data from base table:

OPTIONS (enable_refresh = true, refresh_interval_minutes = 15, max_staleness = INTERVAL "0:30:0" HOUR TO SECOND)

Please correct me if my understanding is wrong.

[ms4446 · Accepted solution]

Here is clarification on better understanding Refresh Frequency and Max Staleness in BigQuery Materialized Views:

Refresh Frequency:

• Correct: Setting the materialized view to refresh every 15 minutes is accurately described. This means the view will attempt to update its data from the base tables at this interval.

Max Staleness:

• Clarification: Max staleness does not trigger automatic refreshes but controls the behavior of queries against the materialized view.
• Function: It determines whether BigQuery uses the cached data in the view or re-queries the base tables.
• Your Setup: With max_staleness = INTERVAL "0:30:0" HOUR_TO_SECOND, BigQuery will:
  • Use cached data if the last successful refresh was within the past 30 minutes.
  • Query the base tables for fresh data if the last refresh was more than 30 minutes ago, which may affect query performance.

Key Point:

• Max staleness is solely about the age of the cached data at the time of a query. It does not initiate data refreshes in the materialized view.

Clarification on Data Handling:

• BigQuery does not mix cached and fresh data. It will use either the cached data (if within the max staleness period) or re-query the base tables for fresh data (if beyond the max staleness period).

Recommendations:

• Set max_staleness to at least the same duration as the refresh interval (15 minutes or more) to ensure efficient use of cached data between scheduled refreshes.
• For critical data freshness, consider a shorter max staleness duration, but be aware of potential performance impacts and increased costs.

Additional Considerations:

• Data Change Frequency: A shorter max staleness might be necessary for datasets with rapid changes to ensure accuracy.
• Query Performance Impact: Direct queries to base tables can impact performance, especially for large datasets.
• Cost Implications: Frequent refreshes and base table queries can lead to higher BigQuery costs.

Best Practices:

• Regularly monitor the materialized view's usage and query performance to fine-tune the refresh intervals and max staleness settings for optimal balance.
• Consider partitioning for large datasets to improve efficiency. However, note that max_staleness is not compatible with partitioning in BigQuery-managed storage.

[jbibi]

Thanks for that detailed explanation it is really helpful 🙂 

[AlaB]

Hello, i stumbled on this thread and find it to be an amazing explanation thank you for that.

But i have two questions about what you said :

• Query Performance Impact: Direct queries to base tables can impact performance, especially for large datasets.
• Cost Implications: Frequent refreshes and base table queries can lead to higher BigQuery cost.

Can you explain more how this might impact the Query Performance ?

And what are the cost implications from Bigquery side with a low max_Staleness or a high max_Staleness ,  you should be querying the same (more or less few new rows from the refresh) table sizes ?

I have a use case of Datastream loading to Bigquery some tables so the max_Staleness is set by Datastream into bigquery table.

I want to know the cost impact will be on Datastream or Bigqurey with a low max_Staleness

Thanks in advance.

[ms4446]

Hi @AlaB ,

Directly querying large base tables can indeed be resource-intensive and slow. Materialized views act as precomputed result sets, significantly accelerating query performance by eliminating the need to recalculate complex queries each time.

The max_staleness setting controls how fresh your materialized view data is:

• Low max_staleness: Ensures near real-time data, but more frequent refreshes can introduce slight query latency during updates. This is usually minimal for well-designed views.
• High max_staleness: Allows the view to lag behind base tables, leading to faster queries but potentially slightly outdated results.

Cost Implications

BigQuery costs are primarily driven by data scanned during queries and refreshes:

• Low max_staleness: More frequent refreshes mean more data scanned, potentially increasing costs, especially for large or rapidly changing base tables.
• High max_staleness: Less frequent refreshes reduce data scanned for updates, generally lowering costs.

Datastream and Your Use Case

Since Datastream manages max_staleness for your BigQuery tables, the cost impact is mainly on the BigQuery side. A lower max_staleness might increase costs due to more frequent refreshes. However, weigh this against the value of fresher data for your specific needs.

Finding the Optimal Balance

The best max_staleness depends on several factors:

• Data change frequency: How often does your source data update?
• Query patterns: How time-sensitive are your analyses?
• Cost tolerance: What's your budget for BigQuery?
• Business requirements: What level of data freshness is acceptable for decision-making?

Recommendations

1. Start with a reasonable max_staleness: Begin with a value balancing freshness and cost, then monitor and adjust as needed.
2. Design efficient materialized views: Target frequently accessed data and optimize aggregations and filters.
3. Consider incremental updates: If data changes frequently, update only changed portions for efficiency.
4. Leverage BigQuery BI Engine: Accelerate analytics queries, potentially reducing the need for very low max_staleness.
5. Implement cost controls: Set budgets and alerts to manage spending effectively.
6. Explore real-time options: For up-to-the-minute data, consider BigQuery streaming or other real-time solutions alongside materialized views.

[AlaB]

Hi @ms4446 

Thanks for this great explanation, yes i will take your advice to weigh in the tradeoff between freshness of data and cost.

All the Best.

[igor-okuyama]

Nice thread here! I understand @ms4446 said that max_staleness does not trigger a refresh, but I was wondering what would happen if I set the following options:

  enable_refresh = false, 
  max_staleness = INTERVAL "24" HOUR,
  allow_non_incremental_definition = true

after the initial 24 hours, will the materialized view always query from base tables like a normal "view"? Or will it update the cache which should last for more 24 hours?

I am trying to achieve the following: I want to have a materialized view that always have data updated up to 24 hours, but that does not need to be refreshed daily, but only when a user requests it and the cache has more than 24 hours of existence.

For instance, consider this timeline:

•  2024-07-18: the MV is created, and some people access its data.
• 2024-07-19: no one accesses the MV, so it is outdated.
• 2024-07-20:
  • At 08:00 AM someone accesses the MV, and it is refreshed and should last for more 24 hours
  • At 09:00 AM someone accesses the MV, and he gets data from the cache
• 2024-07-21: no one accesses the MV, so it is outdated.
• 2024-07-22: no one accesses the MV, so it is outdated.
• 2024-07-23: someone accesses the MV, and it is refreshed and should last for more 24 hours.

[jbibi]

Hello @igor-okuyama I want to understand that you are trying to explain how Materialized view work or you have already did experimented on it and posting your observation  please suggest  this will be great add on to my knowledge. Thanks

[igor-okuyama]

I have not tested it yet, but I am planning do it soon.

[jbibi]

Thanks @igor-okuyama I did not got chance to do more experiment around this and understand it better so thought if you have real time exp and can share it here. I will appreciate for  your effort and sharing your exp with us here. Thanks in advance.

[Valmir]

Hi Igor and everyone, this use case is very close to what I needed as well. Do you have any update or experience that you could share? Did it work as intended? Thank you.  

[Julia87]

You can learn more about the `max_staleness` parameter and how it works with materialized views by checking the official documentation of your database system. For example, if you're using PostgreSQL, their documentation provides details on `max_staleness` and materialized view refresh options.

[jbibi]

I have already read the docs some time last year but I want to know if any one has real time experience to understand it better. And I hope you understand I am just asking to share experience not trying to some one to read docs and teach me here.