Snowflake Search Optimization Service

Some queries hunt for a tiny needle inside a huge haystack: one order number among billions of rows, one email address, one device ID. Standard micro-partition pruning struggles with these lookups. The Search Optimization Service builds extra search structures that find needles in seconds.

The Problem in Plain Words

Imagine a library with ten million books shelved by publication year. A visitor asking for books from 1999 walks straight to the right shelf. A visitor asking for the book with a specific barcode must check every shelf. Snowflake faces the same situation. Pruning works well for ranges on the sorted column and poorly for random lookups on other columns.

WITHOUT SEARCH OPTIMIZATION
===========================
Query: WHERE order_id = 'A84921'

[P1][P2][P3][P4][P5][P6][P7][P8] ... [P9000]
 ^   ^   ^   ^   ^   ^   ^   ^          ^
 Every partition may hold the value, so the warehouse scans thousands.

WITH SEARCH OPTIMIZATION
========================
Query: WHERE order_id = 'A84921'

  +--------------+
  | Search index |  order_id A84921 lives in partition P3077
  +--------------+
         |
         v
        [P3077]  only one partition is read

What the Service Does

Search Optimization Service, often shortened to SOS, maintains a persistent data structure that records which micro-partitions may contain each value. Queries consult that structure first and read only the matching partitions. Snowflake updates the structure automatically in the background as table data changes.

The service needs Enterprise Edition or higher.

Turning It On

-- Optimize the whole table for equality lookups
ALTER TABLE orders ADD SEARCH OPTIMIZATION;

-- Target specific columns and search styles
ALTER TABLE orders ADD SEARCH OPTIMIZATION
  ON EQUALITY(order_id, customer_email);

-- Support LIKE and text searches inside strings
ALTER TABLE orders ADD SEARCH OPTIMIZATION
  ON SUBSTRING(product_name);

-- Support geospatial lookups
ALTER TABLE stores ADD SEARCH OPTIMIZATION
  ON GEO(location);

-- Turn it off
ALTER TABLE orders DROP SEARCH OPTIMIZATION;

-- Check status and build progress
SHOW TABLES LIKE 'ORDERS';
DESCRIBE SEARCH OPTIMIZATION ON orders;

The first build scans the entire table, so large tables need time before the benefit appears. Snowsight and the SHOW commands report build progress.

Query Types That Benefit

QUERY PATTERN                          BENEFIT
-------------------------------------  ----------------------------
WHERE order_id = 'A84921'              Excellent
WHERE email IN ('a@x.com','b@x.com')   Excellent
WHERE product_name LIKE '%wireless%'   Good (needs SUBSTRING)
WHERE VARIANT_COL:user.id = 42         Good
WHERE amount > 10                      Little or none
SELECT SUM(amount) FROM orders         None (reads everything)

Search Optimization Versus Clustering Keys

FEATURE            CLUSTERING KEY                 SEARCH OPTIMIZATION
-----------------  -----------------------------  ---------------------------
Best for           Range filters on date, region  Point lookups on many columns
Columns covered    A few chosen columns           Any column, many at once
Data layout        Re-sorts the stored data       Adds a separate search structure
Typical table      Large analytic fact tables     Lookup, audit, and log tables

Both features work together on one table. Clustering sorts the data for range scans, and search optimization handles random lookups.

Cost Considerations

The service bills in two ways. Serverless compute builds and refreshes the structure, and extra storage holds it. Tables with heavy update traffic raise the refresh cost. Estimate the price before enabling it.

SELECT SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS('ORDERS');

Track actual spending through the SEARCH_OPTIMIZATION_HISTORY view in the Account Usage schema.

Good Candidates

• Tables with hundreds of millions of rows or more.
• Queries that return a few rows and currently take many seconds.
• Columns with many distinct values, such as IDs, emails, and tokens.
• Tables that change moderately rather than constantly.

A Practical Scenario

A support team looks up customer tickets by ticket number inside a table that holds three billion rows. Each lookup scans thousands of partitions and takes twenty seconds. A data engineer enables search optimization on the ticket number column. The first build runs in the background for several hours. Afterward, each lookup returns in under a second because Snowflake reads only the partition that holds the ticket. Support agents notice the change right away, and the team's dashboards load faster too.

Frequently Asked Questions

Does the service change my query text?

Queries stay exactly as written. Snowflake decides at run time whether the search structure helps and uses it automatically.

Can I enable it for a few columns only?

Column-level settings reduce build cost and storage. Target only the columns that appear in selective filters.

Key Points

• Search Optimization Service speeds up selective lookups on large tables.
• A background search structure points queries to the exact micro-partitions.
• Enable it per table or per column with ALTER TABLE.
• It fits point lookups; broad scans and large aggregations gain nothing.
• Costs include serverless build compute and extra storage.
• Estimate costs before enabling the service on busy tables.