What are Different Methods to Create Snowflake Tables?

In my other posts, I have discussed on how to create Snowflake clustered tables, creating external tables in Snowflake, etc. In this article, we will check what are different methods to create Snowflake tables with some basic examples.

Different Methods to Create Snowflake Tables

During database development, developer create a table such as permanent, temporary or transient tables as per the requirement. Developers usually create tables using DDL such as “CREATE TABLE” statement. But, sometimes you may need to use different methods such as creating a copy of an existing table or creating table similar to another table, etc.

Well, following are some of the different methods to create table in Snowflake data warehouse.

Create Snowflake Table using DDL
CREATE TABLE AS Method to Create Snowflake Table
Creating Snowflake Table using CREATE TABLE LIKE Method
CREATE TABLE with CLONE Option

Now, let us check these Snowflake table creation approaches briefly.

Create Snowflake Table using DDL

Using table DDL is the most common method to create tables in Snowflake.

Typically, simple DDL in Snowflake looks something like following example.

CREATE OR REPLACE TABLE SampleTable
 (
 id INT NOT NULL identity,
 name VARCHAR(50),
 Age INT NOT NULL 
 )
 CLUSTER BY (id);

Snowflake supports many other options. You can read more about the different options on official documentation.

CREATE TABLE AS Method to Create Snowflake Table

CREATE TABLE AS (CTAS) is another common method to creates a new table based on a query. This method creates a new table populated with the data returned by a query. The query is mandatory to use CTAS.

The following statement can create table in Snowflake using CTAS.

CREATE OR REPLACE TABLE SampleTable2
AS 
SELECT 	ID,
		NAME,
		AGE
FROM SampleTable;

The new table is loaded with data defined by the query used in the CTAS. The table columns have names and data types associated with the output columns of the query.

Snowflake CREATE TABLE AS (CTAS) with CLUSTER BY Option

You can include options such as CLUSTER BY column in CTAS. However, CLUSTER BY is recommended only on big tables and if your queries are taking more time to execute.

For example,

CREATE OR REPLACE TABLE SampleTable3
CLUSTER BY (id)
AS 
SELECT 	ID,
		NAME,
		AGE
FROM SampleTable;

CREATE TABLE LIKE Method to Create Snowflake Table

Creates a new table with the same column definitions as an existing table, but without copying data from the existing table. The new table inherits the column names, types, and other settings such as defaults, and constraints.

The following statement creates a “SampleTable3” table by copying settings from the exiting “SampleTable” table.

create table SampleTable3 like SampleTable;

CREATE TABLE LIKE creates only table structure with the same column definitions as an existing table. However, it does not copy the data into a new table.

CLONE Snowflake Table

Creates a new table with the same column definitions and containing all the existing data from the source table, without actually copying the data. You can specify the specific time/point in the time while using CLONE.

Following statement clone a table at its current state:,

create table orders_clone clone orders;

Following statement clone a table at a specific time in the past.

create table orders_clone_restore clone orders at (timestamp => to_timestamp_tz('04/05/2013 01:02:03', 'mm/dd/yyyy hh24:mi:ss'));