ADA-C01 Question 274
Single answerCancel statements for single users or multiple usersA Snowflake administrator receives an alert that several ad hoc queries submitted by analysts in the ANALYST role are consuming excessive warehouse resources and causing service delays for other teams. The administrator needs to stop currently running SQL statements for only two specific users, USER_A and USER_B, without affecting statements from other users on the same warehouse. Which approach should the administrator use?
- A
Use SHOW QUERIES to identify running statements for USER_A and USER_B, then execute SYSTEM$CANCEL_QUERY for each query ID that should be stopped.
- B
Suspend the warehouse that is executing the statements, then resume it after USER_A and USER_B disconnect.
- C
Use ALTER USER USER_A SET DISABLED = TRUE and ALTER USER USER_B SET DISABLED = TRUE to immediately cancel all currently running statements for those users only.
- D
Execute SYSTEM$ABORT_SESSION for USER_A and USER_B usernames directly; Snowflake will resolve the active sessions and cancel only their current statements.
Show answer and explanation
Correct answer: A
Explanation
When the goal is to stop active work for selected users without disrupting other users, Snowflake administrators should use the most targeted control available. For currently running SQL statements, that means identifying the relevant query IDs for those users and canceling them with SYSTEM$CANCEL_QUERY. This approach is preferable to suspending the warehouse, which affects everyone using it, or aborting entire sessions, which is broader than necessary. A common administrative workflow is to find active queries through query-monitoring metadata such as QUERY_HISTORY-related views/functions and then cancel the exact statements that are causing the issue. Snowflake also supports session termination with SYSTEM$ABORT_SESSION, but that requires session IDs and is appropriate when the entire session must be stopped rather than only one or more active statements.
- A. Correct.
Correct. To cancel statements for specific users without impacting others, the administrator should first identify the active query IDs for those users and then cancel the targeted statements individually with SYSTEM$CANCEL_QUERY. In practice, administrators commonly obtain currently running queries from ACCOUNT_USAGE/INFORMATION_SCHEMA views or query history-related functions and then cancel by query ID. This is the least disruptive option because it affects only the identified statements.
- B. Incorrect.
Incorrect. Suspending the warehouse would interrupt all running workloads on that warehouse, not just statements from USER_A and USER_B. This is a broader operational action and would affect other users sharing the warehouse, which the scenario explicitly says should be avoided.
- C. Incorrect.
Incorrect. Disabling a user prevents future logins, but it is not the correct targeted method for canceling currently running statements. Even if an administrator wanted to prevent new activity, this does not represent the standard or precise mechanism for stopping specific in-flight SQL statements.
- D. Incorrect.
Incorrect. SYSTEM$ABORT_SESSION operates on session IDs, not usernames. An administrator would first need to identify the relevant session IDs and then abort those sessions. Also, aborting sessions is more disruptive than canceling only the specific statements because it terminates the sessions rather than just stopping selected queries.