COF-C03 Question 51
Single answerParameter hierarchyA Snowflake administrator sets the STATEMENT_TIMEOUT_IN_SECONDS parameter to 1800 at the account level to prevent runaway queries. Later, the admin sets the same parameter to 300 for the ANALYST role because analysts should have shorter query limits. A user named JORDAN has the ANALYST role and runs queries in a warehouse where no warehouse-level value has been set. JORDAN's user object does not have an explicit value for this parameter. What timeout value will apply when JORDAN executes a query using the ANALYST role?
- A
300 seconds, because the role-level setting overrides the account-level setting for sessions using that role
- B
1800 seconds, because account-level settings apply unless the parameter is set directly on the user or session
- C
The lower of the two values, 300 seconds, because Snowflake automatically chooses the most restrictive value across all levels
- D
No timeout applies, because parameter conflicts between account and role levels cause Snowflake to ignore both settings
Show answer and explanation
Correct answer: A
Explanation
Snowflake parameters can be set at multiple levels, including account, user, session, and for certain parameters, role, warehouse, database, schema, or object levels depending on the parameter type. When the same parameter is defined at more than one applicable level, Snowflake resolves the effective value using the documented parameter hierarchy: the more specific setting overrides the more general one. In this case, the account-level STATEMENT_TIMEOUT_IN_SECONDS value of 1800 is overridden by the role-level value of 300 for sessions using the ANALYST role, because there is no user-level or session-level override. A best practice is to use account-level parameters as broad defaults and apply narrower overrides at lower levels only where a distinct operational need exists. Candidates should distinguish hierarchy-based precedence from the mistaken idea that Snowflake automatically chooses the most restrictive numeric value.
- A. Correct.
Correct. For many session parameters, Snowflake uses a parameter hierarchy in which a more specific setting overrides a more general one. If a parameter is set on both the account and the role, and the session is using that role, the role-level value takes precedence unless an even more specific value exists at the user or session level. Since no user-level or session-level override is present, JORDAN inherits 300 seconds from the ANALYST role.
- B. Incorrect.
Incorrect. This reflects a common misconception that only user-level or session-level settings can override account-level values. In Snowflake, roles can also have parameter values, and those values can affect sessions using that role. Because STATEMENT_TIMEOUT_IN_SECONDS is set on the ANALYST role, the role-level value is more specific than the account-level value in this scenario.
- C. Incorrect.
Incorrect. Snowflake does not generally resolve parameter values by comparing all configured levels and choosing the most restrictive one. Instead, it applies the value based on the documented parameter hierarchy, where the closest applicable object in the hierarchy wins. Here, 300 seconds applies because it is set at the role level, not because Snowflake evaluates both values and picks the lower one.
- D. Incorrect.
Incorrect. Snowflake does not ignore parameter settings when multiple levels define the same parameter. Conflicts are expected and are resolved through precedence rules in the parameter hierarchy. Therefore, a valid timeout still applies.