ADA-C01 Question 348
Single answer4.2 Manage DML locking and concurrency in Snowflake.A data engineering team loads a large FACT_SALES table every 5 minutes using a recurring MERGE statement. At the same time, analysts run long SELECT queries against FACT_SALES, and a separate process performs ad hoc UPDATE statements to correct recent rows. Recently, the MERGE jobs have started timing out during peak business hours, while the SELECT queries continue to complete successfully. The Snowflake administrator needs to reduce DML contention without interrupting analyst reporting. Which action is the BEST choice?
- A
Increase the size of the virtual warehouse running the MERGE jobs so the locks are released faster and the timeouts stop
- B
Clone FACT_SALES before each MERGE, apply the changes to the clone, and swap objects after every load to avoid all locking
- C
Review and adjust the transaction design so ad hoc UPDATE statements are shorter and committed promptly, and set an appropriate LOCK_TIMEOUT for the MERGE sessions
- D
Disable Time Travel on FACT_SALES so long-running SELECT queries no longer see older table versions and stop blocking the MERGE
Show answer and explanation
Correct answer: C
Explanation
The key fact pattern is that SELECT queries continue to succeed while MERGE jobs time out. In Snowflake, readers and writers are largely isolated through multiversion concurrency control (MVCC), so read activity is usually not the source of blocking for DML. The likely contention is between the recurring MERGE and the ad hoc UPDATE operations, both of which are write transactions against the same table. The best administrative response is to minimize the duration and overlap of write transactions: ensure UPDATE statements are narrowly scoped, commit promptly, and avoid leaving transactions open longer than necessary. The LOCK_TIMEOUT session parameter can also be tuned so MERGE operations do not wait indefinitely or fail too aggressively. Snowflake documentation and best practices around transactions, locks, and concurrency emphasize short transactions and awareness of concurrent DML on the same object. Warehouse scaling can improve performance but does not fundamentally solve lock contention, and Time Travel settings are unrelated to write locking behavior.
- A. Incorrect.
Incorrect. Increasing warehouse size can reduce execution time for compute-intensive work, but it does not eliminate DML locking conflicts by itself. In Snowflake, SELECT queries use MVCC and generally do not block DML, so the primary issue here is likely contention among concurrent write operations such as MERGE and UPDATE. More compute may help a query finish sooner, but it is not the best administrative action for managing lock contention.
- B. Incorrect.
Incorrect. Zero-copy cloning and object swapping are useful in some deployment or refresh patterns, but cloning a large production table before every recurring MERGE is operationally heavy and not the standard solution for DML lock contention. It also introduces additional complexity around dependent objects, privileges, and object identity. This option overengineers the problem rather than addressing the source of concurrent write conflicts.
- C. Correct.
Correct. In Snowflake, concurrent DML statements that modify the same table can contend for locks, while readers typically continue because Snowflake uses multiversion concurrency control. A common cause of DML timeout issues is long-running or poorly scoped write transactions that hold locks longer than necessary. Best practice is to keep transactions short, commit promptly, avoid unnecessary overlap in write windows, and use the LOCK_TIMEOUT parameter so sessions fail or wait according to business requirements. This directly addresses MERGE-versus-UPDATE contention without affecting SELECT workloads.
- D. Incorrect.
Incorrect. Disabling Time Travel does not prevent long-running SELECT statements from accessing a consistent snapshot, nor is it the mechanism for resolving DML lock contention. Snowflake's read consistency is based on MVCC, and SELECT statements generally do not block writes. This option reflects a misunderstanding of how Time Travel and read concurrency relate to DML locking.