Introduction:

 

In today’s article, we’ll explore together Snowflake’s Search Optimization Service, a feature that can improve the performance of point lookup for certain queries by creating search access paths. The service is available on the Enterprise Edition or higher for Snowflake and is best for business users who rely on quick access to data for critical business decisions.

The article also covers how to turn on the service, its benefits, and its cost. We also introduce Snoptimizer™, our service that scans for all the Snowflake anti-patterns and optimizes your account to help you run cost-effectively.

 

History of Snowflake & Search Optimization:

 

Snowflake initially made a name for itself as the easiest data warehouse to use back in 2014. Since then it has transformed itself and its core technology into a full Snowflake Data Cloud. While the Snowflake Data Cloud Account at first comes with many amazing features by default, there are many areas where you can optimize Snowflake for your specific needs and use cases. As Snowflake has grown over the years, it has added a ton of functionality including paid services such as SnowPipe, Materialized Views, Auto Clustering, Search Optimization Service, and others.

Alternatively, it can be useful for data scientists who want to continuously explore specific subsets of data. Essentially it is a maintenance service that runs in the background of Snowflake and creates search access paths. These paths make it easier to load and populate data quickly, as well as update stale data.

 

Turning on the Feature:

 

To turn on such a feature, you must first ensure you are using an account that has access to add it to a table. Having access means you have the following privileges: ownership & add search optimization. Once that requirement is met, it’s as simple as typing the following into your console:

 

ALTER TABLE [IF EXISTS] <table_name> ADD SEARCH OPTIMIZATION;

To ensure it is turned on, show your tables and check to see that ‘Search Optimization’ says ON. A few notes to add is that you will see an increase in credit consumption while the service runs and starts to build the search access paths. You can get an estimate of the cost for specific tables before committing by running the following command:

 

SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS(‘<table_name>’)

Being strategic with the tables you introduce to the search optimization service will help greatly with reducing those costs. The service fits best for tables that aren’t queried by columns and tables that aren’t clustered.

If you add the service and decide to move it later on, you can easily do so with the correct privileges by running the following command:

 

ALTER TABLE [IF EXISTS] <table_name> DROP SEARCH OPTIMIZATION;

This is just one solution to make your life easier and queries faster, however, there are many more out there that are more cost-friendly and do not require you to look thoroughly through your tables. One of the prime examples is Snoptimizer™, our service that scans for all the Snowflake anti-patterns and optimizes your account to help you run cost-effectively. It checks your resource monitors, auto-suspend settings, cloud service consumption, and warehouse computing among other things to fix your account and ensure you are fully optimized.

 

Conclusion:

 

Snowflake’s Search Optimization Service is a powerful feature that can significantly improve the speed and efficiency of certain queries. While it comes with a cost, it can be a valuable investment for business users who rely on quick access to data for critical decision-making. However, it’s important to be strategic about which tables you introduce to the service to minimize costs. Additionally, there are alternative solutions available, such as Snoptimizer™, that can optimize your account and help you run cost-effectively. With the right approach, Snowflake’s Search Optimization Service can be a powerful tool in your data optimization arsenal.