Swapping Microsoft Fabric Dataflows Gen2 for Python Notebooks
When Microsoft changed the pricing for Dataflows Gen2 starting from August, our Fabric capacity felt it straight away. Background usage on our F8 capacity jumped, and I knew I had to do something about it.
I rewrote the main Dataflows as Python Notebooks. To fast track to the ending: we now run the process every 1 hour instead of every 2 hours, and our background usage has dropped to around 30% of what it was. Twice the refreshes, for roughly a third of the compute!
The twist? I didn't hand-write the Notebooks. I vibe-coded them with my OpenClaw agent, running on Claude Sonnet.
Now, let's go back to the beginning and I'll walk you through it.
The Problem
We had a set of Dataflows Gen2 doing a fairly ordinary job, which was to load our timesheets from a number of Excel files, loading, shaping, mapping to several dimensional datasets like rates, DevOps, Customers then landing it in the our Lakehouse. They ran every 2 hours on our F8 capacity.
Then the pricing changed. Under the current model, standard compute for a Dataflow Gen2 is charged at 12 CU per second for the first 10 minutes of each query (CI/CD dataflows), or 16 CU per second flat for non-CI/CD ones (Microsoft Learn). That first 10 minutes rate is the killer if, like us, you have lots of short queries running on a schedule.
The impact on our capacity was immediate. Background usage climbed, and even at a 2 hour schedule it was already sitting higher than I was comfortable with. I wanted to run things more often, not less, so something had to give.
Take a look at our Azure usage data, filtered specifically to Dataflows Gen2. All smooth sailing until August, then BAM! The spike started when the pricing changed, and dropped when I deployed the new Notebooks and switched off the Dataflows.
The Fix
The obvious answer was to move the logic out of Dataflows and into Python Notebooks. Why Notebooks? You pay for the compute the Notebook actually uses, and a lean bit of Python doing a focused job uses very little of it.
I wanted to make use of this AI thing going around, so here's what I did:
Copy all of the M-Code from the Dataflows & pass to the AI Prompt, which is my Openclaw agent, leveraging a number of different AI models.
Let it write the Python Notebooks, save to Azure DevOps, and sync with my Power BI workspace.
Run it, check the output against what the Dataflow produced, and feed back anything that was off.
Overall, this took a lot of iterations, more than I would have hoped. But it was still faster than hand-writing them. My oversight was to ensure best-practice in architecture, reusability, security, parameterisation, naming conventions. I did a final review with Claude's Fable model at the end, for a deep review on overall best practice, but Sonnet was plenty for the main workloads.
Once the Notebooks were working, I wired them into a Data Pipeline to handle the orchestration and scheduling. That also made it easy to move from every 2 hours to every hour.
The Impact
The change showed up in the Capacity Metrics app almost immediately. Background usage dropped to around 30% of what it was before, and that's with the process now running twice as often.
The below screenshot shows the capacity usage, the green line is when the background usage plummets to much more satisfactory levels. It's been humming around 20% background usage since then.
The Trade-offs
To be fair to Dataflows, they still have their place. They're low-code, easy for others to pick up, and Power Query is a skill a lot of teams already have. Moving to Notebooks means whoever supports this next needs to be comfortable reading Python.
I'd also be honest that vibe-coded Notebooks need a proper review before you trust them in production. The agent got me there much faster, but the checking was still on me.
Conclusion
If a pricing change has pushed your capacity up, it's worth looking at which Dataflows could be Notebooks instead. For us, it meant running twice as often for roughly a third of the background compute, and an agent did most of the rewriting.
Next port of call for me is reviewing the rest of our workloads with the same lens. For now, I'm happy with the headroom!
I'd love your thoughts & feedback, and if you'd like a hand doing the same on your own capacity, let's chat.
Comments