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.

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

Snowflake official documentation

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 with Understanding Using Time Travel Snowflake Documentation

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.

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. |
+----------------------------------+

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.

FAQ

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