COF-C03 Question 283
Single answerClustering keysA retail company stores 4 years of order history in a large Snowflake table named SALES_FACT. Most analytics queries filter by ORDER_DATE ranges such as the last 7, 30, or 90 days, and often also filter by REGION_ID. The table receives continuous inserts throughout the day. Query performance has become inconsistent because many micro-partitions must be scanned. The team wants to improve pruning for these common filters while minimizing unnecessary maintenance overhead. Which action is the BEST recommendation?
- A
Define a clustering key on (ORDER_DATE, REGION_ID) for SALES_FACT and monitor clustering depth over time
- B
Create a primary key on ORDER_DATE so Snowflake can physically sort the table by date
- C
Create a search optimization service on every column in SALES_FACT instead of using clustering keys
- D
Recreate SALES_FACT as a temporary table so new micro-partitions are automatically ordered by ORDER_DATE
Show answer and explanation
Correct answer: A
Explanation
The best answer is to define a clustering key that matches the table's common filtering patterns. In Snowflake, clustering keys help improve micro-partition pruning by organizing data so related values are grouped together. This is especially useful for large tables with frequent selective filters or range predicates, such as recent ORDER_DATE windows and secondary filtering by REGION_ID. Snowflake documentation and best practices emphasize using clustering selectively on large tables where pruning benefits justify maintenance costs. Primary keys do not control physical storage layout in Snowflake, and Search Optimization Service is intended for different access patterns rather than broad replacement of clustering. Monitoring clustering metrics such as clustering depth or clustering information helps determine whether the key continues to provide value as new data is inserted.
- A. Correct.
Correct. Clustering keys are used to co-locate rows with similar key values in the same micro-partitions, which can improve partition pruning for large tables with selective filters. Because the most common predicates are on ORDER_DATE and often REGION_ID, a clustering key on (ORDER_DATE, REGION_ID) aligns with the workload. Monitoring clustering information over time is also a best practice because ongoing inserts can reduce clustering quality and may require automatic or manual reclustering considerations.
- B. Incorrect.
Incorrect. In Snowflake, primary key constraints are informational by default for standard tables and do not physically organize data or enforce sort order for query pruning. A common misconception is to assume relational constraints affect storage layout as they might in other platforms. They do not replace clustering keys.
- C. Incorrect.
Incorrect. Search Optimization Service can accelerate certain highly selective point-lookups and search patterns, but it is not a blanket replacement for clustering on large fact tables with common range filters such as date ranges. Applying it to every column would usually add unnecessary cost and does not directly address the primary pattern of improving micro-partition pruning for ORDER_DATE range predicates.
- D. Incorrect.
Incorrect. Temporary tables do not automatically maintain physical ordering by a specific column. Snowflake manages data in micro-partitions regardless of whether a table is permanent, transient, or temporary. Changing the table type would not solve the pruning issue and does not provide clustering behavior.