What can the Snowflake Administrator do to optimize the use of persisted query results whenever possible?

An organization’s sales team leverages this Snowflake query a few times a day:

SELECT CUSTOMER ID, CUSTOMER_NAME, ADDRESS, PHONE NO FROM CUSTOMERS WHERE LAST UPDATED BETWEEN TO_DATE (CURRENT_TIMESTAMP) AND (TO_DATE (CURRENT_TIMESTAMP) -7);

What can the Snowflake Administrator do to optimize the use of persisted query results whenever possible?
A . Wrap the query in a User-Defined Function (UDF) to match syntax execution.
B . Assign everyone on the sales team to the same virtual warehouse.
C . Assign everyone on the sales team to the same security role.
D . Leverage the CURRENT_DATE function for date calculations.

View Answer

Answer: D

Explanation:

According to the web search results from my predefined tool search_web, one of the factors that affects the reuse of persisted query results is the exact match of the query syntax1. If the query contains functions that return different values for successive runs, such as CURRENT_TIMESTAMP, then the query will not match the previous query and will not benefit from the cache. To avoid this, the query should use functions that return consistent values for the same day, such as CURRENT_DATE, which returns the current date without the time component2.

Option A is incorrect because wrapping the query in a UDF does not guarantee the syntax match, as the UDF may also contain dynamic functions.

Option B is incorrect because the virtual warehouse does not affect the persisted query results, which are stored at the account level1.

Option C is incorrect because the security role does not affect the persisted query results, as long as the role has the necessary privileges to access the tables and views used in the query1.

1: Using Persisted Query Results | Snowflake Documentation

2: Date and Time Functions | Snowflake Documentation