Comment on page
MSCEWI1066
Number type column may not behave similarly in Snowflake
Low
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.
CREATE TABLE table1
(
column1 NUMBER,
column2 NUMBER (20, 4)
);
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 ***/
);
The next examples show how the arithmetic issues can happen when using Number columns:
CREATE OR REPLACE TABLE number_table( column1 NUMBER(38, 19) );
INSERT INTO number_table VALUES (1);
SELECT column1 / column1 FROM number_table;
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.0000000000000000000This 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:
CREATE OR REPLACE TABLE number_table( column1 NUMBER(38, 18) );
INSERT INTO number_table VALUES (1);
SELECT column1 / column1 FROM number_table;
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.
- 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
MONEYcolumns a typical precision isNUMBER(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.