SnowPro Associate: Platform Question 197
Single answer○ Structured dataA retail company stores sales data in a Snowflake table named SALES with the following structured columns: ORDER_ID NUMBER, ORDER_DATE DATE, CUSTOMER_ID NUMBER, and AMOUNT NUMBER(10,2). Analysts frequently run reports that filter by ORDER_DATE and aggregate AMOUNT by CUSTOMER_ID. The table has grown significantly, and query performance has become inconsistent. Which action would be the most appropriate first step to improve performance for this structured data workload?
- A
Create a clustering key on ORDER_DATE and CUSTOMER_ID to improve micro-partition pruning for the common filter and grouping patterns
- B
Convert the structured columns into a single VARIANT column so Snowflake can optimize storage automatically
- C
Create a materialized view for every reporting query because materialized views always eliminate the need to scan base table data
- D
Increase the size of every virtual warehouse permanently because larger warehouses are the primary method for optimizing structured data queries
Show answer and explanation
Correct answer: A
Explanation
This question focuses on structured relational data stored in standard Snowflake column types. For large tables with repeated filtering and aggregation patterns, clustering keys can help improve micro-partition pruning, especially when performance becomes inconsistent as data volume grows. Snowflake automatically manages micro-partitions, but clustering can make data layout more aligned with frequently used columns such as dates and customer identifiers. Native structured data types like NUMBER and DATE are the correct modeling choice for this scenario; replacing them with VARIANT would be a misuse of semi-structured storage. Materialized views and warehouse scaling can be valid optimization tools in some scenarios, but they are not the most appropriate first action here. This aligns with Snowflake best practices on table clustering, micro-partitions, and choosing native structured types for relational data.
- A. Correct.
Correct. For a large table with common access patterns on ORDER_DATE and CUSTOMER_ID, defining a clustering key can improve micro-partition pruning and make query performance more consistent. Snowflake automatically organizes table data into micro-partitions, and clustering can help align the physical data layout with frequent filter patterns. This is a practical first step when query patterns are known and the table is large enough for clustering to provide benefit.
- B. Incorrect.
Incorrect. Converting well-defined structured columns into a single VARIANT column is not appropriate for this use case. Structured relational data such as dates, numeric IDs, and amounts is typically best stored in native column types to preserve type enforcement, compression efficiency, and straightforward query optimization. VARIANT is designed for semi-structured data, not as a performance optimization for standard relational workloads.
- C. Incorrect.
Incorrect. Materialized views can improve performance for some repeated query patterns, but creating one for every reporting query is not a best practice and would add maintenance cost. Also, materialized views do not universally eliminate scans of the base table; whether they are used depends on query compatibility and optimizer decisions. This option reflects an overly broad assumption about materialized views.
- D. Incorrect.
Incorrect. Increasing warehouse size can reduce query runtime by adding compute, but it does not address the underlying data organization issue causing inconsistent performance. For structured data workloads with recurring filter patterns, improving pruning through clustering is often more targeted than permanently scaling up compute. Warehouse resizing may help in some cases, but it is not the best first step here.