COF-C03 Question 69
Single answerAd-hoc queriesA business analyst frequently runs unpredictable, one-time SQL statements to investigate sales anomalies in Snowflake. These ad-hoc queries are important, but they should not compete with the ETL workloads that load the SALES_FACT table every 15 minutes. The analyst also wants query performance to remain consistent during busy periods without requiring manual warehouse resizing. Which Snowflake solution best meets these requirements?
- A
Run the ad-hoc queries on the same warehouse as the ETL jobs and rely on Snowflake result cache to prevent contention
- B
Create a separate virtual warehouse for the analyst's ad-hoc queries and enable auto-suspend, auto-resume, and multi-cluster if concurrency increases
- C
Convert the SALES_FACT table to a temporary table during business hours so ad-hoc queries do not interfere with ETL processing
- D
Use a materialized view for every possible ad-hoc query pattern so the analyst does not need a warehouse
Show answer and explanation
Correct answer: B
Explanation
The best answer is to place ad-hoc queries on a separate virtual warehouse. In Snowflake, compute and storage are decoupled, so different warehouses can access the same underlying data without competing for the same compute resources. This makes separate warehouses ideal for isolating ETL, BI, and ad-hoc analytics workloads. Auto-suspend and auto-resume are recommended for sporadic workloads to reduce cost, while multi-cluster warehouses are useful when concurrent users or queries increase and consistent performance is required. This aligns with Snowflake best practices for workload isolation and warehouse management. Relevant Snowflake documentation includes guidance on virtual warehouses, multi-cluster warehouses, auto-suspend/auto-resume behavior, and caching behavior.
- A. Incorrect.
Incorrect. Using the same warehouse means the ad-hoc queries and ETL jobs share compute resources, which can cause resource contention and unpredictable performance. While Snowflake's result cache can speed up repeated identical queries when underlying data and session conditions allow, ad-hoc queries are often unique and may not benefit from cached results. Result cache is not a reliable isolation strategy for mixed workloads.
- B. Correct.
Correct. Separating workloads by assigning ad-hoc queries to a dedicated virtual warehouse is a Snowflake best practice for workload isolation. Auto-suspend and auto-resume help control cost for intermittent analyst activity, and a multi-cluster warehouse can help maintain performance when concurrency rises. This approach prevents analyst queries from competing with ETL compute resources while supporting consistent performance.
- C. Incorrect.
Incorrect. Temporary tables are session-scoped objects intended for short-lived data storage, not a mechanism for reducing compute contention between ETL and ad-hoc queries. Converting a production fact table to a temporary table would break persistence and shared access requirements and does nothing to isolate compute workloads.
- D. Incorrect.
Incorrect. Materialized views can improve performance for specific repeated query patterns, but they do not eliminate the need for compute resources because queries still require a virtual warehouse to execute. In addition, ad-hoc workloads are by nature unpredictable, so creating materialized views for every possible query pattern is impractical and costly.