Error ingesting json files in warehouse table

[smeetsh]

Hi All,

We have a pipeline that uses an API to get data from one of our suppliers. It will create a number of json files,  which we then ingest into a warehouse table so we can ETL, join, upsert etc ..all the fun stuff 🙂

As of this morning 5.30 am new zealand time, we are getting the below error. We have not made any changes and the array it is pointing at (or seems to be pointing at) has had NULL there in the past, for as far as i can check.

ErrorCode=UserErrorWriteFailedFileOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The file operation is failed, upload file failed at path: '9e9fce10-9b68-486f-8d48-b77f907bba71/_system/services/DI/pipelines/a69bad01-bb02-46a4-8b26-f369e5bfe237/MSSQLImportCommand'.,Source=mscorlib,''Type=System.InvalidOperationException,Message=Not able to get enumerator for non-array. Path: databody.daysQuality.testResults,Source=Microsoft.DataTransfer.Common,'

 we are not sure what is going on and how we can fix this, is there a Fabric issue perhaps? Wer have found one setting in the activity that ingests the json files into the warehouse table, "skip incompatible rows", I am hesitant to tick that, since i dont know what the overall effect is and the json consists of multiple (nested at times) arrays

Below a sample of part of the json, with potentially the cause of the error, and one that does not have the NULL, but as i said it has never been a problem in the past, and this has been stable for about 1,5 years now and rund 3 times a day, 7 days a week.
With NULL:

Without NULL

We are stumped, any help would be greatly appreciated.
Cheers

Hans

[smeetsh]

Good afternoon @v-kpoloju-msft It has not been resolved yet. I am still working through the notebook. The suppliers API is not helping me due to the complexity of nested json libraries within libraries 🤣 .. I do get a little bit further everyday though.  

I did get feedback from Microsoft though, which make me feel that this may be a bug. The response last Monday was :

"I’ve reproduced the issue on my end and confirmed that it appears to be related to the Lakehouse. We’ve already opened a collaboration ticket with the relevant team to investigate further.

We truly appreciate your patience and understanding while we work through this. Please allow us a little time, and we’ll keep you updated as soon as we have more information."

For now we are handling this as a two pronged approach
1: Trying to get the notebook to work, once I have a working notebook I will post sample code here
2: Waiting (and chasing MS) to see if this is a bug or caused by an update.

[tayloramy]

Hi @smeetsh, 

Let us know if you need any assistance. 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution. 

[v-kpoloju-msft]

Hi @smeetsh,

Thank you for coming back with the update, really appreciate you sharing both your progress and the information from Microsoft. It is good to hear that the issue has been reproduced on Microsoft’s side, and a ticket has already been raised with the product team.

In the meantime, while you are working through the notebook, you might find these resources useful for handling nested JSON structures in Fabric.

1. What is a lakehouse? - Microsoft Fabric | Microsoft Learn
2. JSON file - Azure Databricks | Microsoft Learn

These cover approaches to flattening or parsing nested JSON objects, which could help in building your notebook logic.

It is great that you are tackling this from both directions experimenting with a working notebook while also waiting for confirmation on the potential bug.

Hope this helps you move forward. Please give it a try and let us know how it goes.
Thanks for using the Microsoft Fabric Community Forum and keeping us updated.

[v-kpoloju-msft]

Hi @smeetsh,

Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.

Thank you.

[v-kpoloju-msft]

Hi @smeetsh,

Hope you had a chance to try out the solution shared earlier. Let us know if anything needs further clarification or if there's an update from your side always here to help.

Thank you.

[smeetsh]

Thanks for the suggestion, I'll try it out. Question though. I have found json files from 6 months ago, which also had this issue, and it didnt cause a problem. Has the way json is handled in a copy activity changed in the last update?

The error has slightly changed: it now says "databody.daysQuality[14].testResults", in stead of 

"Path: databody.daysQuality.testResults"  (there is a [14] added to it now, is that an idication of a specific record)

ErrorCode=UserErrorWriteFailedFileOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The file operation is failed, upload file failed at path: '9e9fce10-9b68-486f-8d48-b77f907bba71/_system/services/DI/pipelines/624f7dd3-e46f-4c8a-9c22-64f824c713f0/MSSQLImportCommand/33172.parquet'.,Source=Microsoft.DataTransfer.Common,''Type=System.ArgumentException,Message=Node is not JArray. path: databody.daysQuality[14].testResults, type Null,Source=Microsoft.DataTransfer.Common,'

