ADA-C01 Question 230
Single answerAnalyze table design considerationsA retail company stores 4 years of sales data in a Snowflake table named SALES_FACT with more than 8 billion rows. Most analytic queries filter on ORDER_DATE ranges such as the last 7, 30, or 90 days and frequently add selective predicates on REGION_ID and STORE_ID. The table is loaded continuously throughout the day. Performance has degraded over time, and Query Profile shows many micro-partitions scanned even for recent-date queries. The administrator wants to improve query pruning while avoiding unnecessary maintenance overhead. Which action is the BEST choice?
- A
Define a clustering key on (ORDER_DATE, REGION_ID, STORE_ID) and monitor clustering depth before deciding whether to recluster further
- B
Convert SALES_FACT to a temporary table so Snowflake stores fewer micro-partitions and improves pruning
- C
Create a search optimization service on every column in SALES_FACT because it replaces the need for clustering on large fact tables
- D
Split SALES_FACT into one physical table per month because Snowflake does not prune efficiently across a single large table
Show answer and explanation
Correct answer: A
Explanation
The best answer is to define a clustering key that matches the dominant query access pattern and then validate the benefit. Snowflake automatically stores min/max metadata for micro-partitions and uses that metadata for partition pruning. Over time, continuous DML or append patterns can reduce clustering quality for common predicates, causing more micro-partitions to be scanned. For very large tables with frequent selective filtering, especially on date ranges plus additional dimensions, clustering can improve pruning and reduce scan cost. However, clustering should be used judiciously because it can introduce maintenance cost; administrators should review clustering information, query patterns, and actual performance benefit. Search Optimization Service is valuable for specific selective lookup use cases, but it is not a universal substitute for clustering and is generally not the first choice for broad date-range filtering on massive fact tables. Snowflake best practices also favor keeping data in a single logical table rather than manually partitioning into many monthly tables, since Snowflake handles physical partitioning through micro-partitions automatically. Relevant Snowflake documentation includes guidance on micro-partitions, clustering keys, clustering depth/information, and Search Optimization Service use cases.
- A. Correct.
Correct. For a very large, continuously loaded fact table where queries commonly filter by date ranges and additional selective dimensions, a clustering key aligned to those predicates can improve micro-partition pruning. ORDER_DATE is the primary filtering dimension, and adding REGION_ID and STORE_ID can further help where query patterns justify it. Snowflake best practice is to use clustering selectively for large tables that benefit from improved pruning, and to monitor clustering information and cost/benefit rather than assuming aggressive reclustering is always required.
- B. Incorrect.
Incorrect. Temporary tables are session-scoped objects intended for transient workloads, not a performance feature for large production fact tables. They do not inherently reduce micro-partition count or improve pruning behavior. Converting a persistent sales fact table to temporary would also break durability and shared access requirements.
- C. Incorrect.
Incorrect. Search Optimization Service is designed for accelerating highly selective point lookups and certain query patterns, not as a blanket replacement for clustering on all columns of a large fact table. Enabling it on every column would add unnecessary cost and is not the best fit for common range predicates on ORDER_DATE in a very large table.
- D. Incorrect.
Incorrect. Manually splitting the fact table into monthly tables is generally an anti-pattern in Snowflake. Snowflake is designed to prune data within a single table using micro-partition metadata, and excessive table sharding increases management complexity, complicates queries, and often provides less benefit than appropriate clustering or other native optimization methods.