COF-C03 Question 70
Single answerAd-hoc queriesA data analyst runs unpredictable ad-hoc SQL queries throughout the day against a large SALES table in Snowflake. Sometimes the queries finish in seconds, but at other times they are much slower because they compete with scheduled ETL jobs running on the same warehouse. The analyst does not want to impact ETL performance, and the company wants to avoid paying for a larger warehouse all day long. What is the BEST solution?
- A
Create a separate virtual warehouse for ad-hoc queries and configure it to auto-suspend when idle and auto-resume when queries are submitted.
- B
Convert the SALES table to a temporary table so ad-hoc queries do not compete with ETL workloads.
- C
Enable Time Travel on the SALES table so Snowflake can serve ad-hoc queries from historical data without using warehouse resources.
- D
Increase the size of the existing warehouse and keep using it for both ETL and ad-hoc queries.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to place ad-hoc queries on a separate virtual warehouse with auto-suspend and auto-resume enabled. A core Snowflake design principle is the separation of compute and storage, which allows different workloads to run independently against the same data without interfering with each other. This is a common best practice for workload isolation: ETL, BI, and ad-hoc analytics often use separate warehouses. For sporadic or unpredictable usage, auto-suspend and auto-resume help minimize credit consumption by turning compute off when it is not needed and restarting it automatically when a query arrives. This approach is more aligned with Snowflake operational best practices than simply resizing a shared warehouse. Relevant Snowflake documentation includes guidance on virtual warehouses, warehouse management, auto-suspend/auto-resume behavior, and workload isolation using separate warehouses.
- A. Correct.
Correct. Snowflake separates compute from storage, so using a dedicated virtual warehouse for ad-hoc queries isolates compute resources from ETL workloads. This prevents resource contention between unpredictable analyst queries and scheduled data pipelines. Configuring auto-suspend and auto-resume is a cost-efficient best practice for intermittent ad-hoc workloads because the warehouse only consumes credits while running.
- B. Incorrect.
Incorrect. Temporary tables affect object persistence and session scope, not compute isolation. Query contention occurs because workloads are sharing the same virtual warehouse, not because of the table type. Converting a permanent table to a temporary table would also be impractical for shared production data.
- C. Incorrect.
Incorrect. Time Travel allows access to historical versions of data for recovery and analysis, but queries against Time Travel data still require compute from a virtual warehouse. It does not eliminate warehouse usage or isolate ad-hoc workloads from ETL jobs.
- D. Incorrect.
Incorrect. Increasing the size of the existing warehouse may improve performance, but both ETL and ad-hoc queries would still share the same compute cluster and continue to contend for resources. It also increases cost for the entire time the warehouse runs, which does not align with the requirement to avoid paying for larger compute all day.