Snowflake Optima¶
Snowflake Optima extends Snowflake’s core principles of performance and simplicity by applying an intelligent approach to workload optimization. Instead of requiring manual tuning, Snowflake Optima continuously analyzes workload patterns and implements the most effective strategies automatically. Snowflake Optima ensures that queries run faster and more cost-efficiently, without added configuration or maintenance. By anticipating and adapting to the evolving nature of SQL workloads, Snowflake Optima automatically improves performance.
Note
Snowflake Optima is only available on Snowflake generation 2 standard warehouses.
Optima Indexing¶
Optima Indexing is a Snowflake Optima feature that automatically analyzes workloads to create and maintain indexes in the background. Optima Indexing is built on top of the search optimization service.
By continuously monitoring SQL workloads, Optima Indexing identifies opportunities to improve performance — such as repetitive point-lookup queries on a table — and automatically generates hidden indexes to accelerate those workloads. These indexes are built and maintained on a best-effort basis, without requiring user intervention.
There are no additional costs for Optima Indexing, and because it is fully integrated into Snowflake, no additional configuration or effort is required to benefit from improved performance.
For specialized workloads that demand guaranteed performance — for example, threat detection in the cybersecurity industry — you can still directly apply search optimization. This option provides consistent index freshness and ultimately consistent performance for scenarios where near real-time results are critical.
Monitoring Snowflake Optima use¶
You can monitor Snowflake Optima use on the following panes in the Query Profile tab under Query History in Snowsight:
Query insights pane¶
The Query insights pane displays each type of insight that was detected for a query and lists each instance of that insight type that was detected for the query. To learn more about the condition that was detected, select View next to an entry in the Query insights pane. If Snowflake Optima was used to optimize the given query, then Snowflake Optima used appears and the details are displayed.
The following image shows an example of the Query insights pane that indicates that Snowflake Optima was used:
Statistics pane¶
To view pruning statistics for Snowflake Optima, open the Statistics pane on the Query Profile tab. Look for the row labeled Partitions pruned by Snowflake Optima. This row shows the number of partitions skipped during query execution, indicating how Snowflake Optima improved performance by reducing the amount of data scanned.
The following image shows an example the Statistics pane that indicates that Snowflake Optima was used:
