ARA-C01 Question 216
Single answerTablesA retail company stores 12 TB of order history in a Snowflake table named ORDERS. The table is updated continuously by ETL jobs, and most analyst queries filter on ORDER_DATE, REGION, and CUSTOMER_ID. Performance has become inconsistent because some queries scan far more data than expected. The architect wants to improve pruning efficiency without requiring application changes or creating multiple copies of the data. Which action should be recommended?
- A
Define a clustering key on (ORDER_DATE, REGION, CUSTOMER_ID) for the ORDERS table and allow Snowflake to maintain clustering depth over time
- B
Convert the ORDERS table to a temporary table so that Snowflake can rebuild micro-partitions more frequently
- C
Create additional virtual warehouses dedicated to queries on ORDER_DATE, REGION, and CUSTOMER_ID so scans are automatically reduced
- D
Replace the ORDERS table with an external table on cloud storage so partition pruning can be controlled directly in object paths
Show answer and explanation
Correct answer: A
Explanation
The best recommendation is to define a clustering key on the columns most commonly used to filter the large ORDERS table. In Snowflake, query pruning depends on micro-partition metadata such as value ranges. As tables undergo continuous inserts, updates, and deletes, the natural clustering of data can degrade, causing more micro-partitions to be scanned than necessary. A clustering key helps co-locate related rows and improve pruning for selective predicates. This is a table design optimization and does not require application changes. Virtual warehouses affect compute capacity, not storage pruning. Temporary tables are not appropriate for persistent enterprise datasets. External tables are intended for external data lake access, not as a performance replacement for large native Snowflake tables. Snowflake documentation on clustered tables and micro-partitions supports using clustering keys selectively for very large tables with predictable filter patterns, while weighing the added maintenance cost against performance gains.
- A. Correct.
Correct. For a large, frequently queried table where filters commonly use specific columns, defining an appropriate clustering key can improve micro-partition pruning and reduce the amount of data scanned. Clustering is especially relevant when natural data ordering has degraded due to ongoing DML activity. This approach improves pruning behavior without requiring application query changes or duplicating data. Snowflake uses clustering metadata at the micro-partition level, and a clustering key on commonly filtered columns such as ORDER_DATE, REGION, and CUSTOMER_ID is a practical design choice when the benefit justifies the maintenance cost.
- B. Incorrect.
Incorrect. Temporary tables are session-scoped objects intended for transient, short-lived workloads. Converting a large production fact table to a temporary table would not improve pruning behavior and would make the data unavailable outside the session. Temporary tables do not provide any special mechanism for rebuilding micro-partitions to optimize query pruning.
- C. Incorrect.
Incorrect. Additional virtual warehouses can provide more compute concurrency or faster execution through more processing power, but they do not change table storage layout or micro-partition pruning. If queries are scanning too much data due to poor pruning, scaling or isolating compute does not address the root cause.
- D. Incorrect.
Incorrect. External tables are designed for querying data in external cloud storage and have different performance and management characteristics than native Snowflake tables. Replacing a heavily used internal table with an external table would typically worsen performance for this scenario and adds operational complexity. External object path partitioning is not a substitute for optimizing native Snowflake table pruning on a large internal transactional history table.