Snowflake warehouses left at the default 48-hour statement timeout
What does ZopNight detect here?
ZopNight is meant to flag a Snowflake warehouse whose `STATEMENT_TIMEOUT_IN_SECONDS` is unset or at the default of 172800 seconds, which is 2 days. It does not read that parameter yet, so today the finding appears on every warehouse; confirm the real value before acting. At the default, a runaway query can bill for up to 48 hours.
Signal and threshold
| Field | Value |
|---|---|
| Rule IDs | RC-2103 |
| Category | governance |
| Severity | low |
| Metric | STATEMENT_TIMEOUT_IN_SECONDS |
| Threshold | unset or 172800 |
| Source | ZopNight |
| Permissions used | MONITOR on the warehouse |
Where it applies
Two days is a long time for a query to run unnoticed
STATEMENT_TIMEOUT_IN_SECONDS 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:
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
- Find the longest legitimate statement on the warehouse, for example the maximum
TOTAL_ELAPSED_TIMEin QUERY_HISTORY over the last month. - Set the timeout with headroom above it:
ALTER WAREHOUSE etl_wh SET STATEMENT_TIMEOUT_IN_SECONDS = 3600; - 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.