SnowPro Associate: Platform Question 108
Single answer● TablesA data engineering team loads sales transactions into a Snowflake table every hour. Analysts frequently run time-based filters such as WHERE sale_date BETWEEN '2025-01-01' AND '2025-01-31' and aggregations by region. Over time, query performance has become inconsistent because newly loaded rows are inserted in no particular order. The team wants to improve pruning efficiency for these common filters without changing analyst queries. Which action should the team take?
- A
Define a clustering key on the table using columns such as sale_date and region
- B
Convert the table to a temporary table so Snowflake can scan less historical data
- C
Create additional virtual warehouses dedicated to queries on this table
- D
Add a primary key on sale_date so Snowflake automatically stores rows in date order
Show answer and explanation
Correct answer: A
Explanation
Snowflake stores table data in columnar micro-partitions and automatically captures metadata such as value ranges for columns. Query performance for selective filters depends heavily on micro-partition pruning. When data is inserted in an uncorrelated order over time, pruning can become less effective because relevant values are spread across more micro-partitions. For large tables with predictable filtering patterns, defining a clustering key on those filter columns can improve pruning and make performance more consistent. Virtual warehouses affect compute resources, not storage organization. Temporary tables are for transient session-level use cases. Primary key constraints in Snowflake do not physically sort data or enforce uniqueness on standard tables. This aligns with Snowflake documentation on clustered tables, micro-partitions, and table constraints.
- A. Correct.
Correct. Defining a clustering key on commonly filtered columns like sale_date and possibly region can improve micro-partition pruning when data is loaded in a less organized pattern over time. Snowflake tables are automatically partitioned into micro-partitions, but clustering keys can help maintain better co-location of related values for large tables with common selective filters.
- B. Incorrect.
Incorrect. Temporary tables are session-scoped objects intended for short-lived data, not a performance optimization for reducing scans of historical data in a persistent analytics workload. Converting a production fact table to a temporary table would also make it unavailable after the session ends.
- C. Incorrect.
Incorrect. Adding more or separate virtual warehouses can increase available compute and concurrency, but it does not directly improve micro-partition pruning within the table. The scenario specifically points to inconsistent pruning caused by row distribution in storage, which is addressed by clustering strategy rather than warehouse configuration.
- D. Incorrect.
Incorrect. In Snowflake, primary key constraints are generally informational and are not enforced on standard tables. They also do not cause physical ordering of table data. A common misconception is that declaring a primary key changes storage layout as it might in some traditional database systems.