ARA-C01 Question 16
Single answerOutline the Snowflake parameter hierarchy and the relationship between the parameter types.A global Snowflake deployment has strict security and session-management requirements. The platform team sets STATEMENT_TIMEOUT_IN_SECONDS = 1800 at the ACCOUNT level to control runaway queries. For a finance workload, the team sets STATEMENT_TIMEOUT_IN_SECONDS = 900 on the FINANCE warehouse because finance queries should fail faster. A data engineer who uses the ANALYST role also has STATEMENT_TIMEOUT_IN_SECONDS = 300 set at the USER level for interactive work. During one session, the engineer runs ALTER SESSION SET STATEMENT_TIMEOUT_IN_SECONDS = 120; and then executes a query on the FINANCE warehouse. Which parameter value will apply to that query, and why?
- A
120 seconds, because the session-level setting overrides user, warehouse, and account values for that session.
- B
300 seconds, because user-level settings take precedence over warehouse-level and session-level values for parameters affecting query execution.
- C
900 seconds, because warehouse-level settings override user-level settings and session-level settings for all compute-related parameters.
- D
1800 seconds, because account-level settings remain authoritative unless the query explicitly specifies a timeout value.
Show answer and explanation
Correct answer: A
Explanation
This question tests practical understanding of Snowflake's parameter hierarchy and the relationship between parameter types and scopes. Snowflake parameters are not all set at the same object levels; each parameter supports specific scopes such as account, user, session, warehouse, database, schema, object, or task depending on the parameter. The key architectural skill is knowing that the effective value is determined by both: (1) whether the parameter is supported at that scope, and (2) the precedence of the most specific applicable setting. For session-related behavior, an explicit session setting typically overrides broader defaults such as user and account values. Account-level values are baseline defaults for the environment, user-level values personalize defaults, and session-level values allow temporary overrides for a current connection. Architects should verify supported scopes for each parameter in Snowflake documentation rather than assuming every parameter follows the same object hierarchy. Relevant Snowflake documentation includes the sections on Parameters, Parameter Hierarchy and Types, ALTER ACCOUNT, ALTER USER, ALTER SESSION, and warehouse/session parameter behavior.
- A. Correct.
Correct. In Snowflake, parameters can be set at multiple scopes, and the most specific applicable setting takes precedence. For session parameters, the hierarchy includes account, user, and session, with session-level values overriding user- and account-level values for that session. Even though a warehouse-level value exists, a session-level value explicitly set for the current session is the one that applies to the query.
- B. Incorrect.
Incorrect. User-level parameter values do override account-level values when no more specific setting exists, but they do not override a session-level setting. A common misconception is to treat user settings as the highest personal default, but an explicit ALTER SESSION command is more specific and takes precedence during that session.
- C. Incorrect.
Incorrect. Warehouse-level parameters can influence behavior for queries using that warehouse, but they do not universally supersede session-level settings. This option reflects the common misunderstanding that warehouse-scoped parameters always dominate anything related to execution. In Snowflake, the effective value depends on the parameter type and scope precedence, and an explicit session setting is more specific here.
- D. Incorrect.
Incorrect. Account-level parameters act as defaults when no lower-level override is in effect. They are not authoritative once the same parameter has been set at a more specific scope such as user, warehouse, or session, depending on the parameter and supported scopes.