ARA-C01 Question 8
Single answerParameters (all levels)A global Snowflake deployment uses a shared ETL service user to load data into multiple databases and schemas. The architecture team wants all newly created sessions for this ETL user to use the UTC timezone and to enforce a 45-minute statement timeout, without changing behavior for analysts who use the same warehouses. During testing, an engineer runs these commands:
- ALTER ACCOUNT SET TIMEZONE = 'America/Los_Angeles';
- ALTER USER ETL_SVC SET TIMEZONE = 'UTC';
- ALTER WAREHOUSE ETL_WH SET STATEMENT_TIMEOUT_IN_SECONDS = 2700;
- ALTER USER ETL_SVC SET STATEMENT_TIMEOUT_IN_SECONDS = 1800;
The ETL service user then connects using warehouse ETL_WH and does not issue any ALTER SESSION commands. Which settings will apply to the ETL user's new session by default?
- A
TIMEZONE = 'America/Los_Angeles' and STATEMENT_TIMEOUT_IN_SECONDS = 2700 because account and warehouse settings override user settings
- B
TIMEZONE = 'UTC' and STATEMENT_TIMEOUT_IN_SECONDS = 1800 because user-level parameters override account-level and warehouse-level defaults for the session
- C
TIMEZONE = 'UTC' and STATEMENT_TIMEOUT_IN_SECONDS = 2700 because user-level parameters apply only to localization settings, while execution controls come from the warehouse
- D
TIMEZONE = 'America/Los_Angeles' and STATEMENT_TIMEOUT_IN_SECONDS = 1800 because account-level parameters control session initialization, but user-level timeout overrides warehouse timeout
Show answer and explanation
Correct answer: B
Explanation
Snowflake parameters can be set at multiple levels, including account, user, session, warehouse, database, schema, and object levels depending on the parameter. The effective value is determined by parameter type and scope, with more specific settings generally overriding broader defaults. In this scenario, the team wants only the ETL service user's sessions to change, so user-level settings are the correct approach. The account TIMEZONE acts as a default, but ALTER USER ETL_SVC SET TIMEZONE = 'UTC' overrides it for that user's new sessions. Similarly, a user-level STATEMENT_TIMEOUT_IN_SECONDS becomes the default for sessions started by that user unless explicitly changed at the session level. This is a practical best practice because it targets the ETL principal directly and avoids affecting analysts who may use the same warehouse. See Snowflake documentation on parameters, parameter hierarchy, and setting parameters at the account, user, session, and warehouse levels.
- A. Incorrect.
Incorrect. For session parameters such as TIMEZONE and STATEMENT_TIMEOUT_IN_SECONDS, Snowflake uses parameter precedence where the more specific level overrides the more general one. User-level settings override account-level defaults for sessions created by that user. Warehouse parameters do not take precedence over a user-level session parameter in this scenario.
- B. Correct.
Correct. TIMEZONE set at the user level overrides the account-level TIMEZONE for new sessions created by ETL_SVC. Likewise, STATEMENT_TIMEOUT_IN_SECONDS set at the user level overrides broader defaults for that user's session if no session-level override is issued. This meets the requirement of changing behavior for the ETL service user without affecting analysts using the same warehouse.
- C. Incorrect.
Incorrect. This reflects a common misconception that warehouse parameters universally control execution-related behavior for any session using that warehouse. In Snowflake, parameter resolution is based on object and session hierarchy, and user-level session defaults can override broader settings. Execution-related parameters are not limited to warehouse control only.
- D. Incorrect.
Incorrect. While user-level parameters can override account-level defaults, the account-level TIMEZONE does not remain in effect when a more specific user-level TIMEZONE is defined. The user-level TIMEZONE = 'UTC' is the effective default for new ETL_SVC sessions.