Identifier differences between BigQuery and Snowflake

This page shows the differences between identifier types in BigQuery and Snowflake

Quoted identifiers

BigQuery quoted identifiers are enclosed by backticks (`) while Snowflake encloses them in double quotes (“).

In BigQuery, quoted identifiers stick to the case sensitivity rules, which means that, for example, column names are still case insensitive even when quoted:

BigQuery

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 then it will transform them to quoted identifiers in Snowflake, alphanumeric identifiers will be left unquoted:

BigQuery

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;

Known issues

Quoted identifiers

In Big Query, the following identifiers are equivalent:

BigQuery

CREATE TABLE test.`MyTableȘ1`
(
  col1 INTEGER
);

INSERT INTO test.`MyTableȘ1`(col1) VALUES (88);

-- Will be marked as missing reference
INSERT INTO `test.MyTableȘ1`(col1) VALUES (200);

But SnowConvert will mark the second insert statement with MSCEWI1050, this happens because test.`MyTableȘ1` and `test.MyTableȘ1` are processed as two different objects names, therefore the definition of `test.MyTable#1` is considered as a missing reference:

Snowflake

CREATE TABLE test."MyTableȘ1"
(
  col1 INTEGER
);

INSERT INTO test."MyTableȘ1" (col1) VALUES (88);

-- Will be marked as missing reference
--** MSC-WARNING - MSCEWI1050 - MISSING DEPENDENT OBJECT "`test.MyTable#1`" **
INSERT INTO test."MyTableȘ1" (col1) VALUES (200);

This missing reference case will not affect the identifiers transformation, both identifiers will be transformed correctly and match the resulting table name, however, it may affect the transformation of statements that require semantic information about the objects being referenced. To avoid missing reference warnings or incomplete statement transformations due to this issue please consider changing the usages of quoted identifiers so they are consistent with the name used to create the object in the input code.

Unquoted identifiers

By default, BigQuery considers table and dataset names as case insensitive, unless the is_case_sensitive option is activated for the dataset, this allows the following tables to coexist without problems:

BigQuery

CREATE TABLE test.myTable
(
  col1 INTEGER
);

CREATE TABLE test.MyTable
(
  col1 INTEGER
);

However, unquoted identifiers in Snowflake are always stored and compared in uppercase, meaning that test.MyTable will raise a duplicated object error when trying to create it. SnowConvert also works under the assumption that identifiers are case insensitive, so when one of these scenarios appears during transformation, MSCEWI1039 will be generated to warn the user:

Snowflake

CREATE TABLE test.myTable
(
  col1 INTEGER
);
--** MSC-WARNING - MSCEWI1039 - SEMANTIC INFORMATION COULD NOT BE LOADED FOR test.MyTable. CHECK IF THE NAME IS INVALID OR DUPLICATED. **
CREATE TABLE test.MyTable
(
  col1 INTEGER
);