SnowConvert AI - BigQuery Functional Differences¶

Note

Conversion Scope

SnowConvert AI for Google BigQuery currently supports assessment and translation for TABLES and VIEWS. Although SnowConvert AI can recognize other types of statements, they are not fully supported.

SSC-FDM-BQ0001¶

Accessing arrays produces NULL instead of an error for positive out of bounds indexes in Snowflake.

Description¶

When accessing an ARRAY object by index in Snowflake, specifying an index greater than the size of the array will result in a NULL value, this differs with the behavior of BigQuery, where accessing an ARRAY with an index that is out of bounds will produce an error, unless the functions SAFE_OFFSET or SAFE_ORDINAL are used.

This FDM is added to any ARRAY access that is not safe.

Code Example¶

Input Code:¶

BigQuery¶

 SELECT ([40, 12, 30])[8];

SELECT ([40, 12, 30])[SAFE_OFFSET(8)];

Generated Code:¶

Snowflake¶

 SELECT
--** SSC-FDM-BQ0001 - ACCESSING ARRAYS PRODUCES NULL INSTEAD OF AN ERROR FOR POSITIVE OUT OF BOUNDS INDEXES IN SNOWFLAKE **
([40, 12, 30])[8];

SELECT
PUBLIC.SAFE_OFFSET_UDF( ([40, 12, 30]), 8);

Best Practices¶

Analyze the uses of array access in the code. If there was never the risk of getting an out of bounds error in the original code, no difference will be observed and this FDM can be safely ignored.
If the original code relies on out-of-bounds access raising an error (e.g., for flow control), add explicit bounds checking in Snowflake using ARRAY_SIZE before accessing the array.

SSC-FDM-BQ0002¶

Exception system variables are not supported in Snowflake.

Description¶

BigQuery’s exception system variables (@@error.message, @@error.stack_trace, @@error.statement_text, @@error.formatted_stack_trace) have no direct equivalent in Snowflake. SnowConvert AI replaces exception variable references with OBJECT_CONSTRUCT('SQLERRM', SQLERRM, 'SQLCODE', SQLCODE, 'SQLSTATE', SQLSTATE) as a workaround. This workaround provides basic error information but does not include stack trace or statement text details available in BigQuery. For more information, see Handling Exceptions in Snowflake.

Code Example¶

Input Code:¶

BigQuery¶

 CREATE OR REPLACE PROCEDURE test.proc1()
BEGIN
  SELECT 1/0;
EXCEPTION WHEN ERROR THEN
  SELECT
    @@error.message as message,
    @@error.stack_trace as stack_trace,
    @@error.statement_text as statement_text,
    @@error.formatted_stack_trace as formatted_stack_trace;
END;

Result¶

[
  {
    "message": "Query error: division by zero: 1 / 0 at [snowflake-snowconvert-team.test.proc1:2:3]",
    "stack_trace": [
      {
        "line": "2",
        "column": "3",
        "filename": null,
        "location": "snowflake-snowconvert-team.test.proc1"
      },
      {
        "line": "1",
        "column": "1",
        "filename": null,
        "location": null
      }
    ],
    "statement_text": "SELECT 1/0",
    "formatted_stack_trace": "At snowflake-snowconvert-team.test.proc1[2:3]\nAt [1:1]\n"
  }
]

Generated Code:¶

Snowflake¶

 CREATE OR REPLACE PROCEDURE test.proc1 ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "bigquery",  "convertedOn": "04/09/2025",  "domain": "test" }}'
AS
$$
    BEGIN
    SELECT 1/0;
  EXCEPTION WHEN OTHER THEN
--      --** SSC-FDM-BQ0002 - EXCEPTION SYSTEM VARIABLES ARE NOT SUPPORTED IN SNOWFLAKE. **
--    SELECT
--      @@error.message as message,
--      @@error.stack_trace as stack_trace,
--      @@error.statement_text as statement_text,
--      @@error.formatted_stack_trace as formatted_stack_trace;
      RETURN OBJECT_CONSTRUCT('SQLERRM', SQLERRM, 'SQLCODE', SQLCODE, 'SQLSTATE', SQLSTATE);
    END;
$$;

Result¶

{
  "SQLCODE": 100051,
  "SQLERRM": "Division by zero",
  "SQLSTATE": "22012"
}

Best Practices¶

Snowflake provides three built-in exception variables as an alternative to BigQuery’s @@error system variables:

BigQuery Variable	Snowflake Equivalent	Notes
`@@error.message`	`SQLERRM`	Error message text
`@@error.statement_text`	N/A	No direct equivalent in Snowflake
`@@error.stack_trace`	N/A	No direct equivalent in Snowflake
`@@error.formatted_stack_trace`	N/A	No direct equivalent in Snowflake
N/A	`SQLSTATE`	5-character ANSI SQL state code
N/A	`SQLCODE`	5-digit signed integer error code

