ARA-C01 Question 404
Single answerClustering keysA retail company stores 8 TB of order history in a Snowflake table named ORDERS_FACT. The table receives continuous inserts throughout the day and is queried heavily by dashboards that almost always filter on ORDER_DATE ranges such as the last 7, 30, or 90 days. Some analyst queries also add REGION, but ORDER_DATE is present in nearly every selective filter. Query Profile shows a high percentage of micro-partitions scanned for these date-range queries, and the team wants to improve pruning without redesigning the ETL pipeline. Which action is the most appropriate?
- A
Define a clustering key on ORDER_DATE for ORDERS_FACT and allow Snowflake to maintain clustering as needed
- B
Create a clustering key on a high-cardinality surrogate key such as ORDER_ID because unique values maximize pruning
- C
Create a search optimization service on ORDER_DATE instead of using clustering because search optimization is intended for large range scans
- D
Convert ORDERS_FACT to a transient table so Snowflake can reorganize micro-partitions more efficiently for date filters
Show answer and explanation
Correct answer: A
Explanation
Snowflake clustering keys are most useful for large tables where query predicates repeatedly filter or join on the same columns and where better micro-partition pruning can materially reduce scan volume. In this scenario, ORDER_DATE is the dominant selective predicate, and the evidence from Query Profile indicates poor pruning, making a clustering key on ORDER_DATE the best fit. Snowflake documentation and best practices emphasize choosing columns that match common filter patterns, especially range filters, while avoiding overly high-cardinality expressions that increase reclustering cost without proportional benefit. Search Optimization Service is a different optimization designed for selective lookup-style access patterns rather than broad date-range scans. Changing a table to transient does not improve clustering or pruning behavior.
- A. Correct.
Correct. Clustering keys are appropriate when large tables are frequently filtered on one or more columns, especially range predicates such as date filters. Because ORDER_DATE is used in most selective predicates and Query Profile indicates poor micro-partition pruning, defining a clustering key on ORDER_DATE is a practical way to improve locality of similar values across micro-partitions and reduce the number scanned. This aligns with Snowflake best practices for very large tables with repeated filtering patterns.
- B. Incorrect.
Incorrect. High-cardinality columns such as unique ORDER_ID values are typically poor clustering key choices. They tend to produce excessive maintenance cost with limited benefit for common analytical access patterns, especially when queries are not filtering on that column. The misconception is that more unique values automatically mean better pruning; in practice, clustering should align to common filter predicates and range access patterns.
- C. Incorrect.
Incorrect. Search Optimization Service is generally used for highly selective point lookups or specific predicate patterns, not as the primary solution for broad date-range pruning on a large fact table. For repeated range filters on ORDER_DATE, clustering is usually the more appropriate optimization. A candidate might choose this option because both features improve query performance, but they target different access patterns.
- D. Incorrect.
Incorrect. Transient tables affect data protection characteristics such as Fail-safe, not micro-partition organization for pruning. Converting the table to transient would not address the underlying issue of poor pruning on ORDER_DATE. This distractor reflects a misunderstanding between storage lifecycle/cost features and physical data organization behavior.