ADA-C01 Question 228
Single answerManage tablesA data engineering team loads a large fact table named SALES_FACT every hour using a batch process. Over time, query performance has degraded because many queries filter on ORDER_DATE and REGION, but the table now has a very high clustering depth. The team wants to improve pruning efficiency without rewriting application queries or manually reorganizing data after each load. Which action should the Snowflake administrator take?
- A
Define a clustering key on (ORDER_DATE, REGION) and enable Automatic Clustering for the table
- B
Convert SALES_FACT to a temporary table so Snowflake can rebuild its micro-partitions more frequently
- C
Create a materialized view on SALES_FACT and drop the base table to force queries to use the new storage layout
- D
Increase the virtual warehouse size used for queries so Snowflake can bypass micro-partition pruning limitations
Show answer and explanation
Correct answer: A
Explanation
Snowflake stores table data in micro-partitions and uses metadata to prune partitions during query execution. When a large table is frequently filtered by certain columns, and those values are poorly organized across micro-partitions, clustering depth can increase and pruning becomes less effective. For large, frequently updated or loaded tables, defining a clustering key on commonly filtered columns such as ORDER_DATE and REGION can improve partition pruning. Automatic Clustering is the preferred operational feature when the goal is to maintain clustering over time without manual intervention. Warehouse scaling affects compute capacity but does not reorganize table storage. Temporary tables are not appropriate for persistent shared datasets, and materialized views are workload-specific optimizations rather than a replacement for proper table clustering. This aligns with Snowflake best practices for managing large tables, clustering keys, and Automatic Clustering.
- A. Correct.
Correct. When queries commonly filter on specific columns and clustering depth is high, defining an appropriate clustering key can improve micro-partition pruning. On a frequently loaded large table, Automatic Clustering is the operationally sound choice because Snowflake maintains the clustering in the background as data changes, avoiding repeated manual reclustering tasks. This directly addresses the stated requirement to improve pruning efficiency without changing application SQL or manually reorganizing data after each load.
- B. Incorrect.
Incorrect. Temporary tables are session-scoped and are not intended as a performance tuning mechanism for persistent production fact tables. Converting a core fact table to a temporary table would break persistence and shared access requirements rather than improve long-term clustering behavior.
- C. Incorrect.
Incorrect. A materialized view can help some query patterns, but dropping the base table is not a valid or practical solution for a production fact table that is continuously loaded. Materialized views also do not replace the base table for all access patterns, and they introduce maintenance cost. The problem described is specifically about poor clustering on the base table, which is more directly solved by a clustering key with Automatic Clustering.
- D. Incorrect.
Incorrect. Increasing warehouse size can improve compute performance for scanning and processing, but it does not fix poor micro-partition pruning caused by suboptimal clustering. The underlying issue is table organization relative to filter columns, not insufficient compute resources.