Question

A data engineer loads several years of transaction data into a large Snowflake table using daily batch loads ordered by transaction_date. Analysts almost always filter queries by transaction_date ranges, and query pruning is already very effective. The engineer is deciding whether to define an explicit clustering key on transaction_date. What is the most appropriate recommendation?

Accepted Answer

Do not add an explicit clustering key, because the natural load order already aligns micro-partitions with transaction_date, providing effective pruning without incurring reclustering credit costs.

Answer

Add a clustering key on transaction_date immediately, because explicit clustering is always required for any table larger than a few gigabytes.

Answer

Add a clustering key on a high-cardinality column such as transaction_id to maximize the number of micro-partitions scanned.

Answer

Convert the table to a materialized view instead, since clustering keys cannot be applied to tables that receive daily batch loads.