The Snowflake Search Optimization Service (SOS) is a feature that improves the performance of point-lookup and analytical queries, particularly those involving selective filtering on large tables.

This article is divided into two parts. Part 1 will cover the basics of the Snowflake search optimization service, including its implementation and a hands-on example. Part 2 of the article will explore the advantages of using the search optimization service, the types of queries that can benefit from it, the associated costs, strategies for cost management, and factors affecting the cost.

So, get ready to learn more about this powerful feature, and let's dive in!

What is Snowflake Search Optimization Service (SOS)?

Search Optimization Service (SOS) is a feature of the Snowflake cloud data platform that utilizes a background maintenance service to create an optimized data structure called the search access path. This path scans all of the table’s micro partitions and records metadata, which is subsequently used by Snowflake to construct the most effective search access path for the table’s data.  When queries which perform point-lookups - many filters that retrieve a small subset of data - are run on a table with Search Optimization, the Snowflake optimizer analyzes the search access path to determine the shortest and most effective access path.

Note: Snowflake search optimization service is only available in the Enterprise edition or higher.

The Search Optimization Service (SOS) is applicable to a wide range of queries, including selective point lookup queries on tables. These queries typically return only one or a small number of distinct rows and are commonly used by business users who need fast response times for critical dashboards with highly selective filters, data scientists exploring large data volumes and looking for specific subsets of data, and data applications retrieving a small set of results based on an extensive set of filtering predicates.

Also, the SOS feature is quite useful for substring and regular expression searches (e.g. LIKE, ILIKE, RLIKE, etc.), queries on fields in  VARIANT, OBJECT and ARRAY columns that use certain types of predicates (equality predicates, IN predicates, predicates that use ARRAY_CONTAINS and ARRAYS_OVERLAP, and predicates that check for NULL values), and queries that use selected geospatial functions with GEOGRAPHY values.

Once you identify the queries that can benefit from the search optimization service, you can configure search optimization for the columns and tables used in those queries, which can lead to significant improvements in query performance.

Check out this official Snowflake documentation to find out more about it.

How Snowflake's Search Optimization Service Boosts Query Performance?

Snowflake search optimization service improves query performance by creating a set of indexes on the columns involved in the query. These indexes serve as a map that helps Snowflake quickly locate the micro-partitions containing the necessary data, reducing the amount of time and computing resources required to execute the query.

In addition to creating indexes, the Snowflake search optimization service applies filters to prune any unnecessary micro-partitions from the query. By doing this, it further reduces the amount of data that needs to be scanned, which helps to accelerate query performance.

This service is especially helpful when working with large tables or tables with a large number of micro-partitions, as it helps to ensure that only the required data is scanned, reducing the computational overhead of executing the query.

How to Enable Snowflake Search Optimization for a Table?

To enable Snowflake search optimization for a table, you need to follow these steps:

• Make sure you have the necessary privileges to enable Snowflake search optimization.
• Run the ALTER TABLE command to enable the Snowflake search optimization for the table.

The ALTER TABLE command to enable Snowflake search optimization service for a table is as follows:

ALTER TABLE some_table_name ADD SEARCH OPTIMIZATION

To demonstrate the default Snowflake search optimization service option for tables, we can create a new table called "my_table" that contains all the data from the "CUSTOMER" table in the "TPCH_SF100" database of the "SNOWFLAKE_SAMPLE_DATA" schema and enable Snowflake search optimization service for the table using the default settings.

Here is an example:

CREATE TABLE my_table as SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.CUSTOMER;

We can then enable Snowflake search optimization service for the table using the following command:

ALTER TABLE my_table ADD search OPTIMIZATION

Note: search optimization is done by the Snowflake background process. So it might take a ~ few minutes/hrs for the process to complete.

Also, note that you will see a sudden surge in Snowflake credit consumption while the search optimization runs and starts to build the search access path.

To estimate the costs for specific tables before committing, you can use the SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS function with the <table_name> as a parameter.

select SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS('<table_name>')

If you need to disable the service, you can easily do so with the correct privileges by running the ALTER TABLE statement with the DROP search OPTIMIZATION clause and the table name.

ALTER TABLE my_table DROP SEARCH OPTIMIZATION;

How do you check the list of columns for which Snowflake search optimization service is enabled?

To check the list of columns for which Snowflake search optimization service is enabled, you can use the following DESCRIBE command:

DESCRIBE SEARCH OPTIMIZATION ON my_table;

