Metadata ODate endpoint

[BastiaanKr]

In my pipeline I added a copy data activity with a OData endpoint as source and a Fabric datawarehouse as the destination without using the mapping functionality. I don't use the mapping because I want to use the copy data activity in a for each loop to get data from multiple endpoints dynamically.

When I run the pipeline with a single table or multiple tables the tables are succesfully created in the Fabric datawarehouse. The problem is that there is no functionality to suppress the OData metadata. The result is that every destination table  has two extra columns with the OData url of the endpoint and every column header starts with the value value. It's no option to use an extra header Accept - odata.metadata=none because the use of this header is not allowed when you use the REST source connection.

Does anyone have a solution for this?

[v-nikhilan-msft · Accepted solution]

Hi @BastiaanKr 
Thanks for using Fabric Community.
Currently, as you’ve mentioned, Fabric does not support the use of the Accept: application/json;odata.metadata=none header in the REST source connection. 
Dataflow Gen2 can potentially be used in your scenario to achieve the desired data shaping without the extra OData metadata columns.

1. Utilize Dataflow Gen2 with Staging Storage:

• Instead of directly writing to the Fabric data warehouse, use a Dataflow Gen2 for transforming the data.
• Within the Dataflow, employ transformations to exclude the desired columns. 
• Rename the desired columns according to your preference.
• For more information on how to transform the data please refer to these links:

https://learn.microsoft.com/en-us/fabric/data-factory/tutorial-end-to-end-dataflow
https://learn.microsoft.com/en-us/fabric/data-factory/transform-data
https://www.youtube.com/watch?v=ZrDXzLQG7nI

2. Load Transformed Data to Warehouse:

• Use a separate Copy Data activity to load the transformed data from the output of the Dataflow Gen2 to the Fabric data warehouse.
• You can make use of the dataflow activity in pipeline to call the desired Dataflow Gen2.
  https://learn.microsoft.com/en-us/fabric/data-factory/tutorial-dataflows-gen2-pipeline-activity
  
• This ensures a clean, transformed dataset reaches the final destination.

Hope this helps. Please let me know if you have any further questions.

[v-nikhilan-msft]

Hi @BastiaanKr 
We haven’t heard from you on the last response and was just checking back to see if you got some insights regarding the query. Otherwise, will respond back with the more details and we will try to help.
Thanks

[v-nikhilan-msft]

Hi @BastiaanKr 
We haven’t heard from you on the last response and was just checking back to see if you got some insights regarding the query. Otherwise, will respond back with the more details and we will try to help.
Thanks

[BastiaanKr]

HI @v-nikhilan-msft,

I am aware that you can also use a dataflow gen2 for the ingest. I have tested this and the advantage is that I have no issues with the Odata metadata.

The disadvantage for me is that I have to manually set all column types correctly. This in itself does not have to be a problem if you want to do both the ingest and the transformation in one step.

[v-nikhilan-msft]

Hi @BastiaanKr 
Thanks for the reply. Unfortunately Fabric does not support the use of the Accept: application/json;odata.metadata=none header in the REST source connection. So the only way is using dataflow gen2.
Hope this helps.