Exam DEA-C02 Topic 1 Question 331 Discussion

Actual exam question for Snowflake's DEA-C02 exam
Question #: 331
Topic #: 1

A data warehousing team is experiencing inconsistent query performance on a large fact table C SALES FACT) that is updated daily. Some queries involving complex joins and aggregations take significantly longer to execute than others, even when run with the same virtual warehouse size. You suspect that the query result cache is not being effectively utilized due to variations in query syntax and the dynamic nature of the data'. Which of the following strategies could you implement to maximize the effectiveness of the query result cache and improve query performance consistency? Assume virtual warehouse size is large and the data is skewed across days.

A. Implement query tagging to standardize query syntax. By applying consistent tags to queries, you can ensure that similar queries are recognized as identical and reuse cached results. B. Use stored procedures with parameters to encapsulate the queries. This will ensure that the query syntax is consistent, regardless of the specific parameters used. C. Create a separate virtual warehouse specifically for running these queries. This will isolate the cache and prevent it from being invalidated by other queries. D. Implement a data masking policy on the 'SALES_FACT table. Data masking will reduce the size of the data that needs to be cached, improving cache utilization. E. Optimize the 'SALES_FACT table by clustering it on the most frequently used filter columns and enabling automatic clustering. This will improve data locality and reduce the amount of data that needs to be scanned.

Suggested Answer: B,E Vote an answer

Using stored procedures with parameters (B) standardizes query syntax, making it easier for Snowflake to recognize and reuse cached results. Optimizing the 'SALES_FACT table with clustering (E) improves data locality, reducing the amount of data that needs to be scanned and potentially cached. Query tagging (A) can help, but might not address subtle syntax differences. Creating a separate virtual warehouse (C) doesn't guarantee better cache utilization; it just isolates it. Data masking (D) is primarily for security, not caching, although it might indirectly reduce data size.

by Nicholas at Jun 23, 2026, 08:24 AM

Limited Time Offer

15%

Off

Get Premium DEA-C02 Questions as Interactive Self Test Engine or PDF

Comments

0 Happy Clients

0 Shares

0 Demo Downloads

10 Years in Business