CREATE SNAPSHOT TABLE

Creates a table snapshot based on a source table. The source table can be a table, a table clone, or a table snapshot.

Grammar Syntax

CREATE SNAPSHOT TABLE [ IF NOT EXISTS ] table_snapshot_name
CLONE source_table_name
[FOR SYSTEM_TIME AS OF time_expression]
[OPTIONS(snapshot_option_list)] 

Click here to go to the specification for this syntax.

Sample InputCode

CREATE SNAPSHOT TABLE mytablesnapshot
CLONE mytable;

Snowflake OutputCode

CREATE
-- ** MSC-ERROR - MSCEWI1021 - SNAPSHOT IS NOT SUPPORTED **
--       SNAPSHOT
                TABLE mytablesnapshot CLONE mytable;                                                              ;

If the Create Table has options, these will be commented since they are not supported in Snowflake. Except for the "description" option.

Sample InputCode

CREATE SNAPSHOT TABLE `table_snapshot`
CLONE `table1`
OPTIONS(
  expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
  friendly_name="my_table_snapshot",
  description="table snapshot cloned from table1",
  labels=[("org_unit", "development")]
);

Snowflake OutputCode

CREATE
--       --** MSC-ERROR - MSC-BQ0002 - SNAPSHOT IS NOT SUPPORTED **
--       SNAPSHOT
                TABLE `table_snapshot` CLONE `table1`
COMMENT 'table snapshot cloned from table1'
----** MSC-ERROR - MSC-BQ0001 - THE OPTIONS CLAUSE WITHIN CREATE TABLE IS NOT SUPPORTED IN SNOWFLAKE WHEN IT CONTAINS THE FOLLOWING OPTIONS EXPIRATION_TIMESTAMP, FRIENDLY_NAME, LABELS. **
--OPTIONS(
--  expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
--  friendly_name="my_table_snapshot",
--  labels=[("org_unit", "development")]
--)
 ;

Note

The LABELS are not transformed into TAGs because the TAGs of the source table are copied, they cannot be changed during the copy of the table.

Related EWIs

1. MSC-BQ0002: The use of Snapshot Table is not supported in Snowflake.
2. MSC-BQ0001: The OPTIONS clause within CREATE TABLE is not supported in Snowflake when it contains the following options.