ARA-C01 Question 173
Single answerData warehouseA retail company uses Snowflake as its enterprise data warehouse. The ELT pipeline loads sales data continuously into a large fact table. Finance analysts run many short dashboard queries throughout the day, while a data science team runs a few long-running exploratory queries against the same database. During month-end close, dashboard users report inconsistent response times because some queries wait for compute resources. The architect must improve concurrency for dashboard workloads without significantly increasing cost for all workloads. Which solution best meets these requirements?
- A
Move both dashboard and data science workloads to a single larger warehouse so all queries finish faster
- B
Create a dedicated warehouse for dashboard queries and enable multi-cluster scaling on that warehouse
- C
Convert the fact table to a temporary table during month-end close to reduce contention
- D
Enable Search Optimization Service on the fact table to eliminate warehouse queueing
Show answer and explanation
Correct answer: B
Explanation
The best answer is to isolate the dashboard workload on its own virtual warehouse and enable multi-cluster scaling for that warehouse. In Snowflake, virtual warehouses provide compute isolation, so separating BI dashboards from data science workloads prevents resource contention between short, latency-sensitive queries and long-running exploratory queries. Multi-cluster warehouses are specifically designed to handle periods of high concurrency by adding clusters as needed, which helps reduce or eliminate queueing for dashboard users. This approach is usually more cost-effective than simply upsizing a shared warehouse for all workloads, because additional compute is targeted to the workload and time periods that need it. By contrast, Search Optimization Service improves certain query access patterns but does not address warehouse-level concurrency, and table types such as temporary tables are unrelated to compute contention. This aligns with Snowflake best practices around workload isolation using separate warehouses and using multi-cluster warehouses for concurrent analytics workloads.
- A. Incorrect.
Incorrect. Increasing the size of a single warehouse can improve individual query performance, but it does not isolate workloads. Long-running data science queries can still consume cluster resources and affect dashboard concurrency. A larger single warehouse may also increase cost continuously, even when additional compute is only needed for the dashboard workload during peak periods.
- B. Correct.
Correct. Separating dashboard queries onto their own warehouse isolates the BI workload from long-running exploratory queries. Enabling multi-cluster scaling on that warehouse allows Snowflake to add clusters when concurrency increases, reducing queueing for many short queries. This is a standard architecture pattern for mixed workloads in a data warehouse: isolate workloads by warehouse and use multi-cluster warehouses for high-concurrency user-facing analytics.
- C. Incorrect.
Incorrect. Table type does not address warehouse compute contention. Temporary tables are session-scoped objects and are not a mechanism for improving concurrent query execution against shared compute resources. Changing a central fact table to temporary would also be operationally inappropriate for an enterprise data warehouse because other users and sessions would lose access.
- D. Incorrect.
Incorrect. Search Optimization Service can improve the performance of selective queries on supported access patterns, but it does not solve warehouse queueing caused by concurrent demand for compute. The issue described is inconsistent response time due to queries waiting for resources, which is addressed by warehouse sizing, workload isolation, and multi-cluster behavior rather than search access path optimization.