ADA-C01 Question 349
Single answer4.2 Manage DML locking and concurrency in Snowflake.A Snowflake administrator is troubleshooting an ELT pipeline that intermittently fails during peak load. Two independent tasks run every 5 minutes against the same large fact table. Task A performs a long-running DELETE to remove expired rows. Task B performs a MERGE to upsert new data. Analysts can still query the table successfully during these incidents, but one of the tasks often waits and eventually fails due to lock timeout. The business wants to reduce these failures without changing query results. Which action is the MOST appropriate?
- A
Increase the warehouse size for both tasks so Snowflake can bypass DML locks through more compute
- B
Reschedule the tasks so the DELETE and MERGE do not overlap on the same target table, or combine the changes into a single DML workflow
- C
Replace the DELETE with a SELECT statement in a transaction so readers and writers can run without contention
- D
Enable a higher isolation level for the table so concurrent DELETE and MERGE statements can both commit
- E
Suspend analyst queries on the table during the task window because SELECT statements are blocking the DML operations
Show answer and explanation
Correct answer: B
Explanation
This scenario tests practical understanding of Snowflake DML concurrency and locking. Snowflake allows concurrent reads and writes using snapshot-based isolation, which is why analyst queries continue successfully even while DML is running. However, concurrent write operations against the same table, such as DELETE, UPDATE, and MERGE, can block one another and lead to lock waits or lock timeout errors when they overlap. The best administrative response is usually to reduce overlapping write windows by staggering schedules, shortening transactions, or consolidating related changes into fewer DML statements. Adding compute can help performance but does not remove write-write contention. Likewise, reader queries generally do not block DML in Snowflake. These behaviors align with Snowflake guidance on transactions, locks, and concurrency, including best practices to avoid overlapping DML against the same objects when possible.
- A. Incorrect.
Incorrect. Increasing warehouse size can reduce execution time, but it does not eliminate DML locking behavior on the same target table. In Snowflake, concurrent writes to the same table can still contend, regardless of available compute. More compute may shorten the duration of lock holding, but it does not allow Snowflake to bypass transactional protections.
- B. Correct.
Correct. In Snowflake, concurrent DML statements that modify the same table can conflict because write operations require coordination and can wait on locks held by other transactions. Since analysts can still query the table, the issue is not read blocking but overlapping write activity. Staggering the schedules or redesigning the process so one workflow applies changes in a single operation is the most appropriate way to reduce lock timeout failures while preserving results.
- C. Incorrect.
Incorrect. A SELECT does not delete data, so this would change business outcomes rather than solve concurrency correctly. Although Snowflake supports concurrent reads with snapshot isolation semantics, replacing a DELETE with a SELECT does not address the requirement to remove expired rows.
- D. Incorrect.
Incorrect. Snowflake does not provide a user-controlled per-table isolation level setting that would allow conflicting concurrent write operations to the same table to commit independently. The misconception is assuming traditional database tuning knobs exist for lock behavior in the same way they do in some on-premises platforms.
- E. Incorrect.
Incorrect. Analysts' SELECT queries are not the main source of the blocking in this scenario. Snowflake supports non-blocking reads through MVCC-style snapshot behavior, so readers can typically continue while writers operate. The contention here is between DELETE and MERGE, both of which are DML write operations against the same target table.