Controlling cost¶

Control access to warehouses¶

Carefully defining who can work with warehouses and what they can do with those warehouses helps control cost by limiting compute resource usage to known warehouses that have cost-effective configurations. Snowflake’s granular access control allows you to grant the following privileges for warehouses:

• CREATE WAREHOUSE — Global privilege (i.e. granted on the account) that restricts which roles can create a new warehouse, allowing you to force individuals to use existing warehouses that have cost controls in place.

• MODIFY — Privilege on a specific warehouse that allows changing the settings that affect cost, including resizing a warehouse and disabling the auto-suspend setting. Commonly, users increase the size of a warehouse for a particular workload and then forget to change it back to its original size, which can have a significant effect on cost.

• USAGE — Privilege on a specific warehouse that allows activating the warehouse to provide compute resources for queries and other SQL actions. Carefully assigning this privilege ensures that users can only use warehouses with the appropriate size and configuration for their workloads.

Centralizing the responsibility of creating and scaling warehouses to just a few members of your team is considered a best practice. You can create a dedicated role with permissions to create and modify all warehouses, and then grant that role to a limited number of users. This allows you to control your warehouse policies and prevent accidental cost overruns resulting from warehouses being created or upsized unexpectedly.

For a list of all the privileges that can be set for a warehouse, see Virtual warehouse privileges.

Choose the best warehouse size¶

The size of a warehouse determines the maximum number of credits that can be spent in an hour. For example, a Small warehouse cannot consume more than 2 credits per hour. For a complete list of warehouse sizes along with the maximum number of credits they consume, see Overview of warehouses.

One best practice for cost control is to use different warehouses for different workloads, which allows you to choose a size that is appropriate for each workload. If you are not sure what is the best size for the warehouse, start with a smaller size and slowly increase it based on workload performance and its SLA.

For more information about choosing the right warehouse for a workload, see Managing Snowflake Compute Resources (Snowflake blog post).

Limit query times¶

Hung queries consume excessive credits because they run longer than expected. To avoid the excess cost associated with a runaway query, you can set the STATEMENT_TIMEOUT_IN_SECONDS parameter to define the maximum amount of time a SQL statement can run before it is cancelled.

The STATEMENT_TIMEOUT_IN_SECONDS parameter can be set for an entire account, a user, a session, or a specific warehouse so that you can carefully set time limits that match the expected run times for various workloads. This parameter is set at the account level by default. When the parameter is set for a warehouse in addition to the session, the lowest non-zero value is enforced.

Use the following commands to view the current query time limits:

SHOW PARAMETERS LIKE 'STATEMENT_TIMEOUT_IN_SECONDS' IN ACCOUNT;
SHOW PARAMETERS LIKE 'STATEMENT_TIMEOUT_IN_SECONDS' IN USER <username>;
SHOW PARAMETERS LIKE 'STATEMENT_TIMEOUT_IN_SECONDS' IN SESSION;
SHOW PARAMETERS LIKE 'STATEMENT_TIMEOUT_IN_SECONDS' IN WAREHOUSE <warehouse_name>;

If you need to adjust the time limits, use one of the following commands:

ALTER ACCOUNT SET STATEMENT_TIMEOUT_IN_SECONDS = <number_of_seconds>;
ALTER USER <username> SET STATEMENT_TIMEOUT_IN_SECONDS = <number_of_seconds>;
ALTER SESSION SET STATEMENT_TIMEOUT_IN_SECONDS = <number_of_seconds>;
ALTER WAREHOUSE <warehouse_name> SET STATEMENT_TIMEOUT_IN_SECONDS = <number_of_seconds>;

Limit statement queue times¶

SQL statements that are in a queue to use a warehouse do not consume credits. However, if a query stays in the queue too long, it may no longer be relevant by the time it executes. Running a query that is no longer relevant wastes credits, so you can implement a cost control by setting a maximum amount of time that a SQL statement can be queued before it is cancelled.

The parameter that controls the amount of time that a SQL statement stays in the queue is STATEMENT_QUEUED_TIMEOUT_IN_SECONDS. This parameter can be set for an entire account, a user, a session, or a specific warehouse. This parameter is set at the account level by default. When the parameter is set for a warehouse in addition to the session, the lowest non-zero value is enforced.

Use the following commands to view the current queue time limits:

SHOW PARAMETERS LIKE 'STATEMENT_QUEUED_TIMEOUT_IN_SECONDS' IN ACCOUNT;
SHOW PARAMETERS LIKE 'STATEMENT_QUEUED_TIMEOUT_IN_SECONDS' IN USER <username>;
SHOW PARAMETERS LIKE 'STATEMENT_QUEUED_TIMEOUT_IN_SECONDS' IN SESSION;
SHOW PARAMETERS LIKE 'STATEMENT_QUEUED_TIMEOUT_IN_SECONDS' IN WAREHOUSE <warehouse_name>;

If you need to adjust the time limits, use one of the following commands:

ALTER ACCOUNT SET STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = <number_of_seconds>;
ALTER USER <username> SET STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = <number_of_seconds>;
ALTER SESSION SET STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = <number_of_seconds>;
ALTER WAREHOUSE <warehouse_name> SET STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = <number_of_seconds>;

Use auto-suspension¶

By default, all warehouses have the auto-suspend setting enabled, which means a warehouse automatically shuts down when it is inactive for a defined period of time. A suspended warehouse does not consume credits, so the warehouse only incurs cost when it is processing a workload.

Restricting users from disabling the auto-suspend setting helps to prevent an unused warehouse from wasting credits. You can use access control to allow someone to use a warehouse but also prevent them from modifying its Auto Suspend setting.

Query: Find warehouses without auto-suspend

Use the following query to periodically check whether the auto-suspend setting has been disabled for any warehouses.

SHOW WAREHOUSES
;

SELECT "name" AS WAREHOUSE_NAME
    ,"size" AS WAREHOUSE_SIZE
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE IFNULL("auto_suspend",0) = 0;

To enable auto-suspend for the warehouses that have it turned off, open Snowsight, and navigate to Admin » Warehouses. You can also use the AUTO_SUSPEND parameter of the ALTER WAREHOUSE command.

SHOW WAREHOUSES
;

SELECT "name" AS WAREHOUSE_NAME
    ,"size" AS WAREHOUSE_SIZE
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "auto_resume" = 'false';

Enforce spending limits¶

Resource monitors provide the ability to set limits on credits consumed by a warehouse during a specific time interval or date range. This can help prevent warehouses from unintentionally consuming more credits than typically expected.

Sometimes a resource monitor simply notifies an administrator when a credit limit is reached, but you can also enforce a limit by configuring a resource monitor to suspend a warehouse as soon as the limit is reached. There are two options when enforcing a limit: suspend the warehouse once pending statements are executed or suspend immediately without waiting for statements to complete.

Because a single resource monitor can be set for multiple warehouses or an entire account, you can effectively suspend multiple warehouses when an overall spending limit is reached. A warehouse can be assigned to its own resource monitor and an account-specific resource monitor at the same time; the warehouse is suspended when either of the credit limits is reached.

For more details about suspending warehouses when spending limits are reached, see Working with resource monitors.

Query: Find warehouses without resource monitors

The following query lists the warehouses that have not been assigned to a warehouse-specific resource monitor, which makes them vulnerable to runaway costs. Note that the query does not check for account-level resource monitors; warehouses in the list that belong to an account that has an account-level resource monitor are still subject to credit limits.

SELECT "name" AS WAREHOUSE_NAME
      ,"size" AS WAREHOUSE_SIZE
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "resource_monitor" = 'null'
;