4

Recently I migrated data from netezza to Snowflake. The problem I am facing is with double precision data type. In netezza DB double precision field has value of .34757853258953 but when I query same in Snowflake UI I see it as .3475785326.

Anyone has any idea why so and how to deal with it.

rjain550
  • 105
  • 2
  • 8

1 Answers1

4

This is a UI limitation, not a Snowflake limitation. The value stored is correct, but the UI display only shows 9 digits past the decimal point. You can verify like this:

SELECT  0.34757853258953::DOUBLE, 0.34757853258953::DOUBLE * 100000;
Mike Walton
  • 6,595
  • 2
  • 11
  • 22
  • 1
    If you want exact values, you shouldn't be using float or double to store data since these data types are "approximate representations". That said, you could always convert to a fixed-decimal number like sale::number(38,14) to see all of decimals of that approximation beyond 9 digits. – Mike Walton Aug 23 '20 at 15:22
  • Mike I tried with sale:: number (38,20) and I can see some extra value after decimal even though source had only 13 digit post decimal but in Snowflake I can see 20 digit post value. Looks like Snowflake handle double precision and float in different way compare to other DB – rjain550 Aug 23 '20 at 18:54
  • 2
    Nope - same as others. Float isn't an exact number on any database out there, so you're seeing more information about the approximation that comes with float or double values. If you want your data to be preserved as exact numbers, you shouldn't be using float or double. – Mike Walton Aug 23 '20 at 18:56
  • I will tell you my secnario. In netezza when I query sale column I see value after decimal till 12 digit but in Snowflake when I query same data like sale::number(38,20) I can see value after decimal till 20 digit. This is driving me crazy during data certification. Just to add column is double precision in Snowflake and netezza – rjain550 Aug 23 '20 at 19:08
  • 2
    As Mike said, floating point (double precision) numbers are an _approximate value_, usually used to represent very large numbers. If your Netezza system is using the "double precision" type to represent anything to do with a "sale" then I suggest it is using the wrong data type for this purpose. You cannot migrate floating point numbers exactly since they are not exact numbers to begin with. – Nathan Griffiths Aug 23 '20 at 20:44
  • Thanks Mike & Nathan. There is no issue but the UI created confusion by displaying 9 digit post decimal – rjain550 Aug 24 '20 at 08:53