Query timeouts are an important tool for Snowflake users looking to control costs and prevent accidental cost spikes. In this post we’ll cover why they’re useful and how they can be configured.

What are Snowflake query timeouts?

Query timeouts are a setting that prevents Snowflake queries from running for too long. If a query runs for longer than the timeout setting, the query is automatically cancelled by Snowflake.

Why use a timeout?

In Snowflake, queries run on virtual warehouses and Snowflake charges for each second a warehouse is resumed, or “active”. For example, if someone runs an inefficient query on a large dataset and it takes 2 days to complete on an extra-large warehouse, that single query will cost the customer nearly $2,000 dollars.

By lowering the query timeout setting, Snowflake customers can control their costs and avoid unexpected charges from long running queries.

What is the default query timeout in Snowflake?

The default query timeout in Snowflake is 2 days (172,800 seconds) for sessions and warehouses (source). Due to this long default, most Snowflake customers should consider updating their query timeouts to shorter times.

How to configure query timeouts in Snowflake

Query timeouts can be set in a few different ways: account wide, per user, for a single session, or for a given warehouse. All methods use the STATEMENT_TIMEOUT_IN_SECONDS parameter.

Session Query Timeout

To check the existing query timeout setting for the current session, run the command below:

show parameters for session

To set the query timeout to 1 hour for the current session, run the command below:

alter session set statement_timeout_in_seconds = 3600

User Query Timeout

To see the existing query timeout setting for a given user (analytics_user), run the command below:

show parameters for user analytics_user

To set the query timeout to 1 hour for a given user, run the command below:

alter user analytics_user set statement_timeout_in_seconds = 3600

Account Wide Timeout

To see the existing query timeout setting for the account, run the command below:

show parameters for account

It will likely be set to the default 2 days (172,800). To drop this down to 1 day, run the command below:

alter account set statement_timeout_in_seconds = 86400

Warehouse Query Timeout

To check the current query timeout setting for an existing warehouse, run the command below. In this example, the warehouse is named COMPUTE.

show parameters for warehouse compute

To change the query timeout to 1 hour for the warehouse, run the command below:

alter warehouse compute set statement_timeout_in_seconds = 3600

The timeout setting can also be configured when creating a new warehouse:

create warehouse compute
    warehouse_size = 'XSMALL'
    statement_timeout_in_seconds = 3600
;

Which query timeout is used when multiple are set?

When multiple query timeout settings are present, Snowflake enforces the lowest setting. For example, if a session has a query timeout setting of 1 hour and a warehouse has a timeout setting of 10 minutes, then Snowflake will cancel any query that runs for longer than 10 minutes.

How to configure task timeouts in Snowflake

Timeouts for Snowflake tasks are configured using the USER_TASK_TIMEOUT_MS parameter. The default timeout is 1 hour (source). Note that the unit is milliseconds as opposed to seconds for query timeouts.

Similar to warehouses and sessions, you can check the current timeout setting by running show parameters for task my_task. To change the task timeout to 60 seconds, run alter task my_task set user_task_timeout_ms = 60000.

Are you billed for queries and tasks cancelled by Snowflake timeouts?

Yes. Snowflake charges customers for each second a virtual warehouse is active. If a query runs on a virtual warehouse for 4 hours before it is cancelled by Snowflake, customers will be charged for the 4 hours that warehouse was active.