Identifier differences between Vertica and Snowflake

Quoted identifiers

In Vertica, quoted identifiers sticks to the case sensitivity rules, which means that, for example, column names are still case insensitive even when quoted. Thus, identifiers "ABC", "ABc", and "aBc" are synonymous, as are ABC, ABc, and aBc :

Vertica

CREATE TABLE test.quotedIdentTable
(
  "col#1" INTEGER
);

SELECT "col#1" FROM test.quotedIdentTable;

SELECT "COL#1" FROM test.quotedIdentTable;

In Snowflake, case sensitivity of quoted identifiers depends on the session parameter QUOTED_IDENTIFIERS_IGNORE_CASE, by default quoted identifiers comparison is case sensitive, this means that the result code from migrating the above example:

Snowflake

CREATE TABLE test.quotedIdentTable
(
  "col#1" INTEGER
);

SELECT
  "col#1"
FROM
  test.quotedIdentTable;

SELECT
  "COL#1"
FROM
  test.quotedIdentTable;

Will fail when executing the second select unless the session parameter is set to TRUE.

How SnowConvert migrates quoted identifiers

SnowConvert will analyze quoted identifiers to determine if they contain non-alphanumeric characters or are reserved words in Snowflake, if they do SnowConvert will left them as they are, alphanumeric identifiers will be left unquoted:

Vertica

CREATE TABLE test.identsTable1
(
  "col#1" INTEGER,
  "col2" INTEGER
);

-- Group is a reserved word
SELECT 
"col#1" AS "group",
"col2" AS "hello"
FROM
test.identsTable1;

Snowflake

CREATE TABLE test.identsTable1
(
  "col#1" INTEGER,
  col2 INTEGER
);

-- Group is a reserved word
SELECT
  "col#1" AS "group",
  col2 AS hello
FROM
  test.identsTable1;