How to Create an Index in Snowflake with Clustering Keys
Let's immediately clarify one thing: Snowflake doesn't support indices. Instead of creating or dropping an index in Snowflake, you can use clustering keys to accomplish query performance. This tutorial will show you how to define a clustering key for a particular table.
To create clustering key, use the
ALTER TABLE .. CLUSTER BY command:
-- syntax alter table table_name cluster by (column1, column2, .., columnN); -- create clustering key on one column alter table active_users cluster by (id) -- create clustering on multiple columns alter table active_users cluster by (id, active)
However, you should be very careful about using clustering keys. Clustering keys should be used only in case you are dealing with tables of the following properties:
- Tables containing huge amount of data (i.e. measured in terabytes).
- Data from tables are filtered on a regular basis.
Note: Unlike other technologies that allow us creation of multiple indices on table, Snowflake lets us create only one clustering key per table!