Virtual Warehouse Management: Multi-Cluster, Auto-Suspend & Scaling

Architecture Diagram 1: Warehouse Hierarchy & Sizing

Architecture Diagram 2: Multi-Cluster Scaling Behavior

Architecture Diagram 3: Warehouse Hierarchy

Architecture Diagram 4: Multi-Cluster Auto-Scaling Timeline

Architecture Diagram 5: Scaling Policies Comparison

Detailed Explanation

What are Virtual Warehouses?

Virtual warehouses are independent compute clusters that execute SQL queries and DML operations. Unlike traditional fixed-size appliances, they are elastic and scale dynamically based on workload demands.

Virtual Warehouse Architecture

• Each warehouse: independent nodes with CPU, RAM, and local SSD cache
• Complete isolation — multiple warehouses access same data without contention
• Cloud services layer manages query routing and metadata independently

Multi-Cluster Warehouse Operations

• Automatically manages multiple independent clusters under one logical warehouse
• Scaling triggers: queue depth exceeding threshold
• Scaling behavior:
  • STANDARD policy — scales aggressively (queue ≥ 1)
  • ECONOMY policy — scales conservatively (queue ≥ 6)
• Queries routed to cluster with lowest queue depth
• Suspended clusters complete running queries before shutdown

Auto-Suspend and Auto-Resume

• Common defaults: 60s for dev, 300s for production
• Zero credit consumption during suspension

Resource Monitoring and Cost Control

• Warehouse-level monitors — individual workload cost attribution
• Account-level monitors — aggregate consumption visibility
• Triggers: NOTIFY, SUSPEND, SUSPEND_IMMEDIATELY at threshold percentages
• Email notifications and external monitoring integration

Performance Optimization Strategies

1. Right-size — scale up for complex analytics, down for simple queries
2. Scaling policies — STANDARD for interactive, ECONOMY for batch ETL
3. Statement timeouts — STATEMENT_TIMEOUT (max execution) + STATEMENT_QUEUED_TIMEOUT (max queue wait)
4. Leverage result cache — avoid non-deterministic functions for cache hits

Key Takeaway: Use SCALING_POLICY=ECONOMY for batch ETL where queueing is acceptable, STANDARD for interactive workloads requiring fast response.

Key Concepts Table

Code Examples

-- Example 1: Create warehouse with comprehensive settings
CREATE WAREHOUSE analytics_wh
    WAREHOUSE_SIZE = 'xlarge'
    MIN_CLUSTER_COUNT = 2
    MAX_CLUSTER_COUNT = 8
    SCALING_POLICY = 'STANDARD'
    AUTO_SUSPEND = 300
    AUTO_RESUME = TRUE
    INITIALLY_SUSPENDED = FALSE
    STATEMENT_TIMEOUT_IN_SECONDS = 3600
    STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = 600
    RESOURCE_MONITOR = 'analytics_monitor'
    COMMENT = 'Multi-cluster warehouse for analytics workloads';

-- Example 2: Create resource monitor
CREATE RESOURCE MONITOR account_monitor
    WITH
    CREDIT_QUOTA = 10000
    FREQUENCY = MONTHLY
    START_TIMESTAMP = IMMEDIATELY
    TRIGGERS
        ON 80% DO NOTIFY
        ON 90% DO SUSPEND
        ON 95% DO SUSPEND_IMMEDIATELY;

-- Example 3: Create warehouse-specific monitor
CREATE RESOURCE MONITOR etl_monitor
    WITH
    CREDIT_QUOTA = 5000
    FREQUENCY = MONTHLY
    TRIGGERS
        ON 75% DO NOTIFY
        ON 90% DO SUSPEND;

-- Assign monitor to warehouse
ALTER WAREHOUSE etl_wh SET RESOURCE_MONITOR = etl_monitor;

-- Example 4: Monitor warehouse performance
SELECT 
    warehouse_name,
    warehouse_size,
    cluster_number,
    AVG(queries_completed) as avg_queries,
    AVG(queued_overload_queries) as avg_queued,
    AVG(execution_time_ms) / 1000 as avg_exec_seconds,
    SUM(credits_used) as total_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD(day, -30, CURRENT_TIMESTAMP())
GROUP BY 1, 2, 3
ORDER BY total_credits DESC;

-- Example 5: Analyze auto-scaling events
SELECT 
    warehouse_name,
    event_name,
    event_timestamp,
    event_reason,
    cluster_number
FROM TABLE(INFORMATION_SCHEMA.WAREHOUSE_LOAD_HISTORY(
    START_TIME => DATEADD(hour, -24, CURRENT_TIMESTAMP())
))
WHERE event_name IN ('RESUME', 'SUSPEND', 'ADD_CLUSTER', 'REMOVE_CLUSTER')
ORDER BY event_timestamp DESC;

