ADA-C01 Question 353
Select 3Implement best practices for DML locking and concurrencyA data engineering team runs several ELT pipelines that load into the same large FACT_SALES table throughout the day. Recently, two scheduled jobs began overlapping: one job performs a large MERGE into FACT_SALES every 15 minutes, while another job runs a DELETE followed by an INSERT to reprocess late-arriving rows for the same table. During peak hours, operators notice increasing lock wait time and occasional statement failures due to concurrent DML on FACT_SALES. The Snowflake administrator needs to reduce contention while preserving data correctness. Which TWO actions are the best practices to implement?
- A
Refactor the overlapping workflows so that DML against FACT_SALES is serialized or coordinated, for example by adjusting schedules or orchestrating dependencies to avoid simultaneous writes to the same table
- B
Replace the DELETE plus INSERT pattern with a single MERGE where possible, so changes to the target table are applied in one coordinated DML statement
- C
Increase the warehouse size for both jobs so Snowflake can bypass DML locks by finishing compute faster and allowing both write operations to proceed concurrently on the same target table
- D
Enable multi-cluster warehouses for the ETL warehouse, because additional clusters remove table-level write contention for concurrent DML statements on the same target table
- E
Move the reprocessing logic into a separate staging table first, then apply consolidated changes to FACT_SALES in fewer, controlled write operations
Show answer and explanation
Correct answers: A, B, E
Explanation
Snowflake supports ACID transactions and protects table consistency during DML operations such as INSERT, UPDATE, DELETE, and MERGE. When multiple pipelines perform overlapping writes to the same table, lock contention and blocked statements can occur. The best-practice response is usually architectural and operational: reduce simultaneous writes to the same target, consolidate changes into fewer DML statements, and stage intermediate work before applying final changes. In this scenario, serializing or coordinating workflows, replacing DELETE+INSERT with MERGE where appropriate, and using staging tables to reduce direct writes are the strongest choices. By contrast, increasing warehouse size or enabling multi-cluster warehouses may improve throughput for many workloads, but they do not remove transactional write contention on the same table. This aligns with Snowflake guidance around transactions, table-level locking behavior for DML, and designing pipelines to minimize conflicting concurrent modifications.
- A. Correct.
Correct. A core best practice for DML locking and concurrency in Snowflake is to avoid overlapping write operations against the same target table whenever possible. Coordinating schedules, adding orchestration dependencies, or otherwise serializing conflicting DML reduces lock contention and retries. This is especially important when separate pipelines target the same table with MERGE, UPDATE, or DELETE operations.
- B. Correct.
Correct. Consolidating multiple write steps into a single MERGE is a common best practice when the business logic allows it. A DELETE followed by an INSERT creates multiple write operations and can increase the lock window. Using one MERGE can reduce contention, simplify logic, and improve transactional consistency for late-arriving or changed rows.
- C. Incorrect.
Incorrect. A larger warehouse may reduce execution time, but it does not eliminate DML locking rules or allow conflicting concurrent writes to the same table to proceed without coordination. This option reflects the misconception that more compute removes transactional contention. Compute scaling can help performance, but it is not the primary fix for conflicting DML on the same object.
- D. Incorrect.
Incorrect. Multi-cluster warehouses improve concurrency for independent queries by adding compute clusters, but they do not remove write conflicts on the same target table. Table-level DML contention is a transactional behavior, not a warehouse concurrency problem that extra clusters can solve. This is a common misunderstanding when teams conflate query concurrency with concurrent write compatibility.
- E. Correct.
Correct. Staging changes outside the target table and then applying them in fewer, controlled operations is a strong design pattern. It reduces repeated direct writes to the production table, shortens the time locks may be held on the target, and makes it easier to coordinate one final MERGE or similar DML statement. This approach is commonly used to improve both reliability and maintainability in Snowflake ELT pipelines.