Snowflake CLONE TABLE: How to Clone a Table
The CLONE function in Snowflake allows for creating exact copies of various objects like databases, schemas, tables, and roles without the need for duplicating the data physically.
This feature is particularly useful for various purposes including testing, backup, and recovery scenarios, as well as quickly provisioning environments for development or analysis without impacting the performance of production systems.
Syntax
The CLONE command can be appended to standard CREATE statements to duplicate the specified object. Here’s how you can use it:
- Databases and Schemas:
_10CREATE [OR REPLACE] {DATABASE | SCHEMA} [IF NOT EXISTS] <name>_10 CLONE <source_name>_10 [AT | BEFORE {TIMESTAMP => <timestamp> | OFFSET => <offset> | STATEMENT => <id>}]_10 [IGNORE TABLES WITH INSUFFICIENT DATA RETENTION]
- Tables:
_10CREATE [OR REPLACE] TABLE [IF NOT EXISTS] <name>_10 CLONE <source_table_name>_10 [AT | BEFORE {TIMESTAMP => <timestamp> | OFFSET => <offset> | STATEMENT => <id>}]
Time Travel Cloning
For objects like databases, schemas, and tables, you can utilize Snowflake’s Time Travel capability to clone them as they existed at a specific point in the past using the AT or BEFORE clause.
You can find out more about the Time Travel functionality here (opens in a new tab).
Key points
- Cloned objects are completely independent of their source objects. Changes made post-clone to either the source or the clone do not affect the other.
- For cloning databases and schemas, the operation is recursive—cloning a database will clone all schemas and objects within.
- Usage of CLONE requires appropriate privileges on the source object. For instance, cloning a table requires SELECT privileges.
- Cloning does not duplicate the data physically. It leverages Snowflake’s unique architecture to manage data storage efficiently, making cloning operations quick and cost-effective.
Common use cases
- Backup and Disaster Recovery: Quickly clone production databases or tables before significant changes for backup.
- Environment Setup: Rapidly set up development, testing, or staging environments by cloning existing databases or schemas without the overhead of data replication.
- Data Analysis: Clone analytical data sets for exploratory or experimental analyses without affecting the original data set.
Examples
Clone a Table for Development
Here’s a simple example of cloning a table:
Input
Table: galactic_travel_logs
| character_id | character_name | planet_visited | visit_date | reason_for_visit |
|---|---|---|---|---|
| 1 | Arthur Dent | Betelgeuse | 2024-04-18 | Vacation |
| 2 | Ford Prefect | Earth | 2024-04-20 | Research |
| 3 | Zaphod Beeblebrox | Magrathea | 2024-04-22 | Business |
Snowflake SQL Query
_10CREATE TABLE dev_env_db.dev_schema.galactic_travel_logs_clone_10 CLONE galactic_travel_logs;
Output
Table: galactic_travel_logs_clone
| character_id | character_name | planet_visited | visit_date | reason_for_visit |
|---|---|---|---|---|
| 1 | Arthur Dent | Betelgeuse | 2024-04-18 | Vacation |
| 2 | Ford Prefect | Earth | 2024-04-20 | Research |
| 3 | Zaphod Beeblebrox | Magrathea | 2024-04-22 | Business |
Restore a Table from Yesterday’s Backup
Here’s a refined example for restoring a table from a backup as it existed 24 hours ago:
Input
Table: galactic_travel_logs
| character_id | character_name | planet_visited | visit_date | reason_for_visit |
|---|---|---|---|---|
| 1 | Arthur Dent | Betelgeuse | 2024-04-18 | Vacation |
| 2 | Ford Prefect | Earth | 2024-04-20 | Research |
| 3 | Zaphod Beeblebrox | Magrathea | 2024-04-22 | Business |
Snowflake SQL Query
_10CREATE TABLE galactic_travel_logs_1804_bkp_10 CLONE galactic_travel_logs AT (OFFSET => -24*60*60);
Output
Table: galactic_travel_logs_2405_bkp
| character_id | character_name | planet_visited | visit_date | reason_for_visit |
|---|---|---|---|---|
| 1 | Arthur Dent | Betelgeuse | 2024-04-18 | Vacation |
