cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Step-by-Step Tutorial: Building ETLs with Microsoft Fabric
By
Sally_Dabbah
Published Jul 27 2023 01:23 PM 13.4K Views
Sally_Dabbah
Microsoft
‎Jul 27 2023 06:23 AM

Step-by-Step Tutorial: Building ETLs with Microsoft Fabric

‎Jul 27 2023 06:23 AM

Introduction:
In this comprehensive guide, we'll walk you through the process of creating Extract, Transform, Load (ETL) pipelines using Microsoft Fabric. Follow these steps to leverage the power of Fabric and efficiently manage your data workflows.

Sally_Dabbah_0-1690463932326.png

we would like to extract data from ADLS, transform it using Fabric Notebooks and then load it into Fabric Lakehouse.

Perquisites:

  • Basic knowledge in ADLS, ETL's and PySpark.
  • workspace in Microsoft Fabric and storage account in ADLS Gen 2. 

Step 1: Prepare Data

  • Save metadata to ADLS
    We utilize product data as our dataset, which is stored in ADLS as a .csv file within a subfolder named 'Product Data'.
    Category	Subcategory	Model	ListPrice	Color	StandardCost	Product	SKU	ProductKey
Components	Road Frames	HL Road Frame	1431.5	Black	868.63	HL Road Frame - Black 58	FR-R92B-58	210
Components	Road Frames	HL Road Frame	1431.5	Red	868.63	HL Road Frame - Red 58	FR-R92R-58	211
Accessories	Helmets	Sport-100	33.64	Red	12.03	Sport-100 Helmet Red	HL-U509-R	212
Accessories	Helmets	Sport-100	33.64	Red	13.88	Sport-100 Helmet Red	HL-U509-R	213
Accessories	Helmets	Sport-100	34.99	Red	13.09	Sport-100 Helmet Red	HL-U509-R	214
Accessories	Helmets	Sport-100	33.64	Black	12.03	Sport-100 Helmet Black	HL-U509	215
Accessories	Helmets	Sport-100	33.64	Black	13.88	Sport-100 Helmet Black	HL-U509	216
Accessories	Helmets	Sport-100	34.99	Black	13.09	Sport-100 Helmet Black	HL-U509	217
Clothing	Socks	Mountain Bike Socks	9.5	White	3.4	Mountain Bike Socks M	SO-B909-M	218
Clothing	Socks	Mountain Bike Socks	9.5	White	3.4	Mountain Bike Socks L	SO-B909-L	219
Accessories	Helmets	Sport-100	33.64	Blue	12.03	Sport-100 Helmet Blue	HL-U509-B	220
Accessories	Helmets	Sport-100	33.64	Blue	13.88	Sport-100 Helmet Blue	HL-U509-B	221
Accessories	Helmets	Sport-100	34.99	Blue	13.09	Sport-100 Helmet Blue	HL-U509-B	222
Clothing	Caps	Cycling Cap	8.64	Multi	5.71	AWC Logo Cap	CA-1098	223
Clothing	Caps	Cycling Cap	8.64	Multi	5.23	AWC Logo Cap	CA-1098	224
Clothing	Caps	Cycling Cap	8.99	Multi	6.92	AWC Logo Cap	CA-1098	225
Clothing	Jerseys	Long-Sleeve Logo Jersey	48.07	Multi	31.72	Long-Sleeve Logo Jersey S	LJ-0192-S	226
Clothing	Jerseys	Long-Sleeve Logo Jersey	48.07	Multi	29.08	Long-Sleeve Logo Jersey S	LJ-0192-S	227
Clothing	Jerseys	Long-Sleeve Logo Jersey	49.99	Multi	38.49	Long-Sleeve Logo Jersey S	LJ-0192-S	228
Clothing	Jerseys	Long-Sleeve Logo Jersey	48.07	Multi	31.72	Long-Sleeve Logo Jersey M	LJ-0192-M	229
Clothing	Jerseys	Long-Sleeve Logo Jersey	48.07	Multi	29.08	Long-Sleeve Logo Jersey M	LJ-0192-M	230
Clothing	Jerseys	Long-Sleeve Logo Jersey	49.99	Multi	38.49	Long-Sleeve Logo Jersey M	LJ-0192-M	231
Clothing	Jerseys	Long-Sleeve Logo Jersey	48.07	Multi	31.72	Long-Sleeve Logo Jersey L	LJ-0192-L	232
Clothing	Jerseys	Long-Sleeve Logo Jersey	48.07	Multi	29.08	Long-Sleeve Logo Jersey L	LJ-0192-L	233
Clothing	Jerseys	Long-Sleeve Logo Jersey	49.99	Multi	38.49	Long-Sleeve Logo Jersey L	LJ-0192-L	234
Clothing	Jerseys	Long-Sleeve Logo Jersey	48.07	Multi	31.72	Long-Sleeve Logo Jersey XL	LJ-0192-X	235
Clothing	Jerseys	Long-Sleeve Logo Jersey	48.07	Multi	29.08	Long-Sleeve Logo Jersey XL	LJ-0192-X	236
Clothing	Jerseys	Long-Sleeve Logo Jersey	49.99	Multi	38.49	Long-Sleeve Logo Jersey XL	LJ-0192-X	237
Components	Road Frames	HL Road Frame	1263.46	Red	747.97	HL Road Frame - Red 62	FR-R92R-62	238
Components	Road Frames	HL Road Frame	1301.36	Red	722.26	HL Road Frame - Red 62	FR-R92R-62	239
Components	Road Frames	HL Road Frame	1431.5	Red	868.63	HL Road Frame - Red 62	FR-R92R-62	240
Components	Road Frames	HL Road Frame	1263.46	Red	747.97	HL Road Frame - Red 44	FR-R92R-44	241
Components	Road Frames	HL Road Frame	1301.36	Red	722.26	HL Road Frame - Red 44	FR-R92R-44	242
Components	Road Frames	HL Road Frame	1431.5	Red	868.63	HL Road Frame - Red 44	FR-R92R-44	243
Components	Road Frames	HL Road Frame	1263.46	Red	747.97	HL Road Frame - Red 48	FR-R92R-48	244
Components	Road Frames	HL Road Frame	1301.36	Red	722.26	HL Road Frame - Red 48	FR-R92R-48	245
Components	Road Frames	HL Road Frame	1431.5	Red	868.63	HL Road Frame - Red 48	FR-R92R-48	246
Components	Road Frames	HL Road Frame	1263.46	Red	747.97	HL Road Frame - Red 52	FR-R92R-52	247
Components	Road Frames	HL Road Frame	1301.36	Red	722.26	HL Road Frame - Red 52	FR-R92R-52	248
Components	Road Frames	HL Road Frame	1431.5	Red	868.63	HL Road Frame - Red 52	FR-R92R-52	249
Components	Road Frames	HL Road Frame	1263.46	Red	747.97	HL Road Frame - Red 56	FR-R92R-56	250
Components	Road Frames	HL Road Frame	1301.36	Red	722.26	HL Road Frame - Red 56	FR-R92R-56	251
Components	Road Frames	HL Road Frame	1431.5	Red	868.63	HL Road Frame - Red 56	FR-R92R-56	252
Components	Road Frames	LL Road Frame	297.63	Black	176.2	LL Road Frame - Black 58	FR-R38B-58	253
Components	Road Frames	LL Road Frame	306.56	Black	170.14	LL Road Frame - Black 58	FR-R38B-58	254
Components	Road Frames	LL Road Frame	337.22	Black	204.63	LL Road Frame - Black 58	FR-R38B-58	255
Components	Road Frames	LL Road Frame	297.63	Black	176.2	LL Road Frame - Black 60	FR-R38B-60	256
Components	Road Frames	LL Road Frame	306.56	Black	170.14	LL Road Frame - Black 60	FR-R38B-60	257
Components	Road Frames	LL Road Frame	337.22	Black	204.63	LL Road Frame - Black 60	FR-R38B-60	258
Components	Road Frames	LL Road Frame	297.63	Black	176.2	LL Road Frame - Black 62	FR-R38B-62	259
Components	Road Frames	LL Road Frame	306.56	Black	170.14	LL Road Frame - Black 62	FR-R38B-62	260
Components	Road Frames	LL Road Frame	337.22	Black	204.63	LL Road Frame - Black 62	FR-R38B-62	261
Components	Road Frames	LL Road Frame	306.56	Red	181.49	LL Road Frame - Red 44	FR-R38R-44	262
Components	Road Frames	LL Road Frame	337.22	Red	187.16	LL Road Frame - Red 44	FR-R38R-44	263
Components	Road Frames	LL Road Frame	306.56	Red	181.49	LL Road Frame - Red 48	FR-R38R-48	264
Components	Road Frames	LL Road Frame	337.22	Red	187.16	LL Road Frame - Red 48	FR-R38R-48	265
Components	Road Frames	LL Road Frame	306.56	Red	181.49	LL Road Frame - Red 52	FR-R38R-52	266
Components	Road Frames	LL Road Frame	337.22	Red	187.16	LL Road Frame - Red 52	FR-R38R-52	267
Components	Road Frames	LL Road Frame	306.56	Red	181.49	LL Road Frame - Red 58	FR-R38R-58	268
Components	Road Frames	LL Road Frame	337.22	Red	187.16	LL Road Frame - Red 58	FR-R38R-58	269
Components	Road Frames	LL Road Frame	306.56	Red	181.49	LL Road Frame - Red 60	FR-R38R-60	270
Components	Road Frames	LL Road Frame	337.22	Red	187.16	LL Road Frame - Red 60	FR-R38R-60	271
Components	Road Frames	LL Road Frame	306.56	Red	181.49	LL Road Frame - Red 62	FR-R38R-62	272
Components	Road Frames	LL Road Frame	337.22	Red	187.16	LL Road Frame - Red 62	FR-R38R-62	273
Components	Road Frames	ML Road Frame	594.83	Red	352.14	ML Road Frame - Red 44	FR-R72R-44	274
Components	Road Frames	ML Road Frame	594.83	Red	352.14	ML Road Frame - Red 48	FR-R72R-48	275
Components	Road Frames	ML Road Frame	594.83	Red	352.14	ML Road Frame - Red 52	FR-R72R-52	276
Components	Road Frames	ML Road Frame	594.83	Red	352.14	ML Road Frame - Red 58	FR-R72R-58	277
Components	Road Frames	ML Road Frame	594.83	Red	352.14	ML Road Frame - Red 60	FR-R72R-60	278
Components	Road Frames	LL Road Frame	297.63	Black	176.2	LL Road Frame - Black 44	FR-R38B-44	279
Components	Road Frames	LL Road Frame	306.56	Black	170.14	LL Road Frame - Black 44	FR-R38B-44	280
Components	Road Frames	LL Road Frame	337.22	Black	204.63	LL Road Frame - Black 44	FR-R38B-44	281
Components	Road Frames	LL Road Frame	297.63	Black	176.2	LL Road Frame - Black 48	FR-R38B-48	282
Components	Road Frames	LL Road Frame	306.56	Black	170.14	LL Road Frame - Black 48	FR-R38B-48	283
Components	Road Frames	LL Road Frame	337.22	Black	204.63	LL Road Frame - Black 48	FR-R38B-48	284
Components	Road Frames	LL Road Frame	297.63	Black	176.2	LL Road Frame - Black 52	FR-R38B-52	285
Components	Road Frames	LL Road Frame	306.56	Black	170.14	LL Road Frame - Black 52	FR-R38B-52	286
Components	Road Frames	LL Road Frame	337.22	Black	204.63	LL Road Frame - Black 52	FR-R38B-52	287
Components	Mountain Frames	HL Mountain Frame	1204.32	Silver	623.84	HL Mountain Frame - Silver 42	FR-M94S-42	288
Components	Mountain Frames	HL Mountain Frame	1240.45	Silver	660.91	HL Mountain Frame - Silver 42	FR-M94S-42	289
Components	Mountain Frames	HL Mountain Frame	1364.5	Silver	747.2	HL Mountain Frame - Silver 42	FR-M94S-42	290
Components	Mountain Frames	HL Mountain Frame	1364.5	Silver	706.81	HL Mountain Frame - Silver 44	FR-M94S-44	291
Components	Mountain Frames	HL Mountain Frame	1364.5	Silver	706.81	HL Mountain Frame - Silver 48	FR-M94S-52	292
Components	Mountain Frames	HL Mountain Frame	1204.32	Silver	623.84	HL Mountain Frame - Silver 46	FR-M94S-46	293
Components	Mountain Frames	HL Mountain Frame	1240.45	Silver	660.91	HL Mountain Frame - Silver 46	FR-M94S-46	294
Components	Mountain Frames	HL Mountain Frame	1364.5	Silver	747.2	HL Mountain Frame - Silver 46	FR-M94S-46	295
Components	Mountain Frames	HL Mountain Frame	1191.17	Black	617.03	HL Mountain Frame - Black 42	FR-M94B-42	296
Components	Mountain Frames	HL Mountain Frame	1226.91	Black	653.7	HL Mountain Frame - Black 42	FR-M94B-42	297
Components	Mountain Frames	HL Mountain Frame	1349.6	Black	739.04	HL Mountain Frame - Black 42	FR-M94B-42	298
Components	Mountain Frames	HL Mountain Frame	1349.6	Black	699.09	HL Mountain Frame - Black 44	FR-M94B-44	299
Components	Mountain Frames	HL Mountain Frame	1349.6	Black	699.09	HL Mountain Frame - Black 48	FR-M94B-48	300
Components	Mountain Frames	HL Mountain Frame	1191.17	Black	617.03	HL Mountain Frame - Black 46	FR-M94B-46	301
Components	Mountain Frames	HL Mountain Frame	1226.91	Black	653.7	HL Mountain Frame - Black 46	FR-M94B-46	302
Components	Mountain Frames	HL Mountain Frame	1349.6	Black	739.04	HL Mountain Frame - Black 46	FR-M94B-46	303
Components	Mountain Frames	HL Mountain Frame	1191.17	Black	617.03	HL Mountain Frame - Black 38	FR-M94B-38	304
Components	Mountain Frames	HL Mountain Frame	1226.91	Black	653.7	HL Mountain Frame - Black 38	FR-M94B-38	305
Components	Mountain Frames	HL Mountain Frame	1349.6	Black	739.04	HL Mountain Frame - Black 38	FR-M94B-38	306
Components	Mountain Frames	HL Mountain Frame	1204.32	Silver	623.84	HL Mountain Frame - Silver 38	FR-M94S-38	307
Components	Mountain Frames	HL Mountain Frame	1240.45	Silver	660.91	HL Mountain Frame - Silver 38	FR-M94S-38	308
Components	Mountain Frames	HL Mountain Frame	1364.5	Silver	747.2	HL Mountain Frame - Silver 38	FR-M94S-38	309
Bikes	Road Bikes	Road-150	3578.27	Red	2171.29	Road-150 Red 62	BK-R93R-62	310
Bikes	Road Bikes	Road-150	3578.27	Red	2171.29	Road-150 Red 44	BK-R93R-44	311
Bikes	Road Bikes	Road-150	3578.27	Red	2171.29	Road-150 Red 48	BK-R93R-48	312
Bikes	Road Bikes	Road-150	3578.27	Red	2171.29	Road-150 Red 52	BK-R93R-52	313
Bikes	Road Bikes	Road-150	3578.27	Red	2171.29	Road-150 Red 56	BK-R93R-56	314
Bikes	Road Bikes	Road-450	1457.99	Red	884.71	Road-450 Red 58	BK-R68R-58	315
Bikes	Road Bikes	Road-450	1457.99	Red	884.71	Road-450 Red 60	BK-R68R-60	316
Bikes	Road Bikes	Road-450	1457.99	Red	884.71	Road-450 Red 44	BK-R68R-44	317
Bikes	Road Bikes	Road-450	1457.99	Red	884.71	Road-450 Red 48	BK-R68R-48	318
Bikes	Road Bikes	Road-450	1457.99	Red	884.71	Road-450 Red 52	BK-R68R-52	319
Bikes	Road Bikes	Road-650	699.1	Red	413.15	Road-650 Red 58	BK-R50R-58	320
Bikes	Road Bikes	Road-650	782.99	Red	486.71	Road-650 Red 58	BK-R50R-58	321
Bikes	Road Bikes	Road-650	699.1	Red	413.15	Road-650 Red 60	BK-R50R-60	322
Bikes	Road Bikes	Road-650	782.99	Red	486.71	Road-650 Red 60	BK-R50R-60	323
Bikes	Road Bikes	Road-650	699.1	Red	413.15	Road-650 Red 62	BK-R50R-62	324
Bikes	Road Bikes	Road-650	782.99	Red	486.71	Road-650 Red 62	BK-R50R-62	325
Bikes	Road Bikes	Road-650	699.1	Red	413.15	Road-650 Red 44	BK-R50R-44	326
Bikes	Road Bikes	Road-650	782.99	Red	486.71	Road-650 Red 44	BK-R50R-44	327
Bikes	Road Bikes	Road-650	699.1	Red	413.15	Road-650 Red 48	BK-R50R-48	328
Bikes	Road Bikes	Road-650	782.99	Red	486.71	Road-650 Red 48	BK-R50R-48	329
Bikes	Road Bikes	Road-650	699.1	Red	413.15	Road-650 Red 52	BK-R50R-52	330
Bikes	Road Bikes	Road-650	782.99	Red	486.71	Road-650 Red 52	BK-R50R-52	331
Bikes	Road Bikes	Road-650	699.1	Black	413.15	Road-650 Black 58	BK-R50B-58	332
Bikes	Road Bikes	Road-650	782.99	Black	486.71	Road-650 Black 58	BK-R50B-58	333
Bikes	Road Bikes	Road-650	699.1	Black	413.15	Road-650 Black 60	BK-R50B-60	334
Bikes	Road Bikes	Road-650	782.99	Black	486.71	Road-650 Black 60	BK-R50B-60	335
Bikes	Road Bikes	Road-650	699.1	Black	413.15	Road-650 Black 62	BK-R50B-62	336
Bikes	Road Bikes	Road-650	782.99	Black	486.71	Road-650 Black 62	BK-R50B-62	337
Bikes	Road Bikes	Road-650	699.1	Black	413.15	Road-650 Black 44	BK-R50B-44	338
Bikes	Road Bikes	Road-650	782.99	Black	486.71	Road-650 Black 44	BK-R50B-44	339
Bikes	Road Bikes	Road-650	699.1	Black	413.15	Road-650 Black 48	BK-R50B-48	340
Bikes	Road Bikes	Road-650	782.99	Black	486.71	Road-650 Black 48	BK-R50B-48	341
Bikes	Road Bikes	Road-650	699.1	Black	413.15	Road-650 Black 52	BK-R50B-52	342
Bikes	Road Bikes	Road-650	782.99	Black	486.71	Road-650 Black 52	BK-R50B-52	343
Bikes	Mountain Bikes	Mountain-100	3399.99	Silver	1912.15	Mountain-100 Silver 38	BK-M82S-38	344
Bikes	Mountain Bikes	Mountain-100	3399.99	Silver	1912.15	Mountain-100 Silver 42	BK-M82S-42	345
​
  • Link ADLS to Fabric Lakehouse:
    Initially, we must establish a lakehouse within our Microsoft Fabric workspace. To achieve this, navigate to "My Workspace" in the left pane, click on "Create," select "Lakehouse," and then choose a suitable name for it.

    Sally_Dabbah_0-1690435009442.png
    To access the lakehouse, navigate to the "Get Data" option, then create a new shortcut and provide your ADLS credentials.
    Sally_Dabbah_1-1690435151469.png

     

    The ADLS URL can be found in your data lake's settings under "endpoints." Copy the data lake storage URL, not the blob URL.
    like so:
    Sally_Dabbah_1-1690435414966.png


    After linking our ADLS to our lakehouse, we can see all files in our ADLS container under lakehouse. 

