Snowflake Exam ARA-R01 Topic 4 Question 13 Discussion

Actual exam question for Snowflake's ARA-R01 exam

Question #: 13
Topic #: 4

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 Ashlyn at Jul 11, 2024, 10:51 AM

Limited Time Offer

25%

Off

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

Contribute your Thoughts:

Submit Cancel

Anissa

4 months ago

I can't believe they're even considering option A. Caching query results? What is this, the Stone Age? I'd rather just set up a dedicated warehouse and call it a day.

upvoted 0 times

...

Dominga

4 months ago

Option C seems like a bit of a wild card. I'm not convinced the search optimization service can really work its magic on this query. It's worth a shot, I guess, but I wouldn't put all my money on it.

upvoted 0 times

...

Scot

4 months ago

This query sounds like a real headache. I'd go with option D and create a dedicated size Large warehouse. After all, who needs to save money when you can just throw more hardware at the problem?

upvoted 0 times

Chandra

3 months ago

I agree, sometimes you just need to scale up to get the performance you need.

upvoted 0 times

...

Giuseppe

4 months ago

Option D seems like the best solution. Just throw more hardware at it.

upvoted 0 times

...

5 months ago

I think option A is the best recommendation. Running the variations of the query before users come in will help with performance.

upvoted 0 times

...