Dataflow Gen2: Low-Code Transformation at Scale in Microsoft Fabric

What Is Dataflow Gen2 and Why It Matters#

Dataflow Gen2 is Fabric Data Factory's low-code data preparation item, built on the same Power Query experience familiar to users of Excel, Power BI, Power Platform, and Dynamics 365 ^[S1]. That shared foundation means analysts who already know Power Query can apply their skills immediately, without learning a new language or authoring environment.

On the transformation side, Gen2 exposes more than 300 built-in transformations spanning joins, aggregations, data cleansing, column manipulations, conditional logic, and custom expressions ^[S2]. Microsoft positions the tool specifically for scenarios where data must be shaped before it is loaded — the classic Extract, Transform, Load (ETL) pattern ^[S2]. It sits alongside Copy Activity (which handles high-throughput, minimally-transformed movement) as one of the two primary ingestion paths in Fabric Data Factory.

Running Dataflow Gen2 requires a Fabric capacity, a Fabric trial capacity, or a Power BI Premium capacity ^[S1]. It is not available on per-user licensing alone.

Gen1 vs Gen2: What Actually Changed#

Dataflow Gen2 is a distinct item from Power BI's Dataflow Gen1, not an in-place upgrade ^[S3]. Understanding the differences matters because the two coexist in the Fabric workspace and serve overlapping but different purposes.

Gen2 adds four major capabilities that Gen1 lacks ^[S1]:

• Multiple output destinations — transformed data can be written to a range of Fabric and external targets (detailed below) rather than to Gen1's private internal storage.
• Pipeline integration — Gen2 dataflows can be invoked as scheduled activities inside a Fabric Data Factory pipeline.
• High-performance compute — Gen2 can scale query execution using Fabric SQL compute engines (see the Internals section).
• Autosave with background publishing — the authoring experience saves continuously and publishes without blocking the editor.

The one capability Gen1 retains that Gen2 does not replicate is DirectQuery access through the dataflow connector ^[S1]. Gen1 results are reachable downstream via that connector; Gen2 routes its output through explicit destination writes instead.

There is no automatic upgrade path from Gen1 to Gen2 ^[S3]. Migration requires one of the three manual routes described in the Migration section below.

Gen2 executes on a managed Spark backend rather than the mashup engine Gen1 relies on — the underlying reason it can scale to larger data volumes. (Tier-6 community source; treat the engine-attribution detail as unverified beyond this single source until corroborated.)

Supported Output Destinations#

Gen1 keeps its results in its own internal storage, accessible only through the dataflow connector. Gen2 breaks that constraint entirely: it can write transformed output to a wide range of external and Fabric-native destinations ^[S1]:

• Fabric Lakehouse (both Delta tables and files)
• Fabric Data Warehouse
• Fabric KQL database
• Fabric SQL database
• Azure SQL Database
• Azure Data Explorer
• Azure Data Lake Storage Gen2
• SharePoint files
• Snowflake

This destination flexibility is the core architectural shift. A single Gen2 dataflow can land prepared data directly into the lakehouse table that a notebook will then query, or into an Azure SQL database that a downstream pipeline stage will promote to a warehouse — without any intermediate copy step.

Authoring Experience#

Autosave and Background Publishing#

Gen2 autosaves edits continuously as a draft, so closing the browser or losing network connectivity during an editing session does not discard work ^[S1]. When the author is ready, publishing persists the dataflow and runs validations in the background, leaving the editor unblocked. This is a meaningful improvement over Gen1's blocking publish model for practitioners who work in interrupted, meeting-heavy environments.

Recent Data and Browse Location#

Gen2's Recent Data module remembers previously used tables, files, folders, databases, and sheets so they can be reloaded onto the canvas without reconfiguring connections from scratch ^[S1]. The Browse Location picker lets the author select sibling items from the same folder or database, reducing navigation friction when working across items in the same workspace.

Copilot Integration#

Copilot in Fabric is integrated into the Dataflow Gen2 authoring surface ^[S1]. Through natural-language prompts, an author can connect to sources, apply transformations, generate sample data, and undo applied steps. Each Copilot action is surfaced as a card in the UI alongside the corresponding step in the Applied Steps pane, so the author retains full visibility into what Copilot changed and can review or remove any step. Inference: This makes Copilot an additive accelerator rather than a black-box replacement for the Power Query editor.

