ADA-C01 Question 528
Single answerAutomatic clusteringA retail company has a 15 TB SALES_FACT table that receives continuous micro-batch inserts throughout the day. The table is frequently queried with filters on ORDER_DATE and REGION, and performance has degraded as the table has grown. An administrator defines a clustering key on (ORDER_DATE, REGION) and enables Automatic Clustering. After two days, query pruning improves, but the ACCOUNTADMIN notices a significant increase in Snowflake consumption attributed to background maintenance. The business wants to keep query performance benefits while reducing unnecessary clustering cost. Which action should the administrator take?
- A
Drop the clustering key, because once Automatic Clustering has reorganized the existing micro-partitions, the improved pruning will remain without further maintenance cost.
- B
Suspend Automatic Clustering for the table during heavy DML periods and resume it later when reclustering overhead is acceptable.
- C
Convert the table to a temporary table so Snowflake performs less background clustering maintenance.
- D
Create a larger virtual warehouse for user queries so query execution finishes faster and reduces Automatic Clustering charges.
Show answer and explanation
Correct answer: B
Explanation
Automatic Clustering is designed to maintain the clustering quality of tables with defined clustering keys as data changes over time. For large, frequently updated tables, it can materially improve pruning and query performance, but it may also increase cost because Snowflake performs ongoing reclustering in the background using Snowflake-managed compute. Administrators should evaluate whether the performance benefit justifies the maintenance cost and may suspend and resume Automatic Clustering based on workload patterns. This is especially relevant for heavy DML windows, where repeated reclustering can be expensive. Best practices from Snowflake documentation emphasize monitoring clustering effectiveness and cost, choosing appropriate clustering keys, and using Automatic Clustering selectively for large tables that benefit from pruning. Larger query warehouses do not control Automatic Clustering cost, and removing the clustering key would forfeit ongoing maintenance as new data arrives.
- A. Incorrect.
Incorrect. Dropping the clustering key stops further clustering maintenance, but the table will continue to receive new inserts and become less well clustered over time. Any current benefit will gradually degrade as new micro-partitions are added. This option reflects a common misconception that reclustering is a one-time operation rather than an ongoing maintenance activity for tables with frequent DML.
- B. Correct.
Correct. For tables with heavy ongoing DML, Automatic Clustering can generate noticeable maintenance cost because Snowflake continuously reclusters to preserve clustering depth. Suspending Automatic Clustering during periods of intense data change and resuming it later is a practical administrative approach when the business can tolerate some temporary degradation in clustering quality. This balances performance and cost while preserving the clustering key definition.
- C. Incorrect.
Incorrect. Temporary tables are session-scoped objects and are not an appropriate solution for a large shared fact table in production. Converting a permanent analytical table to temporary would break persistence and shared access requirements. There is no best-practice recommendation to use temporary tables to reduce Automatic Clustering cost for a production workload.
- D. Incorrect.
Incorrect. Automatic Clustering uses Snowflake-managed compute and is billed separately from user-managed virtual warehouses. Increasing the size of a query warehouse may improve query runtime, but it does not reduce background reclustering activity or its associated charges. This option confuses warehouse sizing for query processing with serverless services used by Automatic Clustering.