SnowConvert AI - Redshift - CREATE TABLE¶

Create Table Syntax Grammar.

Description¶

Creates a new table in the current database. You define a list of columns, which each hold data of a distinct type. The owner of the table is the issuer of the CREATE TABLE command.

For more information please refer to CREATE TABLE (https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html) documentation.

Grammar Syntax ¶

 CREATE [ [LOCAL ] { TEMPORARY | TEMP } ] TABLE
[ IF NOT EXISTS ] table_name
( { column_name data_type [column_attributes] [ column_constraints ]
  | table_constraints
  | LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] }
  [, ... ]  )
[ BACKUP { YES | NO } ]
[table_attributes]

where column_attributes are:
  [ DEFAULT default_expr ]
  [ IDENTITY ( seed, step ) ]
  [ GENERATED BY DEFAULT AS IDENTITY ( seed, step ) ]
  [ ENCODE encoding ]
  [ DISTKEY ]
  [ SORTKEY ]
  [ COLLATE CASE_SENSITIVE | COLLATE CASE_INSENSITIVE  ]

and column_constraints are:
  [ { NOT NULL | NULL } ]
  [ { UNIQUE  |  PRIMARY KEY } ]
  [ REFERENCES reftable [ ( refcolumn ) ] ]

and table_constraints  are:
  [ UNIQUE ( column_name [, ... ] ) ]
  [ PRIMARY KEY ( column_name [, ... ] )  ]
  [ FOREIGN KEY (column_name [, ... ] ) REFERENCES reftable [ ( refcolumn ) ]


and table_attributes are:
  [ DISTSTYLE { AUTO | EVEN | KEY | ALL } ]
  [ DISTKEY ( column_name ) ]
  [ [COMPOUND | INTERLEAVED ] SORTKEY ( column_name [,...]) |  [ SORTKEY AUTO ] ]
  [ ENCODE AUTO ]
         

Copy

BACKUP¶

Description¶

Enables Amazon Redshift to automatically adjust the encoding type for all columns in the table to optimize query performance. In Snowflake, the concept of BACKUP as seen in other databases is not directly applicable. Snowflake automatically handles data backup and recovery through its built-in features like Time Travel and Fail-safe, eliminating the need for manual backup operations. For these reasons, the statement BACKUP is removed during the transformation process

See the Redshift data distribution documentation (https://docs.aws.amazon.com/redshift/latest/dg/t_Distributing_data.html) for this syntax.

Grammar Syntax¶

 BACKUP { YES | NO }

Copy

Sample Source Patterns¶

Input Code:¶

Redshift¶

 CREATE TABLE table1 (
    col1 INTEGER
)
BACKUP YES;

Copy

Output Code:¶

Snowflake¶

 CREATE TABLE table1 (
    col1 INTEGER
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}'
;

Copy

IF NOT EXISTS¶

Description¶

In Amazon Redshift, IF NOT EXISTS is used in table creation commands to avoid errors if the table already exists. When included, it ensures that the table is created only if it does not already exist, preventing duplication and errors in your SQL script.

See the Redshift CREATE TABLE documentation (https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html) for this syntax.

Grammar Syntax¶

 IF NOT EXISTS

Copy

Sample Source Patterns¶

Input Code:¶

Redshift¶

 CREATE TABLE IF NOT EXISTS table1 (
    col1 INTEGER
);

Copy

Output Code:¶

Snowflake¶

 CREATE TABLE IF NOT EXISTS table1 (
    col1 INTEGER
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}';

Copy

Related EWIs¶

There are no known issues.

LOCAL¶

Description¶

In Amazon Redshift, LOCAL TEMPORARY or TEMP are used to create temporary tables that exist only for the duration of the session. These tables are session-specific and automatically deleted when the session ends. They are useful for storing intermediate results or working data without affecting the permanent database schema.

See the Redshift CREATE TABLE documentation (https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html) for this syntax.

Grammar Syntax¶

 LOCAL { TEMPORARY | TEMP }

Copy

Sample Source Patterns¶

Input Code:¶

Redshift¶

 CREATE LOCAL TEMPORARY TABLE table1 (
    col1 INTEGER
);

Copy

Output Code:¶

Snowflake¶

 CREATE LOCAL TEMPORARY TABLE table1 (
    col1 INTEGER
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}';

Copy

Related EWIs¶

There are no known issues.

DISTKEY¶

Description¶

In Amazon Redshift, DISTKEY is used to distribute data across cluster nodes to optimize query performance. Snowflake, however, automatically handles data distribution and storage without needing explicit distribution keys. Due to differences in architecture and data management approaches, Snowflake does not have a direct equivalent to Redshift’s DISTKEY.

See the Redshift data distribution documentation (https://docs.aws.amazon.com/redshift/latest/dg/t_Distributing_data.html) for this syntax.

Grammar Syntax¶

 DISTKEY ( column_name )

Copy

Sample Source Patterns¶

Input Code:¶

Redshift¶

 CREATE TABLE table1 (
    col1 INTEGER
)
DISTKEY (col1);

Copy

Output Code:¶

Snowflake¶

 CREATE TABLE table1 (
    col1 INTEGER
)
----** SSC-FDM-RS0001 - DISTKEY OPTION IS NOT SUPPORTED IN SNOWFLAKE. DATA STORAGE IS AUTOMATICALLY HANDLED BY SNOWFLAKE. **
--DISTKEY (col1)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}';

Copy

Related EWIs¶

SSC-FDM-RS0001: Data storage option is not supported in Snowflake. Data distribution is automatically handled by Snowflake.

DISTSTYLE¶

Description¶

Keyword that defines the data distribution style for the whole table.

See the Redshift data distribution documentation (https://docs.aws.amazon.com/redshift/latest/dg/t_Distributing_data.html) for this syntax.

Grammar Syntax¶

 DISTSTYLE { AUTO | EVEN | KEY | ALL }

Copy

Sample Source Patterns¶

Input Code:¶

Redshift¶

 CREATE TABLE table1 (
    col1 INTEGER
)
DISTSTYLE AUTO;

CREATE TABLE table2 (
    col1 INTEGER
)
DISTSTYLE EVEN;

CREATE TABLE table3 (
    col1 INTEGER
)
DISTSTYLE KEY
DISTKEY (col1);

CREATE TABLE table4 (
    col1 INTEGER
)
DISTSTYLE ALL;

Copy

Output Code:¶

Snowflake¶

 CREATE TABLE table1 (
    col1 INTEGER
)
----** SSC-FDM-RS0001 - DISTSTYLE AUTO OPTION IS NOT SUPPORTED IN SNOWFLAKE. DATA STORAGE IS AUTOMATICALLY HANDLED BY SNOWFLAKE. **
--DISTSTYLE AUTO
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}';

CREATE TABLE table2 (
    col1 INTEGER
)
----** SSC-FDM-RS0001 - DISTSTYLE EVEN OPTION IS NOT SUPPORTED IN SNOWFLAKE. DATA STORAGE IS AUTOMATICALLY HANDLED BY SNOWFLAKE. **
--DISTSTYLE EVEN
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}';

CREATE TABLE table3 (
    col1 INTEGER
)
----** SSC-FDM-RS0001 - DISTSTYLE KEY OPTION IS NOT SUPPORTED IN SNOWFLAKE. DATA STORAGE IS AUTOMATICALLY HANDLED BY SNOWFLAKE. **
--DISTSTYLE KEY
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}'
;

