COF-C03 Question 94
Single answerData clusteringA retail company stores 8 TB of order history in a Snowflake table named ORDERS_FACT. The table receives nightly batch inserts, and analysts frequently run queries that filter on ORDER_DATE ranges and REGION, usually returning less than 1% of the table. Query profiles show a large number of micro-partitions scanned for these reports. The team wants to improve pruning for these queries without changing the SQL used by analysts. Which action is the MOST appropriate?
- A
Define a clustering key on (ORDER_DATE, REGION) for ORDERS_FACT and allow Snowflake to maintain clustering as needed
- B
Create additional virtual warehouses dedicated to the reporting workload so more micro-partitions can be scanned in parallel
- C
Convert ORDERS_FACT to a temporary table so Snowflake can reorganize micro-partitions more aggressively
- D
Create a materialized view on ORDERS_FACT without a different physical organization, because materialized views automatically eliminate the need for clustering
Show answer and explanation
Correct answer: A
Explanation
Snowflake stores table data in micro-partitions and uses metadata to prune irrelevant partitions during query execution. When a large table is frequently filtered on specific columns, and query profiles show many micro-partitions being scanned, defining a clustering key on those filter columns can improve pruning efficiency. This is most beneficial for large tables with selective filters and where natural clustering has degraded due to ongoing inserts or updates. Increasing warehouse size addresses compute throughput, not pruning. Temporary tables do not provide special clustering benefits. Materialized views can help certain workloads, but they are not a universal replacement for clustering and do not inherently solve poor pruning on the base table. This aligns with Snowflake best practices around clustering keys, micro-partition pruning, and using query profile information to identify scan inefficiencies.
- A. Correct.
Correct. The scenario indicates selective filters on ORDER_DATE and REGION, along with poor micro-partition pruning. A clustering key on the commonly filtered columns can improve co-location of related rows across micro-partitions, which helps pruning and reduces the amount of data scanned. This is especially relevant for large tables with ongoing DML where natural clustering may degrade over time. Snowflake can then perform automatic reclustering as needed for a clustered table.
- B. Incorrect.
Incorrect. Adding or resizing warehouses can improve compute capacity and parallelism, but it does not improve micro-partition pruning. If queries are scanning too many micro-partitions because of poor clustering, more compute may reduce runtime somewhat but does not address the root cause of unnecessary data scanning.
- C. Incorrect.
Incorrect. Temporary tables are session-scoped objects and do not provide special behavior for clustering or micro-partition organization. Changing the table type would not solve pruning issues for a persistent 8 TB fact table used by analysts.
- D. Incorrect.
Incorrect. Materialized views can improve performance for some repeated query patterns, but they do not automatically remove the need for clustering on the base table. Also, the option states there is no different physical organization. In this scenario, the problem is excessive micro-partition scanning on the base table for common filter columns, which is more directly addressed with an appropriate clustering key.