ARA-C01 Question 241
Single answerQuery performance impactsA retail company runs an hourly dashboard query that aggregates the last 7 days of sales from a 12 TB SALES_FACT table joined to small dimension tables. The query has become increasingly slow even though the SQL text has not changed. Query Profile shows a very high percentage of partitions scanned on SALES_FACT, and the table receives continuous inserts throughout the day. The architect wants to improve performance without changing the dashboard logic. Which action is the BEST choice to reduce query runtime?
- A
Define a clustering key on SALES_FACT using the event_date column most commonly used in the dashboard filter, then allow automatic clustering or recluster as needed
- B
Create a materialized view on each dimension table so the join operations complete faster
- C
Increase the warehouse size permanently, because partition pruning cannot be improved for large continuously loaded tables
- D
Convert SALES_FACT to a temporary table during dashboard processing so Snowflake can skip micro-partition metadata checks
Show answer and explanation
Correct answer: A
Explanation
The key evidence is that Query Profile shows a high percentage of partitions scanned on a very large fact table, while the query filters only the last 7 days. In Snowflake, query performance for large tables often depends on effective micro-partition pruning. Continuous inserts can gradually reduce clustering quality for commonly filtered columns, especially date or timestamp columns used in range predicates. Defining a clustering key on the filter column can improve data locality and reduce partitions scanned. Snowflake documentation and best practices emphasize evaluating clustering for large tables with selective predicates and using Query Profile to validate whether partition pruning is effective. Warehouse resizing can help throughput, but it does not solve inefficient pruning. Likewise, optimizing small dimension tables or changing table type does not address the primary scan bottleneck.
- A. Correct.
Correct. The profile indicates poor micro-partition pruning, which is often caused by the table's clustering depth degrading over time as new data is inserted. Since the query filters on a recent date range, defining an appropriate clustering key such as event_date can improve co-location of similar values in micro-partitions and reduce the number of partitions scanned. This directly targets the identified bottleneck without changing application logic. In Snowflake, clustering is especially useful for very large tables with selective filters where pruning quality materially affects scan cost and runtime.
- B. Incorrect.
Incorrect. Materialized views on the small dimension tables are unlikely to address the main issue because Query Profile already points to excessive scanning of SALES_FACT. The bottleneck is fact-table pruning, not repeated expensive dimension-table computation. A candidate might choose this if focusing on joins in general, but improving small-table joins will not materially reduce runtime when the largest cost is scanning a poorly clustered 12 TB fact table.
- C. Incorrect.
Incorrect. A larger warehouse may reduce elapsed time through more compute resources, but it does not fix the underlying partition-pruning problem. If most micro-partitions are still scanned, the query remains inefficient and may continue to scale poorly as data grows. Snowflake best practice is to address data layout and pruning opportunities before relying on permanent warehouse upsizing for recurring workloads.
- D. Incorrect.
Incorrect. Temporary tables do not provide a performance advantage for this scenario and do not change how Snowflake uses micro-partition metadata for pruning. In fact, converting a large production fact table to a temporary table for dashboard processing would add operational complexity without addressing the root cause. This option reflects a misconception that table type changes query optimization behavior in a way that bypasses normal scan logic.