Skip to main content

Search Optimization Service (SOS)

πŸ“Œ What is SOS?​

Snowflake’s Search Optimization Service is an advanced indexing-like feature that accelerates point lookups and selective filter queries (e.g., WHERE employee_id = 12345) in large or semi-structured datasets by building a search access path outside normal table storage.

It’s automated β€” Snowflake maintains and updates these paths in the background without manual index rebuilds.


πŸš€ Example Use Cases​

  • Fast search on large JSON columns
  • Accelerating highly selective WHERE filters
  • Reducing scan costs in frequently queried large datasets (e.g., HR)

βš™οΈ How SOS Works​

  • Optimization: Reduces full table scans by storing metadata to narrow search scope.
  • Automation: Continuously updated as data changes.
  • Impact: Targets only relevant micro-partitions instead of scanning all of them.

🏷️ SOS vs. Traditional Indexing​

FeatureSOSTraditional Indexing
Storage locationSeparate from table data, transparentInside the table
MaintenanceAutomaticManual rebuild needed
ScopePoint lookups/filtersJoins, filters, etc.
Best forLarge/semi-structured datasets, JSONRelational structures

βš™οΈ How SOS Works (Behind the Scenes)​

  1. Data in Snowflake is stored in micro-partitions (MPs) β€” typically 50–500 MB compressed.

  2. SOS builds search metadata that maps column values β†’ micro-partitions.

  3. At query time:

    • Without SOS: Snowflake checks all partitions.
    • With SOS: Snowflake checks only partitions that might contain the value.

βœ… Step-by-Step Example with Numbers​

Table: SALES_TRANSACTIONS (10 rows)​

TRANSACTION_IDCUSTOMER_IDREGIONAMOUNT
1101East500
2102West300
3103East700
4104South400
5105East600
6106North200
7107East800
8108West350
9109South450
10110East900

πŸ” Query: SELECT * FROM SALES_TRANSACTIONS WHERE REGION = 'East';​


Case 1: Without SOS (Full Scan)​

  • How it works:

    • Snowflake scans all 10 rows.
    • Compares REGION for every row.
  • Work done:

    Check Row 1 β†’ East βœ…  
    Check Row 2 β†’ West ❌
    …
    Check Row 10 β†’ East βœ…
  • βœ… Rows scanned: 10
  • βœ… Rows returned: 5

Case 2: With SOS Enabled on REGION​

SOS builds metadata like this:

REGION = 'East'  β†’ MP1, MP3, MP5, MP7, MP10
REGION = 'West' β†’ MP2, MP8
REGION = 'South' β†’ MP4, MP9
REGION = 'North' β†’ MP6

So, for REGION = 'East':

  • It jumps directly to MP1, MP3, MP5, MP7, MP10.
  • Reads only those micro-partitions (not all 10).
  • βœ… Rows scanned: 5
  • βœ… Rows returned: 5

In Numbers: Big Picture​

ScenarioRows ScannedPartitions ScannedCost
Full Scan10AllHigh
With SOS5Relevant MPs onlyLower

Imagine this on 1 billion rows:

  • Full scan β†’ 1,000,000,000 checks
  • With SOS β†’ maybe 50 million checks (or fewer)
  • Credit savings = huge

βœ… Does SOS create MP1…MP10 per value?​

No. Micro-partitions already exist based on data load. SOS maps values β†’ existing micro-partitions like an inverted index. It doesn’t create new MPs.


πŸ”₯ Key Formula in Background​

  • Each micro-partition stores min/max for columns.
  • SOS adds value β†’ MP mapping so Snowflake can skip irrelevant MPs.
  • For equality filter (=), it pinpoints only MPs that could contain that value.

⚑ Key Takeaways​

βœ” Best for point lookups & highly selective filters βœ” Minimal setup β†’ big gains


πŸ”Ž How to Enable SOS​

