ADA-C01 Question 352
Select 2Implement best practices for DML locking and concurrencyA data engineering team loads hourly order data into a large FACT_ORDERS table using a MERGE statement. At the same time, several downstream jobs run UPDATE statements on the same table to enrich rows with fraud scores and shipping attributes. During peak hours, some jobs spend significant time waiting, and administrators see lock-related contention in query history. The team wants to reduce DML locking conflicts without sacrificing data correctness. Which TWO actions are the best recommendations?
- A
Consolidate multiple row-by-row UPDATE statements into fewer set-based DML operations that touch the table less often
- B
Break the target table into separate tables by workflow and use scheduled tasks or streams to apply changes in sequence when possible
- C
Increase the warehouse size for the blocked sessions, because larger warehouses remove DML locks more quickly
- D
Replace MERGE and UPDATE statements with SELECT statements against a transient clone of the table, then swap the clone back in place after each change
- E
Enable a higher isolation level for the database so concurrent DML statements can update the same rows without waiting
Show answer and explanation
Correct answers: A, B
Explanation
The best answers are to reduce the frequency of writes and to redesign pipelines so multiple processes are not concurrently modifying the same table. In Snowflake, concurrent reads are highly scalable, but overlapping DML against the same object can still create lock waits or transactional contention. Best practices include using set-based operations instead of many small updates, batching changes, staging data before applying it, and sequencing writes with streams and tasks or other orchestration. Simply adding warehouse compute does not solve lock semantics, and Snowflake does not expose a database isolation setting that allows conflicting writes to bypass locking rules. These recommendations align with Snowflake guidance on transaction management, table-level write concurrency considerations, and general performance best practices for minimizing overlapping DML on the same target objects.
- A. Correct.
Correct. A key best practice for reducing DML contention in Snowflake is to minimize the number and duration of write transactions against the same object. Replacing many small or row-by-row updates with fewer set-based DML statements reduces how often locks are acquired and can significantly improve concurrency. This is a practical design improvement when multiple workflows are repeatedly updating the same large table.
- B. Correct.
Correct. Redesigning workflows so that separate processes do not repeatedly write to the same target table at the same time is an effective way to reduce lock contention. In Snowflake, using streams and tasks, staging tables, or workflow-specific tables and then applying changes in a controlled sequence is a common architectural best practice. This preserves correctness while avoiding overlapping DML on the same table.
- C. Incorrect.
Incorrect. Increasing warehouse size may reduce query execution time in some cases, but it does not eliminate DML locking behavior. Lock contention is primarily a concurrency and transactional design issue, not a compute sizing issue. A larger warehouse can sometimes shorten the time a lock is held if the statement finishes faster, but it is not a best-practice solution to blocking on its own.
- D. Incorrect.
Incorrect. Using a clone and swapping objects is not an appropriate replacement for routine concurrent DML updates in this scenario. Cloning is metadata-based and useful for environments, testing, or some maintenance workflows, but repeatedly cloning a heavily updated production table to avoid normal DML contention is operationally complex and not a standard best practice for concurrent enrichment pipelines. It can also introduce coordination and consistency challenges.
- E. Incorrect.
Incorrect. Snowflake does not provide a user-tunable higher isolation setting to allow conflicting concurrent DML statements to update the same rows without waiting. Snowflake manages transactions with its own concurrency control model, and the right mitigation is to redesign write patterns and reduce overlapping DML, not to change an isolation-level parameter.