SnowPro Advanced: Data Engineer free question 5 / 10 · Domain 2: Performance Optimization
A warehouse has STATEMENT_TIMEOUT_IN_SECONDS = 1800. An analyst ran ALTER SESSION SET STATEMENT_TIMEOUT_IN_SECONDS = 7200 in their own session and ran a long aggregate on that warehouse. Which combination of the limit that applies to this aggregate and the reason is correct?
- 7200 seconds. For long-running statements the session value is used and the warehouse value is suspended
- 1800 seconds. When both the warehouse and the session have a value, the smaller non-zero value applies
- 1800 seconds. A value set on the warehouse overrides the session value
- 7200 seconds. The session setting takes precedence over the warehouse setting
Show the answer and explanation
Answer: B
Why this is correct
STATEMENT_TIMEOUT_IN_SECONDS sets the maximum time a SQL statement can run before it is canceled. It can be set on the account, user, session, and warehouse, and by default it is set at the account level. The documentation says that when both the warehouse and the session have a setting, the smaller of the non-zero values applies. In this example the statement is canceled at 1800 seconds. It is a control to keep a stuck query from continuing to use credits.
Why the other options are wrong
"7200 seconds. The session setting takes precedence over the warehouse setting" … The reading that the more specific level wins, as with ordinary parameters, does not apply to this combination. The documentation says that when both the warehouse and the session have a value, the smaller non-zero value applies.
"1800 seconds. A value set on the warehouse overrides the session value" … The value that applies, 1800 seconds, is right, but the reason is different. The warehouse value does not replace the session value; the smaller one applies. If the session were set to 600 seconds, the statement would be canceled at 600 seconds.
"7200 seconds. For long-running statements the session value is used and the warehouse value is suspended" … The documentation makes no distinction in which a long statement takes the session value. What limits the wait is a different parameter, STATEMENT_QUEUED_TIMEOUT_IN_SECONDS.
Limit on waiting
Statements in the queue do not consume credits, but if the wait is too long, the result may no longer be needed by the time the statement starts. STATEMENT_QUEUED_TIMEOUT_IN_SECONDS prevents that waste, and the smaller non-zero value applies when both the warehouse and the session have it. These controls do not apply to cloud services and serverless features.