of bulletins from the current FabCon Europe in Vienna, one which will have gone beneath the radar was in regards to the enhancements in efficiency and price optimization for Dataflows Gen2.
Earlier than we delve into explaining how these enhancements influence your present Dataflows setup, let’s take a step again and supply a short overview of Dataflows. For these of you who’re new to Microsoft Material — a Dataflow Gen2 is the no-code/low-code Material merchandise used to extract, rework, and cargo the information (ETL).
A Dataflow Gen2 supplies quite a few advantages:
- Leverage 100+ built-in connectors to extract the information from a myriad of information sources
- Leverage a well-recognized GUI from Energy Question to use dozens of transformations to the information with out writing a single line of code — a dream come true for a lot of citizen builders
- Retailer the output of information transformation as a delta desk in OneLake, in order that the reworked knowledge can be utilized downstream by varied Material engines (Spark, T-SQL, Energy BI…)
Nonetheless, simplicity normally comes with a value. Within the case of Dataflows, the associated fee was considerably greater CU consumption in comparison with code-first options, reminiscent of Material notebooks and/or T-SQL scripts. This was already well-explained and examined in two nice weblog posts written by my fellow MVPs, Gilbert Quevauvilliers (Fourmoo): Comparing Dataflow Gen2 vs Notebook on Costs and usability, and Stepan Resl: Copy Activity, Dataflows Gen2, and Notebooks vs. SharePoint Lists, so I gained’t waste time discussing the previous. As an alternative, let’s concentrate on what the current (and future) brings for Dataflows!
Adjustments to the pricing mannequin
Let’s briefly look at what’s displayed within the illustration above. Beforehand, each second of the Dataflow Gen2 run was billed at 16 CU (CU stands for Capacity Unit, representing a bundled set of resources — CPU, memory, and I/O — used in synergy to perform a specific operation). Relying on the Material capability measurement, you get a sure variety of capability items — F2 supplies 2 CUs, F4 supplies 4 CUs, and so forth.
Going again to our Dataflows state of affairs, let’s break this down by utilizing a real-life instance. Say you may have a Dataflow that runs for 20 minutes (1200 seconds)…
- Beforehand, this Dataflow run would have value you 19.200 CUs: 1200 seconds * 16 CUs
- Now, this Dataflow run will value you 8.100 CUs: 600 seconds (first 10 minutes) * 12 CUs + 600 seconds (after first 10 minutes) * 1.5 CUs
The longer your Dataflow must execute, the larger the financial savings in CUs you probably make.
That is superb by itself, however there’s nonetheless extra to that. I imply, it’s good to be charged much less for a similar quantity of labor, however what if we might make these 1200 seconds, let’s say, 800 seconds? So, it wouldn’t save us simply CUs, but additionally scale back the time-to-analysis, because the knowledge would have been processed sooner. And, that’s precisely what the subsequent two enhancements are all about…
Fashionable Evaluator
The brand new preview characteristic, named Fashionable Evaluator, permits utilizing the brand new question execution engine (working on .NET core model 8) for working Dataflows. As per the official Microsoft docs, Dataflows working the trendy evaluator can present the next advantages:
- Quicker Dataflow execution
- Extra environment friendly processing
- Scalability and reliability
The illustration above exhibits the efficiency variations between varied Dataflow “flavors”. Don’t fear, we’ll problem these numbers quickly in a demo, and I’ll additionally present you tips on how to allow these newest enhancements in your Material workloads.
Partitioned Compute
Beforehand, a Dataflow logic was executed in sequence. Therefore, relying on the logic complexity, it might take some time for sure operations to finish, in order that different operations within the Dataflow needed to wait within the queue. With the Partitioned Compute feature, Dataflow can now execute components of the transformation logic in parallel, thus decreasing the general time to finish.
At this second, there are particular limitations on when the partitioned compute will kick in. Specifically, solely ADLS Gen2, Material Lakehouse, Folder, and Azure Blob Storage connectors can leverage this new characteristic. Once more, we’ll discover the way it works later on this article.
3, 2, 1…Motion!
Okay, it’s time to problem the numbers supplied by Microsoft and verify if (and to what diploma) there’s a efficiency achieve between varied Dataflows sorts.
Right here is our state of affairs: I’ve generated 50 CSV recordsdata that comprise dummy knowledge about orders. Every file accommodates roughly 575.000 data, so there are ca. 29 million data in whole (roughly 2.5 GBs of information). All of the recordsdata are already saved within the SharePoint folder, permitting for a good comparability, as Dataflow Gen1 doesn’t help OneLake lakehouse as a knowledge supply.
I plan to run two sequence of checks: first, embrace the Dataflow Gen1 within the comparability. On this state of affairs, I gained’t be writing the information into OneLake utilizing Dataflows Gen2 (yeah, I do know, it defeats the aim of the Dataflow Gen2), as I need to evaluate “apples to apples” and exclude the time wanted for writing knowledge into OneLake. I’ll check the next 4 eventualities, during which I carry out some primary operations to mix and cargo the information, making use of some primary transformations (renaming columns, and so on.):
- Use Dataflow Gen1 (the previous Energy BI dataflow)
- Use Dataflow Gen2 with none extra optimization enhancements
- Use Dataflow Gen2 with solely the Fashionable evaluator enabled
- Use Dataflow Gen2 with each the Fashionable evaluator and Partitioned compute enabled
Within the second sequence, I’ll evaluate three flavors of Dataflow Gen2 solely (factors 2-4 from the record above), with writing the information to a lakehouse enabled.
Let’s get began!
Dataflow Gen1
The whole transformation course of within the previous Dataflow Gen1 is pretty primary — I merely mixed all 50 recordsdata right into a single question, cut up columns by delimiter, and renamed columns. So, nothing actually superior occurs right here:
The same set of operations/transformations has been applied to all three Dataflows Gen2.
Please keep in mind that with Dataflow Gen1 it’s not possible to output the data as a Delta table in OneLake. All transformations are persisted within the Dataflow itself, so when you need this data, for example, in the semantic model, you need to take into account the time and resources needed to load/refresh the data in the import mode semantic model. But, more on that later.
Dataflow Gen2 without enhancements
Let’s now do the same thing, but this time using the new Dataflow Gen2. In this first scenario, I haven’t applied any of these new performance optimization features.
Dataflow Gen2 with Modern Evaluator
Ok, the moment of truth — let’s now enable the Modern Evaluator for Dataflow Gen2. I’ll go to the Options, and then under the Scale tab, check the Allow use of the modern query evaluation engine box:
Everything else stays exactly the same as in the previous case.
Dataflow Gen2 with Modern Evaluator and Partitioned Compute
In the final example, I’ll enable both new optimization features in the Options of the Dataflow Gen2:
Now, let’s proceed to the testing and analyzing results. I will execute all four dataflows in sequence from the Fabric pipeline, so we can be sure that each of them runs in isolation from the others.
And, here are the results:
Partitioning obviously didn’t count much in this particular scenario, and I will investigate how partitioning works in more detail in one of the following articles, with different scenarios in place. Dataflow Gen2 with Modern Evaluator enabled, outperformed all the others by far, achieving 30% savings compared to the old Dataflow Gen1 and ca. 20% time savings compared to the regular Dataflow Gen2 without any optimizations! Don’t forget, these savings also reflect in the CU savings, so the final CU estimated cost for each of the used solutions is the following;
- Dataflow Gen1: 550 seconds * 12 CUs = 6.600 CUs
- Dataflow Gen2 with no optimization: 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 Partitioning: 474 seconds * 12 CUs = 5.688 CUs
However, I wanted to double-check and confirm that my calculation is accurate. Hence, I opened the Capacity Metrics App and took a look at the metrics captured by the App:
Although the overall result accurately reflects the numbers displayed in the pipeline execution log, the exact number of used CUs in the App is different:
- Dataflow Gen1: 7.788 CUs
- Dataflow Gen2 with no optimization: 5.684 CUs
- Dataflow Gen2 with Modern Evaluator: 3.565 CUs
- Dataflow Gen2 with Modern Evaluator and Partitioning: 4.732 CUs
So, according to the Capacity Metrics App, a Dataflow Gen2 with Modern Evaluator enabled consumed less than 50% of the capacity compared to the Dataflow Gen1 in this particular scenario! I plan to create more test use cases in the following days/weeks and provide a more comprehensive series of tests and comparisons, which will also include a time to write the data into OneLake (using Dataflows Gen2) versus the time needed to refresh the import mode semantic model that is using the old Dataflow Gen1.
Conclusion
When compared to other (code-first) options, Dataflows were (rightly?) considered “the slowest and least performant option” for ingesting data into Power BI/Microsoft Fabric. However, things are changing rapidly in the Fabric world, and I love how the Fabric Data Integration team makes constant improvements to the product. Honestly, I’m curious to see how Dataflows Gen2’s performance and cost develop over time, so that we can consider leveraging Dataflows not only for low-code/no-code data ingestion and data transformation requirements, but also as a viable alternative to code-first solutions from the cost/performance point of view.
Thanks for reading!
Disclaimer: I don’t have any affiliation with Microsoft (except being a Microsoft Data Platform MVP), and I haven’t been approached/sponsored by Microsoft to write this article
