ARA-R01 practice question 1 of 10

A Developer is having a performance issue with a Snowflake query. The query receives up to 10 different values for one parameter and then performs an…

Domain Architecting a Solution

A Developer is having a performance issue with a Snowflake query. The query receives up to 10 different values for one parameter and then performs an aggregation over the majority of a fact table. It then joins against a smaller dimension table. This parameter value is selected by the different query users when they execute it during business hours. Both the fact and dimension tables are loaded with new data in an overnight import process. On a Small or Medium-sized virtual warehouse, the query performs slowly. Performance is acceptable on a size Large or bigger warehouse. However, there is no budget to increase costs. The Developer needs a recommendation that does not increase compute costs to run this query. What should the Architect recommend?

Answer options

A: Create a task that will run the 10 different variations of the query corresponding to the 10 different parameters before the users come in to work. The query results will then be cached and ready to respond quickly when the users re-issue the query. Correct answer

B: Create a task that will run the 10 different variations of the query corresponding to the 10 different parameters before the users come in to work. The task will be scheduled to align with the users' working hours in order to allow the warehouse cache to be used.

C: Enable the search optimization service on the table. When the users execute the query, the search optimization service will automatically adjust the query execution plan based on the frequently-used parameters.

D: Create a dedicated size Large warehouse for this particular set of queries. Create a new role that has USAGE permission on this warehouse and has the appropriate read permissions over the fact and dimension tables. Have users switch to this role and use this warehouse when they want to access this data.

Correct Answer:

A. Create a task that will run the 10 different variations of the query corresponding to the 10 different parameters before the users come in to work. The query results will then be cached and ready to respond quickly when the users re-issue the query.

Explanation

The most effective solution that meets all constraints is to leverage Snowflake's result cache. Since the data is loaded overnight and remains static during business hours, the queries are deterministic. By creating a task to execute the 10 known query variations before users start their day, the results for these specific queries are stored in the global result cache. When a user subsequently runs one of these exact queries, Snowflake returns the cached result almost instantly, bypassing warehouse execution entirely. This dramatically improves performance for the users and consumes no virtual warehouse credits for those cached reads, thereby adhering to the strict budget constraint.

Why Incorrect

B: Running the task during business hours would compete for warehouse resources with users. The warehouse (local disk) cache helps speed up data scanning but does not eliminate query execution and compute costs like the result cache does. C: The search optimization service is designed for highly selective point-lookup queries, not for queries that perform aggregations over a majority of a table. It also incurs its own storage and compute costs, violating the budget constraint. D: This option directly contradicts the primary constraint that there is no budget to increase compute costs. A dedicated Large warehouse would be more expensive per unit of time than the current Small or Medium warehouses.

References

1. Result Cache Usage: Snowflake Documentation, "Using the Result Cache". This section explains: "When a query is executed, Snowflake checks for a previously executed query that is syntactically the same. If a matching query exists and the results are still cached, Snowflake uses the cached result set and does not execute the query." It also lists the conditions for using the cache, which are met in this scenario (e.g., underlying data has not changed).

2. Tasks for Automation: Snowflake Documentation, "Introduction to Tasks". This document confirms that tasks can be used to "execute a single SQL statement... on a schedule," which is the mechanism proposed in the correct answer for pre-populating the result cache.

3. Search Optimization Service Use Cases and Costs: Snowflake Documentation, "Search Optimization Service". This page clarifies that the service is for "improving the performance of selective point lookup queries" and explicitly states, "Snowflake charges a fee for the storage and compute resources used to... maintain the search access paths," making it unsuitable for this scenario's query type and cost constraints.

Keep practicing

Take the free ARA-R01 practice test

Ten exam-style questions with answers and explanations, plus the exam facts and study guides.

Start the free practice test