Get startedGet started for free

Optimizing Dataflow Gen2 performance

1. Optimizing Dataflow Gen2 performance

Now you'll find a dataflow's slow phase and match the response to its cause.

2. Find the slow phase before changing the design

Timing a refresh's three phases, Extract, Transform, and Load, shows where the time goes. To find the slow phase, open Recent runs for the dataflow from the workspace and select the run's start time. The refresh details list each table and activity with its duration. In this simplified diagram, Extract takes 3 minutes, Transform 24, and Load 4, so Transform sets the pace. Start with Transform: make one change, then refresh and compare its duration with earlier runs.

3. Reduce the load before adding complexity

Suppose a query loads 80 columns, but the reports use only 12. Remove the unused columns near the start, so later steps handle less data. In the Power Query editor, open Home and use Choose columns to select the columns to keep. Alternatively, select unwanted columns and use Remove columns. To reduce rows, use the same column filter you used to remove canceled orders previously.

4. Stage shared preparation once

Three downstream queries might each repeat the same expensive cleanup. With staging, Dataflow Gen2 stores an intermediate result in Fabric-managed staging storage, and referenced queries continue from it. Enable staging marks the shared query, while Reference creates a dependent query. Both options are on the query's right-click menu in the Queries pane. This reusable preparation can improve reliability and performance.

5. When Fast Copy applies

Dataflow Gen2 automatically uses Fast Copy, its optimized copy engine, for compatible sources. Fast Copy requires Fabric capacity and a supported connector, such as Lakehouse, Warehouse, SQL Server, PostgreSQL, Oracle, Snowflake, or ADLS Gen2, Azure's cloud file storage. The query must also use compatible transformations. For database sources, steps that fold into the source query work. For file sources, only a few steps are compatible, such as combining files, choosing columns, and changing types. If you need a Group by or merge, put it in a second query that references the ingestion query, so the ingestion query still qualifies. By default, Fabric considers Fast Copy for supported file sources of at least 100 MB or database sources with at least 5 million rows. Fast Copy also loads only to a Lakehouse destination.

6. Toggling Fast Copy

Fast Copy is on by default. You'd turn it off mainly to compare it with the standard engine when troubleshooting. To toggle it, open Options on the ribbon, select the Scale tab, and toggle Allow use of fast copy connectors. Its label varies slightly with the ribbon layout. A query can also be set to Require fast copy. This bypasses the size threshold, but the refresh fails if Fast Copy cannot be used.

7. Match the response to the cause

With the cause known, choose the response. If a query carries more data than it needs, reduce it early. If several downstream queries repeat the same preparation, stage it once. If a large load from a supported source needs only light transformations, evaluate Fast Copy. Some problems are better solved outside the dataflow. If the work needs branching, notifications, or several coordinated activities, move it into a pipeline. If you need a continuously synchronized copy of a supported source, use Fabric mirroring. If a heavy join, such as a merge with a large table, slows Transform, push it to the source as a view or a folded query. Changing tools is itself an optimization when the design doesn't fit.

8. Let's practice!

Let's practice!

Create Your Free Account

or

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.