Step-by-Step Tutorial: Building ETLs with Microsoft Fabric
Published Jul 27 2023 06:23 AM 9,422 Views
Microsoft

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: