ADA-C01 Question 350
Single answerAnalyze DML concurrency considerationsA data engineering team loads updates into a large ORDERS table every 5 minutes using a MERGE statement. At the same time, several task-driven pipelines also run UPDATE and DELETE statements against ORDERS for data correction and retention processing. Recently, some task runs have been delayed, and query history shows longer execution times for the DML statements even though warehouse size has not changed. The team wants to reduce the impact of DML concurrency on this table without changing the business logic. Which action is the MOST appropriate?
- A
Increase the virtual warehouse size, because larger warehouses eliminate transactional contention between concurrent DML statements on the same table
- B
Reschedule the pipelines so that the MERGE, UPDATE, and DELETE operations against ORDERS run in separate time windows, reducing overlapping write activity on the same target table
- C
Convert the UPDATE and DELETE statements to SELECT statements against a clone of ORDERS, because clones allow write operations to bypass locking on the source table
- D
Enable a higher auto-suspend time on the warehouse, because keeping the warehouse warm prevents DML statements from waiting on table-level conflicts
Show answer and explanation
Correct answer: B
Explanation
This question tests practical understanding of DML concurrency in Snowflake. The key issue is not insufficient compute; it is overlapping write activity against the same table. Snowflake supports high concurrency for many workloads, but concurrent DML that modifies the same table, especially UPDATE, DELETE, and MERGE, can experience queuing or longer runtimes due to transaction coordination and locking behavior. As a result, a common administrative best practice is to reduce overlap among write-heavy jobs that target the same object.
Why option 2 is best: separating the execution windows for MERGE, UPDATE, and DELETE reduces direct contention on ORDERS while preserving the existing logic. This is often more effective than simply scaling the warehouse, because compute does not eliminate conflicts between concurrent writers.
Relevant Snowflake guidance includes understanding how transactions, locks, and table updates behave, as well as using Query History/monitoring to identify blocking and scheduling patterns. Documentation topics to review include Snowflake transactions, locking/concurrency behavior, MERGE semantics, and performance best practices for DML-heavy workloads.
- A. Incorrect.
Incorrect. Increasing warehouse size can improve compute performance, but it does not remove transactional coordination or write conflicts caused by concurrent DML against the same target table. In Snowflake, concurrent INSERT operations can scale well, but overlapping UPDATE, DELETE, and MERGE activity on the same table can still serialize or wait due to transaction and locking behavior.
- B. Correct.
Correct. This is the most appropriate operational change when the goal is to reduce DML concurrency issues without changing business logic. Staggering MERGE, UPDATE, and DELETE statements that target the same table reduces overlapping write transactions and therefore reduces blocking/waiting caused by concurrent modifications. This is a common best practice for heavily updated tables in Snowflake.
- C. Incorrect.
Incorrect. Zero-copy clones are separate objects created from the source table's micro-partitions, but converting DML to SELECTs against a clone does not preserve the original business logic of modifying ORDERS. Also, clones do not provide a mechanism for write operations on the original table to bypass concurrency constraints.
- D. Incorrect.
Incorrect. Auto-suspend settings affect warehouse lifecycle and startup behavior, not transactional contention on a table. Keeping a warehouse running may reduce resume latency, but it does not address DML statements waiting on concurrent changes to the same target table.