Dataflows Gen2 vs Notebooks: Choosing in Microsoft Fabric

Use Dataflows Gen2 by default for structured sources, where Power Query can push the work to the source through query folding and incremental loads keep each run small. Use a Spark notebook when transformations cannot fold, when you need distributed compute for large joins or aggregations, when the data is nested or streaming, or when you need Delta table maintenance at scale. A Delta table is an open table format with built-in transactions and version history. Both land data in OneLake, Fabric's single underlying data lake for all its storage, and many Fabric setups use both.

For what a dataflow is and how Gen1 differs from Gen2, see how and when to use dataflows in Power BI.

How it works

Dataflows Gen2 run Power Query in the cloud. They extract and transform data from hundreds of connectors and write the result to a destination, such as a lakehouse table, lakehouse files or a Fabric Warehouse. Their strengths are connectivity, a visual editor, and query folding, which sends the work back to the source system. They run on your Fabric capacity and can be orchestrated from pipelines.

Notebooks run on managed Apache Spark. You write transformations in PySpark, Spark SQL, Scala or SparkR, and read and write files and Delta tables in OneLake directly. The engine is distributed, so joins, wide aggregations and complex transformations spread across the Spark cluster. Notebooks also cover work beyond ETL, such as data quality checks, feature engineering and streaming.

Decision guide: Dataflows Gen2 vs notebooks

Treat the table as a starting point, not a rule. A common split is dataflows for source ingestion and light shaping, and notebooks for heavy joins and curation into gold tables.

Dataflows with very large tables

Dataflows Gen2 handle large, structured fact and dimension tables well when they are designed for folding and incremental processing:

• The source does the heavy lifting. With query folding, filters, column selection, joins and aggregations run in the source database. The dataflow moves data and does the final shaping.

• Loads are incremental. Refresh only new or changed periods, not the full history, using a date column. Each run processes a small slice.

• Transformations are set-based. Avoid steps that break folding, such as a custom function applied to every row. Use native operations that translate to SQL.

Three techniques keep large tables manageable:

1. Push filters to the source, for example loading only the last few days on each run.

2. Split very large tables into separate queries by period or domain, and refresh them in parallel.

3. Write the output to Delta tables in OneLake, which keeps the schema predictable for everything downstream.

Dataflows struggle when folding is impossible or the work needs distributed compute. If key steps do not fold, Power Query pulls the data and transforms it itself. Performance then depends on available capacity, and runs can be throttled, queued or time out.

When notebooks are the right tool

Choose a notebook when any of these apply:

• Heavy transformations that cannot fold. Large fact-to-fact joins, complex window functions, or deduplication across very large tables.

• Nested or semi-structured data. JSON with arrays and structs, XML, or logs that need exploding, flattening and schema drift handling.

• File-heavy or streaming patterns. Many small files, or micro-batches with Spark Structured Streaming and checkpoints in the lakehouse.

• Code libraries. Data quality frameworks, parsing or enrichment in Python or Scala.

• Delta table maintenance. Control over compaction, partitioning and cleanup on large tables.

• Layered curation. Moving data from bronze to silver to gold, the medallion architecture pattern of raw, cleaned and business-ready layers, with idempotent merge logic.

A typical notebook cell flattens nested JSON and merges it into a Delta table, so rerunning it never creates duplicates:

from delta.tables import DeltaTable
from pyspark.sql import functions as F

updates = (
    spark.read.format("json").load("Files/raw/orders/")
    .withColumn("item", F.explode("items"))
    .select(
        "orderId",
        "orderDate",
        F.col("item.sku").alias("sku"),
        F.col("item.qty").alias("qty"),
    )
)

target = DeltaTable.forName(spark, "silver_order_lines")

(
    target.alias("t")
    .merge(updates.alias("s"), "t.orderId = s.orderId AND t.sku = s.sku")
    .whenMatchedUpdateAll()
    .whenNotMatchedInsertAll()
    .execute()
)

This assumes the notebook has a default lakehouse attached and the table silver_order_lines exists. For curated tables that must land in a Warehouse, the Spark connector for Fabric Data Warehouse writes them from the notebook.

Implementation guide

1. Classify sources and targets. Group workloads by source type (relational or semi-structured), transformation complexity and required latency. Decide whether each target is a lakehouse table, a Warehouse, or both.

2. Test for folding early. Build a prototype in Power Query and use the folding indicators on each step. If key steps do not fold, plan a notebook.

3. Design the incremental strategy. Choose a partitioning key, such as ingestion date or business date. For dataflows, filter on it at the source. For notebooks, partition the Delta table on it and write idempotent merges.

4. Pilot both at realistic volume. Measure run time and capacity consumption with representative data. Small test data hides the differences that matter.

5. Write down the pattern. Record when to use each tool, naming conventions, destinations and monitoring, and turn them into templates.

Performance, capacity and cost tactics

Dataflows Gen2

Keep query folding for as many steps as possible, and put non-folding steps last. Push filters and joins to the source, and pre-aggregate where the report allows it. Split large tables into separate queries so they run in parallel. Set column types once, early, instead of changing them late in the query.

Notebooks

Partition Delta tables on the columns queries filter by, but avoid so many partitions that each one holds tiny files. Control file sizes by repartitioning before writes. Use idempotent merges with deterministic keys. Schedule compaction and cleanup:

