COF-C03 Question 29
Single answerTablesA retail company loads clickstream data into a large FACT_EVENTS table every 5 minutes. Analysts frequently run queries filtered by EVENT_DATE and REGION, but query performance has become inconsistent as the table has grown. The company wants to improve pruning efficiency without changing application logic or creating additional tables. Which action should the Snowflake administrator take?
- A
Define a clustering key on EVENT_DATE and REGION for the FACT_EVENTS table
- B
Convert the table to a temporary table so Snowflake can optimize micro-partitions more aggressively
- C
Create a materialized view on the entire FACT_EVENTS table without filters
- D
Increase the virtual warehouse size permanently so Snowflake can skip more micro-partitions during scans
Show answer and explanation
Correct answer: A
Explanation
Snowflake stores table data in immutable micro-partitions and uses metadata to perform partition pruning during query execution. When a large table is frequently filtered on specific columns, defining a clustering key on those columns can improve clustering depth and make pruning more effective, especially if data arrives continuously and natural ordering no longer aligns with query patterns. In this scenario, EVENT_DATE and REGION are common filter columns, so clustering the table on those columns is the most direct way to improve scan efficiency without changing application logic or adding tables. Warehouse size affects compute parallelism and speed, but not the underlying pruning decision. Temporary tables are unrelated to this use case, and a materialized view is a different optimization technique that is not the best fit for improving access patterns on the existing table itself. This aligns with Snowflake best practices for clustered tables and micro-partition pruning.
- A. Correct.
Correct. Defining a clustering key on EVENT_DATE and REGION can improve micro-partition clustering for the columns most commonly used in filters. Better clustering increases partition pruning efficiency, which can reduce the amount of data scanned and improve performance for selective queries. This is appropriate when large tables have common filter predicates and natural clustering has degraded over time due to ongoing inserts and updates.
- B. Incorrect.
Incorrect. Temporary tables are session-scoped and are not designed to improve pruning behavior for a persistent analytics workload. Their purpose is transient data storage, not better micro-partition optimization. Changing the table type would also likely break the requirement for ongoing shared analytical access.
- C. Incorrect.
Incorrect. A materialized view can help certain repeated query patterns, but creating one over the entire base table without a more selective design does not directly address the root issue of poor pruning on the base table. It also introduces additional maintenance cost and does not meet the requirement as cleanly as improving clustering on the existing table.
- D. Incorrect.
Incorrect. Increasing warehouse size may reduce execution time by adding compute, but it does not make Snowflake skip more micro-partitions. Partition pruning depends on table metadata and how data is organized across micro-partitions, not on warehouse size. This option treats the symptom with more compute rather than improving data access efficiency.