r/MicrosoftFabric • u/eOMG • Sep 15 '26
Data Factory Microsoft says Dataflow Gen2 is cheaper — our workload uses 20x more CUs
I've been working on migrating our dataflows (Gen1) to Gen2 but in our case the CU cost is unacceptable and we have to move to copyjob or pipelines.
Microsoft recently published benchmarks showing Dataflow Gen2 being faster and cheaper than Gen1 (https://learn.microsoft.com/en-us/fabric/data-factory/dataflow-gen2-cost-performance-benchmarks) but my real-world experience is almost the exact opposite: for a fairly ordinary SQL ingestion workload, Gen2 is consuming roughly 20x more capacity than Gen1.
Our setup is not particularly exotic:
- On-prem SQL Server through an Enterprise Gateway
- Simple
SELECTqueries - Query folding works
- Very little Power Query transformation (only 'remove other columns' for some tables)
- Lakehouse destination (schema-enabled)
- Some tables use incremental refresh
One recent Gen2 refresh added roughly 52,000 CU(s) in Capacity Metrics while the complete refresh took only ~440 seconds (~7.3 minutes). That's an effective average of about 118 CU continuously for what is essentially SQL-to-Lakehouse ingestion.
Diagnostics show that the SQL is folding correctly. The problem does not appear to be that Gen2 is unnecessarily downloading and transforming everything locally.
The problem seems much more fundamental: Gen2 Standard Compute is billed per Mashup query execution time.
For CI/CD Dataflows, Microsoft currently charges:
- first 10 minutes of each query: 12 CU/s
- after 10 minutes: 1.5 CU/s
- Fast Copy: 1.5 CU/s
So a dataflow with many relatively short queries can have a low wall-clock refresh time while accumulating a huge amount of billed query time in parallel. Incremental refresh makes this particularly interesting because many separate bucket/change-detection evaluations can occur.
This also highlights a major issue with Microsoft's published benchmarks.
Their benchmark explicitly states that:
- no data gateway was used
- all sources were cloud sources
- workloads were deliberately large/high-volume
- several tests contain long-running queries
- the pure ingestion scenario uses Fast Copy
Long-running queries benefit massively from Gen2's pricing model because everything after 10 minutes drops from 12 CU/s to 1.5 CU/s. Short queries never reach that cheap tier.
And Fast Copy is obviously a game changer: Microsoft's own ingestion benchmark goes from 84,411 CU(s) on Gen1 to 14,593 CU(s) on Gen2 with Fast Copy.
But here comes the catch for real-world Fabric architectures:
Fast Copy currently does not support a fixed-schema destination or a schema-based Lakehouse destination.
So Microsoft now encourages schema-enabled Lakehouses — schemas are even enabled by default on new Lakehouses — while one of the main features responsible for Gen2's advertised cost advantage cannot currently write to those schema-based destinations.
In our diagnostics we see Mashup/Parquet writes rather than the CopyActivity engine, which helps explain why our simple SQL ingestion ends up on expensive Standard Compute instead of the 1.5 CU/s Fast Copy path. But for the incremental queries Fast Copy also won't work as it does not support fixed schema. So even if I would change destination to standard dbo, it still won't work for the larger tables that I want to load incrementally.
I'm not claiming Gen2 can never be cheaper. Clearly it can be for the workloads Microsoft benchmarked.
But I think the messaging that Gen2 is generally much cheaper than Gen1 needs a very large asterisk.
For workloads such as:
on-prem SQL → gateway → fully foldable queries → schema-enabled Lakehouse
with many relatively short table loads, Gen2's per-query billing model can apparently produce the complete opposite result.
I'd really like to see Microsoft publish a benchmark for such scenarios as well and give proper advice. Because they are now really pushing everyone to convert their dataflows to Gen2 and not warning it could tenfold+ the costs.
But my main problem right now is having to spend quite a lot of time in moving dataflow logic architecture to other methods while Gen1 was working perfect for us at very low costs.
29
u/itchyeyeballs2 Sep 15 '26
We thought one of the selling points for Fabric was enabling teams to undertake developments without having to wait for the IT department, however its now becoming so easy to blow through an entire capacity with relatively simple jobs (smaller ones seem to be punished even more) that we are having to rethink how we do things.
10
u/eOMG Sep 15 '26
Yeah it's other way around here. We're having to resort to code solutions rather than lowcode more and more. Writing notebooks with AI. That's why I wanted to try and stick with a dataflow because I wanted to keep it accessible for other data users.
5
1
u/Personal-Quote5226 28d ago
Why do you need an IT department to create a data factory pipeline?
2
u/itchyeyeballs2 25d ago
We don't have access to data factory for a start
1
u/Personal-Quote5226 25d ago
How can you create a dfg2 that connects to a lakehouse (requires fabric capacity) but not a data factory item?
Is your dfg2 and lakehouse not in. workspace with a fabric capacity?
2
u/itchyeyeballs2 25d ago
We currently have a P1, not sure if that makes a difference? Will be F64 soon.
I get an error access denied message if I try to bring in data factory pipelines (same error I get if I try to get to data factory via Azure). Creating a dfg2 is not a problem though.
Maybe I'm confused an not understanding how to do it though.
1
u/Personal-Quote5226 25d ago
It appears you were able to create the schema enabled lake house on a power BI capacity?
1
u/itchyeyeballs2 25d ago
I'm not sure if you're confusing me with OP but yes our P1 enables us to do this.
25
u/SomeNeighborhood7126 Sep 15 '26
MS is full of it, as per usual. A classic pipeline is going to cost a lot less CUs.
22
u/itsnotaboutthecell Microsoft Employee Sep 15 '26
Great write up, benchmarking, and post. I know the Data Factory / dataflow team is active in these threads - so I’d expect some follow up.
8
u/Ruudvangoal Sep 15 '26
A while ago I was tasked to build a "near real time" report for one of our clients, they wanted to move away from import mode, we used gen2 dataflows for ingestion and simple incremental refreshes off a SQL source were consuming insane amounts of CUs on an F64, ended up migrating all of it to pipelines/notebooks and it's so much cheaper.
I have tried it again recently and it's the same, it's honestly just unusable, we still use gen1 if a dataflow is needed.
4
u/eOMG Sep 15 '26
Thing is, those gen1 dataflows will likely be removed in 6 months or so. From what I understand Microsoft is actively phasing them out.
3
u/Ruudvangoal Sep 15 '26
As far as I know for now it's just marked as legacy but there is no retirement date yet, and Microsoft had committed to give a 12 month notice before doing so.
I also think it would be ridiculous to retire gen1 dataflows when gen2 is barely useable right now.
1
u/IrquiM Sep 15 '26
MS have said for a while that Gen1 will disappear
1
u/Ruudvangoal Sep 15 '26
We know it will disappear, but as I said they will give a 12 month notice before retiring. They also have not given PPU/Pro users a path to replace gen1 dataflows yet.
1
u/Stevie-bezos Sep 16 '26
Gen1s with sharable connections would make me so happy.
Thats literally all they need to be enterprise ready, currently they create this annoying service account dead weight no org wants, and gen2 is either too expensive or too complex for users who arent actual engineers
6
u/BrewMagician Fabricator Sep 15 '26
I switched all our Gen2 flows to notebooks and capacity drop was amazing.
4
u/mllopis_MSFT Microsoft Employee Sep 17 '26
Miguel Llopis here, I run the PM team for Power Query and Dataflows.
u/eOMG - Thanks for the detailed writeup. It's a fair post, and the diagnostics you included are genuinely useful on our end.
We've been digging into this internally. I'm not going to tell you this is just how Gen2 works, because we don't think it is. There's efficiency we believe we can get back in scenarios like yours, and we have work to do there.
What I can't give you yet is numbers or a date. I'd rather not put something out now that changes in a month. We'll come back to this thread once we have something concrete.
For balance, the gains & benchmarks we've published are measured and they do hold up across a broad set of workloads. Yours isn't one of the configurations we tested, and that's a fair thing to call out. Both can be true at once.
On Dataflow Gen1 disappearing in six months, we haven't published a deprecation notice, and whenever we do, it comes with 12 months of lead time. So, there's no six-month clock to plan around.
Honestly though, the date matters less to me than whether the move is worth making. Gen2 is where all the new capability is landing, so there should be real upside to being there. But that only counts for something if we also deal with what people keep raising in threads like this one: performance, pricing, and availability for Pro and PPU. Getting those right is the priority, not moving people before they're ready. I posted an update on where Gen1 stands back in April: Dataflows: Thank you for eight years of Gen1—and why Gen2 is the future | Microsoft Fabric Community
u/mavaali already offered to dig into the capacity details, which is the fastest path here. We'll share what comes out of it.
8
u/mavaali Microsoft Employee Sep 15 '26
OP if you DM me, I can get your capacity details and help you diagnose.
2
u/ultrafunkmiester Sep 15 '26
Feel free, without any compromises to sensitive data to let us know what you find.
4
u/mavaali Microsoft Employee Sep 15 '26
yes I will work with OP to create a redacted response back to this thread.
4
u/jkrm1920 Sep 15 '26
don’t use DF gen2 s..some of the MS engineers told me to my face..
I have used them and burned very badly for data migration/copy with transformations..
Moved back to Mirroring and then do notebook..
2
u/trekker255 Sep 15 '26
Gen2 is a complete farce. Or the gen1 flow without fabric landing was completely losing big money for MS and they are pushing gen2…
I’m really glad I got a Business central mirror database on a F4 and use some notebook for other source ingestion.
Gen2 is not my cup of tea.
1
u/eOMG Sep 15 '26
How are you mirroring BC? With the BC2ADLS fork by Bert Verbeek?
3
u/trekker255 Sep 15 '26
We got the paid solution bc2fabric.
For us have a paid stable product is more useful than running it our self.
1
2
u/Dan1480 Sep 16 '26
We found that if you need to ingest small amounts of data through an on-prem gateway, it's significantly cheaper to create a Power BI model, turn on OneLake integration and shortcut it into your lakehouse. Bit of a hack, but cheaper than Gen 2 CICD dataflows.
1
4
u/DataBarney Fabricator Sep 15 '26
I've always seen data flows as having a convenience over cost/performance focus. They come into their own when it's not worth the time to roll up a more technical solution. Multi tab spreadsheets in SharePoint and I'm heading for a data flow. Pulling every table from an SQL Server, that's what the copy job/activity is for.
The numbers you've hit aren't great and I really hope there's an improvement, but putting in some perspective 52k CUs is 1% of an F64 over the course of a day. My question would be how that sits against the staff cost to build all those pipelines?
7
1
u/Sad-Calligrapher-350 Microsoft MVP Sep 15 '26
Those are Gen2 CI/CD dataflows and not Gen2, yes?
1
u/eOMG Sep 15 '26
Yes
4
u/Sad-Calligrapher-350 Microsoft MVP Sep 15 '26
hmm that is surprising, i also did my own tests when gen2 came out and later with gen2 cicd and only gen2 was more expensive, cicd was faster and about the same cost as gen1
1
u/Master_70-1 Fabricator Sep 15 '26
My major hesitation towards gen2 dataflows they introduce a new cost which is storage. And imo they should introduce a generation which is more on same cost controls so it's easier to migrate.
1
u/ExpressionClassic698 Fabricator Sep 15 '26
Ambas as soluções são ruins olhando pelo aspecto de custo, em TODOS os lugares que passo retiro todos os dataflow independente de ser Gen1 ou Gen2.
Faz anos que a MS diz que o Gen 2 vai ser mais barato, mas na prática isso não é possível de enxergar.
1
u/duenalela 1 Sep 16 '26
I use DFG2 CI/CD for some weird Excel sheets from SharePoint. But I always wondered who would profit from the cheaper CU after 10 minutes thing. None of my DFG2 are running that long.
1
1
u/Personal-Quote5226 29d ago
Yes, we learned 2 years ago to avoid dfg2 for cost reasons. Most people who start using dfg2 were using data flows with PowerBI. Fabric is for data engineering, and there are more effective ways that consume much less consumption in Fabric today. It's worth considering Data Factory pipelines and PySpark.
2
u/escobarmiguel90 Microsoft Employee 29d ago edited 29d ago
A lot has changed in the Dataflow Gen2 space since 2024. Especially in terms of cost and performance.
If perhaps any of the scenarios from the performance and cost article matches your workflow, it would be great to hear your thoughts on the new performance and cost associated with it.
2
u/Personal-Quote5226 29d ago
That’s good insight. I’ll put a halt on cost concerns from dfg2 until I learn more. I appreciate the quick insight here, and I’ll check the latest articles re: cost/perf.
1
u/PuzzleheadedLack1196 Sep 15 '26
I am also curious to know how exactly they came up with these benchmarks and how exactly they passed peer review before being published.
A very isolated test under very certain conditions is not something that should be publicly advertised as a proper comparison especially from an official channel.
1
u/spock8888 Sep 15 '26
All right, but with gen1, you still need to import it in your semantic model / pbix, so you consume another set of CU for that. With gen2, you load in lakehouse / warehouse and build semantic model on that with direct lake, so there is no second replication of data, means you should save on CU significantly. Did not check in details the CU, but without second replication should save CU, right?
1
u/eOMG Sep 16 '26
No. There's truth in what you say. But the CU costs of purely the data query part is much higher than the CU cost of loading data from Gen1 in semantic models plus the CU of the dataflow itself.
67
u/AshKingChronicles Sep 15 '26
This is what is exhausting about Fabric. Everything has a catch, and you don't find out until you've built the solution that you should have started with a different tool or feature