ARA-C01 Question 422
Select 2Optimization techniquesA retail company stores 8 TB of order history in a Snowflake table named FACT_ORDERS. Most dashboard queries filter on ORDER_DATE for the last 7-30 days and often join to DIM_CUSTOMER on CUSTOMER_ID. Query Profile shows significant bytes scanned even when only recent data is requested. The table is loaded continuously in chronological order by ORDER_DATE. The architect must improve query performance while controlling ongoing maintenance cost. Which TWO actions are the most appropriate?
- A
Define a clustering key on ORDER_DATE so Snowflake can improve micro-partition pruning for recent-date filters.
- B
Create a Search Optimization Service on CUSTOMER_ID to accelerate highly selective lookups and joins involving the large FACT_ORDERS table.
- C
Create a materialized view that contains all columns from FACT_ORDERS and refresh it after each load to reduce bytes scanned.
- D
Increase the virtual warehouse size for all dashboard workloads because larger warehouses reduce the amount of data scanned from storage.
- E
Recluster the table manually after every load using a task, because Snowflake does not maintain clustered tables automatically once a clustering key is defined.
Show answer and explanation
Correct answers: A, B
Explanation
The best answer is to combine optimization techniques that match the access patterns. Since FACT_ORDERS is loaded in chronological ORDER_DATE order and queries commonly filter recent dates, clustering on ORDER_DATE improves micro-partition pruning and reduces bytes scanned. Because queries also frequently join using CUSTOMER_ID, Search Optimization Service can help when those joins are selective on the large fact table. By contrast, simply increasing warehouse size adds compute but does not reduce storage scan volume, and a full-copy materialized view is generally not an efficient design. Snowflake best practices emphasize choosing optimizations based on workload characteristics: clustering for large tables with common filter predicates that benefit partition pruning, and search optimization for highly selective lookups or joins. Relevant Snowflake documentation includes guidance on Clustering Keys, Automatic Clustering, Micro-Partition Pruning, and Search Optimization Service.
- A. Correct.
Correct. Because data is loaded in chronological order, ORDER_DATE is a strong candidate for clustering when many queries filter on recent date ranges. A clustering key on ORDER_DATE helps Snowflake organize micro-partitions to improve pruning, reducing scanned bytes for those predicates. This is an optimization aligned with the workload pattern and typically provides benefit when a large table is frequently filtered by the clustering column.
- B. Correct.
Correct. Search Optimization Service is designed to speed up point-lookups and highly selective access patterns, including selective joins, especially on large tables. If dashboard queries frequently join FACT_ORDERS to DIM_CUSTOMER using CUSTOMER_ID and the join/filter pattern is selective, enabling search optimization on CUSTOMER_ID can reduce lookup cost and latency without requiring a full table scan.
- C. Incorrect.
Incorrect. A materialized view can help for specific repeated aggregations or projections, but creating one that simply reproduces all columns from the base table is usually not an effective optimization and introduces maintenance cost. It does not address the root issue of micro-partition pruning on ORDER_DATE or selective lookups on CUSTOMER_ID in an efficient way.
- D. Incorrect.
Incorrect. A larger warehouse may improve execution speed through more compute resources, but it does not reduce the amount of data scanned from storage. The scenario specifically identifies excessive bytes scanned, which is better addressed through pruning and access-path optimizations such as clustering or search optimization.
- E. Incorrect.
Incorrect. This reflects a common misconception. When a clustering key is defined, Snowflake can perform Automatic Clustering to maintain clustering depth over time; manual reclustering after every load is not the standard best practice and can add unnecessary operational complexity and cost. The decision should be based on whether clustering benefit justifies maintenance cost, not on an assumption that manual reclustering is required.