Introducing Snowflake Optima: Accelerating Performance with Intelligent Optimizations

Snowflake enables innovation by helping users rapidly generate insights from their data. From the beginning, our twin design tenets have been performance and simplicity. Today, we are excited to introduce Snowflake Optima, a novel optimization engine that represents a paradigm shift in effortless performance.

Snowflake has long set the standard for performance and simplicity in enterprise data platforms. From multi-cluster warehouses that dynamically scale compute, to features like Automatic Clustering and Search Optimization Service that continuously optimize table data and access, Snowflake has empowered organizations to quickly turn their data into insights. More recently, intelligent optimizations we’re making in Gen2 warehouses — like smart DML acceleration — further demonstrate this commitment.

However, the pace of change is accelerating as analytics becomes accessible to everyone, building data applications becomes easier, and a rapidly growing fleet of intelligent agents executes more and more tasks for customers. Snowflake Optima leverages deep intelligence, automatically analyzes your workload history and delivers intelligent optimizations that automatically accelerate performance. The result: industry-leading performance, made even simpler.

Optima Indexing

Snowflake Optima enables a range of new capabilities, starting with Optima Indexing, now available on Gen2 warehouses.

When we launched the Search Optimization Service a few years ago, our goal was to simplify how customers optimize recurring point-lookup queries. On legacy systems, creating and maintaining search indexes is a burdensome process. Admins have to monitor for index fragmentation, manage index rebuilds and storage, and weigh read versus write performance trade-offs. With the Search Optimization Service, we removed this burden.

However, a persistent challenge in configuring search optimization has been determining which queries benefit and whether the benefit is worth the cost. Often, the end users running queries aren’t directly responsible for costs, and they don’t have the knowledge or ability to implement optimizations. And often, administrators aren’t familiar with all of the new workloads their users are spinning up, let alone have time to analyze and optimize them. This leads to many missed opportunities.

With Optima Indexing, we do the analysis and optimization for you. Snowflake Optima continuously analyzes your workloads, and when it identifies a recurring point-lookup query that can be accelerated, it proactively creates a search index. This index is built and maintained automatically, and you don’t pay any additional charges for compute or storage. Speed is simply an outcome of using Snowflake. No configuration knob, no extra cost; just effortless performance.

The Optima effect: A real-world example

After enabling Optima Indexing, we’ve seen dramatic performance improvements without users having to lift a finger. 

As an example, an automotive customer experienced a 15x acceleration for frequently recurring, highly selective queries on Gen2 warehouses thanks to Snowflake Optima. Before Optima Indexing, these queries averaged 17.36 seconds to complete, pruning only about 30% of the table’s micro-partitions during execution. After Optima Indexing kicked in, the same queries dropped to an average of 1.17 seconds, with Optima Indexing contributing to an additional 66% of micro-partition pruning, achieving a total pruning rate of 96%.

Here is the before and after chart of query execution times. The yellow dots show the duration (in seconds) of each query execution without the Optima index, and the blue dots show the durations with the index:

And here’s an example query profile after Optima Indexing kicked in:

Above, you can see that Optima Indexing helped prune an additional 8,343 of the 10,389 micro-partitions in the table scan (an 80% improvement). Combined with existing micro-partition metadata, this resulted in a final pruning rate of 96%, leading to an execution time of just 636 milliseconds.

Because these queries comprised a significant portion of the jobs running on the warehouse, Optima Indexing substantially accelerated average job run time on that warehouse, from an average of 2.63 seconds per job to 1.15 seconds — more than 2x faster. Notably, the average runtime improvement wasn’t solely due to the queries directly accelerated by Snowflake Optima. It was also due to the additional benefit of less warehouse resource contention, allowing other queries to complete faster. The queries that weren’t directly accelerated by Snowflake Optima also saw a 46% improvement in run time — almost a 2x speed-up — even though they continued to scan roughly the same amount of data.

This is just one of countless examples where Snowflake Optima has seamlessly accelerated customer workloads. Over the last month, Optima Indexing has automatically accelerated workloads by up to 15x for hundreds of customers using Gen2 warehouses, seamlessly pruning billions of micro-partitions for their point-lookup queries.

What’s next?

We encourage you to experience the power of Gen2 warehouses for recurring point-lookup queries, as well as for other data engineering and data transformation workloads.

Optima Indexing is just the beginning for Snowflake Optima. Later this year, we’ll also be launching Optima Metadata. With this feature, Snowflake Optima will enhance current micro-partition metadata — which enables fine-grained table pruning and query acceleration — by analyzing your query patterns and creating custom metadata tailored to your workloads. This means more efficient data retrieval and faster queries, automatically, with no action required.

Stay tuned for more developments in this space!