MSCEWI1066

Number type column may not behave similarly in Snowflake

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

Severity

Low

Description

This warning appears when a NUMBER Type column is being created within a Table. The reason for this is due to arithmetic differences when performing operations related to the scales of intermediate values in Snowflake which could make some operations fail. For more information please refer to Snowflake's post on intermediate numbers in Snowflake.

In order to avoid these arithmetic issues, you can run data samplings to verify the needed precision and scales for these operations.

Example Codes

Simple Table with Number Columns

Input Code (Oracle):

CREATE TABLE table1
(
column1 NUMBER,
column2 NUMBER (20, 4)
);

Output Code:

CREATE TABLE table1
(
column1 NUMBER (38, 18) /*** MSC-WARNING - MSCEWI1066 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE ***/,
column2 NUMBER (20, 4) /*** MSC-WARNING - MSCEWI1066 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE ***/
);

Arithmetic Issue Examples

The next examples show how the arithmetic issues can happen when using Number columns:

Snowflake Code with Division Error:

CREATE OR REPLACE TABLE number_table( column1 NUMBER(38, 19) );

INSERT INTO number_table VALUES (1);

SELECT column1 / column1 FROM number_table;

Snowflake Code with Multiplication Error:

CREATE OR REPLACE TABLE number_table( column1 NUMBER(38, 20) );

INSERT INTO number_table VALUES (1);

SELECT column1 * column1 FROM number_table;

When running either SELECT statements Snowflake will return an error:

Number out of representable range: type FIXEDSB16{nullable}, value 1.0000000000000000000

This is due to the intermediate operation's result overflowing Snowflake's maximum capacity; reducing the number scales by 1 on each example will fix the error and work normally:

Snowflake Code with Division:

CREATE OR REPLACE TABLE number_table( column1 NUMBER(38, 18) );

INSERT INTO number_table VALUES (1);

SELECT column1 / column1 FROM number_table;

Snowflake Code with Multiplication:

CREATE OR REPLACE TABLE numbertable( column1 NUMBER(38, 19) );

INSERT INTO number_table VALUES (1);

SELECT column1 * column1 FROM number_table;

For this reason, SnowConvert sets the default scale of Numbers to 18, minimizing the number of errors when migrating.

Recommendations

• Verify that your operations' intermediate values don't exceed a scale of 37, as that is Snowflake's maximum.

• Run Data Samplings on your data, to make sure you have the required precision and scales before running any operations.

• In most cases, after doing some data sampling or discussing with the business you might come to the conclusion that the precision can be different. For example, for MONEY columns a typical precision is NUMBER(20,4). In snowflake you cannot easily alter a column data type, you can check this post on our forum which provides some guidance on how to alter your columns data types and preserve your data.

• If you need more support, you can email us at [e-mail]