π¦ Optimizing Performance with Snowflake Warehouse
π Overviewβ
Snowflake warehouses power your compute β queries, transformations, and data processing.
The goal is to achieve performance without wasting credits.
1. What is Warehouse Sizing & Scaling?β
In Snowflake, a warehouse (compute cluster) executes your queries and data transformations.
Sizing a warehouse means selecting the right size (XS, S, M, L, XL, etc.) for your workload.
Scaling refers to automatically adjusting the number of clusters (multi-cluster mode) to handle variable query volumes.
Key Concepts:
- Size: Amount of compute resources (CPU, memory) allocated to a warehouse.
- Scaling: Adjusting compute power dynamically by adding/removing clusters.
Why is it Required?β
-
β Performance & Cost Balance
- A properly sized warehouse ensures fast query performance without over-provisioning (wasting Snowflake credits).
- Under-sized warehouses cause slow queries.
- Over-sized warehouses consume unnecessary credits.
-
β Handling Concurrency
- Multi-cluster scaling solves concurrency issues (many users accessing the same warehouse simultaneously).
- Prevents queue build-up and long wait times.
-
β Efficient Resource Utilization
- Snowflakeβs auto-suspend and auto-resume features let you pay only for active compute, so warehouse sizing and scaling must align with your usage patterns.
π Scaling Logic (Multi-Cluster)β
- β Ensures performance during peak and cost savings during idle.
2. Why is Sizing & Scaling Important?β
-
β Performance vs Cost
- Right size = fast queries without waste
- Too small = slow queries
- Too large = costly
-
β Handle Concurrency
- Multi-cluster scaling prevents query queuing
-
β Cost Efficiency
- Use AUTO-SUSPEND & AUTO-RESUME
- Pay only for compute you use
3. SQL Examples: Create & Scale Warehousesβ
β Create Warehouseβ
CREATE WAREHOUSE analytics_wh
WAREHOUSE_SIZE = 'LARGE' -- Options: XSMALL, SMALL, MEDIUM, LARGE, XLARGE, XXLARGE
AUTO_SUSPEND = 300 -- Suspend after 5 minutes of inactivity
AUTO_RESUME = TRUE; -- Auto-resume when new queries come in
INITIALLY_SUSPENDED = TRUE;
β Enable Multi-Clusterβ
ALTER WAREHOUSE analytics_wh
SET MIN_CLUSTER_COUNT = 1 -- Minimum clusters
MAX_CLUSTER_COUNT = 3 -- Maximum clusters
SCALING_POLICY = 'ECONOMY'; -- Can also be 'STANDARD'
π‘ Tips:
ECONOMYβ Adds/removes clusters quickly for cost controlSTANDARDβ Keeps clusters longer for stability
4. Use Case: BI Dashboard Workloadβ
Scenario:
- BI team runs heavy dashboard queries each morning at 9 AM.
- Light ad-hoc queries rest of the day.
Solution:
- Size warehouse as LARGE.
- Configure
MIN_CLUSTER_COUNT = 1,MAX_CLUSTER_COUNT = 3. - Auto-suspend after 5 minutes.
Result:
- Fast dashboard refreshes during peak.
- Cost savings during idle periods.
5. Warehouse Architectureβ
π Warehouse Architectureβ
- β Best Practice: Separate warehouses for ETL, BI, and Data Science for isolation & performance.
6. Auto-Suspend & Auto-Resume Workflowβ
π Auto-Suspend/Resume Flowβ
-
β Result:
- Saves credits during idle
- No manual start required
7. Best Practicesβ
Best Practicesβ
-
β Match warehouse size to workload complexity
- Start small (XS/SMALL) for ad-hoc queries
- Use larger warehouses (L/XL) for ETL or batch jobs
- Scale gradually instead of over-provisioning from the start
-
β Enable AUTO-SUSPEND & AUTO-RESUME
- Saves credits during idle periods
- No need for manual start/stop
-
β Monitor performance & usage
- Use
WAREHOUSE_LOAD_HISTORYto track utilization - Use
QUERY_HISTORYto detect query queuing β sign of under-sizing
- Use
-
β Enable Multi-Cluster for Concurrency
- Prevents query queuing when multiple users/tools hit the same warehouse
- Use
MIN_CLUSTER_COUNTandMAX_CLUSTER_COUNTwisely to balance performance vs cost
-
β Separate Workloads
- Use dedicated warehouses for ETL, BI reporting, and Data Science
- Avoid mixing heavy ETL jobs with dashboard queries
-
β Use Resource Monitors
- Control spend with quotas and alerts
- Prevent runaway costs in case of unexpected workloads
-
β Iterate Based on Usage Patterns
- Review warehouse metrics regularly
- Adjust
WAREHOUSE_SIZE, cluster counts, and scaling policy as workload evolves
8. Doβs β & Donβts ββ
| β Doβs | β Donβts |
|---|---|
| Enable AUTO-SUSPEND & RESUME | Keep warehouses running idle |
| Separate ETL & BI warehouses | Mix heavy ETL with dashboard queries |
| Monitor usage & credit spend | Ignore warehouse history |
| Start small, scale gradually | Over-provision from start |
Limitationsβ
β Warehouse size is fixed (no fractional sizes)
- You must choose from Snowflakeβs predefined options (XS, S, M, L, XL, etc.)
β Multi-cluster can increase costs
- If
MAX_CLUSTER_COUNTis set too high, Snowflake may scale out aggressively - More clusters = higher credit consumption, even if queries donβt fully utilize them
β Scaling doesnβt make individual queries faster
- Adding clusters improves concurrency (more queries can run in parallel)
- But it does not speed up a single queryβs execution time
Advantagesβ
-
β Improved Performance
- Right-sized warehouses ensure faster query execution and better throughput
-
β Better Concurrency Handling
- Multi-cluster scaling allows more users to run queries simultaneously
- Prevents query queuing delays
-
β Cost Efficiency
- Auto-suspend and auto-resume save credits during idle times
- Pay only for active compute usage
-
β Flexibility
- Easy to resize or adjust cluster counts without downtime
- Adapt quickly to changing workload patterns
Disadvantagesβ
β Possible Over-Provisioning
- Choosing too large a warehouse or enabling too many clusters can waste Snowflake credits unnecessarily
β Complexity
- Requires careful monitoring of workloads and adjustments to sizing/scaling settings
- Not a simple βset and forgetβ decision
β Multi-Cluster Costs
- Spinning up multiple clusters increases overall compute cost
- If not managed with proper
MIN/MAX_CLUSTER_COUNT, bills can grow quickly
12. Roles & Privileges Requiredβ
π Create or Alter a Warehouse
- Requires
SYSADMINrole (or equivalent) - Privileges:
CREATE WAREHOUSE,MODIFY WAREHOUSE
π Monitor Warehouse Usage
-
Requires access to Account Usage views
-
Common ones:
WAREHOUSE_LOAD_HISTORYβ Monitor warehouse load & utilizationQUERY_HISTORYβ Check query performance & queuing
β‘ 13. Pro Tipsβ
β Use query result caching (independent of warehouse size)
β Schedule warehouse start/stop for batch jobs
β Use MAX_CONCURRENCY_LEVEL for resource balancing
β Implement Resource Monitors for credit control
Summaryβ
β Warehouse Sizing = Choose the right size for workload
β Warehouse Scaling = Multi-cluster handles concurrency
β Best Practices = Auto-suspend, monitor, separate workloads
β Balance performance & cost = Donβt over-provision
β Always monitor with history views to fine-tune