Snowflake
Clone Tables

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:

_10
CREATE [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:

_10
CREATE [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

  1. Backup and Disaster Recovery: Quickly clone production databases or tables before significant changes for backup.
  2. Environment Setup: Rapidly set up development, testing, or staging environments by cloning existing databases or schemas without the overhead of data replication.
  3. 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_idcharacter_nameplanet_visitedvisit_datereason_for_visit
1Arthur DentBetelgeuse2024-04-18Vacation
2Ford PrefectEarth2024-04-20Research
3Zaphod BeeblebroxMagrathea2024-04-22Business

Snowflake SQL Query


_10
CREATE TABLE dev_env_db.dev_schema.galactic_travel_logs_clone
_10
CLONE galactic_travel_logs;

Output

Table: galactic_travel_logs_clone

character_idcharacter_nameplanet_visitedvisit_datereason_for_visit
1Arthur DentBetelgeuse2024-04-18Vacation
2Ford PrefectEarth2024-04-20Research
3Zaphod BeeblebroxMagrathea2024-04-22Business

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_idcharacter_nameplanet_visitedvisit_datereason_for_visit
1Arthur DentBetelgeuse2024-04-18Vacation
2Ford PrefectEarth2024-04-20Research
3Zaphod BeeblebroxMagrathea2024-04-22Business

Snowflake SQL Query


_10
CREATE TABLE galactic_travel_logs_1804_bkp
_10
CLONE galactic_travel_logs AT (OFFSET => -24*60*60);

Output

Table: galactic_travel_logs_2405_bkp

character_idcharacter_nameplanet_visitedvisit_datereason_for_visit
1Arthur DentBetelgeuse2024-04-18Vacation
Y42 SQL completion user interface

Your AI copilot for Snowflake SQL

Write production-ready SQL with AI-based suggestions.

Start for free