ADA-C01 Question 323
Single answerIdentify the various timeout parametersA Snowflake administrator is troubleshooting two cost and reliability issues for an ELT workload. First, some ad hoc analyst queries are running much longer than expected and should be stopped automatically after 15 minutes. Second, JDBC sessions from a legacy application are staying open for hours with no activity, preventing the associated warehouse from suspending as quickly as intended. Which configuration changes should the administrator implement to address BOTH issues with the most appropriate Snowflake timeout parameters?
- A
Set STATEMENT_TIMEOUT_IN_SECONDS to 900 for the relevant users, roles, or warehouse; set CLIENT_SESSION_KEEP_ALIVE to FALSE for the legacy application connection or user/session context.
- B
Set USER_TASK_TIMEOUT_MS to 900000 for the affected users; set AUTOCOMMIT to FALSE for the legacy application so idle sessions terminate sooner.
- C
Set LOCK_TIMEOUT to 900 for analyst users; set CLIENT_RESULT_PREFETCH_THREADS to 0 so inactive JDBC connections are closed automatically.
- D
Set STATEMENT_QUEUED_TIMEOUT_IN_SECONDS to 900 for analyst users; set AUTO_SUSPEND to 900 on the warehouse, which will close idle client sessions and stop long-running queries.
Show answer and explanation
Correct answer: A
Explanation
This scenario requires distinguishing among Snowflake timeout-related parameters that address different layers of behavior. For long-running SQL statements, the correct parameter is STATEMENT_TIMEOUT_IN_SECONDS, which aborts a statement that exceeds the configured execution time. For queued statements that have not started yet, Snowflake provides STATEMENT_QUEUED_TIMEOUT_IN_SECONDS, but that would not solve this scenario because the issue is statements running too long after they begin execution. To address legacy JDBC sessions remaining open without activity, CLIENT_SESSION_KEEP_ALIVE is the relevant parameter. When enabled, compatible clients can keep a session alive beyond normal inactivity; when disabled, idle sessions are not artificially prolonged by the client. Administrators should also understand that warehouse AUTO_SUSPEND governs compute suspension after inactivity, but it does not itself terminate sessions or replace statement timeout controls. Likewise, LOCK_TIMEOUT pertains only to waiting on locks, and USER_TASK_TIMEOUT_MS is for task execution. These distinctions are consistent with Snowflake documentation on parameters for statement execution limits, queued statement limits, task timeouts, lock wait behavior, and client session keep-alive settings.
- A. Correct.
Correct. STATEMENT_TIMEOUT_IN_SECONDS limits how long a statement is allowed to run before Snowflake aborts it, which directly addresses ad hoc queries that should stop after 15 minutes. CLIENT_SESSION_KEEP_ALIVE controls whether a client connection keeps the session alive indefinitely; setting it to FALSE prevents the client from maintaining idle sessions unnecessarily. This is the appropriate combination for controlling long-running statements and reducing the likelihood of idle client sessions keeping compute resources active longer than expected.
- B. Incorrect.
Incorrect. USER_TASK_TIMEOUT_MS applies to tasks, not interactive analyst queries or general SQL statements submitted by users. It would be relevant for task execution time limits, not ad hoc query governance. AUTOCOMMIT controls transaction behavior, not session idleness or whether JDBC sessions remain open.
- C. Incorrect.
Incorrect. LOCK_TIMEOUT determines how long a statement waits to acquire a lock before timing out; it does not limit total statement execution duration. CLIENT_RESULT_PREFETCH_THREADS affects result download concurrency/performance in some drivers, not whether an idle JDBC session remains alive.
- D. Incorrect.
Incorrect. STATEMENT_QUEUED_TIMEOUT_IN_SECONDS limits how long a statement can remain queued before execution starts; it does not stop queries that are already running for too long. AUTO_SUSPEND controls when an idle warehouse suspends, but it does not terminate client sessions, nor does it kill long-running statements already executing.