Course contents

SnowPro Core COF-C03 Study GuidePart 2: Warehouses and performanceSearch Optimization & Query Acceleration

Search Optimization & Query Acceleration

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.

SOS · find rows
  • WHERE user_id = 8842193
  • Builds a lookup path per table
  • Pays: storage + maintenance, continuously
QAS · chew through scans
  • Month-end scan of everything
  • Rents extra workers per query
  • Pays: per second, only while borrowed
Definition

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 returnedPartitions scannedRuntimeWhy
Before SOS8,140 of 8,20042 sIDs scattered everywhere; min/max pruning can't exclude anything
After SOS3 of 8,2000.4 sThe 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

SOS territory
  • Equality: WHERE user_id = 8842193
  • IN lists: WHERE status IN ('A','B')
  • Substring and regex on enabled columns
  • Some VARIANT and geospatial predicates
Not SOS territory
  • 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.

The same point lookup, WHERE user_id = 8842193, on a table of 8,200 partitions (illustrative). Without search optimization, user IDs are scattered, so every partition's min/max range contains the value and all 8,200 partitions are scanned. With search optimization, the search access path maps the value to the two partitions that hold it, partitions 3,005 and 7,941, and only those two are scanned. No clustering key and no reordering are involved.WITHOUT SOS · MIN/MAX ONLYWITH SEARCH OPTIMIZATIONPOINT LOOKUPWHERE user_id = 8842193every partition's rangecontains the value8,200 of 8,200 scannedSERVERLESSSearch access pathvalue to partitionspoints to MP 3,005 and MP 7,9412 of 8,200 scannedEach cell stands for about 200 partitions (illustrative). The same point lookup, WHERE user_id = 8842193, on a table of 8,200 partitions (illustrative). Without search optimization, user IDs are scattered, so every partition's min/max range contains the value and all 8,200 partitions are scanned. With search optimization, the search access path maps the value to the two partitions that hold it, partitions 3,005 and 7,941, and only those two are scanned. No clustering key and no reordering are involved.POINT LOOKUPWHERE user_id = 8842193WITHOUT SOS · MIN/MAX ONLYevery partition's range contains the value8,200 of 8,200 scannedWITH SEARCH OPTIMIZATIONSERVERLESSSearch access pathvalue to partitionspoints to MP 3,005and MP 7,9412 of 8,200 scannedEach cell stands for about 200 partitions(illustrative).
Figure 2.11 Search optimization finds rows that min/max pruning can't. Top: scattered user IDs put the value inside every partition's range, so the whole table is scanned. Bottom: the search access path, kept current by a serverless service, points straight to the two partitions that hold the value. No clustering key and no physical reordering are involved.

SOS vs clustering

Clustering keySearch Optimization
Predicate shapeRanges and equality on the key columns, in key orderEquality, IN, substring/regex on any enabled column
Rows returnedSlices of the table (date ranges, regions)A handful (needle in haystack)
Sort orders servedOne: the key you choseIndependent per column; no ordering involved
MechanismPhysically reorders rows into partitionsHidden search access path alongside the table
CostServerless recluster creditsStorage 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_QUERY_ACCELERATION for QAS below. Once it's live, the actual spend is auditable in ACCOUNT_USAGE.SEARCH_OPTIMIZATION_HISTORY, 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 table
ALTER TABLE events ADD SEARCH OPTIMIZATION;

-- Or per column and predicate type, which is cheaper
ALTER TABLE events ADD SEARCH OPTIMIZATION ON EQUALITY(user_id), SUBSTRING(email);
Exam traps
  • The search access path doesn't help wide range predicates. WHERE order_date BETWEEN ... is clustering territory; WHERE user_id = 8842193 is SOS territory. (Secondary indexes, created with CREATE INDEX and maintained under search optimization, can serve selective ranges.)
  • CREATE OR REPLACE TABLE makes 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.
Key takeaways
  • 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

Definition

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 SET
  ENABLE_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 SET
  ENABLE_QUERY_ACCELERATION = TRUE
  QUERY_ACCELERATION_MAX_SCALE_FACTOR = 8;

-- Which past queries would have benefited?
SELECT * FROM TABLE(SYSTEM$ESTIMATE_QUERY_ACCELERATION('<query_id>'));
Who does what under Query Acceleration. The month-end rollup is submitted to BI_WH, a Medium warehouse, which plans the query, does the joins and aggregation, and returns the final result. Eligible scan-and-filter fragments are offloaded to a serverless QAS pool, which reads SALES micro-partitions from storage and sends filtered rows back to the warehouse. The warehouse is billed as usual; the pool is billed separately per second, capped by QUERY_ACCELERATION_MAX_SCALE_FACTOR.WAREHOUSE · BILLED AS USUALQAS POOL · BILLED SEPARATELYSTORAGE LAYERQUERYMonth-end rollupscans 2 TB of SALESWAREHOUSEBI_WH · Mediumplans, joins, aggregatesRESULTResultto the userSERVERLESSQAS poolscan and filter fragmentsper second, capped byMAX_SCALE_FACTORoffload eligible fragmentsfiltered rows8,200 PARTITIONSSALES micro-partitionsreadssubmittedfinal result

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

Figure 2.12 Query Acceleration lends serverless compute to the scan, not to the whole query. The warehouse still plans, joins and returns the result; eligible fragments go down to the serverless pool, which reads micro-partitions and sends filtered rows back up. The pool is billed separately, per second, capped by the warehouse's QUERY_ACCELERATION_MAX_SCALE_FACTOR. Queries that aren't eligible run on the warehouse unchanged and add no QAS cost.

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:

StepArithmeticResult
Warehouse rateMedium4 credits/hour
Borrowing ceilingscale factor 5 × 4 credits/hourup to 20 credits/hour of serverless compute
Per second, at full burst20 ÷ 3,600≈ 0.0056 credits/second
One 90-second max-capacity burst90 × 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_QUERY_ACCELERATION('<query_id>') evaluates one past query, and the ACCOUNT_USAGE.QUERY_ACCELERATION_ELIGIBLE view ranks the warehouses and queries with the most eligible time across the account. After enabling, the credits actually consumed land in ACCOUNT_USAGE.QUERY_ACCELERATION_HISTORY, the verify-the-bill counterpart to the eligibility view.

Exam traps
  • 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 scenarioBuy thisNot this
Many users, queries queuing at peakScale out (multi-cluster)Scaling up
One query slow, spilling to storageScale up (resize)Scaling out
Range filters on the same columns scan the whole big tableClustering keySOS
Point lookup by ID returns 3 rows, scans 40,000 partitionsSearch OptimizationClustering, MV
Same expensive aggregation re-run constantly on one tableMaterialized viewQAS
Occasional huge ad-hoc scans on an otherwise fine warehouseQuery AccelerationPermanent resize