Compute Layer (Virtual Warehouses)

Overview

The Compute Layer is where the actual heavy lifting of query execution takes place. It consists of Virtual Warehouses, which are Massively Parallel Processing (MPP) compute clusters provisioned from the underlying cloud provider (AWS, Azure, GCP). Understanding Virtual Warehouses is critical for the COF-C03 exam, particularly regarding performance scaling and cost management.

Key Concepts

Virtual Warehouses

A Virtual Warehouse is one or more compute nodes working together to process queries. They are independent of the storage layer.

• Independent: You can have multiple virtual warehouses running simultaneously against the same data without contention. For example, a heavy ETL warehouse will not impact a BI reporting warehouse.
• T-Shirt Sizes: Warehouses come in standard sizes: X-Small, Small, Medium, Large, X-Large, up to 6X-Large.
• Credit Consumption: You pay for compute using Snowflake Credits. As you go up a size, the compute power doubles, and the credit consumption per hour doubles. (e.g., XS = 1 credit/hr, S = 2 credits/hr, M = 4 credits/hr).

Auto-Suspend and Auto-Resume

To control costs, virtual warehouses can automatically suspend when inactive and resume when a query is submitted.

• Auto-Suspend: Automatically shuts down the warehouse after a specified period of inactivity (e.g., 5 minutes).
• Auto-Resume: Automatically wakes up the warehouse the moment a new query is submitted.

Concurrency and Multi-Cluster Warehouses

When too many users submit queries to a single warehouse simultaneously, queries may queue.

• Scaling Up (Vertical): Resizing a warehouse from Small to Large. Good for complex queries with large data volumes.
• Scaling Out (Horizontal): Using Multi-Cluster Warehouses (MCW). When concurrency is high, Snowflake automatically spins up additional clusters of the *same size* to handle the load, and shuts them down when demand drops. Two scaling policies exist:
• Standard (Default): Favors performance. Starts new clusters immediately to prevent queuing.
• Economy: Favors conservation. Waits up to 6 minutes for an existing cluster to become available before starting a new one.

How It Works

Caching in the Compute Layer

Virtual Warehouses have local SSD storage used as a Data Cache (formerly called the Local Disk Cache).

• When a warehouse reads micro-partitions from the central storage layer, it caches them locally.
• Subsequent queries running on the *same* warehouse that need the same data will read from the fast local SSD cache instead of remote cloud storage.
• If the warehouse is suspended, this cache is dropped (cleared).

Warehouse Types

• Standard: The default type, suitable for most SQL workloads.
• Snowpark-Optimized: Designed for workloads requiring high memory, particularly machine learning training or complex Python/Java data processing in Snowpark. They provide 16x the memory per node compared to standard warehouses and consume 1.5x more credits (e.g., Medium Standard = 4 credits/hr, Medium Snowpark-Optimized = 6 credits/hr).

SQL Examples

-- Create a new Virtual Warehouse with auto-suspend and auto-resume
CREATE WAREHOUSE my_bi_wh 
  WITH WAREHOUSE_SIZE = 'SMALL' 
  AUTO_SUSPEND = 300 -- 5 minutes
  AUTO_RESUME = TRUE 
  INITIALLY_SUSPENDED = TRUE;

-- Scale up a warehouse (Vertical scaling)
ALTER WAREHOUSE my_bi_wh SET WAREHOUSE_SIZE = 'LARGE';

-- Configure a Multi-Cluster Warehouse (Horizontal scaling - requires Enterprise Edition or higher)
ALTER WAREHOUSE my_bi_wh 
  SET MIN_CLUSTER_COUNT = 1 
      MAX_CLUSTER_COUNT = 3 
      SCALING_POLICY = 'STANDARD';

Exam Tips

⚠️ Crucial for COF-C03:

• Data Cache vs. Result Cache: The Data Cache lives on the Virtual Warehouse SSD (cleared on suspend). The Result Cache lives in the Cloud Services layer (lasts 24 hours).
• Scaling: Use Scale UP (size) for complex, slow queries. Use Scale OUT (multi-cluster) for queuing/concurrency issues.
• Billing: Warehouses are billed per second of execution, with a 1-minute minimum every time they resume.
• Multi-Cluster Warehouses require the Enterprise Edition or higher.

Key Takeaways

• Virtual Warehouses are independent MPP clusters.
• They scale independently from storage.
• Auto-suspend and auto-resume are vital for cost control.
• Snowpark-optimized warehouses are available for high-memory ML/AI workloads.