SnowPro Advanced: Architect — Question 140

An Architect notes that a large table is having poor query performance. The Query Profile shows that the table is accessed by multiple teams within the company. Each team retrieved specific subsets of data using point-lookup queries on different columns.

What can be done to ensure that the query is performant for all teams?

Answer options

• A. Build multiple materialized views with cluster keys on each column. The teams can access the data by querying the materialized view that meets their requirements.
• B. Build multiple materialized views with cluster keys on each column. Snowflake will automatically replace the base table with the materialized view that optimizes the query.
• C. Use the search optimization service on the underlying table.
• D. Create clustering keys using the combination of columns used in the filter and lookup conditions.

Correct answer: C

Explanation

The correct answer is C because using the search optimization service can significantly enhance query performance by creating optimized structures for point-lookup queries on large tables. Options A and B suggest relying on materialized views, which may not address the performance issues effectively for all teams. Option D, while useful for clustering, does not specifically enhance performance for diverse query patterns across multiple teams.