What you will be able to do
- Explain how micro-partition metadata and clustering depth affect pruning
- Choose between search optimization, query acceleration and materialized views for a given query pattern
- Estimate a query's benefit from QAS before enabling it, and cap QAS spend
- Tell the persisted query result cache apart from the warehouse cache
- Reduce Time Travel and Fail-safe storage costs with transient or temporary tables
1.Micro-partitions, pruning and clustering
Each feature on this page works by making a query read less data, so start with how Snowflake stores data. Every table is automatically divided into micro-partitions of 50–500 MB of uncompressed data, stored by column. Snowflake records metadata for each micro-partition, including the range of values in each column and the number of distinct values. At query time it uses that metadata to prune. First it skips micro-partitions that can't contain matching rows. Then it reads only the referenced columns in the ones that remain. Ideally, a filter that selects 10% of a range scans about 10% of the micro-partitions.
How well pruning works depends on clustering, meaning how closely the stored order follows the columns you filter on. Tables are partitioned in the order the data is loaded. As DML changes the table, value ranges in different micro-partitions can start to overlap. Clustering depth measures that overlap. An empty table has a depth of 0, and a populated table has an average depth of 1 or more. Smaller is better. Use depth to monitor clustering health over time and to decide whether a large table needs an explicit clustering key, which you set with CREATE TABLE or ALTER TABLE. Depth is not a precise score, though. The best signal is query performance: if queries slow down over time, the table may benefit from clustering. Hybrid tables don't support clustering keys.
Checkpoint 1 of 8· Check yourself
Which statement about clustering depth is correct?
Depth measures overlap between micro-partitions, so less overlap means a lower number and better clustering. An empty table has depth 0, and query performance remains the final test.
“The smaller the average depth, the better clustered the table is with regards to the specified columns.”Source: docs.snowflake.com
Sources1
2.Search optimization service for selective lookups
Clustering helps range filters on the clustered columns. The search optimization service targets a different pattern: highly selective lookups. Examples are point lookups that return one or a few rows, SEARCH and SEARCH_IP text searches, LIKE/ILIKE/RLIKE substring and regex predicates, equality and IN predicates on semi-structured and structured columns, and some geospatial functions. It builds a persistent search access path that records which micro-partitions might contain which values, so more micro-partitions can be skipped. A predicate breaks this if it casts the column instead of the constant. For example, CAST(c2 AS NUMBER) = 2 can't use search optimization, while c2 = 2 can.
The service runs in the background and doesn't need a warehouse, but its storage and compute are billed. When data changes, the access path is updated automatically, which adds maintenance cost.
ALTER TABLE test_table ADD SEARCH OPTIMIZATION;Checkpoint 2 of 8· Put it in order
Put these steps in order when you roll out search optimization and measure its benefit
- 1.The maintenance service builds the search access path in the background
- 2.Check search_optimization_progress in SHOW TABLES until the table shows as fully optimized
- 3.Re-run the lookup queries and compare execution times
- 4.Run ALTER TABLE ... ADD SEARCH OPTIMIZATION on the table
Queries aren't accelerated until the access path is fully built, so measuring before the progress column confirms full optimization gives misleading results.
“make sure this column shows that the table has been fully optimized”Source: docs.snowflake.com
Sources2
3.Query acceleration service for outlier queries
The query acceleration service (QAS) works on the warehouse side. It offloads parts of eligible queries to shared serverless compute, which reduces the impact of outlier queries on the warehouse. Two patterns qualify: large scans with an aggregation or selective filter, and large scans that insert or copy many rows. A query is ineligible if it scans too few partitions, its filter isn't selective enough, its GROUP BY cardinality is too high, or it uses nondeterministic functions such as SEQ or RANDOM. QAS depends on server availability, so the speed-up can vary over time.
You don't have to guess. The QUERY_ACCELERATION_ELIGIBLE view lists eligible queries and warehouses. SYSTEM$ESTIMATE_QUERY_ACCELERATION takes the ID of a past query and returns estimated execution times at different scale factors.
SELECT PARSE_JSON(SYSTEM$ESTIMATE_QUERY_ACCELERATION('8cd54bf0-1651-5b1c-ac9c-6a9582ebd20f'));QAS can raise a warehouse's credit consumption rate, and QUERY_ACCELERATION_MAX_SCALE_FACTOR limits it. The defaults are worth knowing. If you set ENABLE_QUERY_ACCELERATION = TRUE explicitly, the scale factor is 8. Snowflake turns QAS on automatically for new Gen2 standard and new multi-cluster warehouses, with a scale factor of 2. Converting an existing single-cluster warehouse to multi-cluster does not turn it on.
Checkpoint 3 of 8· Fill the gap
Which property turns QAS on for an existing warehouse?
ALTER WAREHOUSE my_other_wh SET ? = true;ENABLE_QUERY_ACCELERATION switches the service on. QUERY_ACCELERATION_MAX_SCALE_FACTOR only limits how much serverless compute it can use.
Source: docs.snowflake.comCheckpoint 4 of 8· Exam question
A dashboard runs a single complex aggregation query against a 10 TB fact table. The query currently runs on a Small warehouse and takes 40 minutes because it is CPU- and memory-bound on one node, but only a handful of users run it concurrently. Which change addresses the bottleneck?
Correct answer: A — Resize the warehouse to a larger size (scale up) so the single query gets more compute and memory per node to process the complex aggregation faster
- A. Correct: a single expensive query bound by CPU and memory on one node benefits from scaling up, since a larger warehouse size gives each node in the cluster more compute and memory to work through the same aggregation faster.
- B. Incorrect: multi-cluster warehouses scale out to handle more concurrent queries by spinning up additional clusters, but they do not make any single query run faster because each query still executes on one cluster.
- C. Incorrect: search optimization service accelerates highly selective point-lookup and equality/IN predicates on large tables, not a full-table aggregation scan that touches most of the data regardless of index structures.
- D. Incorrect: resource monitor notify thresholds only alert administrators about credit consumption; they do not change how compute resources are allocated to a running query, so they would not reduce the 40-minute runtime.
4.Materialized views and serverless maintenance costs
The search optimization documentation lists materialized views, clustered or unclustered, alongside query acceleration and clustering as ways to optimize query performance. The provided sources say little about how materialized views behave, but they are clear about cost. Maintaining a materialized view uses serverless compute, tracked in a Snowflake-provided warehouse named MATERIALIZED_VIEW_MAINTENANCE. You can see it in Snowsight cost management or in the MATERIALIZED_VIEW_REFRESH_HISTORY view or table function. You control the cost by choosing how many views to create, on which tables, and with how many rows and columns. Suspending a view's maintenance only postpones the cost. The longer maintenance is deferred, the more work is waiting.
Automatic clustering, search optimization and materialized views all maintain themselves on serverless compute. When a large batch of DML lands on a table that uses all three, each one does catch-up work and gets billed for it. Resource monitors won't stop this: an account-level monitor doesn't control serverless features such as automatic reclustering and materialized views.
Checkpoint 5 of 8· Check yourself
Materialized view maintenance costs are rising. A colleague suggests suspending the views to save money. What is the documented effect?
Suspending maintenance defers work rather than removing it, and resource monitors don't govern this serverless spend.
“suspending maintenance typically only defers costs, rather than reducing them.”Source: docs.snowflake.com
Checkpoint 6 of 8· Exam question
A data engineering team runs a Python stored procedure that trains a scikit-learn model entirely within a single Snowpark session, and the workload frequently fails with out-of-memory errors even on a Large standard warehouse. Which warehouse configuration should the team use instead?
Correct answer: A — A Snowpark-optimized warehouse, which provides substantially more memory per node than a standard warehouse of the same size for memory-intensive single-node workloads
- A. Correct: Snowpark-optimized warehouses provide significantly more memory per node than standard warehouses of the same size, which is exactly suited to single-node, memory-intensive workloads like in-memory ML model training that run inside one Snowpark session.
- B. Incorrect: resizing a standard warehouse increases the number of servers in the cluster and per-node resources at higher tiers, but a single-threaded, single-node Python training job cannot spread across that added cluster capacity, so the out-of-memory condition on that node persists.
- C. Incorrect: multi-cluster warehouses scale out to run more concurrent queries across separate clusters; they do not give one single stored procedure execution access to more memory on the node it happens to run on.
- D. Incorrect: query acceleration service targets scan-heavy and filtering portions of SQL queries by offloading them to serverless compute; it does not apply to memory consumed by a Python process executing arbitrary in-memory training code.
5.Caching: persisted results and the warehouse cache
Snowflake has two caches. The persisted query result cache keeps every query's result. If the same query runs again and the underlying data hasn't changed, Snowflake returns the stored result without executing the query. Cached results of any size expire after 24 hours. The warehouse cache is different: an active warehouse caches table data, and queries that read from that cache instead of from tables run faster. This is why "optimize the warehouse cache" appears in Snowflake's list of warehouse tuning strategies.
Checkpoint 7 of 8· Check yourself
A user re-runs yesterday morning's report query 30 hours later. The tables haven't changed. Can the persisted query result be reused?
Persisted results of any size expire after 24 hours, so a re-run 30 hours later executes again.
“For persisted query results of all sizes, the cache expires after 24 hours.”Source: docs.snowflake.com
6.Optimizing storage configuration and cost
Storage settings have their own costs. Permanent tables keep historical data for Time Travel and then for Fail-safe. Temporary and transient tables exist to reduce those costs. Their Time Travel retention is 0 or 1 day, they have no Fail-safe, and so they incur at most one day's worth of historical storage cost. Temporary tables last only for their session. Transient tables last until you drop them. Both are billed for storage while they exist. One more hidden cost: dropping a column doesn't rewrite the micro-partitions, so the dropped column's data remains in storage.
| Table type | Time Travel retention (days) | Fail-safe (days) |
|---|---|---|
| Permanent (Standard Edition) | 0 or 1 | 7 |
| Permanent (Enterprise Edition) | 0 to 90 | 7 |
| Transient / Temporary | 0 or 1 | 0 |
Checkpoint 8 of 8· Check yourself
An ETL staging table is rebuilt every night and never needs recovery. Which configuration minimizes its historical storage cost while keeping it available across sessions?
Transient tables have no Fail-safe and at most 1 day of Time Travel, and they last until dropped. A temporary table would disappear when the session ends.
“Transient and temporary tables can, at most, incur a one day’s worth of storage cost.”Source: docs.snowflake.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Queries speed up as soon as you run ALTER TABLE ... ADD SEARCH OPTIMIZATION.Why is that wrong?
The search access path is built in the background, and queries aren't accelerated until it is complete.
Covered in Search optimization service for selective lookups
2.Suspending materialized view maintenance is a lasting way to cut its cost.Why is that wrong?
Suspending defers the cost. Maintenance builds up and runs when the view resumes.
Covered in Materialized views and serverless maintenance costs
3.Converting a single-cluster warehouse to multi-cluster also turns on the query acceleration service.Why is that wrong?
QAS is on by default only for newly created multi-cluster warehouses. Altering an existing warehouse leaves QAS as it was.
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Each micro-partition contains between 50 MB and 500 MB of uncompressed data”
↩︎ Micro-partitions, pruning and clustering“Ultimately, query performance is the best indicator of how well-clustered a table is”
↩︎ Micro-partitions, pruning and clustering“The data in the dropped column remains in storage.”
↩︎ Optimizing storage configuration and cost“Snowflake does not prune micro-partitions based on a predicate with a subquery, even if the subquery results in a constant.”
↩︎ Prediction“The smaller the average depth, the better clustered the table is with regards to the specified columns.”
↩︎ Checkpoint - 2.
“Selective point lookup queries on tables.”
↩︎ Search optimization service for selective lookups“However, there is a cost for the storage and compute resources of maintenance.”
↩︎ Search optimization service for selective lookups“Queries are not accelerated until the search access path has been fully built.”
↩︎ Exam trap 1“make sure this column shows that the table has been fully optimized”
↩︎ Checkpoint - 3.
“You can also use the SYSTEM$ESTIMATE_QUERY_ACCELERATION function to assess whether a specific query is eligible for acceleration.”
↩︎ Query acceleration service for outlier queries“The maximum scale factor can help limit the consumption rate.”
↩︎ Query acceleration service for outlier queries - 4.
“When you create a new multi-cluster warehouse, Snowflake enables the Query Acceleration Service (QAS) by default.”
↩︎ Query acceleration service for outlier queries“Altering an existing single-cluster warehouse to multi-cluster does not enable QAS.”
↩︎ Exam trap 3 - 5.
“The credit costs are tracked in a Snowflake-provided virtual warehouse named MATERIALIZED_VIEW_MAINTENANCE.”
↩︎ Materialized views and serverless maintenance costs“suspending maintenance typically only defers costs, rather than reducing them.”
↩︎ Exam trap 2“suspending maintenance typically only defers costs, rather than reducing them.”
↩︎ Checkpoint - 6.
“An account-level resource monitor does not control credit usage by the Snowflake-provided compute resources for serverless features”
↩︎ Materialized views and serverless maintenance costs - 7.
“Snowflake uses persisted query results to avoid re-generating results when nothing has changed”
↩︎ Caching: persisted results and the warehouse cache“For persisted query results of all sizes, the cache expires after 24 hours.”
↩︎ Checkpoint - 8.
“Query performance improves if a query can read from the warehouse’s cache instead of from tables.”
↩︎ Caching: persisted results and the warehouse cache - 9.
“Transient and temporary tables can have a Time Travel retention period of either 0 or 1 day.”
↩︎ Optimizing storage configuration and cost“Transient and temporary tables can, at most, incur a one day’s worth of storage cost.”
↩︎ Checkpoint