ARA-C01 Question 403
Single answerAuto-clusteringA retail company stores 15 TB of clickstream data in a Snowflake table named EVENT_LOG. The table receives continuous micro-batch inserts throughout the day and is queried heavily by analysts using filters on EVENT_DATE and REGION. The architects defined a clustering key on (EVENT_DATE, REGION) six months ago. Recently, query performance on those filters has become inconsistent, and Snowflake costs have increased. The team finds that several ETL jobs perform frequent UPDATE and MERGE operations on older data in the same table. They want to improve filter query performance while minimizing ongoing maintenance effort.
Which action should the architect recommend?
- A
Enable automatic clustering on EVENT_LOG so Snowflake maintains the clustering key as DML operations change the table
- B
Create a search optimization service on EVENT_LOG and remove the clustering key because search optimization replaces clustering for range and equality predicates
- C
Schedule a nightly manual RECLUSTER command on EVENT_LOG to fully reorganize all micro-partitions after each day of ETL processing
- D
Increase the size of the virtual warehouse used by analyst queries so pruning becomes more effective despite degraded clustering
Show answer and explanation
Correct answer: A
Explanation
The best recommendation is to enable automatic clustering for the table. Snowflake clustering keys help co-locate related rows in micro-partitions to improve pruning for selective queries. However, frequent DML on a large table can degrade clustering over time, especially when older data is updated or merged. Automatic clustering is intended to maintain tables with clustering keys in the background without requiring administrators to manage manual reclustering jobs.
This is the most appropriate choice because the company wants both improved performance and reduced maintenance effort. Search Optimization Service is a different feature with different use cases and is not a direct replacement for clustering on a large fact-style table filtered by date and region. Increasing warehouse size addresses compute, not storage layout. Manual reclustering adds operational burden and is not the preferred long-term solution.
This aligns with Snowflake best practices and product documentation regarding clustering keys, clustering depth, micro-partition pruning, and automatic clustering behavior for large, heavily queried tables subject to ongoing DML.
- A. Correct.
Correct. Automatic clustering is designed for tables with defined clustering keys whose clustering depth degrades over time due to DML such as INSERT, UPDATE, DELETE, and MERGE. In this scenario, the table is large, frequently modified, and queried on the clustering columns. Enabling automatic clustering reduces the need for manual maintenance and helps restore micro-partition organization so partition pruning is more effective for filters on EVENT_DATE and REGION.
- B. Incorrect.
Incorrect. Search Optimization Service can accelerate certain selective point-lookups and some equality, substring, and semi-structured access patterns, but it does not generally replace clustering for large analytic tables with frequent range-based pruning needs. For a table queried heavily on EVENT_DATE and REGION, especially where a clustering key already exists, removing the clustering key would likely worsen pruning behavior rather than solve the underlying maintenance issue.
- C. Incorrect.
Incorrect. Manual reclustering is not the recommended operational approach for ongoing maintenance of large, frequently changing tables. Snowflake's automatic clustering exists specifically to manage reclustering in the background. A nightly manual process would add administrative overhead, may be inefficient for continuous DML throughout the day, and does not align with best practice for minimizing maintenance effort.
- D. Incorrect.
Incorrect. Increasing warehouse size may speed up query execution by adding compute, but it does not improve micro-partition clustering or pruning quality. The root problem is degraded clustering caused by frequent updates and merges. More compute cannot compensate for poor data organization in the way auto-clustering can.