SnowPro Advanced: Architect — Question 115

An Architect is investigating a performance issue with an existing architecture and found a query where the Query Profile indicates a high number of bytes spilled to remote storage. The query includes a where clause that filters out a high percentage of records.

What actions can be taken to reduce the spillage to remote storage and improve the query performance? (Choose two.)

Answer options

• A. Increase the size of the virtual warehouse.
• B. Increase the maximum number of clusters for the virtual warehouse.
• C. Define a clustering key on columns used for selective filtering with a high clustering depth.
• D. Ensure primary keys and foreign keys are defined for large tables referenced by the query.
• E. Decrease the data retention period for tables referenced by the query.

Correct answer: A, C

Explanation

Increasing the size of the virtual warehouse (Option A) provides more resources for processing the query, which can help manage the workload and reduce spills to remote storage. Defining a clustering key (Option C) on columns that are heavily filtered can enhance performance by optimizing data access patterns. The other options either do not directly address the issue of spillage or may not lead to significant improvements in query performance.