Connector Security and Lifecycle#

Dataflow Gen2 inherits its entire connectivity surface from Power Query, so the health of that connector ecosystem is directly relevant to anyone building production dataflows. Microsoft has stated it is bringing all Power Query connectors in-house, framing Microsoft-owned connector development and maintenance as a deliberate way to reduce long-term security and operational supply-chain risk compared with externally maintained connector code ^[S5].

That in-housing effort runs through a structured four-stage lifecycle that Microsoft applies to each connector ^[S5]:

1. Preview — early access for evaluation.
2. General Availability (GA) — fully supported, production-ready, with ongoing updates and versioning.
3. Transparent Migration — a supported transition period as the implementation changes, for example a new security model or protocol.
4. Retirement — legacy versions are removed once a more secure alternative is broadly available.

Retirement is explicitly gated on migration readiness, not a calendar date: a legacy connector version is only retired once its transparently migrated, more secure replacement is broadly available ^[S5]. That sequencing matters for planning — a connector reaching "Transparent Migration" is a signal to start testing the replacement, not an immediate breaking change.

Several connectors illustrate where this lifecycle has already played out. The Snowflake V2 connector reached GA on ADBC (Arrow Database Connectivity), which Microsoft describes as the foundation for its modern, in-house connectivity approach going forward ^[S5]. A fully supported Google BigQuery V2 connector reached GA after what Microsoft describes as a multi-semester investment in stability, performance, and enterprise readiness ^[S5], and an Amazon Redshift V2 connector reached GA developed in collaboration with Amazon Redshift as part of a strategy for consistent, secure connectivity across major cloud data warehouses ^[S5]. Not every source gets a Microsoft-shipped V2 connector: Vertica and IBM Netezza instead use a "Bring Your Own Driver" model through the on-premises data gateway, where the customer supplies their own driver rather than Microsoft shipping and maintaining a connector directly ^[S5]. Microsoft frames the Snowflake, BigQuery, Redshift, and Vertica/Netezza releases as having reached GA around September 2025, ahead of the post that documents them ^[S5].

On-premises connectivity has a related diagnostics improvement: gateway diagnostic information is now integrated directly into the Dataflow Gen2 run experience, so administrators and support teams can inspect relevant gateway diagnostics without switching to a separate tool ^[S8].

CI/CD and Git Integration#

Since April 2026, every new Dataflow Gen2 item is created with CI/CD and Git integration support enabled by default ^[S1]. The older "classic" creation path — which lacked ALM integration — has been removed for new items ^{[S1] [S6]}. Existing classic dataflows continue to work and can be converted to the CI/CD-enabled format using the Save As feature.

This change aligns Dataflow Gen2 with the rest of Fabric's ALM story: workspace Git integration, deployment pipelines, and version history all apply once an item is in CI/CD mode. A tier-6 community tutorial adds a useful mechanical detail worth treating as a single-source, unverified-beyond-this-post claim: it describes Gen2 storing its Power Query logic as plain-text .pq files, which would allow normal Git diffing and review workflows through Fabric's GitHub or Azure DevOps integration ^[S6]. This detail is hedged because it comes from one tier-6 source only — verify the file format for your own tenant before relying on it for tooling.

Migrating from Gen1 to Gen2#

Three migration routes exist from Gen1 to Gen2 ^[S1]:

1. PQT template export/import — export the Gen1 queries as a Power Query Template (.pqt) file from the Power Query editor and import that file into a new Gen2 item. This is the most portable option and works even when the Gen1 item is in a different workspace or tenant.

2. Copy and paste — copy individual queries from the Gen1 Power Query editor and paste them into Gen2. Best suited for selective migration of specific queries rather than a full dataflow.

3. Save As — clone any existing dataflow directly as a CI/CD-enabled Gen2 item in a single step. The fastest path when the source dataflow is accessible in the same workspace and the author wants to preserve all queries.

Choose the PQT route for cross-workspace or cross-tenant migrations; choose Save As for in-workspace conversions where speed matters. A tier-6 tutorial source separately notes that, as of April 2026, the classic Dataflow Gen2 creation experience was no longer available for new items, reinforcing that new work should target the current experience rather than the classic one ^[S6].

