ARA-C01 Question 400
Single answerNatural clusteringA retail company stores 8 TB of order history in a Snowflake table named ORDERS. The table receives nightly batch loads of new data appended by ORDER_DATE, and analysts primarily run selective queries for the last 7-30 days using predicates on ORDER_DATE. The architect is considering whether to define an explicit clustering key on ORDER_DATE. Which action is the MOST appropriate based on Snowflake best practices for natural clustering?
- A
Do not define a clustering key yet, because appending rows in ORDER_DATE order is likely to maintain good natural clustering for those query patterns.
- B
Define a clustering key on ORDER_DATE immediately, because all large tables with date filters require explicit clustering.
- C
Create a materialized view on ORDERS ordered by ORDER_DATE, because materialized views physically sort base table micro-partitions.
- D
Recreate the table after every nightly load using ORDER BY ORDER_DATE, because Snowflake only preserves pruning when data is manually resorted.
Show answer and explanation
Correct answer: A
Explanation
Snowflake uses micro-partition metadata to prune data during query execution. When data is loaded in a pattern that aligns with common filter predicates, such as appending records by ORDER_DATE and querying recent date ranges, the table may benefit from natural clustering without an explicit clustering key. Snowflake documentation and best practices recommend assessing whether pruning is already effective before enabling clustering, because clustering keys can improve performance for some workloads but also introduce ongoing maintenance cost. In this scenario, the ingestion pattern and query pattern are aligned, so the most appropriate architectural choice is to rely on natural clustering first and only add explicit clustering if monitoring shows inadequate pruning or increasing clustering depth over time.
- A. Correct.
Correct. This scenario is a strong candidate for natural clustering. Snowflake automatically organizes data into micro-partitions as data is loaded. When inserts are appended in increasing ORDER_DATE order and queries filter on that same column, the table can naturally maintain good micro-partition pruning without the cost of defining and maintaining an explicit clustering key. Best practice is to evaluate actual pruning and clustering depth before adding clustering, since explicit clustering adds maintenance cost.
- B. Incorrect.
Incorrect. A common misconception is that any large table filtered by date should automatically have a clustering key. In Snowflake, explicit clustering is not required for every large table. If the load pattern already aligns with the query predicate, natural clustering may provide sufficient pruning. Defining a clustering key unnecessarily can increase compute and maintenance costs without meaningful performance benefit.
- C. Incorrect.
Incorrect. Materialized views can improve performance for some repeated query patterns, but they do not exist to physically reorder the base table's micro-partitions. Also, Snowflake does not support using an ORDER BY clause to physically persist row ordering in a table or materialized view in the way implied here. This option reflects a misunderstanding of how Snowflake stores data and how materialized views work.
- D. Incorrect.
Incorrect. Rebuilding the table after each load to force ordering is generally unnecessary and inefficient. Snowflake does not rely on persistent row order semantics like traditional systems, and repeated full-table rewrites would add significant cost and operational complexity. If the load pattern already appends data in date order, natural clustering can often be sufficient for pruning.