Using Snowflake Create Table Made Easy: 2 Critical Aspects

Are you just getting started with Snowflake? Looking to learn more about creating tables in Snowflake? You are in the right place, we have just the information you need. Get an overview of Snowflake and learn how to use Snowflake Create Table. All you need is a Snowflake account and some knowledge of SQL.

Table of Contents

• What is Snowflake?
  • Key Features of Snowflake
• How to Create a Database in Snowflake?
  • Create a Database in Snowflake With Example
• How to Create a Table with Different Column Names and Types?
• How to Create Tables in Snowflake?
• CREATE TABLE Command Examples
• Create Table in Snowflake DDL Query?
• How to Create a Table in Snowflake Without Writing Query Using UI?
• What are the Different Types of Tables in Snowflake?
  • Permanent Table
  • Temporary Table
  • Transient Table
• Conclusion

What is Snowflake?

It was until recently that setting up a data warehouse required purchasing expensive, custom hardware and running it in your data center. However, Snowflake, a data warehouse provided as Software-as-a-Service (SaaS) eliminates the need for installing any new hardware. Snowflake is built on top of the Amazon Web Services or Microsoft Azure cloud infrastructure. With Snowflake, there is no need to select, install, configure, or manage any hardware or software, making it perfect for organizations that do not want to dedicate resources to set up and maintain in-house servers.

There are other cloud-based data warehouses in the market but what sets Snowflake apart is its architecture and data sharing capabilities. The Snowflake architecture enables the independence of data storage from computation enabling them to scale separately and letting customers pay less. Snowflake also has excellent data-sharing capabilities, allowing for easy collaboration and real-time decision making.

Key Features of Snowflake

Here are some of the benefits of using Snowflake as a Software as a Service (SaaS) solution:

• Snowflake enables you to enhance your Analytics Pipeline by transitioning from nightly Batch Loads to Real-time Data Streams, allowing you to improve the quality and speed of your analytics. By enabling secure, concurrent, and monitoring access to your Data Warehouse across your organization, you can improve the quality of analytics at your company.
• Snowflake uses the Caching Paradigm to swiftly deliver the results from the cache. To avoid re-generation of the report when nothing has changed, Snowflake employs Persistent (within the session) Query results.
• Snowflake allows you to get rid of silos and ensure access to meaningful insights across the enterprise, resulting in better Data-driven Decision-Making. This is a crucial first step toward bettering partner relationships, optimizing pricing, lowering operational expenses, increasing sales effectiveness, and more.
• Snowflake allows you to better analyze Customer Behaviour and Product Usage. You can also use the whole scope of data to ensure customer satisfaction, drastically improve product offers, and foster Data Science innovation.
• Snowflake allows you to create your own Data Exchange, which allows you to securely communicate live, controlled data. It also encourages you to improve data relationships throughout your business units, as well as with your partners and customers.

How to Create a Database in Snowflake?

• Log in to your Snowflake account and navigate to the worksheet where you can run SQL queries.
• The syntax to create a Database in Snowflake is given below:

CREATE DATABASE [DATABASE_NAME]

• Here, “DATABASE_NAME” is the user-defined name of the Database.

Create a Database in Snowflake With Example

• The SQL query to create a Database in Snowflake is given below:

create DATABASE  SNOWFLAKE_Practice;
  
-- Output
+---------------------------------------------------+
| status                                            |
|---------------------------------------------------|
| Database SNOWFLAKE_PRACTICE successfully created. |
+---------------------------------------------------+

Create a Database in Snowflake Using Portal 

• You can also create a Database in Snowflake using UI Portal.
• Click on the “Database” tab at the Portal’s top.
• Here, click on the “Create” button to create a Snowflake Database.
• It will ask you to provide the Database name and comments as an optional field.
• Then, click on the “Finish” button.

How to Create a Table with Different Column Names and Types?

The syntax to Snowflake create a table with different column names and types is given below.

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

Example

create table CUST (C1,C2,C3) as select * from CUSTOMER;
 
-- Output
+----------------------------------+
| status                           |
|----------------------------------|
| Table CUST successfully created. |
+----------------------------------+

How to Create Tables in Snowflake?

You can create a new table or replace an existing one using the Snowflake CREATE TABLE command. A table can have multiple columns, with each column definition consisting of a name, data type and optionally whether the column:

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

Additionally, the Snowflake CREATE TABLE command supports 3 variants:

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

The syntax for the Snowflake CREATE TABLE command 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 ]
                             [ 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 = <num> ]
  [ CHANGE_TRACKING = { TRUE | FALSE } ]
  [ DEFAULT_DDL_COLLATION = '<collation_specification>' ]
  [ COPY GRANTS ]
  [ COMMENT = '<string_literal>' ]

