Fabric copy activity fails when JSON source is missing attributes

[Ashutosh_A]

I have created a Fabric Copy activity that gets data from a REST API connection. I was able to apply the "Collection Reference" as $['results'] node of the JSON response and am writing the inner structs to different fields of a table. 

The copy activity fails when one of the structs is missing from any API response, and I would like to define a strict mapping that uses NULL or default value for any struct or attribute that is missing from the source instead of failing with an error.

Can you please help with a better solution to improve the fault tolerance here?

[Lozovskyi]

Hello @Ashutosh_A, yes, it's quite a common issue as schema inference fails.

What I can advise (and actually do myself), dump these data as JSON files into a file section and then process them using PySpark. df = spark.read.format("json").load(path)

https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.DataFrameReade...

Then Spark will help you to infer the maximum known schema for the JSONs, and you are free to either transform or store it as a struct.

In my experience, it's a bulletproof scenario. As well, it might be cheaper and faster if you dump data using Python in a Jupyter notebook.

[v-hjannapu · Accepted solution]

Hi @Ashutosh_A,

Thank you  for reaching out to the Microsoft fabric community forum.

It looks like the Copy activity is expecting all the mapped fields to be present in the API response. If any struct is missing in a particular response, the activity fails because the defined mapping does not find that field in the JSON.

Copy activity works well when the schema is consistent, but it can fail when nested fields are optional or missing in some responses. In this case, using a Notebook or Dataflow Gen2 may help, since you can handle the JSON more flexibly and set NULL or default values when fields are not present.

This way the pipeline will not fail even if some attributes are missing in the API response. Let us know if you need any more details.

Kindly refer to the below documentation links for better understanding:
Configure REST in a copy activity - Microsoft Fabric | Microsoft Learn

Hope the above provided information help you resolve the issue, if you have any further concerns or queries, please feel free to reach out to us.
Regards,
Community Support Team.

[v-hjannapu]

Hi @Ashutosh_A,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

Regards,
Community Support Team.

[v-hjannapu]

Hi @Ashutosh_A,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

Regards,
Community Support Team.

[spaceman127]

Hi @Ashutosh_A,

i can only agree @tayloramy. A notebook or Dataflow Gen2 is here a good way to fix your issues.

Myself i use notebooks for many projects. Notebooks are very flexible and in my opionion simple to create for you problem. Copy activity should be only for simple can be used.

Best regards

Feel free to leave kudos or accept it as a solution. This will also help other community members.

[tayloramy]

Hi @Ashutosh_A, 

The best way to do this would be to use a notebook instead of a copy activity, then you can write code to handle missing fields however you like. 

If you need a low/no code solution, you might have more luck with a dataflow gen 2.