Snowflake Caching, Search Optimization and Query Acceleration

1.Persisted query results: the result cache

Snowflake keeps (persists) the result of every query. If the same query runs again and nothing it depends on has changed, Snowflake returns the stored result without running the query at all. A cached result expires 24 hours after it is created. Each reuse resets that 24-hour period, up to 31 days from the first execution, after which the result is purged.

The match has to be exact. Any difference in syntax, such as a table alias or lowercase keywords, prevents reuse. The query also cannot contain non-reusable functions such as UUID_STRING, RANDOM or RANDSTR, external functions, or hybrid tables. The underlying data must be unchanged, and so must the table's micro-partitions; reclustering, for example, invalidates the cache. The role must have privileges on every table in the query. For a SHOW query, the role must be the same role that produced the result. Even when all these conditions hold, reuse is not guaranteed. Reuse is on by default and is controlled by the USE_CACHED_RESULT parameter at the account, user and session level. You can also query a previous result directly with RESULT_SCAN.

SELECT DISTINCT(severity) FROM weather_events; SELECT DISTINCT(severity) FROM weather_events; SELECT DISTINCT(severity) FROM weather_events we; select distinct(severity) from weather_events;

Sources1

2.The warehouse cache and micro-partition metadata

The result cache stores finished answers. The warehouse cache stores raw table data. While a warehouse is running, it caches the table data its queries read, and later queries on the same warehouse can read from that cache instead of from the tables. That suits frequent, similar queries. The query below shows, for each warehouse, what share of scanned bytes came from cache. If that share is low for a workload that should benefit, tuning the cache may help.

The auto-suspend setting directly affects query performance, because the cache is dropped when the warehouse is suspended. Snowflake's guidelines: for tasks, suspend immediately; for DevOps, DataOps and Data Science use cases, about 5 minutes, because the cache matters less for ad-hoc and unique queries; for query warehouses such as BI and SELECT use cases, at least 10 minutes, to keep the cache for users. The trade-off is cost: a running warehouse consumes credits even when it is not processing queries. If a warehouse runs a query only every 30 minutes, a 10-minute auto-suspend burns idle credits without any cache benefit, because the cache is dropped before the next query.

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;

The third layer is metadata. Snowflake records each micro-partition's value ranges and distinct counts. Pruning uses this metadata, and so do DML operations. Some operations, such as deleting every row in a table, touch only metadata. The COUNT documentation adds that Snowflake maintains statistics on tables and views, which lets simple COUNT queries run faster. That optimization works best on tables and views without a row access policy, because with a policy Snowflake must scan each row to check whether the user may see it. The sources for this lesson do not list other query types that are answered from metadata alone.

Sources234

3.Search Optimization Service for point lookups

Clustering helps range filters on a few columns. Point lookups are a different pattern: queries expected to return only a few rows, such as fetching one customer ID from a huge table. Clustering on a unique key often costs more than it saves unless point lookups are the table's main use. The Search Optimization Service is designed for this pattern. It can speed up point lookups that use equality predicates (column = constant) or IN lists, but only for columns where search optimization is enabled. Snowflake's best practice is to enable it only for specific columns, using the ON EQUALITY clause. Leaving out the clause enables EQUALITY for every column of a supported data type, except semi-structured and GEOGRAPHY columns.

How it works and what it costs: the service builds and maintains a persistent search access path that tracks which values might be found in each micro-partition, so some micro-partitions can be skipped. A background maintenance service builds it without blocking the table and keeps it updated as data changes. Queries are not accelerated until the path is fully built. You need no virtual warehouse for this, but maintenance has a cost for storage and compute.

Compared with clustering: Automatic Clustering is the broadest option, best for range queries or inequality filters, and there can be only one cluster key per table. Search optimization is usually faster for point lookups. If a query returns more than a few records, consider Automatic Clustering instead.

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(mycol);

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON  ? (mycol);

Sources5678

4.Query Acceleration Service for outlier scans

Some queries are outliers that need far more resources than the rest of a warehouse's workload. The Query Acceleration Service (QAS) offloads parts of that work to shared compute, so the scanning and filtering run in parallel. It helps two patterns: large scans with selective filters or aggregations, and statements that insert, copy, update or delete large amounts of data. To enable it, set ENABLE_QUERY_ACCELERATION = TRUE on a warehouse. Gen2 standard warehouses and multi-cluster warehouses have QAS on 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;

To find candidates, query the QUERY_ACCELERATION_ELIGIBLE view, or pass a query ID to SYSTEM$ESTIMATE_QUERY_ACCELERATION. For an eligible query, the function returns estimated run times at different scale factors. For an ineligible one, it returns the reason. Common reasons are a scan with too few partitions, filters that are not selective enough, a GROUP BY whose cardinality is too high, a LIMIT clause that prevents acceleration, and nondeterministic functions such as SEQ or RANDOM.

{
  "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 a warehouse can lease as a multiple of the warehouse's own size. For example, a Medium warehouse costs 4 credits per hour, so a scale factor of 5 allows up to 20 extra credits per hour. QAS is billed per second, only while it is in use, and separately from the warehouse. The default is 8 when you enable QAS explicitly and 2 when a Gen2 or multi-cluster warehouse enables it automatically. A value of 0 removes the cap entirely.

Sources9

Exam traps

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

1. 1.A query that returns the same rows as an earlier one will reuse its cached result, even if it adds a table alias or uses lowercase keywords.Why is that wrong?

   The new query must match the earlier one exactly. A different alias or different letter case is enough to prevent reuse.

   Covered in Persisted query results: the result cache

2. 2.Setting the QAS scale factor to 0 turns off acceleration spend.Why is that wrong?

   A scale factor of 0 removes the upper limit, so queries can lease as many resources as are available. QAS credits are billed separately from the warehouse.

   Covered in Query Acceleration Service for outlier scans