Read the official documentation here to get further details about each of these parameters on the Snowflake CREATE TABLE. 

1. CREATE TABLE … AS SELECT (also known as CTAS)

This command is used when you want to create a table from an existing table or schema:

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

This command can be used to do any of the following:

• Snowflake create table from an existing table with all columns and rows
• Snowflake create table with selected columns from the existing table
• Snowflake create table by changing the names of columns and the data types of an existing table
• Snowflake create table with the result of a select query

In a CTAS, the COPY GRANTS clause can only be used when it is combined with the OR REPLACE clause. CTAS with COPY GRANTS will allow you to overwrite a table with new data and at the same time maintain existing grants on that table.

For further details about the COPY GRANTS clause, see COPY GRANTS.

2. CREATE TABLE … LIKE

This command is used when you want to Snowflake create table with the same column definitions as an existing table but you don’t want to copy its data. Different parameters like the column names, data types, default values and constraints are copied to the new table:

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

3. CREATE TABLE … CLONE

This Snowflake CREATE TABLE CLONE command is used when you want to create a new table with the same column definitions and the same data from the source table, without actually copying the data. It can also be used to make a clone of a table at any specific point in the past (using Time Travel):

CREATE [ OR REPLACE ] TABLE <name> CLONE <source_table>
  [ { AT | BEFORE } { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> } ]
  [ COPY GRANTS ]
  [ ... ]

CREATE TABLE Command Examples

Snowflake CREATE TABLE in the current database and insert a row in the table:

Output:

+-------------------------------------+
| status                              |
|-------------------------------------|
| Table MYTABLE successfully created. |
+-------------------------------------+

Output:

+---------------------------------+---------+---------------+-------------+-------+---------+------------+------+-------+--------------+----------------+
| created_on                      | name    | database_name | schema_name | kind  | comment | cluster_by | rows | bytes | owner        | retention_time |
|---------------------------------+---------+---------------+-------------+-------+---------+------------+------+-------+--------------+----------------|
| Mon, 11 Sep 2017 16:32:28 -0700 | MYTABLE | TESTDB        | PUBLIC      | TABLE |         |            |    1 |  1024 | ACCOUNTADMIN | 1              |
+---------------------------------+---------+---------------+-------------+-------+---------+------------+------+-------+--------------+----------------+

Output:

+--------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name   | type         | kind   | null? | default | primary key | unique key | check | expression | comment |
|--------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| AMOUNT | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+--------+--------------+--------+-------+---------+-------------+------------+-------+---

Snowflake CREATE TABLE and specify comments for both the table and the column in the table:

Output:

+-------------------------------------+
| status                              |
|-------------------------------------|
| Table EXAMPLE successfully created. |
+-------------------------------------+

Output:

+---------------------------------+---------+---------------+-------------+-------+-----------------+------------+------+-------+--------------+----------------+
| created_on                      | name    | database_name | schema_name | kind  | comment         | cluster_by | rows | bytes | owner        | retention_time |
|---------------------------------+---------+---------------+-------------+-------+-----------------+------------+------+-------+--------------+----------------|
| Mon, 11 Sep 2017 16:35:59 -0700 | EXAMPLE | TESTDB        | PUBLIC      | TABLE | a table comment |            |    0 |     0 | ACCOUNTADMIN | 1              |
+---------------------------------+---------+---------------+-------------+-------+-----------------+------------+------+-------+--------------+----------------+

Output:

+------+--------------+--------+-------+---------+-------------+------------+-------+------------+------------------+
| name | type         | kind   | null? | default | primary key | unique key | check | expression | comment          |
|------+--------------+--------+-------+---------+-------------+------------+-------+------------+------------------|
| COL1 | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | a column comment |
+------+--------------+--------+-------+---------+-------------+------------+-------+------------+------------------+
Create a table by selecting from an existing table:

Create a Snowflake table by selecting from an existing table:

Output:

+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name | type         | kind   | null? | default | primary key | unique key | check | expression | comment |
|------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| B    | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+

Output:

