Copy activity convert NUMBER to DOUBLE format

Question

Copy activity convert NUMBER to DOUBLE format

Zhu, Yueli YZ [NC] 280

Hi

we use the ADF copy activity to copy data from an oracle source to azure gen2 container as parquet file. The source data contain data type NUMBER, but the copy activity converts it to DOUBLE format. Could you please provide a solution for this one?

Thanks,

Chandra Boorla 14,585 Reputation points Microsoft External Staff Moderator

2025-06-10T17:18:08.4733333+00:00

@Zhu, Yueli YZ [NC]

Just checking in to see, did the suggestions above help address your adaptive schema mapping and data type targeting needs in the ADF pipeline? Specifically, were you able to control how Oracle NUMBER columns are mapped to the desired data type in your Parquet output?

As your feedback is valuable and can assist others in the community facing similar issues.

If you have found a resolution to your issue, we would appreciate it if you could share it in the thread to benefit others.

1 answer

Your answer

Chandra Boorla 14,585 Reputation points Microsoft External Staff Moderator

2025-06-10T17:18:08.4733333+00:00

@Zhu, Yueli YZ [NC]

Just checking in to see, did the suggestions above help address your adaptive schema mapping and data type targeting needs in the ADF pipeline? Specifically, were you able to control how Oracle NUMBER columns are mapped to the desired data type in your Parquet output?

As your feedback is valuable and can assist others in the community facing similar issues.

If you have found a resolution to your issue, we would appreciate it if you could share it in the thread to benefit others.

Answer 1

Chandra Boorla 14,585 Microsoft External Staff Moderator

@Zhu, Yueli YZ [NC]

I see you're encountering an issue where the Azure Data Factory Copy Activity is converting Oracle NUMBER data types into DOUBLE when writing to Parquet files in Azure Data Lake Gen2.

By default, Azure Data Factory maps Oracle's NUMBER data type to DOUBLE when writing to Parquet format. This is because NUMBER in Oracle is a flexible type and ADF chooses the closest general-purpose match, which is DOUBLE. However, this can result in precision loss, especially for financial or high-precision numeric data.

User's image

For details, please refer: Data type mapping for Oracle

To preserve the exact precision and scale, here are a few suggestions to address this:

Explicit casting in source query

To avoid unwanted conversions, you can modify your source query to explicitly cast the NUMBER column to a more appropriate data type:

SELECT CAST(ColA AS NUMBER(18,2)) AS ColA FROM TableA

Alternatively, for scientific or floating-point values:

SELECT CAST(ColA AS BINARY_DOUBLE) AS ColA FROM TableA

Note - Choose precision (p) and scale (s) based on your data range. For example, NUMBER(18,2) supports up to 16 digits before the decimal and 2 digits after.

Review Data Type Mappings

Review the Oracle-to-Parquet data type mappings in ADF. A NUMBER without precision/scale may default to DOUBLE. Specifying precision/scale in your query helps ADF preserve the correct format in the Parquet sink.

Validate Output

After applying the cast, test your query output in ADF and check the resulting Parquet file to ensure values are stored correctly without loss of precision.

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Thank you.

Zhu, Yueli YZ [NC] 280 Reputation points

2025-06-10T20:12:49.3766667+00:00

It's a good solution. However, we have many oracle source datasets that contain NUMBER format. Is there any other way to solve this instead of reaching out to many DBAs for changes?
Chandra Boorla 14,585 Reputation points Microsoft External Staff Moderator

2025-06-10T20:33:21.5433333+00:00
@Zhu, Yueli YZ [NC]

That's a great point, having to reach out to multiple DBAs to modify source queries across many Oracle datasets can definitely be challenging and time-consuming.

Here are a few alternative approaches you might consider:

Use Mapping Data Flows for Centralized Type Conversion

Instead of relying on Copy Activities, you can use Mapping Data Flows in ADF. With Data Flows, you can:

Automatically read the schema from the Oracle source

Add a Derived Column step to cast all NUMBER columns to a desired DECIMAL(p,s) format

Output to Parquet with full control over data types

This approach allows you to handle type conversion within ADF itself, without requiring changes to the source queries.

Override Sink Types in the Copy Activity Mapping

If you prefer to stick with Copy Activities, you can go to the Mapping tab, import schemas, and override the sink column types from DOUBLE to DECIMAL(x,y). While this requires setup per dataset, it avoids changes to the Oracle side and can be automated using templates or parameterized pipelines.

Introduce a Staging Step (Optional)

You could also consider writing the Oracle data to an intermediate format like CSV and then use a Data Flow or Synapse/Spark Notebook to convert to Parquet with the correct data types. This gives you full control over the transformation layer.

I hope this information helps.
Chandra Boorla 14,585 Reputation points Microsoft External Staff Moderator

2025-06-11T17:17:40.98+00:00

@Zhu, Yueli YZ [NC]

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.

Share via

Copy activity convert NUMBER to DOUBLE format

1 answer

Your answer