SnowConvert AI - BigQuery - Identifier differences between 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;
Copy

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

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`;
Copy

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

Known issues¶

By default, BigQuery considers table and dataset names as case sensitive, unless the is_case_insensitive 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
);
Copy

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, SSC-FDM-0019 will be generated to warn the user:

Snowflake¶

CREATE TABLE test.myTable
(
  col1 INTEGER
);

--** SSC-FDM-0019 - SEMANTIC INFORMATION COULD NOT BE LOADED FOR test.MyTable. CHECK IF THE NAME IS INVALID OR DUPLICATED. **
CREATE TABLE test.MyTable
(
  col1 INTEGER
);
Copy
Language: English