CREATE TABLE table4 (
    col1 INTEGER
)
----** SSC-FDM-RS0001 - DISTSTYLE ALL OPTION IS NOT SUPPORTED IN SNOWFLAKE. DATA STORAGE IS AUTOMATICALLY HANDLED BY SNOWFLAKE. **
--DISTSTYLE ALL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}';

Copy

Related EWIs¶

SSC-FDM-RS0001: Data storage option is not supported in Snowflake. Data distribution is automatically handled by Snowflake.

ENCODE¶

Description¶

In Snowflake, defining ENCODE is unnecessary because it automatically handles data compression, unlike Redshift, which requires manual encoding settings. For this reason, the ENCODE statement is removed during migration.

See the Redshift CREATE TABLE documentation (https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html) for this syntax.

Grammar Syntax¶

 ENCODE AUTO

Copy

Sample Source Patterns¶

Input Code:¶

Redshift¶

 CREATE TABLE table1 (
    col1 INTEGER
)
ENCODE AUTO;

Copy

Output Code:¶

Snowflake¶

 CREATE TABLE table1 (
    col1 INTEGER
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}'
;

Copy

Related EWIs¶

There are no known issues.

SORTKEY¶

Description¶

The keyword that specifies that the column is the sort key for the table. In Snowflake, SORTKEY from Redshift can be migrated to CLUSTER BY because both optimize data storage for query performance. CLUSTER BY in Snowflake organizes data on specified columns, similar to how SORTKEY orders data in Redshift.

