ADA-C01 Question 351
Single answerAnalyze DML concurrency considerationsA retail company loads order events continuously into a Snowflake table named ORDER_FACT using multiple parallel ETL tasks. At the same time, an hourly deduplication process runs a large MERGE statement against ORDER_FACT to update existing rows and insert new ones from a staging table. Recently, the MERGE duration has become highly variable, and query history shows periods of waiting during peak ingest times. The administrator needs to reduce DML contention without changing the business logic of the load. Which action is the MOST appropriate?
- A
Cluster ORDER_FACT on the merge key so concurrent INSERT and MERGE statements no longer contend for table access
- B
Replace the MERGE with a CREATE OR REPLACE TABLE statement so the deduplication runs as DDL instead of DML
- C
Stage incoming data in a separate table and run the MERGE into ORDER_FACT during controlled windows, reducing overlap between concurrent writers
- D
Increase the virtual warehouse size for the MERGE so Snowflake can bypass transactional locking on heavily updated micro-partitions
Show answer and explanation
Correct answer: C
Explanation
Snowflake supports concurrent workloads well, but administrators should still analyze DML concurrency when multiple writers target the same table. INSERT operations and a large MERGE against the same table can create contention, leading to waiting and variable runtimes. The most effective administrative mitigation here is to reduce overlapping write activity on the same target object by ingesting into a staging table first, then applying a controlled MERGE into the destination table. This preserves business logic while reducing concurrent DML pressure. Clustering and warehouse scaling may improve performance characteristics, but they do not fundamentally resolve write-contention semantics. This approach is consistent with Snowflake guidance to design data pipelines so heavy UPDATE/DELETE/MERGE operations are coordinated rather than competing simultaneously on the same table.
- A. Incorrect.
Incorrect. Clustering can improve pruning and scan efficiency, but it does not eliminate DML concurrency conflicts. Concurrent DML against the same target table can still contend, especially when operations overlap on the same table or micro-partitions. A common misconception is that physical organization alone resolves transactional contention.
- B. Incorrect.
Incorrect. CREATE OR REPLACE TABLE would change object semantics, replace the table atomically, and is not an appropriate substitute for an in-place deduplication MERGE when concurrent ingest must continue against the same production object. It can also disrupt grants, dependencies, streams, and workload expectations depending on implementation. This changes the loading pattern and business process more substantially than required.
- C. Correct.
Correct. Separating ingest into a staging table and applying changes to the target table in controlled batches is a practical way to reduce concurrent write overlap on the same target. This aligns with Snowflake best practices for minimizing DML contention: avoid having multiple concurrent write operations target the same table when possible, and serialize or window heavy MERGE/UPDATE/DELETE activity against a shared target.
- D. Incorrect.
Incorrect. Increasing warehouse size may reduce execution time for the MERGE, but it does not remove transactional contention or locking behavior related to concurrent DML on the same table. Compute scaling helps performance, not concurrency semantics. This option reflects the common mistake of treating all waiting time as a compute problem.