topic Staging area in fabric in Data Warehouse

Staging area in fabric

Si_7777 — Mon, 25 Nov 2024 16:34:14 GMT

New to fabric so please go gently

I currently have a Data Warehouse in SQL Server. We have a database for staging and a database for the Data Warehouse containing dimension and facts. We use SSIS to ETL the data from legacy to staging and then to the DW. I am looking to move this into the cloud preferably Fabric. I have watched allot of You Tube videos on fabric/azure/dw and all of them seem to do things different which makes me double guess myself, so can anyone help clear the mud.

For a staging area in Fabric would this be a lake-house as some videos have shown staging tables as part of the warehouse?

Re: Staging area in fabric

FabianSchut — Mon, 25 Nov 2024 17:17:50 GMT

Hi, I always use lakehouses for staging.

One advantage of lakehouses is that you can store (un)structered data files (like JSON or even images). Especially if you work with API-calls that contain complex JSON responses, it could be convenient to first store the output as a JSON file before transforming it into a delta table.

Another advantage of a lakehouse is that it natively interacts with a PySpark Notebook. I prefer to use PySpark to extract data sources. With PySpark you have all the flexibilty to connect to all (complex) data sources.

The last advantage I will mention here is the use of shortcuts. In a lakehouse, you can create shortcuts from other lakehouses or warehouses, but also other data sources outside Fabric. You can find the full list of shortcut sources here: https://learn.microsoft.com/en-us/fabric/onelake/onelake-shortcuts#types-of-shortcuts

Warehouses in Fabric lack these three features, that is why my preference is a lakehouse for staging.

Re: Staging area in fabric

Srisakthi — Tue, 26 Nov 2024 04:12:45 GMT

Hi Si_7777 ,

Do you want to completely move from legacy to cloud? or just want to keep only the data warehouse piece?

Complete Legacy to Cloud:

When you wan to completely move from legacy to cloud, you can consider the following

Staging Area : Lakehouse files/tables

Data Ingestion : To get the data from legacy source systems you can use data pipeline(check for your connector) and load it to staging area.

Data Transformation : If you want to clean and transform your data you can use notebook and persist the data to warehouse.

Consumption Area : Warehouse

Only from Warehouse to Cloud:

When you wan to move from warehouse to cloud, you can consider the following

Consumption Area : Warehouse

Data Ingestion : To get the data from warehouse systems you can use data pipeline(check for your connector) and load it to Warehouse. Assuming your warehouse is already a cleaned and ready to consume data.

Regards,

Srisakthi

Re: Staging area in fabric

Si_7777 — Tue, 26 Nov 2024 09:07:38 GMT

Hi Srisakthi

Yes eventually complete to cloud, thank you for your reply, Simon

Re: Staging area in fabric

Si_7777 — Tue, 26 Nov 2024 09:08:04 GMT

Thank you Fabian very helpful