See the Redshift data distribution documentation (https://docs.aws.amazon.com/redshift/latest/dg/t_Distributing_data.html) for this syntax.

Grammar Syntax¶

 [COMPOUND | INTERLEAVED ] SORTKEY ( column_name [,...]) | [ SORTKEY AUTO ]

Copy

Sample Source Patterns¶

Input Code:¶

Redshift¶

 CREATE TABLE table1 (
    col1 INTEGER,
    col2 VARCHAR,
    col3 INTEGER,
    col4 INTEGER
)
COMPOUND SORTKEY (col1, col3);

CREATE TABLE table2 (
    col1 INTEGER
)
INTERLEAVED SORTKEY (col1);

CREATE TABLE table3 (
    col1 INTEGER
)
SORTKEY AUTO;

Copy

Output Code:¶

Snowflake¶

 CREATE TABLE table1 (
    col1 INTEGER,
    col2 VARCHAR,
    col3 INTEGER,
    col4 INTEGER
)
--** SSC-FDM-RS0002 - THE PERFORMANCE OF CLUSTER BY IN SNOWFLAKE MAY VARY COMPARED TO THE PERFORMANCE OF SORTKEY IN REDSHIFT. **
CLUSTER BY (col1, col3)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}'
;

CREATE TABLE table2 (
    col1 INTEGER
)
--** SSC-FDM-RS0002 - THE PERFORMANCE OF CLUSTER BY IN SNOWFLAKE MAY VARY COMPARED TO THE PERFORMANCE OF SORTKEY IN REDSHIFT. **
CLUSTER BY (col1)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}'
;

CREATE TABLE table3 (
    col1 INTEGER
)
----** SSC-FDM-RS0001 - SORTKEY AUTO OPTION IS NOT SUPPORTED IN SNOWFLAKE. DATA STORAGE IS AUTOMATICALLY HANDLED BY SNOWFLAKE. **
--SORTKEY AUTO
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}';

Copy

Related EWIs¶

SSC-FDM-RS0001: Data storage option is not supported in Snowflake. Data distribution is automatically handled by Snowflake.
SSC-FDM-RS0002: The performance of CLUSTER BY in Snowflake may vary compared to the performance of SORTKEY in Redshift.

FOREIGN KEY¶

Description¶

Constraint that specifies a foreign key constraint, which requires that a group of one or more columns of the new table must only contain values that match values in the referenced column or columns of some row of the referenced table.

See the Redshift CREATE TABLE documentation (https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html) for this syntax.

Warning

The translation for Foreign Key will be delivered in the future.

