ARA-C01 Question 373
Select 2Domain 4.0: Performance Optimization (20%)A retail company stores 18 months of point-of-sale transactions in a Snowflake table containing 12 billion rows. Analysts primarily run dashboard queries for the most recent 7 days, filtered by STORE_ID and TRANSACTION_TS, while finance runs occasional month-end queries across the full table. The dashboard workload has become inconsistent: some queries return in seconds, while others take much longer even though they use similar predicates. An architect reviews the query profile and observes high scan volume for many dashboard queries and poor micro-partition pruning on the transaction table. Which TWO actions would most directly improve performance for the dashboard workload while balancing the occasional long-range finance queries?
- A
Define a clustering key on (STORE_ID, TRANSACTION_TS) for the transaction table and monitor clustering depth over time
- B
Create a materialized view that contains only the last 7 days of data for all columns in the transaction table, and direct all queries to that view
- C
Use Search Optimization Service on the transaction table for equality lookups on STORE_ID and selective predicates, if the access pattern justifies the cost
- D
Increase the virtual warehouse size for all dashboard queries from MEDIUM to 4XL to compensate for poor pruning
- E
Convert the transaction table to a temporary table during dashboard business hours to reduce storage-layer overhead
Show answer and explanation
Correct answers: A, C
Explanation
The key evidence in the scenario is the query profile showing high scan volume and poor micro-partition pruning on a very large table. In Snowflake, one of the most effective performance optimizations for large selective workloads is to improve pruning so fewer micro-partitions must be scanned. A clustering key on columns commonly used together in filters, such as STORE_ID and TRANSACTION_TS, can improve data locality and pruning effectiveness. Search Optimization Service is another targeted feature that can accelerate highly selective point or small-subset lookups, especially for equality predicates, though it should be evaluated against cost and selectivity. By contrast, simply increasing warehouse size adds compute without fixing excessive scanning, and using a temporary table is not appropriate for a shared persistent dataset. Materialized views can be valuable in some workloads, but for this scenario they are less direct than fixing pruning behavior on the base table and may add maintenance cost. Relevant Snowflake best practices include using query profile to identify scan bottlenecks, leveraging clustering for large tables with repeated selective filters, and considering Search Optimization Service for suitable selective access patterns.
- A. Correct.
Correct. The scenario specifically indicates poor micro-partition pruning for queries filtered by STORE_ID and TRANSACTION_TS. Defining an appropriate clustering key on these columns can improve co-location of related rows in micro-partitions, which can reduce the amount of data scanned for the common dashboard access pattern. Monitoring clustering depth is important because clustering effectiveness can degrade as new data is loaded, and reclustering has cost implications. This is a practical optimization when the table is large and the workload repeatedly filters on the same dimensions.
- B. Incorrect.
Incorrect. Although a materialized view can improve performance for some repeated query patterns, creating one that contains all columns for the last 7 days is often expensive to maintain on a very large, frequently changing transaction table. It also does not directly solve the broader pruning issue on the base table, especially if analysts use varying query shapes. Materialized views are typically most effective when they precompute expensive aggregations, projections, or stable filtered subsets with clear reuse patterns. In this case, the requirement is to improve selective dashboard filtering while still supporting occasional broad finance queries, and clustering plus search optimization are the more direct table-level performance levers.
- C. Correct.
Correct. Search Optimization Service can significantly improve performance for highly selective lookups and predicate evaluation patterns, especially for equality predicates such as STORE_ID and other selective searches where scanning large portions of the table would otherwise be required. It is not a replacement for good clustering in all cases, but it can complement clustering when the workload justifies the additional cost. Because the dashboard queries repeatedly filter on specific stores and recent time windows, this can be an effective targeted optimization if selectivity is high enough.
- D. Incorrect.
Incorrect. Increasing warehouse size may reduce execution time for some scans by adding compute resources, but it does not address the root cause identified in the query profile: poor micro-partition pruning and high scan volume. Scaling compute is often the wrong first response when storage-level pruning and table organization are the main bottlenecks. It also increases cost substantially for all dashboard queries, even though many could be optimized more efficiently through data access improvements.
- E. Incorrect.
Incorrect. Temporary tables are session-scoped objects intended for transient working data and do not provide a performance advantage for this type of persistent fact table workload. Converting a core transaction table to a temporary table would also make it unavailable across sessions and is not a valid strategy for reducing storage-layer overhead in Snowflake. This option reflects a misunderstanding of Snowflake table types and their purpose.