ADA-C01 Question 529
Single answerAutomatic clusteringA retail company loads millions of rows per day into a large SALES_FACT table using frequent micro-batch INSERTs. The table is often filtered by ORDER_DATE and REGION in dashboards, but query performance has degraded over time as data was appended out of order. The table owner defines a clustering key on (ORDER_DATE, REGION) and wants Snowflake to maintain clustering without scheduling manual reclustering jobs. Which action should the administrator take to meet this requirement with the least operational overhead?
- A
Enable Automatic Clustering for the SALES_FACT table after defining the clustering key.
- B
Create a task that runs ALTER TABLE SALES_FACT RECLUSTER every hour.
- C
Increase the virtual warehouse size used by dashboard queries so Snowflake can reorganize micro-partitions during query execution.
- D
Convert SALES_FACT to a temporary table so clustering maintenance happens automatically at session end.
Show answer and explanation
Correct answer: A
Explanation
Automatic Clustering is the Snowflake feature designed to maintain table clustering after a clustering key has been defined, especially for large tables that receive ongoing DML such as INSERT, DELETE, and MERGE operations. In this scenario, the table is heavily queried on ORDER_DATE and REGION and receives out-of-order micro-batch inserts, so clustering naturally degrades over time. Enabling Automatic Clustering is the best-fit administrative action because it continuously manages reclustering in the background and avoids the operational burden of creating and tuning manual reclustering jobs. This aligns with Snowflake best practices: use clustering keys only for large tables where pruning benefits justify maintenance cost, and rely on Automatic Clustering when ongoing DML would otherwise degrade clustering depth. Warehouse resizing improves compute capacity for queries but does not maintain clustering, and temporary tables are unrelated to automatic clustering management.
- A. Correct.
Correct. After a clustering key is defined, Snowflake can use Automatic Clustering to maintain the table's clustering over time as DML changes occur. This is the managed, low-operational-overhead approach for keeping micro-partitions organized around the specified key without building custom scheduling logic.
- B. Incorrect.
Incorrect. While manual reclustering was historically used, the requirement is to avoid scheduled maintenance and minimize operational overhead. A task that periodically reclusters introduces management complexity and is not the intended managed solution when Automatic Clustering is available.
- C. Incorrect.
Incorrect. Warehouse size affects query execution performance, but it does not cause Snowflake to reorganize table micro-partitions as part of dashboard queries. Reclustering and Automatic Clustering are separate from query-time warehouse scaling.
- D. Incorrect.
Incorrect. Temporary tables are session-scoped objects and are not used to enable automatic clustering behavior. Converting a large persistent fact table to a temporary table would not meet the business requirement and would break persistence and shared analytics use cases.