COF-C03 Question 52
Single answerParameter hierarchyA Snowflake administrator sets the STATEMENT_TIMEOUT_IN_SECONDS parameter to 3600 at the account level so that most users can run longer analytical queries. Later, the administrator sets STATEMENT_TIMEOUT_IN_SECONDS to 300 for the FINANCE role because finance users should have shorter-running sessions. A user named ANA has the FINANCE role and starts a session using that role. To support a month-end process, ANA runs ALTER SESSION SET STATEMENT_TIMEOUT_IN_SECONDS = 1800; and then executes a query. Which timeout value will apply to ANA's query?
- A
300 seconds, because the role-level setting overrides the account-level setting and cannot be changed in the session
- B
1800 seconds, because the session-level setting overrides the role-level and account-level settings for that session
- C
3600 seconds, because account-level parameters take precedence over both role and session settings
- D
No timeout will apply, because setting the parameter at multiple levels causes Snowflake to ignore it until the session reconnects
Show answer and explanation
Correct answer: B
Explanation
Snowflake supports parameter settings at multiple scopes, including account, user, session, and for some parameters additional object scopes such as warehouse or role. When the same parameter is set at more than one applicable level, the most specific setting takes precedence. In this scenario, the account-level value of 3600 acts as a general default, the role-level value of 300 is more specific for sessions using the FINANCE role, and the session-level value of 1800 is the most specific for ANA's current session. Therefore, ANA's query uses 1800 seconds. This reflects Snowflake best practices for using broader defaults at the account level and narrower overrides only where needed. See Snowflake documentation on parameters and parameter hierarchy, including ALTER ACCOUNT, ALTER ROLE, and ALTER SESSION behavior.
- A. Incorrect.
Incorrect. While a role-level setting can override an account-level default for sessions using that role, a session-level parameter setting has higher precedence for that session. The misconception here is assuming role settings are immutable once the session starts.
- B. Correct.
Correct. In Snowflake, parameters can be set at multiple levels, and the most specific applicable setting takes precedence. A session-level setting overrides role-level and account-level values for that active session, so ANA's query will use a timeout of 1800 seconds.
- C. Incorrect.
Incorrect. Account-level parameters provide defaults, but they do not take precedence over more specific levels such as user, session, or in this case role and session. This option reflects a common misunderstanding that broader administrative settings are always dominant.
- D. Incorrect.
Incorrect. Snowflake does not ignore a parameter because it is set at multiple levels. Parameter hierarchy is a standard feature, and Snowflake resolves the effective value based on precedence rules. A reconnect is not required for a valid
ALTER SESSIONchange to take effect.