ADA-C01 Question 275
Single answerCancel statements for single users or multiple usersA Snowflake administrator is responding to an incident in which several ad hoc queries submitted by a small group of analysts are consuming excessive warehouse resources and delaying a critical data load. The administrator wants to stop only the currently running statements for those affected users without disabling the users or suspending the warehouse. Which approach should the administrator use?
- A
Use the SYSTEM$CANCEL_ALL_QUERIES function for each affected username to cancel all currently running statements owned by those users.
- B
Execute ALTER USER <user_name> SET DISABLED = TRUE for each analyst, which immediately cancels all active statements for that user.
- C
Suspend the virtual warehouse currently used by the analysts so that only their running statements are canceled.
- D
Use SYSTEM$ABORT_TRANSACTION for each affected user to stop all current and future statements submitted by that user.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use SYSTEM$CANCEL_ALL_QUERIES for each affected user. This function is designed for targeted operational control when an administrator needs to cancel all currently executing queries for a single user. If multiple users are involved, the function can be executed once per user. This approach aligns with Snowflake administrative best practices: use the least disruptive action that resolves the incident. Disabling users is an access-management action, not a query-management tool; suspending a warehouse is broader and can interrupt unrelated workloads; and aborting a transaction is narrower in scope and transaction-specific rather than user-specific. Refer to Snowflake documentation for system functions related to query management, including SYSTEM$CANCEL_QUERY and SYSTEM$CANCEL_ALL_QUERIES, as well as guidance on warehouse administration and user management.
- A. Correct.
Correct. To cancel statements for a specific user, Snowflake provides the SYSTEM$CANCEL_ALL_QUERIES function, which cancels all currently running queries for the specified user. This is the most targeted option when the goal is to stop active work for one or more users without disabling accounts or affecting unrelated workloads on the warehouse. In practice, the administrator would invoke the function separately for each affected user.
- B. Incorrect.
Incorrect. Disabling a user prevents future logins, but it is not the appropriate method for selectively canceling currently running statements as an operational response. It is more disruptive than necessary and is intended for account access control rather than routine workload management.
- C. Incorrect.
Incorrect. Suspending a warehouse affects all workloads using that warehouse, not just the analysts' statements. In this scenario, the administrator specifically wants to avoid impacting the critical data load and other users, so warehouse suspension is too broad.
- D. Incorrect.
Incorrect. SYSTEM$ABORT_TRANSACTION is used to abort a specific transaction, not to cancel all running statements for a user. It does not operate as a user-level query cancellation mechanism, and it would not address multiple unrelated running statements across one or more users.