Ingesting Data with Dataflow Gen2: Power Query Skills in Microsoft Fabric

Introduction

You've got a Sales database in Azure SQL, a folder of CSV exports from your ERP system, and a SharePoint list that marketing swears is the authoritative source for product categories. Your lakehouse is waiting. The question isn't whether you need to move this data — it's how to do it without writing a thousand lines of Spark code or orchestrating a brittle web of custom connectors.

Dataflow Gen2 is Microsoft Fabric's answer to that problem. Built on the same Power Query engine that millions of Power BI and Excel users already know, Dataflow Gen2 gives you a low-code, graphical transformation environment that writes data directly into OneLake — as Delta tables, no less. But calling it "low-code" undersells what it can actually do. Beneath the point-and-click surface is a fully programmable M language runtime, a connector ecosystem spanning over 150 sources, a query folding engine that pushes work back to source systems, and a staging layer that solves the "connect once, fan out everywhere" problem. When you understand how all of those pieces interact, Dataflow Gen2 stops being a convenience tool and becomes a serious ingestion workhorse.

By the end of this lesson, you will have built, optimized, and scheduled a Dataflow Gen2 that ingests data from multiple sources, applies meaningful transformations, and lands clean Delta tables in a Fabric lakehouse. You'll also understand the architectural decisions that determine whether your dataflow runs in two minutes or twenty.

What you'll learn:

• How Dataflow Gen2 fits into the Fabric ingestion architecture and when to choose it over alternatives like data pipelines or Spark notebooks
• How to connect to diverse sources and use the staging layer effectively
• How to write and debug M code for transformations that the GUI can't express
• How query folding works and how to verify and maximize it
• How to configure data destinations, map schemas, and handle incremental refresh
• How to troubleshoot the most common failure modes that trip up even experienced Power Query users

Prerequisites

You should have a working Microsoft Fabric capacity — either a paid F SKU or a trial — and at least one workspace assigned to it. If you haven't set that up yet, start with the lesson on Fabric capacities and workspaces before continuing here.

You should also have a lakehouse to receive your data. If you need to create one, the lesson on building your first lakehouse in Microsoft Fabric walks through the full setup including the SQL analytics endpoint. Some familiarity with Power Query in Power BI Desktop or Excel is helpful but not required — we'll explain the relevant concepts as we go.

What Is Dataflow Gen2 and How Does It Fit In?

Before you write a single transformation, it's worth understanding where Dataflow Gen2 sits in the Fabric ingestion landscape and what it's actually doing under the hood.

Fabric offers three primary mechanisms for getting data into a lakehouse or warehouse: data pipelines (orchestration with Copy Activity), Spark notebooks (code-first, full control), and Dataflow Gen2 (Power Query-based, GUI-first). These aren't mutually exclusive — in practice, most mature Fabric implementations use all three depending on the job.

Dataflow Gen2 excels when:

• Your source has a supported connector and you want to avoid writing connector code
• Your transformations are column-level operations (filtering, joining, reshaping, type casting, simple calculations) rather than heavy analytical processing
• Your team includes analysts who know Power Query but not PySpark
• You want a transformation layer that non-engineers can audit and modify without reading code

It's a weaker choice when:

• You're moving terabytes of raw files where a Copy Activity pipeline would be faster and cheaper
• Your transformations require complex iterative logic or machine learning that M can't express
• You need extremely fine-grained control over Spark execution plans

The execution engine behind Dataflow Gen2 is Mashup — Microsoft's Power Query runtime. When you submit a dataflow, Fabric compiles your M query graph, applies query folding where possible to push computation back to the source, and then executes whatever remains in the Mashup engine running on Fabric's compute infrastructure. The resulting data lands in a staging area (more on that shortly) before being written to your configured destination as a Delta table.

This architecture has a meaningful implication: Dataflow Gen2 is not Spark. It runs on a different compute layer, which means it doesn't share Spark job capacity with your notebooks. It also means that M code — not PySpark or SQL — is the programming model, with all the benefits (rich connector support, query folding, declarative style) and limitations (memory-bound execution, no native distributed shuffle) that come with it.

Navigating the Dataflow Gen2 Editor

