Clusters

  Snowflake cloud data warehouse produces create clustered tables by default. However, as the table size grows and DML occurs on the table, the data in some table rows may no longer cluster optimally on desired dimensions. In this article, we will check how to create Snowflake clustered tables to improve the DML query performance.

Snowflake Clustered Tables 

 When you create a table and insert records into the Snowflake tables, inserted rows are grouped into continuous storage such as micro-partitions. But, as the table size grows, data in some column may no longer cluster optimally. Clustering is not intended for all tables, you will add cluster key to improve the clustering of the underlying table micro-partitions.

The cluster columns can be a subset of the table columns, or an expression of the tables. A table with a clustering key defined is considered to be clustered table.

What is Snowflake Clustering Key?

A clustering key is a subset of columns in a table or an expression that are explicitly used to co-locate the data in the table in the same micro-partition.

This is very useful for the large tables (multi-TB) where ordering of the column was not optimal or extensive DML operation on the table has caused the table’s natural clustering to degrade.

So, which column(s) should be clustering key?

Following are the recommended columns as a clustering key.

• Cluster columns that are most actively used in selective filters. For example, many fact tables involves the date column as a filter condition (for example “WHERE invoice_date = X” ), choosing the date column is a good idea.
• Consider columns frequently used in join predicates.

The number of distinct values in a column/expression is a critical aspect of selecting it as a clustering key.

You should identify a clustering key that has:

• A large enough number of distinct values to enable effective pruning on the table.
• A small enough number of distinct values to allow Snowflake to effectively group rows in the same micro-partitions.

Note that, if you define two or more columns/expressions as the clustering key for a table, the order has an impact on how the data is clustered in micro-partitions. Snowflake recommends ordering the columns from lowest cardinality to highest cardinality.

Snowflake Clustered Table Syntax

You can use following syntax to create clustered tables in Snowflake.

CREATE TABLE <name> ... CLUSTER BY ( <expr1> [ , <expr2> ... ] )

Snowflake Clustered Table Examples

Consider following example to create table with clustering key.

create or replace table sn_clustered_table

(c1 date, c2 string, c3 number)

cluster by (c1, c2);

Alter Snowflake Table to Add Clustering Key

You can add the clustering key while creating table or use ALTER TABLE syntax to add a clustering key to existing tables.

Following is the syntax to add a clustering key to existing Snowflake table.

ALTER TABLE <name> CLUSTER BY ( <expr1> [ , <expr2> ... ] )

For example,

alter table sn_clustered_table2 cluster by (c1, c3);

Dropping the Clustering Keys in Snowflake

If the query performance is not as per expectation, you can drop the clustering key.

Following is the syntax to drop the clustering key on Snowflake table.

ALTER TABLE <name> DROP CLUSTERING KEY

For example,

ALTER TABLE sn_clustered_table2 DROP CLUSTERING KEY

Reclustering in Snowflake

Reclustering in Snowflake is automatic; no maintenance is needed. During reclustering, Snowflake uses the clustering key for a clustered table to reorganize the column data, so that related records are relocated to the same micro-partition. This DML operation deletes the affected records and re-inserts them, grouped according to the clustering key.

Limitation of Clustering in Snowflake

Following are some of the limitations on clustered Tables.

• An existing Clustering key is not propagated when using CREATE TABLE … LIKE operation.
• An existing clustering key is not supported when a table is created using CREATE TABLE … AS SELECT; however, you can use the ALTER TABLE command to define a clustering key after the table is created.
• You cannot use a VARIANT column in clustering key. However, you can specify the expression to extract value in clustering key.
• CREATE TABLE … CLONE will retain the clustering key.

Conclusion :

Snowflake does not support indexes, though it does support "clustering" for performance improvements of I/O.

Snowflake does not use indexes. This is one of the things that makes Snowflake scale so well for arbitrary queries. Instead, Snowflake calculates statistics about columns and records in files that you load, and uses those statistics to figure out what parts of what tables/records to actually load to execute a query

Snowflake doesn't support indexes, it keeps data in micro partition or in another sense it breaks data sets in small files and format rows to column and compress them. Snowflake metadata manager in service layer will have all the information about each micro partition like which partition have which data. Each partition will have information about itself in header like max value, min value, cardinality etc. this is much better then indexes as compare to conventional databases.

 

 

Oracle Vs Snowflake

Constraints:

Snowflake NOT NULL Constraint:

Constraints other than NOT NULL are created as disabled. Snowflake enforces only NOT NULL. You can create NOT NULL constraint while creating tables in the cloud database.

A Snowflake table can have multiple NOT NULL columns.

Snowflake NOT NULL Constraint Syntax

There are many methods that you can use to add NOT NULL on Snowflake table.

·         Column level NOT NULL – Add NOT NULL constraint during table creation.

·         Alter Table to Add NOT NULL – Use Alter table command to add NOT NULL constraint.

Column level NOT NULL

You can add the NOT NULL to the Snowflake table DDL along with column the data type.

For example, consider below table DDL with a column ID defined as NOT NULL.

CREATE TABLE nn_demo_table 

  ( 

     id      INT NOT NULL, 

     NAME    VARCHAR(10), 

     address VARCHAR(100)

  );

