SQL Rounding off to 4 digits, while ADF is not - Reconciliation issue

Question

Hello all,

I have a weird problem with rounding off. I am writing a SQL script to test/reconcile the outputs from Azure ADF flow.

• SQL Query: SELECT CAST(1708868.4400000000 * CAST((1.0 - ISNULL(0.0250000000, 0)) AS DECIMAL(38, 28)) AS DECIMAL(38, 28)) AS MyCalculationOutput;
• SQL Output 1666146.7290000000000000000000000000
• ADF

   toDecimal((MyField1 *(1.0-toDecimal(iifNull( MyField2,0),18,10))),22,10)

• ADF Result 1666146.7289999998

Could you please shower some light on this, as I am not able to get anywhere with it!

Thanks in advance..

Answer 1

When comparing results of decimal or float calculations in different environments, you should not expect exactly the same results, as different math libraries are used, and rules may be different.

I don't use ADF, but it is quite clear that ADF is not only using decimal calculation here. We have a two-decimal number multiplied with three-decimal number. This can yield at most five decimals, so ADF is smoking something. I would guess that MyField is handled as a 64-bit float. And indeed, if you try:

SELECT CAST( cast(1708868.4400000000 as FLOAT) * CAST((1.0 - ISNULL(0.0250000000, 0)) AS DECIMAL(38, 28)) AS DECIMAL(38, 28)) AS MyCalculationOutput;

You get something which more closely resembles what ADF produces.