Recommendations overview

BigQuery works with Active Assist to provide various recommendations that you can use to optimize your BigQuery resources.

Recommendations are generated by recommenders, which use machine learning (ML) or heuristics to provide recommendations on how to optimize your BigQuery resource usage.

You can view and manage recommendations across the different recommenders by using BigQuery in the Google Cloud console—either in the BigQuery Recommendation Hub, or by recommendation notifications in BigQuery Studio. You can also view recommendations through various INFORMATION_SCHEMA views at the project and organization level.

To view your BigQuery recommendations along with other recommendations across the Google Cloud console, use the Active Assist Recommendation Hub.

BigQuery recommenders

BigQuery offers the following recommenders:

  • Partitioning and clustering recommender, which analyzes your query behavior to find opportunities for partitioning and clustering to optimize your BigQuery tables.
  • Materialized view recommender, which finds opportunities to use materialized views to optimize your workflows.
  • IAM recommender, which analyzes permissions on your BigQuery datasets and suggests Identity and Access Management (IAM) role updates for principals that have excess permissions.

View recommendations

To view your recommendations using the Google Cloud console, do the following:

  1. In the Google Cloud console, go to the BigQuery page.

    Go to BigQuery

  2. In the navigation menu, click Recommendations.

    The Recommendations page opens, showing all recommendations that are generated for the current project or organization, depending on the selected scope.

  3. To see more information about a specific recommendation or insight, click a recommendation.

    You can also click the Recommendations notification in BigQuery Studio to view your available BigQuery recommendations.

    Clicking Recommendations lets you view all recommendations.

View recommendations with INFORMATION_SCHEMA

You can also view your recommendations and insights using INFORMATION_SCHEMA views. For example, you can use the INFORMATION_SCHEMA.RECOMMENDATIONS view to view your top three recommendations based on slots savings, as seen in the following example:

+---------------------------------------------------+--------------------------------------------------------------------------------------------------+
|                    recommender                    |   target_resources      | est_gb_saved_monthly | slot_hours_saved_monthly |  last_updated_time
+---------------------------------------------------+--------------------------------------------------------------------------------------------------+
| google.bigquery.materializedview.Recommender      | ["project_resource"]    | 140805.38289248943   |        9613.139166666666 |  2024-07-01 13:00:00
| google.bigquery.table.PartitionClusterRecommender | ["table_resource_1"]    | 4393.7416711859405   |        56.61476777777777 |  2024-07-01 13:00:00
| google.bigquery.table.PartitionClusterRecommender | ["table_resource_2"]    |   3934.07264107652   |       10.499466666666667 |  2024-07-01 13:00:00
+---------------------------------------------------+--------------------------------------------------------------------------------------------------+

For more information, see the following resources:

What's next