ELT JSON / Semi structured

Hi icassiem 

For your scenario, I'd recommend choosing the transformation tool based on the complexity of the JSON.

• Data Pipeline is responsible for orchestrating the workflow (ingestion, scheduling, monitoring, and triggering downstream activities).

• Dataflow Gen2 works well for low-code transformations such as expanding JSON records, changing data types, filtering, and merging data.

• Fabric Notebooks (PySpark) are a better fit for complex or deeply nested JSON structures, especially when the schema evolves over time.

In one of our implementations using Bizzabo as the source, the data was delivered as nested JSON. We used a Fabric Notebook with PySpark to parse and explode the nested arrays, applied the required transformations, and then stored the curated data as Delta tables in the Lakehouse. This approach provided greater flexibility for handling complex JSON than a low-code transformation.

This approach is also aligned with Microsoft Learn, which highlights that Fabric Notebooks are designed to analyze and process raw formats such as JSON using Apache Spark capabilities before writing processed formats like Parquet or Delta Lake.

How to use notebooks - Microsoft Fabric | Microsoft Learn

I would recommend starting with Dataflow Gen2 if your JSON is relatively simple, and moving to a Notebook when the transformations become more complex or require custom logic.

I hope this helps! If this resolves your issue, you can mark the post as Accepted Solution so it may help other community members facing the same situation.