ELT JSON / Semi structured

[icassiem]

Hi, 

I need help please, im learning and not even sure if its pipeline or data factory the old ssis or dataflow

but just starting with F2 , i want to use python with a data factory when really complex tranform like possible forecatsin future but most of my sources are semi structured like JSON/S3 which i could transfrom from powerbi but now i just get a json text if im thinking correctly does data factory do the transformation like powerbi or must i write pythoi scripts or?

Please help

Regards

[Adityakumar · Accepted solution]

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.

[icassiem]

@Adityakumar thank you so much, i was afraid i would be stuck

1. So the pipeline is the orch and dataflow is the ETL 1 and use pythin is ETL 2 for more complex and then to Gold_whs i use pipeline to execute dbt core?

2. Could you share me links on each 3+DBT "the :how" for learning gudie to reference and start please?

[Adityakumar]

Hi @icassiem 

You're welcome, glad it helped.

Yes, your understanding is correct:

- Pipeline = orchestration
- Dataflow Gen2 = low-code ETL/transformation
- Notebook/Python/PySpark = complex transformation
- Warehouse/Lakehouse = serving layer for curated data

- dbt Core can be used for SQL-based transformation patterns where it fits your architecture

A good learning order would be:

1. Fabric Data pipelines:
https://learn.microsoft.com/en-us/fabric/data-factory/pipeline-overview

2. Dataflow Gen2:
https://learn.microsoft.com/en-us/fabric/data-factory/dataflows-gen2-overview

3. Fabric notebooks:
https://learn.microsoft.com/en-us/fabric/data-engineering/how-to-use-notebook

4. Lakehouse:
https://learn.microsoft.com/en-us/fabric/data-engineering/lakehouse-overview

5. Warehouse:
https://learn.microsoft.com/en-us/fabric/data-warehouse/data-warehousing

6. dbt with Fabric:
https://learn.microsoft.com/en-us/fabric/data-warehouse/tutorial-setup-dbt

For your architecture, I would suggest starting simple:

Raw JSON/S3
→ Lakehouse Files
→ Dataflow Gen2 or Notebook transformation
→ Lakehouse tables
→ Warehouse or semantic model/reporting layer

Once that flow is working, you can add dbt for SQL-based transformations in the Warehouse layer.

Hope this gives you a good starting path.