ADA-C01 Question 213
Select 2Analyze the impact on data loading and query processing based on warehouse sizes and typesA data engineering team loads 2 TB of compressed JSON files into a staging table every night using COPY INTO. At the same time, BI users run dashboard queries with many joins and aggregations against curated tables. The team currently uses a single MEDIUM standard warehouse for both workloads and observes that the nightly load window is missed and dashboard latency becomes inconsistent during the load period. The administrator must improve both load throughput and query responsiveness while minimizing unnecessary cost. Which TWO actions are the most appropriate?
- A
Create a separate warehouse for data loading and size it up (for example, to LARGE) so more files can be processed in parallel, while keeping BI queries on a different warehouse.
- B
Convert the existing warehouse to a multi-cluster warehouse in Auto-scale mode and use it for both COPY INTO and dashboard queries, because multi-cluster improves the performance of a single large load job and complex queries equally.
- C
Move BI queries to a dedicated warehouse and consider increasing its size if individual dashboards are CPU/memory bound, because larger warehouses can reduce execution time for a single query.
- D
Keep one shared warehouse, but enable auto-suspend at 60 seconds; this will significantly increase parallel file loading throughput and reduce query queuing during concurrent workloads.
- E
Reduce the warehouse size to SMALL for the loading process, because smaller warehouses start faster and therefore complete large batch loads sooner than larger warehouses.
Show answer and explanation
Correct answers: A, C
Explanation
The key issue is workload contention: one warehouse is serving both nightly ingestion and interactive BI. In Snowflake, virtual warehouses are independent compute clusters, so separating workloads onto different warehouses is the primary way to isolate performance. For data loading with COPY INTO, larger warehouse sizes can improve throughput by providing more compute for parallel file processing, particularly when many files are loaded. For query processing, increasing warehouse size can improve the runtime of individual CPU- or memory-intensive queries, while multi-cluster warehouses are mainly designed to handle concurrent query workloads by adding clusters to reduce queuing. Therefore, the best solution is to separate ETL and BI onto dedicated warehouses and size each based on workload characteristics. This aligns with Snowflake guidance on warehouse sizing, concurrency, multi-cluster behavior, and workload isolation.
- A. Correct.
Correct. Separating ETL/loading from BI is a common Snowflake best practice to isolate workloads and prevent resource contention. For COPY INTO, warehouse size affects the compute resources available for loading and can increase parallelism, especially when loading many files. A larger warehouse can improve throughput for batch ingestion jobs, although the actual benefit depends on file count, file sizes, and transformation complexity.
- B. Incorrect.
Incorrect. Multi-cluster warehouses are primarily intended to address concurrency by adding clusters when many queries run at the same time. They do not typically speed up a single query, and they are not the primary mechanism for improving the performance of one large COPY INTO job. Using one multi-cluster warehouse for both ETL and BI may reduce some queuing, but it does not provide the clean workload isolation needed here and may add cost without targeting the root cause effectively.
- C. Correct.
Correct. A dedicated BI warehouse isolates dashboard workloads from ingest activity. If dashboard queries themselves are slow because each query needs more compute, increasing warehouse size can improve performance for individual query execution. This is distinct from multi-cluster scaling, which mainly helps concurrent query throughput rather than single-query speed.
- D. Incorrect.
Incorrect. Auto-suspend helps control cost when a warehouse is idle, but it does not materially increase loading parallelism or solve resource contention between simultaneous ETL and BI workloads. If both workloads run at the same time on one warehouse, they still compete for the same compute resources while the warehouse is active.
- E. Incorrect.
Incorrect. Although smaller warehouses may provision quickly, that does not make them better for large batch loads. In general, reducing size lowers available compute and can reduce loading throughput, especially for large volumes and parallel file processing. This option reflects a common misconception that startup speed is the main determinant of total batch duration.