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
WHEREfilters - 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β
| Feature | SOS | Traditional Indexing |
|---|---|---|
| Storage location | Separate from table data, transparent | Inside the table |
| Maintenance | Automatic | Manual rebuild needed |
| Scope | Point lookups/filters | Joins, filters, etc. |
| Best for | Large/semi-structured datasets, JSON | Relational structures |
βοΈ How SOS Works (Behind the Scenes)β
-
Data in Snowflake is stored in micro-partitions (MPs) β typically 50β500 MB compressed.
-
SOS builds search metadata that maps column values β micro-partitions.
-
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_ID | CUSTOMER_ID | REGION | AMOUNT |
|---|---|---|---|
| 1 | 101 | East | 500 |
| 2 | 102 | West | 300 |
| 3 | 103 | East | 700 |
| 4 | 104 | South | 400 |
| 5 | 105 | East | 600 |
| 6 | 106 | North | 200 |
| 7 | 107 | East | 800 |
| 8 | 108 | West | 350 |
| 9 | 109 | South | 450 |
| 10 | 110 | East | 900 |
π Query: SELECT * FROM SALES_TRANSACTIONS WHERE REGION = 'East';β
Case 1: Without SOS (Full Scan)β
-
How it works:
- Snowflake scans all 10 rows.
- Compares
REGIONfor 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β
| Scenario | Rows Scanned | Partitions Scanned | Cost |
|---|---|---|---|
| Full Scan | 10 | All | High |
| With SOS | 5 | Relevant MPs only | Lower |
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 Type | Meaning | Example Value | Notes |
|---|---|---|---|
| BuildCosts | One-time compute credits to initially create SOS paths | 0.001493 Credits | Usually tiny |
| StorageCosts | Extra storage/month for SOS metadata | 0.000034 TB (~34 MB) | Recurring |
| MaintenanceCosts | Ongoing compute to keep SOS paths updated | Not available until 7 days history | Increases with frequent updates |
| Benefit | Not 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:
WHEREclauses 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.