SnowPro Advanced Architect

By Snowflake · Question Mode

QuestionQ27

Performance, Cost, and Resource Optimization

A company has a very large table queried by two groups. Each group uses distinct WHERE clauses in its queries. The first group consistently obtains high-performance results, whereas the second group has poor query performance.

Query Profile analysis shows that the first group’s queries access only a limited number of micro-partitions, while the second group’s queries scan every micro-partition in the table.

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

Explanation

A materialized view can maintain a separate clustering key for the columns used by the poorly performing group’s filters, enabling micro-partition pruning for those queries without changing the base table layout that already supports the other group. Snowflake supports using materialized views to provide different clustering keys for the same source table.

Learn more

Community Discussion

No comments yet. Be the first to start the discussion!