How to Not Run a $12,000 Query on Snowflake

Set up query timeouts today.

Sahil Singla

Co-founder of Baselit

Why should I set up query timeouts?

Snowflake has simplified many tasks for data teams, but this simplicity comes with a catch. Among the conveniences is the unintended potential to run queries that can rack up thousands of dollars in costs. I’ve come across several stories that go like this: An analyst sets a query to run on a 3X-Large warehouse on a Friday, and then promptly forgets about it. The query keeps running through the weekend, until it times out after 48 hours. On Monday, the team discovers this minor oversight. The outcome of the query: a trillion rows of data processed, and a staggering $12,000 bill.

To avoid such costly scenarios, Snowflake offers a solution: a query timeout parameter. This setting determines the maximum duration a query can run before Snowflake automatically cancels it. By default, the timeout is set to 48 hours, which seems excessive. If your queries are running close to 48 hours, you have bigger problems to worry about. In almost all cases, it’s advisable to set a considerably shorter timeout period. First, let’s delve into how you can adjust this timeout value from its default setting.

How to set up query timeouts in Snowflake?

Query timeouts are set using the STATEMENT_TIMEOUT_IN_SECONDS parameter.
It can be set at one or more levels in the hierarchy of account, user and session. In addition, a query timeout can be set for an individual warehouse. Let’s say we want to set a query timeout of two hours (or 7200 seconds) at each level and for each warehouse. Here are the commands you’ll have to run.

-- Set session level query timeout
alter session set statement_timeout_in_seconds = 7200

-- Set user level query timeout
alter user example_user set statement_timeout_in_seconds = 7200

-- Set account level query timeout
alter account set statement_timeout_in_seconds = 7200

-- Set warehouse level query timeout
alter warehouse example_warehouse set statement_timeout_in_seconds = 7200

How long should a query timeout be?

One way to arrive at a value for STATEMENT_TIMEOUT_IN_SECONDS is take the runtime of your longest queries and then add a little margin to be safe. For example, if you expect to your queries to run within 45 minutes, the timeout could be set at 1 hour.

But what if you are not able to forecast your max query runtimes for some reason? In that case, we can use a cost-based heuristic. First let’s take a look at how much a timed-out query costs for different sized warehouses. We’ll use the default value for STATEMENT_TIMEOUT_IN_SECONDS (i.e. 48 hours) and assume that each credit costs $3.

No one wants to pay tens of thousands of dollars for a cancelled query. To ensure that this never happens, we will build our heuristic based on two constraints. First, no query should end up costing us more than $400. You can pick a different dollar cap that sounds reasonable. Then we reverse calculate what timeout value leads to this number for each warehouse size.

The second constraint is that no query should be allowed to run for more than 8 hours. If you have a query that runs for longer than this, you might want to consider moving to a larger warehouse. Finally, we take the minimum of 8 hours and the $400 cap value, and set it as our timeout value. The result of this exercise is shown in the below table.

Setting up alerts for timed-out queries

We can't just set statement_timeout_in_seconds to a lower value without solving the real issue i.e. fixing the timed-out query. Or we would end up absorbing costs for one cancelled query after another. Snowflake provides the functionality to set up alerts that can be triggered when certain conditions are met. We can use this to receive email alerts so that we never miss any query timeouts.

There are a couple of steps involved that I will walk you through. First, you’ll have to create a notification integration if this is the first time you are setting up email alerts on Snowflake. Run this statement which creates an email integration called my_email_int.

CREATE NOTIFICATION INTEGRATION my_email_int
  TYPE=EMAIL
  ENABLED=TRUE

Next, create the send_timeout_alert() procedure that will be called by our alert. It sends an email to you, which contains the query IDs and query texts of all timed-out queries from the last 6 hours.