ALTER TABLE my_table ADD SEARCH OPTIMIZATION;
-- Or target specific columns
ALTER TABLE my_table ADD SEARCH OPTIMIZATION ON (employee_id, email);

🎯 Demo: Measuring SOS Impact​

1. Create & Populate a Large Table​

-- Example HR dataset
CREATE OR REPLACE TABLE EMPLOYEES (
employee_id BIGINT,
first_name VARCHAR,
last_name VARCHAR,
department VARCHAR,
hire_date DATE,
email VARCHAR,
salary NUMBER(10,2)
);
-- Insert millions of rows...

2. Query Without SOS​

SELECT * FROM EMPLOYEES WHERE employee_id = 123456;

Likely scans many micro-partitions β†’ slower.


3. Enable SOS​

ALTER TABLE EMPLOYEES ADD SEARCH OPTIMIZATION;
ALTER TABLE EMPLOYEES ADD SEARCH OPTIMIZATION ON EQUALITY(employee_id);

4. Query Again​

SELECT * FROM EMPLOYEES WHERE employee_id = 123456;

Should be faster with fewer partitions scanned.


πŸ“Š Measuring Cost & Performance​

WITH query_costs AS (
SELECT
query_id,
warehouse_size,
total_elapsed_time/1000 AS elapsed_seconds,
bytes_scanned/1024/1024 AS mb_scanned,
CASE UPPER(warehouse_size)
WHEN 'X-SMALL' THEN 1
WHEN 'SMALL' THEN 2
WHEN 'MEDIUM' THEN 4
WHEN 'LARGE' THEN 8
WHEN 'X-LARGE' THEN 16
WHEN '2X-LARGE' THEN 32
END AS credit_rate_per_hour
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
WHERE query_text ILIKE '%employee_id%'
AND start_time > DATEADD(hour, -1, CURRENT_TIMESTAMP)
)
SELECT
query_id,
elapsed_seconds,
mb_scanned,
ROUND((elapsed_seconds / 3600) * credit_rate_per_hour, 8) AS credits_used,
ROUND((elapsed_seconds / 3600) * credit_rate_per_hour * 3, 8) AS cost_usd
FROM query_costs;

Formulas:

Credits used = (Elapsed seconds Γ· 3600) Γ— Warehouse Credit Rate
Cost (USD) = Credits used Γ— Credit price

πŸ“ˆ Estimating SOS Overhead​

SELECT SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS('EMPLOYEES');

Example output & interpretation:

Cost TypeMeaningExample ValueNotes
BuildCostsOne-time compute credits to initially create SOS paths0.001493 CreditsUsually tiny
StorageCostsExtra storage/month for SOS metadata0.000034 TB (~34 MB)Recurring
MaintenanceCostsOngoing compute to keep SOS paths updatedNot available until 7 days historyIncreases with frequent updates
BenefitNot auto-calculated β€” measure via Query History before & after SOSβ€”If SOS drastically reduces scanned MB β†’ credits saved

πŸ’‘ Where SOS Gives Real Benefit​

  • Point Lookups: Queries fetching 1 or very few rows in huge datasets.
  • Highly Selective Filters: WHERE clauses that match < 1% of rows.
  • Large JSON & Semi-Structured Data: Especially when searching nested keys.
  • High-Value Columns: Keys used in frequent lookups by downstream systems.
  • Cold Data Search: Historical tables where only small date ranges are queried.

Example: Without SOS: Query scans 1 TB of data β†’ costs ~0.25 credits. With SOS: Query scans 2 MB of data β†’ cost drops to nearly zero, paying back the tiny build/storage overhead very quickly.


⚑ Key Takeaways​

  • Best for point lookups and highly selective filters in large datasets.
  • Minimal setup β†’ big performance gains.
  • Always measure before/after using Query History.
  • SOS has tiny build/storage costs vs. potential credit savings.
  • Benefit is highest when full table scans drop to micro-partition scans.