Deal of The Day! Hurry Up, Grab the Special Discount - Save 25% - Ends In 00:00:00 Coupon code: SAVE25
Welcome to Pass4Success

- Free Preparation Discussions
Mail Us support@pass4success.com
Location US
Mob_nav_barsMENU

Snowflake Exam ARA-R01 Topic 2 Question 23 Discussion

Actual exam question for Snowflake's ARA-R01 exam
Question #: 23
Topic #: 2
[All ARA-R01 Questions]

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?

Show Suggested Answer Hide 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
Lizette
4 months ago
This question really separates the Snowflake architects from the Snowflake enthusiasts. Better bring your A-game!
upvoted 0 times
Katy
3 months ago
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.
upvoted 0 times
...
Claribel
3 months ago
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.
upvoted 0 times
...
Shalon
4 months ago
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.
upvoted 0 times
...
...
Leonora
4 months ago
I bet the developer is wishing they had a magic 'make-query-fast' button. Alas, there's no such thing in Snowflake!
upvoted 0 times
Jackie
3 months ago
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.
upvoted 0 times
...
Lai
3 months ago
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.
upvoted 0 times
...
Steffanie
4 months ago
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.
upvoted 0 times
...
Kris
4 months ago
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.
upvoted 0 times
...
...
Roslyn
4 months 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
Olive
3 months ago
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.
upvoted 0 times
...
Salena
3 months ago
A is a good idea, it can help improve performance without increasing costs.
upvoted 0 times
...
Thurman
4 months ago
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.
upvoted 0 times
...
Noemi
4 months ago
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.
upvoted 0 times
...
...
Katlyn
5 months 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
Elinore
3 months ago
A: Yeah, I see your point. Option C does seem risky. It's probably best to go with a more proactive approach like A or B.
upvoted 0 times
...
Teresita
4 months ago
B: I agree, option B could also work well. Scheduling the task to align with users' working hours makes sense.
upvoted 0 times
...
Colene
4 months ago
A: I think option A is a good idea. Running the variations of the query before users come in could really help with performance.
upvoted 0 times
...
...
Denny
5 months ago
B looks good too, aligning the task schedule with the users' working hours is a nice touch.
upvoted 0 times
Oretha
4 months ago
B looks good too, aligning the task schedule with the users' working hours is a nice touch.
upvoted 0 times
...
Starr
4 months ago
A looks like a good solution, caching the results beforehand will definitely speed up the query performance.
upvoted 0 times
...
Jamal
4 months ago
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.
upvoted 0 times
...
Tien
4 months ago
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.
upvoted 0 times
...
...
Chandra
5 months 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
Katy
4 months ago
User 4: Pre-running the queries and caching the results is a clever workaround for the performance problem.
upvoted 0 times
...
Myong
4 months ago
User 3: I agree. It's a practical solution that can make a big difference in query speed.
upvoted 0 times
...
Beckie
5 months ago
User 2: That's a good point. It would definitely help with the performance issue without increasing costs.
upvoted 0 times
...
Keena
5 months 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
5 months 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
5 months 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
...

Save Cancel