How to Clone Schema in Snowflake: Syntax and Examples
In Snowflake, you can clone a schema using the
CREATE SCHEMA new_schema CLONE existing_schema command. This creates a copy of the schema instantly without duplicating data physically.Syntax
The syntax to clone a schema in Snowflake is simple:
CREATE SCHEMA new_schema CLONE existing_schema;
Here, new_schema is the name of the schema you want to create as a clone, and existing_schema is the schema you want to copy.
sql
CREATE SCHEMA new_schema CLONE existing_schema;Example
This example shows how to clone a schema named sales_data into a new schema called sales_data_clone. The clone will have the same tables and objects as the original.
sql
CREATE SCHEMA sales_data_clone CLONE sales_data;Output
Schema SALES_DATA_CLONE successfully created as a clone of SALES_DATA.
Common Pitfalls
Common mistakes when cloning schemas include:
- Trying to clone a schema that does not exist.
- Using a schema name for the clone that already exists.
- Not having the required privileges to clone the schema.
Always check that the source schema exists and you have USAGE and CREATE SCHEMA privileges.
sql
/* Wrong: cloning non-existing schema */ CREATE SCHEMA new_clone CLONE missing_schema; /* Right: cloning existing schema */ CREATE SCHEMA new_clone CLONE existing_schema;
Quick Reference
| Command | Description |
|---|---|
| CREATE SCHEMA new_schema CLONE existing_schema; | Creates a new schema as a clone of an existing schema. |
| SHOW SCHEMAS; | Lists all schemas in the current database. |
| DROP SCHEMA schema_name; | Deletes a schema and all its objects. |
Key Takeaways
Use CREATE SCHEMA new_schema CLONE existing_schema to clone schemas instantly.
Cloning copies schema structure and data without duplicating storage.
Ensure the source schema exists and you have proper privileges.
Avoid naming conflicts by choosing a unique name for the cloned schema.
Cloning is fast and efficient for creating test or backup environments.
