COF-C03 Question 282
Single answerClustering keysA retail analytics team stores 4 years of sales data in a large Snowflake table named FACT_SALES. The table receives continuous INSERTs throughout the day. Most high-priority dashboard queries filter on SALE_DATE ranges and REGION_ID, and query performance has become inconsistent as the table has grown. The team wants to improve partition pruning for these queries without unnecessarily increasing maintenance cost. Which action is the MOST appropriate?
- A
Define a clustering key on (SALE_DATE, REGION_ID) after confirming these columns are commonly used in selective filters on the large table.
- B
Create a primary key on SALE_DATE and REGION_ID so Snowflake physically orders rows by those columns.
- C
Convert FACT_SALES to a temporary table so Snowflake automatically reclusters data during each session.
- D
Create a clustering key on every column used by any report to maximize pruning across all workloads.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to define a clustering key on the columns most commonly used in selective filters for this large, frequently queried table: SALE_DATE and REGION_ID. Snowflake stores table data in micro-partitions and uses metadata about the values in those partitions to perform partition pruning. As continuous inserts accumulate over time, data can become less well organized for the dominant query patterns, causing pruning efficiency and performance consistency to degrade. Clustering keys help Snowflake maintain better co-location of related values across micro-partitions, which can improve pruning for range and equality predicates on the clustering columns. However, clustering is not free: maintenance and reclustering consume resources, so best practice is to use clustering only when there is a clear benefit and to keep the key focused on the most valuable columns rather than many columns. Snowflake documentation on clustered tables and micro-partitions emphasizes evaluating query patterns, table size, cardinality, and maintenance cost when choosing clustering keys.
- A. Correct.
Correct. Clustering keys are intended for large tables where query predicates frequently filter on specific columns. Because the main workload filters on SALE_DATE ranges and REGION_ID, defining a clustering key on these columns can improve micro-partition pruning and make query performance more consistent. This aligns with Snowflake guidance to choose clustering keys based on common, selective filter columns and to weigh the benefit against ongoing reclustering costs.
- B. Incorrect.
Incorrect. In Snowflake, primary key constraints are informational by default on standard tables and do not physically order data or control micro-partition layout. A common misconception is to assume relational constraints automatically optimize storage layout, but clustering keys, not primary keys, are the feature used to influence co-location of data values for pruning.
- C. Incorrect.
Incorrect. Temporary tables do not provide special automatic reclustering behavior. Changing the table type would not address the underlying micro-partition organization for a production fact table with ongoing inserts. This option reflects a misunderstanding of table types versus storage optimization features.
- D. Incorrect.
Incorrect. Defining a clustering key on too many columns is generally a poor practice because it can increase maintenance cost and reduce effectiveness. Snowflake recommends selecting a small number of columns that match the most important query predicates. Trying to optimize for every report often leads to unnecessary reclustering overhead with limited benefit.