Dataflow staging?

[st_0999]

Hi,

I finally got a Dataflow to work today.

I had to disable Enable Staging for all tables except for the last table.

I had a few questions though:

Q1. What is the Enable Staging feature for?

Q2. Does it slow the output of the final table(s)? And best to only enable it on the the outputs? Or is it actually faster to have them switched on for everything?

Q3. Why does the Dataflow fail with Enable Staging turned on for upstream tables? Is this a licensing issue?

Q4. Is it faster to load the output tables of a Dataflow to Lakehouse tables? What's the point, if you can directly query any staged Tables in the Dataflow anyway (e.g. using the Fabric Dataflow connector in Power BI)?

Q5. Currently I can only connect to the Dataflow in Power BI, and also actually see the data. When I try the same in Excel (ie copy the script generated by Power BI, into PQ in Excel), it shows the data fields of the staged table, but in Excel the table shows up as completely empty? Any ideas? Or do I need to upgrade Excel?

Q6. Where is the data stored in a staged table in a Dataflow, or is it so fast that what I'm seeing (at least in Power BI, when I connect to it) is the output of the dataflow having refreshed from its data source? Or do I need to refresh it online again or something?

Everything works if I copy the data to Lakehouse tables and query from there (I'm only thinking that directly querying a Dataflow might be faster, so no need for the extra step to load to the lakehouse?)

Great to get some advice on best practice on the above, for a fairly large database (about 150 MB so far)

Thank you!

[st_0999]

Hi, 

Thanks for the help. For my work I decided on the following approach

- Load the output tables of a Dataflow to a LakeHouse

- Not use Staging (I discovered these get loaded to the LakeHouse anyway, in the Default Staging Lakehouse - the one you can't delete). And hence, with this, not use the Dataflows connector. Just the Lakehouse connector

- If a ETL is complex, that not using Staging results in a Stacked Overflow error (I got this several times), to:

1. Split the Query into smaller tables, and still send the Output table of the result to the Lakehouse

So effectively, I'm doing my own staging. 

I think, too, that the Excel I'm using is a bit old, but you still can't see the Fabric preview connectors, like you can with Power BI. 

Cheers, 

[miguel]

Availability of connectors relies on the product integration. You are correct that some connectors might be available in one product, but not in others, but this is by design. You can suggest new ideas to have those connectors in Excel by posting the idea on the ideas site: aka.ms/FabricIdeas

If you are seeing stackoverflow errors, please do reach out to our support team so an engineer can take a closer look at it. You can use the link below to raise a support ticket:

https://support.fabric.microsoft.com/support

[miguel]

Hi,

Apologies for the late reply on this. I'll look into why this wasn't answered earlier. Please take a look at the blog post from the link below for a more thorough explanaition on how Dataflow Gen2 works behind the scenes.

Data Factory Spotlight: Dataflow Gen2 | Blog de Microsoft Fabric | Microsoft Fabric

The above should help you with questions 1 through 4 as well as #6.

For #5, I'm unable to repro this situation. Could you please share some repro steps on how to achieve this behavior? I can copy / paste the queries from Fabric into Excel without issues.