Composition Patterns#

Gen2 dataflows compose well with other Fabric and pipeline workloads ^[S1]. A common pattern chains Gen2 with downstream items:

• Lakehouse + Notebook — a Gen2 dataflow loads curated, cleansed data into a Lakehouse Delta table; a Spark notebook then reads that table for further analysis, ML feature engineering, or complex procedural logic that Power Query cannot express. A separate tutorial source frames this notebook-versus-Gen2 choice around scale and audience: notebooks suit large recurring transforms (its own example is a daily 10GB-plus load), while Gen2 suits low-code, business-user-driven visual mapping ^[S9].
• Azure SQL + Pipeline — a Gen2 dataflow writes prepared data to Azure SQL Database; a Fabric pipeline then moves or copies that data onward to a warehouse at a scheduled interval.
• Pipeline orchestration — a Fabric Data Factory pipeline can invoke a Dataflow Gen2 as a scheduled activity, giving full pipeline-level dependency management, retry logic, and monitoring across the chain. Gen2 also exposes an Execute Query API that runs a specific query on demand outside the normal scheduled-refresh cycle, which is useful for triggering transformation logic from an external orchestrator rather than waiting on the schedule ^[S6].

Inference: This composability means Gen2 is most valuable in the ingestion and light-transformation layer, not as a replacement for complex Spark or SQL transformations. Heavy procedural logic, nested data structures, ML inference, and streaming workloads are better handled downstream.

Where Dataflow Gen2 Sits in the Declarative Landscape#

Fabric's data engineering toolset is moving toward more declarative transformation patterns. Dataflow Gen2 is a graphical, low-code expression of that shift — the author declares what the output should look like, and the Power Query engine determines execution. For SQL-centric teams, interface-level declarative tools such as dbt compile developer-authored SQL models into execution plans, but still issue imperative mutations at runtime; they are analogous to build systems like Make ^[S4]. Execution-level tools such as SQLMesh go further with a plan-and-apply workflow that shows exact changes before they run and supports isolated virtual-environment testing ^[S4].

The diagram below lays out how to choose between Gen2, Copy Activity, and code-first tools for a given transformation need, walks through the staging internals that make Gen2's fast-copy path work, and places Gen2 on the broader declarative-tooling spectrum alongside dbt and SQLMesh.

For most low-code and citizen-data-engineer scenarios, Gen2 occupies the right balance: a fully graphical authoring surface with broad connector coverage, no SQL or code required, and direct integration with Fabric destinations. It does not replace dbt or SQLMesh for teams with mature SQL-model libraries; it complements them by handling source connectivity and initial shaping.

Best Practices#

These recommendations follow from the verified pattern-type claims above.

• Use Save As for in-workspace Gen1 migration. It creates a CI/CD-enabled Gen2 item in one step and preserves the full query set ^[S1]. Reserve the PQT export route for cross-workspace scenarios.
• Prefer Gen2 as the ETL layer, not the full pipeline. Chain Gen2 with a Lakehouse or notebook for complex analytical logic rather than trying to express everything in Power Query ^{[S1] [S9]}.
• Leverage pipeline invocation for scheduling and dependencies. Rather than scheduling Gen2 dataflows in isolation, wrap them as activities in a Data Factory pipeline to get retry logic, run history, and dependency chaining across the full ingestion flow ^[S1].
• Migrate classic items to CI/CD mode. Since April 2026, the classic path no longer applies to new items; converting existing classic dataflows via Save As brings them into the standard ALM model ^{[S1] [S6]}.
• Treat staging system items as read-only infrastructure. Do not modify DataflowsStagingLakehouse or DataflowsStagingWarehouse directly; changes can corrupt ongoing dataflow runs ^[S1].
• Use a stable service account as dataflow owner, not an individual's identity. A tier-6 troubleshooting source recommends this explicitly as a preventive measure against identity-bound refresh failures (see the troubleshooting section below), and to include the dataflow's type property explicitly in source-controlled ARM/pipeline templates rather than relying on deployment-tool defaults ^[S7].

Before/after example, grounded in the CI/CD type-property fix documented for error 20302 ^[S7]:

