Notebooks, Dataflows Gen2 or pipelines: choosing the right Fabric tool for each job

Fabric gives you several ways to move and transform data, and they overlap. Picking one per job, and agreeing the rule as a team, matters more than which one is technically best.

One of the first questions on any Fabric project is which tool should do the work. Fabric offers several ways to move and transform data, and they overlap. Pick well and the platform is easy to support. Pick differently every time and nobody can follow how a number was made.

This is the fourth post in our Fabric building blocks series, after Delta and Parquet.

Pipelines: the conductor

Data Factory pipelines orchestrate work. They run activities in order, handle dependencies, retry failures, send alerts and run on schedules or triggers. The Copy activity moves data from hundreds of sources into OneLake at scale.

Pipelines are the right choice for moving data and for coordinating everything else. They are not really a transformation tool. Their job is to make sure the right things happen in the right order, which we cover in orchestration is not scheduling.

Dataflows Gen2: low-code transformation

Dataflows Gen2 use Power Query, the same interface as Power BI Desktop and Excel. They connect to a wide range of sources, apply transformations through a visual editor, and write results to a lakehouse or warehouse.

They suit smaller volumes, business-owned transformations and teams whose skills are in Power Query rather than code. For large volumes or complex logic they can use more capacity than the code-based alternatives, so it is worth checking the cost of heavy dataflows. Our post on stopping paying for capacity you do not use covers how to spot that.

Notebooks: code for scale and control

Notebooks run code, most often PySpark or Spark SQL, against data in OneLake. Fabric also offers lighter Python notebooks for jobs that do not need a Spark cluster.

Notebooks suit large volumes, complex logic such as slowly changing dimensions and merges, and anything that benefits from being tested and version controlled like software. They need engineering skills, and they reward good engineering practice.

Materialized lake views: declared transformations

Materialized lake views are the newest option. You define a transformation in Spark SQL, and Fabric keeps the result as a Delta table, refreshes it in dependency order, skips the refresh when nothing has changed, and refreshes incrementally where it can. Data quality rules can be declared in the same statement.

They fit the Silver and Gold layers of a medallion architecture well, because much of that work is exactly this: declared transformations from one layer to the next.

A simple rule of thumb

• Moving data from a source into OneLake: a pipeline Copy activity, or mirroring where the source is supported.
• Small, business-owned transformations: Dataflows Gen2.
• Large volumes, complex logic, history handling: notebooks.
• Layer-to-layer SQL transformations that should refresh themselves: materialized lake views.
• Running all of it in the right order: pipelines.

The most important decision is not which tool is best. It is agreeing a rule and sticking to it, so that anyone can find where a transformation lives. Mixed tools with no rule are a common feature of estates that rot over time.

Where to go next

The last post in this series compares the two ways of querying Fabric data with SQL: the lakehouse SQL analytics endpoint and the Fabric Warehouse. If you would like a second opinion on the tools in your estate, our data platform team is happy to help.

Choose the tool for the job, then choose it the same way every time.