COF-C03 Question 254
Single answerQuery Performance TuningA retail company has a 4 TB SALES_FACT table that is queried hundreds of times per day by dashboards. Most queries filter on ORDER_DATE and REGION, and then aggregate revenue by product category. The table was loaded in random order over time, and query profiles show a large amount of data being scanned even when users request only the last 7 days for a single region. The company wants to improve query performance without changing the BI tool queries. Which action would most directly reduce the amount of data scanned for these queries?
- A
Define a clustering key on ORDER_DATE and REGION for SALES_FACT, then monitor clustering depth and reclustering as needed
- B
Create a materialized view on the entire SALES_FACT table without filters so all dashboard queries read from the materialized view instead of the base table
- C
Increase the warehouse size from Medium to Large so fewer micro-partitions need to be scanned
- D
Convert SALES_FACT to a temporary table so Snowflake can cache the data more aggressively
Show answer and explanation
Correct answer: A
Explanation
This scenario is testing Snowflake query performance tuning through micro-partition pruning. In Snowflake, performance is often improved not only by adding compute but by reducing the amount of data that must be scanned. Since the workload repeatedly filters on ORDER_DATE and REGION, and the table was loaded in random order, poor clustering likely causes many micro-partitions to overlap on those values. A clustering key on the frequently filtered columns can improve pruning and lower scan volume. This is especially relevant for large tables with selective predicates. By contrast, increasing warehouse size improves compute throughput but does not change how much data is eligible to be scanned. Materialized views can help in some cases, but they should be designed for specific query patterns and are not the most direct remedy when the main issue is poor pruning on a large base table. Snowflake documentation and best practices for query performance emphasize examining query profile details, reducing scanned data, and considering clustering for large tables with common selective filters.
- A. Correct.
Correct. Snowflake stores table data in micro-partitions and uses metadata for partition pruning. If a large table is loaded in random order, filters on ORDER_DATE and REGION may not prune efficiently because the relevant values are spread across many micro-partitions. Defining a clustering key on the commonly filtered columns can improve co-location of related rows and make partition pruning more effective, directly reducing bytes scanned for these dashboard queries. Monitoring clustering information is important because clustering effectiveness can degrade as new data is loaded.
- B. Incorrect.
Incorrect. A materialized view can improve performance for some repeated query patterns, but creating one on the entire base table without a selective design does not directly address the root problem of excessive scanning caused by poor pruning in the base table layout. In addition, materialized views introduce maintenance cost and are most useful when they precompute a targeted subset, projection, or aggregation that matches query patterns closely. This option is too broad and not the most direct fix for the described issue.
- C. Incorrect.
Incorrect. Increasing warehouse size can reduce elapsed runtime by adding compute resources, but it does not reduce the number of micro-partitions that qualify for scanning. The question asks for the action that would most directly reduce the amount of data scanned. Warehouse resizing helps processing speed, not partition pruning efficiency.
- D. Incorrect.
Incorrect. Temporary tables are session-scoped objects and do not provide a query-performance benefit for this scenario. They do not change how micro-partition pruning works on the underlying data distribution, and converting a heavily used fact table to a temporary table would be operationally inappropriate for shared dashboard workloads.