I have been working with the Snowflake Data Cloud since it was just an Analytical RDBMS. Since the beginning of 2018, the Snowflake technology has been pretty fun to work with as a data professional and data entrepreneur. It allows data professionals amazing flexible data processing power in the cloud. The key to a successful Snowflake deployment is setting up security and account optimizations correctly from the beginning. In this article we will discuss the CREATE WAREHOUSE default settings.
Snowflake Cost and Workload Optimization is the Key
After analyzing and working on hundreds of Snowflake customer accounts we have found key processes to optimize Snowflake for compute and storage costs. The best way to have a successful Snowflake deployment is to make sure you setup the compute for cost and workload optimization.
The Snowflake default “create warehouse” settings are not optimized to limit costs. That is part of the reason we built our Snoptimizer service (Snowflake Cost Optimization Service) to help you automatically and easily optimize your Snowflake Account(s). There is no other way to make continuous query and cost optimizations so your Snowflake Cloud Data solution can run as efficiently as possible. Let’s take a quick look at how the Snowflake Account default settings are set right now for that brand spanking new Snowflake Account.
Here is the default screen that comes up when I click +Warehouse in the Classic Console.
Okay, for those of you already in Snowsight (aka Preview App), here is the default screen within Snowsight (or Preview App) – It is almost the same.
So let’s dig into the default settings for these Web UIs that will be there if you just choose a name and click “Create Warehouse” – Let’s further evaluate what happens with our Snowflake Compute if you leave the default Create Warehouse settings.
Create Warehouse – Default Setting #1
Size (Really Warehouse of Compute Size): X-Large is set. I’m going to assume you know how Snowflake Compute works and understand the Snowflake Warehouse T-Shirt Sizes. If not then read this quickly – Snowflake Compute Sizes. Notice that the default setting is X-Large Warehouse vs a smaller warehouse settings of (XS, S, M, L) T-shirt default setting. This defaults to the same setting for both the Classic Console and Snowsight (the Preview App)
Create Warehouse – Default Setting #2 [assuming an Enterprise or higher Edition] Maximum Clusters: 2
While this default setting makes sense if you want to have clustering enabled it still has serious cost implications by default. It makes the assumption that the data cloud customer wants to launch a 2nd cluster and pay more for it on this Snowflake warehouse if it has a certain level of queued statements on the warehouse. If you stick with the XL settings – duplicating a cluster has serious cost consequences of $X/hr.
This is only the default setting for the Classic Console. It also is ONLY set if you have Enterprise Edition or higher because the Standard Edition does not offer Clustering.
Create Warehouse – Default Setting #3 [assuming an Enterprise or higher Edition]
Minimum Clusters: 1
This is great. No issues here.
This is only the default setting for the Classic Console.
Create Warehouse – Default Setting #4 [assuming an Enterprise or higher Edition]
Scaling Policy: Standard
This setting is hard to rate but the truth is if you are a really cost conscious customer you would want to change this to “Economy” by default and not have it set as “Standard”. Why? What’s the difference? See our Q&A here on What’s the difference between Economy and Standard Scaling? High level though is that your 2nd cluster which is set by Default will kick in as soon as and Queuing happens on your Snowflake warehouse versus not launching a 2nd cluster until the Snowflake algos think that it has a minimum of 6 minutes of work that 2nd cluster would have to perform.
This is only the default setting for the Classic Console but when you do toggle the “Multi-cluster Warehouse” on Snowsight setting this does default to “Standard” vs. defaulting to “Economy”.
Create Warehouse – Default Setting #5
Auto Suspend: 10 minutes
For me, I find this typically too high of a setting by default. Many warehouses, especially ELT/ETL warehouses do not need this high of a default setting. For example, a load warehouse which may run at regular intervals never needs a cache and setting this high. Our Snoptimizer service finds inefficient and potentially costly settings like this. For a load warehouse use case Snoptimizer immediately saves you 599 seconds in compute for every interval this would run. We talk more about it in this Snowflake Warehouse Best Practice Auto Suspend article but this can add up especially if your Load Warehouses is larger in T-shirt size.
This defaults to the same setting for both the Classic Console and Snowsight (the Preview App)
Snowflake Create Warehouse – Default Setting #6
Auto Resume Checkbox: Checked by Default.
This setting is totally fine. I don’t even remember the last time I created a warehouse not set to “Auto Resume” being checked by default. This is one of the very very awesome things about Snowflake is that in milliseconds or seconds once a query is executed it brings that automated warehouse compute to your needs. This is revolutionary and awesome!
This defaults to the same setting for both the Classic Console and Snowsight (the Preview App)
Snowflake Create Warehouse – Default Setting #7
Click “Create Warehouse”: The Snowflake Warehouse is immediately started.
This setting I do not like. I do not think it should immediately start to consume credits and go into the Running state. It is too easy for a new SYSADMIN to start a warehouse they do not need. The default setting before this is already set to “Resume”. The Snowflake Warehouse will already resume when a job is sent to it so there is no need to automatically start.
This defaults to the same execution for both the Classic Console and Snowsight (the Preview App)
What do you think?
We really hope this was useful for you. The ITS Snowflake Solutions is a community and data professionals dedicated to Snowflake education and solutions. We are here to help you run your Snowflake Account as efficiently as possible. We work to solve your data driven and data automation challenges!
Below is the SQL code for those of you who just do not do “GUI”
Forget the GUI – Let’s go to the Snowflake CREATE WAREHOUSE code to see what is happening….
DEFAULT SETTINGS: (I think since I started in 2018)
CREATE WAREHOUSE XLARGE_BY_DEFAULT WITH WAREHOUSE_SIZE = ‘XLARGE’ WAREHOUSE_TYPE = ‘STANDARD’ AUTO_SUSPEND = 600 AUTO_RESUME = TRUE MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 2 SCALING_POLICY = ‘STANDARD’ COMMENT = ‘This sucker will consume a lot of credits fast’;
See our Resource Monitor videos – https://www.youtube.com/watch?v=z9nvAGe1Ens