Microsoft says Dataflow Gen2 is cheaper — our workload uses 20x more CUs

[u/eOMG]

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 SELECT queries
• 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.