COF-C03 Question 92
Single answerMicro-partitionsA retail company stores 4 years of order history in a Snowflake table named ORDERS with several billion rows. Analysts frequently run queries that filter by ORDER_DATE, but performance has become inconsistent. A Snowflake administrator reviews the workload and wants to improve pruning of micro-partitions for these date-filtered queries without changing the query results. Which action is the MOST appropriate?
- A
Define a clustering key on ORDER_DATE for the ORDERS table and allow Snowflake to maintain clustering depth over time
- B
Create a primary key on ORDER_DATE so Snowflake can use the key to physically reorder micro-partitions
- C
Convert the table to a temporary table so Snowflake rebuilds the micro-partitions when queries run
- D
Increase the virtual warehouse size because larger warehouses automatically reduce the number of micro-partitions scanned
Show answer and explanation
Correct answer: A
Explanation
Snowflake automatically divides table data into micro-partitions and maintains metadata about each partition, including ranges of column values. Query performance can improve significantly when Snowflake is able to prune micro-partitions that cannot contain matching rows. For very large tables with frequent selective filters on a column such as ORDER_DATE, clustering on that column can improve partition pruning by organizing related values closer together across micro-partitions. This is especially useful when natural load order no longer aligns with common filter predicates. In contrast, constraints such as primary keys do not physically reorder data in standard Snowflake tables, and warehouse resizing affects compute capacity rather than storage layout. Snowflake documentation on micro-partitions, clustering keys, and partition pruning supports this behavior and describes clustering as a targeted optimization for large tables with predictable filter patterns.
- A. Correct.
Correct. Snowflake stores table data in immutable micro-partitions and uses metadata such as min/max column values to prune partitions during query execution. If queries frequently filter on ORDER_DATE and pruning is inconsistent, defining a clustering key on ORDER_DATE can improve the physical organization of rows across micro-partitions over time, which helps Snowflake eliminate more irrelevant micro-partitions for date-based filters. This is the appropriate tuning action when a very large table has selective, repeated filter patterns on specific columns.
- B. Incorrect.
Incorrect. In Snowflake, primary key constraints are informational by default for standard tables and are not used to physically organize table storage. Creating a primary key on ORDER_DATE will not reorder existing micro-partitions or improve pruning. This option reflects a common misconception carried over from traditional relational databases where keys may influence access paths more directly.
- C. Incorrect.
Incorrect. Temporary tables are session-scoped objects intended for transient workloads, not a performance optimization mechanism for reorganizing micro-partitions in production analytics tables. Converting a large historical table to a temporary table would not be appropriate for shared analytics use and does not cause Snowflake to rebuild micro-partitions dynamically for better pruning.
- D. Incorrect.
Incorrect. Increasing warehouse size can improve query processing throughput by adding compute resources, but it does not automatically improve micro-partition pruning. Pruning depends on table storage organization and micro-partition metadata, not warehouse size. A larger warehouse may scan the same set of micro-partitions faster, but it does not reduce how many need to be scanned.