DAA-C01 Question 180
Single answerLeverage clustering keysA retail analytics team stores 4 years of sales transactions in a large Snowflake table named FACT_SALES. Analysts frequently run queries that filter on SALE_DATE ranges such as the last 7, 30, or 90 days, and often add REGION_ID to the filter. The table is loaded continuously throughout the day. Query Profile shows that these reports scan a large number of micro-partitions even when the date filter is selective. The team wants to improve pruning for these common filters without changing the reporting SQL. What is the BEST recommendation?
- A
Define a clustering key on (SALE_DATE, REGION_ID) for FACT_SALES and monitor clustering depth/overlap to confirm improved pruning over time.
- B
Create a search optimization service on all columns in FACT_SALES because it replaces the need for clustering on large fact tables.
- C
Convert FACT_SALES to a temporary table so new data is written in date order and micro-partitions stay naturally clustered.
- D
Create a materialized view that selects all columns from FACT_SALES without filters; Snowflake will automatically use it to reduce micro-partition scans for date predicates.
Show answer and explanation
Correct answer: A
Explanation
For large Snowflake tables, clustering keys are most useful when query patterns repeatedly filter or join on a small set of columns and natural micro-partition ordering is not sufficient. In this scenario, the repeated use of SALE_DATE range predicates, often combined with REGION_ID, makes clustering a strong fit because it can reduce micro-partition overlap and improve pruning. Snowflake stores metadata about micro-partitions and uses min/max and other statistics to skip partitions that cannot satisfy a predicate. If data is poorly clustered, many partitions still need to be scanned even for selective date filters. Defining an appropriate clustering key can improve this. Best practice is to choose columns that are commonly used in selective filters, validate benefits using query profile and clustering metrics, and weigh maintenance cost against performance gain. Search Optimization Service is a different feature aimed at specific lookup/search patterns and is not the best first recommendation for broad date-range pruning on a large fact table.
- A. Correct.
Correct. Clustering keys are designed to improve micro-partition pruning for large tables with frequent selective filters on specific columns. Because queries commonly filter by SALE_DATE and sometimes REGION_ID, a clustering key on (SALE_DATE, REGION_ID) aligns the physical organization of data with the access pattern. Snowflake can then prune more micro-partitions when evaluating those predicates. Monitoring clustering information such as clustering depth/overlap is a best practice to validate that the key is beneficial and that reclustering activity is delivering value.
- B. Incorrect.
Incorrect. Search Optimization Service can accelerate certain highly selective lookups and predicate patterns, but it does not generally replace clustering for large range-based scans on common fact-table filter columns. In this scenario, the primary issue is poor pruning for date-range filters across a large transaction table, which is a classic clustering use case. Choosing SOS on all columns would also be unnecessarily broad and potentially costly.
- C. Incorrect.
Incorrect. Temporary tables are scoped to a session and are not an optimization mechanism for long-lived production fact tables. Also, simply making a table temporary does not guarantee useful micro-partition ordering or sustained clustering as continuous loads arrive. This option reflects a misconception that table type controls physical organization for pruning purposes.
- D. Incorrect.
Incorrect. A materialized view that simply mirrors all columns from the base table without additional aggregation, projection benefit, or selective filtering is unlikely to solve the pruning problem. Materialized views are not a substitute for clustering in this scenario. They are most useful when they precompute expensive transformations or maintain a more selective/aggregated subset that queries can use.