HOW TO CREATE A TABLE IN SNOWFLAKE?

Are you guys looking for the steps to create a table in snowflake? Do you want to know about the data types and types of tables? Then you are in the right place. Why snowflake? Snowflake provides a Data warehouse as a Service for any workload in the cloud platforms like Azure, Google cloud platform, and AWS. Snowflake database is a new SQL database engine that is designed to interact with cloud infrastructure. There is no need to install, configure, or manage any hardware or software because it runs fully on public cloud infrastructure. It provides unlimited performance and scalability. The warehouse in
snowflake is used to perform operations on the resources. We can use only 1 warehouse at a time. In this, we will discuss the ways to create a database and table.

Before creating the Table in Snowflake let’s first start with creating the Database.

Contents

1 HOW TO CREATE A DATABASE IN SNOWFLAKE?
- 1.1 CREATE A DATABASE IN SNOWFLAKE EXAMPLE
2 How to create Transient Database in Snowflake?
- 2.1 Example Create Transient Database in Snowflake
- 2.2 Example Create Clone Database in Snowflake
3 WHAT ARE THE DIFFERENT WAYS TO CREATE A TABLE IN SNOWFLAKE?
4 How to Create Table As Select in Snowflake?
- 4.1 Example to Create Table As Select in Snowflake?
5 HOW TO CREATE A TABLE WITH DIFFERENT COLUMN NAMES AND TYPES?
- 5.1 Example of create a table with Different column name and type
6 How to Create Table like in Snowflake?
- 6.1 Example of CreateTable Like in Snowflake
7 How to CREATE TABLE CLONE in Snowflake?
- 7.1 Example of Create Table Clone in Snowflake?
8 CREATE TABLE IN SNOWFLAKE DDL QUERY?
9 HOW TO CREATE TABLE WITH PRIMARY KEY IN SNOWFLAKE?
- 9.1 Example of Create table with Primary Key in Snowflake
10 HOW TO CREATE A TABLE IN SNOWFLAKE WITHOUT WRITING QUERY USING UI?
11 What are the different types of Table in Snowflake?
12 What is TEMPORARY TABLE in Snowflake?
13 What is TRANSIENT TABLE in Snowflake?
- 13.1 Example of Transient table in Snowflake
14 What is Permanent Table in Snowflake?
15 Final Thoughts

HOW TO CREATE A DATABASE IN SNOWFLAKE?

Once you login into Snowflake, you will find a worksheet page where you can write SQL queries. The syntax for creating a database is

CREATE DATABASE [DATABASE_NAME]

The DATABASE_NAME can be any user-defined name for the database. It creates a new database in the system

CREATE A DATABASE IN SNOWFLAKE EXAMPLE

create DATABASE  SNOWFLAKE_Practice;


-- Output
+---------------------------------------------------+
| status                                            |
|---------------------------------------------------|
| Database SNOWFLAKE_PRACTICE successfully created. |
+---------------------------------------------------+

CREATE A DATABASE IN SNOWFLAKE FROM PORTAL EXAMPLE

We can also create the database using the UI. Select the database tab
which is present at the top.

You will end up with the page shown below where you can give the database name and add a comment for the database. Select finish after specifying the fields. The comments can be added or ignored.

If we select show SQL, it will display the query for the database which we have created

How to create Transient Database in Snowflake?

Transitory databases are not fail-safe and consume less storage, there is no way to retrieve data when it is lost.

The syntax for creating a transient database is as follows:

CREATE TRANSIENT DATABASE [DATABASE_NAME]

Example Create Transient Database in Snowflake

create TRANSIENT DATABASE SNOWFLAKE_PRACTICE_TRANSIENT;
-- Output
+-------------------------------------------------------------+
| status                                                      |
|-------------------------------------------------------------|
| Database SNOWFLAKE_PRACTICE_TRANSIENT successfully created. |
+-------------------------------------------------------------+

How to create Clone of Database in Snowflake?

The syntax for creating a clone database is as follows:

CREATE DATABASE [DATABASE_NAME] CLONE [SOURCE_DATABASE_NAME]

Example Create Clone Database in Snowflake

Create DATABASE  SNOWFLAKE_PRACTICE_CLONE CLONE SNOWFLAKE_PRACTICE;

-- Output
+---------------------------------------------------------+
| status                                                  |
|---------------------------------------------------------|
| Database SNOWFLAKE_PRACTICE_CLONE successfully created. |
+---------------------------------------------------------+

