Managing Your Snowflake’s Virtual Warehouse Costs

A Snowflake virtual warehouse consumes Snowflake credits when it runs. The number of credits consumed depends on the size of the warehouse and how long the virtual warehouse runs. Managing credit consumption to ensure that credits are being used efficiently helps avoid unexpected surprises when it’s time to pay the bill. There are some very simple things you can do to manage your Snowflake’s virtual warehouse costs.

Set Up Resource Monitors to Control Costs

Snowflake virtual warehouse compute costs can be controlled by using Snowflake’s resource monitors to trigger certain actions such as sending notifications or suspending one or more warehouses.

Here is an example to create a Resource Monitor

Resource Monitor

Only an Account Administrator can create or drop a resource monitor. The Account Administrator can, however, grant another role the privilege of modifying a resource monitor that has already been created. A good practice would be to give the privilege to view and modify Snowflake resource monitors to a role that is part of the Accounting department.

Query the Snowflake Database

The Account Administrator also has access to the Snowflake database in the account, a system-defined read only shared database provided by Snowflake. Among other things, this database can be queried to provide information about warehouses that use the most credits. The two code examples provided here will order the warehouses that have used the most credits in the past year and in the past seven days.

Query the Snowflake Database

Once you know which warehouses have consumed the most credits, you can dig a little deeper into the details using the History Tab.

Use the History Tab and Selection Criteria

The History Page allows you to view information about queries for up to 14 days that are waiting in queue, running, succeeded or failed. A user can select any of the following criteria: Status, User, Warehouse, Duration, End Time, Session ID, SQL Text, Query ID, Statement Type or Query Tag.

Use the History Tab and Selection Criteria

For example, you can select queries that have a status of succeeded and then sort the column “Total Duration” to view the warehouses that have the highest credit consumption. You can then scan the “User” and “Warehouse Size” columns to see if there is a pattern that needs to be investigated.

highest credit consumption

Make Good Warehouse Auto-Suspension Choices

When creating a new warehouse, the auto-suspend default value is set to 600 seconds. This means that after 10 minutes of inactivity, the warehouse will automatically suspend. You may want to reduce your default value to one minute, 60 seconds, if your workload runs infrequently. Note that you don’t want to set the auto-suspend to NULL unless your query workloads require a continually running warehouse or else your consumption charges will be much higher than necessary. Because the warehouse cache is dropped every time a warehouse is suspended, it could be cheaper and faster not to have the warehouse auto-suspend if jobs are executed continually. Finding the right balance between whether to enable auto-suspend or not for a particular warehouse may require a bit of testing and consideration.

For more information on how SpringML can help you manage your Snowflake credit consumption, email us at [email protected]