Power BI Advanced Data Preparation - Layered Dataflows That Actually Scale

Most Power BI environments I get asked to look at have the same problem, and it is not the reports. It is the twelve different Power Query scripts that all pull from the same ERP tables, each one written by a different analyst, each one with a slightly different definition of "active customer". The finance team's numbers don't match the operations team's numbers, and the ERP admin is quietly furious because every refresh at 6am hammers the production database six times over.

Microsoft's Power BI implementation planning guidance has a usage scenario for exactly this, which they call advanced data preparation. It builds on the simpler self-service data prep scenario (one dataflow, many semantic models) and adds layers, separate workspaces, and a split between the people who prepare data and the people who model it. I want to walk through what that looks like in practice, because the documentation describes the shape well but doesn't tell you where it hurts.

What "advanced" actually means here

In the basic self-service scenario, someone builds a dataflow, it lands cleaned tables, and a few semantic models consume them. Fine for a team of five.

The advanced scenario splits the work into tiers:

• Staging dataflows that do nothing except extract raw data from source systems and land it. Minimal transformation. One trip to the source.
• Transformation dataflows that read from staging via linked tables and do the real work: cleansing, conforming, merging, applying business rules.
• Final dataflows (or the output of the transformation layer) that present tidy, documented, model-ready tables to semantic model authors.

Each tier usually lives in its own workspace. Data creators, often a small central team or a capable analyst in each business unit, own the dataflow workspaces. Semantic model creators consume the outputs and never need to touch the source systems at all.

The guidance also points out that this pattern generally needs Premium, Premium Per User, or Fabric capacity, because linked and computed tables (the bits that let one dataflow reference another without re-querying the source) are capacity features. That is worth flagging early in any business case. If you are on Pro licences only, you can approximate this but you lose most of the benefit.

Why the staging layer matters more than people think

The staging tier looks like pointless overhead when you first draw it on a whiteboard. Why land raw data and then transform it in a separate step?

Three reasons, and we've hit all of them on real projects.

First, source system load. One client, a mid-sized logistics business in Brisbane, had their on-premises SQL Server ERP getting hit by around 30 separate Power Query refreshes every morning through the gateway. Moving to a single staging dataflow per source area cut that to four queries. The DBA bought us coffee.

Second, the refresh window. When extraction and transformation are tangled together, a slow transformation holds open a connection to the source. Separate them and the source connection is short and predictable, while the heavier merges run against data already sitting in capacity storage.

Third, debugging. When a number looks wrong, you can open the staging table and see exactly what came out of the source before anyone touched it. That alone has saved us days of arguing about whether the problem was "the data" or "the logic".

Separate workspaces - yes, but don't overdo it

The guidance recommends separate workspaces for each tier, and I mostly agree. It gives you clean permission boundaries: the staging workspace can be locked down to the data team, while the final workspace grants Build access to the wider group of model authors.

Where I see this go wrong is when organisations take it too literally and end up with a workspace per tier per source per environment. Suddenly there are 40 workspaces and nobody can find anything. My rule of thumb:

• One staging workspace per major source domain (ERP, CRM, HR, for example)
• One transformation workspace per subject area (Sales, Finance, Operations)
• Dev and prod separation through deployment pipelines, not by doubling every workspace by hand

Name them consistently. Something like DF - Staging - ERP and DF - Sales - Curated is boring and that is the point.

The enhanced compute engine is your friend

When a transformation dataflow reads from a linked staging table and does merges or aggregations, the enhanced compute engine can fold those operations down into a SQL-based cache rather than doing everything in the mashup engine. On larger tables the difference is very noticeable. We've seen transformation refreshes drop from 40 minutes to under 10 just by making sure the engine was switched on and the transformations were written in a way it could fold.

A few honest caveats though. The enhanced compute engine doesn't fold everything. Custom M functions, some text operations and anything involving row-by-row logic will often fall back to the mashup engine. If you have an analyst who writes beautiful but deeply nested M, you will need to have a conversation about keeping transformations simple and fold-friendly. Also, it consumes capacity, so on a small F SKU or P1 you need to watch your capacity metrics app when you turn it on for big workloads.

Bring your own storage (ADLS Gen2)

The scenario also covers connecting dataflows to your own Azure Data Lake Storage Gen2 account rather than using Power BI's internal storage. The appeal is that the CDM folders land somewhere other tools can read them: data engineers in Azure Databricks, data scientists in notebooks, and so on.

I'm lukewarm on this for most clients now. In 2026, if you want data to be shared broadly across tools, Microsoft Fabric with a lakehouse and Dataflow Gen2 writing to OneLake is a cleaner story. Bring-your-own ADLS still works and some organisations have good reasons for it (an existing data lake, strict storage location requirements, a central platform team that owns the storage account). But I wouldn't start a new design there unless one of those reasons applies. If you're weighing up the move, our Microsoft Fabric consultants page goes into how we approach it.

People and process, which is where these projects really succeed or fail

The technology in this scenario is not hard. What breaks it is ownership.

The whole point is that a small number of trusted data creators build and maintain the curated dataflows, and a larger group of model creators reuse them. That only works if:

1. Model creators trust the data. If the first curated table they connect to has a wrong revenue figure, they will go straight back to connecting to the source themselves and you've lost them for six months.
2. Changes are communicated. Renaming a column in a transformation dataflow can break 15 downstream semantic models. You need a change process, even a lightweight one. A Teams channel and a rule that column renames need a week's notice is better than nothing.
3. Someone owns each dataflow. Not "the BI team". A named person. When it fails at 5am, who gets the email?
4. Documentation exists. Table and column descriptions in the dataflow, plus a simple catalogue of what is available. Endorsement (promoted and certified) helps model creators find the trusted stuff.

One government agency we worked with had built a technically excellent three-tier dataflow architecture, and almost nobody used it. The analysts didn't know it existed. Two lunch-and-learn sessions and a one-page "what's in the curated layer" guide got adoption from about 10% to most of the team within a couple of months. The architecture didn't change at all.

What to watch out for

A short list of things that bite people:

• Refresh chaining. Staging must finish before transformation starts. Scheduled refreshes at fixed times are fragile. Use refresh orchestration (Power Automate, Fabric pipelines, or the API) so downstream refreshes trigger when upstream ones succeed.
• Gateway bottlenecks. All on-premises extraction goes through the gateway. Consolidating into staging dataflows helps, but size the gateway properly and consider a cluster.
• Incremental refresh. On big fact tables in staging, set up incremental refresh early. Retrofitting it later means a full reload and an awkward weekend.
• Lineage view. Use it. It is genuinely good at showing what depends on what, and it is the fastest way to answer "what breaks if I change this?"
• Gen1 versus Gen2. The guidance largely describes Power BI dataflows (Gen1). If you are on Fabric capacity, think about whether new work should go into Dataflow Gen2 instead. The concepts carry over, the mechanics differ.

Is this worth it for your organisation?

If you have one or two analysts and a handful of reports, no. Use the basic self-service pattern and spend your energy on good semantic models.

If you have multiple business units building reports off the same source systems, regular arguments about whose numbers are right, or a source system owner who winces whenever Power BI is mentioned, then yes. The layered approach takes a few weeks to set up properly and pays for itself in reduced rework and fewer reconciliation meetings.

We help Australian organisations design and roll out this kind of data preparation architecture as part of our Power BI consulting work, and increasingly as a stepping stone to Fabric. If you are trying to work out whether your Power BI estate needs this level of structure, or you've built it and nobody's using it, get in touch and we can have a look.

The original Microsoft guidance is here if you want the full reference: Power BI usage scenarios - Advanced data preparation.