SSC-FDM-0006

Number type column may not behave similarly in Snowflake

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Description

This functional difference message 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 and Number out of representable range.

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):

IN -> Oracle_01.sql

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

Output Code:

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE table1
(
column1 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
column2 NUMBER(20, 4) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

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 [email protected]