Dataflow Gen2 Staging

[robert-murray]

I’m working with Dataflow Gen2 and bring in tables from Azure SQL, usually just one or two per Dataflow. I like to break up my work into several linked/referenced tables/steps. Here’s a typical workflow:

1. Table/Step1: Get/Extract table(s) from Azure SQL as-is
2. Table/Step2: Apply any applicable filters
3. Table/Step3: Merge/join tables where applicable
4. Table/Step4: Transforms related to creating a unique key column (conditional, Clean, Trim, Uppercase)
5. Table/Step5: Show/keep duplicates (not referenced by other steps). Any reason to stage?
6. Table/Step6: Sort, index, remove duplicates (references Table/Step4)
7. Table/Step7: Additional transforms and column adds
8. Table/Step8: Final “NowUseThis” table (just references previous step)

By default, every table has “Staging enabled”. Everything works, but I’m wondering how it could be optimized.

Questions:

• Which steps should have staging enabled for optimal performance?
• How should I handle references to tables in different dataflows within the same workspace?
• What about references to tables in different dataflows in different workspaces?

Any advice or best practices would be greatly appreciated!

Thanks!

[Akash_Varuna · Accepted solution]

Hi @robert-murray , Use staging for reusable steps Like step 1 and step 4 for intermediate steps dont use staging
You could use ADLS or Datamart for cross workspaces
In same workspace you could go for linked tables 
If this post helped please do give a kudos and accept this as a solution
Thanks In Advance

[robert-murray]

Hi Akash,

Thanks for the reply. What are your thoughts on steps 5 to 8? In particular, as I go from Query 7 (the last query that I actually do anything in) to Query 8 which is just a reference to 7. I know I need enable staging for query 8 or I won't be able to link to it from other dataflows. So should I consider 7 intermediate and not stage?

The changes you suggested have improved Refresh times so thank you! Sure wish Microsoft would release some updated Best Practices because most of what is out there is clearly written for Gen1...

[Akash_Varuna]

Hi @robert-murray Treat 7 as intermediate unless if you are accesing its output to other workflows maybe