Query Acceleration Service & Search Optimization in Snowflake
Day 37 put each workload on its own warehouse. It also weighed scaling up against scaling out. Those levers change the warehouse itself. Today’s two levers leave the warehouse unchanged. They add serverless help on top of it. The Query Acceleration Service sends heavy scan work to a shared serverless pool. Search Optimization builds a separate structure that prunes micro-partitions for selective lookups. Both cost extra credits beyond the warehouse. The exam tests when each one is worth that cost.
Five terms drive every question in this sub-objective. The naive reading of each points away from the exam answer.
| Term | What it sounds like | What it means in Snowflake |
|---|---|---|
| Serverless | Runs without a server you manage | Compute Snowflake runs for you on demand. You pay only while it works. It is billed apart from your virtual warehouse. |
| Query Acceleration Service | A turbo button | A warehouse setting. When one query has a big scan, Snowflake hands that scan to a serverless pool so it finishes sooner. Enterprise Edition only. |
| Search access path | Some kind of index | The persistent structure the search optimization service builds per table. It records which micro-partitions can hold a value, so lookups skip the rest. It is not a copy of the rows. |
| Point lookup | Find one thing | A highly selective query that returns one row or a few. A single customer by id is the classic case. |
| Scale factor | How hard it tries | A cap on how much serverless compute acceleration may lease. It multiplies the warehouse size. Default 8, range 0 to 100, where 0 means no cap. |
The Query Acceleration Service
What it does
The Query Acceleration Service (QAS) offloads the heavy scan and filter parts of a query to a shared serverless pool. Those parts run in parallel on leased compute. The rest of the query runs on your warehouse as usual. The result returns faster, with no resize.
Snowflake decides per query whether acceleration helps. It engages only when a query has a large eligible scan. A small query gets no benefit and pays nothing extra.
Which problem it solves
QAS targets one heavy query that scans a lot but returns little. An example is a single large aggregation on an otherwise idle warehouse. Multi-cluster from Day 37 solves a different problem: many queries queueing at once. Resizing solves a third: a complex query that needs more memory per step, the spilling case from Day 36.
| Symptom in the stem | Right lever | Why |
|---|---|---|
| One heavy query scans billions of rows, returns few | Query Acceleration Service | Offloads the scan to serverless compute |
| Many queries queue while each runs fast | Multi-cluster, scale out | Adds clusters for concurrency (Day 37) |
| One complex query is slow and spills to disk | Resize larger, scale up | More memory per cluster (Day 36, Day 8) |
In training sessions I have run, candidates pick multi-cluster for a single slow scan. That is the swap to watch. One heavy query points to QAS. Many queued queries point to more clusters.
Enabling it and capping the cost
Enable QAS on a warehouse with one parameter. Cap its spend with a second.
ALTER WAREHOUSE lab_xs SET
ENABLE_QUERY_ACCELERATION = TRUE
QUERY_ACCELERATION_MAX_SCALE_FACTOR = 8;The scale factor limits how much serverless compute a query may lease. It is a multiplier on the warehouse size. The default is 8 when you set acceleration through CREATE WAREHOUSE. Valid values run from 0 to 100. A value of 0 removes the cap and lets a query lease whatever the service has free.
Checking eligibility, cost, and edition
Check whether a past query would benefit with SYSTEM$ESTIMATE_QUERY_ACCELERATION. Pass it a query id. It returns a JSON object with the eligibility status and the estimated time at several scale factors.
QAS requires Enterprise Edition or higher. It is a serverless feature. Credits are billed by the second and only while it runs. They are separate from your warehouse credits.
Snowflake now turns QAS on by default for new Gen2 and multi-cluster warehouses. The exam still tests the durable facts. You enable it with a warehouse parameter. It needs Enterprise Edition. It bills as a serverless add-on.
The Search Optimization Service
What it builds
The Search Optimization Service speeds up selective lookups on large tables. It works by building a search access path for each enabled table. The search access path is a persistent structure. It records which micro-partitions can contain each value. A lookup then skips every micro-partition that cannot hold the searched value.
Candidates I have trained expect this to act like a row index. It is not. It stores no rows. It stores partition-level metadata that drives pruning.
Which searches it supports
You enable search optimization per column and name a search method. Three methods cover most exam cases:
- EQUALITY handles
=andINpredicates. It is the default when you add search optimization without naming a method. - SUBSTRING handles
LIKEand regular-expression matches, but only when the searched substring is five characters or longer. - GEO handles geospatial predicates on GEOGRAPHY columns.
Equality on a high-cardinality column gives the biggest win. A column with few distinct values, such as a status flag, gains little from it.
Configuring and inspecting it
ALTER TABLE so_test
ADD SEARCH OPTIMIZATION ON EQUALITY(O_ORDERSTATUS), SUBSTRING(O_COMMENT);
DESCRIBE SEARCH OPTIMIZATION ON so_test;Run DESCRIBE SEARCH OPTIMIZATION to list the methods and columns configured on a table. SHOW TABLES reports build progress in its search_optimization_progress column. The access path is ready for use when that value reaches 100.
Cost and edition
Search optimization needs Enterprise Edition or higher. It is serverless. The search access path consumes storage. Building and maintaining it consumes compute. Both are billed separately from your warehouse.
The persistent structure has one name on the exam: the search access path. That exact phrase is the answer to a known item type, not micro-partitions and not a clustering key.
Search Optimization Is Not Clustering
Both features reduce how much data a query scans. They work in different ways. They suit different queries. The exam leans on candidates blurring them.
What each one changes
A clustering key reorganizes rows so similar values sit in the same micro-partitions. That helps range scans, large filtered scans, and joins on the key. It moves data physically. A table carries one clustering key. Day 39 covers clustering in full.
Search optimization moves no data. It adds a separate metadata structure, the search access path. That structure targets very selective point lookups, meaning one row or a few from a large table. You configure it per column and per method.
| Question | Clustering key | Search optimization |
|---|---|---|
| What it changes | Row order inside micro-partitions | Adds a search access path, no row change |
| Best query type | Range scans, large filtered scans, joins on the key | Selective point lookups using =, IN, substring, or geo |
| Granularity | One key per table | Per column and per method |
| Result size it suits | Many rows across a range | One row or a few |
The two features are complementary. A table can carry a clustering key and a search access path at the same time. Snowflake documentation supports running them together.
Read the result size in the stem to choose. A few rows from a highly selective filter points to search optimization. A large filtered range or a join on a key points to clustering.
Cheat Sheet
| Concept | What to remember | Exam keyword |
|---|---|---|
| Query Acceleration Service | Offloads a heavy scan to a serverless pool. One big query, not concurrency | “serverless scan” |
| Enable QAS | ENABLE_QUERY_ACCELERATION = TRUE on the warehouse | “warehouse parameter” |
| Scale factor | Cap on leased compute. Default 8, range 0 to 100, 0 means no cap | “max scale factor” |
| SYSTEM$ESTIMATE_QUERY_ACCELERATION | Tells you if a past query would benefit from QAS | “estimate” |
| Search Optimization Service | Builds a search access path for selective point lookups | “point lookup” |
| Search access path | The persistent structure SOS maintains. Not a copy of rows | “search access path” |
| Search methods | EQUALITY for = and IN, SUBSTRING for 5+ chars, GEO for GEOGRAPHY | “equality / substring / geo” |
| Search opt vs clustering | Point lookups vs ranges and joins. SOS adds metadata, clustering moves rows | “selective vs range” |
| Edition for both | Enterprise Edition or higher for QAS and search optimization | “Enterprise” |
| Cost for both | Serverless. Billed separately from the warehouse | “extra credits” |
Exam Tip
Two swaps appear in this sub-objective. The first swaps the Query Acceleration Service with multi-cluster. QAS speeds one heavy scan on a serverless pool. Multi-cluster clears a queue of many concurrent queries. Match the lever to the symptom the stem describes.
The second swap calls search optimization a kind of clustering. It is not. Clustering reorders rows inside micro-partitions. Search optimization builds a separate search access path. It moves no rows. When a stem asks for the persistent structure behind point lookups, the answer is the search access path.
Both services require Enterprise Edition. If a stem sets the scene on Standard edition, any option that depends on QAS or search optimization is wrong before you read further.
Hands-On Lab
lab_xs warehouse, and the SNOWFLAKE_SAMPLE_DATA database. This lab copies sample data into a throwaway table. It never touches day10_orders.Turn on query acceleration for the lab warehouse. Enable the service and set a scale-factor cap so a runaway query cannot lease unlimited compute.
ALTER WAREHOUSE lab_xs SET
ENABLE_QUERY_ACCELERATION = TRUE
QUERY_ACCELERATION_MAX_SCALE_FACTOR = 8;
USE WAREHOUSE lab_xs;SHOW WAREHOUSES LIKE 'lab_xs';. The enable_query_acceleration column reads true and the scale factor reads 8. If the ALTER fails on those properties, your account is not Enterprise Edition. That failure is the edition rule in action.Run a heavy scan, then estimate acceleration for it. The first query gives you a query id. The function then reports whether that query was eligible.
-- Heavy scan to generate a query id.
SELECT L_RETURNFLAG, SUM(L_QUANTITY)
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.LINEITEM
GROUP BY L_RETURNFLAG;
-- Copy the query id from Query History, then estimate.
SELECT SYSTEM$ESTIMATE_QUERY_ACCELERATION('<paste_query_id>');Create a disposable copy of ORDERS. Search optimization is configured on a table you own, so this lab works on a private copy.
CREATE OR REPLACE TABLE so_test AS
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS;so_test is a private, throwaway copy. The shared day10_orders table is never read or changed by this lab.Add two search methods. Equality on the status column, substring on the free-text comment column.
ALTER TABLE so_test
ADD SEARCH OPTIMIZATION ON EQUALITY(O_ORDERSTATUS), SUBSTRING(O_COMMENT);SHOW TABLES LIKE 'so_test'; and watch search_optimization_progress climb toward 100. The access path is not usable until that build finishes.Inspect the configuration. This shows the methods and target columns as a table.
DESCRIBE SEARCH OPTIMIZATION ON so_test;O_ORDERSTATUS and SUBSTRING on O_COMMENT. This is the search access path configuration in readable form.Run a lookup and read the Query Profile. A selective filter is where the access path does its work.
SELECT *
FROM so_test
WHERE O_ORDERSTATUS = 'F'
AND O_COMMENT LIKE '%special%';O_ORDERSTATUS has only three distinct values, so equality there prunes little. The substring on O_COMMENT is seven characters, above the five-character floor, so it is the part the access path can serve.Cleanup. Drop the search optimization, drop the copy, and return the warehouse to its prior state.
ALTER TABLE so_test DROP SEARCH OPTIMIZATION;
DROP TABLE IF EXISTS so_test;
ALTER WAREHOUSE lab_xs SET ENABLE_QUERY_ACCELERATION = FALSE;
-- lab_xs returns to its prior state. day10_orders is untouched.Practice Questions
Options:
A. Micro-partitions
B. Clustering keys
C. Equality searches
D. Search access paths
Why D: The search optimization service builds and maintains a persistent structure called a search access path for each enabled table. It records which micro-partitions can hold a value, so point lookups skip the rest.
Why not A: Micro-partitions are the storage for every table, not a structure specific to search optimization.
Why not B: Clustering keys reorder rows inside micro-partitions. That is a different optimization. Re-read Day 39 once it lands if this slips.
Why not C: An equality search is a query pattern, not a stored data structure.
Options:
A. Convert the warehouse to multi-cluster
B. Enable the Query Acceleration Service on the warehouse
C. Lower AUTO_SUSPEND on the warehouse
D. Increase STATEMENT_TIMEOUT_IN_SECONDS
Why B: The bottleneck is a single large scan, not concurrency. QAS offloads the scan portion to a serverless pool, so the heavy part finishes faster without resizing.
Why not A: Multi-cluster fixes queuing from many concurrent queries. The warehouse here is idle, so there is no queue to clear. Re-read Day 37 if missed.
Why not C: Auto-suspend controls idle cost. It does nothing for a slow scan.
Why not D: A longer timeout lets the query run longer before it aborts. It does not make it faster.
Options:
A. A clustering key on customer_id
B. Search optimization with EQUALITY on customer_id
C. A materialized view over the table
D. Resizing the warehouse to the next size
Why B: Selective point lookups on a high-cardinality column are the exact case search optimization is built for. The equality access path prunes micro-partitions that cannot hold the id.
Why not A: Clustering helps range scans and joins on the key. It is less direct for single-row point lookups than a search access path.
Why not C: A materialized view precomputes an aggregation. It does not speed up arbitrary single-row lookups.
Why not D: A bigger warehouse scans the same partitions, just with more compute. It does not prune them.
Options:
A. EQUALITY, for equality and IN predicates
B. SUBSTRING, for LIKE and regular-expression matches
C. GEO, for geospatial predicates on GEOGRAPHY columns
D. RANGE, for BETWEEN and greater-than filters
E. CLUSTER, for reordering rows in micro-partitions
Why A: EQUALITY is the method for = and IN. It is also the default when no method is named.
Why B: SUBSTRING supports LIKE and regex, as long as the searched substring is five characters or longer.
Why C: GEO supports selected geospatial functions on GEOGRAPHY columns.
Why not D: Range filters are a clustering strength, not a search optimization method.
Why not E: Reordering rows is what clustering does. It is a separate feature, not a search method.
Options:
A. Both are free once you are on Enterprise Edition
B. Both are serverless features that consume credits separately from your virtual warehouse
C. Both are billed only as extra storage, never as compute
D. Neither consumes credits because both run on the Cloud Services layer
Why B: Both are serverless add-ons. Enterprise Edition unlocks them, but using them still draws serverless credits that are billed apart from warehouse compute.
Why not A: Edition unlocks the features. It does not make their usage free.
Why not C: QAS cost is compute. Search optimization has both a storage cost for the access path and a compute cost to build and maintain it.
Why not D: Cloud Services handles metadata and optimization, not these workloads. Both are serverless compute features. Re-read Day 2 on the Cloud Services layer if this is fuzzy.
Snowflake Documentation
Official docs for today’s topics. The exam pulls directly from these.
Today you learned: two serverless levers that sit on top of a warehouse. The Query Acceleration Service offloads one heavy scan to a leased serverless pool, controlled by ENABLE_QUERY_ACCELERATION and a scale factor that defaults to 8. It is for a single big scan, not for concurrency. Search optimization builds a persistent search access path that prunes micro-partitions for selective point lookups, configured per column with EQUALITY, SUBSTRING, or GEO. Both require Enterprise Edition and bill as serverless add-ons.
Key takeaway: match the lever to the symptom. One heavy scan means query acceleration. A selective point lookup means search optimization. Many queued queries still means multi-cluster from Day 37. The persistent structure behind point lookups is the search access path. That is not the same thing as a clustering key.
Tomorrow (Day 39): Clustering Keys and Materialized Views. You set a clustering key and read clustering depth, then meet materialized views that refresh on serverless credits. The exam rule to carry in: cluster on the columns you filter in WHERE before the columns you join on.
