Skip to main content

πŸ“¦ 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 control
  • STANDARD β†’ 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_HISTORY to track utilization
    • Use QUERY_HISTORY to detect query queuing β†’ sign of under-sizing
  • βœ… Enable Multi-Cluster for Concurrency

    • Prevents query queuing when multiple users/tools hit the same warehouse
    • Use MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT wisely 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 & RESUMEKeep warehouses running idle
Separate ETL & BI warehousesMix heavy ETL with dashboard queries
Monitor usage & credit spendIgnore warehouse history
Start small, scale graduallyOver-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_COUNT is 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 SYSADMIN role (or equivalent)
  • Privileges: CREATE WAREHOUSE, MODIFY WAREHOUSE

πŸ”‘ Monitor Warehouse Usage

  • Requires access to Account Usage views

  • Common ones:

    • WAREHOUSE_LOAD_HISTORY β†’ Monitor warehouse load & utilization
    • QUERY_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