Performance Optimization · Topic 3 of 9

Query Acceleration Service and Snowpark-Optimized Warehouses

Decide when to use query acceleration or a Snowpark-optimized warehouse: eligibility, scale factor, estimate functions, memory tiers and cost.

  • 4 min
In a nutshell
  • QAS: Enterprise or higher, serverless, ENABLE_QUERY_ACCELERATION and QUERY_ACCELERATION_MAX_SCALE_FACTOR (default 8, 0 is unlimited).
  • Check eligibility with SYSTEM$ESTIMATE_QUERY_ACCELERATION or the QUERY_ACCELERATION_ELIGIBLE view.
  • QAS fits large selective scans among mixed workloads, not small scans, unselective filters or non deterministic queries.

What it is

Two features cover cases that a plain resize handles badly. The query acceleration service (QAS) lends extra serverless compute to a warehouse for parts of a large query, so one big scan does not force you to size the whole warehouse up. A Snowpark-optimized warehouse is a warehouse type with much more memory per node, built for memory hungry code such as ML training.

Large selective scanon a normal warehouseoffloadQuery accelerationserverless computemerge resultsWarehousefinishes the query

How it works

Query acceleration service

QAS offloads some of the scan and filter work of eligible queries to serverless compute. It suits workloads with a mix of short and long queries, where an occasional large scan would otherwise need a bigger warehouse for everyone.

ALTER WAREHOUSE reporting_wh SET
  ENABLE_QUERY_ACCELERATION = TRUE
  QUERY_ACCELERATION_MAX_SCALE_FACTOR = 8;
SettingMeaning
ENABLE_QUERY_ACCELERATIONTurns QAS on for the warehouse
QUERY_ACCELERATION_MAX_SCALE_FACTORUpper limit on serverless compute, as a multiple of the warehouse size. Default 8; 0 means no limit

QAS requires Enterprise Edition or higher. Its serverless compute is billed in addition to the warehouse, so the scale factor is a cost ceiling.

Good candidates are queries that scan a lot of data and then filter or aggregate down to a small result, ad hoc analytics with unpredictable size, and queries with large scans where the warehouse is otherwise sized fine.

Poor candidates are queries that are already fast, queries limited by a long sort or a join rather than a scan, queries without enough partitions or without selective enough filters, some queries with a LIMIT clause, and queries that use non deterministic functions such as SEQ or RANDOM.

Finding eligible queries

  • SYSTEM$ESTIMATE_QUERY_ACCELERATION('<query_id>') returns a JSON estimate of whether the query would be accelerated and how much faster at different scale factors.
  • The QUERY_ACCELERATION_ELIGIBLE view in ACCOUNT_USAGE lists recent queries that could have benefited, with an estimated time and the scale factor needed.
  • The QUERY_ACCELERATION_BYTES_SCANNED column in query history shows how much the acceleration service scanned for queries that used it.
Slow querybig scancheckSYSTEM$ESTIMATE_QUERY_ACCELERATIONeligible?yesEnable on warehouseset max scale factor
Exam tip

Choose QAS when the problem is a few unpredictable heavy scans on a shared warehouse. Choose a larger warehouse when most queries are heavy, or a multi-cluster warehouse when the problem is concurrency.

Snowpark-optimized warehouses

A Snowpark-optimized warehouse provides 16 times the memory per node of a standard warehouse by default. Create one with WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED'.

CREATE WAREHOUSE ml_wh WITH
  WAREHOUSE_SIZE = 'MEDIUM'
  WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED'
  RESOURCE_CONSTRAINT = 'MEMORY_16X';
RESOURCE_CONSTRAINTMemory per node (up to)Smallest size
MEMORY_1X16 GBX-Small
MEMORY_16X (default)256 GBMedium
MEMORY_64X1 TB, preview and AWS onlyLarge

The x86 variants of each setting choose the x86 CPU architecture. Use these warehouses for ML model training, large UDF or stored procedure workloads with big in memory data, and Snowpark jobs that spill on a standard warehouse. Workloads that do not use Snowpark code generally gain little.

AspectStandard warehouseSnowpark-optimized
Memory per nodeBaseline16 times by default
Best forSQL queries and ETLMemory heavy Snowpark, ML and UDF code
Credit rateStandardHigher for the same size
StartupFastCan take longer to create and resume
Watch out

Do not move ordinary SQL workloads to a Snowpark-optimized warehouse hoping for speed. They pay a higher rate for memory they do not use.

Choosing between the options

SituationBest choice
Rare large scans on a busy shared warehouseQuery acceleration service
Every report is heavyLarger warehouse
Queries wait in a queueMulti-cluster warehouse
Python ML training runs out of memorySnowpark-optimized warehouse
Same selective lookups over a huge tableSearch optimization service or clustering

Common mistakes

Enabling QAS on every warehouse. It adds serverless cost and only helps eligible, scan heavy queries.

Setting the scale factor to 0 to be safe. Zero removes the cap on serverless compute, which can raise cost.

Using QAS to fix concurrency. It speeds up individual queries, not queues.

Choosing Snowpark-optimized for plain SQL. It is for memory bound Snowpark workloads.

What to remember for the exam

  • QAS: Enterprise or higher, serverless, ENABLE_QUERY_ACCELERATION and QUERY_ACCELERATION_MAX_SCALE_FACTOR (default 8, 0 is unlimited).
  • Check eligibility with SYSTEM$ESTIMATE_QUERY_ACCELERATION or the QUERY_ACCELERATION_ELIGIBLE view.
  • QAS fits large selective scans among mixed workloads, not small scans, unselective filters or non deterministic queries.
  • Snowpark-optimized warehouses give 16 times the memory per node by default and use WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED'.
  • RESOURCE_CONSTRAINT chooses the memory tier; MEMORY_16X is the default and needs size Medium or larger.

Check yourself

  • A shared warehouse is fine except for rare huge scans. What feature fits?

    Query acceleration service, which offloads part of those scans to serverless compute without resizing the warehouse.

  • How do you cap the extra cost of QAS?

    Set QUERY_ACCELERATION_MAX_SCALE_FACTOR; the default is 8 and 0 removes the limit.

  • A Python training job spills on a Large standard warehouse. What is the likely fix?

    A Snowpark-optimized warehouse, which provides far more memory per node.

Topic 3 of 9 in Performance Optimization

Test yourself on Performance Optimization

The full Snowflake ARA-C01 question bank has 687 questions, with timed practice exams and an explanation for every answer.

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.