SnowPro Advanced Data Engineer

By Snowflake · Question Mode

QuestionQ17

Design and Manage Snowflake Resources and Performance

A large, 200-column table holds two years of historical data. Queries against it filter on a single day. The following is the Query Profile:

Question Image

When run on a 2XL virtual warehouse, this query required more than one hour to finish. What will improve query performance the MOST?

Explanation

A date clustering key colocates rows with similar date values into micro-partitions, allowing Snowflake to prune the overwhelming majority of partitions for a single-day predicate. This reduces remote disk I/O and data scanned, which are the principal bottlenecks in this workload. Adding warehouse clusters primarily increases concurrency, and increasing warehouse size does not correct the lack of partition pruning.

Learn more

Community Discussion

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