SnowConvert: Redshift Basic elements¶

Description ¶

Names identify database objects, including tables and columns, as well as users and passwords. The terms name and identifier can be used interchangeably. There are two types of identifiers, standard identifiers and quoted or delimited identifiers. Identifiers must consist of only UTF-8 printable characters. ASCII letters in standard and delimited identifiers are case-insensitive and are folded to lowercase in the database. (Redshift SQL Language reference Names and identifiers).

Standard identifiers ¶

Standard SQL identifiers adhere to a set of rules and must:

• Begin with an ASCII single-byte alphabetic character or underscore character, or a UTF-8 multibyte character two to four bytes long.

• Subsequent characters can be ASCII single-byte alphanumeric characters, underscores, or dollar signs, or UTF-8 multibyte characters two to four bytes long.

• Be between 1 and 127 bytes in length, not including quotation marks for delimited identifiers.

• Contain no quotation marks and no spaces.

• Not be a reserved SQL keyword. (Redshift SQL Language reference Standard identifiers)

This syntax is fully supported by Snowflake.

 CREATE TABLE #TABLE_NAME
(
    COL#1 int,
    "col2#" int
);

INSERT INTO #TABLE_NAME(COL#1, "col2#") VALUES (1,20),(2,21),(3,22);

SELECT col#1, "col2#" as col# FROM #TABLE_NAME;

 CREATE TEMP TABLE _H_TABLE_NAME
(
	COL_H_1 int,
	"col2#" int
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/04/2025",  "domain": "test" }}';

INSERT INTO _H_TABLE_NAME (COL_H_1, "col2#") VALUES (1,20),(2,21),(3,22);

SELECT
	col_H_1,
	"col2#" as col_H_
FROM
	_H_TABLE_NAME;

Delimited identifiers ¶

Delimited identifiers (also known as quoted identifiers) begin and end with double quotation marks (“). If you use a delimited identifier, you must use the double quotation marks for every reference to that object. The identifier can contain any standard UTF-8 printable characters other than the double quotation mark itself. Therefore, you can create column or table names that include otherwise illegal characters, such as spaces or the percent symbol. (Redshift SQL Language reference Delimited identifiers).

In Redshift, identifiers can be enclosed in quotes and are not case-sensitive by default. However, in Snowflake, they are case-sensitive by default. For this reason, to emulate this behavior, we are removing the quotes from all identifiers that are enclosed in quotes, are not reserved keywords in Snowflake, and contain alphanumeric characters. Reserved keywords in Snowflake will always be enclosed in double quotes and defined in lowercase.

 CREATE TABLE lateral
(
    INCREMENT int,
    "local" int
);

INSERT INTO lateral(INCREMENT, "local") VALUES (1,20),(2,21),(3,22);

SELECT lateral.INCREMENT, "local" FROM LATERAL;

 CREATE TABLE "lateral"
(
    "increment" int,
    local int
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "12/10/2024",  "domain": "test" }}';

INSERT INTO "lateral" ("increment", local) VALUES (1,20),(2,21),(3,22);

SELECT
    "lateral"."increment",
    local
FROM
    "lateral";

Quoted identifiers in Functions¶

In Redshift, function names can be enclosed in quotes and are not case-sensitive by default. However, in Snowflake, functions may cause issues if they are in quotes and written in lowercase. For this reason, in Snowflake, any function name enclosed in quotes will always be transformed to uppercase and the quotation marks will be removed.

 SELECT "getdate"();

 SELECT GETDATE();

Description¶

In Redshift you can use some of the Snowflake reserved keywords as column names, table names, etc. For this reason, it is necessary that these words are enclosed in double quotes in order to be able to use them.

Sample Source Patterns¶

 CREATE TABLE alter
(
    alter INT
);

CREATE TABLE CONNECT
(
    CONNECT INT
);

DROP TABLE alter;
DROP TABLE CONNECT;

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

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

DROP TABLE "alter";
DROP TABLE "connect";

Related EWIs¶

No related EWIs.

Known Issues ¶

No issues were found.