COF-C03 Question 93
Single answerMicro-partitionsA retail company stores five years of sales data in a large Snowflake table named SALES_FACT. Analysts most frequently run queries that filter on ORDER_DATE ranges such as the last 7, 30, or 90 days. The data was loaded over time from many source files, and query performance has become inconsistent. An architect wants to improve query efficiency by helping Snowflake skip scanning unnecessary micro-partitions when these date filters are used. Which action would best address this requirement?
- A
Define a clustering key on ORDER_DATE for SALES_FACT so Snowflake can better organize overlapping micro-partition values and improve pruning for date-range filters.
- B
Create a materialized view on SALES_FACT without any filter so Snowflake automatically eliminates all irrelevant micro-partitions for ORDER_DATE queries.
- C
Increase the warehouse size used for the queries so Snowflake can repartition the table into fewer micro-partitions during execution.
- D
Convert SALES_FACT into a temporary table so Snowflake refreshes micro-partition metadata more frequently for recent data.
Show answer and explanation
Correct answer: A
Explanation
Snowflake stores table data in immutable micro-partitions and automatically captures metadata about each micro-partition, including column ranges and other statistics. Query pruning works best when the filtered column's values are grouped such that fewer micro-partitions contain overlapping ranges. In this scenario, the common access pattern is filtering by ORDER_DATE, and the inconsistent performance suggests poor clustering on that column due to loading from many files over time. A clustering key on ORDER_DATE is the best fit because it helps Snowflake maintain better co-location of similar date values and improves partition pruning for range queries. Snowflake documentation on micro-partitions, clustering keys, and partition pruning emphasizes that clustering is most beneficial for large tables with selective filters on columns whose values become poorly ordered over time. Warehouse size affects compute throughput, not persistent micro-partition layout.
- A. Correct.
Correct. Snowflake automatically stores data in micro-partitions and maintains metadata such as the minimum and maximum values for columns in each micro-partition. When data is not well ordered for a frequently filtered column like ORDER_DATE, values can overlap heavily across many micro-partitions, reducing pruning effectiveness. Defining a clustering key on ORDER_DATE can improve clustering depth over time and help Snowflake prune more micro-partitions for range predicates, especially on very large tables with common selective filters.
- B. Incorrect.
Incorrect. A materialized view can improve performance for some repeated query patterns, but simply creating one without a specific aggregation, projection, or filter does not directly solve the underlying micro-partition pruning problem on the base table. It also does not mean Snowflake will eliminate all irrelevant micro-partitions for all ORDER_DATE queries. This option reflects the misconception that materialized views are a general replacement for clustering.
- C. Incorrect.
Incorrect. Increasing warehouse size adds compute resources and may reduce elapsed time for scans, but it does not reorganize table storage or repartition persistent table data during query execution. Micro-partitions are part of Snowflake's storage layer, and warehouse sizing does not change how data is clustered for pruning.
- D. Incorrect.
Incorrect. Temporary tables differ mainly in lifecycle and session scope, not in how micro-partition metadata is maintained for pruning. Converting a permanent fact table to a temporary table would not improve date-range pruning and would be operationally inappropriate for a shared production analytics dataset.