+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name | type         | kind   | null? | default | primary key | unique key | check | expression | comment |
|------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| C    | NUMBER(39,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+

Output:

+---+
| C |
|---|
| 2 |
+---+

More advanced example of Snowflake CREATE TABLE by selecting from an existing table; in this example, the values in the summary_amount column in the new table are derived from two columns in the source table:

Create a table by selecting columns from a staged Parquet data file:

CREATE OR REPLACE TABLE parquet_col (
  custKey number default NULL,
  orderDate date default NULL,
  orderStatus varchar(100) default NULL,
  price varchar(255)
)
AS SELECT
  $1:o_custkey::number,
  $1:o_orderdate::date,
  $1:o_orderstatus::text,
  $1:o_totalprice::text
FROM @my_stage;

Output:

+-----------------------------------------+
| status                                  |
|-----------------------------------------|
| Table PARQUET_COL successfully created. |
+-----------------------------------------+

Output:

+-------------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name        | type         | kind   | null? | default | primary key | unique key | check | expression | comment |
|-------------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| CUSTKEY     | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| ORDERDATE   | DATE         | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| ORDERSTATUS | VARCHAR(100) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| PRICE       | VARCHAR(255) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+-------------+--------------+--------+-------+---------+-------------+------------+-------+-------

Snowflake CREATE TABLE command used to create a table with the same column definitions as another table, but with no rows:

CREATE TABLE mytable (amount NUMBER);
INSERT INTO mytable VALUES(1);
SELECT * FROM mytable;

Output:

+--------+
| AMOUNT |
|--------|
|      1 |
+--------+

CREATE TABLE mytable_2 LIKE mytable;
DESC TABLE mytable_2;

Output:

+--------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name   | type         | kind   | null? | default | primary key | unique key | check | expression | comment |
|--------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| AMOUNT | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+--------+--------------+--------+-------+---------+-------------+------------+-------+--

Output:

+--------+
| AMOUNT |
|--------|
+--------+

Snowflake CREATE TABLE command used to create a table with a multi-column clustering key:

CREATE TABLE mytable (date timestamp_ntz, id number, content variant) CLUSTER BY (date, id);
SHOW TABLES LIKE 'mytable';

Output:

+---------------------------------+---------+---------------+-------------+-------+---------+------------------+------+-------+--------------+----------------+
| created_on                      | name    | database_name | schema_name | kind  | comment | cluster_by       | rows | bytes | owner        | retention_time |
|---------------------------------+---------+---------------+-------------+-------+---------+------------------+------+-------+--------------+----------------|
| Mon, 11 Sep 2017 16:20:41 -0700 | MYTABLE | TESTDB        | PUBLIC      | TABLE |         | LINEAR(DATE, ID) |    0 |     0 | ACCOUNTADMIN | 1              |
+---------------------------------+---------+---------------+-------------+-------+-----

Snowflake CREATE TABLE command used to specify collation for columns in a table:

CREATE TABLE collation_demo (
  uncollated_phrase VARCHAR, 
  utf8_phrase VARCHAR COLLATE 'utf8',
  english_phrase VARCHAR COLLATE 'en',
  spanish_phrase VARCHAR COLLATE 'sp'
  );

INSERT INTO collation_demo (uncollated_phrase, utf8_phrase, english_phrase, spanish_phrase) 
   VALUES ('pinata', 'pinata', 'pinata', 'piñata');

How to Create Table in Snowflake DDL Query?

You can perform DML and DDL operations using SQL queries. The syntax of the Snowflake CREATE TABLE command to create a table using DDL queries is given below:

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>' ]

How to Create a Table in Snowflake Without Writing Query Using UI?

The web-based graphical interface of Snowflake allows you to create and manage objects, including virtual warehouses, databases, and database objects. 

The steps to create a table in Snowflake using UI are given below.

1. Click on the “Database” tab. It will display the list of available databases.
2. Choose the Database in which you want to create a Table.
3. Click on the “Create” button in the Table tab.
4. Now, provide the table name, comments, and columns with their respective data types.
5. Click on the “Finish” button to Snowflake create table.

What are the Different Types of Tables in Snowflake?

There are 3 types of tables is Snowflake, listed below:

• Permanent Table
• Temporary Table
• Transient Table

Permanent Table

It is the default table in Snowflake, and it’s possible to enable space consumption and a fail-safe timeframe.

Temporary Table 

The temporary table in Snowflake is visible for the current session and exists during the session in which they are created. These tables are only available for that duration of the session and it does not support some standard functionalities.

The syntax to create a Temporary table is given below:

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

Transient Table

Transient tables are available to all the users with the necessary privileges until they are revoked explicitly. These are similar to permanent tables except they don’t have a fail-safe period. The fail-safe provides a 7 day period in which it allows users to retrieve the data. Also, these tables cannot be converted to another table type once created.

The syntax to create a Transient table is given below:

create Transient table emp (name VARCHAR(100), eid int, CONSTRAINT pk_emp PRIMARY Key (eid));
 
-- Output
+---------------------------------+
| status                          |
|---------------------------------|
| Table EMP successfully created. |
+---------------------------------+

Conclusion

You have not only learned about how to Snowflake Create Table but also 3 variants for the same. Now easily create tables and store your data in Snowflake.

Visit our Website to Explore Hevo

Sign Up for Hevo today. You no longer have to worry about creating tables and managing data. Hevo takes care of everything. See for yourself by signing up for a 14-day free trial.

Have any further questions? Let us know in the comments section below.