11.3 Warehouse Types & Query Acceleration Service

11.3 Warehouse Types & Query Acceleration Service

As enterprise analytical architectures evolve beyond standard SQL reporting to incorporate machine learning pipelines, deep Snowpark transformations, and petabyte-scale ad-hoc exploration, virtual warehouse compute must adapt to diverse resource profiles. Snowflake provides specialized compute solutions: Standard Warehouses, Snowpark-Optimized Warehouses, and the serverless Query Acceleration Service (QAS). For the SnowPro Advanced: Architect exam, you must evaluate the memory, cache, and cost trade-offs among these compute models and contrast them against Snowflake's native serverless features.

Standard vs. Snowpark-Optimized Warehouses

Snowflake offers two warehouse types: STANDARD and SNOWPARK-OPTIMIZED.

1. Standard Virtual Warehouses

Standard warehouses represent the general-purpose compute workhorse of Snowflake. They provide a balanced ratio of CPU compute cores, physical memory (RAM), and local attached NVMe SSD storage.

• Standard warehouses are ideal for SQL ELT pipelines, operational reporting, BI dashboards, relational joins, and standard analytical queries.
• Sizing ranges from X-Small (1 credit/hour) to 6X-Large (512 credits/hour).

2. Snowpark-Optimized Warehouses

Snowpark-Optimized warehouses let you configure memory and CPU architecture for memory-intensive work:

• Default: RESOURCE_CONSTRAINT = MEMORY_16X — 16x the memory per node of a standard warehouse — and a default size of MEDIUM.
• Other options: MEMORY_1X (up to 16 GB, minimum size XSMALL) and MEMORY_64X (up to 1 TB, minimum size LARGE), plus _x86 variants when a specific CPU architecture is required. X5LARGE and X6LARGE are supported only with MEMORY_16X.
• Pricing: billed at higher per-hour credit rates than standard warehouses of the same size (see the Snowflake Service Consumption Table), so justify them with memory-bound workloads.

-- Create a Snowpark-Optimized warehouse for machine learning model training
CREATE OR REPLACE WAREHOUSE ml_training_wh
  WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED'
  WAREHOUSE_SIZE = 'LARGE'
  RESOURCE_CONSTRAINT = 'MEMORY_16X'      -- default memory profile for Snowpark-optimized
  AUTO_SUSPEND = 120                      -- Suspend quickly after training completes
  AUTO_RESUME = TRUE
  COMMENT = 'Dedicated high-memory warehouse for Snowpark ML model fitting and feature engineering';

Architectural Comparison Matrix

Sizing, Memory Multipliers & Spilling Elimination

A common architectural pitfall is misidentifying the root cause of query failure or excessive execution duration. When running Python stored procedures, Snowpark DataFrame pipelines, or massive data transformations, standard warehouses may fail with Out of Memory (OOM) errors or suffer from crippling remote disk spilling.

When to Transition to Snowpark-Optimized Warehouses

1. Severe Remote Disk Spilling: When an operation spills hundreds of gigabytes or terabytes to remote cloud object storage, and scaling a Standard warehouse up to 2X-Large or 3X-Large still exhibits spilling because per-node RAM is insufficient for the memory footprint of the algorithms.
2. In-Memory Machine Learning: Training algorithms (such as XGBoost, Scikit-learn, LightGBM, or PyTorch via Snowpark ML) that require full datasets or large matrix representations to reside simultaneously in memory.
3. Heavy Python / Java / Scala UDFs: Custom User-Defined Functions running embedded Python runtimes that construct large internal memory structures (e.g., text parsing, image processing, natural language tokenization).
4. PySpark Workload Migrations: Migrating complex Spark code to Snowpark where operations like crossJoin or large window aggregations demand extreme per-node memory.

Architect Cost Governance Rule: Snowpark-Optimized warehouses cost more per hour than standard warehouses of the same size. If a query does not exhaust memory on a standard warehouse, moving it to a Snowpark-Optimized warehouse adds cost without a meaningful gain. Reserve them for confirmed memory-bound pipelines.

Query Acceleration Service (QAS) Architecture & Mechanics

In analytical workloads, warehouses frequently face the "outlier query" challenge: a warehouse sized appropriately for 95% of routine queries suddenly encounters an infrequent, massive query that scans billions of rows across years of historical data. Sizing the entire warehouse up to 2X-Large or 4X-Large to accommodate that single query wastes enormous compute credits during the remaining 95% of routine execution.

Snowflake solves this dilemma with the Query Acceleration Service (QAS), a serverless compute bursting capability.

How QAS Works

When a query is submitted to a warehouse with QAS enabled: QAS is an Enterprise Edition feature that targets two query patterns: large scans with selective filters, and statements that insert, copy, update, or delete large amounts of data.

