ADA-C01 Question 212
Select 2Analyze the impact on data loading and query processing based on warehouse sizes and typesA data engineering team uses a Snowflake X-SMALL standard warehouse to run a nightly COPY INTO job that loads 4 TB of compressed JSON files from cloud storage into a raw table. Recently, the load window has been exceeded. At the same time, analysts run complex dashboard queries on the same warehouse and report slower performance during the load. The administrator must improve load completion time and reduce the impact on query processing without changing the file source or table design. Which TWO actions would best address this requirement?
- A
Move the COPY INTO workload to a separate warehouse sized specifically for ingestion, so loading and dashboard queries use different compute resources.
- B
Resize the existing warehouse from X-SMALL to LARGE and keep both loading and dashboard queries on that same warehouse to guarantee both workloads improve without contention.
- C
Use a larger warehouse for the data load because additional compute can increase parallelism for file loading and transformation work during COPY INTO.
- D
Convert the existing warehouse to a multi-cluster warehouse in Auto-scale mode to speed up a single COPY INTO statement, because multi-cluster adds more compute to one query.
- E
Keep the warehouse size unchanged and rely on result cache to reduce the runtime of COPY INTO operations during repeated nightly loads.
Show answer and explanation
Correct answers: A, C
Explanation
The best answer is to both isolate the workloads and right-size compute for ingestion. In Snowflake, virtual warehouses provide compute isolation, so placing ETL/loading and BI workloads on separate warehouses prevents one from degrading the other. This is a core operational best practice for mixed workloads. In addition, increasing warehouse size can improve data load throughput because larger warehouses provide more compute for parsing and loading files, especially when many files are being processed. By contrast, multi-cluster warehouses are mainly intended for managing concurrency rather than improving the performance of a single query or single COPY INTO command. Snowflake documentation on virtual warehouses, warehouse sizing, multi-cluster behavior, and performance optimization supports these distinctions.
- A. Correct.
Correct. Separating ingestion and BI/query workloads onto different warehouses is a Snowflake best practice when you want workload isolation. Virtual warehouses do not share compute, so running COPY INTO on one warehouse and dashboard queries on another prevents the load job from consuming the same compute resources used by analysts. This directly reduces query slowdowns caused by concurrent mixed workloads.
- B. Incorrect.
Incorrect. Increasing the warehouse size can help performance, but keeping both workloads on the same warehouse does not eliminate resource contention between ingestion and dashboard queries. A larger single warehouse may reduce the symptom, but concurrent workloads can still interfere with each other. The claim that this will guarantee both workloads improve without contention is too strong and is not the best design compared with workload isolation.
- C. Correct.
Correct. Warehouse size affects available compute resources. For data loading, especially large COPY INTO operations with many files and possible parsing/transformation overhead, a larger warehouse can improve throughput by allowing more parallel processing. This is a practical way to shorten the load window when the current warehouse is undersized.
- D. Incorrect.
Incorrect. Multi-cluster warehouses are designed primarily to handle higher concurrency by adding clusters for multiple simultaneous queries, not to accelerate a single statement such as one COPY INTO operation. A common misconception is that multi-cluster automatically makes one load job faster; in practice, warehouse sizing is the relevant lever for single-query performance, while multi-cluster helps concurrent query workloads.
- E. Incorrect.
Incorrect. The result cache applies to eligible query result reuse, not to COPY INTO data loading operations. Re-running nightly loads of new files does not benefit from result cache in a way that would reduce COPY INTO execution time. This option reflects a misunderstanding of caching behavior in Snowflake.