// Before — relies on the deployment tool's default, which the source
// reports can silently default to the legacy "Dataflow" type
{
  "name": "RefreshCrmDataflow",
  "type": "DataflowActivity",
  "typeProperties": {
    "workspaceId": "<workspace-guid>",
    "dataflowId": "<dataflow-guid>"
  }
}

// After — dataflowType set explicitly and case-correctly, and owned by
// a service principal / service account rather than a named employee
{
  "name": "RefreshCrmDataflow",
  "type": "DataflowActivity",
  "typeProperties": {
    "workspaceId": "<workspace-guid>",
    "dataflowId": "<dataflow-guid>",
    "dataflowType": "Dataflow Fabric"
  }
}

What Goes Wrong#

The following antipatterns and edge cases emerge from the verified claims.

Expecting a direct upgrade path from Gen1. There is no automatic upgrade: a Gen1 dataflow cannot be promoted to Gen2 in place ^[S3]. Teams that assume the two are interchangeable will find that Gen1's downstream dataflow connector is not available in Gen2, requiring rework of any reports or datasets that depend on it ^[S1].

Confusing Gen1 and Gen2 in the workspace. Because both item types can coexist in a Fabric workspace, naming conventions and item-type labels matter. A Gen1 dataflow without an explicit output destination will silently keep data in internal storage; a Gen2 item without a destination configured will not persist results anywhere useful.

Modifying or deleting staging system items. The DataflowsStagingLakehouse and DataflowsStagingWarehouse are provisioned and managed by Gen2 internally ^[S1]. Deleting them, renaming them, or writing directly into their tables can break active dataflow runs and may require recreating the Gen2 item to restore the staging infrastructure.

Treating Gen2 as a replacement for complex Spark logic. Power Query's 300+ transformations cover a wide range, but complex iterative logic, ML scoring, streaming ingestion, and deeply nested semi-structured processing remain the domain of Spark notebooks ^{[S4] [S9]}.

Scheduled refresh failing with a generic "user configuration issue" (error 20302). A tier-6 troubleshooting source documents this specific error surfacing during scheduled refresh with a message the author describes as masking the real, underlying identity or definition problem rather than pointing at a specific misconfiguration ^[S7]. This is a single tier-6 source — treat the exact error code and message text as unverified beyond it, and confirm against your own tenant behavior before relying on it operationally. Two root causes and matching fixes are documented:

• Stale creator identity. The dataflow's refresh is bound to the identity/credentials of whoever created it, so refresh starts failing once that person leaves the organization or changes their password ^[S7]. The documented fix is to explicitly take ownership of the dataflow in its settings and force a metadata refresh — for example, editing a step description or renaming a Power Query step and republishing ^[S7].
• CI/CD type-property mismatch. CI/CD-deployed pipeline definitions can default the dataflow's type property to the legacy Dataflow value instead of the Gen2-specific Dataflow Fabric value, and the source notes this property is case-sensitive ^[S7]. The documented fix is to edit the deployed pipeline activity's JSON definition and set dataflowType to Dataflow Fabric explicitly rather than relying on the deployment tool's default ^[S7] — see the before/after example above.

Worked Example: Ingesting CRM Data into a Lakehouse#

Scenario: a sales team's CRM exports a nightly CSV to ADLS Gen2. The data needs column renaming, type casting, null filtering, and a derived field before it lands in a Lakehouse Delta table for reporting.

1. In the Fabric workspace, create a new Dataflow Gen2 item (it is created in CI/CD mode by default as of April 2026 ^[S1]).
2. In the Power Query editor, connect to the ADLS Gen2 storage account. The connection is saved; next time it appears in the Recent Data panel ^[S1].
3. Apply transformations: rename columns to match the lakehouse schema, cast date strings to Date type, filter out rows where the customer ID is null, add a loaded_date column using DateTime.LocalNow().
4. In the Output Destination panel, select the target Lakehouse and specify the Delta table name. Gen2 handles schema mapping and Delta write ^[S1].
5. Publish the dataflow. The platform provisions the staging lakehouse and warehouse in the background, runs the query against the SQL compute engine, and writes the result to the Delta table ^[S1].
6. In a Data Factory pipeline, add a Dataflow activity pointing to this Gen2 item. Configure it to run after a Copy Activity that lands the raw CSV, so the transformation only triggers when new data is present ^[S1]. Set the pipeline activity's dataflowType explicitly to Dataflow Fabric in the deployed JSON rather than leaving it to a CI/CD default, to avoid the type-mismatch failure mode documented above ^[S7].
7. Set the dataflow's owner to a service account rather than the authoring engineer's personal identity, to avoid the stale-identity refresh failure documented above ^[S7].