To create a Dataflow Gen2, open your workspace, click New, and select Dataflow Gen2. The editor that opens is a near-identical sibling of Power Query Online — the same environment used in Power Platform dataflows and Azure Data Factory's Power Query activity.

The layout has four main regions:

Queries panel (left sidebar): Each query is a named transformation pipeline. You can think of each query as a table that may or may not have a data destination. Queries can reference other queries, which is how you build modular, reusable transformation logic.

Canvas/diagram view: A visual representation of your query graph showing sources, transformations, and references between queries. This is particularly useful once you have more than a handful of queries.

Applied Steps panel (right sidebar): Every transformation you apply to a query is recorded as a step. Steps are sequential, and each step is actually a line of M code. You can click any step to see the data state at that point — this is invaluable for debugging.

Data preview: The bottom panel shows the actual data rows for the currently selected step. This preview is live — it's querying the source (or staging, if enabled) in real time.

Connecting to Sources

Click "Get data" in the Home ribbon to open the connector gallery. You'll find connectors organized by category: databases (SQL Server, PostgreSQL, Oracle, Snowflake), file storage (Azure Data Lake Storage, SharePoint, local upload), SaaS applications (Salesforce, Dynamics, Google Analytics), and dozens more.

Let's walk through connecting to three realistic sources that represent the mix most data teams encounter.

Connecting to Azure SQL Database

Select Azure SQL Database from the connector gallery. You'll be prompted for:

• Server: Your fully qualified server name (e.g., myserver.database.windows.net)
• Database: The specific database name
• Connection credentials: You can use Basic (username/password) or OAuth/Service Principal for production workloads

Once connected, you'll see the Navigator — a tree view of schemas, tables, and views. Select the tables you want. Each selected table becomes its own query in the left panel.

The moment you select a table and click Transform Data, Power Query sends a query to Azure SQL to retrieve the data preview. This is where query folding starts — and it's worth pausing on this concept because it fundamentally determines your dataflow's performance.

Understanding Query Folding

Query folding is the process by which Power Query translates your M transformations back into the native query language of the source system. When you filter rows, remove columns, join tables, or cast types on a folding-capable source, Power Query doesn't fetch all the data and process it locally — it rewrites those operations as SQL (or OData, or whatever the source speaks) and sends them to the source.

For a SQL source, this is enormous. A filter step that reads "keep only rows where OrderDate >= 2024-01-01" becomes a WHERE clause in the SQL sent to Azure SQL. If that column is indexed, you're exploiting that index. If you're pulling from a 50-million-row table, the difference between folding and not folding can be minutes vs. seconds of execution time.

To check whether a step is folding, right-click it in the Applied Steps panel. If "View Native Query" is available and not grayed out, that step is folding. If it's grayed out, folding has been broken at that step or an earlier one.

Common folding breakers:

• Merging a SQL query with a non-foldable query (like a CSV file)
• Using M functions that have no native SQL equivalent (e.g., Text.BetweenDelimiters)
• Adding a custom column with complex M expressions

The practical strategy is: filter aggressively (rows and columns) before you do anything that breaks folding. This minimizes the data that the Mashup engine has to process locally.

Connecting to a Folder of CSV Files

Select "Azure Data Lake Storage Gen2" or "SharePoint folder" depending on where your CSVs live. For ADLS Gen2:

• URL: https://yourstorage.dfs.core.windows.net
• Authentication: You can use Account Key, SAS token, or (preferred in production) the organizational account/service principal

After connecting, Power Query shows you the folder contents as a table where each row represents a file — with columns for name, path, content, date modified, etc. This is the folder query pattern, and it's powerful once you understand it.

To combine all CSVs in the folder, click the double-arrow icon in the Content column header (or use the "Combine Files" transformation in the ribbon). Power Query will:

1. Take the first file as a sample to infer the schema
2. Generate a helper query called "Transform Sample File" that defines the per-file transformation
3. Apply that transformation to every file and union the results

The "Transform Sample File" query is where you define how a single file should be parsed — delimiter, encoding, whether the first row is a header, column data types. Every file in the folder goes through that same transformation. If your files have inconsistent schemas across months, this is where you handle the mapping.

Connecting to SharePoint Lists

