Define a Snowflake clustering key on a large table, monitor micro-partition pruning efficiency, and control automatic clustering cost

Steps

1. Identify the columns used most frequently in WHERE and JOIN predicates for a large table and define a clustering key: ALTER TABLE orders CLUSTER BY (order_date, region)
2. Check the current clustering depth and overlap before and after: SELECT SYSTEM$CLUSTERING_INFORMATION('orders', '(order_date, region)') — returns average_depth and average_overlaps metrics
3. Monitor query pruning efficiency in the query profile: look for 'Partitions scanned' vs 'Partitions total' in query details — high pruning ratio confirms the clustering key is effective
4. Snowflake's Automatic Clustering service reclusters the table in the background; it incurs compute credit consumption visible in the Automatic Clustering billing line
5. Suspend automatic clustering on a table to stop incurring cost: ALTER TABLE orders SUSPEND RECLUSTER
6. Resume or drop the clustering key: ALTER TABLE orders RESUME RECLUSTER or ALTER TABLE orders DROP CLUSTERING KEY