Review the generated OBJECT_CONSTRUCT('SQLERRM', SQLERRM, 'SQLCODE', SQLCODE, 'SQLSTATE', SQLSTATE) workaround and adjust it based on your specific error-handling requirements.
For more information, see Handling Exceptions in Snowflake.

SSC-FDM-BQ0003¶

Unable to generate correct return table clause due to missing dependent object information.

Note

This issue is deprecated and no longer generated by SnowConvert AI. Check SSC-EWI-BQ0009 for the issue now generated for this scenario

Description¶

Snowflake requires a valid RETURNS TABLE clause for CREATE TABLE FUNCTION statements.

If the original BigQuery source code does not have a RETURNS TABLE clause, SnowConvert AI must build one. To do this, an analysis is made to the CREATE TABLE FUNCTION query to properly infer the types of the columns of the resulting table. When SnowConvert AI cannot gather the required information, this EWI is added.

Code Example¶

Input Code:¶

BigQuery¶

 CREATE OR REPLACE TABLE FUNCTION function_name_noreturns_asterisk_join (parameter_name INTEGER)
AS
  SELECT *
  FROM unknownTable1 t1
  JOIN unknownTable2 t2 ON t1.col1 = t2.fk_col1;

Generated Code:¶

Snowflake¶

 --** SSC-FDM-0007 - MISSING DEPENDENT OBJECTS "unknownTable1", "unknownTable2" **

CREATE OR REPLACE FUNCTION function_name_noreturns_asterisk_join (parameter_name INTEGER)
----** SSC-FDM-BQ0003 - UNABLE TO GENERATE CORRECT RETURNS TABLE CLAUSE DUE TO MISSING DEPENDENT OBJECT INFORMATION. **
--RETURNS TABLE (
--)
AS
    $$
      SELECT *
      FROM
      unknownTable1 t1
      JOIN
          unknownTable2 t2 ON t1.col1 = t2.fk_col1
    $$;

Best Practices¶

Always try to include any dependent object definitions in the input code, so that SnowConvert AI has access to important information.
If you need more support, you can email us at snowconvert-support@snowflake.com

SSC-FDM-BQ0004¶

The INFER_SCHEMA function requires a file path without wildcards to generate the table template, replace the FILE_PATH placeholder with it

Warning

This FDM is deprecated; please refer to SSC-FDM-0035 for the latest version of this FDM.

Description¶

The INFER_SCHEMA function is used in Snowflake to generate the columns definition of a table based on the structure of a file, it requires a LOCATION parameter that specifies the path to a file or folder that will be used to construct the table columns, however, this path does not support regex, meaning that the wildcard * character is not supported.

When the table has no columns, SnowConvert AI will check all URIS to find one that does not use wildcards and use it in the INFER_SCHEMA function. When no URI meets such criteria, this FDM and a FILE_PATH placeholder is generated, and the placeholder has to be replaced with the path of one of the files referenced by the external table to generate the table columns.

Code Example¶

Input Code:¶

BigQuery¶

 CREATE OR REPLACE EXTERNAL TABLE test.my_external_table_json2
OPTIONS(
  FORMAT='JSON',
  URIS=['gs://sc_external_table_bucket/folder_with_json/*']
);

Generated Code:¶

Snowflake¶

 CREATE OR REPLACE TEMPORARY FILE FORMAT SC_TEST_MY_EXTERNAL_TABLE_JSON2_FORMAT
TYPE = JSON;


CREATE OR REPLACE EXTERNAL TABLE test.my_external_table_json2 USING TEMPLATE (
SELECT
  ARRAY_AGG(OBJECT_CONSTRUCT('COLUMN_NAME', COLUMN_NAME, 'TYPE', TYPE, 'NULLABLE', NULLABLE, 'EXPRESSION', EXPRESSION))
FROM
  --** SSC-FDM-BQ0004 - THE INFER_SCHEMA FUNCTION REQUIRES A FILE PATH WITHOUT WILDCARDS TO GENERATE THE TABLE TEMPLATE, REPLACE THE FILE_PATH PLACEHOLDER WITH IT **
  TABLE(INFER_SCHEMA(LOCATION => '@EXTERNAL_STAGE/FILE_PATH', FILE_FORMAT => 'SC_TEST_MY_EXTERNAL_TABLE_JSON2_FORMAT'))
)
!!!RESOLVE EWI!!! /*** SSC-EWI-BQ0015 - EXTERNAL TABLE REQUIRES AN EXTERNAL STAGE TO ACCESS gs://sc_external_table_bucket, DEFINE AND REPLACE THE EXTERNAL_STAGE PLACEHOLDER ***/!!!
LOCATION = @EXTERNAL_STAGE
AUTO_REFRESH = false
PATTERN = 'folder_with_json/.*'
FILE_FORMAT = (TYPE = JSON);