-- Example 6: Monitor current warehouse status
SELECT 
    warehouse_name,
    state,
    num_queries,
    num_queued_queries,
    time_active,
    time_suspended
FROM TABLE(INFORMATION_SCHEMA.WAREHOUSE_LOAD_HISTORY(
    START_TIME => DATEADD(hour, -1, CURRENT_TIMESTAMP())
))
ORDER BY time_active DESC;

-- Example 7: Dynamic warehouse scaling
-- Scale up warehouse for batch processing
ALTER WAREHOUSE etl_wh SET WAREHOUSE_SIZE = '2xlarge';

-- Scale out warehouse for peak load
ALTER WAREHOUSE analytics_wh SET MAX_CLUSTER_COUNT = 10;

-- Scale in warehouse during off-hours
ALTER WAREHOUSE analytics_wh SET MAX_CLUSTER_COUNT = 2;

-- Example 8: Warehouse usage analysis
SELECT 
    warehouse_name,
    DATE_TRUNC('day', start_time) as usage_date,
    SUM(credits_used) as daily_credits,
    SUM(credits_used) * 3 as daily_cost_usd,  -- ~$3/credit
    AVG(queries_completed) as avg_queries_per_hour
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD(day, -90, CURRENT_TIMESTAMP())
GROUP BY 1, 2
ORDER BY usage_date DESC, daily_credits DESC;

-- Example 9: Optimize warehouse for specific workload
-- Create warehouse optimized for large scans
CREATE WAREHOUSE scan_wh
    WAREHOUSE_SIZE = '3xlarge'
    AUTO_SUSPEND = 600
    STATEMENT_TIMEOUT_IN_SECONDS = 7200
    COMMENT = 'Optimized for large table scans';

-- Create warehouse for interactive queries
CREATE WAREHOUSE interactive_wh
    WAREHOUSE_SIZE = 'medium'
    AUTO_SUSPEND = 60
    STATEMENT_TIMEOUT_IN_SECONDS = 300
    STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = 60
    COMMENT = 'Optimized for interactive queries';

-- Example 10: Monitor query performance by warehouse
SELECT 
    warehouse_name,
    AVG(execution_time_ms) / 1000 as avg_exec_seconds,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY execution_time_ms) / 1000 as median_exec_seconds,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY execution_time_ms) / 1000 as p95_exec_seconds,
    COUNT(*) as total_queries,
    SUM(CASE WHEN execution_time_ms > 60000 THEN 1 ELSE 0 END) as slow_queries
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP())
  AND execution_status = 'SUCCESS'
GROUP BY 1
ORDER BY avg_exec_seconds DESC;

Performance Metrics

Best Practices

1. Right-size warehouses: Start with Medium for ad-hoc queries, scale up for batch ETL. Monitor queue depth to identify under-provisioned warehouses.

2. Use multi-cluster warehouses: Set MIN_CLUSTER_COUNT=1 and MAX_CLUSTER_COUNT=3-5 for concurrent workloads. Use SCALING_POLICY=ECONOMY for cost optimization.

3. Implement warehouse isolation: Create separate warehouses for different workloads (ETL, reporting, ad-hoc) to prevent resource contention and enable independent scaling.

4. Configure appropriate timeouts: Set STATEMENT_QUEUED_TIMEOUT for queries that shouldn't run indefinitely and STATEMENT_TIMEOUT to kill long-running queries.

5. Leverage resource monitors: Set up alerts for credit consumption to prevent unexpected costs. Use MAX_CREDIT_QUOTA per warehouse for budget control.

6. Optimize auto-suspend: Set AUTO_SUSPEND=60 for development warehouses, AUTO_SUSPEND=300 for production, and AUTO_SUSPEND=60 for infrequently used warehouses.

7. Monitor scaling events: Track warehouse load history to identify scaling patterns and optimize MIN/MAX_CLUSTER_COUNT settings.

8. Use warehouse groups: Organize warehouses by workload type (analytics, ETL, development) to simplify management and cost attribution.

9. Implement cost allocation: Use resource monitors and tags to attribute warehouse costs to specific departments or projects.

10. Regular performance reviews: Analyze warehouse performance metrics weekly to identify optimization opportunities and right-sizing needs.

See Also

• Snowflake Architecture — Warehouse architecture details
• Optimization Techniques — Query optimization strategies
• Access Control — Warehouse access control
• Stored Procedures — Automated warehouse management
• PySpark Iceberg Tables — Compute optimization patterns
• Data Warehouse Concepts — Data warehouse design principles