WHAT ARE THE DIFFERENT WAYS TO CREATE A TABLE IN SNOWFLAKE?

We can create the table either by using the UI or by queries. We can create tables by using the following ways.

CREATE TABLE … AS SELECT
CREATE TABLE … LIKE
CREATE TABLE … CLONE

How to Create Table As Select in Snowflake?

Create table using select helps us to create a new table with the same schema along with the data returned by the query. The syntax is as follows:

CREATE TABLE [TABLE_NAME] as SELECT [COLUMN_NAME] FROM [DATABASE_NAME].[SCHEMA_NAME].[TABLE_NAME]

Example to Create Table As Select in Snowflake?

create table CUSTOMER_1 as Select * from azurelib.public.CUSTOMER;

-- Output
+----------------------------------------+
| status                                 |
|----------------------------------------|
| Table CUSTOMER_1 successfully created. |
+----------------------------------------+

HOW TO CREATE A TABLE WITH DIFFERENT COLUMN NAMES AND TYPES?

The syntax for creating a table with different column names and types is as follows:

CREATE TABLE [TABLE_NAME]([COLUMN_NAMES]) as SELECT [COLUMN_NAMES] FROM [DATABASE_NAME].[SCHEMA_NAME].[SOURCE_TABLE_NAME]

Example of create a table with Different column name and type

create table CUST (C1,C2,C3) as select * from CUSTOMER;

-- Output
+----------------------------------+
| status                           |
|----------------------------------|
| Table CUST successfully created. |
+----------------------------------+

How to Create Table like in Snowflake?

This is used to create a new table with only the structure of an existing table and no data copied. The syntax is as follows:

CREATE TABLE [TABLE_NAME] LIKE [SOURCE_TABLE_NAME].PUBLIC.[DATABASE_NAME]

Example of CreateTable Like in Snowflake

create table CUSTOMER_L LIKE CUSTOMER;

-- Output
+----------------------------------------+
| status                                 |
|----------------------------------------|
| Table CUSTOMER_L successfully created. |
+----------------------------------------+

How to CREATE TABLE CLONE in Snowflake?

Cloning results in the creation of a new table. If you make any modifications to the new table, they have no effect on the original table. The syntax is as follows:

CREATE TABLE [TABLE_NAME] CLONE [SOURCE_TABLE_NAME]

Example of Create Table Clone in Snowflake?

create table CUSTOMER_C CLONE CUSTOMER;

-- Output
+----------------------------------------+
| status                                 |
|----------------------------------------|
| Table CUSTOMER_C successfully created. |
+----------------------------------------+

CREATE TABLE IN SNOWFLAKE DDL QUERY?

On the worksheet page, we can perform DML, DDL operations using SQL queries. The syntax for creating a table is as follows:

CREATE [ OR REPLACE ]
    [ { [ LOCAL | GLOBAL ] TEMP[ORARY] | VOLATILE } | TRANSIENT ]
    TABLE [ IF NOT EXISTS ] <table_name>
    ( <col_name> <col_type>
                             [ COLLATE '<collation_specification>' ]
                                /* COLLATE is supported only for text data types (VARCHAR and synonyms) */
                             [ { DEFAULT <expr>
                               | { AUTOINCREMENT | IDENTITY } [ ( <start_num> , <step_num> ) | START <num> INCREMENT <num> ] } ]
                                /* AUTOINCREMENT (or IDENTITY) is supported only for numeric data types (NUMBER, INT, FLOAT, etc.) */
                             [ NOT NULL ]
                             [ [ WITH ] MASKING POLICY <policy_name> [ USING ( <col_name> , <cond_col1> , ... ) ]
                             [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
                             [ inlineConstraint ]
      [ , <col_name> <col_type> [ ... ] ]
      [ , outoflineConstraint ]
      [ , ... ] )
  [ CLUSTER BY ( <expr> [ , <expr> , ... ] ) ]
  [ STAGE_FILE_FORMAT = ( { FORMAT_NAME = '<file_format_name>'
                           | TYPE = { CSV | JSON | AVRO | ORC | PARQUET | XML } [ formatTypeOptions ] } ) ]
  [ STAGE_COPY_OPTIONS = ( copyOptions ) ]
  [ DATA_RETENTION_TIME_IN_DAYS = <integer> ]
  [ MAX_DATA_EXTENSION_TIME_IN_DAYS = <integer> ]
  [ CHANGE_TRACKING = { TRUE | FALSE } ]
  [ DEFAULT_DDL_COLLATION = '<collation_specification>' ]
  [ COPY GRANTS ]
  [ [ WITH ] ROW ACCESS POLICY <policy_name> ON ( <col_name> [ , <col_name> ... ] ) ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
  [ COMMENT = '<string_literal>' ]

If the table already exists, the REPLACE clause can be used to replace it.

The most frequently used data types in Snowflake are
1)Numeric type
• Number
• Decimal
• Int
• Float
• Double
• Real
2)String type
• Varchar
• Char
• String
3)Date and time type
• Date
• Datetime
• Time
• Timestamp

