MSC-BQ0009

Data type precision and scale changed to the max allowed in Snowflake.

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Medium

Description

This error is added when a numeric data type has a higher precision or scale than allowed in Snowflake.

Code Example

BigQuery:

Input.sql

CREATE TABLE table1(
  COL1 BIGNUMERIC(42,38)
  COL2 BIGDECIMAL(76,38)
);

Snowflake:

Output.sql

CREATE TABLE table1(
  COL1 NUMERIC(38, 37) /*** MSC-ERROR - MSC-BQ0009 - BIGNUMERIC DATA TYPE CONVERTED TO NUMERIC. DATA TYPE PRECISION AND SCALE CHANGED TO THE MAX ALLOWED IN SNOWFLAKE. ***/
  COL2 DECIMAL(38, 37) /*** MSC-ERROR - MSC-BQ0009 - BIGDECIMAL DATA TYPE CONVERTED TO DECIMAL. DATA TYPE PRECISION AND SCALE CHANGED TO THE MAX ALLOWED IN SNOWFLAKE. ***/
);

Recommendations

• Review your named window definitions, it might be possible to take the definition and apply it to the OVER clause of the functions it is used in. However, keep in mind the functional differences between BigQuery and Snowflake window frames still apply, take the following case as an example:

BigQuery:

SELECT SUM(col1) OVER(myWindow) FROM test.exampleTable WINDOW myWindow AS (ORDER BY col2);

Snowflake:

SELECT SUM(col1) OVER(ORDER BY col2) FROM exampleTable;

These two queries will produce the same rows but the Snowflake results will not be ordered, this is because the ORDER BY clause for window frames does not impact the entire query ordering as it does in BigQuery.

Recommendations

• If you need more support, you can email us at [email protected]