Snowflake Caching, Materialized Views, Search Optimization and Query Acceleration

1.Result cache: skipping execution entirely

The result cache sits at the top of the stack. Snowflake persists every query result. If the same query runs again and nothing it depends on has changed, Snowflake returns the stored result without executing the query. A persisted result expires after 24 hours. Each reuse resets that 24-hour period, up to a maximum of 31 days after the query first ran. Result reuse is on by default. You can turn it off at the account, user or session level with the USE_CACHED_RESULT parameter.

Reuse depends on several conditions together. The query text must match exactly. The query must not use non-reusable functions such as UUID_STRING, RANDOM or RANDSTR, external functions, or hybrid tables. The underlying data and micro-partitions must not have changed, including through reclustering. The role must have the required privileges. Even when every condition is met, reuse is still not guaranteed. The materialized views documentation adds that a cached query result is used only if the query uses deterministic functions only, and gives CURRENT_DATE as an example of a function that does not qualify. You can also post-process a cached result with RESULT_SCAN.

Sources12

2.Warehouse (local disk) cache and suspend/resume

When a query has to execute, the next layer is the warehouse cache. A running warehouse caches table data, and later queries on the same warehouse can read from that cache instead of from the tables. The cache only exists while the warehouse runs. It is dropped when the warehouse suspends, so after a resume the first queries read from the tables again. That makes auto-suspend a performance setting as well as a cost setting. To see how much data each warehouse actually reads 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;

Keeping a warehouse running has a cost, because an idle warehouse still uses credits. If a query arrives only every 30 minutes, a 10-minute auto-suspend pays for idle time and still loses the cache before the next query. AUTO_SUSPEND is specified in seconds, not minutes.

ALTER WAREHOUSE my_wh SET  ?  = 600;

Sources3

3.Metadata-based results

The cheapest query does not touch table data at all. Snowflake stores metadata about all rows in every micro-partition, including the range of values for each column, the number of distinct values, and other properties used for optimization and efficient query processing. Some queries can be answered from that metadata alone. The Query Profile shows these as a Metadata-based Result, and they do not run on a virtual warehouse. SELECT COUNT(*) FROM a table and SELECT CURRENT_DATABASE() are examples. Some DML works the same way: deleting all rows from a table is a metadata-only operation. The same metadata also drives pruning: it lets Snowflake skip micro-partitions that a filter cannot match, and the EXPLAIN plan reports the partitions left after compile-time pruning. The documentation available here does not describe how long this metadata is retained.

Sources45

4.Materialized views, search optimization and clustering

When caches cannot help, you can change how the data is stored. Snowflake offers four options, and each fits a different query pattern.

Materialized views can do more than precompute results. You can use them to give the same source table a different clustering key, or to store flattened JSON or VARIANT data so it only has to be flattened once. They only help queries that stay within the rows and columns the view contains. A materialized view is created with CREATE MATERIALIZED VIEW ... AS followed by a query, for example a view over an external table (see the next section). Snowflake maintains it automatically in the background as the base table changes. Snowflake recommends adding search optimization for point lookup queries, which filter on a single column to retrieve one or a few rows, such as WHERE some_id = some_UUID. DESCRIBE SEARCH OPTIMIZATION shows the search optimization configuration of a table and its columns. The excerpts available here do not include the statement that enables search optimization. Search optimization and query acceleration can apply to the same query. Search optimization first prunes the micro-partitions the query does not need. Query acceleration then offloads eligible parts of the remaining work.

Sources67

5.External tables and performance

External tables query files that stay in a stage, so performance depends on how those files are laid out. Snowflake recommends partitioning external tables. That requires the underlying files to sit in logical paths that include dimensions such as date, time or country. The partitions are stored in the external table metadata, so a query can read only the relevant slice of the data instead of the whole data set. File size also matters for parallel scanning. Snowflake recommends 256-512 MB for Parquet files, 16-256 MB for Parquet row groups, and 16-256 MB for other formats. For the best performance on large data files, create materialized views over the external tables and query those.

Partition columns are defined when the external table is created, with CREATE EXTERNAL TABLE ... PARTITION BY. Each partition column is an expression that parses the path or filename information in the METADATA$FILENAME pseudocolumn. This syntax adds partitions automatically:

CREATE EXTERNAL TABLE
  <table_name>
     ( <part_col_name> <col_type> AS <part_expr> )
     [ , ... ]
  [ PARTITION BY ( <part_col_name> [, <part_col_name> ... ] ) ]
  ..

