From 'Dataslows' to Dataflows: The Gen2 Performance Revolution in Microsoft Fabric

of the announcements from the recent FabCon Europe in Vienna, one that may have gone under the radar was about performance enhancements and cost optimization for Dataflows Gen2.

Before we dive into explaining how these enhancements affect your Dataflows setup, let's take a step back and give a brief overview of dataflows. For those of you who are new to Microsoft Fabric — Dataflow Gen2 is a no-code/low-code Fabric object used to extract, transform, and load data (ETL).

Dataflow Gen2 offers many advantages:

• Use 100+ built-in connectors to extract data from multiple data sources
• Use the standard GUI from Power Query to apply dozens of changes to data without writing a single line of code — a a dream come true for many citizen engineers
• Store the output of the data transformation as a delta table in OneLake, so that the transformed data can be used down the Fabric engines (Spark, T-SQL, Power BI…)

However, simplicity often comes at a cost. In the case of Dataflows, the costs were much higher for CU implementation compared to source code solutions, such as Fabric notebooks and/or T-SQL scripts. This was already well explained and explored in two great blog posts written by our fellow MVPs, Gilbert Quevauvilliers (Fourmoo): Comparing Dataflow Gen2 vs Notebook on cost and usability, and Stepan Resl: Copy Work, Dataflows Gen2, and Notebooks vs SharePoint Lists, so I won't waste time discussing. Instead, let's focus on what the present (and future) brings to Dataflows!

Changes to the pricing model

Let's briefly examine what is shown in the image above. Previously, each second of Dataflow Gen2 run was billed at 16 CU (CU stands for Capacity Unit, which represents a set of combined resources – CPU, memory, and I / O – used in synergy to perform a specific task). Depending on the size of the Fabric capacity, you get a certain number of power units – F2 provides 2 CUs, F4 provides 4 CUs, and so on.

Returning to our Dataflows scenario, let's break this down using a real-life example. Say you have Dataflow running for 20 minutes (1200 seconds)…

• Previously, this implementation of Dataflow would cost you 19.200 CUs: 1200 seconds * 16 CUs
• Now, this Dataflow operation will cost you 8.100 CUs: 600 seconds (first 10 minutes) * 12 CUs + 600 seconds (after first 10 minutes) * 1.5 CUs

The longer your Dataflow needs to run, the greater the savings in CUs you can make.

This is amazing in itself, but there is more to it. I mean, it's great to be charged less for the same amount of work, but what if we could do this for 1200 seconds, let's say, 800 seconds? Therefore, it would not only save us CUs, but also reduce the analysis time, as the data would be processed faster. And, that's exactly what the next two enhancements are about…

The Modern Inspector

A new preview feature, named Modern Evaluator, allows using the new query engine (running on .NET core version 8) to use Dataflows. According to Microsoft's official documentation, Dataflows using a modern inspector can provide the following benefits:

• Faster data flow processing
• Very efficient processing
• Scalability and reliability

The figure above shows the performance differences between the various “flavors” of Dataflow. Don't worry, we'll challenge these numbers soon in a demo, and I'll show you how to enable these latest enhancements in your fabric load.

Partitioned Compute

Previously, Dataflow logic was implemented sequentially. Therefore, depending on the complexity of the concept, it can take a while for some tasks to finish, so other tasks in Dataflow are waiting in the queue. With the Partitioned Compute feature, Dataflow can now execute parts of the transformation logic in parallel, thus reducing the overall completion time.

Currently, there are some restrictions on when a classified computer will come into play. That is, only ADLS Gen2 connectors, Fabric Lakehouse, Folder, and Azure Blob Storage can use this new feature. Also, we will explore how it works later in this article.

3, 2, 1…Action!

Well, it's time to challenge the numbers provided by Microsoft and check if (and to what extent) there is a performance advantage between the various types of Dataflows.

Here's our situation: I've created 50 CSV files that contain random data about orders. Each file contains about 575.000 records, so there are ca. 29 million records in total (about 2.5 GBs of data). All files are already stored in a SharePoint folder, which allows for a correct comparison, since Dataflow Gen1 does not support the OneLake lakehouse as a data source.

I plan to do a series of two tests: first, put Dataflow Gen1 in the comparison. In this case, I will not write data to OneLake using Dataflows Gen2 (yes, I know, it defeats the purpose of Dataflow Gen2), as I want to compare “apples to apples” and reduce the time required to write data to OneLake. I will examine the following four scenarios, where I perform some basic operations to compile and load data, using basic transformations (rename columns, etc.):

