Study guide
11.3 Warehouse Types & Query Acceleration Service
SnowPro Advanced: Architect ARA-C01
Track your progress as you read
Finished sections get a check mark.
1 Introduction: The ARA-C01 Exam
About the SnowPro Advanced: Architect (ARA-C01) Exam
2 Chapter 1: Account Strategy & Organization Architecture
1.1 Multi-Account Strategy & Snowflake Organizations 1.2 Snowflake Parameter Hierarchy & Configuration 1.3 Database and Schema Design Architecture
3 Chapter 2: Identity, Access Control & Storage Integrations
2.1 Role-Based Access Control Architecture & Hierarchy 2.2 Secondary Roles, Object Ownership & Delegated Administration 2.3 Cloud Storage Integrations & IAM Trust Policies 2.4 Identity Federation, SSO, SCIM & Authentication Policies
4 Chapter 3: Governance, Data Privacy, Network Security & Compliance
3.1 Column-Level Masking & Row Access Policies 3.2 Object Tagging, Tag-Based Masking & Audit Governance 3.3 Encryption, Key Management & Tri-Secret Secure 3.4 Aggregation Policies, Projection Policies & External Tokenization 3.5 Network Policies, Network Rules, External Access & Private Connectivity 3.6 Snowflake Editions & Compliance Architecture (PCI DSS, PII & PHI)
5 Chapter 4: Data Modeling in Snowflake
4.1 Dimensional Modeling: Star vs. Snowflake Schemas 4.2 Data Vault 2.0 Architecture in Snowflake 4.3 Relational Constraints & Query Optimizer Integration
6 Chapter 5: Data Sharing & Collaboration Solutions
5.1 Direct Data Sharing Architecture & Security 5.2 Managed Reader Accounts for External Consumers 5.3 Snowflake Marketplace, Data Exchange & Clean Rooms
7 Chapter 6: Multi-Cloud, Multi-Region & Business Continuity
6.1 Database & Account Replication Mechanics 6.2 Disaster Recovery Architecture & Client Redirect 6.3 Cross-Cloud Auto-Fulfillment & Listing Replication 6.4 Data Protection: Time Travel, Fail-safe & Storage Costs
8 Chapter 7: Environments, Lakehouse, DevOps & AI/ML Applications
7.1 Multi-Tier Environment Strategy & Zero-Copy Cloning 7.2 Data Lakehouse Architecture & Apache Iceberg 7.3 DataOps, CI/CD & Snowflake Git Integration 7.4 AI/ML Pipelines & Applications: Cortex AI, ML Functions, Snowpark Container Services, Streamlit & Native Apps
9 Chapter 8: Data Ingestion Architecture
8.1 Bulk Ingestion Architecture: COPY INTO Optimization 8.2 Snowpipe Continuous Automated Ingestion 8.3 Snowpipe Streaming & Real-Time Ingestion 8.4 Data Unloading Architecture & Cloud Export Patterns 8.5 Schema Detection, Schema Evolution & Source-Change Architecture
10 Chapter 9: Continuous Pipelines & Stream Transformation
9.1 Change Data Capture (CDC) with Snowflake Streams 9.2 Task Orchestration, DAGs & Serverless Scheduling 9.3 Declarative Data Pipelines with Dynamic Tables 9.4 Views, Materialized Views & Transformation Table Design
11 Chapter 10: Semi-Structured Data & Ecosystem Tooling
10.1 Semi-Structured Data: VARIANT, OBJECT & ARRAY Processing 10.2 Snowflake Ecosystem Connectors, Drivers & SQL API 10.3 Snowpark, User-Defined Functions & Stored Procedures
12 Chapter 11: Virtual Warehouse Architecture & Concurrency
11.1 Virtual Warehouse Architecture, Sizing & Credit Economics 11.2 Multi-Cluster Warehouses & Concurrency Scaling Policies 11.3 Warehouse Types & Query Acceleration Service
13 Chapter 12: Query Profiling & Performance Diagnostics
12.1 Query Profile Deep-Dive & Bottleneck Analysis 12.2 Memory Spilling, Warehouse Queuing & Timeout Governance 12.3 Three-Tier Caching Architecture & Result Reuse
14 Chapter 13: Storage Optimization, Cost Governance & Observability
13.1 Micro-Partitioning & Automatic Clustering 13.2 Search Optimization Service (SOS) & Point Lookup Acceleration 13.3 Resource Monitors, Financial Governance & Usage Observability 13.4 Warehouse Monitoring, Alerts, Event Tables & Performance Explorer
11.3 Warehouse Types & Query Acceleration Service
Key Takeaways Explain This Topic AI
Snowpark-optimized warehouses provide 16x the memory per node of a standard warehouse by default (RESOURCE_CONSTRAINT = MEMORY_16X, default size MEDIUM); MEMORY_1X (from XSMALL) and MEMORY_64X (from LARGE) options also exist, billed at higher rates than standard.
Use Snowpark-optimized warehouses for memory-bound work such as ML training and large Python UDFs or procedures; for ordinary SQL that does not exhaust memory, they add cost without benefit.
The Query Acceleration Service (Enterprise Edition) offloads parts of eligible queries — large scans with selective filters, and large INSERT, COPY, UPDATE, or DELETE work — to shared serverless compute.
QAS is enabled per warehouse with ENABLE_QUERY_ACCELERATION = TRUE; QUERY_ACCELERATION_MAX_SCALE_FACTOR (default 8) caps the leased resources, and 0 removes the cap. Use SYSTEM$ESTIMATE_QUERY_ACCELERATION and QUERY_ACCELERATION_ELIGIBLE to find candidates.
Serverless compute features (Snowpipe, Automatic Clustering, Search Optimization, Materialized Views, Serverless Tasks) run on Snowflake-managed compute resources, consuming dedicated serverless credits tracked separately from virtual warehouse metering.
Last updated: September 2026
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
Specification / Feature Standard Virtual Warehouse Snowpark-Optimized Warehouse
Hardware Architecture Balanced CPU, memory, and local storage High-memory profiles (default 16x memory per node)
- Minimum Sizing X-Small X-Small with MEMORY_1X ; Medium (the default) with MEMORY_16X ; Large with MEMORY_64X
- Maximum Sizing 6X-Large 6X-Large (with MEMORY_16X )
- Credit Rate Standard rate for the size Higher rate for the same size (Service Consumption Table)
Primary Workload Fit Standard SQL queries, BI dashboards, batch ELT Machine learning training, PySpark migrations, Python UDFs
Spilling Elimination Resolves moderate spilling via scaling UP Eliminates massive remote spilling in memory-bound jobs
Anti-Pattern Warning Poor fit for memory-heavy single-node Python models Highly wasteful for standard SQL queries that don't need RAM
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
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.
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.
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).
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 .
Snowflake analyzes the query plan and identifies portions (such as scanning and filtering) that can be offloaded.
If the query qualifies, Snowflake transparently offloads the intensive scanning and filtering work to a dynamically provisioned, shared serverless compute pool .
The serverless QAS workers scan micro-partitions in parallel, apply filters, compute intermediate aggregations, and return the filtered subsets to the virtual warehouse.
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 × 4 = 8 \times 4 = 8 × 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?
Workload Type QAS Eligible? Technical Rationale
Massive Table Scans with Filters YES QAS parallelizes partition scanning and predicate evaluation across serverless workers
High-Cardinality Aggregations YES Pre-aggregates groups across scanned partitions before returning data to the warehouse
Complex Multi-Table Joins NO QAS does not accelerate hash joins; joins remain on the virtual warehouse nodes
Large INSERT , COPY , UPDATE , DELETE YES QAS can accelerate statements that insert, copy, update, or delete large amounts of data
Queries with Non-Deterministic UDFs NO State-dependent or external functions cannot be offloaded to serverless scan workers
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
Snowpipe: Ingests micro-batches of files triggered by cloud event notifications (S3, Azure Blob, GCS) using serverless compute without requiring an active virtual warehouse.
Automatic Clustering: Re-organizes table micro-partitions in the background to maintain optimal clustering depth along defined clustering keys.
Search Optimization Service (SOS): Builds and maintains persistent search access paths (equality, substring, geospatial) to accelerate selective point-lookup queries.
Materialized Views Maintenance: Continuously updates materialized views in the background as source table micro-partitions undergo DML changes.
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.
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.