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
In a nutshell
  • 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

FeatureBest forEditionOngoing cost
Query acceleration serviceOutlier queries with large scans and a selective filter or aggregation; large INSERT or COPYEnterprise or higherServerless credits, only while accelerating
Search optimization servicePoint lookups returning few rows, substring and regex searches, semi-structured and geospatial lookupsEnterprise or higherStorage for the access path plus maintenance compute
Clustering keysRange and equality filters on very large tablesAll editionsAutomatic Clustering serverless credits
Materialized viewsThe same aggregation or projection on one table, run oftenEnterprise or higherStorage plus background refresh compute
Slow querylarge scan, outliersQuery acceleration serviceSlow queryneedle in haystack lookupSearch optimization serviceSlow queryrange filter on huge tableClustering keySlow querysame aggregate every timeMaterialized view

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_FACTOR caps 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 the ACCOUNT_USAGE.QUERY_ACCELERATION_ELIGIBLE view.

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.

Exam tip

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.

Point lookupWHERE id = 42consultSearch access pathvalues per partitionskipNon matching partitionsscanFew partitionsfew rows
  • Enable it: ALTER TABLE t ADD SEARCH OPTIMIZATION, or limit it to columns and methods, such as ON EQUALITY(customer_id), SUBSTRING(email).
  • Helps equality and IN lookups, LIKE and RLIKE substring searches, SEARCH and SEARCH_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) or ALTER TABLE t CLUSTER BY (...).
  • Check health with SYSTEM$CLUSTERING_INFORMATION and SYSTEM$CLUSTERING_DEPTH. A lower average depth means better clustering and better pruning.
  • Pause or restart maintenance with ALTER TABLE t SUSPEND RECLUSTER and RESUME 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, LIMIT or 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.

Watch out

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.