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

Lizette

16 days ago

This question really separates the Snowflake architects from the Snowflake enthusiasts. Better bring your A-game!

upvoted 0 times

Shalon

2 days 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

19 days 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

Steffanie

8 days 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

11 days ago

upvoted 0 times

...

Roslyn

29 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

Thurman

3 days ago

upvoted 0 times

...

Noemi

9 days ago

upvoted 0 times

...

Katlyn

1 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

1 days 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

8 days 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

14 days 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

1 months ago

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

upvoted 0 times

Oretha

11 days ago

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

upvoted 0 times

...

Starr

12 days ago

A looks like a good solution, caching the results beforehand will definitely speed up the query performance.

upvoted 0 times

...

Jamal

15 days ago

upvoted 0 times

...

Tien

18 days ago

upvoted 0 times

...

Chandra

2 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

17 days ago

User 4: Pre-running the queries and caching the results is a clever workaround for the performance problem.

upvoted 0 times

...

Myong

21 days ago

User 3: I agree. It's a practical solution that can make a big difference in query speed.

upvoted 0 times

...

Beckie

1 months ago

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

upvoted 0 times

...

Keena

1 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

2 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

2 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

...