DAA-C01 Question 179
Single answerLeverage clustering keysA data analyst manages a 12 TB SALES_FACT table in Snowflake that receives continuous daily loads. Most dashboard queries filter on SALE_DATE ranges such as the last 7, 30, or 90 days and also commonly filter by REGION_ID. Query profiles show large scan volumes even when only recent data is requested. The analyst wants to improve pruning for these dashboard queries without redesigning the entire pipeline. Which action is the BEST choice?
- A
Define a clustering key on (SALE_DATE, REGION_ID) for the SALES_FACT table and monitor clustering depth over time
- B
Create a search optimization service on every column in SALES_FACT because it replaces the need for clustering on large fact tables
- C
Convert SALES_FACT to a temporary table so new loads are stored together and recent-date queries scan fewer micro-partitions
- D
Create a materialized view that selects all columns from SALES_FACT without filters, because materialized views automatically recluster the base table
Show answer and explanation
Correct answer: A
Explanation
Snowflake stores table data in immutable micro-partitions and uses metadata about value ranges to prune partitions during query execution. When a large fact table is frequently filtered by date ranges and another common dimension such as region, a clustering key can improve how related rows are grouped across micro-partitions, leading to more effective pruning and lower scan costs. This is especially relevant for very large tables with recurring selective filter patterns. Best practice is to choose clustering keys based on actual query predicates and cardinality patterns, and then monitor clustering quality using Snowflake system functions and query performance over time. Search Optimization Service is valuable for certain highly selective access patterns, but it is not a general substitute for clustering on large range-filtered fact tables. Materialized views and temporary tables do not directly solve the described pruning problem. These behaviors align with Snowflake documentation on clustering keys, micro-partition pruning, and query optimization best practices.
- A. Correct.
Correct. Clustering keys help co-locate rows with similar key values in micro-partitions, which improves partition pruning for selective filters. Because the most common predicates are on SALE_DATE ranges and REGION_ID, clustering on these columns is a practical way to reduce scanned data for dashboard queries. Monitoring clustering information such as clustering depth helps validate whether the table remains well-clustered as continuous loads occur.
- B. Incorrect.
Incorrect. Search Optimization Service is designed for specific selective lookup patterns, such as point lookups, substring searches, or highly selective predicates, but it does not broadly replace clustering for large range-based fact-table filtering. In this scenario, the primary issue is poor pruning for common date-range filters, which is a classic use case for clustering keys.
- C. Incorrect.
Incorrect. Temporary tables affect object lifespan and session scope, not how Snowflake organizes large production fact data for pruning. Converting a persistent fact table used by dashboards into a temporary table would break the workload model and does not inherently solve micro-partition pruning issues.
- D. Incorrect.
Incorrect. Materialized views can improve performance for specific precomputed query patterns, but creating one that simply selects all columns from the base table without aggregation or filtering is unlikely to help. Also, a materialized view does not automatically recluster the base table. The problem described is about the physical organization of the base table for pruning, which clustering addresses more directly.