Search Optimization Service
Two services in this chapter are turbo buttons, and they solve opposite problems. The support team at IsmaFrost looks up one customer at a time in a 5 TB table: SOS makes finding a needle fast. The finance team scans that whole table once a month: QAS (just below) makes scanning the haystack fast, by borrowing temporary serverless workers. Same table, opposite access patterns, two different services, and every question about them reduces to which pattern the scenario describes.
- WHERE user_id = 8842193
- Builds a lookup path per table
- Pays: storage + maintenance, continuously
- Month-end scan of everything
- Rents extra workers per query
- Pays: per second, only while borrowed
The Search Optimization Service (SOS) builds and maintains a hidden search access path over a table so that point lookups, queries returning a handful of rows via highly selective filters, find their rows without scanning. Serverless maintenance, Enterprise edition, enabled per table and optionally per column.
SOS in one worked example
Youness gets a fraud alert and needs one user's events, right now:
SELECT * FROM events WHERE user_id = 8842193;
The table is 5 TB across 8,200 micro-partitions, and user IDs arrive in event order, so every partition's min/max range for user_id spans nearly the whole ID space. Pruning has nothing to work with: metadata can't exclude any partition, and the query returns 3 rows after scanning almost everything. One ALTER TABLE events ADD SEARCH OPTIMIZATION ON EQUALITY(user_id) later, Snowflake has built a search access path, a persistent map from each value to the partitions that contain it, and the same query jumps straight to its 3 partitions.
| Same query, same 3 rows returned | Partitions scanned | Runtime | Why |
|---|---|---|---|
| Before SOS | 8,140 of 8,200 | 42 s | IDs scattered everywhere; min/ |
| After SOS | 3 of 8,200 | 0.4 s | The access path already knows which partitions hold 8842193 |
That's the whole service in one picture: it doesn't reorganize the table and it doesn't cache results, it maintains a lookup structure on the side so exact-value hunts skip the scan.
What SOS accelerates, and what it never will
- Equality: WHERE user_id = 8842193
- IN lists: WHERE status IN ('A','B')
- Substring and regex on enabled columns
- Some VARIANT and geospatial predicates
- Ranges (BETWEEN, <, >): that's clustering
- Aggregations over millions of rows: that's QAS or an MV
- External tables and materialized views: can't be enabled at all
- Caching results: SOS finds rows, it stores nothing you query
Clustering can only serve one sort order; a table clustered on date still scans fully for WHERE user_id = 8842193. SOS covers exactly that gap: equality and IN predicates on any enabled column, plus (in current Snowflake) substring/regex searches and some VARIANT and geospatial predicates. In short, SOS improves equality searches, and its scenario is "point lookup queries returning a small number of rows from large tables using highly selective filters". When those words appear, SOS beats materialized views, clustering, and query acceleration in the option list.
ALTER TABLE events ADD SEARCH OPTIMIZATION ON EQUALITY(user_ id), SUBSTRING( url);
Costs are storage for the access path plus serverless credits to keep it current, so it suits read-heavy tables with needle-in-haystack lookups, and punishes tables under constant heavy churn.
SOS vs clustering
| Clustering key | Search Optimization | |
|---|---|---|
| Predicate shape | Ranges and equality on the key columns, in key order | Equality, IN, substring/ |
| Rows returned | Slices of the table (date ranges, regions) | A handful (needle in haystack) |
| Sort orders served | One: the key you chose | Independent per column; no ordering involved |
| Mechanism | Physically reorders rows into partitions | Hidden search access path alongside the table |
| Cost | Serverless recluster credits | Storage for the path + serverless maintenance credits |
Before enabling it on a big table, ask Snowflake for a quote: SELECT SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS('events') returns predicted build and maintenance costs, the same move as SYSTEM$ESTIMATE_ for QAS below. Once it's live, the actual spend is auditable in ACCOUNT_, per table, closing the loop: estimate first, enable second, verify the bill third.
Two operational details round out the picture. Enabling SOS is privilege-gated: it takes OWNERSHIP on the table plus the ADD SEARCH OPTIMIZATION privilege on the schema, so a data engineer who owns the table but lacks the schema privilege still can't turn it on. And it doesn't attach to everything: external tables and materialized views can't have SOS, so "add search optimization to the external table" is always wrong.
-- Whole tableALTER TABLE events ADD SEARCH OPTIMIZATION;-- Or per column and predicate type, which is cheaperALTER TABLE events ADD SEARCH OPTIMIZATION ON EQUALITY(user_ id), SUBSTRING( email);
- The search access path doesn't help wide range predicates.
WHERE order_date BETWEEN ...is clustering territory;WHERE user_id = 8842193is SOS territory. (Secondary indexes, created withCREATE INDEXand maintained under search optimization, can serve selective ranges.) CREATE OR REPLACE TABLEmakes a new table and silently drops the search access path with the old one; SOS must be re-added.- SOS speeds up finding rows. It is not a cache, does not store results, and does nothing for aggregations over millions of rows.
- Equality, IN, substring: SOS. Ranges: clustering. Few rows from a huge table with a selective filter is the trigger phrase.
- Enterprise edition, per table and optionally per column, serverless maintenance plus storage.
- SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS prices it before you commit.
That closes the needle side of the chapter. Now flip the access pattern completely: instead of finding a few specific rows fast, the problem becomes chewing through a massive scan fast, and the tool changes from a persistent lookup structure to temporarily rented muscle.
Query Acceleration Service
The Query Acceleration Service (QAS) lets a warehouse borrow elastic serverless compute for the parallelizable portions of eligible queries, mainly large table scans. Be precise about this: QAS accelerates the scan-intensive portions of a query, not the query as a whole. The serverless workers take over the big read; the warehouse still runs the joins, sorts, and final aggregation. Set per warehouse, with a scale factor capping how much extra compute it may borrow (8 if you don't set it). New standard warehouses are created with it on; Snowpark-optimized ones aren't. Enterprise edition.
QAS in one worked example
Finance runs one query a month, and it's a monster: a revenue rollup that scans 2 TB of the sales table. On the Medium warehouse that serves everyone's dashboards it takes 25 minutes, and while it runs, every analyst behind it queues. The classic bad fix is resizing the warehouse to X-Large for the whole month, paying 4x every hour for a query that runs once. The QAS fix leaves the warehouse alone:
ALTER WAREHOUSE bi_wh SETENABLE_QUERY_ ACCELERATION = TRUE QUERY_ACCELERATION_ MAX_ SCALE_ FACTOR = 5;
Without QAS illustrative
The month-end rollup scans 2 TB on the Medium warehouse alone. It runs for a long time and dashboards queue behind it.
With QAS illustrative
Eligible scan-and-filter fragments run on a serverless pool in parallel. The query finishes sooner and the warehouse is freed for other queries. The speed-up depends on the query; Snowflake doesn't promise a ratio.
The warehouse stays Medium for the other 30 days, and the burst is billed per second only while borrowed (the arithmetic below puts real numbers on it). That's the shape QAS exists for: occasional outliers on an otherwise well-sized warehouse.
QAS helps outlier queries, the occasional monster scan that would otherwise force you to oversize the whole warehouse. Eligible work is scan-heavy and parallelizable: large scans feeding an aggregation or a selective filter, and large-scale scans that insert or copy substantial volumes of new rows. Ineligible work includes joins over small tables, scans with too few partitions, filters that aren't selective enough, and queries that call nondeterministic functions such as RANDOM or SEQ.
ALTER WAREHOUSE analytics_wh SETENABLE_QUERY_ ACCELERATION = TRUE QUERY_ACCELERATION_ MAX_ SCALE_ FACTOR = 8; -- Which past queries would have benefited?SELECT * FROM TABLE(SYSTEM$ESTIMATE_ QUERY_ ACCELERATION('<query_ id>' ));
WAREHOUSE · BILLED AS USUAL
QUERYMonth-end rollupscans 2 TB of SALES
submitted
WAREHOUSEBI_WH · Mediumplans, joins, aggregates
offload eligible fragments
filtered rows
final result
RESULTResultto the user
QAS POOL · BILLED SEPARATELY
SERVERLESSQAS poolscan and filter fragments
per second, capped by MAX_SCALE_FACTOR
reads
STORAGE LAYER
8,200 PARTITIONSSALES micro-partitions
The scale factor is the cost ceiling, and it's worth being able to run the numbers. Take a Medium warehouse with QUERY_ACCELERATION_MAX_SCALE_FACTOR = 5 and walk the arithmetic step by step:
| Step | Arithmetic | Result |
|---|---|---|
| Warehouse rate | Medium | 4 credits/ |
| Borrowing ceiling | scale factor 5 × 4 credits/ | up to 20 credits/ |
| Per second, at full burst | 20 ÷ 3,600 | ≈ 0.0056 credits/ |
| One 90-second max-capacity burst | 90 × 0.0056 | ≈ 0.5 credits |
The last row is the point: the ceiling sounds scary as an hourly figure, but QAS bills per second and only for the seconds actually borrowed, so a monster scan that bursts at full capacity for a minute and a half costs about half a credit on top of the warehouse's own rate. Setting the factor to 0 removes the ceiling entirely, and that setting deserves fear: 0 doesn't disable QAS, it hands Snowflake a blank check for serverless compute, one runaway scan billing whatever it takes to finish. Reserve it for warehouses whose spend a budget is actively watching, never as a default. Two tools find candidates: SYSTEM$ESTIMATE_ evaluates one past query, and the ACCOUNT_ view ranks the warehouses and queries with the most eligible time across the account. After enabling, the credits actually consumed land in ACCOUNT_, the verify-the-bill counterpart to the eligibility view.
- QAS is per warehouse, not per table (SOS) or per query. Enabling it changes nothing for ineligible queries: they run normally, no penalty.
- Scale factor 0 means unlimited, not disabled. Disabling is ENABLE_QUERY_ACCELERATION = FALSE.
- QAS credits are serverless: outside resource monitors, visible to budgets.
Choosing between the four services
| Symptom in the scenario | Buy this | Not this |
|---|---|---|
| Many users, queries queuing at peak | Scale out (multi-cluster) | Scaling up |
| One query slow, spilling to storage | Scale up (resize) | Scaling out |
| Range filters on the same columns scan the whole big table | Clustering key | SOS |
| Point lookup by ID returns 3 rows, scans 40,000 partitions | Search Optimization | Clustering, MV |
| Same expensive aggregation re-run constantly on one table | Materialized view | QAS |
| Occasional huge ad-hoc scans on an otherwise fine warehouse | Query Acceleration | Permanent resize |

