ADA-C01 Question 322
Select 2Identify the various timeout parametersA Snowflake administrator is troubleshooting two different issues in a shared analytics environment. First, BI users report that some dashboard queries are being canceled after running for about 10 minutes. Second, data engineers say ad hoc SQL sessions stay open for hours when they leave their worksheets idle, creating governance concerns. The administrator wants to address each issue using the most appropriate Snowflake timeout parameter while minimizing impact to other workloads. Which TWO parameters should the administrator configure?
- A
STATEMENT_TIMEOUT_IN_SECONDS
- B
USER_TASK_TIMEOUT_MS
- C
CLIENT_SESSION_KEEP_ALIVE
- D
AUTOCOMMIT
- E
LOCK_TIMEOUT
Show answer and explanation
Correct answers: A, C
Explanation
This scenario tests whether the candidate can distinguish among Snowflake timeout-related parameters based on operational symptoms rather than by memorization alone. For queries being canceled after a duration, the relevant parameter is STATEMENT_TIMEOUT_IN_SECONDS, which limits how long a statement can execute. For sessions remaining open for extended periods, CLIENT_SESSION_KEEP_ALIVE is the key parameter to evaluate because it allows supported clients to keep sessions alive while connected, which can undermine policies intended to reduce lingering sessions.
The incorrect choices are realistic but apply to different behaviors: USER_TASK_TIMEOUT_MS is for tasks, LOCK_TIMEOUT is for waiting on locks, and AUTOCOMMIT is not a timeout parameter at all. In practice, administrators should also remember that Snowflake parameters can be set at different levels and that effective behavior depends on parameter precedence and workload context. Snowflake documentation on parameters, statement timeouts, task timeouts, and client session keep-alive behavior provides the authoritative reference for these distinctions.
- A. Correct.
Correct. STATEMENT_TIMEOUT_IN_SECONDS controls the maximum amount of time a SQL statement is allowed to run before Snowflake cancels it. This directly addresses dashboard queries being terminated after a time threshold or, if the goal is to adjust that behavior, it is the relevant parameter to review at the appropriate scope (account, user, session, warehouse, etc., depending on governance requirements). A common administrative use case is setting different timeout thresholds for interactive BI workloads versus long-running engineering workloads.
- B. Incorrect.
Incorrect. USER_TASK_TIMEOUT_MS applies to tasks, not interactive user queries or worksheet sessions. It controls how long a task is allowed to run before timing out. An administrator troubleshooting dashboard query runtime limits or idle interactive sessions would not use this parameter for those problems. This is a plausible distractor because it is a timeout parameter, but it is specific to task execution.
- C. Correct.
Correct. CLIENT_SESSION_KEEP_ALIVE affects whether the client keeps the session alive indefinitely while the connection remains open. If enabled, idle sessions can persist much longer, which can conflict with governance goals for ad hoc SQL usage. Reviewing and disabling this behavior where appropriate is relevant when sessions appear to remain open for hours. This is the parameter most directly associated with long-lived client sessions rather than statement execution duration.
- D. Incorrect.
Incorrect. AUTOCOMMIT controls transaction behavior, specifically whether each individual SQL statement is automatically committed if successful. It has nothing to do with statement runtime limits or session idle duration. Candidates may choose this if they confuse transaction management with session lifecycle management.
- E. Incorrect.
Incorrect. LOCK_TIMEOUT controls how long a statement waits to acquire a lock before failing. It is relevant for blocking and concurrency issues, not for general statement execution time limits or idle session persistence. This distractor targets the common misconception that any query cancellation after waiting must be governed by the main statement timeout.