For SharePoint, select the SharePoint Online List connector and enter your SharePoint site URL (not the list URL — the root site URL). After authenticating with your organizational account, you'll see all lists on that site.

SharePoint list data often arrives messaged in Power Query with lots of metadata columns you don't want (GUID, Author, Editor, _UIVersionString, etc.). Your first transformation should almost always be "Choose Columns" to discard everything except the fields you actually need. This also helps folding since fewer columns means less payload.

Using the Staging Layer

This is one of the features that separates Dataflow Gen2 from Gen1, and it's one of the most misunderstood aspects of the product.

By default, Dataflow Gen2 enables a staging lakehouse — a temporary Fabric lakehouse that acts as an intermediate landing zone for your source data before transformations are applied. When staging is enabled, Fabric first copies the raw data from your source into the staging lakehouse, and then runs your M transformations against the staged copy.

Why does this matter? Three reasons:

Isolation from source load: With staging on, your source system is only queried once, during the initial copy. Subsequent transformation previews in the editor query the staging data, not your production SQL server.

Gateway-free fan-out: If your source requires an on-premises gateway (say, an on-prem SQL Server), staging lets you query through the gateway once and work against the staged copy for all downstream queries — without requiring the gateway to be available for every downstream reference.

Reliable query folding baseline: Staging provides a Delta-backed store. For sources that don't support query folding, staging doesn't help with folding per se, but it does mean transformations run in the Fabric compute layer rather than being bottlenecked by the source's own throughput.

To configure staging, look at the staging icon (a cloud with an arrow) next to each query in the left panel. Toggling it on enables staging for that query. You can enable staging on source queries while leaving derived queries to run directly against staged data.

The staging lakehouse is managed by Fabric and is not the same as your destination lakehouse. You don't directly access it. It's automatically cleaned up after the dataflow run completes.

Building Transformations with the GUI and M Code

The Power Query editor gives you a transformation ribbon that covers about 80% of what most people need: remove columns, filter rows, split columns, merge queries, pivot/unpivot, change data types, replace values, group by, add conditional columns. Every click in the ribbon generates M code behind the scenes.

Let's walk through a realistic transformation sequence for a sales orders table from Azure SQL.

The Transformation Sequence

Assume we have an Orders table with these columns: OrderID, CustomerID, ProductID, Quantity, UnitPrice, OrderDate, ShipDate, Status, CreatedBy, LastModified.

Step 1: Remove audit columns

In the column headers, Ctrl-click CreatedBy and LastModified, right-click, and choose Remove Columns. The Applied Steps panel adds a Removed Columns step. The M it generates:

= Table.RemoveColumns(Source, {"CreatedBy", "LastModified"})

Step 2: Filter to active orders

Click the dropdown on the Status column header, uncheck anything except Confirmed and Shipped. This generates:

