Snowflake’s Search Optimization Service (SOS) is a powerful platform-native feature designed to dramatically enhance performance for analytical and lookup queries.
If you’re an enterprise overseeing a large dataset, SOS is a game-changer. It can make a world of difference when it comes to reducing query times and optimization costs.
In this guide, I’ll walk you through:
- What SOS is exactly
- Benefits you can expect from using it
- How it works for queries, tables, and columns
- How you can implement it
- Common pitfalls to watch out for.
Note: SOS is an Enterprise Edition (or higher) feature, so double check your plan before continuing.
TLDR: Snowflake’s Search Optimization Service (SOS) can cut query time up to 95% – but only if done correctly. This guide will show you how to make sure you can cut down to the last penny while preserving performance with SOS.
What is Search Optimization Service in Snowflake
Search Optimization Service is a specialized Snowflake service that uses a ground maintenance service to create the search access path, an optimized data structure.
The search access path will scan your table’s micro-partitions and record any metadata used by Snowflake to inform the most effective search access path for that table’s data. This maintenance service works in the background, so it won’t block any other operations. This enables your queries to run faster and more efficiently – saving you money.
Key benefits of Snowflake Search Optimization
The best use cases for Snowflake SOS include:
- Business owners working with critical dashboards and selective filters who want fast response times
- Data analysts looking to retrieve a small set of results based on extensive filtering predicates
- Data scientists exploring large data volumes who need specific data subsets
I find that companies usually most enjoy the following Snowflake SOS benefits:
- Reduced query times by up to 95%.
- Cost optimization and significant savings
- Improve versatility as SOS can be applied to unique object UUIDs, specific IP addresses, geospatial data searches, text substrings, and more
SOS is especially helpful if you’re working with a large table, or even with a table that has a lot of micro-partitions because your query will only have to scan required data, which helps massively reduce computational overhead.
How does Search Optimization Service work
SOS creates a secondary data structure known as a Search Access Path. SOS will then track the values that appear in micro-partitions, making querying much faster by massively optimizing the scanning process. This creates a set of indexes on the column the query uses, and those indexes act as a map, reducing query time and reducing compute resources.
SOS will also apply filters to remove unnecessary micro-partitions. This means your query will scan even less data, improving performance further.
But Snowflake Search optimization wasn’t made for all queries. The following are all queries on tables that I highly recommend applying SOS to:
- Selective point lookup queries on tables
- Substring expression searches like RLIKE, LIKE, REGEXP, REGEXP_LIKE
- Fields in variant columns
- Regular expression searches
- Geospatial functions with GEOGRAPHY objects
- Queries on fields in VARIANT, ARRAY, and OBJECT
The key is figuring out which query would most benefit from SOS so you can configure optimizations for the tables and columns the query uses.
Implementing search optimization in Snowflake
If your table and its columns match any of the following scenarios, you’ll have a good contender for using SOS:
- Query table data volume is at least 200 GBs
- Query filter operation column has at least 100k-200k distinct values
- Query returns a couple of rows with highly selective filters
- Query usually takes a few seconds or longer to run
- Table is frequently queried on columns other than cluster key columns
Identifying optimal tables for SOS
Implementing SOS is more a matter of figuring out which table is best suited for optimization.
- Evaluate if a table can be used based on query patterns and data structure. Make sure you have the necessary privileges.
Pro tip: You can use this query string to retrieve information about a table. This will tell you if the table is a good candidate for search optimization or not.
- Estimate build and maintenance costs using Snowflake tools to see if it’s worth using SOS.
- Run the ALTER TABLE command to enable Snowflake search optimization for the table you’ve chosen.
Pro tip: Use this code to run the ALTER TABLE command
ALTER TABLE some_table_name ADD SEARCH OPTIMIZATION
- Build Search Access Paths for your selected columns and tables
- Write your new queries that use the new Search Access Paths.
- Verify your setup to check Snowflake SOS has been enabled correctly.
Pro tip: Use this DESCRIBE command to view your SOS setup:
DESCRIBE SEARCH OPTIMIZATION ON my_table;
Common SOS challenges and solutions
I’ve seen a fair share of incorrectly implemented Search Optimization Services in Snowflake in my experience. Here are the most common problems you should watch out for:
- Not fully optimizing your table before measuring results which leaves you with inaccurate measurements
- Improper privilege. Snowflake SOS requires either OWNERSHIP or the ADD SEARCH OPTIMIZATION privilege for schema with the table you want to optimize.
- Managing storage and compute costs associated with the maintenance of the Search Access Path.
Pro tip: If you don’t have OWNERSHIP, you can either have a user who does grant you ownership of a schema using this command:
GRANT OWNERSHIP ON SCHEMA <schema_name> TO <user_or_role>;
Or you can ADD SEARCH OPTIMIZATION privilege on the schema with the table you want to optimize. If you decide to use this approach, this is the best code for you:
GRANT ADD SEARCH OPTIMIZATION ON SCHEMA <schema_name> TO <user_or_role>;
Just make sure you have SELECT privilege on the table.
Managing Snowflake search optimization costs
Using Snowflake SOS means adding on additional compute and storage resource costs to your overall Snowflake bill. You’ll pay for the amount of compute and storage resources used. Your total bill is based on the number and size of columns used during SOS.
The main factors that increase this bill are:
- Number of rows and columns in a table (table width and length).
- Number of distinct values in each column (this increases storage).
- Number of inserts, updates, and deletes on the table (this increases compute costs because of search access path maintenance).
Cost optimization strategies
Don’t let yourself get caught up in the SOS offering and get excited by how much you can save without calculating ROI first. There are a few ways to keep your SOS from climbing too high while still reaping the benefits:
- Only use SOS for tables that need it. This may seem obvious, but not all tables benefit from SOS.
- Batch INSERT, UPDATE, DELETE, and MERGE operations where possible to reduce the number of compute-intensive transactions.
- Make sure to cluster your table. If your table isn’t clustered, drop Snowflake SOS and add your table and columns back after you’ve reclustered the table.
- Monitor and adjust your usage of SOS using either Snowflake’s Account Usage tools or third party tools.
How to level up your optimization game beyond Slowflake native tools
While Snowflake Search Optimization service can mean substantial performance benefits, implementing it correctly across your entire Snowflake environment means endless analysis and maintenance.
That’s why many teams are turning to automation tools like Yuki to simplify implementation and unlock deeper savings.
Yuki Data provides a plug-and-play solution that:
- Automatically identifies the perfect tables and columns for Search Optimization
- Continuously monitors SOS costs and performance
- Delivers actionable real-time routing optimizations – no developer needed
- Gets you 30-50% greater cost savings compared to manual operations
Yuki’s automated approach means you can implement Snowflake Search optimization best practices across your entire data warehouse with a tenth of the effort. Maximizing performance and maintaining reasonable costs are done for you.
Want to see how much you could be saving with SOS done right? Get your free optimization report from Yuki.
Snowflake SOS FAQ
How do you check to see what columns enabled Search Optimization Service?
Use the “DESCRIBE SEARCH OPTIMIZATION ON my_table” command. You’ll be able to check the list of columns with SOS enabled.
How can you disable SOS for a table?
Make sure you have the appropriate privileges (either OWNER or ADD SEARCH OPTIMIZATION for that specific table). Use the ALTER TABLE statement and add a “DROP SEARCH OPTIMIZATION” clause along with the table name to disable the service.
Do I have to use automatic clustering for search optimization?
Yes, you’ll need automatic clustering if you want to use SOS. Contact Snowflake Support if you need additional support.
