COF-C03 Question 8
Single answerDatabase Storage layerA data engineering team loads 2 TB of clickstream data into a Snowflake table each day. Analysts run many selective queries that filter on EVENT_DATE and CUSTOMER_ID, but performance has become inconsistent as the table has grown. The team wants to improve query pruning without changing the application logic or redesigning the schema. Which action is the BEST choice to improve performance at the database storage layer?
- A
Define a clustering key on EVENT_DATE and CUSTOMER_ID so Snowflake can better organize micro-partitions for pruning
- B
Create additional virtual warehouses dedicated to queries that filter on EVENT_DATE and CUSTOMER_ID
- C
Convert the permanent table to a temporary table so Snowflake stores less historical metadata
- D
Increase the data retention period so Snowflake has more historical versions available for pruning
Show answer and explanation
Correct answer: A
Explanation
Snowflake's database storage layer organizes table data into immutable micro-partitions and automatically captures metadata about the values stored within them. Query performance for selective predicates often depends on how effectively Snowflake can prune micro-partitions before scanning them. When a large table is commonly filtered by specific columns, a clustering key on those columns can improve clustering depth over time and make pruning more effective. In contrast, virtual warehouses affect compute resources, not storage organization. Time Travel retention and table type changes do not address micro-partition pruning. This aligns with Snowflake documentation on micro-partitions, clustering keys, and partition pruning best practices.
- A. Correct.
Correct. Snowflake stores table data in compressed columnar micro-partitions and maintains metadata such as min/max values for columns in each micro-partition. Defining a clustering key on frequently filtered columns such as EVENT_DATE and CUSTOMER_ID can improve micro-partition clustering, which helps partition pruning and reduces the amount of data scanned by selective queries. This is a storage-layer optimization and fits the requirement of improving performance without changing application logic or schema design.
- B. Incorrect.
Incorrect. Additional virtual warehouses increase available compute and can improve concurrency or raw processing power, but they do not change how table data is organized in storage or how effectively micro-partition pruning works. The scenario specifically points to inconsistent pruning on a large table, which is better addressed through clustering rather than more warehouses.
- C. Incorrect.
Incorrect. Temporary tables are intended for session-scoped transient data and are not a performance feature for large production analytical tables. Converting the table to temporary would also change object lifecycle and availability in ways that do not meet the scenario. Historical metadata retention is unrelated to improving selective query pruning on active production data.
- D. Incorrect.
Incorrect. Data retention period affects Time Travel and historical data recovery, not pruning efficiency for current table scans. Increasing retention typically increases the amount of historical data maintained for recovery purposes, but it does not improve the organization of active micro-partitions for selective query filters.