ARA-C01 Question 372
Select 2Domain 4.0: Performance Optimization (20%)A retail company stores 8 TB of order history in a Snowflake table named ORDERS. The table is loaded continuously and has frequent updates to ORDER_STATUS for recent records. Analysts run a dashboard every 5 minutes with queries similar to: SELECT CUSTOMER_ID, ORDER_DATE, ORDER_STATUS, TOTAL_AMOUNT FROM ORDERS WHERE ORDER_DATE >= CURRENT_DATE - 30 AND REGION = 'EMEA'; Query Profile shows most execution time is spent scanning a large number of micro-partitions, even though only the last 30 days are queried. The architect wants to improve performance for this workload without changing dashboard logic. Which TWO actions should the architect recommend?
- A
Define a clustering key on (ORDER_DATE, REGION) for the ORDERS table and monitor clustering depth to improve micro-partition pruning for the dashboard filters.
- B
Create a materialized view that selects only the last 30 days of rows using CURRENT_DATE in the definition so it automatically stays aligned with the dashboard filter.
- C
Enable Search Optimization Service on the ORDERS table for the ORDER_DATE and REGION columns to accelerate highly selective lookups and equality predicates.
- D
Increase the warehouse size for the dashboard workload because poor micro-partition pruning cannot be improved through table design changes.
- E
Replace the dashboard queries with result cache lookups by suspending all DML on ORDERS during business hours.
Show answer and explanation
Correct answers: A, C
Explanation
This scenario is centered on a common Snowflake performance problem: poor micro-partition pruning on a very large table with recurring filtered queries. When Query Profile shows excessive partition scanning, architects should first evaluate physical data organization and selective access features rather than simply increasing compute.
Option 1 is a strong recommendation because clustering keys can improve pruning when large tables are repeatedly filtered on columns such as ORDER_DATE and REGION. Snowflake automatically maintains micro-partition metadata, and clustering helps align that metadata with common predicates so fewer partitions need to be scanned.
Option 3 is also appropriate because Search Optimization Service is designed to accelerate selective point-lookups and similar selective predicates on large tables. It is not a universal replacement for clustering, but it can complement clustering for repeated selective filters when the cost-benefit is justified.
Option 2 is incorrect because a materialized view based on a moving CURRENT_DATE filter is not a valid solution for this sliding-window use case. Option 4 is a common misconception: more compute can help process scans faster, but it does not improve pruning. Option 5 misuses result caching and conflicts with the requirement for continuous DML.
These recommendations align with Snowflake best practices around query profile analysis, micro-partition pruning, clustering keys, and Search Optimization Service for performance tuning in architect-level scenarios.
- A. Correct.
Correct. The workload repeatedly filters on ORDER_DATE and REGION, and Query Profile indicates excessive micro-partition scanning. A clustering key on the filter columns can improve co-location of relevant rows and increase pruning efficiency, especially for a large table with ongoing DML that may degrade natural clustering over time. Monitoring clustering information is also a best practice because clustering has a maintenance cost and should be justified by pruning benefit.
- B. Incorrect.
Incorrect. Materialized views in Snowflake have restrictions on supported expressions and are not appropriate for a sliding-window definition based on non-deterministic functions such as CURRENT_DATE in the way described here. Even aside from that, the requirement is to improve pruning and performance without rewriting dashboard logic, and this option proposes a design that would not be a valid or reliable implementation for an automatically moving 30-day window.
- C. Correct.
Correct. Search Optimization Service can improve performance for selective queries, especially where filters target a small subset of rows using equality predicates and similar access patterns. In this scenario, REGION = 'EMEA' is an equality predicate, and the last-30-days filter is intended to narrow the result set substantially. While clustering addresses pruning at the micro-partition level, Search Optimization can further help selective access patterns on large tables when justified by workload and cost.
- D. Incorrect.
Incorrect. Scaling up the warehouse may reduce elapsed time by providing more compute, but it does not address the underlying issue identified in Query Profile: scanning too many micro-partitions. Snowflake performance tuning best practices generally recommend improving pruning and data access patterns before relying only on larger warehouses, especially when the same query pattern runs frequently.
- E. Incorrect.
Incorrect. Result cache can accelerate repeated identical queries only when the underlying data has not changed in a way that invalidates the cached result. The scenario explicitly states that ORDERS is loaded continuously and frequently updated, so relying on result cache by pausing DML is operationally unrealistic and does not solve the architectural performance issue. It also violates the practical requirement of maintaining business operations.