Best Practices¶

If you need more support, you can email us at snowconvert-support@snowflake.com

SSC-FDM-BQ0005¶

Parsing the CSV header is not supported in external tables, columns must be renamed to match the original names

Description¶

Snowflake external tables do not support parsing the header of CSV files. SKIP_HEADER is used as a workaround to avoid runtime errors, but the resulting table column names will have auto-generated names (c1, c2, …, cN) instead of the original header names.

When SnowConvert AI detects an external table with CSV file format and no explicit column list, it adds the SKIP_HEADER = 1 file format option. The columns must be manually renamed to match the original names from the CSV header.

Code Example¶

Input Code:¶

BigQuery¶

 CREATE OR REPLACE EXTERNAL TABLE test.my_external_table_csv
OPTIONS(
  FORMAT='CSV',
  URIS=['gs://sc_external_table_bucket/folder_with_csv/Employees.csv']
);

Generated Code:¶

Snowflake¶

 CREATE OR REPLACE TEMPORARY FILE FORMAT SC_TEST_MY_EXTERNAL_TABLE_CSV_FORMAT
TYPE = CSV
SKIP_HEADER = 1;

CREATE OR REPLACE EXTERNAL TABLE test.my_external_table_csv
--** SSC-FDM-BQ0005 - PARSING THE CSV HEADER IS NOT SUPPORTED IN EXTERNAL TABLES, COLUMNS MUST BE RENAMED TO MATCH THE ORIGINAL NAMES **
USING TEMPLATE (
SELECT
  ARRAY_AGG(OBJECT_CONSTRUCT('COLUMN_NAME', COLUMN_NAME, 'TYPE', TYPE, 'NULLABLE', NULLABLE, 'EXPRESSION', EXPRESSION))
FROM
  TABLE(INFER_SCHEMA(LOCATION => '@EXTERNAL_STAGE/folder_with_csv/Employees.csv', FILE_FORMAT => 'SC_TEST_MY_EXTERNAL_TABLE_CSV_FORMAT'))
)
!!!RESOLVE EWI!!! /*** SSC-EWI-0032 - EXTERNAL TABLE REQUIRES AN EXTERNAL STAGE TO ACCESS gs://sc_external_table_bucket, DEFINE AND REPLACE THE EXTERNAL_STAGE PLACEHOLDER ***/!!!
LOCATION = @EXTERNAL_STAGE
AUTO_REFRESH = false
PATTERN = 'folder_with_csv/Employees.csv'
FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1);

Best Practices¶

Rename the auto-generated column names (c1, c2, …, cN) back to the original column names from the CSV file header.
If the original column names are known, use ALTER TABLE ... RENAME COLUMN or recreate the external table with explicit column definitions.
For non-external-table loading scenarios, consider using MATCH_BY_COLUMN_NAME with PARSE_HEADER = TRUE in the file format to automatically match columns by header names.

SSC-FDM-BQ0006¶

Reading from Google Drive is not supported in Snowflake, upload the files to the external location and replace the FILE_PATH placeholders

Description¶

Snowflake does not support reading data from files hosted in Google Drive, this FDM is generated to notify it, please upload the Google Drive files to the external location so they can be accessed through the external stage.

The PATTERN clause will hold autogenerated placeholders FILE_PATH0, FILE_PATH1, …, FILE_PATHN that should be replaced with the file/folder path after the files were moved to the external location.

Code Example¶

Input Code:¶

BigQuery¶

 CREATE OR REPLACE EXTERNAL TABLE test.my_external_table_drive_test
OPTIONS(
  FORMAT='JSON',
  URIS=['https://drive.google.com/open?id=someFileId']
);

Generated Code:¶

Snowflake¶

 CREATE OR REPLACE TEMPORARY FILE FORMAT SC_TEST_MY_EXTERNAL_TABLE_DRIVE_TEST_FORMAT
TYPE = JSON;

CREATE OR REPLACE EXTERNAL TABLE test.my_external_table_drive_test USING TEMPLATE (
SELECT
  ARRAY_AGG(OBJECT_CONSTRUCT('COLUMN_NAME', COLUMN_NAME, 'TYPE', TYPE, 'NULLABLE', NULLABLE, 'EXPRESSION', EXPRESSION))
FROM
  --** SSC-FDM-0035 - THE INFER_SCHEMA FUNCTION REQUIRES A FILE PATH WITHOUT WILDCARDS TO GENERATE THE TABLE TEMPLATE, REPLACE THE FILE_PATH PLACEHOLDER WITH IT **
  TABLE(INFER_SCHEMA(LOCATION => '@EXTERNAL_STAGE/FILE_PATH', FILE_FORMAT => 'SC_TEST_MY_EXTERNAL_TABLE_DRIVE_TEST_FORMAT'))
)
!!!RESOLVE EWI!!! /*** SSC-EWI-0032 - EXTERNAL TABLE REQUIRES AN EXTERNAL STAGE TO ACCESS AN EXTERNAL LOCATION, DEFINE AND REPLACE THE EXTERNAL_STAGE PLACEHOLDER ***/!!!
LOCATION = @EXTERNAL_STAGE
AUTO_REFRESH = false
--** SSC-FDM-BQ0006 - READING FROM GOOGLE DRIVE IS NOT SUPPORTED IN SNOWFLAKE, UPLOAD THE FILES TO THE EXTERNAL LOCATION AND REPLACE THE FILE_PATH PLACEHOLDERS **
PATTERN = 'FILE_PATH0'
FILE_FORMAT = (TYPE = JSON);

