ARA-C01 Question 242
Single answerQuery performance impactsA data engineering team loads approximately 500 million rows per day into a large SALES_FACT table using many small micro-batch INSERT operations throughout the day. Analysts report that a dashboard query filtering on ORDER_DATE and REGION has become progressively slower, even though the warehouse size has not changed and average concurrency is low. Query Profile shows a large amount of data scanned relative to the filtered result set. The table is not configured with a clustering key. As the Snowflake architect, which action would MOST directly improve query performance for this workload?
- A
Define a clustering key on (ORDER_DATE, REGION) and allow Snowflake to maintain clustering depth over time
- B
Increase the result cache retention period so repeated dashboard queries can reuse cached results longer
- C
Convert the table from permanent to transient to reduce storage overhead and improve scan speed
- D
Create a materialized view on the entire SALES_FACT table without filters or aggregation because materialized views scan less data by default
Show answer and explanation
Correct answer: A
Explanation
This question tests recognition of a common Snowflake performance pattern: degraded micro-partition pruning due to data distribution and load behavior. Snowflake stores data in micro-partitions and uses metadata such as min/max values to prune partitions during query execution. Frequent small inserts can lead to less effective natural clustering over time, particularly for large fact tables. When queries consistently filter on columns like ORDER_DATE and REGION and Query Profile shows large scan volumes relative to rows returned, a clustering key on those predicate columns is often the most direct architectural improvement. Snowflake documentation and best practices emphasize using clustering selectively for very large tables with selective filters and monitoring clustering depth and pruning effectiveness. Result cache is useful for repeated identical queries but is less reliable in continuously changing tables. Table type changes do not affect query execution speed. Materialized views are best when tailored to a specific repeated query pattern, especially aggregations or selective subsets, not as a generic replacement for proper clustering.
- A. Correct.
Correct. The scenario points to poor pruning: many rows are loaded through frequent small DML operations, the query filters on ORDER_DATE and REGION, and Query Profile shows a high scan volume relative to returned rows. In Snowflake, clustering affects how well micro-partition metadata can be used for partition pruning. Defining a clustering key aligned to common selective predicates such as ORDER_DATE and REGION can significantly reduce the number of micro-partitions scanned, especially after many incremental writes degrade natural clustering over time.
- B. Incorrect.
Incorrect. Result cache can help only when the exact same query can reuse cached results and the underlying data has not changed in a way that invalidates the cache. In this scenario, the table is being continuously updated throughout the day with many micro-batches, which reduces cache reuse opportunities. The issue described is excessive data scanned due to poor pruning, so cache retention is not the most direct fix.
- C. Incorrect.
Incorrect. Permanent versus transient affects data protection features such as Fail-safe and some storage cost characteristics, not scan performance for analytical queries. Changing the table type does not improve micro-partition pruning or reduce the amount of data scanned for filters on ORDER_DATE and REGION.
- D. Incorrect.
Incorrect. Materialized views can improve performance for specific precomputed projections, filters, or aggregations, but creating one on the entire base table without narrowing the workload does not inherently solve the pruning problem. It may add maintenance cost without materially reducing scan volume for this query pattern. The more direct optimization is to improve clustering on the base table for the relevant filter columns.