ARA-C01 Question 423
Select 2Optimization techniquesA retail company stores 8 TB of order history in a Snowflake table named FACT_ORDERS. Analysts frequently run dashboard queries that filter on ORDER_DATE for the last 7 days and join FACT_ORDERS to DIM_CUSTOMER on CUSTOMER_ID. The team notices query latency has increased as the table grew. Query Profile shows large scan volumes on FACT_ORDERS, and warehouse size increases have only provided limited improvement. The architect wants to reduce scanned data and improve performance without changing dashboard logic. Which TWO actions are the most appropriate?
- A
Define a clustering key on ORDER_DATE, CUSTOMER_ID for FACT_ORDERS and monitor clustering depth/benefit over time
- B
Create a materialized view on FACT_ORDERS that selects only rows from the last 7 days so dashboards automatically use it for all queries
- C
Enable Search Optimization Service on FACT_ORDERS specifically for equality lookups on CUSTOMER_ID if selective join access justifies the cost
- D
Increase the virtual warehouse from MEDIUM to 2XL because compute scaling is the primary way to reduce table scan bytes
- E
Convert FACT_ORDERS into a temporary table so Snowflake can skip long-term micro-partition maintenance overhead
Show answer and explanation
Correct answers: A, C
Explanation
The best answers are to improve data access paths rather than only adding compute. For very large Snowflake tables, optimization techniques should target reduced scanned data through better pruning and selective access. A clustering key on columns commonly used in filters, especially a date range such as ORDER_DATE, can improve micro-partition pruning when natural clustering is no longer effective. Search Optimization Service is appropriate for highly selective equality predicates and some selective join scenarios, such as access via CUSTOMER_ID into a large table. In contrast, simply increasing warehouse size does not solve excessive scan volume, and temporary tables are not a performance feature for persistent analytic fact tables. Materialized views can help in some repeated-query scenarios, but they are not a universal solution and should be used carefully based on workload shape, maintenance cost, and optimizer eligibility. These recommendations align with Snowflake best practices around query performance tuning, clustering keys, micro-partition pruning, Query Profile analysis, and Search Optimization Service.
- A. Correct.
Correct. Clustering on ORDER_DATE and CUSTOMER_ID can improve micro-partition pruning for a very large fact table when queries repeatedly filter by recent dates and also join on CUSTOMER_ID. Snowflake automatically maintains metadata for micro-partitions, but as data volume and insert patterns grow, natural clustering can degrade. A well-chosen clustering key can reduce the amount of data scanned, especially for range filters on ORDER_DATE. Including CUSTOMER_ID may also help when join patterns are common, though architects should validate the actual benefit and maintenance cost using clustering information functions and query history.
- B. Incorrect.
Incorrect. A materialized view can improve performance for repeated query patterns, but this option is overstated and operationally problematic. A materialized view defined as only the last 7 days of data would not automatically satisfy all dashboard queries unless their predicates exactly align with the view and optimizer rewrite conditions are met. In addition, using a rolling time window in a materialized view definition is not a generally appropriate design for this requirement because the view content must be maintained and may not transparently cover all variations of the dashboard filters. The question asks for actions that reduce scanned data without changing dashboard logic, and clustering plus search optimization are more directly aligned.
- C. Correct.
Correct. Search Optimization Service can accelerate highly selective point-lookups and certain join access patterns, including equality predicates on columns such as CUSTOMER_ID, when the workload justifies the additional cost. In this scenario, if dashboard queries join FACT_ORDERS to DIM_CUSTOMER and the join results in selective access into the large fact table, search optimization can reduce unnecessary scanning. This is especially useful when micro-partition pruning alone is insufficient for the join pattern.
- D. Incorrect.
Incorrect. Increasing warehouse size may reduce execution time for some workloads by adding compute resources, but it does not directly reduce scan bytes. The scenario specifically states that larger warehouses provided only limited improvement and Query Profile shows large scan volumes. That indicates the bottleneck is more related to data access and pruning than raw compute. This option reflects the common misconception that compute scaling is the best first response to all performance issues.
- E. Incorrect.
Incorrect. Temporary tables are session-scoped objects intended for transient intermediate data, not for optimizing a large shared fact table. Converting a persistent production fact table to a temporary table would break multi-session analytics usage and does not provide an optimization benefit for scan reduction. Snowflake micro-partition metadata and pruning behavior are not improved simply because a table is temporary.