In general, the FLOAT64 data type provides standard 8 byte IEEE-754 behavior. So, many fractional values are not represented perfectly; there is some very small amount of rounding error. And a sum over rows can yield slightly incorrect and seemingly non-deterministic results. The result can seem to be non-deterministic because the exact order in which numbers are added can change the final result. When summing over billions of records where a FLOAT64 has been used to store money amounts, you are not guaranteed to get an answer that is accurate to the penny.

The NUMERIC data type was made generally available on August 20, 2018. This data type uses 16 bytes and provides exact representations of numbers with 38 digits of precision and 9 decimal digits of scale. So, the numbers and sums over the numbers within these limits are exactly correct.

The query below demonstrates the problem with using the FLOAT64 data type. sum_1 stores a sum of 0.1 and -0.1, each contributing to the sum ten times in a particular order (alternating 0.1 and -0.1). sum_2 stores a sum of 0.1 and -0.1, each contributing to the sum ten times in a different order (first 0.1 ten times and then -0.1 ten times). If the numbers were represented perfectly, then both sum_1 and sum_2 would yield exactly zero. However, using the FLOAT64 data type, only sum_1 yields exactly zero. Note that there appears to be a bug in the FORMAT function. The actual value of sum_2 is approximately 2.7755575615628914e-17.

with
sum_1 as
(
    select
        0.1 - 0.1
            + 0.1 - 0.1
            + 0.1 - 0.1
            + 0.1 - 0.1
            + 0.1 - 0.1
            + 0.1 - 0.1
            + 0.1 - 0.1
            + 0.1 - 0.1
            + 0.1 - 0.1
            + 0.1 - 0.1
            as value
)
,sum_2 as
(
    select
        0.1 + 0.1 + 0.1 + 0.1 + 0.1 + 0.1 + 0.1 + 0.1 + 0.1 + 0.1
            - 0.1 - 0.1 - 0.1 - 0.1 - 0.1 - 0.1 - 0.1 - 0.1 - 0.1 - 0.1
            as value
)
select
    format(
        '%32.31f'
        ,sum_1.value
    ) as sum_1_value
    ,sum_1.value = 0 as sum_1_eq_zero
    ,sum_1.value < 0.0001 as sum_1_lt_0_0001
    ,format(
        '%32.31f'
        ,sum_2.value
    ) as sum_2_value
    ,sum_2.value = 0 as sum_2_eq_zero
    ,sum_2.value < 0.0001 as sum_2_lt_0_0001
from sum_1
cross join sum_2
;

Below are some links with additional information about this issue.