1. Use Dataflow Gen1 (old Power BI dataflow)
2. Use Dataflow Gen2 without additional development enhancements
3. Use Dataflow Gen2 with only the Modern browser enabled
4. Use Dataflow Gen2 on both a Modern monitor and a Discrete enabled computer

In the second series, I will compare the three flavors of Dataflow Gen2 only (points 2-4 in the list above), by writing data to an enabled lakehouse.

Let's get started!

Data flow Gen1

The whole conversion process in the classic Dataflow Gen1 is basic – I just merge all 50 files into one query, separate the columns with a delimiter, and rename the columns. So, nothing really advanced happens here:

The same set of functions/transformations are used in all Dataflows Gen2.

Please note that with Dataflow Gen1 it is not possible to export data as a Delta table to OneLake. All changes persist within the Dataflow itself, so if you need this data, for example, in a semantic model, you need to consider the time and resources needed to load / refresh the data in the semantic model of import mode. But, more on that later.

Dataflow Gen2 without enhancements

Now let's do the same thing, but this time using the new Dataflow Gen2. In this first instance, I didn't use any of these new performance-enhancing features.

Dataflow Gen2 with Modern Evaluator

Okay, moment of truth — now let's enable the Modern Evaluator for Dataflow Gen2. I'm going to go to Options, and under the Scale tab, check the Enable the use of modern query engine check box:

Everything else remains exactly the same as before.

Dataflow Gen2 with Modern Evaluator and Partitioned Compute

In the last example, I will enable both new optimization features in Dataflow Gen2 Options:

Now, let's move on to testing and analyzing the results. I'm going to use four dataflows in sequence from the Fabric pipeline, to make sure that each works differently from the others.

And, here are the results:

Partitioning obviously doesn't count for much in this situation, and I'll explore how partitioning works in more detail in one of the next articles, with different scenarios in place. Dataflow Gen2 with Modern Evaluator enabled, outperformed all others by farachieving 30% savings compared to the old Dataflow Gen1 and ca. 20% time savings compared to standard Dataflow Gen2 without maintenance! Don't forget, this savings also represents CU savings, so the estimated final cost per CU of the solutions used is as follows;

• Data flow Gen1: 550 seconds * 12 CUs = 6.600 CUs
• Dataflow Gen2 without configuration: 520 seconds * 12 CUs = 6.240 CUs
• Dataflow Gen2 with Modern Evaluator: 368 seconds * 12 CUs = 4.416 CUs
• Dataflow Gen2 with Modern Evaluator and segmentation: 474 seconds * 12 CUs = 5.688 CUs

However, I wanted to double check and make sure my calculation was correct. So, I opened the Capacity Metrics App and looked at the metrics captured by the App:

Although the overall result accurately reflects the numbers shown in the pipeline usage log, the exact number of CUs used in the application is different:

• Data flow Gen1: 7.788 CUs
• Dataflow Gen2 without optimization: 5.684 CUs
• Dataflow Gen2 with Modern Evaluator: 3.565 CUs
• Dataflow Gen2 with Modern Evaluator and classification: 4.732 CUs

So, according to the Capacity Metrics app, Dataflow Gen2 with Modern Evaluator enabled used 50% less power compared to Dataflow Gen1 in this scenario! I plan to create more test use cases in the next days/weeks and provide a more extensive series of tests and comparisons, which will also include the time to write data to OneLake (using Dataflows Gen2) versus the time required to update the semantic model of import mode using the old Dataflow Gen1.

The conclusion

Compared to other (code-first) options, Dataflows (right?) is considered the “slowest and most inefficient option” for importing data into Power BI/Microsoft Fabric. However, things change quickly in the Fabric world, and I love how the Fabric Data Integration team is making continuous improvements to the product. Honestly, I'm curious to see how the performance and costs of Dataflows Gen2 improve over time, so that we can consider using Dataflows not only for low-code/no-code data entry and data transformation needs, but also as a viable form of code-first solutions from a cost/performance point of view.

Thanks for reading!

Disclaimer: I have no affiliation with Microsoft (other than being a Microsoft Data Platform MVP), and I was never contacted/sponsored by Microsoft to write this article.