YT YouTube2Text

YouTube2Text → Transcripts → End-to-End Azure Data Engineering Project | Build a Complete Data Pipeline from Scratch

End-to-End Azure Data Engineering Project | Build a Complete Data Pipeline from Scratch — Transcript

by Tech with Amol · 14,259 words · 2,462 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00Hello everyone, welcome to the channel.
0:03If you are learning a data engineering,
0:05you have probably come across uh
0:06technologies like Azure Data Factory,
0:09Azure Data Bricks, PySpark, Azure Data
0:11Lake Storage, etc. But knowing these
0:14technologies individually is one thing,
0:17and understanding how they work together
0:19to solve a real business problem is what
0:21actually matters when you work as a data
0:24engineer.
0:25The Think about a company that uh sells
0:28electronic products across a multiple
0:30stores. Everyday company generates uh
0:32data from different business operations.
0:35Customer purchase the product, stores
0:37process sales transaction, new products
0:39are launched, and customers raises
0:41warranty claims. Now, imagine all this
0:44information stored across the different
0:46files, datasets, applications. The
0:49business want uh wants a answer to a
0:51questions like how much revenue are we
0:54generating, which products are selling
0:56most, which stores are performing
0:58better, which products are receiving the
1:00most warranty claims. The problem is
1:03that this data isn't always available in
1:06one place. It may contain duplicates
1:08records, missing value, inconsistent
1:11column names, incorrect data types, and
1:13or many other data issues. And before
1:17anyone can use this data for analysis,
1:19someone needs to bring these together,
1:21clean it, transform it, and make it
1:23reliable. That's where data engineer
1:26comes in. A data engineer builds and
1:29maintain the pipeline that moves data
1:31from a source system into a centralized
1:33platform, transform it into a usable
1:36format, and make it available for a
1:38downstream application and analytics.
1:41And that's exactly what we are going to
1:43build in this video. We are going to
1:45create a practical data engineering
1:47pipeline using uh Azure Data Factory,
1:49Azure Data Lake Storage Gen2, and Azure
1:52Data Bricks and PySpark. We will work
1:55with a five datasets, categories,
1:58products, sales, stores, and warranty.
2:00These data sets are available in my
2:03GitHub link.
2:05I have attached that link in my comment
2:07section. So, don't worry about that.
2:10This is just about a learning individual
2:13Azure services. It's about understanding
2:16the responsibility of a data engineer
2:18and how to implement a complete
2:20ingestion and transformation workflow.
2:23Now, if you are preparing for data
2:25engineering role, this project will help
2:27you to understand several concepts that
2:29commonly come up during the technical
2:31interviews. For example, how do you
2:34design end-to-end data pipeline in
2:36Azure? How do you ingest a multiple
2:39files dynamically using Azure Data
2:41Factory? What is the difference between
2:43bronze, silver, and gold layer? How do
2:46you connect Azure Data Bricks to Azure
2:48Data Lake Storage securely? How do you
2:51handle duplicates, null values, or any
2:53other transformation questions? These
2:56are not just a theoretical questions,
2:58truly data engineering interview
3:00questions. Throughout this tutorial, we
3:02will be implementing these concepts
3:04step-by-step. So, whether you are
3:06beginner learning Azure Data Engineering
3:09or developer transitioning into a data
3:11engineering or someone preparing for
3:13technical interviews, this project will
3:16give you a practical foundation. And
3:18more importantly, you will have a
3:20working project that you can practice
3:23explaining during the interviews. Now,
3:25before we start implementing anything,
3:27let's understand the business scenario
3:30and overall architecture. Let's imagine
3:33we are working as a data engineer for a
3:34retail company that sells electronic
3:37products across different location. The
3:39company has a several business system
3:41generating data every day. For this
3:44project, we are working with a five data
3:46set. The category data set contains
3:48information about the product
3:50categories. The product data set, which
3:52gives us the details about the product.
3:55A sales data set contains the
3:56transactional information. Then a stores
3:59data set contains the information about
4:01the company stores. And finally, we have
4:04a warranty data set that contains the
4:06information about the warranty claims
4:08and repair status. Now, imagine the
4:10company wants to analyze its sales
4:13performance. The analytics team needs a
4:15reliable data to understand revenue,
4:17product performance, and store
4:19performance. But before that analysis
4:21can happen, we need to collect all these
4:24data source, store it centrally, and
4:26apply the necessary transformation.
4:28That's That's where your responsibility
4:31come in the picture as a data engineer.
4:34Let's see how we will achieve this using
4:36a Azure. The first component in our
4:38architecture is Azure Data Factory. In a
4:41real company, data might come from SQL
4:43database, ERP system, APIs, cloud
4:46application, files, etc. For this
4:49project, we are using a CSV file to
4:51represent those source system. Our first
4:54responsibility is to move this data into
4:56a cloud storage environment. And that's
4:59where Azure Data Factory comes in. Data
5:02Factory is a data ingestion and
5:04orchestration service that allows us to
5:06build pipelines for moving and
5:08processing data. Now, one approach would
5:10be to create a separate pipeline for
5:13each data set. But imagine working in a
5:15company with a 50 to 100 different
5:18source file, creating and maintaining a
5:20separate pipeline for every file would
5:23quickly become difficult. So, instead of
5:25we are going to build a dynamic reusable
5:28ingestion pipeline. We will use a
5:30lookup, for each, and copy data
5:33activities. This is an important concept
5:35because data engineers frequently need
5:37to design pipelines that can handle
5:39multiple data sets without duplicating
5:42the same logic. By the end of this
5:44section, you will understand how these
5:46activities work together to automate
5:49data ingestion.
5:50Once our data is ingested, we need to
5:53somewhere to store
5:54it. For that, we are using Azure Data
5:57Lake Storage Gen2. Think of it as a
6:00centralized storage location for our
6:02data platform. Instead of keep keeping
6:05information scattered across a different
6:08system, we will bring it into a common
6:10storage environment. Now, one important
6:12concept in modern data engineering is a
6:15Medallion architecture. In this
6:16architecture, data is organized into a
6:19different layer based on how much
6:21processing it has undergone. The three
6:24commonly used layers are bronze, silver,
6:26and gold. The bronze layer is where we
6:29store our raw data received from our
6:31source system. We are preserving the
6:33original source data so that we have a
6:36starting point for the further
6:37processing. This is very important
6:40because if something goes wrong during
6:42the transformation, we can return to our
6:44raw data and process it again. Next, we
6:47have a silver layer. This is where
6:49actual data cleaning and transformation
6:51happens. Once the transformation are
6:53completed, we will save this processed
6:55data set into a silver layer using a
6:58parquet files. This is one of the core
7:00responsibility of data engineer making
7:02sure that the data structure consistent
7:05and suitable for the downstream use. And
7:08the last layer is a gold layer. The gold
7:10layer contains curated data set designed
7:12around the business requirements. All
7:15these layer have a different different
7:18permissions, so
7:19not everyone and each person of your
7:22company will access all these data.
7:25Specific sets of people only can access
7:27the bronze layer, gold layer, and a
7:29silver layer. That is a main purpose of
7:32these three different layer.
7:35Now, let's talk about the processing
7:36component of our architecture. We have
7:39ingested our raw files into a bronze
7:41layer, but how do we actually clean
7:44transform the data? That's where
7:46Databricks comes in. Databricks is an
7:48Apache Spark-based data processing
7:51platform. It allows us to process data
7:53using a
7:54Python or SQL or PySpark. In this
7:57process, we will be using a PySpark. We
8:00will create our Databricks workspace and
8:02configure the required compute resource.
8:05Then we will establish access to our
8:07Azure Data Lake Storage using the
8:09application register in Microsoft Entra
8:12ID. Once the connection is configured,
8:15we will read our bronze data set into a
8:17Spark data frame. Then we will do some
8:20transformation and we will write this
8:22transform data back into our silver
8:24layer using a parquet format. Parquet is
8:27particularly useful for analytics
8:29workload because it supports columnar
8:31storage and efficient compression. So,
8:34the overall flow is straightforward.
8:37Azure Data Factory handles the ingestion
8:39and orchestration. Azure Data Lake
8:41Storage stores our data set and Azure
8:44Databricks perform the transformation.
8:47These are the core components of the
8:48pipelines we are going to build.
8:51Now, there's one more thing that I want
8:53you to understand before we start. When
8:55you attend a data engineering interview,
8:58the interviewer may ask, "Can you
8:59explain me end-to-end pipeline you have
9:02worked on?"
9:03A strong answer isn't simply listing the
9:06technology you have used. You need to
9:08explain the business problem, how the
9:10data was ingested, where it was stored,
9:13how the transformation applied, and how
9:16the processed data made available to
9:18different teams. And that's exactly the
9:21approach we are going to follow in this
9:23project. By the time you complete this
9:25tutorial, you will be able to explain
9:27how a retail data pipeline can be
9:29designed using Azure services. You will
9:32understand why we use a dynamic
9:35pipeline,
9:36why we separate raw and transform data,
9:39how we use a PySpark transformation, and
9:41why we choose a format such as parquet.
9:44You will also be able to discuss some of
9:46the
9:47design decisions and trade-off involved.
9:50The goal is not to just a memorize
9:53interview answers, but to understand the
9:55implementation well enough to explain
9:57confidently. So, that's the business
9:59problem we are trying to solve
10:02and the architecture we are going to
10:04implement. We will start by creating
10:06required Azure resources. Don't worry
10:08about that. I will be explaining each
10:11and every concept and every resources
10:15and how to create it, what process we
10:17need to follow, everything you will
10:19learn in this project. So, don't worry
10:21about that. So, let's get started and
10:23build our Azure data engineering
10:25pipeline.
10:28Let's start working with Azure. We are
10:31now inside the Azure portal. As you can
10:34see, there is a create a resource plus
10:36button here. I know you might be excited
10:39to click on it immediately, but before
10:41creating any resource, there is one more
10:44important step we need to complete
10:46first.
10:47We need to create a resource group. A
10:50resource group is extremely important
10:52because it act as a logical container
10:54where all your Azure resources will be
10:57live.
10:58Think of it as a folder where you are
11:00placing everything in your project. So,
11:03how do we create Azure resource group?
11:07If the resource group option is not
11:10directly visible on your screen, don't
11:12worry. Azure only shows recently used
11:15services here. Simply use the search bar
11:18at the top of the portal. This search
11:21bar helps you navigate the existing
11:23resource and also create new ones.
11:28Type resource group in the search bar
11:31and click on it. Here you will see all
11:33existing resource group. Don't worry if
11:36you see many already created. I already
11:39have some in my accounts.
11:41For this project, we will be creating a
11:44new one.
11:45Click on a create button. This opens the
11:48configuration screen for creating the
11:50resource. The configuration is very
11:54simple. First, we need to provide a name
11:57for our resource group. This name should
11:59be a something meaningful and unique.
12:02Next, you will see the subscription. You
12:05don't need to worry about this as of
12:08now. In most cases, you will have only
12:10one subscription.
12:14Let's give the resource group a name.
12:17Let's think of something unique. I will
12:19give it
12:21a short project.
12:23Azure will check whether the name is
12:26available or not.
12:28Now comes the region selection. This is
12:31a very important concept. It's always
12:34recommended to choose a region closest
12:36to you to achieve a lower latency.
12:39Since this project is not highly
12:41critical, you can choose any region.
12:44I will select East US as it works
12:47perfectly fine.
12:49Click on next to move on the tax
12:52section.
12:53Tax are used to categorize resources,
12:56usually for billing or organization
12:59purpose. For now, we can skip this
13:02section.
13:03Click on review plus create button.
13:06Azure will validate the configuration.
13:09Once validation is successful, then
13:11click on create button.
13:13Within a few seconds, the resource group
13:16will be created.
13:18Now click on the resource group to open
13:21it.
13:22As you can see, it's currently empty
13:24because we haven't added any resources
13:27yet.
13:29There are two way to create a resources.
13:32One way is to use to the create button
13:35inside the resource group,
13:37which is automatically associated
13:40new resources with this group. The
13:43second way is to create a resources from
13:45the Azure home page.
13:49Let's go back to the our Azure home tab.
13:54Uh now comes an important question. What
13:57should be the first resource we create
14:00for the data engineering project?
14:03Yes, you get guess it right. We start
14:05with a data lake.
14:09In Azure, data lake is created using a
14:12storage account. So, search for a
14:15storage account in the search bar and
14:17click on it.
14:19In Azure, data lake is created using a
14:23storage account. You will see the
14:25existing storage account if you already
14:28have some.
14:29Don't worry about those. Click on
14:31create.
14:33During the creation, Azure automatically
14:35select our resource group.
14:39If your resource group didn't exist, you
14:43could also create one from here. But,
14:46it's always best practice to create
14:49it beforehand.
14:51Uh now provide a unique name for our
14:54storage account. Storage account name
14:56must be globally unique. So,
14:59feel free add your name or suffix if
15:03needed.
15:06I will add Azure data lake 90.
15:10Uh next, keep the performance as
15:14standard.
15:22Now comes an inter inter- important
15:26interview related topic. That is
15:28redundancy.
15:30By default Azure select geo-redundant
15:33storage, which replicates the data
15:35across different region.
15:38For this project, we don't need a
15:40geo-replication.
15:41So, we will select locally redundant
15:44storage, which keeps the data replicates
15:47within the same data center.
15:50Now, here's very important question. By
15:53default, a storage account creates a
15:55blob storage, not a data lake.
15:58So, how do we enable data lake
16:00functionality?
16:02The answer is very simple. We need
16:05enable hierarchical namespace.
16:08This small checkbox is the key
16:11difference.
16:12When hierar-
16:13hierarchical namespace is enabled, the
16:16storage account become a data lake.
16:19Without it, you only get a blob storage.
16:23Now, let's understand why this matters.
16:27In blob storage, you can create
16:29containers and store files, but you
16:31cannot create folders hierarchies.
16:34In data lake, you can create folders
16:36within folders just like a file system.
16:39This hierarchical structure is extremely
16:42important when working with analytic
16:44analytics tables and large-scale data
16:46processing. If you are working with
16:49analytical data, you should always use a
16:51data lake. Now, let's move on to the
16:54access tiers. Hot tier is used when data
16:58is accessed frequently.
17:01Cool tier is used less frequent access
17:03at lower cost.
17:05Cold
17:06Cold tier tier is used for archival
17:09purpose
17:11with minimum storage, but the higher
17:14access cost. Since we will be frequently
17:18accessing our data, we will keep our
17:20tier as a hot.
17:22Now, review the configuration and click
17:24on the review plus create button. Once
17:27validation is complete, click on the
17:29create button.
17:31Azure will take few seconds to create
17:33the data lake. Once it's ready, we can
17:36see the resources is successfully
17:38created. Now, the next step is to create
17:41the Azure Data Factory. Search for Azure
17:44Data Factory in our search bar and click
17:46on it. If you don't see any existing
17:49data factories, that's perfectly fine.
17:52Click on the create button, select the
17:54name resource group because all the
17:56resources should be belong to one
17:58project. Give your data factory unique
18:01name. Again, name must be globally
18:03unique, so adjust accordingly.
18:05Once done, click on the review plus
18:08create button and then create.
18:11Azure Data Factory will deploy in a few
18:14seconds. Now, our core resources for
18:17phase one are ready.
18:20We have the resource group, data lake,
18:23and Azure Data Factory in place.
18:26Now, let's move on into Azure Data
18:28Factory.
18:30Our goal is
18:32In this phase, we will create a bronze,
18:34silver, and gold zones and start
18:36building the ingestion pipeline using
18:38Azure Data Factory.
18:40First, click on the launch studio. This
18:42will open the Azure Data Factory studio.
18:45If you are new to Azure Data Factory,
18:47you will love the UI. It's a clean,
18:50intuitive, and designed specifically for
18:53building the data pipelines.
18:55This is the home page of Azure Data
18:58Factory. For now, don't worry about the
19:01GitHub integration. We will cover that
19:03later.
19:04Let me quickly walk through walk you
19:07through the main tabs.
19:10The author tab
19:12is most important one. This is where we
19:15we will create the pipelines, dataset,
19:18link services, activities, and
19:20notebooks. Most of our work will happen
19:24there.
19:25Next, in the monitor monitor tab, this
19:28is where we track the pipeline education
19:31execution, sorry.
19:34Monitor failure and debug issues. Think
19:37of it as a control center for all your
19:40pipelines.
19:41Then we have a manage tab. This is where
19:45we configure the link services, GitHub
19:48or Azure DevOps repositories,
19:50integration runtimes, and global
19:52parameters. Finally, we have the
19:55learning center where you can explore
19:58the documentation, sample dataset, and
20:00learning resources.
20:02Now that you understand the UI, let's
20:05prepare our storage zone. We will we
20:08have to create three containers in a
20:11data lake. One is bronze, another
20:13silver, and another one is gold. Now
20:16let's go back to our Azure portal. Open
20:19your storage account and click on the
20:21containers. Create a new container named
20:24bronze. Repeat the same steps for the
20:27silver and gold containers.
20:29These containers represents different
20:31stages of the data processing and follow
20:34the Mendelian architecture pattern
20:35commonly used in a real world data
20:37engineering projects.
20:39Now that our zones are ready, it's time
20:43to start loading data using the Azure
20:45Data Factory. Let's go back to our data
20:49our Azure Data Factory Studio. To create
20:52the pipeline, click on the pipelines,
20:54then click on the three dots, and select
20:56the new pipeline.
20:58This will open our pipeline canvas.
21:02First, give your pipeline a meaningful
21:04name. Here I'm giving Git to raw.
21:08Now, let's add our first activity.
21:11From the move and transform section,
21:14drag a drag the copy
21:16copy data activity into your canvas.
21:19Rename the activity to copy
21:24copy raw data.
21:26Uh
21:27A copy activity always requires a source
21:30and sync. In our case, the source is
21:33GitHub repository and the sync is Azure
21:36Data Lake Gen2.
21:38To connect to GitHub, we need a linked
21:41service.
21:42A linked service is simply a connection
21:45Azure Data Factory uses linked service
21:47to connect to external systems like
21:49GitHub API, storage accounts.
21:53Before building a data set or pipeline,
21:55it's always a best practice to create a
21:57linked services first.
22:00Now, go to the manage tab. There you can
22:03see the linked services option.
22:05In that, just
22:07click on the new and on the right side
22:09of the panel, you will see the lot of
22:12linked services are already are there.
22:16Uh we are using a GitHub data, so we
22:19need a HTTP
22:21URL.
22:23And for that, we need quiet a HTTP
22:26your linked services. Here you can see
22:29the my GitHub data and on that, you have
22:32to click on the raw data. In the raw
22:35data, you will find the
22:37HTTP link. Now, search the HTTP.
22:41Uh click on that and on that, you will
22:45get a configurational option.
22:48Now, in the name tab, just
22:52uh type a HTTP linked service.
22:55On the base URL,
22:57uh paste the
22:59link you have copied from the GitHub.
23:02Uh here you can see the that link. Just
23:07till the github.com,
23:10just paste that.
23:13In authentication, select anonymous as a
23:16authentication type.
23:18And then click on the test connection to
23:21verify the connectivity.
23:23Once the test succeed, click click on
23:26the create button.
23:29Now, let's create another linked service
23:31for our data lake.
23:33Click on new linked service, search for
23:36the Azure Data Lake Storage Gen2, and
23:40select it.
23:42Uh give it a meaningful name, select
23:44your storage account, test the
23:46connection, and that's it. The and click
23:49the create button.
23:52Now, go back to our author tab and
23:55select the copy activity.
23:57In that, you have to select we have to
24:00create a source and sync now.
24:03In the source, just select the
24:06source format, where you will see that
24:09that we don't have anything. So, in
24:11that, just press click on the new
24:13button. In that, select the HTTP
24:16service.
24:17Once you select the HTTP service, we
24:20need to configure that.
24:23In that, now select the format as a CSV
24:26format because we have all of our data
24:28in a CSV. Just select the CSV.
24:32Now, in the name,
24:34just give DS2 uh
24:37HTTP.
24:38In the
24:40uh linked services, select our HTTP
24:44linked service, which we have created
24:46earlier.
24:47Now, we have to add the relative URL.
24:50So, real relative URL will be your
24:54dot next part of the dot com. So, go to
24:59the GitHub tab. In that,
25:02just select
25:04from name to the dot CSV till the dot
25:08CSV.
25:10Once you have selected that and pasted
25:12on into the relative tab, just click on
25:15the OK button.
25:17Okay?
25:18Now,
25:20here is the catch. If you want to see
25:22the data, how data is looking like, just
25:24click on the preview data. In that, you
25:27can see our data.
25:30Now, we will create the
25:32sync parameter.
25:34In the sync, just select we have the
25:38option of the DS HTTP, but don't select
25:41that because this is for the source one.
25:44We need to create a new one for the
25:46sync. And in the search menu, just
25:50select the
25:51Azure data Azure data lake storage gen
25:55two.
25:56Once you have selected that,
25:59then we need to
26:01select the format that again it will be
26:03stored as a CSV format. So,
26:06select the CSV.
26:08Then give a meaningful name. In that,
26:12we will write uh
26:14um
26:15uh something like
26:18DS raw, DS underscore raw. And in the
26:23linked services, we will write the
26:26storage data link.
26:28Now, we need to give the file path. So,
26:33here is the catch. We have to say that
26:36where we want to store. So, now this one
26:39is our raw data. So, we will be storing
26:41in the bronze folder. So,
26:44just select the bronze folder.
26:47Then
26:49in the another
26:51import schema, we have a three option,
26:54but we will say as none.
26:58Okay.
27:00Uh
27:01in the bronze, we are just giving a
27:04folder name as a category and then we
27:07are giving the file name as a
27:10category.csv
27:12because that file had the
27:14csv. uh
27:16Uh sorry, category.csv
27:19file name.
27:21Okay. Now in the import schema, we are
27:24selecting none. Once we have done that,
27:28just click on the okay button.
27:31Now our sync is
27:33placed.
27:35Okay. Now
27:37Uh
27:38okay. Now we have another tab that is
27:42mapping tab. So if you have already
27:45predefined mapping or we have anything
27:49already there, then we can do that. But
27:52in our case, we don't have any mapping,
27:54so just
27:56don't do anything in the mapping tab.
28:01Okay. Now
28:04moving to another
28:06part that is now let's debug our
28:10pipeline.
28:12Just play click the debug
28:15option.
28:17Now here you will see the all of our
28:20activities in the queue phase.
28:22Now we we are waiting for the green
28:26button or green indicator. So green will
28:29tell us that our activity is
28:32successfully executed.
28:34Just wait that.
28:37It will take couple of seconds because
28:40our data was very less. Yeah. So here
28:43you can see our activity is succeeded.
28:47Now we have to see the our data. So,
28:50let's go back to our
28:53Azure Data Lake
28:55storage account. In that just
28:59click on the bronze folder. In that you
29:01will see the category folder and in that
29:04you have a category.csv file. Just click
29:08on that. Here you will see the all the
29:10details about the file. If you want to
29:12see the file, then just click on the
29:14edit button. Don't change anything
29:17there.
29:18Okay, otherwise it will change our data.
29:21Just close it and again come back to our
29:26Azure
29:28Data Factory. Now click on the
29:31publish all so that it will save all of
29:34our changes all the workings.
29:37Once we have done that, now this was our
29:41static pipeline because we need to add
29:44the all the file name and everything was
29:47there. Now we we wanted to create a
29:49dynamic pipeline. So, how we will create
29:52the dynamic pipeline where it will take
29:54all the inputs from the file or all the
29:57file names. We don't have to add the
30:00file names manually. So, for that
30:03we will be doing the
30:06dynamic pipeline.
30:09Let's go back to our
30:11GitHub folder.
30:12So, there you can see we have
30:15around five files.
30:18We can see that we have
30:21product, then we have a category, sales,
30:25stores, and warranty data.
30:28So, for that we need to add the dynamic
30:30pipeline. So, we don't have to add the
30:32all the each file name and each folder
30:35name in our pipeline. So, how we will do
30:39that?
30:40Now coming back to our
30:44Azure Data Factory
30:47tab, there again from the trans move and
30:50transform, just select the copy
30:54copy data
30:56functionality. In that, now we will we
30:59have to give the unique name for that.
31:03Not unique, just give a meaningful name.
31:05So, for that we I'm giving a dynamic
31:08copy.
31:09Now, we need to create a source.
31:12Click on the new button. In that, search
31:15for HTTP and then select HTTP
31:19and then select the format as a CSV.
31:23Here, we will be giving name as a DS
31:25underscore dynamic and the linked
31:27services as a HTTP linked service.
31:31Now, we will here you can see that we
31:34have a different different files. Now,
31:37we we need to select the relative URL.
31:40So, the relative URL will be your
31:43github.com and your username.
31:46So, in the relative URL, but don't paste
31:50relative URL here.
31:53Click on the advance button.
31:57In that, select the open this data set.
32:01Once you have selected that, you will
32:03see we have a
32:05couple of options to configure.
32:07In the relative URL, if you click on
32:10that, you we will get the option of add
32:12dynamic content. We need to add the
32:15dynamic content.
32:17So, once you click that, you you will
32:19get a
32:20pipeline expression builder
32:22tab. In that, we need to create some
32:25parameters. If you
32:28search on something, you will not see
32:30anything. So, we will need to add the
32:33new parameter. In the new parameter,
32:36just
32:37uh write the name as a P underscore
32:40relative.
32:41Uh
32:42a relative URL, uh then default value
32:45none, and add okay. Now, select that P
32:49relative URL
32:51uh
32:51parameter. Once you selected that, it
32:54will appear at above text
32:57about above box.
33:00Once you selected that, just click on
33:02the okay button.
33:04Okay? Now, we have a relative URL
33:07parameters.
33:10Now, go back to our uh dynamic get to
33:13raw tab. In that in the source section,
33:17you will see that we have a P underscore
33:20relative underscore URL parameter. But,
33:23the value as a null as of now. So, don't
33:26worry about that. We will come back to
33:28that part later on.
33:30Now, move to the
33:33sync section. Just uh
33:36>> [clears throat]
33:36>> select sync. In that also, we need to
33:39create a new parameters.
33:41Uh click new, then
33:43uh Azure Data Lake Gen2, and then format
33:46as a CSV.
33:48Give
33:49name as a DS underscore uh
33:52uh dynamic
33:54uh
33:56raw. Then
33:58uh linked services, uh
34:01again, uh we need to select it as a
34:04storage Data Lake.
34:06Okay? Once you have selected that, you
34:09will see that we need to add the file
34:12path. But, uh we are creating
34:15uh we are storing all the data in our
34:17bronze layer, so we can select that as a
34:21bronze option.
34:23Um
34:24but uh we we are creating the uh dynamic
34:28parameters. So,
34:30uh go to advanced tab. In that, uh,
34:34advanced tab, you will see we have a,
34:37uh,
34:38new connection,
34:40uh, tab is there. We need to configure
34:42that. In the file path, add the, uh,
34:45bronze, uh,
34:46folder. [clears throat]
34:47And then,
34:49uh, in the directory part, uh, select
34:51the add dynamic content. In that, uh, we
34:54will create a new parameter. So, first
34:57one is p_sync_folder.
35:00Select that parameter, and that will
35:03appear in a dynamic,
35:04uh, folder parameter. Now, for the file,
35:08uh, name, uh, we will create a
35:09p_sync_file_name
35:12parameter, and that will
35:14appear under the, uh, file name, uh,
35:18uh, part.
35:19Okay? So, once you have created that,
35:22now we need to pass the values for those
35:25parameters. So, if we go back to the
35:28dynamic,
35:29uh, get to raw tab, there you will see
35:32that, uh, value as a null for both the
35:34parameter, p sync folder and p sync file
35:37name. So,
35:39how we will pass that, uh, values to
35:42our, uh, parameters?
35:44So, instead of hard coding values, we
35:47will pass them dynamically using the
35:49loop. So, drag a for each activity, and
35:53rename it as,
35:55uh, for each files.
35:57Uh, once we have done that, uh, for for
36:00for for each activities, uh, we required
36:04a array to iterate over.
36:06To provide this array, we will use the
36:08lookup activity.
36:10Now, we will creating a JSON file that
36:12contains a metadata for all files,
36:15including relative URL, target folder,
36:18and targeted file names.
36:20And then, we will upload those, uh,
36:24to our,
36:25uh, Azure Data Factory.
36:28Open any, uh, text editor.
36:31In the, uh, I'm opening, uh,
36:34PyCharm. In that, I'm creating a Git and
36:38a Git.
36:39json file.
36:40In that, uh, just, uh, create a one
36:45list. Inside the list, we need to create
36:47a multiple, uh, loops or multiple
36:50parameters. So, first I'm passing
36:53through the, uh, dictionary. So, in the
36:56dictionary, we need to pass over the
36:58three, uh, parameters. So, what was
37:01those? You can see in our, uh, Azure
37:03Data Factory. So, one was, uh, P
37:05relative URL.
37:07Then, uh, select that, paste it there.
37:10Then, uh, another one is, uh, P
37:13underscore, uh, sync folder name. And
37:15another one is P underscore file name.
37:17So, what will be our P relative URL? Go
37:20to our, uh,
37:22GitHub folder. In that, you will see
37:25this over, uh, data and select the raw.
37:28And, uh, from the, uh, URL, you will get
37:32the relative URL. Just select that and
37:34paste it in our, uh, value part.
37:37Now, we need to add the, uh, our two,
37:40uh, parameters. So, one is, uh,
37:43P sync folder and, uh, another one is,
37:46uh, P sync file name.
37:49So,
37:50what will be our P sync
37:52folder? So, in the first case, it will
37:55be a category. And, uh, P sync file name
37:59will be our, uh, file, uh, file
38:01name.csv. Because all the data we have
38:04in a CSV format.
38:06So,
38:08uh, file name will be our,
38:10uh, category.csv.
38:13Now, we need to create all these
38:15dictionary for all of our, uh, folders.
38:18So, we have our
38:20We have total five folders. And, uh,
38:23need to create the uh,
38:25all the parameters for all the folders.
38:27I'm just fast forward fast forwarding
38:31this.
38:33Once we have created all the files,
38:37it will look like this.
38:39Now, I'm just saving this and then once
38:44we have saved that, we need to upload
38:47that file in our
38:51our Azure Data Lake
38:54folder. So, in the Azure Data Lake, I'm
38:58creating one more container that is
39:01parameters and in the in the parameters,
39:04I'm just
39:06drag and drop. Just do the drag and
39:09drop. Once you have done that, now come
39:12to the our Azure Data Factory tab.
39:15Now, let's use the output of our JSON
39:19file.
39:20If we simply want to read a file and see
39:23what it contains, we can use the lookup
39:25activity in our Azure Data Factory.
39:28Lookup allows us to retrieve data from
39:31the file and use that output in other
39:34activities.
39:35So, let's add lookup activity to our
39:38pipeline. I will search for the lookup
39:41and drag it into our canvas. I will name
39:44that
39:46it as a lookup lookup kit.
39:49Now,
39:50in this setting section, we need to
39:53provide a data set because lookup needs
39:56to know which file it should read.
39:59Let's create a new data set. I will
40:02click new, select Azure Data Lake
40:04storage Gen2 and then will continue.
40:09Our file is in a JSON format, so I will
40:12select JSON and will continue.
40:15I will give the data set
40:18a name like DS
40:20get underscore parameter.
40:22For the link services, I will use the
40:25same storage account that we have
40:26configured.
40:28Now, we need to specify the file path. I
40:31will browse through the container where
40:34our JSON is stored. In this case, the
40:37file is inside our parameter container.
40:40I will select the JSON file and will
40:43click okay.
40:45That's all we need to do for the look up
40:48look up configuration.
40:50But there is one important setting that
40:53we need to understand here.
40:55By default, the look up activity is
40:57configured to return only the first row.
41:00In our scenario, that's not what we
41:02want. Our JSON file contains multiple
41:05entities and we want to retrieve all of
41:08them because the later we are going to
41:10pass these
41:12entities into our for each loop.
41:16So, we need to uncheck our first row
41:18only. This is only a small setting, but
41:21it's very important when we are building
41:23a dynamic pipeline using a look up and
41:26for each. If first row only remains
41:29enabled,
41:31look up will return only one record and
41:33our for each loop would execute only
41:36once. Now, let's see what exactly look
41:39up activity returns. For demonstration,
41:43I only want to run the particular
41:45activity. So, I will temporarily
41:47deactivated the other activities on the
41:50canvas. I will open the activity
41:53setting, go to general, and set the
41:56activity state as a deactivated.
41:59I [snorts] will do the same for other
42:01activities and I don't want to execute
42:04that. Now, only our look up activity
42:07will run. Let's trigger a debug run.
42:11Once [snorts]
42:12uh the activity succeeded, we can open
42:15the output and inspect what the lookup
42:17has written. Here, we can see the
42:20output. Under the value property, we
42:23have the list of records that we need.
42:25This is exactly what we are interested
42:28in.
42:30The value property contains an array,
42:33and uh each element in that array
42:35represent one entity.
42:38For example, one item contains the
42:40relative URL, another property contains
42:42sync folder, and another contains the
42:45file name. Our goal is to pass the
42:48entire array into the for each activity.
42:51That way, uh our for each
42:54can process each activity each entity
42:56one by one.
42:58So, let's uh close the output window and
43:02activate the uh our other activity
43:05again. Now, we need to connect the
43:07lookup activity to our for each uh
43:10activity. When we are
43:12connecting activities in ADF, we have
43:15different dependency dependency
43:18conditions, such as uh succeeded,
43:21failed, skip, and completed. In our
43:25case, we want uh if for each activity to
43:27start only when the lookup activity
43:30succeeded. So, I will select the
43:33succeeded dependency and connect the
43:35lookup to if for each.
43:39Now, let's configure the for each
43:41activity.
43:45The most important property here is
43:47items.
43:48This is where we tell for each
43:51uh which collection it needs to iterate
43:54over. Because the collection is coming
43:56from another activity, we don't want to
43:59hardcode anything. We will use a dynamic
44:02content. I will open dynamic content and
44:05select the output from our lookup
44:07activity.
44:09But, there is one important detail here.
44:11We don't want to pass the entire lookup
44:14output. We specifically need the array
44:17stored inside the value property.
44:21So, instead of using only the lookup
44:24output, we need to reference it.
44:27Output.value. This tells ADF that take
44:31the value that take the output of the
44:33lookup activity and then take the value
44:36property from the output. And that value
44:39property contains the array that we want
44:41to iterate over. Now, I will click okay.
44:45Now, our for each activity has the
44:47complete array as its input.
44:50The next step is straightforward. We
44:53need to place our copy activity inside
44:56the for each loop. I will cut the
44:58existing copy activity, open for each
45:01activity, and go to the activity
45:03section.
45:04I will click on the pencil icon and
45:08it will open the internal canvas for the
45:11for each activity. Now, we are inside
45:14the for each loop. I will paste the copy
45:17activity here. This means that copy
45:20activity will execute once for every
45:23items in the array. Now, we need to
45:25configure the parameter of our copy
45:28activity.
45:29Our source is requires values such as
45:32relative URL, sync folder, and file
45:35name.
45:35These values are coming from the current
45:38items of the for each loop. So, again,
45:41we will use the dynamic content. Inside
45:44the for each loop, the current record
45:47can be accessed using the item
45:49expression.
45:50Remember, each element in our array is
45:53one complete object. For example, one
45:56item could look like relative URL, sync
45:59folder, and file name. During the first
46:02iteration, items represent the first
46:04object. During the second
46:07iteration, it represents the second
46:09object and it is continuous until every
46:12object has been processed.
46:14For the source relative URL, we need a
46:18relative URL property from the current
46:20item. So, we will use a
46:22item.p_relativeurl.
46:25I will select that as a dynamic value.
46:28For the sync folder, we will do the same
46:30thing, but this time we will reference
46:33the sync folder property. Items. Sorry,
46:36item.p_sync_folder.
46:40And finally, for the file name, we will
46:42reference the file name property.
46:44Item.p_file_name.
46:47[clears throat]
46:48Now, our copy activity is dynamically
46:50using the values from from each items in
46:53the lookup output. This is the main
46:56concept behind the dynamic pipeline we
46:58are building. Instead of creating a
47:00separate copy activity for each and
47:02every file, we we have one copy activity
47:05inside a for each loop.
47:08The lookup provides the list. The for
47:11each iterates over the list and the copy
47:14activity processes each items using the
47:16parameter from the current iteration.
47:19So, let's run the pipeline and see what
47:21happens. I will start debug run. Now, we
47:25can monitor the execution. If something
47:29fails, that's completely fine. Debugging
47:31is a part important part of building the
47:34data pipeline. Let's monitor the
47:37execution. It might take some
47:39time, some few seconds. And now, we can
47:42see the activities completing completing
47:45successfully.
47:46This is an important lesson when we are
47:49working with a dynamic pipelines. Don't
47:51assume that every failure means the
47:54overall design is wrong. First, look at
47:56the message, then compare it with the
47:59actual data and the expression we are
48:01using. Small differences in the property
48:04names, paths, and parameter can cause
48:07the this kind of issues. Once the
48:10pipeline succeeded, we should
48:13also validate the actual output. Let's
48:16go to our storage account and open the
48:19bronze container. Here we can see the
48:21folders that are that we have created
48:24using the pipeline.
48:26Inside these folders, we should now see
48:28the files that were processed by the
48:30dynamic pipeline.
48:32Uh these all files are in a CSV format.
48:36So our first phase is now completed. We
48:40have successfully created the dynamic
48:42pipeline where lookup reads the
48:44configuration, for each it reads through
48:47the list, and a copy activity uses the
48:50value for each iteration to process the
48:52required files.
48:54This is
48:55uh more scalable than creating the
48:57separate
48:58activities manually for every file. And
49:01this pattern is very useful in
49:03real-world data engineering project
49:06because the number of files the or the
49:08entities can change without requiring us
49:11to redesign the entire pipeline.
49:14Now that our ADF part is completed, we
49:18will move to the next phase of our
49:20project. And in this next phase phase,
49:23we will start working with the Azure
49:25data file data bricks.
49:27>> [snorts]
49:27>> And continue building our end-to-end
49:29data engineering solution. Let's move on
49:32to the data bricks part.
49:35>> Great. With that, our first phase is
49:37completed. We have successfully built
49:40and tested our dynamic pipeline in Azure
49:42Data Factory.
49:44Now let's move to phase two where we
49:46will start working with Azure Data
49:48Bricks.
49:49In this phase, we will create our Data
49:51Bricks workspace, configure the compute,
49:54create the notebooks, and then we will
49:56start working with the data that just we
49:59have ingested. So, let's get started. I
50:02will go back to our Azure portal where I
50:05will click on a home button. From here,
50:08I will select a creator resource and
50:10search for Azure Databricks.
50:13I will select the Microsoft Azure
50:14Databricks service and then click
50:16create. Now, we need to configure the
50:19Databricks workspace.
50:21First, I will select a resource group
50:23that we are using for this project.
50:26For the workspace name, I will use
50:28something meaningful like ADB Azure
50:31project.
50:32You can use anything any meaningful name
50:35based on your project. Next, we need to
50:38select the pricing tier. Depending on
50:41the subscription and purpose of our
50:43environment, we get a options like
50:46standard or premium one. Here, I'm using
50:49a trial
50:50option.
50:52If you are following this as a practice
50:55project, choose the option that is
50:56available and appropriate for your
50:59subscription.
51:00Keep in mind that Databricks resource
51:02can incur cost depending on the
51:05configuration and how long we are
51:07running it.
51:08Now, we have to give the managed
51:10resource group. So, here I'm giving a
51:12managed ADB AW project. This is very
51:16important concept to understand. Azure
51:20Databricks create and manages Azure
51:23resources required to run Databricks
51:25infrastructure,
51:26including resources associated with the
51:29computing environment. You generally
51:32don't manages these resources directly.
51:35Azure Databricks manages them as a part
51:37of your workspace.
51:39Now,
51:40I will use a clear I have used a clear
51:43and meaningful name for the managed
51:45resource group so that we can easily
51:48identify it. Now let's continue with the
51:51remaining configuration. I will go
51:53through the networking setting then
51:55encryption and following the security
51:58option.
51:59For the for this
52:00practice purpose I will keep the default
52:03configuration where
52:05we need to we don't need to configure
52:07anything. Now I will go to review and
52:10create
52:11option.
52:13Azure will validate the configuration.
52:16Once a validation succeeded I will click
52:19on a create button.
52:22The Databricks workspace is now start
52:24deploying.
52:25It might take some time for everyone.
52:29While that's being created let's quickly
52:32understand what we are going to do next.
52:35Once the workspace is ready we will
52:37launch it and start working directly
52:40inside the Databricks environment.
52:43We will build everything step-by-step
52:46starting with the basic components and
52:48then moving towards the actual data
52:50transformation.
52:52This is also where you will start seeing
52:54how Databricks fix into your
52:57end-to-end Azure data engineering
53:00architecture.
53:02The ADF pipelines that we created
53:04earlier handles the ingestion and
53:06orchestration side. While Databricks
53:09will use for the processing and
53:11transformation side.
53:13So the two services work together as a
53:16overall data pipeline.
53:19The deployment is completed so let's
53:22move to Databricks resource.
53:26I will click on a go to resource button.
53:29From here I will launch a Databricks
53:31workspace and now we are inside the
53:34Azure Databricks workspace.
53:36The interface may look slightly
53:39different depending on the Databricks
53:41version and UI update, but the core
53:44concept remains the same. Before we
53:46start writing any code, let's understand
53:50and
53:51let's understand the main section we
53:53will be working on.
53:55The first important concept is compute.
53:58Compute is where our data processing
54:01workload actually run.
54:03In Databricks, this is typically
54:05represented by the compute resource or a
54:08cluster.
54:09When we execute a Spark node, perform
54:12transformation, or process the data, the
54:15compute resource provide the processing
54:17power required to execute those
54:19workloads.
54:21We also have a workspace, which is where
54:24we can organize our notebooks and
54:26development resources.
54:29Then we have a catalog, which is used
54:32for the organizing and governing data
54:35data assets such as like
54:37catalog, schema, table, or external
54:40locations, depending on the
54:42configuration
54:43of the environment. We also have a
54:46workflows,
54:48which can be used for scheduling and
54:51orchestrating the Databricks jobs.
54:54There are
54:56also areas for SQL dashboards, alerts,
54:59and other Databricks capabilities.
55:02For now, we will be focusing on the most
55:06important
55:07component
55:09that is a compute. So, let's create our
55:12compute resource.
55:14I will click on a compute. Since we
55:17don't have any compute resource yet, I
55:19will
55:20click on a create compute.
55:22Now, we need to configure our compute.
55:26First, we can provide a name. I will
55:28call it as a AW project cluster. Next,
55:33we have the compute configuration and
55:36policy option. For this practice
55:38project, I'm going to keep the
55:41configuration simple because we don't
55:43need a large cluster to process over a
55:45relatively
55:47relatively small amount of data what we
55:49are working with.
55:51I will use for single node
55:53configuration. For a smaller learning
55:56project, single node setup is usually
55:58sufficient. Next, we need to select the
56:01Databricks runtime.
56:03The runtime determines the Spark and
56:06related software environment that our
56:09workload will be using.
56:11I will select and available LTS or a
56:14long-term support runtime.
56:17The exact version you may see different
56:20depending on
56:22when you are following this tutorial.
56:25So, that's completely fine. The The
56:27important thing is to choose compatible
56:30LTS runtime that is available in your
56:33environment. Next, we have to select the
56:36node type. I will select general purpose
56:38node suitable for this practice
56:40workload. For example, DS3
56:43V2 configuration may be sufficient for
56:46this small exercise, but the available
56:49node types and pricing can be varied by
56:51the subscription and the regions.
56:54One of the most important setting here
56:56is a auto termination. So, this is very
57:00important
57:01because when you are working with a paid
57:04Azure sub the subscription, if the
57:07cluster remains running when you are not
57:09using, you may continue to incur compute
57:12cost. So, I will configure the cluster
57:15to automatically terminate after short
57:17period of
57:18inactivity.
57:19For this project, I will use around a 20
57:23minutes.
57:25You can choose different value depending
57:27on your workload. If you are a working
57:30on a real project,
57:32don't simply use a short time out
57:34without considering the workload. The
57:37setting should match how frequently the
57:39cluster is expected to be used.
57:42For now,
57:44I will leave the remaining option
57:46as default as default values.
57:50I don't need to add any tags for the for
57:53this demonstration.
57:55And I don't need any additional
57:57acceleration features for this
58:00workloads. So now I will click on a
58:02create compute. Our computer resource is
58:05being created. Once it's ready, we will
58:08able to start running Spark workloads
58:10inside the data bricks.
58:12You can also monitor the compute status
58:15and when needed impact inspect the Spark
58:19UI to understand the execution of the
58:21Spark jobs.
58:24While the computer is starting, let's
58:27understand why we are using a data
58:29bricks here.
58:30Our data factory pipeline was
58:32responsible for dynamic reading the
58:35configuration and moving the resource
58:37file into the bronze layer.
58:39Now we are moving into the processing
58:41stage. Data bricks gives us a Spark base
58:44environment where we we can use a
58:47languages like Python, SQL, Scala, or R
58:51to process our
58:52process our data and transform it.
58:55For our project, we will primarily work
58:57with a Python and PySpark along with a
59:01SQL where where we needed.
59:04We will take the data that we have
59:06landed in our storage account and start
59:08the transforming it.
59:10This is where concepts like data frames,
59:13Spark transformation, join, filters,
59:16aggregation, partition, and eventually
59:19Delta Lake will come into the picture.
59:22It's also important to understand the
59:24difference between simply learn data
59:26bricks in isolation and using a data
59:28bricks as a part of a cloud data
59:30engineering project.
59:32When you connect a data bricks
59:35to the Azure storage, you are working
59:37with the actual cloud data.
59:40You are reading the data from storage,
59:42processing it using a spark, and writing
59:45the transform results back to our
59:48appropriate layers.
59:50That gives you much better understanding
59:52of how data bricks fits into the real
59:55world of data platform.
59:57And that's exactly what we are going to
59:59build in this project. Our compute is
1:00:02now being
1:00:04prepared. Once it become available, we
1:00:07will create our first notebook and start
1:00:09working with the data.
1:00:11From this point onward, we will move
1:00:13from infrastructure setup into the
1:00:15actual data engineering work. So, let's
1:00:18continue with our first data bricks
1:00:21notebook.
1:00:25Now that we have created our Azure data
1:00:28bricks workspace and configure our
1:00:31compute, let's move to the next
1:00:32important step.
1:00:34Connecting data bricks to our Azure data
1:00:37lake storage. Before we start writing
1:00:40any transformation, we need to make sure
1:00:42that data bricks can access our data
1:00:45that we have already stored in our
1:00:46bronze layer.
1:00:48Let's understand how this work. In the
1:00:51previous section, we uh use Azure data
1:00:54factory to ingest a source file into the
1:00:56data lake storage Gen2 account.
1:00:59Our data is now available into the
1:01:01storage account. However, a data lake
1:01:04storage and data bricks are two separate
1:01:06Azure services. Just because we have
1:01:09created both the resources under the
1:01:11same Azure subscription doesn't mean
1:01:14that data bricks automatically has
1:01:16permission to access the storage
1:01:18account.
1:01:19We need to explicitly configure
1:01:21authentication and authorization. So,
1:01:24authentication verifies the identity of
1:01:27the application requesting access, while
1:01:30authorization determines what that
1:01:33application is allowed to do. For
1:01:35example, whether it can read file,
1:01:37create new files, or modify the existing
1:01:40data.
1:01:41For this project, we are going to
1:01:43configure access using the service
1:01:45principle. So, service principle is an
1:01:48identity that application and services
1:01:51can use to authenticate and access Azure
1:01:54resources.
1:01:56Think of it as a dedicated identity of
1:01:59for our Databricks workload. So, instead
1:02:02of using our personal Azure account to
1:02:05access the storage, we will create
1:02:07application identity and assign the
1:02:10required permissions.
1:02:12Then, we will configure Databricks to
1:02:15authenticate using the that identity.
1:02:19The overall process involved three
1:02:21steps. First, we will register our our
1:02:24application in a Microsoft Entra ID.
1:02:28Then, we will assign the application
1:02:31that require permission on our storage
1:02:33account. And finally, we will configure
1:02:36Databricks to use the application
1:02:38credential when accessing the Azure Data
1:02:41Lake storage.
1:02:43Let's go through these steps one by one.
1:02:46Now, I will switch back to my Azure
1:02:50portal.
1:02:52I will suggest to you to do the same. In
1:02:54the search bar, I will search for the
1:02:56Microsoft Entra ID.
1:03:00Microsoft Entra ID is a Microsoft
1:03:04cloud-based identity and access
1:03:06management service. It allows us to
1:03:08manage users, application identities,
1:03:11and authentication across the Azure
1:03:13services. I will open uh will open
1:03:17Microsoft Entra ID. On the left side
1:03:21uh we have a several options including
1:03:24users, groups, roles, and app
1:03:27registration. For our scenario, we we
1:03:30are interested in only app registration
1:03:33as of now.
1:03:34I will select app registration and I
1:03:36will click new registration. Now, Azure
1:03:40is asking us to provide a name of our
1:03:43application.
1:03:44I will name it as a
1:03:46Azure project underscore app.
1:03:50Now,
1:03:52you can choose any meaningful name based
1:03:54on your project. For the support account
1:03:57types, I will keep the default options
1:03:59for the accounts in this organizational
1:04:02directory.
1:04:03We don't need to configure a redirect
1:04:06URL because we are not building an
1:04:08interactive web application.
1:04:10Now, click on the register button.
1:04:13And that's it. Our application
1:04:15registration has been created
1:04:17successfully. Azure has also created an
1:04:20associated application identity that we
1:04:24can use for authentication. Now, let's
1:04:27look at the information available on
1:04:29this page. We have the application
1:04:32client ID, directory
1:04:34tenant ID, and object ID. These
1:04:37identifiers serves uh different
1:04:39purposes. The application ID uniquely
1:04:42identify our registered application. The
1:04:46tenant ID identifies the Microsoft Entra
1:04:49directory where the application is
1:04:51registered.
1:04:53And the object ID identifies the
1:04:55application object within the directory.
1:04:58For our Databricks connections, we'll
1:05:01primarily need application ID and tenant
1:05:04ID. Let's copy all these values and keep
1:05:07them available for the next steps. I
1:05:10will
1:05:11copy first application ID, then I will
1:05:14copy directory and tenant ID.
1:05:17One important point here is that tenant
1:05:20ID and the object ID are not
1:05:22interchangeable. So, when when
1:05:25configuring
1:05:27OAuth authentication, we must use the
1:05:30tenant ID in the app authentication
1:05:32endpoint. Now, we need one more piece of
1:05:35information that is our client secret.
1:05:38To authenticate our application, we are
1:05:40going to create a client secret.
1:05:43A client secret acts like a password for
1:05:46our application identity.
1:05:49Databricks can use the application ID,
1:05:51tenant ID, and client secrets to request
1:05:54an access token from the Microsoft Entra
1:05:57ID.
1:05:58That access token can then be used to
1:06:02access our storage account, provided the
1:06:05application has the required permission.
1:06:08So, let's create the secret. Inside our
1:06:11application registration, I will select
1:06:13certificates and secrets. Under the
1:06:15client secrets, I will click a new
1:06:17client secret. Now, we need to provide a
1:06:21description. I will use Azure project.
1:06:24Next, we need to choose an expiration
1:06:27period. For this demonstration, I will
1:06:29select a 6 months. In production
1:06:32environment, credentials should have an
1:06:34appropriate expiration and rotation
1:06:36policy.
1:06:38Now, I will click add.
1:06:40Our client secret has been created.
1:06:43Here, we can see two different fields,
1:06:45value and secret ID. This distinction is
1:06:48very important. For authentication, we
1:06:51need a value, not a secret ID. The
1:06:54secret ID is only for the identifier for
1:06:57the secret. I will copy the client
1:07:00secret value now because Azure will not
1:07:02display the full value again after
1:07:05we leave this page.
1:07:07For a real project, we should store
1:07:09these values securely in Azure Key Vault
1:07:13or another approach secret management
1:07:16solution.
1:07:17We should never expose secret in a
1:07:20source code or Git repository or public
1:07:23shared notebooks. Now, we have three
1:07:26values required for our authentication.
1:07:29Client ID,
1:07:30tenant ID, and client secret value. Our
1:07:34application identity is ready. But,
1:07:36there is one more thing we need to do
1:07:39that. So, we have to create the
1:07:41identity, but we haven't granted the
1:07:44permissions to access our storage
1:07:46account. So, let's configure that also.
1:07:49I will return to my Azure portal
1:07:52homepage. Now, I will open a storage
1:07:55account that we are using for this
1:07:56project. Remember, this is the same
1:07:59storage account
1:08:01where Azure Data Factory has already
1:08:03loaded our source files.
1:08:05Inside the storage account, I will
1:08:07select access control, that is IAM.
1:08:11IAM stands for identity and access
1:08:14management. This is where we can control
1:08:17which users and application have access
1:08:20to our Azure
1:08:22resources.
1:08:23I will click add and followed by add
1:08:27role assignment. Now, Azure displays
1:08:30several built-in roles. For our project,
1:08:33we need a role that allows our
1:08:35application to read and write the data
1:08:38into Azure Blob Storage and Data Lake
1:08:41Storage Gen2.
1:08:43I will search for storage
1:08:46blob data contributor. This role
1:08:49provides permission to read, write, and
1:08:51delete blob data. Since we will be
1:08:54reading data from the bronze layer and
1:08:56writing the transformation data into our
1:08:58silver layer, this role is suitable for
1:09:01our demonstration. In production
1:09:03environment, we should follow the
1:09:05principle of least privilege and assign
1:09:08permission only at the scope required by
1:09:11the workload.
1:09:13Uh for example, we might grant access at
1:09:16the container level rather than at the
1:09:18storage account level.
1:09:20I will select a storage blob data
1:09:22contributor and click next. Now we need
1:09:25to select the identity that should
1:09:27receive this role. So I will click on a
1:09:30select member. In the search box, I will
1:09:33search for the application we created
1:09:35earlier, that is Azure project
1:09:37underscore app.
1:09:39Here we can see our application. I will
1:09:42select it and confirm it. Now I will
1:09:45click on a review and assign button.
1:09:48Um Azure will create a role assignment.
1:09:51Once the assignment is completed, our
1:09:54application will have the required Azure
1:09:56RBAC permission.
1:09:59Keep in mind that the role assignment
1:10:01may take a few minutes to
1:10:04process. Also for Azure Data Lake
1:10:06Storage Gen2, access can depend on both
1:10:09Azure RBAC and file system ACLs.
1:10:14If you are using a more restrictive
1:10:16permission setup, you may need to
1:10:19configure those separately.
1:10:21For our
1:10:22current configuration, we will
1:10:24proceed with the assigned role.
1:10:27Now we have completed the first two
1:10:29steps. We have created the application
1:10:32identity and assigned storage
1:10:34permission. Next we will be configuring
1:10:36a connection from the Azure Databricks.
1:10:39So now let's return to our Databricks. I
1:10:43will open the Databricks workspace that
1:10:46we have created earlier. We already have
1:10:49our compute configured, so we can start
1:10:52working with the notebooks.
1:10:54First I will navigate to our workspace
1:10:56section. This is where we can organize
1:10:59our notebooks and project files.
1:11:02To keep everything structured, I will
1:11:04create a separate folder for Azure
1:11:06project. I will create a
1:11:10folder. I will name it as Azure
1:11:13underscore project. Now we have
1:11:15dedicated folder where we can store all
1:11:17of our
1:11:18notebooks related to this project. I
1:11:22I will click on create again and select
1:11:26the notebook. This opens the Databricks
1:11:29notebook interface. If you have worked
1:11:32with the Jupyter notebook before, this
1:11:34is this interface should looks familiar
1:11:37to you. Databricks notebooks allows us
1:11:39to write and execute code and in
1:11:42individual cells. We can [snorts] use a
1:11:44Python, SQL, Scala, and R depending on
1:11:47notebook and compute configuration.
1:11:51For this project, we will be mainly
1:11:52using a Python and PySpark. So let's
1:11:55start with our notebook and let's give
1:11:57it a meaningful name.
1:12:00I will
1:12:01rename it as a silver layer data
1:12:04transformation.
1:12:06This notebook will eventually contain
1:12:08the transformation required to process
1:12:10our bronze layer data and prepare the
1:12:13silver layer. So before writing any
1:12:15transformation logic, we need to connect
1:12:18notebook to our compute.
1:12:21I will click connect at the top.
1:12:23Uh here we can see the compute resources
1:12:26that we have created earlier. I will
1:12:28select the our project cluster. If the
1:12:31project cluster is stopped, Databricks
1:12:33will start it when it required. It may
1:12:36take a few minutes for the computer to
1:12:39become available. While that's
1:12:41happening, let's organize our notebook.
1:12:44One
1:12:45useful feature for Databricks notebook
1:12:48is the ability to combine code with a
1:12:50documentation.
1:12:52We can use the markdown cells to add
1:12:55headings,
1:12:56descriptions, and explanations.
1:12:59This makes our notebook easier to
1:13:01understand and maintain. I will select
1:13:04the first cell and change its type to
1:13:07from Python to markdown. Now, I will add
1:13:10a heading silver layer data
1:13:12transformation.
1:13:14>> [snorts]
1:13:14>> To create a heading in a markdown, we
1:13:17can use the hash symbol. For example,
1:13:20one hash symbol create a one-level
1:13:22heading while two or three hash symbol
1:13:26create a smaller headings. I will use a
1:13:29heading and execute the cell cell using
1:13:31a shift plus enter.
1:13:33Now, the heading is displayed in a
1:13:37formatted style. Let's add another
1:13:39section called
1:13:41In that, I will add a data loading.
1:13:44Before
1:13:45Before that, we need to access our data.
1:13:48So,
1:13:49I will again add in a new cell above and
1:13:52I will add in that
1:13:54data access using
1:13:57app. This section will contain the
1:13:59configuration required to access our
1:14:01Azure Data Lake Storage. Below that, we
1:14:04will
1:14:05we will read our
1:14:06data. We have already created our
1:14:09application in Microsoft Entra ID. We We
1:14:13have also assigned the required
1:14:15permission to our storage account. Now,
1:14:18we need to configure a Databricks to
1:14:20authenticate using the application
1:14:22identity. For this, we will be using the
1:14:25auth authentication mechanism supported
1:14:28by the our Azure Data Lake Storage Gen2.
1:14:31Rather than the Rather than writing the
1:14:34configuration entirely from memory, we
1:14:37can refer to the official Microsoft
1:14:39documentation. I will open in another
1:14:42tab and will search for it. Connect
1:14:45Azure Databricks to Azure Data Lake
1:14:48Storage Gen2 using a service principle.
1:14:51From the Microsoft documentation, we can
1:14:54find the configuration required to
1:14:56authenticate using OAuth.
1:14:59I will
1:15:00locate the service principle
1:15:02authenticate authentication example. The
1:15:05configuration uses a spark setting to
1:15:08specify the authentication type OAuth
1:15:11provider client ID, client secret, and
1:15:13token endpoint.
1:15:15I will copy the relevant example in our
1:15:18own notebook.
1:15:20Uh [snorts] don't worry about this code.
1:15:23Once you are familiar with
1:15:25all this stuff, you will automatically
1:15:28remember all this required code. So, you
1:15:31don't have to entirely
1:15:34by heart all this code.
1:15:37Now, before executing the code, we need
1:15:39to replace the placeholder values with
1:15:41our own configuration. The first value
1:15:44is the storage account name. I will
1:15:47return to the Azure portal and copy the
1:15:50name of our storage account. Now, I will
1:15:52replace the storage account placeholder
1:15:55in the configuration.
1:15:57We need to make sure that storage
1:15:58account name is correct everywhere it
1:16:01appears.
1:16:02Next, we have a application ID. I will
1:16:05use a client ID from the application
1:16:07registration we have created earlier.
1:16:10Then, we need a tenant ID. I will copy
1:16:13the directory tenant ID from our
1:16:17Microsoft Entra ID. This will be used to
1:16:21construct OAuth token endpoint. Finally,
1:16:24we need to
1:16:25copy client secret. For this demonstra-
1:16:28demonstration, the configuration expects
1:16:31the client secret value that we
1:16:33generated earlier. However, instead of
1:16:37placing the secret directly into our
1:16:39notebook, the recommended approach is to
1:16:42retrieve it securely using the
1:16:44Databricks secret scope or another
1:16:47supported secret management mechanism.
1:16:50This
1:16:52This will keep sensitive credential out
1:16:54of the notebook source code. Now, let's
1:16:57understand what each configuration
1:17:00property does. The first property
1:17:03specifies the authentication type. We
1:17:06are using OAuth because we want to
1:17:09authenticate through Microsoft Entra ID.
1:17:12The second property identifies the OAuth
1:17:16provider. The third property provide
1:17:19provides our application client ID. And
1:17:22the fourth property supplies the client
1:17:24secret use used for our authentication.
1:17:29And the final property specify the OAuth
1:17:33token endpoint associated with our
1:17:35Microsoft Entra tenant.
1:17:37Together, these setting allows Spark to
1:17:40authenticate when accessing files stored
1:17:43in our Azure storage
1:17:46Azure Data Lake storage account.
1:17:49Once the configuration is complete, we
1:17:51can execute the cell.
1:17:53If the credential and the permission are
1:17:56configured correctly, Databricks will be
1:17:58able to authenticate to the storage
1:18:00account. Of course,
1:18:02configuration authentication is only the
1:18:05first part.
1:18:06We We should also verify that we have
1:18:10actually accessed the data. In the next
1:18:13step, we will be reading the file from
1:18:15our bronze container and confirm that
1:18:18the connection is working.
1:18:21After that, we will
1:18:22begin loading the data into the Spark
1:18:25data frame and move towards the our
1:18:28silver layer transformation.
1:18:30Now that we have configured the storage
1:18:33access, let's start working with our
1:18:35Databricks.
1:18:36As you remember in a first phase of our
1:18:39project, we use a data factory to ingest
1:18:42multiple CSV file into our bronze layer.
1:18:45Uh
1:18:46now, uh we will be using a PySpark to
1:18:49read those file into data bricks,
1:18:51explore the data, and prepare it for our
1:18:53upcoming transformation.
1:18:55Let's start with the category data set.
1:18:58I will create a new Python cell under
1:19:00the data loading section. To read the
1:19:02CSV file, we will be using a spark.read.
1:19:06Now, one important thing, there is a
1:19:08file there is the file path. Since our
1:19:10data is stored in a Azure Data Lake
1:19:13Storage Gen2, we will be using a ABFSS
1:19:17protocol to access it. The path includes
1:19:20the container name, storage account
1:19:22name, and the location of our file. I
1:19:24will be
1:19:25providing the path to our category CSV
1:19:29file.
1:19:30We will store the result in a data frame
1:19:32called df_category.
1:19:35A data frame is basically a collection
1:19:37of a data organized into a row and
1:19:39columns, similar to our database tables,
1:19:43but designed for a distributed cup
1:19:45processing using a Apache Spark. Now,
1:19:48let's execute the code. Once you have
1:19:51executed, you can see we have
1:19:53encountered an error while executing the
1:19:56cell. This is quite common when working
1:19:59with our data bricks, especially when we
1:20:01are reading a file from external
1:20:03storage. Whenever you encounter an error
1:20:07message, uh the first thing you should
1:20:09do is check error message carefully. It
1:20:12usually gives us a indication of what
1:20:15went wrong. In our case, let's review
1:20:18the code and then configure the what we
1:20:21have provided. I will make the required
1:20:23changes and execute the cell again. Now,
1:20:26our data frame has been created
1:20:28successfully. One thing worth
1:20:30understanding here is that Spark uses a
1:20:32lazy evolution.
1:20:35This means that we when we define a data
1:20:38frame, Spark doesn't necessarily process
1:20:40all the data immediately. Instead, it
1:20:43prepares the execution plan and the
1:20:45actual processing happens when we
1:20:48trigger the action.
1:20:50We will understand this concept in more
1:20:52detail when we start working with a
1:20:54transformation.
1:20:55For now, let's verify that our data has
1:20:58been loaded correctly. For that, I will
1:21:01be using display function. This
1:21:03functions allow us to view the content
1:21:06of our data frame directly inside the
1:21:08Databricks notebook. Let's execute it
1:21:12and here we can see our category data.
1:21:14We have the category ID and category
1:21:17name columns along with their
1:21:18corresponding records. This confirms
1:21:21that
1:21:22we have able to access the CSV file from
1:21:26our Azure Data Lake storage and read in
1:21:28it it into Spark data frame. Now, let's
1:21:32look at the something important. When we
1:21:35read the CSV file, Spark provides
1:21:37several options that helps us to control
1:21:39how the data is loaded. For example, we
1:21:42can specify whether first row contain a
1:21:45column names, whether Spark should
1:21:47automatically identify the data types.
1:21:50Let's update our code and include those
1:21:53option. First, we will be using a header
1:21:56option by setting header equals to true.
1:21:59We are telling Spark that first row of
1:22:01our CSV file contains column name. Other
1:22:05Otherwise,
1:22:06Spark may assign default column names
1:22:08such as _c0,
1:22:10_c1 and so on. Next, we will be using a
1:22:14infer schema. By setting infer schema to
1:22:18true, Spark will
1:22:19examine the data and attempt to identify
1:22:23the appropriate data types. For example,
1:22:25if a column contains a numeric value,
1:22:28Spark may recognize it as a integer
1:22:31rather than treating
1:22:32everything as a string. These options
1:22:35are very useful when we are explore
1:22:38exploring the source data. In a
1:22:40production pipeline, we often define the
1:22:43schema explicitly to maintain
1:22:45consistency and avoid unnecessary schema
1:22:48inference.
1:22:49Now, let's execute the updated code. I
1:22:52will display the data frame again to
1:22:54verify the output. As you can see, our
1:22:57category data is available in a
1:22:59structured format. So, we have
1:23:01successfully loaded our first data set.
1:23:04Now, let's move on to the next step.
1:23:06That is we will be reading multiple
1:23:08files in from our bronze layer and we
1:23:11need to load them into Databricks before
1:23:14we can start applying any
1:23:15transformation. The good thing is that
1:23:18we don't need to learn completely
1:23:20different approach for every file. We
1:23:23will be following same pattern. I will
1:23:25create another Python cell and I will
1:23:28use a spark.
1:23:29read again. This time I will update the
1:23:32data frame name and provide the path to
1:23:35to our exact CSV file. The [snorts]
1:23:38remaining configuration stay same. We
1:23:41will specify the file format, enable the
1:23:44required header option, and provide the
1:23:47source path. Now, let's execute the
1:23:50cell. Once the data frame is created, we
1:23:52can use a display function to inspect
1:23:55the record. I will repeat this process
1:23:58for another data sets.
1:24:05We would check the column names, data
1:24:07types, and sample record. This This
1:24:10helps us to identify a potential issue
1:24:12early in the process. For example, we
1:24:15might find a missing value, incorrect
1:24:17data types, and inconsistent column
1:24:19names. These are the kinds of issue we
1:24:22will be addressing when we start
1:24:23building our silver layer. For now, we
1:24:26are just exploring the data and
1:24:28confirming that the files have been
1:24:31loaded correctly.
1:24:33And as you can see, we are able to read
1:24:36the data set directly from our Azure
1:24:38Data Lake storage using a PySpark. Now
1:24:41that we have loaded our data set into
1:24:43Data Bricks, let's start working on a
1:24:45transformation part.
1:24:47In this section, we are going to use a
1:24:49PySpark to transform our existing data
1:24:52frame and prepare them for the silver
1:24:54layer.
1:24:55Before that, let's read all the columns
1:24:58of our data frame. And for that, I will
1:25:01be using a data frame.columns
1:25:03to check our columns available in our
1:25:06data frame. The purpose of silver layer
1:25:09is to take the raw data that we have
1:25:11already ingested and make it more
1:25:13consistent and useful for further
1:25:16processing. For example, we might need
1:25:18to rename the columns, convert data
1:25:20types, standardize the text value, or
1:25:23select only the columns required for our
1:25:25analysis. And that's what exactly we are
1:25:28going to do here. First, I'm going to
1:25:30import the PySpark function that we will
1:25:33be using for transformation. Instead of
1:25:37writing everything from scratch, PySpark
1:25:40provides several built-in functions that
1:25:42make it easier to manipulate data
1:25:44frames. We will be using a function such
1:25:47as col or alias and a few other
1:25:50functions as we move through the
1:25:52different data set. Now, let's start
1:25:55with our category data set. I'm going to
1:25:57create a new data frame for the
1:25:59transform category data. Notice that we
1:26:02have not directly modified our original
1:26:04data frame. Instead, we are creating a
1:26:07separate data frame to store our
1:26:09transformation result. This is very
1:26:11useful because our original data set
1:26:14remains available if we need to compare
1:26:17values or apply the different
1:26:19transformation later.
1:26:21Here I'm creating a new data frame
1:26:23called silver underscore category using
1:26:26our existing data df_category data
1:26:29frame. The first transformation we are
1:26:31applying is drop duplicates. We are
1:26:34using a category underscore ID as a key
1:26:37to identify duplicate records. This
1:26:39means that if multiple records have the
1:26:41same category ID, Spark will retain only
1:26:44one of them. Next we are applying a
1:26:47filter function. Here we are checking
1:26:49whether category underscore ID is not
1:26:51null. This is very important because the
1:26:54category ID is a key identifier in our
1:26:58data set and we don't want to records
1:27:00with a missing category IDs in our clean
1:27:03data. After that, we are using with
1:27:05column along with the trim function. The
1:27:08trim function removes any unnecessary
1:27:10leading or trailing spaces from the
1:27:13category underscore name column. For
1:27:15example, if a category name contains a
1:27:17extra space before or after the actual
1:27:20text, this transformation will remove
1:27:22those spaces and standardize the value.
1:27:25We are also chaining these
1:27:27transformation together, meaning we can
1:27:30apply multiple cleaning operation within
1:27:32a single data frame expression. So with
1:27:35these three operation, we are removing
1:27:37duplicate category IDs, filtering out
1:27:40records with the missing IDs, and
1:27:42cleaning unnecessary spaces from
1:27:44category names. Now,
1:27:46when I executed the cell, you can see
1:27:48that Databricks showing some error. So
1:27:51there is a error in my code, so I will
1:27:54correct it and execute it again. This
1:27:58time transformation should work.
1:28:00Notice that we have only corrected the
1:28:03problem in our transformation logic. We
1:28:06we haven't changed any underlying source
1:28:08data. Now that our category
1:28:10transformation is working, let's move on
1:28:13to the next step. We have created our
1:28:15transform category data frame, but at
1:28:18the moment the transformation result is
1:28:20available through our data bricks
1:28:22notebook. We also want to save this
1:28:25result in Azure data lake storage, so it
1:28:28can be used by another process.
1:28:31For that, we are going to use a data
1:28:33frame write operation. Here, I will take
1:28:36our transform
1:28:37transform category data frame and call a
1:28:40write method. Next, I will specify the
1:28:43output format. For this project, we are
1:28:46using a parquet.
1:28:48Parquet is a columnar file format that's
1:28:51commonly used in the data engineering
1:28:53and analysis workload. Unlike a
1:28:55traditional CSV file, which stores data
1:28:58row by row, parquet are organized data
1:29:00by columns. This allows us processing
1:29:04engine such as Spark to read only the
1:29:06columns required for particular
1:29:08operation.
1:29:09Parquet also
1:29:11supports efficient compression, which
1:29:13can reduce the storage requirement.
1:29:16Now, let's specify the destination of
1:29:19the file. Here, we are writing the
1:29:22transform data into our silver container
1:29:24in our Azure data lake storage account.
1:29:27I will also specify the write mode. The
1:29:30write mode controls what happens if data
1:29:33already exists at the destination. For
1:29:36example, with a overwrite mode, Spark
1:29:39Spark replace the existing data under
1:29:41specify output location.
1:29:44We should use this carefully because it
1:29:46can remove existing files for from the
1:29:49destination. Now, let's execute the
1:29:52cell. The operation
1:29:55completed successfully, so our transform
1:29:57category data set has now been
1:30:00written to silver layer.
1:30:02But instead of assuming everything work,
1:30:04let's verify the output. I will to our
1:30:07Azure portal and I'm going to open a
1:30:10storage accounts. Here you can see my
1:30:14silver container and in that you can see
1:30:16the output folder. Now let's back to
1:30:19back in our data bricks.
1:30:21Now I'm going to add another section for
1:30:24the next data set.
1:30:26Here I will be following the similar
1:30:28approach. Now let's move to our product
1:30:31data set.
1:30:32Before we start transforming our product
1:30:34data set, we will need a PySpark to
1:30:37underscore data function to convert our
1:30:39date column into correct format. So
1:30:42let's first import to underscore date
1:30:44from PySpark.sql.functions.
1:30:47Now let's move on to our product data
1:30:49set. Here I'm creating a new data frame
1:30:51called silver_products
1:30:54using our existing df_products data
1:30:57frame. Unlike the category data set, our
1:30:59product data data set require a few
1:31:02additional transformation. First, I will
1:31:05be using with column rename to
1:31:07standardize the column names. As you can
1:31:09see the original column name contains a
1:31:12upper case letters, so we are converting
1:31:14them into a lower case names with the
1:31:16underscores. For example, product_id
1:31:20becomes product_id
1:31:22in a smaller case.
1:31:24And likewise, we are applying a same
1:31:27naming convention to launch underscore
1:31:29data and price. This helps us to
1:31:31maintain consistency. Next, we are
1:31:34converting the launch date column into a
1:31:36correct data type. In our source data,
1:31:39the date is stored as a string and so
1:31:43I'm using a PySpark to underscore date
1:31:45function to specify the format as This
1:31:48converts the string values
1:31:52into a proper date values, allowing us
1:31:55to perform date base filtering and
1:31:57calculations later. After that, we are
1:32:00converting price column into a decimal
1:32:02data type. I'm using a cast with a
1:32:05decimal 10,2.
1:32:07This means that the columns can store up
1:32:10to 10 digit in total with a two digits
1:32:12after the decimal points. Using a
1:32:15decimal data type is particularly useful
1:32:17for monetary values because it provides
1:32:20fixed precision representation unlike
1:32:23floating point types. Notice that we are
1:32:26chaining all these transformation
1:32:27together using a withColumnRenamed and
1:32:30withColumn. This allows us to
1:32:32standardize the column names and convert
1:32:34the required data types within the
1:32:36single transformation. Finally, we are
1:32:39storing the transformation result in our
1:32:41new data frame silver_products.
1:32:44Now, let's execute the cell. As you can
1:32:46see the transformation completed
1:32:48successfully. Our product data set now
1:32:52has a consistent column names, properly
1:32:54formatted launch date, and decimal data
1:32:57type for the price column. Next, we will
1:33:00be writing this transformation into our
1:33:02parquet format into a silver layer like
1:33:05just we did for the category data set.
1:33:07Now, let's move to our sales data set. I
1:33:10have pasted the transformation code, so
1:33:13let's go through the what we are doing.
1:33:15First, we are creating a data frame
1:33:17called silver_sales
1:33:19using existing df_sales data frame. The
1:33:23first transformation uses a withColumn
1:33:25along with a to_date to convert
1:33:27sales_date
1:33:29column into a proper date data type.
1:33:32This is important because we will be
1:33:34using a sales date for further analysis.
1:33:37Next, we are applying a filter function
1:33:39to to the quantity column. Here, we are
1:33:42keeping only records where the quantity
1:33:44is greater than zero. This helps us to
1:33:47exclude records containing zero or a
1:33:49negative quantities.
1:33:51After that, we are using a drop
1:33:53duplicates on a sales_id column. So,
1:33:56with this three transformation, we are
1:33:58converting sales date, filtering out
1:34:00invalid quantities, and removing
1:34:02duplicate sales IDs. Now, let's execute
1:34:05the sales. Our clean sales data frame is
1:34:09ready. Next, we are going to enrich our
1:34:11sales data set with information from our
1:34:14product data set. I have pasted the
1:34:17next transformation code, so let's
1:34:19understand how it works. Here, we are
1:34:22taking our existing silver underscore
1:34:24sales data frame and joining it with a
1:34:27silver underscore products.
1:34:29From the product data frame, we are
1:34:31selecting only two columns, product
1:34:33underscore ID and price. We don't need
1:34:36all the product information for this
1:34:38transformation. We only need a product
1:34:40ID to establish relationship and the
1:34:43price to calculate the sales amount. We
1:34:46are using a inner join on a product
1:34:48underscore ID. This means that Spark
1:34:51will retain only the sales record that
1:34:53have matched product ID in this product
1:34:56data set. Once the join is complete, we
1:34:58will have the product price available
1:35:00along with the sales information. Next,
1:35:03we are creating a new column called a
1:35:06total underscore amount. We are using a
1:35:09with column to multiply the quantity by
1:35:11price.
1:35:13This
1:35:13This calculation gives us the sales
1:35:15value for each transaction. Next, we are
1:35:18creating a two additional columns, sales
1:35:20underscore uh year and sale underscore
1:35:23month. For this, we are using a PySpark
1:35:26year and month function. This function
1:35:29allow us to extract year and month
1:35:31[clears throat] from a sale date column.
1:35:33So, let's import these two functions.
1:35:36Once we have completed the
1:35:37transformation, we will be again writing
1:35:40this transformation into our silver
1:35:42layer.
1:35:46Now, let's move on to our stores data
1:35:49set. I have already pasted the
1:35:51transformation code here, so let's
1:35:53quickly go through it. We are creating a
1:35:56new data frame called silver underscore
1:35:58store where we are using a existing DF
1:36:01underscore stores data frame.
1:36:04We are using a width column rename to
1:36:06standardize column names. Next, we are
1:36:09using a drop duplicates. This will
1:36:12ensure that we retain only one record
1:36:14for each store ID. Now, let's execute
1:36:17this cell. Next, we will write the
1:36:19silver underscore stores data frame to
1:36:21our silver container in a parquet format
1:36:24using a append
1:36:26mode.
1:36:28Finally, let's move on to our warranty
1:36:31data set. Before executing this
1:36:33transformation, we need one additional
1:36:35PySpark function called upper. So, let's
1:36:38go to our import section and import
1:36:41upper from PySpark.sql.functions.
1:36:45Now, let's return to our warranty
1:36:47transformation. I have already pasted
1:36:50the code and let's see what it does. We
1:36:53are creating a new data frame called a
1:36:55silver underscore warranty using our
1:36:57existing DF underscore warranty data
1:37:00frame. The first one uses a to
1:37:02underscore date to convert the claim
1:37:05date. Then, we are using a upper to
1:37:07convert the values in the repair status
1:37:10column into a upper case. This will help
1:37:12us to standardize the repair status
1:37:15values and maintain consistency across
1:37:17the data set.
1:37:19Now, let's execute this cell. As you can
1:37:22see, the our warranty transformation is
1:37:24completed and let's write this
1:37:26transformation also into our silver
1:37:29layer. Now that we have completed our
1:37:31transformation, let's examine the result
1:37:34more closely. I will be using a display
1:37:36function.
1:37:38The Databricks provides an interactive
1:37:40table view for displaying the data
1:37:42frames. This makes it convenient to
1:37:45inspect the records without having
1:37:47export the data into another
1:37:49application. Now, let's move on to the
1:37:52next operation. Here we are using a
1:37:55group by operation to summarize our data
1:37:57set. Instead of examining every
1:38:00individual record, we want to calculate
1:38:02the aggregate result. For that, I will
1:38:04be using a group by function. Group by
1:38:07function groups records that have the
1:38:10same value in the selected columns. We
1:38:12can apply the aggregation to calculate
1:38:15and summary for each group. Let's
1:38:18execute the cell. Now, let's visualize
1:38:21this aggregated result. One useful
1:38:23feature of Databricks is that we can
1:38:26create a charts directly from the output
1:38:28of our notebook cell. We don't need to
1:38:31move data into a separate reporting tool
1:38:33just to create a quick visualization.
1:38:36Here I will be opening a visualization
1:38:39options. Now, let's select the options
1:38:41to create a visualization. This opens
1:38:44the visualization editor. On the left
1:38:46side, we can configure the chart. On on
1:38:49the right side, the Databricks will
1:38:50display a preview. Let's start with a
1:38:53bar chart. I will select the columns
1:38:55that represent in our group. Then I will
1:38:58select the numeric column that we want
1:39:00to visualize. Notice that the chart
1:39:02updates based on the selected columns.
1:39:05Here we can compare the values across
1:39:07the different groups. A bar chart is
1:39:09very useful when we want to compare a
1:39:11quantity across the categories. For
1:39:14example, if we have a several groups and
1:39:16want to identify which group has the
1:39:18highest or lowest value, a bar chart
1:39:21makes the comparison easier.
1:39:23Now, let's examine the chart. We can
1:39:26move the pointer over the bars and
1:39:28inspect the corresponding values. This
1:39:31gives us a quick way to explore our
1:39:33aggregated data. Now, let's try another
1:39:36visual type. I will open the
1:39:38visualization type drop down. Here
1:39:40Databricks provide us several options.
1:39:43Let's select a line chart. Notice that
1:39:45the same aggregated data is now
1:39:47displayed as a line. The underlying data
1:39:51frame hasn't changed. We are only
1:39:54changing the way of the result as
1:39:56represented. Line charts are commonly
1:39:59used when we want to examine changes
1:40:01across an ordered sequence, particularly
1:40:04over the time. However, for order and
1:40:07order categories, a bar chart is usually
1:40:10easier to interpret. In this example, we
1:40:13are exploring the visualization option
1:40:15available in data bricks. Now, let's
1:40:18look at the another chart type. Next, I
1:40:20will open the visualization type
1:40:22drop-down again. This time, I will be
1:40:25selecting a pie chart. As you can see,
1:40:27data bricks display the result using a
1:40:29different color segment. Each segment
1:40:32represent a group in our aggregated
1:40:35data. The size of each data set or
1:40:38segment is based on the corresponding
1:40:40value. Here, the chart is displayed in a
1:40:43donut style layout. This makes it easier
1:40:46to see the relative distribution of a
1:40:49value across the groups. We can also see
1:40:52the legends on the right side. The
1:40:54legend identifies the groups represented
1:40:57by the different colors. Now, let's look
1:41:00at the percentage displayed on the
1:41:02chart. This percentage helps us to
1:41:05understand how much each group is
1:41:06contributing to our total. This type of
1:41:09visualization is very important when we
1:41:12want to compare the relative proportions
1:41:15of small number of categories. One thing
1:41:18to keep in mind that pie charts become
1:41:20harder to read when we have too many
1:41:22categories. For larger number of groups,
1:41:25a bar chart is generally a better
1:41:26choice.
1:41:28Now, let's review the chart setting.
1:41:31Here, we can configure the column used
1:41:33by the visualization and I adjust the
1:41:36available display options.
1:41:38Once we are satisfied with the
1:41:41visualization, we can save it. Now,
1:41:43let's return to our notebook.
1:41:46Back in our notebook, we can see the
1:41:48aggregated results again. Notice that
1:41:51the output is still available in tabular
1:41:54format. This is very useful because the
1:41:57same data frame results can be explored
1:41:59in different ways. We can expect the
1:42:02actual values in the table and we can
1:42:05and create a visualization to understand
1:42:08the distribution more quickly. Now,
1:42:10let's continue with another data set. I
1:42:13will be moving to the next session of
1:42:15the notebook. Here, we are going to
1:42:17inspect us our sales-related data set.
1:42:20Again, I will be using a group by
1:42:22function and I will be doing a some
1:42:25visualization.
1:42:27You can play with it and you can check a
1:42:30result as per your need. Now, let's go
1:42:33back to our notebook. We can see the
1:42:36aggregated result. After inspecting the
1:42:39transformed data frames and perform
1:42:41aggregation to summarize data, finally
1:42:44we use the data bricks visualization to
1:42:46explore the aggregated result using a
1:42:49bar chart, line charts, and donut
1:42:52charts. So, this completes our silver
1:42:54layer transformation and data
1:42:56exploration section demonstrated in our
1:42:59notebook.
1:43:00And with that, we have reached the end
1:43:02of our Azure Data Engineering project.
1:43:05Throughout this tutorial, we have built
1:43:07a data pipeline using Azure Data
1:43:09Factory, Azure Data Lake Storage Gen2,
1:43:12and Azure Data Bricks. We We started by
1:43:15ingesting our source file into Azure
1:43:18Data Factory and storing raw data into a
1:43:20bronze layer. Next, we configured the
1:43:23connection between Azure Data Bricks and
1:43:25Azure Data Lake Storage. Then, using
1:43:28PySpark, we performed several
1:43:30transformation including removing
1:43:32duplicates, handling missing values,
1:43:34standardizing the column names,
1:43:36converting data types, and enriching our
1:43:38data sets. Finally, we store our
1:43:41transformed data into a silver layer
1:43:43using a parquet format. So, we have
1:43:45successfully implemented the data
1:43:47ingestion, storage, and transformation
1:43:50stages of our Azure data engineering
1:43:52pipeline.
1:43:53In a larger production architecture, we
1:43:56could explain this solution with a
1:43:58serving layer using the Azure Synapse
1:44:01Analytics and reporting through Power
1:44:03BI. However, those components are
1:44:06outside the scope of this tutorial. I
1:44:08hope this tutorial help you understand
1:44:11how these Azure services work together
1:44:14in a practical data engineering project.
1:44:17If you found this video helpful, don't
1:44:19forget to like, subscribe, and share it.
1:44:23Thanks for watching, and I will see you
1:44:25in the next video.

About this transcript

This page contains the full transcript of End-to-End Azure Data Engineering Project | Build a Complete Data Pipeline from Scratch by Tech with Amol, generated from the public captions YouTube serves with the video. The transcript has 14,259 words across 2,462 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to translate the transcript into 100+ languages.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, translation to 100+ languages, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.