[tayloramy]

Hi @smeetsh, 

I'm not sure if there wa sa recent service change, but it is possible. 

databody.daysQuality[14].testResults means it is the testResults of the 14th record in daysQuality.

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, please mark this as the solution.

[tayloramy]

Hi @smeetsh, 

The error "Not able to get enumerator for non-array. Path: databody.daysQuality.testResults" usually means the mapping expects an array at that JSON path, but at least one incoming file has a null, a single object, or some other non-array value there. In other words, the source shape changed and the Copy activity can no longer iterate it (inference based on how Fabric parses hierarchical JSON).

I'd suggest trying to flatten the column and treat it as a string, that way it will load nicely regardless if it is null or not.

• Open the Copy activity > Mappings > expand Column flatten settings and, just for the problematic column, enable Treat array as string or Treat struct as string. This bypasses enumeration and lands the raw value as text that you can parse downstream. See the official mapping options for arrays/structs in Fabric’s Copy activity. 

For the skip incompatible rows setting, this is for row-level incompatibilities between source rows and the sink schema (type mismatches, PK violations, etc.). It can keep a load going and log skipped rows, but it won’t fix a fundamental JSON shape mismatch that throws earlier during parsing. Use it only if you can safely ignore some bad rows and review the session log. 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, please mark this as the solution.

[smeetsh]

I want to get back to this: " In other words, the source shape changed and the Copy activity can no longer iterate it (inference based on how Fabric parses hierarchical JSON)."

I have gone back and can say that

A: The source has not changed, there have always been arrays as null depening on the part of the month/week we were in and

B: We are now seeing this with two very different API's from different vendors

[tayloramy]

@smeetsh 
By two different APIs, do you mean that Fabric and another platform is experiencing this, or that you're eperiencing this from two different sources in Fabric?

I do a lot of work with JSON data and I've not experienced this, but I've always flattened out my JSON as a string when loading it into staging, and then I re JSON-ify it in my notebooks while doing transformations to handle any schema shift. 

Are you able to try this approach? 
If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, please mark this as the solution.

[smeetsh]

Hi @tayloramy With two different API's I mean using Fabric we address  multiple different API's , 2 of those are now showing this issue. I have gone back to the start of this year since we had some json older files and they regulalry had the null in it one day and data the other. I will have to have a look what we can do with flattening the jso, but that could be a major re-write of the ingest and ETL of the affected pipelines.

PS I have created a case with MS, but I have little hope from past experience.

[tayloramy]

Hi @smeetsh, 

I think a case with Microsoft is the next step. 
From my end, I've found it to be a best practice to keep everything to simple datatypes and ingest data directly into a lakehouse, and then do all the transformation logic in the lakehouse. 
This follows the typical medallion architecture of ingesting raw data into a bronze layer, and then doing all your transformations, enhancements, and normalization in a silver layer. 

I myself have had problems trying to injest complex datatypes like JSON in the past with Fabric (and other ETL tools) so I have moved to treating it like a string, and then dealing with parsing it in Python where I have more control. 

Still this doesn't make sense that it was workng and now is not, so something changed here and I'm not sure what it was. 

Hopefully Microsoft is able to get to a solution. 

Going forward, for new development I recommend treating the JSON as a string and then parsing it once it's already in a lakehouse, that will lead for simpler ingestion and more maintainable processes in the future. 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, please mark this as the solution.

[smeetsh]

Hi @tayloramy I have used the "treat array as string option" which dumps the array in a single column in my lakehouse. I assume that is what you meant? I was thinking about using a notebook with sparksql to parse it, is there a reason that you use python for this? 

Thanks
Hans

[tayloramy]

Hi @smeetsh,

That's exactly what I meant. 

If you're comfortable using sparksql then by all means do that. My background is computer science, and while I'm confident in my sql, if I have the choice I gravitate to python.

Thanks, 

Taylor 

[smeetsh]

Thanks for the quick response, I am more towards DBA, hence the sparkSQL 😄 , but always willing to try new things, I am not very versed with notebooks in general anyway 

Ps are you in or around NZ per chance, looking at the response time? 

[tayloramy]

I'm in Canada, just have terrible sleep habits