SSC-FDM-0006
Number type column may not behave similarly in Snowflake
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):
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 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.If you need more support, you can email us at snowconvert-support@snowflake.com
Last updated