Precision loss when migrating data from SQL Server to Snowflake using ADF via CSV staging (but not with Direct Copy) [closed]

The displayed SQL side approximation to pi of 3.141593 is very suspicious. It is most definitely not the best float(24) 32bit approximation to pi.

It looks to me as if the first method is actually corrupting the data in a way that may be a convention in the SQL world by only transferring float(32) values as text rounded to seven places of decimals. Forcing it to round to 8 decimal digits would help a lot.

This is actually the key to the problem. The values in the left hand column are rounded and shown to only 7 decimal digits of text - this is insufficient to accurately represent every possible float(24) in a form that is exactly reproducible when the string is converted back to a float. To be sure of exactly what is going on you would need to display the various float(32) representations in hexadecimal form.

Roughly 1 in 8 of the 2^23 possible FP values in a given power of two range will be corrupted by a round trip to a 7 decimal digit string representation and back.

ISTR that a minimum of 8 decimal digits is essential to preserve most FP32 values reliably and that to catch a handful of awkward edge cases in the full range of FP numbers 9 or even 10 decimal digits are required. There is a website with a few samples of awkward to convert float string constant values somewhere but the URL eludes me at the moment.

If you really care about preserving exact bitwise representation then transfer the values using the hybrid hexadecimal format %a in C language format specifications. That truly is lossless and unambiguous binary bit for bit transfer in text of valid IEEE754 numbers.

This is a toy C program to take all the values between 1.0 and 2.0 through the round trip with various levels of rounding 1 through 8 decimal digits together with the number of good and bad results. Bad meaning that the original floating point value was corrupted in the process.

#include <iostream>

// this code takes values from 1.0 to 1.9999995 on a round trip through string representation
// and counts the number of values that remain exact at each format specification

void toy(const char *format)
{
    int bad, good;
    char string[20];
    char* endptr;
    double pi = 3.1415926535897932384626433832795;
    float x, y = 1.0;
    float dy = 0.6e-7;
    good = bad = 0;
    for (int i = 0; i < 1 << 23; i++)
    {
        if (!(i & 0xfffff)) printf("."); // to show something is happening
        sprintf_s(string, format, y);
        x = strtof(string, &endptr);
        if (x == y)
            good++;
        else
            bad++;
        y += dy;
    }
    printf("format %s good %7i bad %7i pi  = ", format, good, bad);
    printf(format, pi);
    printf("\n");
}

int main() {
    toy("%15.1g");
    toy("%15.2g");
    toy("%15.3g");
    toy("%15.4g");
    toy("%15.5g");
    toy("%15.6g");
    toy("%15.7g");
    toy("%15.8g");
    return 0;
}

Here is the output:

........format %15.1g good       1 bad 8388607 pi  =               3
........format %15.2g good      10 bad 8388598 pi  =             3.1
........format %15.3g good     100 bad 8388508 pi  =            3.14
........format %15.4g good    1000 bad 8387608 pi  =           3.142
........format %15.5g good   10000 bad 8378608 pi  =          3.1416
........format %15.6g good  100000 bad 8288608 pi  =         3.14159
........format %15.7g good 1000000 bad 7388608 pi  =        3.141593
........format %15.8g good 8388608 bad       0 pi  =       3.1415927

This post is arguably a disguised dupe of Is floating point math broken although in this instance it is the conversion to and from decimal string representation that is causing the problem. IEEE754 floating point numbers can be unforgiving - particularly the 32bit float or float(24) as you prefer to call them. It is far too easy to introduce rounding error or have overflows with such a restrictive mantissa and exponent.

Stage copy appears to be doing everything right with enough decimal digits to accurately represent all of the floating point values that can occur (ignoring just a handful of exceptions that require a brute force search or a specialist FP URL to find).

In every case the "wrong" answer it has transferred is a valid and unambiguous float(24) value.