DEA-C02 Exam Dumps | Your team is developing a set of complex analytical queries in Snowflake that involve multiple joins,

<< Prev Question Next Question >>

Question 6/149

Your team is developing a set of complex analytical queries in Snowflake that involve multiple joins, window functions, and aggregations on a large table called 'TRANSACTIONS. These queries are used to generate daily reports. The query execution times are unacceptably high, and you need to optimize them using caching techniques. You have identified that the intermediate results of certain subqueries are repeatedly used across different reports, but they are not explicitly cached. Given the following options, which combination of strategies would MOST effectively utilize Snowflake's caching capabilities to optimize these analytical queries and improve report generation time?

A. Use temporary tables to store the intermediate results of the subqueries. These tables will be automatically cached by Snowflake and can be reused by subsequent queries within the same session.

B. Create common table expressions (CTEs) for the subqueries and reference them in the main query. CTEs will force Snowflake to cache the results of the subqueries, improving performance.

C. Utilize the "RESULT_SCAN' function in conjunction with the query ID of the initial subquery execution to explicitly cache and reuse the results in subsequent queries. This approach requires careful management of query IDs.

D. Create materialized views that pre-compute the intermediate results of the subqueries. This will allow Snowflake to automatically refresh the materialized views when the underlying data changes and serve the results directly from the cache.

E. Consider using 'CACHE RESULT for particularly expensive subqueries or views. This is a hint to snowflake to prioritize caching the result set for future calls.

Question 6/149

LEAVE A REPLY

Download PDF File