ADA-C01 Question 355
Single answerMonitor and manage transaction activityA Snowflake administrator receives an alert that a critical ETL pipeline is blocked while trying to UPDATE rows in a large fact table. Investigation shows another session started a transaction more than an hour ago, performed a DML statement against the same table, and then remained idle without committing. The administrator needs to identify the blocking transaction quickly and remove the blockage with minimal impact. Which action should the administrator take first?
- A
Query ACCOUNT_USAGE.QUERY_HISTORY to find the oldest query on the table, then suspend the warehouse running that query
- B
Use SHOW LOCKS or query the LOCK_WAIT_HISTORY/LOCKS-related metadata to identify the blocking transaction and then call SYSTEM$ABORT_TRANSACTION for that transaction
- C
Run ALTER TABLE ... SUSPEND RECLUSTER on the fact table so DML can proceed without waiting
- D
Use RESULT_SCAN on the blocked UPDATE statement to determine the transaction ID, then drop and recreate the table
Show answer and explanation
Correct answer: B
Explanation
When DML is blocked in Snowflake, the administrator should focus on lock and transaction metadata rather than warehouse-level actions. In a real-world incident, the goal is to identify which session/transaction is holding the lock and then take the least disruptive corrective action. Snowflake provides lock visibility through commands and metadata such as SHOW LOCKS and lock-related account usage/information schema views, and administrators can terminate the specific offending transaction with SYSTEM$ABORT_TRANSACTION. This is preferable to broad actions like suspending warehouses or making table-level structural changes, which do not directly address the blocking transaction. Best practice is to confirm the blocker, validate business impact, and abort only the specific transaction causing contention.
- A. Incorrect.
Incorrect. QUERY_HISTORY can help investigate statements, but finding the oldest query does not reliably identify the active blocking transaction. Suspending a warehouse does not safely resolve an open transaction issue and can cause broader disruption. The problem is transaction-level blocking, not simply warehouse compute activity.
- B. Correct.
Correct. The right approach is to identify the blocking lock/transaction using Snowflake lock monitoring metadata such as SHOW LOCKS and related history views, then abort the specific blocking transaction with SYSTEM$ABORT_TRANSACTION if needed. This targets the source of contention directly and minimizes impact compared with broader actions like suspending compute or recreating objects.
- C. Incorrect.
Incorrect. Reclustering is unrelated to transactional locks that block DML. Suspending reclustering may reduce background maintenance activity, but it does not clear a lock held by an uncommitted transaction.
- D. Incorrect.
Incorrect. RESULT_SCAN returns the result set of a previous query; it is not the correct tool for discovering transaction IDs for lock management. Dropping and recreating the table would be highly disruptive, risks data loss or application outages, and does not represent an appropriate first response to transaction blocking.