Snowflake Caching, Clustering and Search Optimization: When to Use Each and What They Cost

1.Cache layers, cache expiration and auto-suspend

The sources describe two cache layers. The first is the warehouse cache, which is a cache of table data held by a running warehouse. Later queries on the same warehouse can read from it instead of reading the tables, and the Query Profile reports this as the percentage of data scanned from the local disk cache. The second is persisted query results. When a query repeats and nothing has changed, Snowflake skips execution and returns the earlier result. That persisted result expires after 24 hours. Each reuse resets the 24-hour period, up to a maximum of 31 days from when the query first ran. (Snowflake's metadata cache isn't covered by the sources for this lesson. Neither is auto-resume beyond its relationship to suspension, so check the official docs for those details.)

Result reuse has conditions. Typically a result is reused only if all of these hold:

- The new query matches the previous one exactly. A different table alias or lowercase versus uppercase keywords is enough to prevent reuse. - The query has no non-reusable functions such as UUID_STRING, RANDOM or RANDSTR. - The query has no external functions. - The query doesn't select from hybrid tables. - The table data hasn't changed. - The earlier persisted result is still available. - The role has the required privileges.

Even when all conditions are met, reuse is not guaranteed. Result reuse is on by default and is controlled by the USE_CACHED_RESULT parameter.

The warehouse cache is lost when the warehouse suspends, which makes auto-suspend a performance setting as well as a cost setting. A warehouse that suspends between frequent, similar queries throws away the cache that would have sped them up. On the other hand, a running warehouse consumes credits even when it is idle. Snowflake's guidelines follow from that trade-off:

Fit the setting to the actual query rhythm. If queries arrive only every 30 minutes, a 10-minute timeout pays for idle time and still loses the cache before the next query. To check whether a warehouse benefits from its cache at all, measure the share of scanned bytes that came from cache:

SELECT warehouse_name
  ,COUNT(*) AS query_count
  ,SUM(bytes_scanned) AS bytes_scanned
  ,SUM(bytes_scanned*percentage_scanned_from_cache) AS bytes_scanned_from_cache
  ,SUM(bytes_scanned*percentage_scanned_from_cache) / SUM(bytes_scanned) AS percent_scanned_from_cache
FROM snowflake.account_usage.query_history
WHERE start_time >= dateadd(month,-1,current_timestamp())
  AND bytes_scanned > 0
GROUP BY 1
ORDER BY 5;

ALTER WAREHOUSE my_wh SET AUTO_SUSPEND =  ? ;

Sources123

2.Natural clustering, clustering keys and automatic clustering

Caching speeds up repeated reads. Clustering reduces how much data each read has to scan. Snowflake generally produces well-clustered data on its own, which is natural clustering. But on very large tables, sustained DML can degrade it over time. You could re-sort the rows and reinsert them by hand, but that is cumbersome and expensive. Instead, you can define a clustering key: one or more columns or expressions that co-locate similar rows in the same micro-partitions. That lets queries skip micro-partitions that don't match their filters. You can define the key in CREATE TABLE or with ALTER TABLE, and you can change or drop it later. Clustering keys can't be defined on hybrid tables.

After the key is defined, Snowflake keeps the table clustered automatically (automatic clustering). It reclusters only when the table will benefit. Those reclustering compute resources consume credits, so clustering isn't meant for every table. Signs that a table needs a key are queries that are slower than expected or have degraded over time, and a large clustering depth. Clustering pays off best on multi-terabyte tables with selective or sorting queries that mostly use the same columns. To keep costs down, choose tables that are queried often and changed rarely, and group the DML into large, infrequent batches.

Key selection rules: use at most 3–4 columns or expressions. Start with the columns most used in selective filters, such as a date column on a fact table, and then consider join columns. Cardinality matters in both directions. A Boolean column such as IS_NEW_CUSTOMER prunes almost nothing. A nanosecond timestamp is too unique to group rows well and is expensive to maintain. For a very high-cardinality column, cluster on an expression that preserves the column's ordering, for example a truncation.

Sources4

3.Search optimization service for selective lookups

A table can have only one clustering key, which leaves highly selective lookups on other columns unserved. The search optimization service (SOS) covers that case. It targets point lookups, meaning queries that return one or a few distinct rows. It also accelerates substring and regex searches, SEARCH and SEARCH_IP text and IP-address searches, predicates on semi-structured and structured columns, and selected geospatial functions. It works by building a persistent search access path that records which micro-partitions might contain each value, so the remaining micro-partitions can be skipped.

A background maintenance service builds the access path and keeps it current as data changes. You don't create a warehouse for it, but its storage and compute are billed. Queries aren't accelerated until the path is fully built. Before you benchmark, check the search_optimization_progress column of SHOW TABLES. Predicate form matters too. A cast on the constant still qualifies, but a cast on the column, such as CAST(c2 AS NUMBER) = 2, doesn't.

ALTER TABLE test_table ADD SEARCH OPTIMIZATION;

Sources5

4.Choosing an optimization and its cost impact

Snowflake offers four query optimizations: search optimization, query acceleration, materialized views, and clustering. Pick by query shape. Search optimization, materialized views and clustering each carry storage and compute cost in the docs' cost table. Query acceleration is billed differently. Its credits are billed by the second, only while the service is in use, and separately from warehouse usage. The QUERY_ACCELERATION_MAX_SCALE_FACTOR property caps how much compute a warehouse can lease for it. For clustering, the compute used for reclustering consumes credits, which is why keys suit tables that are queried often and changed rarely. The options can also be combined. Search optimization and query acceleration are complementary: SOS first prunes micro-partitions, and then QAS offloads part of the remaining work. Materialized views help only for the rows and columns they contain. They can, however, give the same source table a different clustering key, or store flattened VARIANT data so it is flattened only once.

Sources674

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.ALTER WAREHOUSE ... SET AUTO_SUSPEND = 10 means ten minutes.Why is that wrong?

   In SQL the auto-suspend limit is given in seconds. Ten minutes is 600.

   Covered in Cache layers, cache expiration and auto-suspend

2. 2.A clustering key is a good default for any large table and costs nothing to maintain.Why is that wrong?

   Initial clustering and ongoing maintenance consume credits. Clustering pays off only when the gain in query performance outweighs that cost.

   Covered in Natural clustering, clustering keys and automatic clustering

3. 3.Queries speed up as soon as ADD SEARCH OPTIMIZATION completes.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

Practise it for real

Enable search optimization on a small table and confirm which predicates can use it

1. 1.Create test_table with the documented CREATE OR REPLACE TABLE test_table (id INT, c1 INT, c2 STRING, c3 DATE) AS SELECT * FROM VALUES ... statement

   Why: Gives you a table whose columns cover the predicate types in the docs

   You should see: A five-row table, with one row of NULLs

2. 2.Run ALTER TABLE test_table ADD SEARCH OPTIMIZATION;

   Why: Adds the property that starts the maintenance service building the search access path

   You should see: The statement succeeds right away while the path builds in the background

3. 3.Run SHOW TABLES and inspect search_optimization_progress for test_table

   Why: Queries aren't accelerated until the path is fully built

   You should see: Progress shows the table as fully optimized before you measure anything

4. 4.Run SELECT * FROM test_table WHERE c2 = 2; and then SELECT * FROM test_table WHERE CAST(c2 AS NUMBER) = 2;

   Why: Contrasts a cast on the constant with a cast on the column

   You should see: Only the first form is eligible for search optimization