ARA-C01 Question 402
Single answerAuto-clusteringA retail company loads approximately 800 million rows per day into a large FACT_SALES table using many small micro-batch MERGE operations throughout the day. The table is frequently queried with filters on ORDER_DATE and REGION_ID, and it was defined with a clustering key on (ORDER_DATE, REGION_ID). Over time, query performance has become inconsistent and Snowflake credit consumption has increased. The architect finds that automatic clustering is enabled on the table, and the table experiences constant DML activity during business hours. Which action is the MOST appropriate to improve the balance between query performance and compute cost?
- A
Remove the clustering key because automatic clustering itself causes poor pruning on heavily updated tables, and rely on standard micro-partition metadata only.
- B
Keep the clustering key, but redesign the ingestion pattern to use fewer, larger batch loads where possible so automatic clustering has less continuous reclustering work to perform.
- C
Suspend automatic clustering permanently and run manual reclustering commands nightly on the table to fully control cost and performance.
- D
Create a materialized view on the entire FACT_SALES table with the same columns and filters, because materialized views replace the need for clustering on large fact tables.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to keep the clustering key and optimize the ingestion pattern to reduce the number of small DML operations. In Snowflake, automatic clustering helps maintain the physical organization of data according to a defined clustering key, improving partition pruning for large tables with selective filters. However, when a table is subject to constant small MERGE or UPDATE operations, Snowflake may need to perform ongoing reclustering work, which can increase serverless credit consumption. For very large fact tables, clustering remains appropriate when query predicates align with the key, but architects should also design ingestion to minimize micro-partition churn. Snowflake documentation and best practices emphasize evaluating clustering depth, pruning benefits, and maintenance cost, and they generally favor automatic clustering over manual reclustering management. In this scenario, the root cause is not that clustering is inappropriate; it is that the DML pattern is causing excessive maintenance overhead.
- A. Incorrect.
Incorrect. Removing the clustering key may reduce auto-clustering cost, but it does not address the underlying access pattern where queries commonly filter on ORDER_DATE and REGION_ID. For a very large fact table with selective predicates on those columns, clustering can materially improve pruning. The issue in the scenario is the constant stream of small DML operations, which can increase reclustering effort and credit usage. The misconception is that automatic clustering inherently causes poor pruning; in reality, it helps maintain clustering, but workload design strongly affects its cost.
- B. Correct.
Correct. Snowflake automatic clustering is designed to maintain the clustering key over time, but frequent small MERGE operations can create continuous micro-partition churn and increase reclustering activity. Consolidating data into fewer, larger batch loads typically reduces fragmentation and the amount of maintenance work required, while preserving the pruning benefits of clustering for common filters on ORDER_DATE and REGION_ID. This is the best architectural adjustment because it addresses both performance consistency and cost efficiency without discarding a useful clustering strategy.
- C. Incorrect.
Incorrect. Manual reclustering is not the recommended operational model. Snowflake provides automatic clustering specifically so customers do not need to manage reclustering jobs themselves, and legacy/manual reclustering approaches are not the preferred best practice. Permanently suspending automatic clustering on an actively queried table with a justified clustering key would likely degrade pruning until the next maintenance event and introduce unnecessary operational overhead.
- D. Incorrect.
Incorrect. A materialized view can help specific repeated query patterns, but it does not generally replace clustering for an entire large fact table. Creating a materialized view over the same broad dataset could introduce substantial maintenance cost, especially under heavy DML, and may not solve the root problem of continuous reclustering caused by many small updates. The misconception is treating materialized views as a universal substitute for table design and load-pattern optimization.