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.
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.
To access the lakehouse, navigate to the "Get Data" option, then create a new shortcut and provide your ADLS credentials.
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:
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
After creating the notebook, we can see our lakehouse on the left pane, load the data into our notebook like so:
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:
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
Updated Nov 09, 2023
Version 2.0
Sally_Dabbah
Microsoft
Microsoft