Snowflake computes and adds the partitions when the external table metadata is refreshed. By default the metadata is refreshed automatically when the table is created. The owner can also configure automatic refresh when new or updated files arrive in the stage, or refresh manually with ALTER EXTERNAL TABLE ... REFRESH. Alternatively, you can add partitions manually by adding PARTITION_TYPE = USER_SPECIFIED, and then running ALTER EXTERNAL TABLE ... ADD PARTITION. The method cannot be changed after creation, and automatic refresh is not supported for user-defined partitions. When you query the table, filter on the partition columns in a WHERE clause so that Snowflake scans only the matching partitions. To speed up queries further, create a materialized view over the external table:

CREATE MATERIALIZED VIEW et1_mv
  AS
  SELECT col2 FROM et1;

Sources8

6.Query acceleration service (QAS)

QAS reduces the impact of outlier queries by sending parts of their work to shared compute that the service provides. It helps two patterns: large scans with selective filters or aggregation, and large inserts, copies, updates or deletes. You enable it per warehouse. Gen2 standard warehouses and multi-cluster warehouses have it enabled by default.

CREATE WAREHOUSE my_wh WITH ENABLE_QUERY_ACCELERATION = true;

CREATE WAREHOUSE my_other_wh WITH ENABLE_QUERY_ACCELERATION = false;
ALTER WAREHOUSE my_other_wh SET ENABLE_QUERY_ACCELERATION = true;

Some queries are not eligible. Common reasons are too few partitions to scan, filters that are not selective enough, a high-cardinality GROUP BY, or nondeterministic functions such as SEQ or RANDOM. To check a query that has already run, call SYSTEM$ESTIMATE_QUERY_ACCELERATION. To find candidates across the account, query the QUERY_ACCELERATION_ELIGIBLE view. An ineligible query returns its reason:

{
  "estimatedQueryTimes": {},
  "ineligibleReason": "NO_LARGE_ENOUGH_SCAN",
  "originalQueryTime": 20.291,
  "queryUUID": "cf23522b-3b91-cf14-9fe0-988a292a4bfa",
  "status": "ineligible",
  "upperLimitScaleFactor": 0
}

QUERY_ACCELERATION_MAX_SCALE_FACTOR controls cost. It caps the compute QAS can lease as a multiple of the warehouse's size. The default is 8 when you set ENABLE_QUERY_ACCELERATION = TRUE explicitly, and 2 when QAS is enabled implicitly on a Gen2 warehouse. Setting it to 0 removes the cap. QAS is billed by the second, only while it is in use, and separately from warehouse credits.

Sources9

Exam traps

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

1. 1.A query that is logically identical to an earlier one always reuses its cached result.Why is that wrong?

   Reuse needs an exact text match, unchanged data and micro-partitions, and suitable privileges. Even then it is not guaranteed.

   Covered in Result cache: skipping execution entirely

2. 2.A suspended warehouse keeps its cached table data, so resuming it brings back warm performance.Why is that wrong?

   Suspending a warehouse drops its cache. When it resumes, queries read from the tables again until the cache is rebuilt.

   Covered in Warehouse (local disk) cache and suspend/resume

3. 3.Setting QUERY_ACCELERATION_MAX_SCALE_FACTOR to 0 turns query acceleration off.Why is that wrong?

   A scale factor of 0 removes the upper limit, so queries can lease as many resources as they need and are available.

   Covered in Query acceleration service (QAS)

Practise it for real

Find a warehouse whose outlier queries could use QAS, enable QAS on it, and confirm the setting

1. 1.As ACCOUNTADMIN, run: SELECT warehouse_name, SUM(eligible_query_acceleration_time) AS total_eligible_time FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_ELIGIBLE WHERE start_time > DATEADD('day', -7, CURRENT_TIMESTAMP()) GROUP BY warehouse_name ORDER BY total_eligible_time DESC;

   Why: This ranks warehouses by how much query time QAS could have accelerated in the past week.

   You should see: A list of warehouses, with the best QAS candidate first. It may be empty if no query was eligible.

2. 2.Pick a query_id on that warehouse from QUERY_ACCELERATION_ELIGIBLE and run SELECT PARSE_JSON(SYSTEM$ESTIMATE_QUERY_ACCELERATION('<query_id>'));

   Why: This confirms that the query is eligible and shows estimated run times at different scale factors.

   You should see: JSON with status "eligible", estimatedQueryTimes per scale factor, and an upperLimitScaleFactor.

3. 3.Run ALTER WAREHOUSE <warehouse_name> SET ENABLE_QUERY_ACCELERATION = true;

   Why: QAS is enabled per warehouse.

   You should see: The statement completes successfully.

4. 4.Run SHOW WAREHOUSES LIKE '<warehouse_name>' and inspect enable_query_acceleration and query_acceleration_max_scale_factor.

   Why: This verifies the setting and shows the scale factor that caps QAS cost.

   You should see: enable_query_acceleration is true, with a scale factor of 8 by default after an explicit enable.