Performance Tuning — Clustering Keys and Search Optimisation

Snowflake's micro-partition pruning automatically skips irrelevant partitions for filtered queries, but only when the filter column values are clustered together in the same partitions. Data loaded via COPY INTO is initially sorted in the order it was written, which may not align with common query filter patterns. Over time, as data is inserted, updated, and merged, micro-partitions become poorly clustered: rows with the same `match_date` scatter across many partitions, forcing full scans. Clustering Keys and Automatic Clustering address this by physically sorting table data.

Performance tuning in Snowflake differs fundamentally from traditional database tuning. There are no indexes to create, no statistics to update, no buffer pool to tune, and no table partitioning to manage manually — all are handled automatically by Snowflake's Cloud Services layer. The two primary performance levers for Snowflake data engineers are clustering (controlling which columns data is physically sorted by) and result caching (exploiting Snowflake's automatic result cache to eliminate redundant query computation). Most Snowflake performance problems are solved by improving clustering alignment or reducing query complexity.