SSC-FDM-0006

Number type column may not behave similarly in Snowflake

circle-info

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 Documentationarrow-up-right

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

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 Snowflakearrow-up-right and Number out of representable rangearrow-up-right.

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:

Arithmetic Issue Examples

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

Snowflake Code with Division Error:

Snowflake Code with Multiplication Error:

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:

Snowflake Code with Multiplication:

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 forumarrow-up-right 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]envelope

Last updated