Truncated digits for numerical values in Azure Data Factory pipelines

Question

Truncated digits for numerical values in Azure Data Factory pipelines

Stanislav Slovik - Administrator 20

Hello,
when I am receiving numerical values within json response from API endpoint, Azure Data Factory is applying scientific notations into numerical values. But in the same time, it's also truncating last digits, see the following example:
*Value exposed from API endpoint as part of json response:
0.000016268687991399651977414

But ADF will interpret is as the following:
1.626868799139965E-5*

So, we are able to format it into full decimal number properly, but last digits ( 1977414 ) are truncated, because value 1.626868799139965E-5 does not contain them anymore.

Now, I've been reading couple of articles, how to deal with this kind of issue:

Changing API endpoint to return those values as strings (unfortunately, not possible for us)
Using Data flows in ADF pipelines (might be an option, but it overcomplicates whole pipeline and is adding unnecessary complexity)

It's actually fine for us to have scientific notations within numbers, but could ADF still keep all the numbers. Are there some settings in ADF for length of numerical values:
not having only 1.626868799139965E-5
but rather 1.6268687991399651977414E-5

thanks in advance for your suggestions

J N S S Kasyap 3,455 Reputation points Microsoft External Staff Moderator

2025-05-27T11:02:29.7666667+00:00
Hi @Stanislav Slovik - Administrator

Azure Data Factory by default uses double precision floating point format (IEEE 754) when handling numeric values in JSON payloads. This means numbers are automatically interpreted using scientific notation.ADF uses JsonTextReader from .NET, which automatically converts numeric values in JSON to double (System.Double), double cannot hold more than ~17 significant digits the remaining digits are irreversibly lost during parsing.

Recommended options:

If the API wraps the numbers in quotes ("0.000016268687991399651977414"), ADF will treat them as strings, and you can preserve full precision.

Using Mapping Data Flows with a Derived Column transformation in Azure Data Factory can help preserve full numeric precision by converting values from strings or restructuring the data before truncation occurs. This approach adds some complexity and performance overhead but is effective only if the numerical values are read as strings initially since once a number is parsed as a double, any extra precision is already lost and cannot be recovered.

To help with effective suggestions please help me with below questions

What specific data types are you using in your ADF Pipeline for these numerical values?

Are there any transformations applied to the data before it's returned from the API?

What is your current ADF configuration for handling numerical values?

Could you provide more context on the schema of the data you're receiving from the API?

I hope this info helps, please provide the answers I am happy help further.
J N S S Kasyap 3,455 Reputation points Microsoft External Staff Moderator

2025-05-28T08:30:32.6466667+00:00

@Stanislav Slovik - Administrator
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Dileep Raj Narayan Thumula 255 Reputation points Microsoft External Staff Moderator

2025-05-29T12:09:27.8766667+00:00

When using the decimal function, a value of 0 or 0.0 is automatically converted to an exponential format. One workaround is to convert the decimal value to a string instead.

For example, the following expression ensures that a zero value is represented correctly:

case(Value == 0.0, toString(0.0),toString(toDecimal(Value,20,10)))
Dileep Raj Narayan Thumula 255 Reputation points Microsoft External Staff Moderator

2025-05-30T11:03:01.9866667+00:00

Hello @Stanislav Slovik - Administrator,We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Stanislav Slovik - Administrator 20 Reputation points

2025-06-02T08:56:26.7166667+00:00
hi @J N S S Kasyap , @Dileep Raj Narayan Thumula , thanks for your suggestions and comments.

Regarding your recommended options:

API endpoint wrapping numbers in quotes to treat them as strings
-> as was mentioned in my initial question, this options is currently not available for us (cannot change data types within response from API endpoint)

Using Mapping Data Flows
-> "is effective only if the numerical values are read as strings initially" means, that if our Web activity reads them as number (double), even if next step is mapping data flow, it will already use truncated number as input, right ?
-> is it possible to make http request call directly within data flow (kind of "web activity" step) to avoid conversion into double ? I found that maybe through Azure function, but that is overcomplicating everything too much...

And your questions:
I. What specific data types are you using in your ADF Pipeline for these numerical values?
-> we don't use any specific data types, one step is "web activity" (calling API endpoint) and second step is "lookup activity" (where we use output from web activity, which is json response with double numbers as part of SQL query to insert data directly into SQL table)

II. Are there any transformations applied to the data before it's returned from the API?
-> no, web activity does not offer some transformations (AFAIK) for output data
-> but it's related to 2nd point mentioned above, whether it's possible to make http request call directly from Data flow and if within data flow can be then secured full number?

III. What is your current ADF configuration for handling numerical values?
-> for most of our pipelines is default double precision (coming from ADF) sufficient, but int this use case we are dealing with exchange rate numbers, where longer numerical precision (more than 17 significant digits) is preferred

IV. Could you provide more context on the schema of the data you're receiving from the API?
-> numerical values are returned as numbers from API endpoint and changing API endpoint response to expose them as strings is currently not an option

thanks

Accepted answer

1 additional answer

Your answer

J N S S Kasyap 3,455 Reputation points Microsoft External Staff Moderator