Best Practices¶

Download the files from Google Drive and upload them to a cloud storage location accessible by Snowflake (e.g., Amazon S3, Azure Blob Storage, or Google Cloud Storage).
Create or configure an external stage in Snowflake pointing to the cloud storage location.
Replace the FILE_PATH placeholders in the PATTERN clause with the actual file or folder paths relative to the external stage.

SSC-FDM-BQ0007¶

The GOOGLE_SHEETS format is not supported in Snowflake. CSV file type is used as a workaround.

Description¶

The GOOGLE_SHEETS format is not supported in Snowflake. CSV file type is used as a workaround because the structure of Google Sheets data is similar to CSV.

When SnowConvert AI detects an external table using the GOOGLE_SHEETS format, it produces an external table with the CSV file format instead. The resulting table expects a CSV file rather than a Google Sheets source.

Code Example¶

Input Code:¶

BigQuery¶

 CREATE OR REPLACE EXTERNAL TABLE test.spreadsheetTable
(
  Name STRING,
  Code INTEGER,
  Price INTEGER,
  Expiration_date DATE
)
OPTIONS(
  format="GOOGLE_SHEETS",
  skip_leading_rows = 1,
  uris=['https://docs.google.com/spreadsheets/d/someFileId/edit?usp=sharing']
);

Generated Code:¶

Snowflake¶

--** SSC-FDM-BQ0007 - THE GOOGLE_SHEETS FORMAT IS NOT SUPPORTED IN SNOWFLAKE. CSV FILE TYPE IS USED AS A WORKAROUND. **
CREATE OR REPLACE EXTERNAL TABLE test.spreadsheetTable
(
  Name STRING AS CAST(GET_IGNORE_CASE($1, 'c1') AS STRING),
  Code INTEGER AS CAST(GET_IGNORE_CASE($1, 'c2') AS INTEGER),
  Price INTEGER AS CAST(GET_IGNORE_CASE($1, 'c3') AS INTEGER),
  Expiration_date DATE AS CAST(GET_IGNORE_CASE($1, 'c4') AS DATE)
)
!!!RESOLVE EWI!!! /*** SSC-EWI-0032 - EXTERNAL TABLE REQUIRES AN EXTERNAL STAGE TO ACCESS AN EXTERNAL LOCATION, DEFINE AND REPLACE THE EXTERNAL_STAGE PLACEHOLDER ***/!!!
LOCATION = @EXTERNAL_STAGE
AUTO_REFRESH = false
--** SSC-FDM-BQ0006 - READING FROM GOOGLE DRIVE IS NOT SUPPORTED IN SNOWFLAKE, UPLOAD THE FILES TO THE EXTERNAL LOCATION AND REPLACE THE FILE_PATH PLACEHOLDERS **
PATTERN = 'FILE_PATH0'
FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "bigquery",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}';

Best Practices¶

Export the Google Sheets data as CSV files and upload them to a cloud storage location accessible by Snowflake.
Verify that the CSV export preserves the expected data types and formatting, especially for dates, numbers, and text fields with commas.
If the external table also references Google Drive URIs, see SSC-FDM-BQ0006 for instructions on migrating the files to an external stage.

SSC-FDM-BQ0008¶

Where clause references a column of STRUCT type. Comparison operations may produce different results in Snowflake.

Description¶

BigQuery STRUCT types have no direct equivalent in Snowflake. VARIANT is used as a workaround (see SSC-FDM-0034). When a comparison involves a Snowflake VARIANT created from a BigQuery STRUCT, the results may differ because Snowflake compares both keys and values, whereas BigQuery compares only values regardless of field names.

This FDM is added when a WHERE clause comparison involves a column of STRUCT type that was converted to VARIANT.

Code Example¶

Input Code:¶

BigQuery¶

 CREATE OR REPLACE TABLE test.compExprTable
(
  COL1 STRUCT<sc1 INT64>,
  COL2 STRUCT<sc2 INT64>
);

SELECT * FROM test.compExprTable WHERE COL1 <> (COL2);

Output Code:¶

Snowflake¶

 CREATE OR REPLACE TABLE test.compExprTable
