ARA-C01 Question 174
Single answerData warehouseA retail company runs a nightly ETL process that loads 2 TB of sales data into Snowflake and then executes a series of transformation jobs before business users begin querying dashboards at 7:00 AM. The ETL workload is predictable and runs from 1:00 AM to 5:00 AM every day, while dashboard usage is light during that window but highly concurrent after 7:00 AM. The company wants to minimize cost without causing resource contention between ETL and BI workloads. Which Snowflake architecture choice is the BEST fit for this requirement?
- A
Use one large warehouse for both ETL and BI, and rely on Snowflake caching to prevent contention.
- B
Use separate warehouses: a warehouse sized for ETL that runs only during the nightly load window, and a different warehouse or multi-cluster warehouse for BI that is available for morning dashboard concurrency.
- C
Use a single multi-cluster warehouse for both ETL and BI so that clusters can scale out for all workloads at all times.
- D
Load the data into temporary tables first so that ETL does not consume resources needed by BI queries.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to separate ETL and BI workloads onto different warehouses. In Snowflake, compute and storage are decoupled, and virtual warehouses provide independent compute resources. This makes workload isolation a core architectural best practice for data warehouse design. For predictable batch ETL, a dedicated warehouse can be sized appropriately and configured with AUTO_SUSPEND and AUTO_RESUME to reduce cost outside the processing window. For dashboard and reporting users, a separate warehouse avoids contention; if user concurrency is high, a multi-cluster warehouse can be used for that BI workload specifically. Snowflake documentation and best practices consistently recommend using separate warehouses for different workloads to improve performance isolation, predictability, and cost management, while reserving multi-cluster warehouses mainly for handling concurrency rather than as a blanket solution for all mixed workloads.
- A. Incorrect.
Incorrect. A single warehouse means ETL and BI queries compete for the same compute resources whenever they overlap, which can increase queueing and unpredictable performance. Result cache and other caching features can help some repeated query patterns, but they do not eliminate compute contention for active ETL transformations and concurrent BI workloads. This option also does not optimize cost as well as suspending a dedicated ETL warehouse outside its known execution window.
- B. Correct.
Correct. Snowflake best practice is to isolate workloads with separate virtual warehouses when they have different performance and concurrency characteristics. A dedicated ETL warehouse can be sized for the nightly batch window and auto-suspended when not needed, which controls cost. A separate BI warehouse, potentially configured as multi-cluster if high concurrency requires it, protects dashboard users from ETL resource consumption and provides more predictable performance. This aligns with Snowflake's architecture for workload isolation and elastic compute.
- C. Incorrect.
Incorrect. A multi-cluster warehouse is primarily intended to address concurrency by adding clusters when many queries run at the same time. It is not the best default mechanism for mixing fundamentally different workloads if they can be separated. Using one multi-cluster warehouse for both ETL and BI may work, but it is generally less cost-efficient and offers less clean isolation than separate warehouses, especially when ETL occurs in a predictable time window and can be shut down afterward.
- D. Incorrect.
Incorrect. Temporary tables affect object lifecycle and session scope, not warehouse resource allocation. ETL statements that load or transform data still consume compute on the assigned warehouse. This option reflects a common misconception that table type changes warehouse performance characteristics in a way that isolates workloads.