Now, the Snowflake database will allow only non-null values in the ID column. You will end up getting an error if the value is NULL.

Alter Table to Add NOT NULL Constraint

You can also add the NOT NULL constraint to the existing table. The table must be empty in order to SET NOT NULL constraint.

For example, consider following ALTER statement to add NOT NULL with default value.

ALTER TABLE nn_demo_table MODIFY COLUMN ID SET NOT NULL;

+----------------------------------+

| status                           |

|----------------------------------|

| Statement executed successfully. |

+----------------------------------+

Test Add NOT NULL Constraint

Now, let us try to insert the NULL values into the not null column and check the error.

>INSERT INTO nn_demo_table values (1,'a','abc');

+-------------------------+

| number of rows inserted |

|-------------------------|

|                       1 |

+-------------------------+

>INSERT INTO nn_demo_table values (null,'a','abc');

100072 (22000): NULL result in a non-nullable column

As you can see in the above example, you can only insert non-null values. Therefore, Snowflake cloud data warehouse enforces the NOT-NULL constraint.

Conclusion :In both oracle and snowflake we can add NOT NULL constraint at Column level only not at table level..

Snowflake Unique Key Constraint

You can create Unique key constraint while creating tables in the Snowflake cloud database but it will not be enforced while loading tables. The Snowflake query planner uses these constraints to create a better query execution plan.

Similar to the foreign key, a table can have multiple unique key defined on it.

Snowflake Unique Key Constraint Syntax

There are three methods that you can use to add unique keys on Snowflake table.

·         Column level Unique Key – Add unique key constraint during table creation.

·         Table Level Unique Key – You can define unique key at the end of column definition after all columns are defined.

·         Alter Table to Add Unique Key – User Alter table command to add unique key constraint.

Column Level Unique Key

You can mention if the particular column is unique on column level when you create tables.

For example, consider below DDL for column level unique key.

CREATE TABLE uk_demo_table

  (

     id      INT UNIQUE,

     NAME    VARCHAR(10),

     address VARCHAR(100)

  );

Table Level Primary Key

You can also add the unique key on table level.

For example,

CREATE TABLE uk_demo_table

  (

     id      INT,

     NAME    VARCHAR(10),

     address VARCHAR(100),

           UNIQUE(id)

  );

Alter Table to Add Snowflake Unique Key

You can use ALTER TABLE command to add unique key.

For example,

ALTER TABLE uk_demo_table ADD UNIQUE (id);

Test Unique Constraint on Snowflake

As mentioned earlier, Unique key will not be enforced when you insert records. You can insert duplicate records.

For example,

INSERT INTO uk_demo_table values (1,'a','abc'), (1,'a','abc');

 

+-------------------------+

| number of rows inserted |

|-------------------------|

|                       2 |

+-------------------------+

From the above example, you can see duplicated records are inserted. In

conclusion, a unique key is not enforced in snowflake but enforced in oracle..

Snowflake Primary Key Constraint

You can create the primary key while creating tables on the Snowflake cloud data warehouse. But, primary key will not be enforced when you load the tables. However, constraints provide valuable metadata. The optimizer uses the primary key to create an optimal execution plan.

A table can have multiple unique keys and foreign keys, but only one primary key.

Snowflake Primary Key Constraint Syntax

There are many methods that you can use to add foreign keys on Snowflake table.

·         Column level Primary Key – Add primary key constraint during table creation.

·         Table Level Primary Key – You can define primary key at the end of column definition after all columns are defined.

·         Alter Table to Add Primary Key – User Alter table command to add primary key constraint.

Column Level Primary Key

For example, consider below DDL for column level primary key

CREATE TABLE pk_demo_table 

  ( 

     id      INT PRIMARY KEY, 

     NAME    VARCHAR(10), 

     address VARCHAR(100) 

  ); 

Table Level Primary Key

You can also add the foreign key on table level.

For example,

CREATE TABLE pk_demo_table

  (

     id      INT,

     NAME    VARCHAR(10),

     address VARCHAR(100),

     PRIMARY KEY (id)

  );

Alter Table to Add Snowflake Primary Key

You can use ALTER TABLE command to add primary key.

For example,

ALTER TABLE pk_demo_table1 ADD PRIMARY KEY (id);

In the previous sections, we have mentioned that Primary key is not enforced. We can test the same by inserting duplicate values into a table.

INSERT Duplicate Records

Following INSERT statement examples adds the duplicate records to the table which has a primary key defined on one of its column.

INSERT INTO pk_demo_table1 values (1,'a','abc'), (2,'b','bcd'), (1,'a','abc');

+-------------------------+

| number of rows inserted |

|-------------------------|

|                       3 |

+-------------------------+

As you can see, rows are inserted without any issues. 

Hence, the primary key constraint is not enforced in snowflake but enforced in oracle

Snowflake Foreign Key Constraint

You can create the foreign key while creating tables on the Snowflake cloud data warehouse. But, foreign key will not be enforced when you load the tables. However, constraints provide valuable metadata. The optimizer uses the foreign keys to create an optimal execution plan.

