1.) When a table is very large
2.) When querys degrade over time
You do NOT have to define Snowflake a cluster key on a table, Snowflake will automatically tune querys against tables without a cluster key.
A cluster keys component fields – there can be more than one field in a cluster key – should have a “reasonable cardinality” for example a cardinality in the case of True/False would not be a good candidate field for a cluster key.
Cluster keys are automatically maintained.
Try to pick cluster keys that are similar to your ELT load order – as if you do not, and load frequently automatic maintenance could get expensive.
When choosing what order the fields in a composite cluster key should be in, choose lowest cardinality 1st.
Here is a nice article out of LinkedIn that describes the activity in a little more detail.
https://www.linkedin.com/pulse/clustering-key-design-101-snowflake-minzhen-yang
And here is a link to Snowflake documentation on the subject.
https://docs.snowflake.com/en/user-guide/tables-clustering-keys.html
Cluster Depth should be kept low.
The Snowflake system function SYSTEM$CLUSTERING_DEPTH will show depth. Documentation to this function is here: https://docs.snowflake.com/en/sql-reference/functions/system_clustering_depth.html
tune, optimize, performance, cluster keys, cluster