OPTIMIZE silver_order_lines;
VACUUM silver_order_lines RETAIN 168 HOURS;

OPTIMIZE compacts small files into larger ones. VACUUM removes files older than the retention period that the table no longer references. 168 hours is the Delta default of seven days.

Both

Orchestrate with pipelines, so retries, dependencies and alerts sit in one place. Watch capacity use in the Microsoft Fabric Capacity Metrics app, and move schedules or resize capacity when jobs start to queue.

Governance and security considerations

• Use lineage view and the monitoring hub to trace sources, transformations and consumers across dataflows and notebooks.

• Apply workspace roles and item permissions consistently, and limit write access to curated layers (silver and gold).

• Do not hard-code secrets in notebooks. Use connections and credentials managed in Fabric.

• Separate development, test and production workspaces. Use Git integration, which new Dataflow Gen2 items and notebooks support, with deployment approvals.

• Agree data contracts per table: owner, refresh expectations and schema.

• For Delta tables, plan how columns may change and validate on write.

Common pitfalls

• Assuming data size alone decides the tool. How the transformations behave, and whether they fold, matters more.

• Breaking query folding early with a convenience step, then blaming dataflows for being slow.

• Writing many tiny Delta files from Spark. Fast to write, slow to read, so plan compaction.

• Several writers on one Delta table without coordination, which causes concurrency conflicts. Follow the VACUUM retention guidance so readers do not fail.

• Running OPTIMIZE from a notebook on a lakehouse table that a Dataflow Gen2 fills with incremental refresh. That combination is not supported.

• No incremental strategy, so full reloads of big tables become slow and expensive.

• No monitoring or alerts, so a failure is found days later, when someone needs the numbers.

• Every team solving the same problem differently because nothing was turned into a template.

FAQs

Are Dataflows Gen2 being replaced by Notebooks in Fabric?

No. They serve different purposes and are both current Fabric items. Dataflows Gen2 are the quickest route for structured ingestion where query folding applies. Notebooks are for distributed processing, complex logic, and file-heavy or streaming work.

Can Dataflows handle "very large" tables?

Yes, when they are designed for folding and incremental loads. If the source can filter and aggregate, and each run loads only recent data, a dataflow can keep a large fact table current. If the logic forces Power Query to process everything itself, or needs big joins, a notebook is usually more reliable.

When should I default to Notebooks?

Default to a notebook for nested JSON, large fact-to-fact joins, deduplication across very large tables, complex window functions, or when you need Spark libraries and control over partitioning and Delta maintenance.

Can we mix both on the same table?

You can, but give each table one writer per layer. For example, a dataflow writes bronze or silver, and a notebook curates silver into gold. Avoid concurrent writes to the same table, and use pipelines to run the steps in order. This matters most with Dataflow Gen2 incremental refresh into a lakehouse: Microsoft advises against other writers on that table, and OPTIMIZE is not supported on it.

How do we migrate a Dataflow to a Notebook?

There is no automatic conversion. Translate each Power Query step into Spark SQL or PySpark and compare the output with the dataflow's output. Use the migration to revisit partitioning, joins and Delta layout, and keep the dataflow running until the results match.

What about licensing and capacity?

Both consume Fabric capacity units (CUs) from the workspace's capacity. How much you can run, and what it costs, depends on the capacity size and how many jobs run at once. Use the Capacity Metrics app to decide on scheduling or scaling.

Do Dataflows support CDC?

Partly. Dataflows Gen2 have an incremental refresh setting that reloads only recent periods based on a date column, when the source can filter on it. For change data capture across several sources, watermark tracking, or merge logic that handles deletes, notebooks give more control.

Can Notebooks write to Warehouses?

Yes. The Spark connector for Fabric Data Warehouse writes a DataFrame to a Warehouse table. Alternatively, write curated Delta tables to a lakehouse and query them through its SQL analytics endpoint. Choose the serving layer by who consumes the data.

Glossary

• Dataflows Gen2: Power Query in Fabric's Data Factory, writing results to OneLake, a lakehouse, a Warehouse or other destinations.

• Notebook: An interactive Spark environment in Fabric for Python, SQL, Scala or R.

• OneLake: Fabric's single data lake, the storage under lakehouses and warehouses.

• Lakehouse: A Fabric item that stores Delta tables and files, with a SQL analytics endpoint.

• Warehouse: Fabric's SQL data warehouse.

• Query folding: Power Query sending transformations back to the source to run there.

• Delta Lake: An open table format with transactions, schema evolution and time travel.

• Partitioning: Splitting data by a key, such as date, to speed up loads and queries.

• Incremental load: Loading only new or changed data instead of the full table.

• Orchestration: Scheduling jobs with their dependencies and retries, typically with Fabric pipelines.

For how Fabric's Data Factory compares with Azure Data Factory, see Fabric vs Azure Data Factory. For the wider case for the platform, see why choose Microsoft Fabric and Microsoft Fabric and Power BI.

Closing

If you want a working template for "dataflows by default, notebooks when needed", including capacity sizing and guardrails, contact CaseWhen.

Sources

• Differences between Dataflow Gen1 and Dataflow Gen2 - Microsoft Learn

• Incremental refresh in Dataflow Gen2 - Microsoft Learn

• Spark connector for Microsoft Fabric Data Warehouse - Microsoft Learn

• Delta Lake table maintenance - Microsoft Learn