ARA-C01 Question 398
Single answerClusteringA retail company stores 8 TB of order history in a Snowflake table named FACT_ORDERS. The table receives continuous inserts throughout the day and is queried heavily by dashboards that typically filter on ORDER_DATE ranges and sometimes additionally on REGION_ID. Query performance has become inconsistent, and the architect confirms that many scans read far more micro-partitions than expected for recent date-range queries. The company wants to improve pruning for these common filters without adding significant operational overhead for developers. Which solution is the MOST appropriate?
- A
Define a clustering key on (ORDER_DATE, REGION_ID) and enable Automatic Clustering so Snowflake maintains clustering depth as new data arrives
- B
Create a search optimization service on all columns in FACT_ORDERS because it replaces the need for clustering on range-filter predicates
- C
Increase the virtual warehouse size used by the dashboards so larger scans complete faster, eliminating the need to optimize micro-partition pruning
- D
Recreate FACT_ORDERS as a temporary table each day so newly loaded data is physically ordered and clustering is preserved automatically
Show answer and explanation
Correct answer: A
Explanation
Snowflake stores data in micro-partitions and uses metadata to prune partitions during query execution. When a large table is frequently filtered on specific columns, especially range predicates such as dates, defining an appropriate clustering key can improve pruning efficiency by better co-locating related rows across micro-partitions. In this scenario, ORDER_DATE is the primary filter and REGION_ID is a secondary common predicate, so clustering on (ORDER_DATE, REGION_ID) is a practical design. Because the table receives continuous inserts, Automatic Clustering is the best fit to maintain clustering with minimal operational burden. Snowflake documentation and best practices indicate that clustering is most useful for large tables with frequent selective filtering and where poor pruning is observed, while Search Optimization Service is a separate feature better suited to highly selective lookup patterns rather than broad date-range scans.
- A. Correct.
Correct. Clustering keys are appropriate for very large tables with selective filters, especially on columns commonly used in range predicates such as dates. Because the workload frequently filters by ORDER_DATE and sometimes REGION_ID, a clustering key on (ORDER_DATE, REGION_ID) aligns well with query access patterns and can improve micro-partition pruning. Enabling Automatic Clustering reduces manual maintenance overhead as ongoing inserts would otherwise degrade clustering over time.
- B. Incorrect.
Incorrect. Search Optimization Service is designed primarily to accelerate specific point-lookups and certain selective search patterns, not to generally replace clustering for large range-filter scans. For a workload dominated by date-range predicates on a very large fact table, clustering is typically the more appropriate optimization. This option reflects a common misconception that search optimization is a universal substitute for clustering.
- C. Incorrect.
Incorrect. Increasing warehouse size can reduce execution time by adding compute, but it does not improve partition pruning or storage layout. If queries are scanning too many micro-partitions, compute scaling alone treats the symptom rather than the root cause. It may also increase cost without providing consistent performance improvements for poorly clustered data.
- D. Incorrect.
Incorrect. Temporary tables are session-scoped and not suitable for persistent shared analytics tables such as a central fact table used by dashboards. Also, simply recreating the table daily does not guarantee that ongoing inserts will maintain optimal clustering over time. This introduces unnecessary operational complexity and does not meet the requirement to minimize developer overhead.