Snowflake Solutions Expertise and
Community Trusted By

Enter Your Email Address Here To Join Our Snowflake Solutions Community For Free

Snowflake Solutions Community

Creating a Warehouse

362 viewsConnecting to SnowflakeVirtualwarehouses

Creating a Warehouse

Alejandro Penzini Answered question December 19, 2023

Warehouse creation requires careful consideration of two key factors for optimal cost and performance:

1. Warehouse Size: Determines available compute resources, with larger sizes offering increased processing power but higher ongoing costs.

2. Management Approach: Choose between manual control over warehouse state (e.g., start/resume/suspend) or automated options for flexibility and resource optimization.

Multi-cluster Warehouses: For users with Snowflake Enterprise Edition (or higher), the number of clusters within a warehouse adds another layer of scalability beyond size. Further details on this advanced feature are available in the "Scaling Up vs Scaling Out" section of this topic.

Selecting an Initial Warehouse Size

Choosing the Initial Warehouse Size:

The optimal warehouse size depends on its intended function and processing workload. Consider these guidelines:

Data Loading: Match size to the number and size of files being loaded. See "Planning a Data Load" for details.
Small-Scale Testing: X-Small, Small, or Medium sizes are typically sufficient.
Large-Scale Production: Larger sizes (Large, X-Large, etc.) may be more cost-effective.

However, remember:

Per-second billing and auto-suspend allow starting large and adjusting down as needed.
Decreasing size is always possible.
Larger isn't always faster for simple queries. They may not benefit from additional resources, regardless of concurrency.

In general:

Aim to match warehouse size to the expected size and complexity of queries.

Automatic Warehouse Suspension:

Snowflake allows enabling automatic suspension based on a specified period of inactivity (minutes, hours, etc.). We recommend tailoring this setting to your workload and availability requirements:

Frequent Workloads: Consider a low inactivity threshold (e.g., 5-10 minutes) to minimize unnecessary credit consumption through per-second billing.
Gaps in Queries: Ensure the threshold aligns with natural gaps in your workload to avoid frequent and resource-intensive suspension/resumption cycles (minimum 60 seconds billed each time).
Heavy Workloads: Disable auto-suspension for warehouses with continuous processing needs or zero tolerance for delays. Note that provisioning time, though typically fast (1-2 seconds), can vary based on warehouse size and available resources.

Automatic Warehouse Resumption:

Snowflake offers automatic resumption upon receiving new queries. We recommend tailoring this setting based on your desired control over usage:

Priority on Convenience: Enable auto-resume for immediate availability even with potential minor provisioning delays. This reduces manual intervention but may incur unnecessary costs if unused.
Focus on Cost Control or Access: Disable auto-resume and manually initiate restarts. This provides granular control over resource utilization and user access but requires proactive management.

Alejandro Penzini Answered question December 19, 2023

Maximize Your Data Potential With ITS

Feedback on Q&A