Snowflake Exam ARA-R01 Topic 2 Question 23 Discussion

Actual exam question for Snowflake's ARA-R01 exam

Question #: 23
Topic #: 2

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?

ACreate 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.

BCreate 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.

CEnable 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.

DCreate 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.

Show Suggested Answer

Suggested Answer: C

Enabling the search optimization service on the table can improve the performance of queries that have selective filtering criteria, which seems to be the case here. This service optimizes the execution of queries by creating a persistent data structure called a search access path, which allows some micro-partitions to be skipped during the scanning process. This can significantly speed up query performance without increasing compute costs1.

Reference

* Snowflake Documentation on Search Optimization Service1.

by Laticia at Oct 23, 2024, 04:12 AM

Limited Time Offer

25%

Off

Get Premium ARA-R01 Questions as Interactive Web-Based Practice Test or PDF

Contribute your Thoughts:

Submit Cancel

Roslyn

6 days ago

D is an interesting option, but it might be overkill for a small or medium-sized warehouse. Why not try the caching approach first?

upvoted 0 times

...

Katlyn

12 days ago

Hmm, I'm not sure about C. Relying on the search optimization service to adjust the plan seems a bit risky.

upvoted 0 times

...

Denny

19 days ago

B looks good too, aligning the task schedule with the users' working hours is a nice touch.

upvoted 0 times

...

Chandra

27 days ago

Option A seems like the way to go! Precomputing the results and caching them is a smart way to save on compute costs.

upvoted 0 times

Beckie

9 days ago

User 2: That's a good point. It would definitely help with the performance issue without increasing costs.

upvoted 0 times

...

Keena

14 days ago

User 1: Option A seems like the way to go! Precomputing the results and caching them is a smart way to save on compute costs.

upvoted 0 times

...

Josefa

29 days ago

I agree with Eric. Option A seems like a cost-effective solution to improve query performance without increasing compute costs.

upvoted 0 times

...

Eric

30 days ago

I think option A is the best choice. Running the variations of the query before the users come in will help in caching the results and responding quickly.

upvoted 0 times

...