CREATE OR REPLACE PROCEDURE send_timeout_alert()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
    var initialText = "The following queries have timed out in the last 6 hours: \n \n";
    var resultText = initialText;

    var query = `
        SELECT QUERY_ID, QUERY_TEXT
        FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
        WHERE ERROR_CODE = '000630' -- 000630 is the timeout error code.
        AND END_TIME >= DATEADD('hour', -6, CONVERT_TIMEZONE('UTC', CURRENT_TIMESTAMP()))
        AND END_TIME < CONVERT_TIMEZONE('UTC', CURRENT_TIMESTAMP())
    `;

    var statement = snowflake.createStatement({sqlText: query});
    var result = statement.execute();

    while (result.next()) {
        var line = "Query ID: " + result.getColumnValue(1) + "\n" +
                    "Query Text: " + result.getColumnValue(2) + "\n \n";
        resultText += line;
    }

    if (resultText.length > initialText.length) {
        var sendResult = snowflake.execute({
            sqlText: "CALL SYSTEM$SEND_EMAIL('my_email_int','youremail@email.com', 'Query Timeout Alert', ?)",
            binds: [resultText]
        });
        return "Email sent.";
    } else {
        return "No timeouts found.";
    }

CREATE OR REPLACE PROCEDURE send_timeout_alert()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
    var initialText = "The following queries have timed out in the last 6 hours: \n \n";
    var resultText = initialText;

    var query = `
        SELECT QUERY_ID, QUERY_TEXT
        FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
        WHERE ERROR_CODE = '000630' -- 000630 is the timeout error code.
        AND END_TIME >= DATEADD('hour', -6, CONVERT_TIMEZONE('UTC', CURRENT_TIMESTAMP()))
        AND END_TIME < CONVERT_TIMEZONE('UTC', CURRENT_TIMESTAMP())
    `;

    var statement = snowflake.createStatement({sqlText: query});
    var result = statement.execute();

    while (result.next()) {
        var line = "Query ID: " + result.getColumnValue(1) + "\n" +
                    "Query Text: " + result.getColumnValue(2) + "\n \n";
        resultText += line;
    }

    if (resultText.length > initialText.length) {
        var sendResult = snowflake.execute({
            sqlText: "CALL SYSTEM$SEND_EMAIL('my_email_int','youremail@email.com', 'Query Timeout Alert', ?)",
            binds: [resultText]
        });
        return "Email sent.";
    } else {
        return "No timeouts found.";
    }

Finally, let’s create the actual alert, query_timeout_alert. This alert checks whether there were any query timeouts in the last 6 hours. If the condition is satisfied, it calls the send_timeout_alert() procedure. You can change the alert frequency based on your needs (every hour, every 24 hours etc.).

CREATE ALERT query_timeout_alert
    WAREHOUSE = COMPUTE_WH -- The name of your warehouse
    SCHEDULE = 'USING CRON 0 */6 * * * UTC'  -- Scheduled to run every 6 hours.
    IF (EXISTS(
        SELECT 1
        FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
        WHERE ERROR_CODE = '000630' -- 000630 is the timeout error code.
        AND END_TIME >= DATEADD('hour', -6, CONVERT_TIMEZONE('UTC', CURRENT_TIMESTAMP()))
        AND END_TIME < CONVERT_TIMEZONE('UTC', CURRENT_TIMESTAMP())
    ))
    THEN
        CALL send_timeout_alert()

CREATE ALERT query_timeout_alert
    WAREHOUSE = COMPUTE_WH -- The name of your warehouse
    SCHEDULE = 'USING CRON 0 */6 * * * UTC'  -- Scheduled to run every 6 hours.
    IF (EXISTS(
        SELECT 1
        FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
        WHERE ERROR_CODE = '000630' -- 000630 is the timeout error code.
        AND END_TIME >= DATEADD('hour', -6, CONVERT_TIMEZONE('UTC', CURRENT_TIMESTAMP()))
        AND END_TIME < CONVERT_TIMEZONE('UTC', CURRENT_TIMESTAMP())
    ))
    THEN
        CALL send_timeout_alert()

Now we just have to activate the alert with the following command. With this, you will start receiving email notifications for query timeouts on the email address associated with your Snowflake profile.