ARA-C01 Question 405
Single answerClustering keysA retail company stores 8 years of point-of-sale data in a large Snowflake table named SALES_FACT. The table receives continuous micro-batch inserts throughout the day. Most analytic queries filter on STORE_ID and a transaction date range of 7 to 30 days, and frequently aggregate by PRODUCT_ID. Query performance has degraded as the table grew, and the architect finds that many queries scan far more micro-partitions than expected. The team wants to improve pruning while keeping maintenance overhead reasonable. Which approach is the MOST appropriate?
- A
Define a clustering key on (STORE_ID, TRANSACTION_DATE) and monitor clustering depth to determine whether automatic clustering cost is justified.
- B
Define a clustering key on PRODUCT_ID only, because it is commonly used in GROUP BY clauses and will minimize aggregation cost.
- C
Define a clustering key on all frequently queried columns: STORE_ID, TRANSACTION_DATE, PRODUCT_ID, CASHIER_ID, and PROMOTION_ID, to maximize pruning opportunities.
- D
Do not define any clustering key, because Snowflake automatically sorts rows in each table by insertion order and will optimize pruning without additional design.
Show answer and explanation
Correct answer: A
Explanation
Snowflake clustering keys are intended for large tables where common query predicates do not align well enough with the table's natural micro-partition organization. In this scenario, the key issue is excessive micro-partition scanning for filters on STORE_ID and a recent TRANSACTION_DATE range. A clustering key on those filtering columns is the most appropriate design because it improves partition pruning for the dominant access pattern. PRODUCT_ID is used for aggregation, but aggregation columns are not typically the primary driver for clustering decisions unless they also appear in selective predicates. Likewise, defining a clustering key on too many columns is usually counterproductive and can raise maintenance costs without improving pruning. Snowflake documentation and best practices emphasize evaluating clustering through metrics such as clustering depth/information and balancing performance gains against reclustering cost, especially for tables with ongoing inserts and updates.
- A. Correct.
Correct. Clustering keys are most useful for very large tables where queries repeatedly filter on specific columns and current micro-partition metadata does not provide sufficient pruning. In this scenario, STORE_ID and TRANSACTION_DATE align directly with the primary selective predicates, so clustering on these columns can improve pruning of micro-partitions. Monitoring clustering depth and cost is also a best practice because continuous DML can increase reclustering overhead, and not every large table benefits enough to justify automatic clustering expense.
- B. Incorrect.
Incorrect. GROUP BY usage alone is not the main reason to choose a clustering key. Clustering is primarily intended to improve micro-partition pruning for selective filters and some join patterns, not to optimize aggregation in isolation. Since the workload is driven by predicates on STORE_ID and TRANSACTION_DATE, clustering only on PRODUCT_ID would likely provide limited benefit.
- C. Incorrect.
Incorrect. Adding many columns to a clustering key is a common misconception. Overly wide clustering keys can reduce effectiveness, increase maintenance cost, and make the clustering strategy less aligned to the most selective access patterns. Best practice is to choose a small number of columns that reflect common filtering patterns and provide meaningful pruning.
- D. Incorrect.
Incorrect. Snowflake automatically organizes data into micro-partitions, but it does not guarantee an insertion-order layout that remains optimal for query pruning as data volume and DML grow. For very large tables with predictable filter patterns, explicitly defining a clustering key can significantly improve pruning when natural clustering is insufficient.