HOW TO CREATE TABLE WITH PRIMARY KEY IN SNOWFLAKE?

Primary key is used to uniquely identify a row in a table. It cannot have null values and should
have distinct values. The syntax for creating primary key

CREATE TABLE [DATABASE_NAME]."PUBLIC".[TABLE_NAME] ([COLUMN_NAME] DATA TYPE,…,constraint pk_name primary key([COLUMN_NAME]));

Example of Create table with Primary Key in Snowflake

create table emp (name VARCHAR(100), eid int, CONSTRAINT pk_emp PRIMARY Key (eid));

-- Output
+---------------------------------+
| status                          |
|---------------------------------|
| Table EMP successfully created. |
+---------------------------------+

HOW TO CREATE A TABLE IN SNOWFLAKE WITHOUT WRITING QUERY USING UI?

You can create and manage all Snowflake objects, including virtual warehouses, databases, and database objects, once you’ve logged into the Snowflake web-based graphical interface. It gives us the ability to create databases and tables in UI rather than writing queries.

We can create the table using the UI by following a few steps:

Select the database tab. It will display the list of available databases.
Select the database in which we have to create a table.
Select create an option in the table tab.
Specify the table name, comments, and columns with their respective data types.
Click Finish to create the table.

What are the different types of Table in Snowflake?

We can create 3 types of tables in Snowflake– Permanent table, Temporary table and Transient table. The default table type is Permanent table.

What is TEMPORARY TABLE in Snowflake?

The syntax for creating a temporary table is as follows :

create temporary table [TABLE_NAME]([COLUMN_NAME] DATA
TYPE,.. );

Snowflake’s temporary table is only visible for the current session. Temporary tables exist just during the session in which they are created, and they are only available for the duration of that session. Other users or sessions cannot see temporary tables. Some standard functionalities are not supported by temporary tables.

What is TRANSIENT TABLE in Snowflake?

Transient tables can be created in Snowflake and are available to all users with the necessary privileges until they are explicitly deleted. Transient tables are identical to permanent tables, except that they don’t have a fail-safe period. Fail-safe gives a 7-day timeframe during which
Snowflake may be able to retrieve prior data. Transient tables cannot be converted to any other table type once they have been created.

The syntax for creating a Transient table is as follows:

CREATE Transient table [TABLE_NAME]([COLUMN_NAME] DATA TYPE,.. );

Example of Transient table in Snowflake

create Transient table emp (name VARCHAR(100), eid int, CONSTRAINT pk_emp PRIMARY Key (eid));

-- Output
+---------------------------------+
| status                          |
|---------------------------------|
| Table EMP successfully created. |
+---------------------------------+

What is Permanent Table in Snowflake?

It’s a regular database table. It is possible to enable space consumption and a fail-safe timeframe.

Create table Snowflake official Documentation Link

Final Thoughts

Snowflake is easy to use and has a lot of analytics tools in it. It has a very good performance as it has an inbuilt query optimizer. The data is highly secured and we have to pay for the number of resources we have used. It is the only cloud data platform that can function as both a data warehouse and a data lake. Snowflake makes use of a SQL database
engine with a cloud-specific architecture. Someone new to Snowflake will find this easy to find the steps for creating a table in Snowflake. Hope it helps

For Azure Study material Join Telegram group : Telegram group link:
Azure Jobs and other updates Follow me on LinkedIn: Azure Updates on LinkedIn
Azure Tutorial Videos: Videos Link

Join Telegram Group

Download Free Azure Videos

Connect on LinkedIn

How to ALTER TABLE in Snowflake with Practical Example

How to create Task in Snowflake

How to create table dynamically in Snowflake?

Deepak Goyal

Deepak Goyal is certified Azure Cloud Solution Architect. He is having around decade and half experience in designing, developing and managing enterprise cloud solutions. He is also Big data certified professional and passionate cloud advocate.