SnowPro Associate: Platform Question 105
Single answer● TablesA data engineering team loads clickstream data into a Snowflake table named EVENTS_RAW. The table receives millions of rows per day, and analysts frequently run queries filtered by EVENT_DATE and USER_ID. Performance has become inconsistent as the table grows. The team wants to improve query pruning without changing application logic or manually repartitioning data. Which action is the MOST appropriate?
- A
Define a clustering key on EVENT_DATE and USER_ID for the EVENTS_RAW table
- B
Convert EVENTS_RAW into a temporary table so Snowflake can optimize scans for active sessions
- C
Create a primary key on EVENT_DATE and USER_ID so Snowflake physically orders rows by those columns
- D
Replace the table with an external table so partitions in cloud storage handle pruning automatically
Show answer and explanation
Correct answer: A
Explanation
For large Snowflake tables, query performance often depends on effective micro-partition pruning. When queries repeatedly filter on specific columns, a clustering key can help Snowflake better organize table data over time so fewer micro-partitions are scanned. This is a practical option when a large table experiences inconsistent performance as data volume increases. By contrast, standard primary key constraints in Snowflake do not enforce physical row ordering or clustering. Temporary tables are for session-specific use cases, not persistent production datasets. External tables are intended for data that remains in external storage and are not the preferred solution for optimizing a native Snowflake table used for high-volume analytics. This aligns with Snowflake documentation and best practices on table types, constraints, and clustering for large tables.
- A. Correct.
Correct. Defining a clustering key on columns commonly used in selective filters, such as EVENT_DATE and USER_ID, can improve micro-partition pruning as the table grows. This is especially useful for very large tables with frequent filtering on the same columns. It does not require application query changes, and Snowflake manages table storage internally rather than requiring manual repartitioning.
- B. Incorrect.
Incorrect. Temporary tables are session-scoped and are intended for transient, short-lived data. They do not provide a performance optimization mechanism for large persistent workloads, and converting a production raw events table into a temporary table would make the data unavailable outside the session.
- C. Incorrect.
Incorrect. In Snowflake, primary key constraints are generally informational unless using features such as hybrid tables. For standard tables, declaring a primary key does not physically sort or cluster the data, so it would not improve pruning in the way described.
- D. Incorrect.
Incorrect. External tables are designed for querying data stored outside Snowflake, such as files in cloud storage. They are not a replacement for a heavily queried internal table when the goal is to improve pruning of Snowflake-managed table storage. External tables also typically do not provide the same query performance characteristics as native tables.