search

doitintl / bigquery-optimization-queries

Queries to assist with BigQuery cost and performance.
MIT License
72 stars 31 forks source link

project_storage_model_recommendation edge cases #17

Closed renato-doit closed 3 months ago

renato-doit commented 3 months ago

Query in question: https://github.com/doitintl/bigquery-optimization-queries/blob/main/information_schema/project_storage_model_recommendation.sql

On line 209 for the "recommendation" field, there is no condition covering for when the current storage model is PHYSICAL.

Got a customer that ran this query and they are on Physical storage model and the calculations show that staying in physical model is cheaper than changing to Logical but the recommendation they are getting reads "Do nothing as logical storage is the best option (currently logical)".

renato-doit commented 3 months ago

Also, another edge case found: one calculation shows "savings_in_price_if_physical_is_chosen" is -$1.6K, which means that it is better to be billed by Logical, but the recommendation is to "Change dataset to physical storage for additional savings"