Grammar Syntax¶

 FOREIGN KEY (column_name [, ... ] ) REFERENCES reftable [ ( refcolumn )

Copy

Sample Source Patterns¶

Input Code:¶

Redshift¶

 CREATE TABLE table15 (
    col1 INTEGER,
    FOREIGN KEY (col1) REFERENCES table_test (col1)
);

Copy

Output Code:¶

Snowflake¶

 CREATE TABLE table15 (
    col1 INTEGER
--                ,
--    --** SSC-FDM-RS0003 - SNOWCONVERT AI TRANSLATION FOR REDSHIFT FOREIGN KEY CONSTRAINTS IS PENDING. **
--    FOREIGN KEY (col1) REFERENCES table_test (col1)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/26/2024" }}';

Copy

Related EWIs¶

SSC-FDM-RSOOO3: Foreign Key translation will be supported in the future.

PRIMARY KEY¶

Description¶

Specifies that a column or a number of columns of a table can contain only unique non-null values

See the Redshift CREATE TABLE documentation (https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html) for this syntax.

Note

In Snowflake, unique, primary and foreign keys are used for documentation and do not enforce constraints or uniqueness. They help describe table relationships but don’t impact data integrity or performance.

Grammar Syntax¶

 PRIMARY KEY ( column_name [, ... ] )

Copy

Sample Source Patterns¶

Input Code:¶

Redshift¶

 CREATE TABLE table1 (
    col1 INTEGER, 
    col2 INTEGER,
    PRIMARY KEY (col1)
);

Copy

Output Code:¶

Snowflake¶

 CREATE TABLE table1 (
    col1 INTEGER,
    col2 INTEGER,
    PRIMARY KEY (col1)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}'
;

Copy

Related EWIs¶

There are no known issues.

UNIQUE¶

Description¶

Specifies that a group of one or more columns of a table can contain only unique values.

See the Redshift CREATE TABLE documentation (https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html) for this syntax.

Note

Grammar Syntax¶

 UNIQUE ( column_name [, ... ] )

Copy

Sample Source Patterns¶

Input Code:¶

Redshift¶

 CREATE TABLE table1 (
    col1 INTEGER, 
    col2 INTEGER,
    UNIQUE ( col1, col2 )
);

Copy

Output Code:¶

Snowflake¶

 CREATE TABLE table1 (
    col1 INTEGER,
    col2 INTEGER,
    UNIQUE ( col1, col2 )
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}'
;

Copy

Related EWIs¶

There are no known issues.

NOT NULL | NULL¶

Description¶

NOT NULL specifies that the column isn’t allowed to contain null values. NULL, the default, specifies that the column accepts null values.

See the Redshift CREATE TABLE documentation (https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html) for this syntax.

Grammar Syntax¶

 NOT NULL | NULL

Copy

Sample Source Patterns¶

Input Code:¶

Redshift¶

 CREATE TABLE table1 (
    col1 INTEGER NOT NULL, 
    col2 INTEGER NULL
);

Copy

Output Code:¶

Snowflake¶

 CREATE TABLE table1 (
    col1 INTEGER NOT NULL,
    col2 INTEGER NULL
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}'
;

Copy

Related EWIs¶

There are no known issues.

REFERENCES¶

Description¶

Specifies a foreign key constraint, which implies that the column must contain only values that match values in the referenced column of some row of the referenced table

See the Redshift CREATE TABLE documentation (https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html) for this syntax.

Grammar Syntax¶

 REFERENCES reftable [ ( refcolumn ) ]

Copy

Sample Source Patterns¶

Input Code:¶

Redshift¶

 CREATE TABLE table1 (
    col1 INTEGER REFERENCES table_test (col1)
);

Copy

Output Code:¶

Snowflake¶

 CREATE TABLE table1 (
    col1 INTEGER REFERENCES table_test (col1)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}'
;

Copy

Related EWIs¶

There are no known issues.

UNIQUE | PRIMARY KEY¶

Description¶

Specifies that the column can contain only unique values. In Snowflake, both UNIQUE and PRIMARY KEY are used to document and structure data, but they do not have active data validation functionality in the sense that you might expect in other database systems that enforce these restrictions at the storage level.

See the Redshift CREATE TABLE documentation (https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html) for this syntax.

Note

Grammar Syntax¶

 UNIQUE | PRIMARY KEY

Copy

Sample Source Patterns¶

Input Code:¶

Redshift¶

 CREATE TABLE table1 (
    col1 INTEGER PRIMARY KEY,
    col2 INTEGER UNIQUE
);

Copy

Output Code:¶

Snowflake¶

 CREATE TABLE table1 (
    col1 INTEGER PRIMARY KEY,
    col2 INTEGER UNIQUE
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}'
;

Copy

Related EWIs¶

There are no known issues.

COLLATE¶

Description¶

Specifies whether string search or comparison on the column is CASE_SENSITIVE or CASE_INSENSITIVE.

See the Redshift CREATE TABLE documentation (https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html) for this syntax.

Note

The default collation language is English. If your database uses a different language, please update the ‘en-’ prefix to match your database’s language. For more information, please refer to this link.

Grammar Syntax¶

 COLLATE CASE_SENSITIVE | COLLATE CASE_INSENSITIVE

Copy

Sample Source Patterns¶

Input Code:¶

Redshift¶

 CREATE TABLE table1 (
    col1 TEXT COLLATE CASE_SENSITIVE,
    col2 TEXT COLLATE CASE_INSENSITIVE
);

Copy

Output Code:¶

Snowflake¶

 CREATE TABLE table1 (
    col1 TEXT COLLATE 'en-cs',
    col2 TEXT COLLATE 'en-ci'
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}'
;

Copy

Known issues¶

There are no known issues.

DEFAULT¶

Description¶

Assigns a default data value for the column.

See the Redshift CREATE TABLE DEFAULT clause documentation (https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html#create-table-default) for this syntax.

Grammar Syntax¶

 DEFAULT default_expr

Copy

Sample Source Patterns¶

Input Code:¶

Redshift¶

 CREATE TABLE table1 (
    col1 INTEGER DEFAULT 1
);

Copy

Output Code:¶

Snowflake¶

 CREATE TABLE table1 (
    col1 INTEGER DEFAULT 1
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}'
;

Copy

Related EWIs¶

There are no known issues.

DISTKEY¶

Description¶

See the Redshift data distribution documentation (https://docs.aws.amazon.com/redshift/latest/dg/t_Distributing_data.html) for this syntax.

Grammar Syntax¶

 DISTKEY

Copy

Sample Source Patterns¶

Input Code:¶

Redshift¶

 CREATE TABLE table1 (
    col1 INTEGER DISTKEY
);

Copy

Output Code:¶

Snowflake¶

 CREATE TABLE table1 (
    col1 INTEGER
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}'
;

Copy

Related EWIs¶

There are no known issues.

ENCODE¶

Description¶

The compression encoding for a column. In Snowflake, defining ENCODE is unnecessary because it automatically handles data compression, unlike Redshift, which requires manual encoding settings. For this reason, the ENCODE statement is removed during migration.

See the Redshift CREATE TABLE ENCODE clause documentation (https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html#create-table-encode) for this syntax.

Grammar Syntax¶

 ENCODE encoding

Copy

Sample Source Patterns¶

Input Code:¶

Redshift¶

 CREATE TABLE table1 (
    col1 INTEGER ENCODE DELTA
);

Copy

Output Code:¶

Snowflake¶

 CREATE TABLE table1 (
    col1 INTEGER
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}'
;

Copy

Related EWIs¶

There are no known issues.

GENERATED BY DEFAULT AS IDENTITY¶

Description¶

Specifies that the column is a default IDENTITY column and enables you to automatically assign a unique value to the column.

See the Redshift IDENTITY column documentation (https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html#identity-generated-bydefault-clause) for this syntax.

Grammar Syntax¶

 GENERATED BY DEFAULT AS IDENTITY ( seed, step )

Copy

Sample Source Patterns¶

Input Code:¶

Redshift¶

 CREATE TABLE table1 (
    col1 INTEGER GENERATED BY DEFAULT AS IDENTITY(1,1)
);

Copy

Output Code:¶

Snowflake¶

 CREATE TABLE table1 (
    col1 INTEGER IDENTITY(1,1) ORDER
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}'
;

Copy

Related EWIs¶

There are no known issues.

IDENTITY¶

Description¶

Clause that specifies that the column is an IDENTITY column. (RedShift SQL Language Reference Identity (https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html#identity-clause)).

Grammar Syntax¶

 IDENTITY ( seed, step )

Copy

Sample Source Patterns¶

Input Code:¶

Redshift¶

 CREATE TABLE table1 (
    doc INTEGER,
    id1 INTEGER IDENTITY(1,1),
    id2 INTEGER  DEFAULT "identity"(674435, 0, ('5,3'::character varying)::text),
    id3 INTEGER  DEFAULT default_identity(963861, 1, '1,2'::text),
    id4 INTEGER  DEFAULT "default_identity"(963861, 1, '1,6'::text)
);

INSERT INTO table1 (doc) VALUES (1),(2),(3);

SELECT * FROM table1;

Copy

Results¶

DOC	ID1	ID2	ID3	ID4
1	1	5	1	1
2	2	8	3	7
3	3	11	5	13

Output Code:¶

Snowflake¶

 CREATE TABLE table1 (
    doc INTEGER,
    id1 INTEGER IDENTITY(1,1) ORDER,
    id2 INTEGER IDENTITY(5,3) ORDER,
    id3 INTEGER IDENTITY(1,2) ORDER,
    id4 INTEGER IDENTITY(1,6) ORDER
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "12/04/2024",  "domain": "test" }}';

INSERT INTO table1 (doc) VALUES (1),(2),(3);

SELECT * FROM
 table1;

Copy

Results¶

DOC	ID1	ID2	ID3	ID4
1	1	5	1	1
2	2	8	3	7
3	3	11	5	13

Known Issues ¶

No issues were found.

Related EWIs¶

There are no known issues.

SORTKEY¶

Description¶

See the Redshift data sorting documentation (https://docs.aws.amazon.com/redshift/latest/dg/t_Sorting_data.html) for this syntax.

Grammar Syntax¶

 SORTKEY

Copy

Sample Source Patterns¶

Input Code:¶

Redshift¶

 CREATE TABLE table1 (
    col1 INTEGER SORTKEY
);

Copy

Output Code:¶

Snowflake¶

 CREATE TABLE table1 (
    col1 INTEGER
)
--** SSC-FDM-RS0002 - THE PERFORMANCE OF CLUSTER BY IN SNOWFLAKE MAY VARY COMPARED TO THE PERFORMANCE OF SORTKEY IN REDSHIFT. **
CLUSTER BY (col1)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}';

Copy

Known issues¶

SSC-FDM-RS0002: The performance of CLUSTER BY in Snowflake may vary compared to the performance of SORTKEY in Redshift.