(
  COL1 VARIANT /*** SSC-FDM-0034 - STRUCT<INT64> CONVERTED TO VARIANT. SOME OF ITS USAGES MIGHT HAVE FUNCTIONAL DIFFERENCES. ***/,
  COL2 VARIANT /*** SSC-FDM-0034 - STRUCT<INT64> CONVERTED TO VARIANT. SOME OF ITS USAGES MIGHT HAVE FUNCTIONAL DIFFERENCES. ***/
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "bigquery",  "convertedOn": "07/02/2025",  "domain": "no-domain-provided" }}';

SELECT * FROM
  test.compExprTable
--** SSC-FDM-BQ0008 - WHERE CLAUSE REFERENCES A COLUMN OF STRUCT TYPE. COMPARISON OPERATIONS MAY PRODUCE DIFFERENT RESULTS IN SNOWFLAKE. **
WHERE COL1 <> (COL2);

Best Practices¶

Review WHERE clause comparisons involving STRUCT-derived VARIANT columns. If the original BigQuery query compared STRUCTs by value only, extract and compare individual fields explicitly in Snowflake.
For example, replace WHERE col1 <> col2 with WHERE col1:sc1 <> col2:sc2 to compare specific field values instead of the entire VARIANT object.
For more information on VARIANT comparison behavior, see the Snowflake VARIANT documentation.

SSC-FDM-BQ0010¶

Geography function is not required in Snowflake.

Description¶

Snowflake automatically detects GEOGRAPHY data from WGS 84 formatted strings (WKT, WKB, GeoJSON), so explicit geography conversion functions like ST_GEOGFROMTEXT are not required in VALUES clause inserts. SnowConvert AI removes the function call and passes the string literal directly. This FDM is added to notify that the geography function was removed.

Code Example¶

Input Code:¶

BigQuery¶

 CREATE OR REPLACE TABLE test.geographyType
(
  COL1 GEOGRAPHY
);

INSERT INTO test.geographyType VALUES
(ST_GEOGFROMTEXT('POINT(-122.35 37.55)')),
(ST_GEOGFROMTEXT('LINESTRING(-124.20 42.00, -120.01 41.99)'));

SELECT * FROM test.geographyType;

Output Code:¶

Snowflake¶

 CREATE OR REPLACE TABLE test.geographyType
(
  COL1 GEOGRAPHY
);

INSERT INTO test.geographyType
VALUES
    (
     --** SSC-FDM-BQ0010 - THE FUNCTION 'ST_GEOGFROMTEXT' IS NOT REQUIRED IN SNOWFLAKE. **
     'POINT(-122.35 37.55)'), (
     --** SSC-FDM-BQ0010 - THE FUNCTION 'ST_GEOGFROMTEXT' IS NOT REQUIRED IN SNOWFLAKE. **
     'LINESTRING(-124.20 42.00, -120.01 41.99)');

ALTER SESSION SET GEOGRAPHY_OUTPUT_FORMAT = 'WKT';
SELECT * FROM
test.geographyType;

Best Practices¶

This FDM can be safely ignored in most cases. Snowflake natively supports GEOGRAPHY data from WKT, WKB, and GeoJSON string formats without requiring explicit conversion functions.
If the removed function performed validation or transformation beyond simple type casting, verify that the inserted data is valid GEOGRAPHY data in Snowflake.
For more information, see the Snowflake GEOGRAPHY data type documentation.

SSC-FDM-BQ0011¶

Named parameters in this script were transformed to Snowflake CLI variables.

Description¶

BigQuery supports named parameters using the @parameter_name syntax in queries. SnowConvert AI transforms these named parameters to Snowflake CLI variables using the <% parameter_name %> syntax.

To execute the transformed .sql scripts containing named parameters, use Snowflake CLI with variable substitution.

For more information on how to set up and use Snowflake CLI, see What is Snowflake CLI?

Code Example¶

Input Code:¶

BigQuery¶

SELECT column1 FROM test.parametersExample WHERE column2 = @searchValue;

Example execution (using the bq query command)¶

bq query \
  --use_legacy_sql=false \
  --parameter=searchValue:Int64:80 \
  'SELECT column1 FROM test.parametersExample WHERE column2 = @searchValue'

Output Code:¶

Snowflake¶

--** SSC-FDM-BQ0011 - NAMED PARAMETERS IN THIS SCRIPT WERE TRANSFORMED TO SNOWFLAKE CLI VARIABLES. **
SELECT column1 FROM
test.parametersExample
WHERE column2 = <% searchValue %>;

Example execution (Snowflake CLI)¶

snow sql -f output_file_path -D "searchValue=80"

Best Practices¶

Install and configure Snowflake CLI to execute the transformed scripts with variable substitution using the -D flag (e.g., snow sql -f script.sql -D "param=value").
Review each transformed <% parameter_name %> variable to ensure the parameter name and intended value match the original BigQuery @parameter_name usage.
If the transformed script will be executed outside of Snowflake CLI (e.g., in a Snowflake worksheet), replace <% parameter_name %> variables with literal values or session variables as appropriate.