1. Snowflake analyzes the query plan and identifies portions (such as scanning and filtering) that can be offloaded.
2. If the query qualifies, Snowflake transparently offloads the intensive scanning and filtering work to a dynamically provisioned, shared serverless compute pool.
3. The serverless QAS workers scan micro-partitions in parallel, apply filters, compute intermediate aggregations, and return the filtered subsets to the virtual warehouse.
4. The virtual warehouse completes any final joins, sorting, and client result formatting.

Enabling and Governing QAS

QAS is enabled at the virtual warehouse level and governed by the QUERY_ACCELERATION_MAX_SCALE_FACTOR parameter:

-- Enable Query Acceleration Service on an analytics warehouse
ALTER WAREHOUSE analytics_wh SET
  ENABLE_QUERY_ACCELERATION = TRUE
  QUERY_ACCELERATION_MAX_SCALE_FACTOR = 8;    -- Upper burst ceiling multiplier (default: 8)

• ENABLE_QUERY_ACCELERATION = TRUE | FALSE: Turns the service on or off for the warehouse.
• QUERY_ACCELERATION_MAX_SCALE_FACTOR = <num>: An upper bound (0–100, default 8) on the resources QAS may lease, as a multiple of the warehouse size. A factor of 8 on a Medium warehouse (4 credits/hr) allows up to $8 \times 4 =$ 32 credits/hour of acceleration. A factor of 0 removes the upper bound, letting QAS lease as many resources as necessary and available — the opposite of disabling it.

Identifying Eligible Queries: SYSTEM$ESTIMATE_QUERY_ACCELERATION

Before enabling QAS, evaluate past queries with the system function (or scan many at once with the ACCOUNT_USAGE.QUERY_ACCELERATION_ELIGIBLE view):

-- Evaluate whether a completed query would benefit from QAS
SELECT PARSE_JSON(SYSTEM$ESTIMATE_QUERY_ACCELERATION('01b63c78-0001-2a3b-0000-000012345678'));

For an eligible query, the result looks like this:

{
  "estimatedQueryTimes": { "1": 171, "2": 152, "4": 133, "8": 120, "10": 115 },
  "ineligibleReason": null,
  "originalQueryTime": 300.291,
  "status": "eligible",
  "upperLimitScaleFactor": 10
}

What Workloads Qualify for QAS?

Serverless Compute Models vs. Virtual Warehouse Compute

A critical architectural distinction in Snowflake is the boundary between Customer-Managed Virtual Warehouse Compute and Snowflake-Managed Serverless Compute.

Serverless Compute Architecture

In the serverless model, Snowflake provisions, manages, auto-tunes, and scales cloud compute resources behind the scenes. Customers do not manage T-shirt sizes, auto-suspend timers, or cluster scaling policies for serverless workloads. Snowflake bills serverless consumption based purely on the exact resources leased to execute background maintenance or ingestion tasks.

Catalog of Snowflake Serverless Features

1. Snowpipe: Ingests micro-batches of files triggered by cloud event notifications (S3, Azure Blob, GCS) using serverless compute without requiring an active virtual warehouse.
2. Automatic Clustering: Re-organizes table micro-partitions in the background to maintain optimal clustering depth along defined clustering keys.
3. Search Optimization Service (SOS): Builds and maintains persistent search access paths (equality, substring, geospatial) to accelerate selective point-lookup queries.
4. Materialized Views Maintenance: Continuously updates materialized views in the background as source table micro-partitions undergo DML changes.
5. Serverless Tasks: Executes scheduled task graphs where Snowflake dynamically determines the required compute capacity, charging only for the exact seconds of task execution without idle overhead.
6. Query Acceleration Service (QAS): Bursts serverless compute nodes to parallelize table scans for qualifying warehouse queries.

Telemetry & Billing Demarcation

Because serverless compute does not execute on virtual warehouses, it is never billed to virtual warehouses and does not appear in SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY.

Instead, architects must query dedicated Account Usage views to audit serverless credit consumption:

-- Consolidated serverless compute consumption audit across all services
SELECT 
    service_type,
    DATE_TRUNC('day', start_time) AS usage_date,
    SUM(credits_used) AS total_credits
FROM snowflake.account_usage.metering_history
WHERE service_type IN (
    'PIPE', 
    'AUTO_CLUSTERING', 
    'SEARCH_OPTIMIZATION', 
    'MATERIALIZED_VIEW',
    'SERVERLESS_TASK',
    'QUERY_ACCELERATION'
)
  AND start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY service_type, usage_date
ORDER BY usage_date DESC, total_credits DESC;

Architect Exam Warning: The Cloud Services 10% adjustment (where Snowflake waives daily Cloud Services credits up to 10% of total daily virtual warehouse compute) applies only to virtual warehouse compute. Serverless compute usage does not generate or receive the 10% Cloud Services credit waiver.