Snowflake Search Optimization Service (SOS) Complete Guide: How It Compares to Clustering Keys

Search Optimization Service (SOS) is a serverless feature that dramatically accelerates point lookup queries against large Snowflake tables. It builds an auxiliary data structure called the Search Access Path internally, narrowing down scan targets with a precision that partition pruning alone cannot reach. It is available on Enterprise Edition or higher.

How SOS Works

During normal query execution, Snowflake prunes by consulting micro-partition metadata (column min/max values). On top of that, SOS builds a Search Access Path that records the mapping between column values and micro-partitions. When you search for a specific value in a WHERE clause, the access path pinpoints exactly the matching micro-partitions, cutting scan volume by orders of magnitude.

Enabling SOS with SQL

The SQL syntax differs depending on whether you apply SOS to the entire table or limit it to specific columns and methods.

-- Enable SOS for the entire table
ALTER TABLE analytics.customers
  ADD SEARCH OPTIMIZATION;

-- Enable SOS on specific columns with a chosen method
ALTER TABLE analytics.customers
  ADD SEARCH OPTIMIZATION ON
    EQUALITY(customer_id),
    EQUALITY(email),
    SUBSTRING(customer_name),
    EQUALITY(metadata:plan_type);

-- Disable SOS on a specific column
ALTER TABLE analytics.customers
  DROP SEARCH OPTIMIZATION ON
    SUBSTRING(customer_name);

-- Disable SOS on the entire table
ALTER TABLE analytics.customers
  DROP SEARCH OPTIMIZATION;

-- Check SOS status
DESCRIBE SEARCH OPTIMIZATION ON analytics.customers;

Supported Query Patterns

The query patterns SOS can accelerate are limited. The table below also covers exam-tested points, so make sure you have it down precisely.

Choosing Between SOS and Clustering Keys

SOS and Clustering Keys are not competing features — they are complementary.

Cost Monitoring

SOS maintenance cost is proportional to DML frequency. Tables with high-frequency INSERTs see costs balloon, so periodic monitoring is essential.

-- Review SOS maintenance cost history
SELECT *
FROM TABLE(INFORMATION_SCHEMA.SEARCH_OPTIMIZATION_HISTORY(
  DATE_RANGE_START => DATEADD('DAY', -14, CURRENT_TIMESTAMP()),
  DATE_RANGE_END => CURRENT_TIMESTAMP()
));

-- Aggregate cost by table from ACCOUNT_USAGE
SELECT
  TABLE_NAME,
  SUM(CREDITS_USED) AS total_credits,
  COUNT(*) AS maintenance_runs
FROM SNOWFLAKE.ACCOUNT_USAGE.SEARCH_OPTIMIZATION_HISTORY
WHERE START_TIME >= DATEADD('DAY', -30, CURRENT_TIMESTAMP())
GROUP BY TABLE_NAME
ORDER BY total_credits DESC;

Design Patterns

Here are typical design patterns for adopting SOS in production.

• User search tables: Apply EQUALITY to customer_id and SUBSTRING to customer_name. Use Clustering Keys on date columns to optimize range searches.
• Event log tables: Apply EQUALITY to event_type and to specific JSON payload paths. Use event_timestamp as a Clustering Key for physical sorting.
• Geolocation tables: Apply GEO to GEOGRAPHY-type location columns to accelerate ST_DISTANCE / ST_WITHIN.

Exam Tips

• SOS accelerates equality / IN / substring / VARIANT / GEO search; range search is out of scope
• Requires Enterprise Edition or higher (not available on Standard Edition)
• Can be combined with Clustering Keys; they complement each other by covering different query patterns
• Specify columns and methods individually in the ON clause; avoid applying to columns you do not need
• Maintenance is billed as Serverless Credits and scales with DML frequency

Check with a Sample Question

Frequently Asked Questions