A table can have multiple unique keys and foreign keys. A table can refer multiple tables. But, a column can refer single table.

Snowflake Primary Key Constraint Syntax

There are many methods that you can use to add foreign keys on Snowflake table.

·         Column level Foreign Key – Add foreign key constraint during table creation.

·         Table Level Foreign Key – You can specify the foreign key after all columns are defined.

·         Alter Table to Foreign Key – User Alter table command to add foreign key constraint.

Column Level Foreign Key

You can add the foreign on the column level.

For example, consider below DDL for column level foreign key.

CREATE TABLE fk_demo_table

  ( 

     id      INT references pk_demo_table1(id), 

     NAME    VARCHAR(10), 

     address VARCHAR(100) 

  ); 

Table Level Foreign Key

You can also add the foreign key on table level.

For example,

CREATE TABLE fk_demo_table

  ( 

     id      INT , 

     NAME    VARCHAR(10), 

     address VARCHAR(100),

             foreign key (id) references pk_demo_table1(id)

  );

Alter Table to Add Snowflake Foreign Key

You can use ALTER TABLE command to add foreign key.

For example, consider below alter statement to add foreign key to existing Snowflake DDL.

ALTER TABLE fk_demo_table ADD FOREIGN KEY (id) REFERENCES pk_demo_table1(id);  

Check Foreign Key

In Snowflake, foreign key constraint is not enforced. You can insert values in tables that are not present in the parent table or reference table.

The parent table contains:

+----+------+---------+

| ID | NAME | ADDRESS |

|----+------+---------|

|  1 | a    | abc     |

|  2 | b    | bcd     |

|  1 | a    | abc     |

+----+------+---------+

Below statement will insert record to the table;

insert into fk_demo_table values (100,'A2A','ZZZ');

Hence, the foreign key constraint is not enforced in snowflake but enforced in oracle..

Snowflake supports primary, foreign key(s) and unique key(s), but, does not enforce them. Snowflake table allows you to insert duplicate rows. There are chances that some application may insert the records multiple times. There are sever methods you can use to de-duplicate the snowflake tables.

 

Remove Duplicate Records from Snowflake Table

There are many methods that you can use to remove the duplicate records from the Snowflake table. For example, use the DISTINCT keyword to remove duplicate while retrieving rows.

The following methods can be used to remove duplicate records Snowflake table.

·         Use DISTINCT Keyword

·         ALTER TABLE using SWAP WITH method

·         Use ROW_NUMBER Analytic function

·         Use GROUP BY Clause to Remove Duplicate Records

·         Now, let us check these methods in brief.

·         Use Intermediate and Snowflake DISTINCT Keyword

·         This is one of the easiest methods to remove the duplicate records from the table. This method works perfectly when your table is populated twice.

·         The idea here is to create another intermediate table using the DISTINCT keyword while selecting from the original table.

For example, below query creates an intermediate table using DISTINCT keyword.

CREATE TABLE int_sample_table3 AS SELECT DISTINCT * FROM sample_table3;

ALTER TABLE sample_table3 RENAME TO sample_table3_old;

ALTER TABLE int_sample_table3 RENAME TO sample_table3;

DROP TABLE sample_table3_old;

As you can see, this method is pretty simple and worked perfectly fine if table size is small.

ALTER TABLE using SWAP – Used Along with DISTINCT

The SWAP WITH method swaps all content and metadata between two specified tables. The SWAP will rename two tables in a single transaction.

For example, Following example demonstrates usage of SWAP in alter table.

CREATE OR REPLACE TABLE sample_table3_dedup LIKE sample_table3;

INSERT INTO sample_table3_dedup SELECT DISTINCT * FROM sample_table3;

ALTER TABLE sample_table3_dedup SWAP WITH sample_table3;

DROP TABLE sample_table3_dedup;

 

Use ROW_NUMBER Analytic Function To Remove Dulicate

The row_number Snowflake analytic function is used to rank or number the rows. Here, we use the row_number function to rank the rows for each group of records and then select only one record from that group.

For example, consider below example to create intermediate table using analytical functions to remove duplicate rows.

SELECT col1, col2

FROM

(

 SELECT col1, col2

 ROW_NUMBER() OVER (PARTITION BY col1, col2  ORDER BY col1, col2) RNO

 FROM sample_table3

)

WHERE RNO = 1;

 

Eg:

(

SELECT *

FROM

(

 SELECT school.*,

 ROW_NUMBER() OVER (PARTITION BY 1, 2  ORDER BY 1, 2   ) RNO FROM school

)

WHERE RNO = 1;)

GROUP BY Clause to Remove Duplicate

You can use the GROUP BY clause as an alternative to DISTINCT keyword.

For example, consider below Snowflake query to get unique records from a table using GROUP BY.

SELECT col1, col2, col3

FROM SAMPLE_TABLE3

GROUP BY col1, col2, col3;

This method is usually faster on small tables.

Snowflake supports defining and maintaining constraints, but does not enforce them, except for NOT NULL constraints, which are always enforced. Constraints are provided primarily for data modeling purposes and compatibility with other databases, as well as to support client tools that utilize constraints.