Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Configure Scaling Policy in Snowflake for Auto Scaling

In Snowflake, you configure a scaling policy on a multi-cluster warehouse using the ALTER WAREHOUSE command with the SCALING_POLICY parameter. The two options are AEC (Auto-Expand and Contract) and EC (Expand and Contract), which control how clusters scale up and down automatically.
📐

Syntax

The scaling policy is set on a multi-cluster warehouse using the ALTER WAREHOUSE command. You specify the SCALING_POLICY parameter with one of two values:

  • AEC: Auto-Expand and Contract. Clusters automatically increase and decrease based on load.
  • EC: Expand and Contract. Clusters expand automatically but contract only when idle.
sql
ALTER WAREHOUSE <warehouse_name> SET SCALING_POLICY = {AEC | EC};
💻

Example

This example sets the scaling policy of a multi-cluster warehouse named MY_WAREHOUSE to auto-expand and contract clusters automatically.

sql
ALTER WAREHOUSE MY_WAREHOUSE SET SCALING_POLICY = AEC;
Output
Statement executed successfully.
⚠️

Common Pitfalls

Common mistakes include:

  • Setting scaling policy on a single-cluster warehouse, which has no effect.
  • Confusing AEC and EC policies and expecting clusters to contract immediately with EC.
  • Not setting MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT properly, which limits scaling.
sql
/* Wrong: Setting scaling policy on single cluster warehouse */
ALTER WAREHOUSE SINGLE_CLUSTER SET SCALING_POLICY = AEC;

/* Right: Use multi-cluster warehouse */
ALTER WAREHOUSE MULTI_CLUSTER SET SCALING_POLICY = AEC;
📊

Quick Reference

ParameterDescriptionValues
SCALING_POLICYControls auto scaling behavior of multi-cluster warehousesAEC, EC
MIN_CLUSTER_COUNTMinimum number of clusters to keep runningInteger >= 1
MAX_CLUSTER_COUNTMaximum number of clusters allowedInteger >= MIN_CLUSTER_COUNT
✅

Key Takeaways

Set scaling policy only on multi-cluster warehouses using ALTER WAREHOUSE.
Use AEC for automatic expand and immediate contract, EC for expand and delayed contract.
Always configure MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT to control scaling limits.
Single-cluster warehouses do not support scaling policies.
Check warehouse size and load to optimize scaling behavior.