How to Clone a Table in Snowflake: Syntax and Examples
To clone a table in Snowflake, use the
CREATE TABLE new_table CLONE existing_table command. This creates a zero-copy clone that shares the data with the original table without duplicating it.Syntax
The basic syntax to clone a table in Snowflake is:
CREATE TABLE new_table CLONE existing_table;
Here, new_table is the name of the clone you want to create, and existing_table is the original table you want to clone.
This command creates a new table that initially shares the same data as the original without copying it physically, saving storage and time.
sql
CREATE TABLE new_table CLONE existing_table;
Example
This example shows how to clone a table named sales_data into a new table called sales_data_clone. The clone will have the same data and structure as the original.
sql
CREATE TABLE sales_data_clone CLONE sales_data;
Output
Table SALES_DATA_CLONE successfully created as a clone of SALES_DATA.
Common Pitfalls
Common mistakes when cloning tables in Snowflake include:
- Trying to clone a table that does not exist, which causes an error.
- Using the clone command without specifying the correct database or schema, leading to confusion about which table is cloned.
- Expecting the clone to be independent immediately; clones share data until changes are made, so deleting data in the original affects storage but not the clone's visible data.
Always verify table names and schema context before cloning.
sql
/* Wrong: cloning a non-existent table */ CREATE TABLE clone_table CLONE missing_table; /* Right: cloning an existing table with schema */ CREATE TABLE my_schema.clone_table CLONE my_schema.existing_table;
Output
ERROR: Table 'missing_table' does not exist
Table MY_SCHEMA.CLONE_TABLE successfully created as a clone of MY_SCHEMA.EXISTING_TABLE.
Quick Reference
| Command | Description |
|---|---|
| CREATE TABLE new_table CLONE existing_table; | Creates a zero-copy clone of an existing table. |
| SHOW TABLES LIKE 'table_name'; | Check if the table exists before cloning. |
| DROP TABLE new_table; | Remove the cloned table when no longer needed. |
Key Takeaways
Use CREATE TABLE new_table CLONE existing_table to clone tables in Snowflake.
Cloning creates a zero-copy clone that shares data with the original table.
Verify the original table exists and specify schema to avoid errors.
Clones are independent logically but share storage until data changes.
Use SHOW TABLES to confirm table existence before cloning.
