ETL Data Factory

[icassiem]

Hi, 

Please help, i am new to Fabric and im thinking peipeline and datafactory is the new versions of SSIS

1. Can i use data factory to tranform json semi structured data to ingest directly into silver or do i need to use python or ?

2. I received push back regarding middleware lthe "datafactory or python" layers, when there are updates or patches does this impact my pipes code and needs updating too or fails - please i need to update my cto as this hesiatates him that theres a maintenace dependency / risk?

3. Am i correct in saying that my firs tier for tranfrom is datafactory "old ssis" then pythin for heavier tranform or dbt core "i dont know how" pything with sql?

4. Job management in fabric is the same like sql and i could use SSMS to manage the space or?

Regards

[ssrithar · Accepted solution]

Hi @icassiem ,

There are a few similarities with SSIS, but Fabric is broader than just a replacement for SSIS. Data Factory provides orchestration, data movement, and low-code transformations, while Fabric also includes Spark notebooks, Dataflows Gen2, Lakehouses, Warehouses, Real-Time Intelligence, and more.

1. Can Data Factory transform JSON into Silver?

Yes, depending on the complexity.

• Simple to moderate JSON transformations can be handled using Dataflows Gen2 (Power Query) or data pipelines.

• Complex nested JSON, large volumes, or advanced business logic are generally better handled in Spark/Python notebooks.

A common pattern is:

• Bronze: Copy the raw JSON into a Lakehouse.

• Silver: Flatten, cleanse, and validate the data using Dataflows Gen2 or Spark notebooks.

• Gold: Create business-ready tables or models.

2. Do updates or patches to Fabric break pipelines or require code changes?

Generally, no.

Microsoft manages the Fabric platform, so you don't need to patch or upgrade the service yourself. Platform updates shouldn't require you to rewrite your pipelines or notebooks.

However, as with any integration platform, changes in your source systems (for example, an API schema change, authentication changes, or file format changes) can require updates to your pipelines or transformation logic. That is a normal maintenance consideration regardless of whether you're using Fabric, SSIS, Azure Data Factory, or another ETL tool.

3. Should Data Factory be the first transformation layer and Python only for heavy transformations?

That's a good way to think about it, although there isn't a strict rule.

A common approach is:

• Pipelines → orchestration and data movement.

• Dataflows Gen2 → low-code data cleansing and shaping.

• Spark/Python notebooks → complex transformations, large-scale processing, or custom logic.

• SQL (Warehouse or Lakehouse SQL endpoint) → SQL-based transformations where appropriate.

• dbt can also be used if your team already follows a dbt workflow, but it isn't a Fabric requirement.

4. Can I use SSMS for job management like SQL Server Agent?

Not exactly.

Fabric doesn't use SQL Server Agent. Instead:

• Fabric Pipelines are typically used for scheduling and orchestration.

• You can monitor runs through the Fabric monitoring experience.

• You can connect SSMS to a Fabric Warehouse or SQL analytics endpoint to run SQL queries, but SSMS is not used to manage Fabric jobs or schedules.

Overall, if you're coming from an SSIS background, a useful mental model is:

• Pipelines ≈ SSIS Control Flow (orchestration)

• Dataflows Gen2 ≈ low-code ETL

• Spark Notebooks ≈ advanced ETL/data engineering

• Lakehouse/Warehouse = your storage and analytics layer

Once you become familiar with the Fabric architecture, you'll find that it provides much more flexibility than SSIS while remaining suitable for traditional ETL workloads.

If this post helps, then please appreciate giving a Kudos or accepting as a Solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

[icassiem]

@ssrithar  Thank You