How to Clone a Database in Snowflake: Syntax and Example
To clone a database in Snowflake, use the
CREATE DATABASE new_db CLONE source_db; command. This creates a zero-copy clone that shares data with the original without duplicating storage.Syntax
The basic syntax to clone a database in Snowflake is:
CREATE DATABASE <new_database_name> CLONE <source_database_name>;
This command creates a new database named new_database_name that is a clone of source_database_name. The clone shares the same data storage initially, so it is fast and storage-efficient.
sql
CREATE DATABASE new_db CLONE source_db;Example
This example shows how to clone an existing database named sales_db into a new database called sales_db_clone. The clone is created instantly and uses no extra storage initially.
sql
CREATE DATABASE sales_db_clone CLONE sales_db;Output
Database SALES_DB_CLONE successfully created as a clone of SALES_DB.
Common Pitfalls
Common mistakes when cloning databases in Snowflake include:
- Trying to clone a database that does not exist.
- Using a new database name that already exists.
- Not having the required privileges to clone the source database.
- Expecting the clone to be a full independent copy immediately; it shares storage until changes are made.
Always check that the source database exists and you have USAGE and CREATE DATABASE privileges.
sql
/* Wrong: source database does not exist */ CREATE DATABASE new_clone CLONE missing_db; /* Right: source database exists and privileges are granted */ CREATE DATABASE new_clone CLONE existing_db;
Quick Reference
| Command | Description |
|---|---|
| CREATE DATABASE | Creates a zero-copy clone of the source database. |
| SHOW DATABASES; | Lists all databases including clones. |
| DROP DATABASE | Deletes a database or clone. |
| GRANT USAGE ON DATABASE | Grants access to the database. |
Key Takeaways
Use CREATE DATABASE new_db CLONE source_db to clone a database in Snowflake.
Cloning is instant and storage-efficient because it shares data with the source.
Ensure the source database exists and you have proper privileges before cloning.
Clones behave like independent databases but share storage until data changes.
Avoid naming conflicts by choosing unique names for cloned databases.