SSC-FDM-BQ0012¶

Select * with multiple UNNEST operators will produce column ambiguity in Snowflake

Description¶

As part of the SnowConvert transformation for the UNNEST operator, the FLATTEN function is used, this function generates multiple columns not required to emulate the UNNEST operator functionality like the THIS or PATH columns.

When a SELECT * with the UNNEST operator is found, SnowConvert will remove the unnecessary columns using the EXCLUDE keyword, however, when multiple UNNEST operators are used in the same statement, the columns can not be removed due to ambiguity problems, this FDM will be generated to mark these cases.

It is recommended to expand the SELECT expression list in order to specify only the expected columns and solve this issue.

Code Example¶

Input Code:¶

BigQuery¶

SELECT * FROM UNNEST ([10,20,30]);

SELECT * FROM UNNEST ([10,20,30]) AS numbers, UNNEST(['Hi', 'Hello', 'Bye']) AS words;

Generated Code:¶

Snowflake¶

SELECT
* EXCLUDE(SEQ, KEY, PATH, THIS, INDEX)
FROM
TABLE(FLATTEN(INPUT => [10,20,30])) AS F0_ (
SEQ,
KEY,
PATH,
INDEX,
F0_,
THIS
);

SELECT
--** SSC-FDM-BQ0012 - SELECT * WITH MULTIPLE UNNEST OPERATORS WILL RESULT IN COLUMN AMBIGUITY IN SNOWFLAKE **
 * FROM
TABLE(FLATTEN(INPUT => [10,20,30])) AS numbers (
SEQ,
KEY,
PATH,
INDEX,
numbers,
THIS
),
TABLE(FLATTEN(INPUT => ['Hi', 'Hello', 'Bye'])) AS words (
SEQ,
KEY,
PATH,
INDEX,
words,
THIS
);

Recommendations¶

Expand the SELECT list: Replace SELECT * with an explicit column list specifying only the columns you need from each UNNEST/FLATTEN result. This eliminates the ambiguity caused by duplicate metadata columns.
Use table aliases: Qualify each column reference with the corresponding table alias to avoid ambiguity between the FLATTEN results.

SSC-FDM-BQ0013¶

BigQuery PIVOT output column names may differ from Snowflake; downstream queries that reference pivot output columns may need updates.

Description¶

BigQuery and Snowflake both support a PIVOT operator with the same syntax, including the optional IN-list aliasing form ('Q1' AS first). SnowConvert AI passes single-aggregate PIVOT operators through unchanged. The operator produces the same rows on both engines, but they can name the resulting columns differently:

In Snowflake, an unaliased string value such as 'Q1' becomes a column named 'Q1' (the literal keeps its quotes and must be referenced as "'Q1'"); an IN-list alias replaces the value ('Q1' AS first becomes FIRST); and an aggregate alias is appended as a suffix (SUM(amount) AS total appends _TOTAL). See the Snowflake PIVOT documentation for the exact rules.
BigQuery applies its own naming for the same constructs (see the BigQuery PIVOT operator), which can differ from Snowflake’s for unaliased values and for aliased aggregates.

SnowConvert AI emits this FDM only when the output column names can actually diverge — that is, when at least one IN-list value is unaliased, or the aggregate function is aliased. When every IN-list value is aliased and the aggregate has no alias, both engines produce identical column names, so the marker is suppressed and no downstream changes are required.

The pivot itself executes correctly in both engines; the FDM only flags that downstream queries (SELECT pivoted_col FROM ..., ORDER BY, joins, view definitions) that reference the output columns by name may need to be updated to match the Snowflake naming rule.

Multi-aggregate PIVOT (PIVOT(agg1, agg2 FOR ...)) is not supported in Snowflake. SnowConvert AI currently passes it through unchanged without emitting this FDM; a dedicated EWI is planned as follow-up work.

Code Example¶

Input Code:¶

BigQuery¶

