# Snowflake Warehouse Statement Timeout Not Set

> Flags Snowflake warehouses with STATEMENT_TIMEOUT_IN_SECONDS unset or at the 172800-second default; today every warehouse is flagged because the value is not yet read.

Source: https://zop.dev/integrations/snowflake/recommendations/snowflake-warehouse-statement-timeout-not-set

---

## Two days is a long time for a query to run unnoticed

[STATEMENT_TIMEOUT_IN_SECONDS](https://docs.snowflake.com/en/sql-reference/parameters) is the time after which Snowflake
cancels a running statement. It accepts 0 to 604800 seconds (7 days) and defaults to 172800,
which is 2 days. The limit covers the whole life of the statement, including queue time, lock waits
and compilation, not only execution.

A warehouse bills for every second it runs. One accidental cross join or a stuck MERGE on a Large
Gen1 warehouse, at 8 credits an hour, can reach 384 credits before the default timeout ends it, and
it blocks queued work for the whole time.

## Checking the value on each warehouse

Warehouse-level parameters are not shown by default in SHOW PARAMETERS. Ask for a specific
warehouse:

```sql
SHOW PARAMETERS LIKE 'STATEMENT_TIMEOUT_IN_SECONDS' IN WAREHOUSE etl_wh;
```

The output shows the current and default values and, when the parameter has been set explicitly,
the level it was set at. A value of 172800 with no level means the default applies. Repeat per warehouse,
or script it over the output of `SHOW WAREHOUSES`.

## What ZopNight treats as not set

The check is meant to read the warehouse's own statement timeout and fire when the value is
missing, empty, or exactly 172800 seconds, passing any other number. ZopNight currently collects
warehouses with `SHOW WAREHOUSES`, which does not include this parameter, so today the finding
appears on every warehouse whatever its timeout. Check the real value with the command above before
acting.

## Settings it does not second-guess

Snowflake also lets the parameter be set on the account, the user and the session. When it is set
both in that hierarchy and on the warehouse, the lower non-zero value wins. The rule looks only at
the warehouse value, so a warehouse can be flagged even when every session that uses it already has
a short timeout. It also does not check the separate queued-statement timeout.

## A guardrail with no dollar figure

This is a governance finding, and no saving is attached. A timeout costs nothing until a statement
hits it. What it removes is the tail risk: without it, one bad query can quietly consume a day or
two of a warehouse's credits.

## Setting a timeout that fits the workload

1. Find the longest legitimate statement on the warehouse, for example the maximum
   `TOTAL_ELAPSED_TIME` in QUERY_HISTORY over the last month.
2. Set the timeout with headroom above it:
   `ALTER WAREHOUSE etl_wh SET STATEMENT_TIMEOUT_IN_SECONDS = 3600;`
3. For the rare job that really needs longer, run it on a separate warehouse with a longer limit.
   A longer session value will not help, because the lower of the session and warehouse values
   applies.

**Warning**
A timeout below your longest scheduled job will cancel that job every run. Check the
query history before lowering it on a shared warehouse.