topic Re: Synapse Datawarehouse to Fabric Warehouse Data Load in Data Engineering

Synapse Datawarehouse to Fabric Warehouse Data Load

AnmolGan81 — Thu, 11 Sep 2025 06:15:15 GMT

We are trying to load data from synapse to fabric but getting one issue that is related to float data type that we have in target, during the load it is staging the data into one lake, and not able to insert into float due to conversion issues, orginal error as below:

ErrorCode=DWCopyCommandOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message='DataWarehouse' Copy Command operation failed with error 'Column '' of type 'FLOAT' is not compatible with external data type 'Parquet physical type: FIXED_LEN_BYTE_ARRAY, logical type: DECIMAL(22, 8)', please try with 'DECIMAL(22, 8)'. Underlying data description:

anyone faced any issues related to above, we are using fabric pipeline to load as one time but not able to do it.

Re: Synapse Datawarehouse to Fabric Warehouse Data Load

BalajiL — Thu, 11 Sep 2025 06:40:09 GMT

hi AnmolGan81

Float is being mapped as Decimal(22,8) in the staging parquet file. This cause the pipeline failure.

1. Can you change the target table schema datatype to Decimal(22,8) or Double and try it.

2. Cast datatype in your source query when writing to staging layer

SELECT CAST(my_float_column AS DECIMAL(22,8)) AS my_float_column

Re: Synapse Datawarehouse to Fabric Warehouse Data Load

AnmolGan81 — Thu, 11 Sep 2025 06:41:58 GMT

we tried with DECIMAL(22,8) its not working and at target will have to check if we can keep decimal(22,8)

Re: Synapse Datawarehouse to Fabric Warehouse Data Load

BalajiL — Thu, 11 Sep 2025 06:45:31 GMT

yes try to change the target to decimal(22,8).

when you are writing to the stage cast datatype to double.

SELECT CAST(my_float_column AS double) AS my_float_column

You can try either of the option.

Re: Synapse Datawarehouse to Fabric Warehouse Data Load

AnmolGan81 — Thu, 11 Sep 2025 07:30:05 GMT

DECIMAL(22,8) does not work for us in fabric we have to keep float only, are there any alternatives?

Re: Synapse Datawarehouse to Fabric Warehouse Data Load

V-yubandi-msft — Thu, 11 Sep 2025 10:41:18 GMT

Hi AnmolGan81 ,
Since your target schema requires FLOAT, the error you’re encountering is likely due to Parquet’s handling of decimal encoding during staging in OneLake. FLOAT values are being written as DECIMAL(22,8) with a FIXED_LEN_BYTE_ARRAY physical type in Parquet, leading to a mismatch during loading.

To resolve this, it’s recommended to cast your source column to DOUBLE before staging. This will ensure the Parquet file uses the DOUBLE physical type, which matches FLOAT in Fabric.

Thanks for your quick response BalajiL .

Regards,
Yugandhar_CST team.

Re: Synapse Datawarehouse to Fabric Warehouse Data Load

AnmolGan81 — Thu, 11 Sep 2025 10:46:44 GMT

we are not staging anything here, fabric automatically decides to use staging when loading data from synapse to warehouse, where can we specify it to use double please explain?

Re: Synapse Datawarehouse to Fabric Warehouse Data Load

BalajiL — Thu, 11 Sep 2025 11:12:34 GMT

In your source query cast to double and try

SELECT CAST(my_float_column AS double) AS my_float_column

Re: Synapse Datawarehouse to Fabric Warehouse Data Load

AnmolGan81 — Thu, 11 Sep 2025 12:15:32 GMT

double does not work for us we get incorrect sytanx error when passing it.

Re: Synapse Datawarehouse to Fabric Warehouse Data Load

AntoineW — Thu, 11 Sep 2025 12:32:04 GMT

Hello,

try this syntax : TRY_CAST(column_name AS DECIMAL(18,2) AS column_name

Re: Synapse Datawarehouse to Fabric Warehouse Data Load

V-yubandi-msft — Sat, 13 Sep 2025 10:42:56 GMT

Thanks for your response. Based on Microsoft documented behavior, this issue stems from how Fabric pipelines automatically stage data in OneLake using Parquet format when loading from Synapse to a Fabric Warehouse. During this process, FLOAT values are internally converted to DECIMAL(22,8) with a FIXED_LEN_BYTE_ARRAY, physical type, which causes a mismatch when inserting into a target column defined as FlOAT.
Reference:
Troubleshoot the Parquet format connector - Microsoft Fabric | Microsoft Learn

Could you please try the syntax AntoineW suggested and let me know how it behaves in your setup.

Re: Synapse Datawarehouse to Fabric Warehouse Data Load

AnmolGan81 — Mon, 15 Sep 2025 03:29:07 GMT

It wont work and most of the values are NULL for us, I think we need to load it from lakehouse to override the step of not using the staging and directly copy the parquet file with tabular mapping and enabling float datatypes.

Re: Synapse Datawarehouse to Fabric Warehouse Data Load

V-yubandi-msft — Tue, 16 Sep 2025 04:55:51 GMT

Hi AnmolGan81 ,
Thank you for sharing your findings. Loading data directly from the Lakehouse and bypassing the automatic staging step seems like a good approach, especially if tabular mapping preserves the FLOAT data type. Considering the staging limitations, this could serve as an effective workaround.

I look forward to hearing about your results after you try it.

Thank You.

Re: Synapse Datawarehouse to Fabric Warehouse Data Load

AnmolGan81 — Thu, 18 Sep 2025 04:44:42 GMT

we have an upcoming migration to other enviorment will let you know how it goes.

Re: Synapse Datawarehouse to Fabric Warehouse Data Load

V-yubandi-msft — Thu, 18 Sep 2025 05:22:18 GMT

Thank you for the update. I hope the migration to the new environment goes smoothly. When you've had a chance to test everything, please let me know how it works out.

Re: Synapse Datawarehouse to Fabric Warehouse Data Load

V-yubandi-msft — Tue, 23 Sep 2025 05:16:37 GMT

Could you please let us know the ETA by when you upcoming migration to other enviorment ? This will help us to know when we can follow up. If you encounter any challenges during implementing the provided solution, please inform us and we’ll be glad to assist.

Re: Synapse Datawarehouse to Fabric Warehouse Data Load

AnmolGan81 — Tue, 23 Sep 2025 05:52:07 GMT

Well we have tested this scenerio by creating tabular mapping and directly loading parquet file from data lake into the lakehouse using ADF and then moving that data into warehouse through stored procedure works; without any staging and it works, somehow staging in onelake does not allow us to move the data into warehouse.

This will be an actual solution for the problemetic data types, or someone who is directly looking to copy data to test fabric warehouse/lakehouse performance and test one time migration.

Re: Synapse Datawarehouse to Fabric Warehouse Data Load

V-yubandi-msft — Tue, 23 Sep 2025 07:16:09 GMT

Thanks for your workaround.