February 24, 2024
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.
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
.
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.
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.).
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.