Search Optimisation Service

The Search Optimisation Service (SOS) is a Snowflake Enterprise-tier feature designed to dramatically accelerate point lookup queries on large tables. Unlike clustering keys — which help range-based scans — SOS is purpose-built for queries that search for specific values in large datasets, such as finding a customer by ID or filtering on a phone number.

Understanding the distinction between SOS, clustering keys, and the result cache is a frequent topic on the SnowPro Core COF-C02 exam.

Why SOS Exists: The Problem It Solves

Standard Snowflake query performance relies on micro-partition pruning — Snowflake reads the min/max metadata for each micro-partition and skips those that cannot contain the desired values. This works exceptionally well for range queries and sorted data, but breaks down for random point lookups.

Consider a table of 500 million customer records. If you query WHERE customer_id = 'C-00019284', Snowflake must scan potentially thousands of micro-partitions because customer IDs are randomly distributed — no pruning is possible without a sort order on that column.

The Search Optimisation Service solves this by building a persistent search access path — effectively a specialised index structure — that allows Snowflake to identify precisely which micro-partitions contain the target value, skipping the rest entirely.

How SOS Works Internally

Search Optimisation Service Architecture

Files arrive in a stage. Snowflake builds a persistent Search Access Path alongside table micro-partitions. When a point lookup query runs, SOS identifies qualifying micro-partitions, short-circuiting the standard full-partition scan. The warehouse then reads only the identified partitions.

The process works as follows:

1. Build Phase — When SOS is enabled on a table, Snowflake’s serverless background compute builds the initial search access path. This is a one-time build cost charged in serverless credits.
2. Maintenance Phase — As DML operations (INSERT, UPDATE, DELETE, MERGE) change the table, Snowflake continuously and automatically updates the access path. Each update consumes additional serverless credits.
3. Query Phase — When a qualifying query arrives (e.g. a WHERE id = ? predicate on an SOS-configured column), the query optimiser consults the access path to determine which micro-partitions are relevant. Only those partitions are read by the virtual warehouse.

Supported Predicate Types

SOS does not help every query type. The exam tests whether you know exactly which predicates benefit.

SOS Supported vs Unsupported Predicates

SOS accelerates: equality predicates (=), IN lists, ILIKE/LIKE substring search, and geospatial predicates (ST_WITHIN, ST_INTERSECTS). SOS does NOT help: range predicates alone (>, <, BETWEEN), full table scans, JOIN performance, aggregations, or queries already pruning well via clustering keys.

What SOS Accelerates

What SOS Does NOT Help

• Range predicates alone: WHERE created_at > '2024-01-01' — clustering keys are better here
• Full table scans with no selective predicate
• JOIN performance — SOS does not accelerate join operations
• Aggregate queries: COUNT(*), SUM(), AVG() without selective filters
• Queries that already prune well via clustering keys (SOS adds cost without benefit)

Enabling Search Optimisation Service

SOS requires Snowflake Enterprise Edition or higher. It cannot be used on Standard Edition accounts.

Method 1: Enable on All Columns (Full Table)

1-- Enable Search Optimisation Service on all columns
2-- Snowflake determines which column types are supported
3ALTER TABLE customer_events
4ADD SEARCH OPTIMIZATION;
5
6-- Verify configuration
7DESCRIBE SEARCH OPTIMIZATION ON TABLE customer_events;

Method 2: Enable on Specific Columns and Predicate Types

1-- Enable equality search on specific columns only
2ALTER TABLE customer_events
3ADD SEARCH OPTIMIZATION ON
4  EQUALITY(customer_id, session_id, email)
5  SUBSTRING(full_name, description)
6  GEO(location_point);
7
8-- Remove SOS from specific columns without disabling entirely
9ALTER TABLE customer_events
10DROP SEARCH OPTIMIZATION ON
11  EQUALITY(session_id);
12
13-- Remove SOS entirely from the table
14ALTER TABLE customer_events
15DROP SEARCH OPTIMIZATION;

Monitoring SOS

1-- Check SOS configuration and optimised columns
2DESCRIBE SEARCH OPTIMIZATION ON TABLE customer_events;
3
4-- View SOS build and maintenance history (serverless credit usage)
5SELECT *
6FROM TABLE(
7INFORMATION_SCHEMA.SEARCH_OPTIMIZATION_HISTORY(
8  DATE_RANGE_START => DATEADD('day', -7, CURRENT_DATE()),
9  DATE_RANGE_END   => CURRENT_DATE()
10)
11)
12ORDER BY start_time DESC;
13
14-- Confirm SOS is active on a table via SHOW TABLES
15SHOW TABLES LIKE 'customer_events';
16-- Look for the search_optimization_progress column (100 = fully built)

Step-by-Step: Implementing SOS

SELECT SYSTEM$BOOTSTRAP_DATA_REQUEST('ACCOUNT');

EXPLAIN SELECT * FROM orders WHERE order_uuid = '550e8400-e29b-41d4-a716-446655440000';

ALTER TABLE orders
  ADD SEARCH OPTIMIZATION ON
    EQUALITY(order_uuid, customer_id)
    SUBSTRING(customer_name);

SHOW TABLES LIKE 'orders';
-- Check search_optimization_progress column (0-100)

SELECT SUM(credits_used) AS total_sos_credits
FROM TABLE(
  INFORMATION_SCHEMA.SEARCH_OPTIMIZATION_HISTORY(
    DATE_RANGE_START => DATEADD('month', -1, CURRENT_DATE()),
    DATE_RANGE_END   => CURRENT_DATE()
  )
);

SOS vs Clustering Keys vs Result Cache

These three features are frequently compared on the exam. They are not mutually exclusive — tables can have both SOS and clustering keys simultaneously.

Cost Breakdown

CheatSheet: Search Optimisation Service

Flashcards: Search Optimisation Service

Knowledge Check: Quiz

Summary

The Search Optimisation Service fills a specific gap in Snowflake’s performance toolkit. When your workload includes frequent point lookups on high-cardinality, randomly distributed columns — and clustering keys cannot help — SOS delivers dramatic performance improvements by short-circuiting partition scanning.

The key exam takeaways are:

• Edition: Enterprise Edition or higher required
• Use case: Point lookups — equality, IN lists, ILIKE, geospatial
• Not for: Range predicates, JOINs, aggregations, or full table scans
• Costs: Build cost (one-time) + maintenance cost (ongoing, per DML) + storage
• Monitoring: DESCRIBE SEARCH OPTIMIZATION ON TABLE t and SEARCH_OPTIMIZATION_HISTORY
• SOS vs Clustering: Complementary, not competitive — can use both simultaneously
• SOS vs Result Cache: Different problems — varied values vs identical queries