How to Duplicate a Table in Snowflake

Snowflake is a powerful cloud-based data warehouse solution that allows users to easily scale, manage, and analyze their data. Duplicating a table in Snowflake is a common task, whether you're backing up data, testing in development environments, or preparing data for analysis.

In this tutorial, we will walk you through the process of duplicating a table in Snowflake. We'll cover both the schema and data duplication methods, ensuring you have everything you need to copy tables in your Snowflake environment effectively.

1. Simple Duplicate Using CREATE TABLE AS

The simplest way to duplicate a table in Snowflake is by using the CREATE TABLE AS command. This allows you to create a new table with the same data as an existing table.

CREATE TABLE new_table_name AS
SELECT *
FROM existing_table_name;
                                

This SQL command will copy all rows from the existing_table_name into the new table new_table_name. However, the schema, such as primary keys, foreign keys, and other constraints, will not be copied over. If you need to replicate the full structure, we will cover that next.

2. Duplicate Table Schema and Data

If you want to duplicate both the schema and the data of a table, you can use a combination of CREATE TABLE and INSERT INTO statements. First, create an empty table with the same structure, and then copy the data over:

CREATE TABLE new_table_name LIKE existing_table_name;

INSERT INTO new_table_name
SELECT * FROM existing_table_name;
                                

This approach ensures that the new table has the same schema, including all columns, data types, and other properties, and it also copies the data from the original table.

3. Handling Cloning of Tables

Snowflake also supports a unique feature called CLONE, which allows you to create a new table that is a direct clone of an existing table. The CLONE command is efficient and ensures that the schema, data, and time-travel history are included in the clone, without duplicating the actual storage space.

CREATE TABLE new_table_name CLONE existing_table_name;
                                

The CLONE command is particularly useful for creating temporary duplicates or backups, as it doesn’t take up additional storage space for unchanged data and preserves the state of the original table.

Conclusion

Duplicating tables in Snowflake is a straightforward process, and you have several options depending on your needs. Whether you're using CREATE TABLE AS, creating a new table with the same schema and data, or using the CLONE command for efficient table duplication, Snowflake provides the tools to manage your data seamlessly. By following the steps outlined in this tutorial, you can easily create copies of your tables for backup, development, or testing purposes.