Are you looking to find how to create the table dynamically within the snowflake cloud data warehouse or maybe you are looking for a solution to create a table using the CTAS ( create table as select) in the Snowflake. You may also want to check what could be real-world use case scenarios where you wanted to create dynamically table. In some cases, there is a need to create the duplicate table or backup table dynamically. I will also show you how you can create a table with selected columns from the existing table. I will take you through a step-by-step process of creating the Snowflake table dynamically using the CTAS and through the stored procedure as well. After going through this article you will also be able to take the backup of the table fully or partially. Let’s don’t waste the time and I will take you quickly to the code directly where I will show you the dynamic table in Snowflake.
There are two basic ways to create a table dynamically in snowflake
1. Using the CTAS (create table as select).
2. Using the Stored procedure
Now let’s understand each of these in detail.
Contents
- 1 How to create the table dynamically using CTAS in Snowflake?
- 2 What are the Use Case scenarios for using CTAS (create table as select) or dynamic table in Snowflake?
- 3 How to create the backup table in the Snowflake
- 4 How to duplicate the table in the Snowflake?
- 5 How to create a duplicate table in the Snowflake with the structure only?
- 6 How to create the table dynamically using stored procedure in the Snowflake?
- 7 Final thoughts
How to create the table dynamically using CTAS in Snowflake?
CTAS is popularly known as create table as select. Using this concept we can create the table by executing a select query and whatever the results we get from this query a table will be created out of it.
For example, even if you have a complex query which involves multiple columns using the join and for this complex query you want a simple table to get created, you can use CTAS
Syntax for create table as select (CTAS) in Snowflake
CREATE [ OR REPLACE ] TABLE [database_name].[schema_name].table_name
AS SELECT [comma seperated columns] from [database_name].[schema_name].table_name
[WHERE ]
Create dynamic table example in snowflake
Assume that already we have Customer table with three columns (name, address, salary). Create another table Customer_new using the Customer table
create table Azurelib.public.Customer_new as select * from Azurelib.public.CUSTOMER;
--Output
+------------------------------------------+
| status |
|------------------------------------------|
| Table CUSTOMER_NEW successfully created. |
+------------------------------------------+
1 Row(s) produced. Time Elapsed: 1.652s
Above query will create table Customer_new. All the column names and the data type of the column will be decided by the name and type of the column returned by the select query.
Now you can see that in this case we haven’t provided any column names and not even the column data type for creating the table. This table is getting created dynamically just using the select query.
Using the CTAS doesn’t only create the table metadata but it also loads all the number of rows as well to the table. Hence table will get created with metadata and populated data as well
What are the Use Case scenarios for using CTAS (create table as select) or dynamic table in Snowflake?
Scenario: Create table Backup
There are certain scenarios in the real world situation where you wanted to take the backup of the table. For example, you are trying to do some manipulation on the table and you want to take up a backup of the table before making any modification to the existing table. In this case, you can use the CTAS to dynamically create the backup of the table.
Scenario: Create Duplicate Table
in some cases, you wanted to create the duplication of a table. In fact, you just don’t want to duplicate the table structure only, but you also wanted to duplicate the data as well. Again In this case CTAS will come as a lifesaver, using that you can create the entire duplication of the table with metadata.
Creating table instead of view
Generally, views are created for a complex query, so that every time you don’t have to fire the complex query. But the problem with view is that you cannot update the records or add, delete the records from the view.
How to create the backup table in the Snowflake
To create the backup of the table we use the create table as select (CTAS). This time we will pull out all the rows and all the columns from the table, for which we need to create the backup.
Example query for creating a backup table in the Snowflake as follows
deepak#COMPUTE_WH@AZURELIB.PUBLIC>create table Azurelib.public.Customer_Backup as select * from Azurelib.public.CUSTOMER;
/*
Output
+---------------------------------------------+
| status |
|---------------------------------------------|
| Table CUSTOMER_BACKUP successfully created. |
+---------------------------------------------+
1 Row(s) produced. Time Elapsed: 1.843s
*/
-- Verify Customer_Backup table
deepak#COMPUTE_WH@AZURELIB.PUBLIC>select * from CUSTOMER_BACKUP;
/*
+--------+---------------+--------+
| NAME | ADDRESS | SALARY |
|--------+---------------+--------|
| John | New York | 1000 |
| Mike | San Fransisco | 2000 |
| Peter | LA | 1000 |
| Sheena | California | 3000 |
+--------+---------------+--------+
4 Row(s) produced. Time Elapsed: 1.309s
*/
You can see that Customer_Backup will create as the backup table for Customer .this table contains all the columns and all the rows which exist in this table at the moment of creation.
How to duplicate the table in the Snowflake?
To create the replication of a table, again we going to use the create table as select (CTAS). Creating the backup of a table or making the duplicate table is more or less the same thing.
Here as well we will use the select * query where we will pull out all the columns and all the rows from the table which is needed to be duplicated.
Example query for duplicating the table the Snowflake is as follows
deepak#COMPUTE_WH@AZURELIB.PUBLIC>create table Azurelib.public.Customer_Duplicate as select * from Azurelib.public.CUSTOMER;
/*
Output
+---------------------------------------------+
| status |
|---------------------------------------------|
| Table CUSTOMER_Duplicate successfully created. |
+---------------------------------------------+
1 Row(s) produced. Time Elapsed: 1.843s
*/
-- Verify Customer_Duplicate table
deepak#COMPUTE_WH@AZURELIB.PUBLIC>select * from CUSTOMER_Duplicate;
/*
+--------+---------------+--------+
| NAME | ADDRESS | SALARY |
|--------+---------------+--------|
| John | New York | 1000 |
| Mike | San Fransisco | 2000 |
| Peter | LA | 1000 |
| Sheena | California | 3000 |
+--------+---------------+--------+
4 Row(s) produced. Time Elapsed: 1.309s
*/
Above query create the duplicate Cusomer_Duplicate as a duplication of Customer.
Note:
The duplication is of at the moment, so the time at which you create the duplicate table you get all the rows. But if there are any changes done in the source table after the duplication it won’t it appear in the duplicate table.
How to create a duplicate table in the Snowflake with the structure only?
There are two ways to create a duplicate table with the structure only.
1. Create a duplicate table using CTAS and then delete the data.
2. Copy the DDL and change the table name.
Create a duplicate table using CTAS and then delete the data
create table Azurelib.public.Customer_Duplicate as select * from Azurelib.public.CUSTOMER where limit 1;
--Delete the 1 row as well
delete from Azurelib.public.Customer_Duplicate
Copy the DDL and change the table name
-- get the ddl for the table
select get_ddl('table', 'Customer')
/* Output
+------------------------------------+
| GET_DDL |
| ('TABLE','CUSTOMER') |
|------------------------------------|
| create or replace TABLE CUSTOMER ( |
| NAME VARCHAR(100), |
| ADDRESS VARCHAR(100), |
| SALARY NUMBER(38,0) |
| ); |
+------------------------------------+
1 Row(s) produced. Time Elapsed: 0.918s
*/
-- create duplicate table with table name changed
create or replace TABLE CUSTOMER_COPY (
NAME VARCHAR(100),
ADDRESS VARCHAR(100),
SALARY NUMBER(38,0)
)
How to create the table dynamically using stored procedure in the Snowflake?
Snowflake provides a very good stored procedure functionality where we can write the code in JavaScript. We can create the dynamic table using the Stored Procedure by providing the parameters and logic to the Stored procedure.
Then the stored procedure will create the table based on the parameters for the logic.
CREATE OR REPLACE PROCEDURE create_table_using_stored_procedure()
RETURNS string
LANGUAGE JAVASCRIPT
AS
$$
var tableName = any_table_name;
try {
snowflake.execute(
{
sqlText: `CREATE OR REPLACE TABLE IDENTIFIER(?)(ename VARCHAR);`
,binds: ['Azurelib.public.' + tableName]
}
);
}
catch(err){
return "Exception Occurred: " + err;
};
return "Create Dynamic Table Successful using Store Procedure.";
$$;
Snowflake official Documentation Link
Final thoughts
By this, we have reached the end of our insightful article on how to create the table dynamically in Snowflake. In this article, we have learned how to check create the table using the CTAS i.e. create table as select. We have also gone through how create the backup table or duplicate the table. I have shared the practical Real-world scenarios which could be reason why you will create the table dynamically in the Snowflake. I have already written one dedicated article on how to create task and how to schedule the task in case you have any queries on how to schedule the task please visit this blog to know much more about it.
Please share your comments and suggestions in the comment section below and I will try to answer all your queries as time permits.
- 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
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.