How do you know that a table in Snowflake might benefit from a Cluster Key and how to define them

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

Leave a Comment

Scroll to Top