Step 2: Transform Data using Fabric Notebooks
In Lakehouse -> click on open notebook -> new notebook

Sally_Dabbah_0-1690436147470.png


After creating the notebook, we can see our lakehouse on the left pane, load the data into our notebook like so:

Sally_Dabbah_1-1690436262175.png


this will load our csv into spark data frame.

 

Transforming data:
Our objective is to calculate the average of the ListPrice column and the count of unique Colors per Category.

 

 

 

from pyspark.sql import *
from pyspark.sql.types import *
from notebookutils import mssparkutils
from pyspark.context import SparkContext
from pyspark.sql import SparkSession
import json

sc = SparkContext.getOrCreate()
spark = SparkSession(sc) 

output_folder = 'output/fabricETL.json' 
#click on your datalake -> Copy ABFS path -> add your suffix to it.
output_path = 'abfss://31e03aae-13c0-4c9b-a9a7-8941b7b73c98@msit-onelake.dfs.fabric.microsoft.com/0bb9d83e-3784-4860-b72c-49da115c52d3/Tables/datalakeSally/{output_folder}'

#Read Data
df = spark.read.format("csv").option("header","true").load("Tables/datalakeSally/ProductData/ProductData.csv")

#Group data and calculate average and count of the corresponding column 
df_output = df.groupBy("Category").agg({'Color':'count', 'ListPrice':'avg'})
df_output.show()

#Write output as a Json file in destination.
df_output.write.mode("overwrite").json(output_path)

 

 

 

 

Output:

Sally_Dabbah_0-1690457740129.png

In order to see the output file in your lakehouse, refresh it.

Links:
Microsoft Fabric documentation - Microsoft Fabric | Microsoft Learn 
Create a workspace - Microsoft Fabric | Microsoft Learn 


Call-To-Action:

- Make sure to establish all connections before starting to work on Fabric.
- check Linkes mentioned above.

 - Follow me for more tutorials on LinkedIn: Sally Dabbah | LinkedIn

Version history
Last update:
‎Nov 09 2023 09:37 AM
Updated by:
Labels