How to create an Index in Snowflake

Snowflake does not support Index, but it uses a clustering key to get the query performance. The clustering key is defined as the subset of externally appointed columns for co-locating the data in the table. This blog allows you to create the Clustering key.

Create an Index in Snowflake

Rather than creating or dropping the index in Snowflake, we can utilize the clustering key for achieving the query performance. For creating the clustering key, we use ALTER TABLE . CLUSTER BY command:

Syntax:

alter table table_name cluster by (column1, column2, …, columnN);

Example:

Cluster on a single column

alter table active_users1
cluster by (id1)
Looking forward to a career in a cloud data warehousing domain? Check out the "Snowflake Training" and get certified today

Cluster on multiple columns

alter table active_users1
cluster by (id1, active1)

But, we should be careful about using the clustering keys. The clustering key must be used only when we are handling tables of the below properties:

  • Tables containing a vast amount of data.
  • Data from the tables are filtered frequently.

MindMajix Youtube Channel

Conclusion

Clustering enables us to co-locate the data in similar micro-partitions. It is suitable for huge tables where the ordering is not good. I hope this article gives you adequate information about creating Clustering keys.

Snowflake Related Articles


▶  Snowflake vs Redshift
▶  Snowflake vs BigQuery
▶  Snowflake vs Databricks
▶  Snowflake vs Azure
▶  Snowflake vs Hadoop
▶  Snowflake Time Travel

Job Support Program

Online Work Support for your on-job roles.

jobservice

Our work-support plans provide precise options as per your project tasks. Whether you are a newbie or an experienced professional seeking assistance in completing project tasks, we are here with the following plans to meet your custom needs:

  • Pay Per Hour
  • Pay Per Week
  • Monthly
Learn MoreGet Job Support
Course Schedule
NameDates
Snowflake TrainingDec 07 to Dec 22View Details
Snowflake TrainingDec 10 to Dec 25View Details
Snowflake TrainingDec 14 to Dec 29View Details
Snowflake TrainingDec 17 to Jan 01View Details
Last updated: 04 Apr 2023
About Author

Viswanath is a passionate content writer of Mindmajix. He has expertise in Trending Domains like Data Science, Artificial Intelligence, Machine Learning, Blockchain, etc. His articles help the learners to get insights about the Domain. You can reach him on Linkedin

read less
  1. Share:
Snowflake Articles