How to create Table in Snowflake: Easy Guide

Getting your Trinity Audio player ready...

Data storage, processing, and analytic solutions have become vital for modern large-scale companies. Every fast-growing company is struggling to analyze the massive amount of data scattered at different data sources. Advanced Software as Service (SaaS) data platforms like Snowflake help you store, process, and analyze massive amounts of data. If you are considering Snowflake as your solution and are having problems creating databases and tables in Snowflake, this article will help you understand how to create tables in Snowflake.

Table of Contents

• Prerequisites: SQL Database, and Tables, Snowflake Account
• Introduction to Snowflake
• Use Case of Snowflake
• Creating Database in Snowflake
• Types of Table in Snowflake
• Method to create Table in Snowflake
• Conclusion

Prerequisites: SQL Database, and Tables, Snowflake Account

Before jumping into table creation in snowflake, basic knowledge about SQL databases and SQL tables is required. A database is a collection of tables that store a particular set of structured data. The table comprises rows and columns. Rows in a table are also known as records or tuples. Columns in the table can also be referred to as attributes.
Besides the basics of SQL database and table, you need a snowflake account as well.

If you don’t have a snowflake account, you can click here to create one.

Introduction to Snowflake

Snowflake is a data warehouse that provides data platforms as Cloud Services. It offers SaaS flexibilities. In a nutshell, the three main features of a snowflake are as follows:

1. No hardware installation, configuration, or maintenance is required.
2. The tedious challenge of software installation, configuration, and maintenance gets abstracted.
3. Flexible maintenance, software upgrades, and management. All of these are handled by snowflake itself, with no extra manpower required.

Use Case of Snowflake

Many fintech, technical and finance-related companies like AT&T, Cisco, Doordash, Greyhound, and Citi are using Snowflake as their data warehousing solution.

Snowflake is a data warehousing solution, its use cases comprise:

• Data Storage: Cloud storage is more scalable and generally less expensive than on-premises traditional storage You can easily scale in/out with Snowflake.
• Business Reporting at scale: With a data warehouse solution like Snowflake business reporting, and analysis at a large scale are quite manageable.
• Analytics at any scale: Snowflake lets you analyze data at any scale to get the information you need. Integrating it into a broader system can add value to operational business applications.

Creating a Database in Snowflake

A Snowflake database can be used for the following three purposes:

• Creating a database for cloning the existing database at or before a specific point in the past.
• Creating a database from a share provided by another snowflake account.
• Creating a replica of the existing primary database.

There are three types of syntax to create a database in snowflake resonating with the above three purposes.

Syntax to Create Standard Database

CREATE [ OR REPLACE ] [ TRANSIENT ] DATABASE [ IF NOT EXISTS ] <name>

    [ CLONE <source_db>

          [ { AT | BEFORE } ( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> } ) ] ]

    [ DATA_RETENTION_TIME_IN_DAYS = <integer> ]

    [ MAX_DATA_EXTENSION_TIME_IN_DAYS = <integer> ]

    [ DEFAULT_DDL_COLLATION = ‘<collation_specification>‘ ]

    [ [ WITH ] TAG ( <tag_name> = ‘<tag_value>‘ [ , <tag_name> = ‘<tag_value>‘ , … ] ) ]

    [ COMMENT = ‘<string_literal>‘ ]

Where,

• <name> is the unique identifier for your database.
• CLONE source_db is an optional parameter. It specifies creating a clone of the specified source database.
• AT | BEFORE ( TIMESTAMP => timestamp | OFFSET => time_difference | STATEMENT => id )

While cloning a database, the AT | BEFORE clause signifies the use of Time Travel to clone the database at or before a specific point in the past.

Syntax to Create Shared Database (from a Share)

CREATE DATABASE <name> FROM SHARE <provider_account>.<share_name>

Where,

• <name> is the unique identifier for your database.
• <provider_account>.<share_name> represents the identifier of the share and account name of database shared.

Syntax to Create Secondary Database (Database Replication)

CREATE DATABASE <name>

    AS REPLICA OF <account_identifier>.<primary_db_name>

    [ DATA_RETENTION_TIME_IN_DAYS = <integer> ]

Where,

• <name> is the unique identifier for your database.
• AS REPLICA OF account_identifier.primary_db_name signifies the identifier for a primary database that is used to create a replica (i.e. a secondary database).

Types of Tables in Snowflake

There are three types of tables in Snowflake. These three types are as follows:

1. Temporary tables
2. Transient tables
3. Permanent tables

Let’s understand them one by one.

1. Temporary tables – Temporary tables are those tables that only exist within the sessions it is created. To other users and sessions, these tables are not visible.
2. Transient tables – Transite tables persist until when they are not dropped explicitly. All users with appropriate privileges can access these tables.
3. Permanent tables – Permanent tables are quite similar to transient tables, the only major difference is that they do have a Fail-safe period which provides an extra level of data protection and recovery.

Method to create Table in Snowflake

With the Snowflake CREATE TABLE command, you can create a new table or replace an existing table. The table consists of multiple columns. To create a table you need a column name, the column data type, and optionally whether the column. A table in snowflake requires the following:

• A value (NOT NULL).
• Already has a default value.
• Has any referential integrity constraints (primary key, foreign key, etc.).

Besides the above-mentioned factors, this command also supports the following 4 variations:

• CREATE TABLE … AS SELECT – It is used to create a populated table; also known as CTAS.
• CREATE TABLE … USING TEMPLATE –  It is used to create a table with the column definitions derived from a set of staged files.
• CREATE TABLE … LIKE – It is used to create an empty copy of an existing table.
• CREATE TABLE … CLONE – It is used to create a clone of an existing table.

The syntax for Create Table As Select in Snowflake

When you want to create a new table from an existing table schema and populate data resultant returned by a query, Create table as Select(CATS) is a good fit.

CREATE [ OR REPLACE ] TABLE <table_name> [ ( <col_name> [ <col_type> ] , <col_name> [ <col_type> ] , … ) ]  [ CLUSTER BY ( <expr> [ , <expr> , … ] ) ] [ COPY GRANTS ] AS SELECT <query>   [ … ]

The syntax for CREATE TABLE LIKE in Snowflake

When you want to create a replica of an existing table, CREATE TABLE LIKE is useful. It is used to create a new table with a similar column definition but no data copied from the existing table. Only column name, column data type, defaults, and constraints will be copied to the new table.

CREATE [ OR REPLACE ] TABLE <table_name> LIKE <source_table>  [ CLUSTER BY ( <expr> [ , <expr> , … ] ) ]  [ COPY GRANTS ]  [ … ]

The syntax for CREATE TABLE CLONE in Snowflake

When you want to create a new table with a similar column definition as the source table and containing all the existing data from the source table, without actually copying the data, this variant can be used. You can use this command also to clone a table at a specific time/point in the past.

CREATE [ OR REPLACE ] TABLE <name> CLONE <source_table>

[ { AT | BEFORE } ( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> } ) ]

[ COPY GRANTS ]

[ … ]

Follow this article for further information.

Conclusion

In this blog, we talked about a brief introduction of Snowflake, its use case of it, and methods to create a database in snowflake along with the types of tables present in it. Moreover, you got an overview of methods to create various types of tables in Snowflake.

About Author

• Name: Kamya
• Marketing Analyst SEO Intern