What Kinds of privileges are required to enable Snowflake search optimization service?

To enable the Snowflake search optimization service requires specific privileges. Users must have either OWNERSHIP or the ADD SEARCH OPTIMIZATION privilege on the schema containing the table they want to enable Snowflake search optimization service.

OWNERSHIP

Users with ownership of a schema can enable Snowflake search optimization service for any table within that schema.

To grant ownership of a schema, use the following command:

GRANT OWNERSHIP ON SCHEMA <schema_name> TO <user_or_role>;

ADD SEARCH OPTIMIZATION privilege on the schema

Users without ownership of a schema must have the ADD SEARCH OPTIMIZATION privilege on the schema containing the table they want to enable Snowflake search optimization service for.

To grant the ADD SEARCH OPTIMIZATION privilege, use the following command:

GRANT ADD SEARCH OPTIMIZATION ON SCHEMA <schema_name> TO <user_or_role>;

But, to use Snowflake search optimization for a query, you only need the SELECT privilege on the table. You don’t need additional privileges since search OPTIMIZATION is a table property that is automatically detected and used when querying a table.

Example: Performance Comparison of Snowflake Search Optimized and Non-Optimized Tables

The example below shows Snowflake search optimization service in action.

Let’s create a table with 100 million customer records from SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CUSTOMER

Table with 100M customer record from Snowflake sample data

create table CUSTOMER as select * from  SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CUSTOMER

Step 1: Clone the table

create table customer_SOS clone CUSTOMER;

Step 2: Now, enable Snowflake search optimization service on that cloned table

ALTER TABLE customer_SOS ADD search OPTIMIZATION;

Step 4: Confirm that search Optimization is complete by examining the following fields in the output.

show tables like '%customer_SOS%';

Step 5: let's run a point-lookup query on a table WITHOUT optimization enabled.

SELECT *
FROM CUSTOMER
WHERE C_CUSTOMER_SK = '4722123';

As you can see below, the query profile below shows that it had to scan all the micro partitions to get the required result in 1.3 seconds.

Step 6: Finally, let's run a point-lookup query on a table WITH optimization enabled.

SELECT *
FROM customer_SOS
WHERE C_CUSTOMER_SK = '4722123';

As you can see below, the query profile is running much faster and using search optimization effectively and returning the results much faster.

Note: If you didn't see any improvement, it might be due to cached results. To reset the cache, run the following commands:

🚨 Caution: Use at your own risk! 🚨

ALTER SESSION SET USE_CACHED_RESULT = FALSE;
ALTER WAREHOUSE COMPUTE_WH SUSPEND;
ALTER WAREHOUSE COMPUTE_WH RESUME;

Conclusion

Snowflake's search optimization service is a powerful feature that can significantly enhance the performance of complex queries. Activating this service for a table is a straightforward process, but it should only be used for specific use cases with caution. While search optimization can definitely improve query performance, one needs to look deeper into identifying which tables, columns and queries to use the service for without blowing up your Snowflake costs.

To summarize Part 1 of this article, we covered the basics of Snowflake's search optimization service, including its definition, activation process, required privileges, and a performance comparison between optimized and non-optimized tables. In Part 2 of the article, we will delve deeper into the advantages of search optimization, the types of queries that can benefit from it, cost factors, strategies to manage expenses, and more.

FAQs

What queries can benefit from Snowflake Search Optimization Service?

Snowflake Search Optimization Service (SOS) can benefit the following types of queries:

• Selective point lookup queries
• Substring and regular expression searches
• Queries on fields in VARIANT, OBJECT, and ARRAY columns that use certain types of predicates
• Queries that use selected geospatial functions with GEOGRAPHY values

How do you check the list of columns with enabled Search Optimization Service?

Use the "DESCRIBE SEARCH OPTIMIZATION ON my_table" command on the table to check the list of columns for which Snowflake Search Optimization Service is enabled.

How can you estimate the search optimization costs for a table?

Use the "SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS" function with the table name as a parameter to estimate the search optimization costs

How can you disable Snowflake Search Optimization for a table?

With the appropriate privileges, use the ALTER TABLE statement with the "DROP SEARCH OPTIMIZATION" clause and the table name to disable the service.

Can I enable search optimization on a table?

Yes, To enable search optimization on a table, contact Snowflake Support. It is not enabled by default.

Does search optimization require automatic clustering to be enabled?

Yes, search optimization requires automatic clustering to be enabled. Contact Snowflake Support to enable automatic clustering services if needed.