2025-05-27T11:02:29.7666667+00:00

Hi @Stanislav Slovik - Administrator

Azure Data Factory by default uses double precision floating point format (IEEE 754) when handling numeric values in JSON payloads. This means numbers are automatically interpreted using scientific notation.ADF uses JsonTextReader from .NET, which automatically converts numeric values in JSON to double (System.Double), double cannot hold more than ~17 significant digits the remaining digits are irreversibly lost during parsing.

Recommended options:

If the API wraps the numbers in quotes ("0.000016268687991399651977414"), ADF will treat them as strings, and you can preserve full precision.

Using Mapping Data Flows with a Derived Column transformation in Azure Data Factory can help preserve full numeric precision by converting values from strings or restructuring the data before truncation occurs. This approach adds some complexity and performance overhead but is effective only if the numerical values are read as strings initially since once a number is parsed as a double, any extra precision is already lost and cannot be recovered.

To help with effective suggestions please help me with below questions

What specific data types are you using in your ADF Pipeline for these numerical values?

Are there any transformations applied to the data before it's returned from the API?

What is your current ADF configuration for handling numerical values?

Could you provide more context on the schema of the data you're receiving from the API?

I hope this info helps, please provide the answers I am happy help further.
J N S S Kasyap 3,455 Reputation points Microsoft External Staff Moderator

2025-05-28T08:30:32.6466667+00:00

@Stanislav Slovik - Administrator
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Dileep Raj Narayan Thumula 255 Reputation points Microsoft External Staff Moderator

2025-05-29T12:09:27.8766667+00:00

When using the decimal function, a value of 0 or 0.0 is automatically converted to an exponential format. One workaround is to convert the decimal value to a string instead.

For example, the following expression ensures that a zero value is represented correctly:

case(Value == 0.0, toString(0.0),toString(toDecimal(Value,20,10)))
Dileep Raj Narayan Thumula 255 Reputation points Microsoft External Staff Moderator

2025-05-30T11:03:01.9866667+00:00

Hello @Stanislav Slovik - Administrator,We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Answer 1

Dileep Raj Narayan Thumula 255 Microsoft External Staff Moderator

Hello @Stanislav Slovik - Administrator
My observations are
This behavior is a limitation of the Copy Activity. To preserve the full precision of numeric values from the REST API, you can use a Mapping Data Flow instead. It retains the original value from the source without expanding or truncating the decimal, as shown below in the source data.

Reference: Value mismatch using copy data activity in ADF

User's image

Based on this, if the decimal values have a precision greater than 28 (BigDecimals), you should convert the column's data type to a string using the toString(<ColumnName>) expression in a Derived Column transformation, as demonstrated below:

User's image

Dileep Raj Narayan Thumula 255 Reputation points Microsoft External Staff Moderator

2025-06-04T04:07:17.2066667+00:00

Hello @Stanislav Slovik - Administrator,We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Stanislav Slovik - Administrator 20 Reputation points

2025-06-10T07:18:51.17+00:00

So, Data Flow is currently the only activity type in Azure Data Factory, where numerical precision coming from API endpoint might be preserved, when column type is converted to string. It's also important to mention, that Data Flow is supported only for Azure runtime, not for Self Hosted Integration runtime. Meaning, that if you have some internal resources (API endpoints), which you would like to access through Data Flow, then you need to use Azure runtime (Managed Virtual Network subtype) and configure VNet, subnet and Private Endpoint.

Answer 2

Stanislav Slovik - Administrator 20

hi @Dileep Raj Narayan Thumula , thanks for your suggested solution.
So, Data Flow is currently the only activity type in Azure Data Factory, where numerical precision coming from API endpoint might be preserved, when column type is converted to string.
It's also important to mention, that Data Flow is supported only for Azure runtime, not for Self Hosted Integration runtime.
Meaning, that if you have some internal resources (API endpoints), which you would like to access through Data Flow, then you need to use Azure runtime (Managed Virtual Network subtype) and configure VNet, subnet and Private Endpoint.
Please confirm, thanks

Dileep Raj Narayan Thumula 255 Reputation points Microsoft External Staff Moderator

2025-06-04T11:53:17.4966667+00:00

Hello @ Stanislav Slovik - Administrator •,Yes, Data Flow is the only native ADF activity that preserves numerical precision But it does require Azure IR with VNet support to access private/internal APIs
Dileep Raj Narayan Thumula 255 Reputation points Microsoft External Staff Moderator

2025-06-05T11:17:47.5366667+00:00

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Dileep Raj Narayan Thumula 255 Reputation points Microsoft External Staff Moderator

2025-06-06T10:56:51.15+00:00

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Stanislav Slovik - Administrator 20 Reputation points

2025-06-10T07:20:39.24+00:00

hi @Dileep Raj Narayan Thumula , thanks for all the clarifications.
I've marked as "accepted answer" your response from June 3rd 2025, but also added in its comments, that internal resources have to be handled with additional configuration within Data Flows.
You can therefore consider this question answers and mark it as completed, thx

Share via

Truncated digits for numerical values in Azure Data Factory pipelines

1 additional answer

Your answer