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.
Today, let’s cover their Search Optimization Service. This service can help the performance of point lookup for certain queries but remember it is available on the Enterprise Edition or higher for Snowflake (so Standard Edition users, you are out of luck if you wanted to use this service – you will need to upgrade your Account Edition.) This service is best for business users who rely on quick access to data to make critical business decisions. 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.
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. If you are interested in getting a trial, you can sign up and explore more here.
