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

Mithu - The Data Engineer 20 Reputation points
2023-03-04T08:46:28.1433333+00:00

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..

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,458 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 100.8K Reputation points MVP
    2023-03-04T19:46:15.2266667+00:00

    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.


0 additional answers

Sort by: Most helpful