ETL Data flow multiple servers

To be honest, Fabric is new to me as well. And I have more of a Power BI background, so I know Power Query quite well, but I don't have so much real experience about SQL database, stored procedure, data warehouse admin, etc.

I have tested a lot of features in Fabric, but I haven't used them in production (I have only used Power BI in production).

If I understand correctly, you want to build an analytical data store in Fabric (either as a Fabric Lakehouse or Fabric Warehouse). 

I guess you have some operational systems which are your ultimate data sources. I think a common pattern is

Data source -> Pipeline (with or without a custom SQL query) -> Staging files or tables (Bronze Lakehouse) -> Notebook (ETL, upsert, etc.) -> Tables (Silver Lakehouse) -> Notebook (ETL, upsert, etc.) -> Tables (Gold Lakehouse)

or

Data source -> Pipeline (with or without a custom SQL query) -> Staging tables (Warehouse) -> Stored procedure (ETL, upsert, etc.) -> Gold Tables (Warehouse)

I think also Lakehouse (Bronze) -> Lakehouse (Silver) -> Warehouse (Gold) is a normal pattern. Here you will probably find a lot of information if you Google "Medallion architecture in Fabric".

But I think this is up to your organization to decide if you want to go all in on Lakehouse, Warehouse or a mix.

Fabric Lakehouse uses Spark language (you can choose between and mix PySpark, SparkSQL, Scala or SparkR).

Fabric Warehouse uses T-SQL language (but there are some limitations, because the underlying storage format is Delta Lake so not all T-SQL commands are available). I suppose you can Google to search for which workarounds people are using to adapt to the language limitations.

A general advice is to use the tools which work well with your current skillset (also bearing in mind which direction you want to go).

Using the Fabric Trial, you can test Fabric and its tools cost-free for a period.

So I don't think using Dataflows Gen2 is a must. Actually, my impression is that it is quite heavy on resource-consumption. My impression is that it is a tool for low-code, UI-based ETL (which I like, coming from a Power BI background).

Hopefully someone with more practical experience than me can guide you further.

I think this is a great blog (serverlesssql.com) by AndyDDC.

He can probably correct any mistakes I made in the text above here 😁  

I also like this blog: Fabric: Lakehouse or Data Warehouse? - Sam Debruyn

Although I don't think it's necessary to use both Lakehouse and Data Warehouse. It's just an option.