SnowPro Advanced: Architect — Question 147

A company has a very large table that is being queried by two groups. Each group uses unique WHERE clauses in their queries. The first group consistently achieves high-performance results, while the second group experiences poor query performance.

Analysis of the Query Profile reveals that the first group’s queries access a limited number of micro-partitions, in contrast to the second group’s queries that scan all micro-partitions in the table.

What should be done to ensure that performance is MAXIMIZED for both sets of queries?

Answer options

• A. Create a materialized view of the table and assign a cluster key to the view that supports the WHERE clause of the poorly-performing queries.
• B. Clone the table and assign a cluster key to the cloned table that supports the WHERE clause of the poorly-performing queries.
• C. Modify the table using the CLUSTER BY parameter, including all columns that are referenced in both groups’ WHERE clauses.
• D. Have the second group increase the size of the virtual warehouse when running queries.

Correct answer: B

Explanation

Creating a cloned table with a clustering key tailored to the poorly-performing queries allows for optimized access to the necessary data, thus improving performance. The other options either do not directly address the issues of data access or rely on modifications that may not yield the desired performance improvements for the second group.