ARA-C01 Question 15
Single answerOutline the Snowflake parameter hierarchy and the relationship between the parameter types.A global Snowflake deployment supports multiple business units. The platform team sets STATEMENT_TIMEOUT_IN_SECONDS = 1800 at the ACCOUNT level to protect shared resources. For the FINANCE database, an architect sets STATEMENT_TIMEOUT_IN_SECONDS = 900 because finance workloads should fail faster. A role named FINANCE_ANALYST is granted to a user whose USER object has STATEMENT_TIMEOUT_IN_SECONDS = 120. The user starts a session without altering any session parameters and runs queries against tables in the FINANCE database using a warehouse with no timeout configured. Which timeout value will govern the user's statements, and why?
- A
120 seconds, because user-level parameters override account-level and object-level settings for the session unless a session-level value is explicitly set
- B
900 seconds, because database-level parameters take precedence over user-level parameters when queries access objects in that database
- C
1800 seconds, because account-level parameters remain in effect unless overridden on the warehouse used to run the query
- D
No timeout applies, because parameter values do not inherit across account, database, user, and session scopes
Show answer and explanation
Correct answer: A
Explanation
Snowflake parameters do not all behave identically; their effective value depends on the parameter type and the scopes where that parameter is supported. A key architectural concept is the hierarchy: broader scopes such as ACCOUNT provide defaults, while more specific scopes such as USER or SESSION can override them when the parameter supports those levels. For session-related behavior, Snowflake commonly resolves the value from the most specific applicable scope, with an explicit SESSION value taking highest precedence, followed by USER, then broader defaults. In this scenario, the user did not set a session override, so the USER-level value of 120 seconds governs the session. The FINANCE database setting is plausible as a distractor because architects often think object location determines runtime statement controls, but statement timeout is not chosen simply because the query touches that database. Best practice is to understand each parameter's supported scopes and precedence before using account, object, user, or session values for governance. This aligns with Snowflake documentation on parameters, including parameter hierarchy, inheritance, and object/session/account parameter management.
- A. Correct.
Correct. Snowflake parameters are resolved by hierarchy and parameter type. For session parameters such as STATEMENT_TIMEOUT_IN_SECONDS, a more specific setting overrides a broader one. If no session-level override is set, a USER-level value applies to sessions created by that user. Although the ACCOUNT value provides the default and some object types can also support parameters, the user's explicit setting of 120 seconds is the effective value for the session in this scenario.
- B. Incorrect.
Incorrect. This reflects a common misconception that a database parameter controls all query behavior for statements touching objects in that database. Database parameters affect that database object and inheritable object behavior where supported, but they do not override a user's effective session parameter for statement timeout in this scenario.
- C. Incorrect.
Incorrect. Account-level parameters are defaults, not the highest-precedence settings. They are commonly overridden by more specific scopes such as user or session when the parameter supports those scopes. The absence of a warehouse-level timeout does not cause the account-level value to override the user's value.
- D. Incorrect.
Incorrect. Snowflake does support parameter inheritance and precedence across scopes. The platform uses account defaults and then more specific assignments, depending on the parameter type and supported levels. Assuming no inheritance exists would lead to incorrect governance and troubleshooting decisions.