Performance Optimization, Querying, and Transformation · Topic 3 of 6
Query Acceleration, Search Optimization, Clustering and Materialized Views
Choose between the query acceleration service, search optimization, clustering keys and materialized views by query pattern, edition and ongoing cost.
- 5 min
- QAS: serverless help for outlier large scans;
ENABLE_QUERY_ACCELERATION; max scale factor default 8; Enterprise. - SOS: search access path for point lookups, substring, semi-structured and geospatial searches;
ADD SEARCH OPTIMIZATION; Enterprise. - Clustering keys: for very large tables filtered on the same columns; Automatic Clustering is serverless; check with
SYSTEM$CLUSTERING_INFORMATION.
What it is
Snowflake has four features that speed up specific query patterns beyond what warehouse sizing can do. Each one targets a different shape of query, and each one adds its own cost.
- Query acceleration service (QAS) lends extra serverless compute to large scans.
- Search optimization service (SOS) builds a search access path for selective lookups.
- Clustering keys keep a large table's micro-partitions ordered by the columns you filter on.
- Materialized views store a precomputed result of a query on one table.
The exam usually describes a query pattern and asks which feature fits. Learn the pattern first, then the feature.
How it works
Picking the feature
| Feature | Best for | Edition | Ongoing cost |
|---|---|---|---|
| Query acceleration service | Outlier queries with large scans and a selective filter or aggregation; large INSERT or COPY | Enterprise or higher | Serverless credits, only while accelerating |
| Search optimization service | Point lookups returning few rows, substring and regex searches, semi-structured and geospatial lookups | Enterprise or higher | Storage for the access path plus maintenance compute |
| Clustering keys | Range and equality filters on very large tables | All editions | Automatic Clustering serverless credits |
| Materialized views | The same aggregation or projection on one table, run often | Enterprise or higher | Storage plus background refresh compute |
Query acceleration service
QAS offloads parts of an eligible query, mainly scanning and filtering, to shared serverless compute. The warehouse size stays the same; only the heavy portion gets extra help.
- Enable it per warehouse:
ALTER WAREHOUSE wh SET ENABLE_QUERY_ACCELERATION = TRUE. QUERY_ACCELERATION_MAX_SCALE_FACTORcaps the extra compute as a multiple of the warehouse size. The default is 8; 0 removes the upper limit.- Find candidates with
SYSTEM$ESTIMATE_QUERY_ACCELERATION('<query_id>')or theACCOUNT_USAGE.QUERY_ACCELERATION_ELIGIBLEview.
Queries are not eligible when they scan too few partitions, when filters are not selective enough, or when they use nondeterministic functions such as RANDOM.
QAS is the answer when a warehouse handles a mixed workload and a few outlier queries scan far more data than the rest. Resizing the whole warehouse for those few would waste credits on every other query.
Search optimization service
SOS builds a persistent search access path that records which values might appear in each micro-partition. Queries then skip partitions that cannot match, even when the table is not clustered on that column.
- Enable it:
ALTER TABLE t ADD SEARCH OPTIMIZATION, or limit it to columns and methods, such asON EQUALITY(customer_id), SUBSTRING(email). - Helps equality and
INlookups,LIKEandRLIKEsubstring searches,SEARCHandSEARCH_IP, lookups inside VARIANT, OBJECT and ARRAY columns, and some geospatial functions. - A background service builds and maintains the path; no warehouse is needed for maintenance. Queries are not accelerated until the build finishes.
SOS suits high cardinality columns where clustering cannot help, such as looking up one order ID among billions.
Clustering keys
A clustering key tells Snowflake which columns should keep similar values in the same micro-partitions. Automatic Clustering then reclusters the table in the background as DML changes it, using serverless credits.
- Define it:
CREATE TABLE ... CLUSTER BY (order_date, region)orALTER TABLE t CLUSTER BY (...). - Check health with
SYSTEM$CLUSTERING_INFORMATIONandSYSTEM$CLUSTERING_DEPTH. A lower average depth means better clustering and better pruning. - Pause or restart maintenance with
ALTER TABLE t SUSPEND RECLUSTERandRESUME RECLUSTER.
Good candidates are very large tables (multiple terabytes) that are queried often, filtered on the same columns, and changed less often than they are read. Snowflake recommends at most three or four columns, ordered from lower to higher cardinality.
Materialized views
A materialized view stores the result of a SELECT on one table and keeps it current automatically. A background service refreshes it after base table changes, and queries always see current data.
- The optimizer can rewrite a query on the base table to use a matching materialized view, even if the query does not name it.
- Restrictions: no joins, and no window functions,
HAVING,ORDER BY,LIMITor UDFs in the definition. Only some aggregate functions are supported. - They help most when the base table changes rarely but the same expensive aggregation or filter runs often, or when querying external tables.
Common mistakes
Clustering a small table. Small tables have few micro-partitions, so pruning gains are tiny and reclustering credits are wasted. Clustering pays off on multi terabyte tables.
Using a materialized view for a join. Materialized views cannot contain joins. For a multi table result, use a dynamic table or a regular table refreshed by a task.
Choosing SOS for range scans and aggregations. SOS targets selective lookups that return few rows. Wide range scans or analytics over most of a table belong to clustering or QAS.
QAS, SOS and materialized views all require Enterprise Edition or higher. If a scenario says the account is on Standard Edition, clustering keys and warehouse sizing are the options left.
What to remember for the exam
- QAS: serverless help for outlier large scans;
ENABLE_QUERY_ACCELERATION; max scale factor default 8; Enterprise. - SOS: search access path for point lookups, substring, semi-structured and geospatial searches;
ADD SEARCH OPTIMIZATION; Enterprise. - Clustering keys: for very large tables filtered on the same columns; Automatic Clustering is serverless; check with
SYSTEM$CLUSTERING_INFORMATION. - Materialized views: one table, no joins, always current, automatic query rewrite; Enterprise.
- Every feature adds ongoing credits or storage, so use it only where the pattern fits.
Check yourself
Most queries on a warehouse run fast, but a few scan terabytes with a selective filter. Which feature helps without resizing?
The query acceleration service, which lends serverless compute to eligible large scans on that warehouse.
Users look up single records by a unique transaction ID in a ten billion row table. Which feature fits?
The search optimization service, because it speeds up selective point lookups on high cardinality columns.
Why can a materialized view not replace a reporting query that joins two tables?
Materialized views are defined on a single table and cannot contain joins.
Topic 3 of 6 in Performance Optimization, Querying, and Transformation
Test yourself on Performance Optimization, Querying, and Transformation
The full Snowflake SnowPro Core COF-C03 question bank has 845 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.