What you will be able to do
- Decide whether a table is a good candidate for a clustering key
- Choose clustering key columns based on filter use and cardinality
- Decide when a materialized view is better than a regular view
- Compare materialized views with cached query results and regular tables
1.When a clustering key pays for itself
Snowflake usually produces well-clustered data on its own. On very large tables, though, ongoing DML can leave rows scattered across micro-partitions so that they no longer group well on the columns people filter by. A clustering key is a set of columns or expressions you choose so that Snowflake keeps similar rows together in the same micro-partitions. Queries that filter on the key can then skip partitions that cannot match, and compression can improve too, especially for columns that correlate with the key. You define the key in CREATE TABLE or ALTER TABLE, and you can change or drop it later. Once it is set, Snowflake handles reclustering automatically. Hybrid tables cannot have clustering keys.
That automatic maintenance uses credits. Clustering a table that changes often means paying again and again to restore an order that the next write breaks.
Snowflake's guidance says a table suits clustering when all of these are true. It has a large number of micro-partitions, which usually means several terabytes. Its queries are selective (they read a small share of the rows) or they sort the data. And a high percentage of queries filter or sort on the same few columns. If your main goal is lower cost, the table should also have a high ratio of queries to DML. If a table you want to cluster takes a lot of DML, group the DML into large, infrequent batches. Signs that a key may help include queries that have become slower over time and a large clustering depth. Before you add a key, run a representative set of queries to record baseline performance.
Checkpoint 1 of 7· Check yourself
A team wants to cut costs by clustering one of the four tables below. Which table best fits Snowflake's criteria?
The fact table is large, its queries are selective on the same column, and it has a high ratio of queries to DML, which is the cost-reduction criterion.
“The table contains a large number of micro-partitions. Typically, this means that the table contains multiple terabytes (TB) of data.”Source: docs.snowflake.com
Checkpoint 2 of 7· Exam question
A data engineering team runs ad hoc analytical queries against a 40 TB sales fact table using a Medium warehouse. Most queries finish in under 10 seconds, but a handful of queries that scan large portions of the table behind a highly selective filter occasionally take five to ten times longer than the rest, even though the warehouse is never queued. The team wants to reduce the wall-clock time of just these outlier queries without resizing the warehouse for the whole workload. Which approach best addresses this?
Correct answer: A — Enable the query acceleration service on the warehouse so eligible large-scan, selective-filter queries offload scan and filter work to serverless compute.
- A. This is correct because the query acceleration service is built specifically for queries with large scans and selective filters, letting outlier queries borrow serverless compute to parallelize scanning and filtering without changing the warehouse size for the whole workload.
- B. This is incorrect because permanently upsizing the warehouse raises the credit rate for every query, including the fast ones, when only a small subset of outlier queries actually need extra resources.
- C. This is incorrect because a clustering key improves partition pruning for filtering, but it does not target the specific pattern of large scans with selective filters the way the acceleration service does, and it adds ongoing reclustering cost.
- D. This is incorrect because multi-cluster warehouses add clusters to handle queuing from concurrent users, not to speed up the internal execution of a single outlier query that is not being queued.
Sources1
2.Choosing the key columns: filter use and cardinality
Once a table is worth clustering, the choice of columns matters a great deal. Snowflake suggests this order of priority. First, use the columns that appear most in selective filters. For fact tables queried by date ranges, the date column is usually a good choice. Then, if there is room, add columns that appear often in join predicates. If queries usually filter on two dimensions together, clustering on both can help. Keep the key to 3 or 4 columns or expressions at most. Beyond that, costs tend to rise faster than the benefits.
Cardinality, meaning the number of distinct values, cuts both ways. The key needs enough distinct values to allow useful pruning, but few enough that Snowflake can group rows into the same micro-partitions. A Boolean column like IS_NEW_CUSTOMER has too few values. A nanosecond timestamp has too many, and higher cardinality also makes clustering more expensive to maintain. To cluster on a very high-cardinality column, define the key as an expression that reduces the number of distinct values while keeping the column's order. Each partition's minimum and maximum values then still allow pruning.
Checkpoint 3 of 7· Match them up
Match each candidate key column to Snowflake's assessment of it
Tap a term, then the definition that fits it.
Columns used in selective filters come first and join columns second. Cardinality must be neither very low nor very high.
“A column with very low cardinality might yield only minimal pruning, such as a column named IS_NEW_CUSTOMER that contains only Boolean values.”Source: docs.snowflake.com
Checkpoint 4 of 7· Exam question
A nightly batch job issues a single UPDATE statement that modifies roughly 60% of the rows in a 2 TB table, and this statement alone accounts for most of the batch's total runtime even though the warehouse has spare capacity. Which Snowflake feature is designed to speed up exactly this kind of large-scale write operation?
Correct answer: B — The query acceleration service, since it can offload portions of large INSERT, UPDATE, and DELETE operations to serverless compute.
- A. This is incorrect because the search optimization service accelerates selective read lookups such as equality or substring searches, not the write throughput of large-scale DML statements.
- B. This is correct because the query acceleration service explicitly covers large INSERT, COPY, UPDATE, and DELETE operations in addition to large scans, offloading part of the processing to serverless compute to cut wall-clock time.
- C. This is incorrect because a materialized view stores precomputed query results for reads; it has no effect on how quickly an UPDATE statement modifies rows in its own base table.
- D. This is incorrect because automatic reclustering runs as its own background maintenance process on its own schedule, and it does not run synchronously to speed up an in-flight UPDATE statement.
Sources1
3.Materialized views: storing a result that is queried again and again
Clustering changes how the base data is laid out. A materialized view stores a pre-computed result set instead. It is defined by a SELECT, and its result is kept for later use, so reading the view is faster than running the query against the base table. The benefit is largest for costly aggregations, projections and selections that run often on large data sets. The documentation names cases where they are especially useful: the result is small compared with the base table; the work involves analysing semi-structured data or slow aggregates; the source is an external table; and the base table does not change often.
A background service keeps the view up to date after the base table changes, so you never refresh it yourself. Results are always current. If a query arrives before maintenance has caught up, Snowflake either updates the view first or combines the up-to-date parts of the view with newer data from the base table. You also do not have to name the view in your SQL. The optimizer can rewrite queries against the base table so they use the materialized view.
How do you choose between a materialized view and a regular view? Use a materialized view when all three are true: the results change rarely, they are used much more often than they change, and the query uses a lot of resources. Use a regular view when any one of those is false. Storage cost also counts: a result that is rarely read may not be worth storing.
Checkpoint 5 of 7· Check yourself
A view summarises a table that is reloaded completely every few minutes. Few people query the view, and the query behind it is cheap. Which should you create?
Any one of these conditions points to a regular view: results that change often, results rarely used compared with how often they change, or a cheap query. All three apply here.
“Create a regular view when any of the following are true: The results of the view change often.”Source: docs.snowflake.com
Checkpoint 6 of 7· Exam question
After enabling the query acceleration service on a warehouse, a team notices serverless acceleration credits are consumed faster than expected during a monthly reporting spike. They want to cap how much serverless compute any single query can borrow without disabling the feature entirely. Which setting should they change?
Correct answer: C — Lower the warehouse's QUERY_ACCELERATION_MAX_SCALE_FACTOR so the amount of serverless compute any one query can lease is bounded.
- A. This is incorrect because warehouse size and the query acceleration scale factor are independent settings; shrinking the base warehouse does not place a bound on how much serverless compute acceleration can lease.
- B. This is incorrect because automatic clustering credits and query acceleration credits are billed and tracked separately, so disabling clustering would not directly cap acceleration usage.
- C. This is correct because QUERY_ACCELERATION_MAX_SCALE_FACTOR is the parameter that bounds the serverless compute a single query can borrow for acceleration, letting the team keep the feature enabled while controlling cost.
- D. This is incorrect because a resource monitor suspends the warehouse only after a credit threshold is already crossed for the account or warehouse, which reacts to overspend rather than proactively bounding per-query acceleration.
Sources2
4.Materialized views compared with cached results and tables
Cached query results also store results for reuse, so it helps to see where materialized views differ. A cached result is the fastest form of reuse and the least flexible. It is only used when the same query runs again on unchanged data. A materialized view is more flexible but usually slower than a cached result. When data changes, it can still use its stored result for the rows that did not change. Unlike regular views, materialized views can be clustered, and they cost both storage and maintenance credits.
| Object | Performance benefit | Supports clustering | Uses storage | Uses credits for maintenance |
|---|---|---|---|---|
| Regular table | No | Yes | Yes | No |
| Regular view | No | No | No | No |
| Cached query result | Yes, if data has not changed and only deterministic functions are used | No | No | No |
| Materialized view | Yes | Yes | Yes | Yes |
Checkpoint 7 of 7· Check yourself
In Snowflake's comparison, which statement about materialized views compared with cached query results is correct?
A cached result is only reused for an identical query on unchanged data. A materialized view keeps working as the data changes, at the price of storage and maintenance.
“Materialized views are more flexible than, but typically slower than, cached results.”Source: docs.snowflake.com
Sources2
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.A unique or very high-cardinality column, such as a nanosecond timestamp, makes the best clustering key because it is the most selective.Why is that wrong?
Very high cardinality makes clustering expensive to maintain and is usually a poor choice for a key. Use an order-preserving expression on the column instead.
Covered in Choosing the key columns: filter use and cardinality
2.Queries only benefit from a materialized view if they select from the view by name.Why is that wrong?
The optimizer can rewrite queries against the base table or regular views so they use the materialized view automatically.
Covered in Materialized views: storing a result that is queried again and again
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Clustering keys are not intended for all tables due to the costs of initially clustering the data and maintaining the clustering.”
↩︎ When a clustering key pays for itself“Improved scan efficiency in queries by skipping data that does not match filtering predicates.”
↩︎ When a clustering key pays for itself“If you want to cluster a table that experiences a lot of DML, then consider grouping DML statements in large, infrequent batches.”
↩︎ When a clustering key pays for itself“For most tables, Snowflake recommends a maximum of 3 or 4 columns (or expressions) per key.”
↩︎ Choosing the key columns: filter use and cardinality“The expression should preserve the original ordering of the column so that the minimum and maximum values in each partition still enable pruning.”
↩︎ Choosing the key columns: filter use and cardinality“a column that contains nanosecond timestamp values would not make a good clustering key.”
↩︎ Exam trap 1“clustering is generally most cost-effective for tables that are queried frequently and do not change frequently.”
↩︎ Prediction“The table contains a large number of micro-partitions. Typically, this means that the table contains multiple terabytes (TB) of data.”
↩︎ Checkpoint“A column with very low cardinality might yield only minimal pruning, such as a column named IS_NEW_CUSTOMER that contains only Boolean values.”
↩︎ Checkpoint - 2.
“materialized views can speed up expensive aggregation, projection, and selection operations, especially those that run frequently and that run on large data sets.”
↩︎ Materialized views: storing a result that is queried again and again“Data accessed through materialized views is always current, regardless of the amount of DML that has been performed on the base table.”
↩︎ Materialized views: storing a result that is queried again and again“Used only if data has not changed and if query only uses deterministic functions (e.g. not CURRENT_DATE).”
↩︎ Materialized views compared with cached results and tables“Storage and maintenance requirements typically result in increased costs.”
↩︎ Materialized views compared with cached results and tables“The query optimizer can automatically rewrite queries against the base table or regular views to use the materialized view instead.”
↩︎ Exam trap 2“Create a regular view when any of the following are true: The results of the view change often.”
↩︎ Checkpoint“Materialized views are more flexible than, but typically slower than, cached results.”
↩︎ Checkpoint