= Table.SelectRows(#"Removed Columns", each [Status] = "Confirmed" or [Status] = "Shipped")

Step 3: Change data types

Power Query often infers types as Any. Explicitly set OrderDate and ShipDate to Date, Quantity to Int64, UnitPrice to Decimal Number. Explicit types are important because they affect how data lands in your Delta table schema.

Step 4: Add a calculated column

Click "Add Column" > "Custom Column" and create LineTotal:

= [Quantity] * [UnitPrice]

This generates:

= Table.AddColumn(#"Changed Type", "LineTotal", each [Quantity] * [UnitPrice], type number)

When You Need M Code Directly

Sometimes the GUI doesn't have what you need. Common situations:

Conditional logic with more than two outcomes. The conditional column dialog only handles simple if/else. For multi-branch logic, you need M:

= Table.AddColumn(#"Previous Step", "ShippingTier", each 
    if [LineTotal] >= 10000 then "Priority"
    else if [LineTotal] >= 1000 then "Standard"
    else "Economy",
    type text)

Dynamic date filtering based on execution time. If you want to filter to the last 30 days relative to when the dataflow runs:

= Table.SelectRows(#"Changed Type", each [OrderDate] >= Date.From(DateTime.LocalNow() - #duration(30, 0, 0, 0)))

Parsing structured text. If a column contains JSON or delimited values embedded in text:

= Table.AddColumn(#"Previous Step", "ParsedTags", each 
    Text.Split([TagsField], "|"), 
    type list)

Note that this breaks folding. Put it after all your filter/column steps.

Merging Queries (Joins)

To join the Orders query with a Products query (from a different source, perhaps a SharePoint list), use Home > Merge Queries. Select the key columns from each side and choose the join type (Left Outer, Inner, Full Outer, etc.).

The merge generates a step like:

= Table.NestedJoin(#"Previous Step", {"ProductID"}, Products, {"ProductID"}, "Products", JoinKind.LeftOuter)

This adds a Products column of type Table. To extract the columns you want from the nested table, click the double-arrow icon in the column header and choose which fields to expand.

Parameterizing Your Queries

Parameters make your dataflows reusable and environment-aware. Create a parameter from Home > Manage Parameters > New Parameter. Give it a name (ServerName, DatabaseName, FilterDate), a type, and a default value.

Reference a parameter in your connection step by clicking the server field and selecting the parameter from the dropdown. For use in M expressions:

= Table.SelectRows(#"Changed Type", each [OrderDate] >= FilterDate)

Parameters are especially valuable when you want to template a dataflow — one definition that works across dev, test, and prod by swapping the parameter values.

Configuring Data Destinations

This is the piece that makes Dataflow Gen2 a complete ETL tool. Once your queries are clean, you configure where the output goes.

Click a query in the left panel, then find the "Data destination" section at the bottom right of the screen, or use the "Add data destination" button in the ribbon. Select your destination type — Lakehouse, Warehouse, Azure SQL Database, or Fabric KQL Database.

For a lakehouse destination:

1. Select the lakehouse in your workspace (or another workspace, as long as you have access)
2. Choose the table name — existing table or create new
3. Configure the update method

Update Methods: Replace vs. Append vs. Upsert

This is where most newcomers make critical mistakes.

Replace: Truncates the destination table and writes all rows from the query on every run. Simple, predictable, but expensive for large tables — you're rewriting everything every time.

Append: Adds new rows from the query to the existing table without touching existing rows. Good for log-like data where rows are never updated, only added.

Merge (Upsert): Matches rows by key column(s) and either updates existing rows or inserts new ones. This is what you want for slowly changing dimension (SCD Type 1) patterns. You need to specify one or more key columns that uniquely identify a row.

Schema Mapping

When your query's column names or types don't exactly match the destination table, the schema mapping dialog lets you align them. You can:

• Map source column prod_id to destination column ProductID
• Choose to include or exclude specific columns
• Override the data type inference

If you're creating a new table, the types inferred from your final step's schema become the Delta table schema. This is why explicitly setting types in your transformations matters — the schema you define in Power Query becomes the Delta Lake schema in OneLake.

Understanding what your data actually looks like in OneLake once it lands is covered in depth in the lesson on OneLake explained — Delta tables and shortcuts, which is worth reading alongside this one.

Incremental Refresh in Dataflow Gen2

Full refreshes work fine for small tables, but once your source tables grow into tens of millions of rows, you need incremental refresh — the ability to load only the new or changed rows rather than the full dataset every time.

Dataflow Gen2 supports incremental refresh through a configuration dialog accessible from the query's context menu (right-click the query > Incremental Refresh).

The setup requires:

1. A date/datetime column in your source that serves as the watermark (e.g., LastModified or OrderDate)
2. Configuring how many historical periods to store (e.g., 3 years of data in total)
3. Configuring the refresh window (e.g., refresh the last 10 days on each run to catch late-arriving data)

Under the hood, incremental refresh creates two parameters automatically: RangeStart and RangeEnd. Your M query must filter on these parameters. If you set up incremental refresh through the dialog, the filter is added automatically. If you're writing M manually, add this filter early in your query (before folding breaks):

= Table.SelectRows(Source, each [LastModified] >= RangeStart and [LastModified] < RangeEnd)

Fabric then runs multiple refresh windows in parallel, updating only the partitions that overlap the configured refresh window.

Incremental Refresh and Query Folding

Incremental refresh is useless if RangeStart and RangeEnd don't fold. If your source can't push WHERE LastModified >= @start AND LastModified < @end to the database, you're fetching the full table and filtering locally — defeating the purpose.

Always verify folding for your date filter step with a right-click > View Native Query check. If folding is broken at this step, investigate what step earlier in the chain broke it.

Scheduling and Monitoring Dataflow Gen2

A dataflow that only runs manually isn't particularly useful. From the workspace view, find your dataflow item and click the schedule icon (or go to the dataflow's settings page).

You can schedule refreshes at intervals from 30 minutes up to daily, on specific days and times. For near-real-time needs (sub-30-minute latency), Dataflow Gen2 is not the right tool — consider Event Streams or direct API integration.

For monitoring, the workspace's monitoring hub shows refresh history for all items including dataflows. Each run shows duration, rows loaded, and success/failure status. Clicking into a failed run gives you error details, though these can sometimes be vague — we'll cover common failures in the troubleshooting section.

Orchestrating Dataflow Gen2 with Data Pipelines

For complex scenarios where you need to sequence operations — for example, load raw data with Dataflow Gen2, then trigger a Spark notebook to run aggregations, then send a notification — you need to call Dataflow Gen2 from a data pipeline.

In a pipeline canvas, add a Dataflow activity. Point it to your dataflow. The pipeline then executes the dataflow synchronously and waits for it to complete before moving to the next activity. This gives you conditional logic, retry policies, and dependency management that the dataflow scheduler alone can't provide.

This is where the distinction between Fabric lakehouses and warehouses becomes architectural — if your Dataflow Gen2 writes to a lakehouse, a downstream Spark notebook can work with those Delta files natively. If it writes to a warehouse, downstream SQL scripts can pick up from there.

Advanced Patterns

Multi-Query Dataflows and Reference Queries

Rather than putting all your logic in one massive query, break it into composable pieces. A common pattern:

• Raw queries: Connect to sources, apply minimal transformations (filter, type cast), set as staged. No data destination — these are intermediaries.
• Conformed dimension queries: Reference raw queries, apply business rules, rename columns to conform with your data model. Set data destination to the relevant dimension table.
• Fact queries: Reference raw queries and dimension queries, join them, compute measures. Set data destination to the fact table.

To create a reference query, right-click an existing query and choose "Reference." The new query starts from the output of the referenced query rather than the source. Changes to the parent query flow through automatically.

This modular approach also improves editor performance — you can develop and preview individual queries without re-running the entire graph.

Handling Schema Drift

Real-world sources change their schemas. Columns get added, renamed, or dropped. Your dataflow will break if it explicitly references a column that no longer exists.

Defensive M patterns for schema drift:

// Safe column selection - only keep columns that exist
let
    Source = ...,
    ExpectedColumns = {"OrderID", "CustomerID", "ProductID", "Quantity", "UnitPrice", "OrderDate"},
    ActualColumns = Table.ColumnNames(Source),
    AvailableColumns = List.Intersect({ExpectedColumns, ActualColumns}),
    Selected = Table.SelectColumns(Source, AvailableColumns)
in
    Selected

This pattern selects only the intersection of expected and available columns, so if a column disappears it's silently omitted rather than throwing an error. Whether silence or failure is appropriate depends on your data quality requirements.

For new columns that appear in the source, a "Select columns by type" approach can automatically include all numeric or text columns without naming them explicitly.

Error Handling in M

M has native error handling with try...otherwise:

= Table.AddColumn(#"Previous Step", "SafePrice", each 
    try Number.FromText([PriceText]) otherwise null,
    type number)

This converts a text price column to a number, returning null instead of throwing an error when the text can't be parsed (which happens when the source has empty strings, currency symbols, or "N/A" values).

For entire rows, you can filter out error rows after a type-cast step:

= Table.RemoveRowsWithErrors(#"Changed Type", {"UnitPrice", "Quantity"})

Combining Gateway Sources with Cloud Sources

If your organization uses an on-premises SQL Server or Oracle database, you'll need an on-premises data gateway to connect. The gateway is configured outside of Fabric (in the Power Platform admin portal) and proxies connections between Fabric's cloud compute and your on-prem source.

The staging layer is particularly valuable here. Enable staging on your on-prem source queries. Fabric pulls the data through the gateway once and stages it in OneLake. All your transformation logic then runs against the staged copy without requiring any more gateway traffic.

Be aware that gateway connections can be a bottleneck — the gateway machine's network bandwidth and CPU limit throughput. Size your gateway appropriately for the volumes you're moving, and consider whether a full load vs. incremental load changes the gateway sizing requirements.

Hands-On Exercise

In this exercise, you'll build a Dataflow Gen2 that ingests order data from Azure SQL, enriches it with a product dimension from a CSV file, and lands a clean sales fact table in a lakehouse.

Setup: You need an Azure SQL Database with an Orders table (at minimum: OrderID int, CustomerID int, ProductID varchar(20), Quantity int, UnitPrice decimal(10,2), OrderDate date, Status varchar(50)). You also need a CSV file with product data (ProductID, ProductName, Category, SubCategory). Upload the CSV to an ADLS Gen2 container or SharePoint folder.

Step 1: Create the Dataflow Gen2

In your Fabric workspace, click New > Dataflow Gen2. Name it DF_SalesIngestion.

Step 2: Connect to Azure SQL Orders

Click Get Data > Azure SQL Database. Enter your server and database credentials. Select the Orders table. Click Transform Data.

In the Applied Steps panel, you should see Source and Navigation steps. Verify that right-clicking the Navigation step and choosing "View Native Query" shows a SQL statement — this confirms folding is active.

Now apply these transformations in order:

1. Filter Status to Confirmed and Shipped only
2. Remove any audit columns you don't need
3. Change types explicitly for OrderDate (Date), Quantity (Whole Number), UnitPrice (Decimal Number)
4. Add a custom column LineTotal = [Quantity] * [UnitPrice]

After step 3, right-click the "Changed Type" step and verify folding is still active. After step 4 (the custom column), check again — custom columns sometimes break folding depending on the expression.

Enable staging on this query by clicking the staging icon in the query panel.

Step 3: Connect to Product CSV

Click Get Data > Azure Data Lake Storage Gen2 (or SharePoint, depending on where your file is). Navigate to your product CSV. Use Combine Files if multiple files, or load the single file directly.

Apply transformations:

1. Promote first row as headers
2. Remove any unwanted columns
3. Set ProductID to type Text to match the Orders query

Enable staging on this query too.

Step 4: Merge the Queries

Select the Orders query in the left panel. Go to Home > Merge Queries > Merge Queries as New. Select Orders as the left table with ProductID as the key. Select Products as the right table with ProductID as the key. Use Left Outer join. Name this new query FactSales.

In the merged query, expand the Products column and select ProductName, Category, SubCategory. Then remove the original ProductID from Orders (since it's redundant with the joined data, depending on your model).

Step 5: Configure Data Destination

With FactSales selected, click Add Data Destination > Lakehouse. Select your lakehouse. Name the table fact_sales. Choose "Replace" as the update method for now.

Set Orders and Products to have no data destination (they're staging queries only — uncheck or remove any destination if accidentally set).

Step 6: Publish and Run

Click Publish at the top right. The dataflow is saved to the workspace. Find it in the workspace list, click the refresh icon to trigger a manual run, and then monitor the run in the monitoring hub.

After the run completes, open your lakehouse. Under Tables, you should see fact_sales with rows populated. Run a quick SQL query against it using the SQL analytics endpoint to verify row counts and column types look right.

Step 7: Add Scheduling

From the workspace list, open the dataflow settings. Set a daily refresh schedule. If this were a production scenario, you'd also wire this dataflow into a pipeline that checks for successful completion before triggering downstream notebook aggregations.

Common Mistakes & Troubleshooting

"The key didn't match any rows in the table" on Navigation Steps

This error usually means a table or view that existed when you built the dataflow no longer exists — or was renamed. Go to the Source step and re-navigate to the correct object. If this keeps happening, parameterize the table name so it's easier to update.

Refresh Fails with "Memory Exceeded" Errors

The Mashup engine has memory limits. If your query involves large in-memory joins (especially between a SQL table and a CSV file without folding), you may exceed these limits. Mitigations:

• Enable staging so individual source queries are materialized before joining
• Reduce the data volume with earlier filtering
• For truly large joins, consider whether this logic should be in a Spark notebook instead

Type Mismatch Errors at Destination

If your M query produces a Date column but the existing Delta table has it as DateTime, the write will fail. Fix options: change the type in M to match the destination, or delete and recreate the destination table with the correct schema. Check types in the data preview panel — the type icons in column headers show the inferred type.

Staging Queries Not Using Staged Data

If you enable staging on a source query but a downstream reference query still seems to hit the source directly, check that the downstream query references the staged query rather than reconnecting to the source itself. In the diagram view, you should see an arrow from the source query to the downstream query — not a new source connection.

Schedule Runs But Data Doesn't Change

Check whether your update method is set to Append when it should be Replace (or vice versa). Also verify that the source data has actually changed since the last run — especially if you have date filters that might be excluding new data due to timezone issues.

Gateway Connection Failures

On-prem gateway failures are often either authentication-related (service account password expired) or network-related (gateway machine can't reach the source host). Check the gateway status in the Power Platform admin portal. The error messages from Fabric in gateway failures are often generic — go to the gateway machine's event logs for the actual underlying error.

Query Folding Breaks Unexpectedly After an Edit

Adding a seemingly innocent transformation — like renaming a column mid-sequence — can break folding because M's folding engine can't always translate arbitrary column renames into SQL. If folding breaks after a rename, move the rename step to after all your filtering steps, so the filters still fold.

Understanding the Broader Architecture Context

Now that you understand how Dataflow Gen2 works in detail, it's worth placing it explicitly in the context of what Microsoft Fabric is as a platform — because Dataflow Gen2's role in the architecture is deliberately limited and that's a feature, not a bug.

Fabric is built around OneLake as the single, unified storage layer. Every item — lakehouses, warehouses, semantic models — ultimately reads from and writes to OneLake. Dataflow Gen2's job is to get data into OneLake in a clean, typed, Delta-formatted state. It is not designed to be a compute layer for complex transformations, a real-time processing engine, or an analytics layer. When it's used for those purposes, it tends to be slow and brittle.

The clean architectural pattern is:

1. Dataflow Gen2 ingests from external sources, applies type casting and light transformation, writes to lakehouse Tables (Delta)
2. Spark notebooks (or SQL scripts in the warehouse) handle complex transformation logic, aggregations, business rule application
3. Power BI with Direct Lake reads from the resulting Delta tables with sub-second query response

Each layer does what it's best at. Dataflow Gen2 handles the connector ecosystem and light transformation. Spark handles computation at scale. Direct Lake handles the reporting layer.

If you find yourself writing extremely complex M code to do things that SQL or PySpark would do more naturally, that's a signal to reconsider the layer boundary. Use Dataflow Gen2 for ingestion and conforming, not for the analytical heavy lifting.

Summary & Next Steps

You've now covered the full lifecycle of a Dataflow Gen2 — from connecting to diverse sources through the transformation layers, staging, incremental refresh, data destination configuration, scheduling, and troubleshooting. More importantly, you understand why the architecture works the way it does: query folding pushes work to sources, staging isolates source load and enables gateway-efficient workflows, the Mashup engine handles what SQL can't, and Delta tables in OneLake give you the versioned, schema-enforced storage that downstream tools expect.

The key principles to carry forward:

• Filter early, fold as much as possible. Every row and column you can eliminate at the source reduces what the Mashup engine has to handle.
• Use staging for external and gateway sources. It costs a bit of setup time but pays back in reliability and speed.
• Set types explicitly. Your Power Query schema becomes your Delta schema — be intentional about it.
• Know when to hand off to Spark. Dataflow Gen2 is an ingestion and light transformation tool. For heavy computation, let Spark do the work.
• Monitor folding continuously. As queries evolve, folding can silently break. Build in periodic checks.

From here, good next steps are exploring data pipelines for orchestrating multi-step workflows that include your Dataflow Gen2 runs, and building the Direct Lake-backed Power BI reports that consume the Delta tables you've just built. You might also revisit the OneLake and Delta table architecture lesson with fresh eyes — understanding how Delta Lake versioning and file layout work will help you reason about what's happening when your Dataflow Gen2 writes data, and how to optimize table sizes and partition layouts for the downstream query patterns you'll support.