Forum Discussion
Error ingesting json files in warehouse table
A final update for now at least: Microsoft has confirmed this is a bug, they hope to fix in the 1st quarter of 2026.
In the mean time they suggest to ingest it as a string and than use a notebook to parse the json array back into its original columns, or change the source, which for us wasn't an option.
Since it took ages for them to got to this stage I had already figured out I could use a notebook to read the json files in the lakehouse, read them into a dataframe, and when all files were read, write that dataframe to a table, in my case with an option "overwrite" since this is just a raw staging table.Final kicker: They want me to give permission to archive the case (no doubt because of their KPI's), but the bug is not registered on MS's known issue list. I want them to document it there, but they for some reason don't want to do that, they want to keep it internal, so now my Dutch stubberness is playing up .
The python code was quite simple in the end, even though we had multiple nested arrays. I used co-pilot to do the heavy lifting for me since i had to alias about 30 columns since i had to put the data in an existing table and the names needed to match
These are the libraries I needed:
Folder path to read json files
Exploding the main body of the json (just part of it, as example)
Thats it in a nutshell
Good morning, No it has not been resolved and to be honest my last session with MS was downright painfull. The person from the MS data engineering team had a lot of troubles understanding how an API call wrote to a json file in the lakehouse (which is about as standard as it gets)
MS went back to do more homework.
Hi smeetsh,
Thank you for sharing this update.
It is good that the case has been escalated back to the engineering team for deeper review. Since they’re looking into the behaviour of how the API writes JSON into the Lakehouse, we will hopefully get clearer guidance or a fix from their side soon. While waiting for their update, you could try a few alternate approaches that might help. Below those are mentioned:
Use a Python notebook instead of Spark SQL to read the JSON it can handle nested structures more easily without defining every data type.
Ingest the API output to a landing folder first (as raw JSON) and then use a Dataflow Gen2 or Copy activity in Fabric to flatten and load it into your Lakehouse.
If the schema is changing often, try inferSchema=True in Spark when loading JSON to reduce manual schema issues.
Hopefully one of these can serve as a temporary workaround until the engineering team provides a fix.
Thanks again for keeping us updated. Please let us know if you have any doubts regarding this. We will be happy to help.