Inference: The pipeline wrapper gives retry semantics, alerting, and a single monitoring view for the full ingestion chain — advantages not available when scheduling the dataflow in isolation.

Internals#

Architecture & design#

Gen2's core architectural shift from Gen1 is destination flexibility backed by dedicated staging infrastructure. When a Gen2 item runs, the platform automatically provisions two system items in the workspace: DataflowsStagingLakehouse, a Lakehouse used as an intermediate staging store during transformation execution, and DataflowsStagingWarehouse, a Warehouse used for SQL-engine-backed query processing during the run ^[S1]. These items are internal infrastructure managed by the platform — not designed for direct user interaction, and should not be modified, queried directly, or deleted independently of the dataflow lifecycle ^[S1].

A tier-6 community tutorial adds a specific mechanism worth flagging clearly as single-source and unverified beyond it: it describes Gen2 executing Power Query M transformations on a managed Spark backend rather than the mashup engine that Gen1 dataflows use, and identifies this as the reason Gen2 can scale to larger data volumes ^[S6]. This contradicts, or at least sits alongside, the Microsoft-documented staging-warehouse/SQL-compute path above — Microsoft's own documentation does not itself name Spark as the execution engine, so treat the Spark-backend detail as a single tier-6 claim pending corroboration from a tier-1/2 source, not as confirmed product architecture. The same source describes a Fast Copy path that bypasses the Power Query transformation engine entirely for raw ingestion, though it is limited to Parquet and CSV source formats and explicitly does not support VNet data gateways or on-premises data gateways ^[S6]. Connectivity itself sits on the Power Query connector layer, which Microsoft is actively consolidating in-house through the four-stage Preview → GA → Transparent Migration → Retirement lifecycle described earlier ^[S5] — meaning the connector surface a Gen2 dataflow depends on is itself a versioned, evolving component of the architecture, not a static list.

How it works internally#

The staging layer is what enables Gen2 to scale transformations beyond what the Power Query mashup engine alone could handle: intermediate query results are materialized into the staging lakehouse, and Fabric's SQL compute processes them at warehouse scale before writing to the final destination ^[S1]. On top of that baseline path, a tier-6 tutorial documents Partitioned Compute, which can automatically detect Hive-style hierarchical partition folder structures (for example, year/month partitioning) in ADLS Gen2 and parallelize the dataflow's execution across them without requiring manual M query changes ^[S6]. Single tier-6 source; the exact detection mechanism is not independently confirmed. The same source documents an Execute Query API that runs a specific query on demand outside the normal scheduled-refresh cycle, letting an external orchestrator trigger transformation logic directly rather than waiting on the schedule ^[S6].

Inference: Because the staging items live in the same workspace, teams should account for their presence in workspace-level governance, capacity consumption monitoring, and access-control reviews. The items appear in workspace listings but are system-managed.

Performance characteristics#

One verified performance claim exists in the knowledge base, and it is important to hold to the standard the source itself sets: a tier-6 tutorial reports the author's own benchmark, not a Microsoft-published number. In that self-reported test, a 6 GB daily load on an F64 capacity went from roughly 30 minutes to about 1.5 minutes, with a substantial reduction in estimated cost per run, after layering Fast Copy, a modern query evaluator setting, and Partitioned Compute together versus an unoptimized baseline ^[S6]. This is one practitioner's own benchmark on one workload and one capacity SKU — not a Microsoft-published or independently reproduced number. Treat the ~20x improvement as directional evidence that these three settings compound, not as a guaranteed result for other workloads, data shapes, or capacities. No Microsoft-published throughput, latency, or capacity-consumption benchmark for Dataflow Gen2 exists in the knowledge base yet; that would need a tier-1/2 source (a Microsoft Learn performance guide or a Fabric engineering blog benchmark) to move this beyond a single self-reported data point. Tracked in content/queue.md.

Best Practices — Migration and Troubleshooting Checklist#