Names and identifiers

Names and identifiers translation for Redshift

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.

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.

This change could impact the desired behavior if the enable_case_sensitive_identifier flag is set to true in your configuration. Future updates will allow users to define the desired transformation for these identifiers.

Sample Source Patterns

For this scenario, please keep in mind that "LATERAL" and "INCREMENT" are reserved words in Snowflake, while "LOCAL" is not a reserved word.

Input Code:

Redshift

IN -> Redshift_01.sql

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;

Result

increment	local
1	20
2	21
3	22

Output Code:

Snowflake

OUT -> Redshift_01.sql

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";

Result

increment	LOCAL
1	20
2	21
3	22

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.

Sample Source Patterns

Input Code:

Redshift

IN -> Redshift_01.sql

SELECT "getdate"();

Result

"GETDATE"()
2024-11-21 22:08:53.000000

Output Code:

Snowflake

OUT -> Redshift_01.sql

SELECT GETDATE();

Result

"GETDATE"()
2024-11-21 22:08:53.000 +0000

Recommendations

To work around this limitation, Snowflake provides the QUOTED_IDENTIFIERS_IGNORE_CASE session parameter, which causes Snowflake to treat lowercase letters in double-quoted identifiers as uppercase when creating and finding objects.

(Snowflake SQL Language Reference Identifier requirements).