Warehouse Auto-Scaling: Balance Performance and Cost

Warehouse Auto-Scaling: Balance Performance and Cost

Configure intelligent auto-scaling policies to automatically adjust resources based on demand without overspending on unused capacity.

Auto-Scaling Fundamentals

Auto-scaling adjusts warehouse size based on workload automatically, but improper configuration can lead to unnecessary costs.

Scaling Policies

• Scale Up: Increase warehouse size when queries are slow
• Scale Down: Decrease size during low activity
• Scale Out: Add clusters for concurrency
• Scale In: Remove clusters when queries decrease

Configuration Best Practices

CREATE WAREHOUSE auto_scale_wh WITH
WAREHOUSE_SIZE = 'SMALL' -- Start small
AUTO_SUSPEND = 60
AUTO_RESUME = TRUE
MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 4
SCALING_POLICY = 'ECONOMY'; -- Conservative scaling

Monitoring Auto-Scaling

SELECT
WAREHOUSE_NAME,
AVG(AVG_RUNNING) as AVG_QUERIES,
MAX(AVG_RUNNING) as PEAK_QUERIES,
AVG(AVG_QUEUED_LOAD) as AVG_QUEUED
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_LOAD_HISTORY
WHERE START_TIME >= DATEADD(day, -7, CURRENT_TIMESTAMP())
GROUP BY 1;

Key Takeaways

• Start with conservative settings and adjust based on actual usage
• Use ECONOMY policy for cost optimization
• Monitor queue times to determine if scaling is needed
• Auto-scaling prevents over-provisioning