SELECT * FROM sales PIVOT(SUM(amount) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'));

Generated Code:¶

Snowflake¶

SELECT
  *
FROM
  sales
  --** SSC-FDM-BQ0013 - BIGQUERY PIVOT OUTPUT COLUMN NAMES MAY DIFFER FROM SNOWFLAKE; DOWNSTREAM QUERIES THAT REFERENCE PIVOT OUTPUT COLUMNS MAY NEED UPDATES **
  PIVOT(
    SUM(amount)
    FOR quarter
    IN ('Q1', 'Q2', 'Q3', 'Q4')
  );

When every IN-list value is aliased and the aggregate has no alias, the column names match on both engines, so no marker is added:

SELECT
  *
FROM
  sales PIVOT(
    SUM(amount)
    FOR quarter
    IN ('Q1' AS first, 'Q2' AS second)
  );

Recommendations¶

Inspect downstream column references. Review every SELECT, WHERE, ORDER BY, JOIN, or view definition that references the PIVOT result columns by name and update it to the Snowflake naming rule. For example, where BigQuery exposed an unaliased value 'Q1', Snowflake produces the column 'Q1', referenced as "'Q1'".
Avoid the difference at the source. To get identical column names on both engines, alias every IN-list value and leave the aggregate unaliased (for example IN ('Q1' AS first, 'Q2' AS second)). With both conditions met, SnowConvert AI does not emit this FDM.
Or rename explicitly downstream. Wrap the PIVOT in a subquery and alias the output columns so downstream queries are insulated from the engine difference.

SSC-FDM-BQ0014¶

ST_DISTANCE third argument (use_spheroid) was removed; Snowflake always uses sphere-based geography distance.

Description¶

BigQuery’s ST_DISTANCE function accepts an optional third boolean argument use_spheroid that controls whether the distance computation uses the WGS84 ellipsoid (TRUE) or a perfect sphere (FALSE, the default). Snowflake’s ST_DISTANCE accepts only two arguments and always uses sphere-based math for GEOGRAPHY inputs.

When the BigQuery argument is statically the literal FALSE the third argument is silently dropped because the calls are semantically identical. When the argument is the literal TRUE, a column reference, or any other expression whose value cannot be proven equal to FALSE at compile time, the third argument is removed and this FDM is added to flag the spheroid-vs-sphere divergence — the result will differ from BigQuery’s by up to ~0.5% on long distances.

Code Example¶

Input Code:¶

BigQuery¶

SELECT ST_DISTANCE(g1, g2) FROM cities;
SELECT ST_DISTANCE(g1, g2, FALSE) FROM cities;
SELECT ST_DISTANCE(g1, g2, TRUE) FROM cities;
SELECT ST_DISTANCE(g1, g2, use_spheroid_col) FROM cities;

Generated Code:¶

Snowflake¶

SELECT ST_DISTANCE(g1, g2) FROM cities;
SELECT ST_DISTANCE(g1, g2) FROM cities;
SELECT ST_DISTANCE(g1, g2) /*** SSC-FDM-BQ0014 - THE THIRD ARGUMENT 'USE_SPHEROID' OF ST_DISTANCE WAS REMOVED. SNOWFLAKE ALWAYS USES SPHERE-BASED GEOGRAPHY DISTANCE. ***/ FROM cities;
SELECT ST_DISTANCE(g1, g2) /*** SSC-FDM-BQ0014 - THE THIRD ARGUMENT 'USE_SPHEROID' OF ST_DISTANCE WAS REMOVED. SNOWFLAKE ALWAYS USES SPHERE-BASED GEOGRAPHY DISTANCE. ***/ FROM cities;

Recommendations¶

Accept sphere-based math when precision tolerance permits. For most application use cases the difference between sphere and ellipsoid distance is below 0.5%; if downstream code is not sensitive to that, the FDM can be safely waived.
Verify whether the dropped argument was column-driven. If the third argument was a column reference, evaluate whether the query was actually exercising spheroid math at runtime. A column always-FALSE makes the difference moot; a column always-TRUE means every row needs the precision difference reviewed.

SSC-FDM-BQ0015¶

PARSE_JSON ‘wide_number_mode’ argument is not supported in Snowflake and was removed.

Description¶

BigQuery’s PARSE_JSON accepts an optional named argument wide_number_mode => 'exact' | 'round' that controls how numbers exceeding INT64/FLOAT64 precision are handled at parse time. Snowflake’s PARSE_JSON does not expose an equivalent option; it preserves number precision up to 38 digits natively.

When the BigQuery PARSE_JSON call includes the wide_number_mode named argument SnowConvert AI strips the argument and emits this FDM to flag the silent removal so the user can verify Snowflake’s 38-digit precision is sufficient for their data.

Code Example¶

Input Code:¶

BigQuery¶

SELECT PARSE_JSON('{"big": 9007199254740993}', wide_number_mode => 'exact') AS j;

Generated Code:¶

Snowflake¶

SELECT
--** SSC-FDM-BQ0015 - PARSE_JSON 'WIDE_NUMBER_MODE' ARGUMENT IS NOT SUPPORTED IN SNOWFLAKE AND WAS REMOVED. SNOWFLAKE PRESERVES NUMBER PRECISION UP TO 38 DIGITS. **
PARSE_JSON('{"big": 9007199254740993}') AS j;

Recommendations¶

Verify number magnitudes against Snowflake’s 38-digit limit. If the JSON payloads in your data set never exceed 38 significant digits, the removed argument has no functional effect and the FDM can be waived.

SSC-FDM-BQ0019¶

ST_AREA/ST_LENGTH use_spheroid argument was removed; Snowflake always uses sphere-based geography computation.

Description¶

BigQuery’s ST_AREA and ST_LENGTH accept an optional use_spheroid boolean argument that controls whether the computation uses a spheroid model (use_spheroid = TRUE, more precise) or a sphere model (use_spheroid = FALSE, the default). Snowflake’s equivalents always use sphere-based great-circle math, matching BigQuery’s default behavior.

SnowConvert AI drops the use_spheroid argument in all cases. When the argument is the literal FALSE (semantically equivalent to the 1-arg form), it is dropped silently. When it is the literal TRUE or a non-literal expression that cannot be statically proven to be FALSE, this FDM is emitted to flag the ~0.5% precision difference between spheroid and sphere math.

Code Example¶

Input Code:¶

BigQuery¶

SELECT ST_AREA(g) FROM polygons;
SELECT ST_AREA(g, FALSE) FROM polygons;
SELECT ST_AREA(g, TRUE) FROM polygons;
SELECT ST_AREA(g, use_spheroid_col) FROM polygons;

Generated Code:¶

Snowflake¶

SELECT ST_AREA(g) FROM polygons;
SELECT ST_AREA(g) FROM polygons;
SELECT ST_AREA(g) /*** SSC-FDM-BQ0019 - THE 'USE_SPHEROID' ARGUMENT WAS REMOVED FROM ST_AREA/ST_LENGTH. SNOWFLAKE ALWAYS USES SPHERE-BASED GEOGRAPHY COMPUTATION. ***/ FROM polygons;
SELECT ST_AREA(g) /*** SSC-FDM-BQ0019 - THE 'USE_SPHEROID' ARGUMENT WAS REMOVED FROM ST_AREA/ST_LENGTH. SNOWFLAKE ALWAYS USES SPHERE-BASED GEOGRAPHY COMPUTATION. ***/ FROM polygons;

Recommendations¶

Accept sphere-based math when precision tolerance permits. For most use cases the difference between spheroid and sphere area/length calculations is below 0.5%. If downstream code is not sensitive to that drift, the FDM can be safely waived.
Verify whether the dropped argument was column-driven. If the second argument was a column reference, evaluate whether the query actually exercised spheroid math at runtime. A column always-FALSE makes the difference moot; a column always-TRUE means every row needs the precision difference reviewed.
For overflowing values, project to text or split. If the data legitimately contains numbers wider than 38 digits, store and read those fields as strings (x::STRING) and only cast to NUMBER for the subset of values known to fit.

SSC-FDM-BQ0016¶

SESSION_USER returns an email address in BigQuery. Snowflake CURRENT_USER returns a username.

Description¶

BigQuery’s SESSION_USER returns the email address of the authenticated IAM principal that submitted the query (for example, alice@example.com for a user, or a service-account email for a service account). Snowflake’s closest equivalent is CURRENT_USER, which returns the Snowflake username of the active session, not an email address.

SnowConvert AI rewrites every SESSION_USER() call to CURRENT_USER() and emits this FDM to flag the format change. The rewrite preserves the intent of identifying the active principal, but downstream code that relies on the value being an email — string parsing for the @domain portion, joins against an email-keyed identity table, audit-log records that compare against IAM identities — will need to be reviewed.

Code Example¶

Input Code:¶

BigQuery¶

SELECT SESSION_USER() AS u;

INSERT INTO audit_log (action, who) VALUES ('LOGIN', SESSION_USER());

Generated Code:¶

Snowflake¶

SELECT
  --** SSC-FDM-BQ0016 - SESSION_USER RETURNS AN EMAIL ADDRESS IN BIGQUERY. SNOWFLAKE CURRENT_USER RETURNS A USERNAME. **
  CURRENT_USER() AS u;

INSERT INTO audit_log (action, who)
VALUES ('LOGIN',
--** SSC-FDM-BQ0016 - SESSION_USER RETURNS AN EMAIL ADDRESS IN BIGQUERY. SNOWFLAKE CURRENT_USER RETURNS A USERNAME. **
CURRENT_USER());

Recommendations¶

Audit identity comparisons and joins. Any predicate that compares the result of SESSION_USER() against a stored email column (for example, WHERE email = SESSION_USER()) will not match in Snowflake — the right side is now a username. Either rewrite the predicate to compare against the Snowflake username column, or maintain a username-to-email mapping table.
Review string parsing on the result. Code that extracts the domain portion (e.g., SUBSTR(SESSION_USER(), STRPOS(SESSION_USER(), '@') + 1)) becomes incorrect — Snowflake usernames do not contain @. Rework the logic against a column that holds the email, or remove the parsing if the username alone is sufficient.
Update audit and provenance records. If audit tables capture SESSION_USER() for compliance or traceability, document the format change in the table’s data dictionary and confirm with stakeholders that a Snowflake username satisfies the record-keeping requirement.