Forum Discussion
Dataflow Gen2 File Destination - JSON and Parquet Format Support Needed for Nested OData Data
Hi pwr20 ,
Currently, Microsoft Fabric Dataflow Gen2 supports only CSV/delimited formats for file-based destinations such as Lakehouse Files and ADLS Gen2. These formats do not support nested data structures, which makes them unsuitable for landing raw nested OData data without flattening.
When working with nested OData navigation properties, Dataflow Gen2 treats nested records as unsupported for tabular or file destinations. As a result, nested columns are either dropped or replaced with null values when writing to Lakehouse tables, staging destinations, or files. Fully expanding nested data within Dataflow can work for small volumes but does not scale reliably, especially when using an on‑premises data gateway, due to sequential API calls and timeout limitations.
At this time, JSON or Parquet file destinations are not available in Dataflow Gen2, and there is no built‑in, scalable solution to preserve nested OData structures end‑to‑end. This is a current product limitation, not a configuration issue.
I’d encourage you to submit your detailed feedback and ideas via Microsoft's official feedback channels, such as the Microsoft Fabric Ideas.
Feedback submitted here is often reviewed by the product teams and can lead to meaningful improvement.
Thanks,
Prashanth
Thank you for the detailed response!
I tried the Text.FromBinary(Json.FromValue()) approach, but unfortunately it triggers a 404 error in our case. It appears that when Dataflow applies this transformation, the OData connector attempts to resolve the nested table via a navigation URL rather than operating on the already loaded data. The URL it constructs appends the nested entity path and that endpoint returns a 404 from our API.
We worked around this by using $select in the OData URL to return the nested data inline, combined with Table.ExpandTableColumn. This avoids the navigation request and works cleanly for smaller loads.
However, we are now facing a scale challenge. We have a large number of parent records each containing thousands of nested records, and the $filter parameter only applies at the parent level not the nested level. The standalone endpoint for the nested entity is also restricted in our API.
Do you have any suggestions for handling incremental or filtered loads of nested OData data at scale within Fabric Dataflow Gen2?
Hi pwr20
give this a try
Use Dataflow Gen2 to pull just the parent records (scalar columns only, fast and reliable). Write those to a Lakehouse table. Then trigger a Notebook that reads the parent IDs and calls the OData API directly using requests over HTTP for the nested data. The Notebook can handle retries, parallelism, and write JSON/Parquet directly to Lakehouse Files.
Thank you for the suggestion.
Unfortunately this approach won't work in our case because the OData API is hosted on-premises and is only accessible through an on-premises data gateway. Fabric Notebooks cannot route requests through the on-premises gateway, so a direct HTTP call using requests from a Notebook would not be able to reach the API.
This is why we are constrained to Dataflow Gen2 for the data extraction part, as it is the only Fabric component that can connect through the on-premises gateway to our OData source.
Let me know your thoughts.
