Databricks Free Edition Tutorial: Complete End-to-End Data & AI Project

[00:02] because datab bricks is getting popular. In this complete micro course, we are going to first learn the foundations of datab bricks and then we will build end toend data engineering project in e-commerce domain. We will do our data

[00:15] processing through bronze, silver and gold layers and eventually build gold layers and eventually build business insight dashboard. [music] datab bricks free edition. This free edition enables students and

[00:30] practitioners to learn professional data and AI tools for free. For people who don't know what data bricks is, it is a leading AI and data platform for enterprises. The reason for its popularity is its ability to simplify

[00:44] their architecture and speed things up at the same time. It has helped many companies like Mercedes-Benz to speed up their datainformed decision-m process by enhancing their business intelligence and at the same time improve their query

[00:58] performance and run ML models on sensor data. We will learn more about distributed computing spark and data bricks in our foundation section. All right, let's jump into our project problem statement straight away and we

[01:12] are going to use some interesting [music] storytelling for this. A toz is a rapidly growing e-commerce company headquartered in the United States. As their business expands, both the volume and velocity of their data

[01:25] have exploded. They currently rely on Python-based ETL [music] pipelines for their analytical workflows. But these pipelines have become difficult to scale, causing performance bottlenecks and delays in dashboards. The company's

[01:39] head of data engineering, Tony [music] Sharma, was called in to fix the issue. He quickly realized this [music] wasn't just a coding problem. It was a scalability crisis. Meanwhile, Bruce Hari, the chief operating officer, had

[01:53] been hearing complaints from business teams. Reports were delayed. Dashboards froze. Marketing couldn't see yesterday's data on time. Tony met Bruce in boardroom. They agreed the system had reached its limit. Python scripts were

[02:08] running out of breath. They needed something built for large scale data processing. That's when Tony proposed data bricks, a managed platform [music] that combines Apache Sparks distributed power with the flexibility of the cloud.

[02:24] Bruce agreed, but instead of a full migration, he wanted a pilot project, a small measurable test. Tony assigned the pilot to Peter Pande,

[02:36] a curious data engineer known for tinkering with every new AI and analytics tool in the market. For Peter, this was more than just a project. It this was more than just a project. It was a chance to learn modern data ST and

[02:49] replace old systems. He started building a pipeline on data bricks to test scalability and performance. So in this project, you will be joining Tony, Bruce, and Peter as they explore how data bricks can [music] solve a TOZ's

[03:03] scaling challenges and learn how cloud data engineering drives real business >> Peter, you saw the email from Bruce. We got explore data bricks now and see if it will really fit our needs. >> Yes, I saw that email. I think I'll be

[03:17] able to do it. >> Okay, but how? Do you know data bricks already? Have you worked with it? No, not really. But Tony, you know, nowadays things are easy to learn. You can go to YouTube and find some crash course.

[03:30] >> I hope so. But let me clarify the requirement to you. Okay, I'm reading three things as a part of your exploration, right? The first one is quite obvious. Of course, it should work better than our current ETL process that

[03:43] is given. And the second thing is the tool should be easy to adopt, right? I training the entire team. You know, it's not going to work and it should be easy to adopt. And number three is it should fit our road map. So you know our road

[03:56] things in our road map. One thing is scalable. We should be able to accommodate any scale of data hypothetically. Number two is it should be agile. So what I mean by agile is we should be able to upgrade or downgrade

[04:10] infrastructure at any point without worrying about the cost. Right? So you got that. And number three is I'm looking for a you know a simplified platform like a unified platform where you know just imagine like our data

[04:23] engineers, data analyst and the AI engineers all working together in one place you know and it should essentially simplify our data workflow all together. So that's the that's the most important thing. So

[04:38] does it sound absolutely clear to you? >> Yes, it's very clear. I am excited to start work on this. >> Great. >> Now that you're clear about problem statement from this point on you have

[04:51] two possible learning path. The first one is you continue from here learn data bricks foundations and then do the project. This is a linear learning path most suitable for those who don't know much about data engineering. The second

[05:03] one is a nonlinear learning path where you go to project straight away start to any foundations you come [music] back. This works best for the people who wants to get into the action straight away. Timestamps are provided in video

[05:18] description. I wish you all the best. Let's go ahead and create an account in datab bricks. All you have to do is click a link down below in the video description and you will come here. This is datab bricks free edition. They have

[05:32] created this free addition so that learning community can benefit and we truly appreciate that afford setting it up is very easy. I'm going to use my Google account. You can use your email ID as well. So click on continue with

[05:45] Google and I will select my Google account. Say continue and here for account. Say continue and here for personal use get free edition. Click on continue and start the puzzle. Okay. So I think this is the right answer. Hit

[06:00] submit. After a few seconds, my account is ready. Let's explore this UI quickly. Here you can upload uh your data. You can browse public data sets. Uh so you can go to marketplace and look at all the data

[06:16] products and data sets. You can also go to catalog where you will see your tables etc. So they have these three layers where at the outside you have something called a catalog. So this is a catalog. Under that what you have is

[06:32] schema. So you have this default schema and right now there is no data. So let me quickly upload the data. So you can go to homepage, click on upload data go to homepage, click on upload data button or you can go to data injection

[06:46] button or you can go to data injection here and create or modify table. Okay, click on that and I'm going to provide you this movies.csv file. So which you'll be able to download using a link in the video description below. And this

[06:59] is a simple movies database where you have movie name, industry, release year, IMDb rating and all of that information. So let's drag and drop this file here and it is going to create movies table in workspace catalog. See workspace is a

[07:16] catalog schema is default. And here you will see a quick preview of data. All right. So see I have my title and here it shows the data type. So this is string. This is string release here should be number correct. IMDb rating.

[07:32] It is saying string but we will change it to number. Okay cuz it's a double number. So let's change it to double. And if you just decrease your font size you'll be able to see all the columns in

[07:45] one single view. Things are looking good. Click on create table. Now when you hit a refresh button here you will see this movies table. See so here you have workspace catalog default schema and movies is your table. If you go to

[08:00] sample data it will show you the sample data from your table. Details will show you the metadata of this table. So this is a manage table. We'll talk about manage versus external table later on. You have different level of permissions,

[08:16] policies, history, lineage and so on. I clicked on SQL editor option and I will quickly run some queries. So let's say select star from workspace.default

[08:30] select star from workspace.default dot movies I want to view fire records. And before you run this you have to connect it to a compute. So I'm going to connect it with a serverless compute. So let me talk about that compute. Uh so

[08:45] let me talk about that compute. Uh so here I will go compute right click open it in a new tab. Now in a free addition what you get is a serverless compute. See this option is disabled. If you have enterprise account

[09:00] you can create a compute where you can specify your cluster basically. Okay. How many nodes you are going to have in your uh cluster etc. But we are going to use this serverless compute throughout this course. So let's go to SQL editor

[09:16] and run the query and see you see all your results here. Now serverless compute is sort of like AWS Lambda. So see behind the scene they have servers but all those details are hidden from you. So this way you can just focus on

[09:31] your business logic. You don't have to worry about managing your computers on cloud. Then there is this Genie option. So if you click on it, click on new and you can click on your movies and you can create this genie space and you can ask

[09:46] create this genie space and you can ask questions like show me top five movies by IMDb rating and this is an AI assistant AI rating and this is an AI assistant AI chatbot where it will take a command in

[10:00] natural language and it is internally converting it to a SQL query and see it is showing you the results. So this is very beneficial. So even if you don't very beneficial. So even if you don't know coding etc. you can just give these

[10:13] commands in a natural language and it will show you the results. We also have this option for data injection uh where you can connect with different server they have so many different connectors. If you click on see all you

[10:29] will see this entire list. So you can connect to all these connectors, bring your data into data bricks and work on data engineering, AI etc. And for AI Okay. So there is playground experiments, there is model serving and

[10:46] so on. Jobs and pipeline is something where you can create your ETL jobs or injection pipelines. And if you look at workspace here, you'll be able to create a new notebook. So we are going to use all these options. I'm just giving you a

[11:00] all these options. I'm just giving you a quick overview of the UI here. Now we will look at data frame basics. For this I will go to my workspace and For this I will go to my workspace and create a new notebook. Now if you have

[11:14] uh studied pandas, pandas has a data frame. Similarly spark has a data frame and we are using python here. So the module or the library that we are using module or the library that we are using is pi spark. Okay. uh Spark is supported

[11:29] is pi spark. Okay. uh Spark is supported in Python, uh Scala, Java, different languages but we are going to be mainly using Python. Now here you will find this object called spark and if you control enter and run it, it will show

[11:45] control enter and run it, it will show you this thing. Okay, so uh datab bricks environment gives you this uh variable this object which is a spark session object. If you are not running in data bricks then you have to do something

[12:00] like this. See you you will usually write this kind of code where you will write this kind of code where you will say okay import pispark SQL spark session and create this. But in databicks environment you get this

[12:13] object by default. Now let's create a data frame by calling this function data frame by calling this function spark dot table. Okay. And here you can

[12:25] spark dot table. Okay. And here you can specify workspace dot default dot movies specify workspace dot default dot movies and the return value will be a data and the return value will be a data frame. Now let's just execute this code.

[12:39] See now it is showing you this data frame and make sure you are connected to your uh compute. So here it will be serverless compute. You can also run df do.show show function where let's say I want to display only five rows. Now this

[12:56] is not very well formatted. Display will give you a proper formatted table but sometimes people use this show method because it is very fast. You can run uh

[13:09] because it is very fast. You can run uh df dot print schema count right all of this useful method. So let me run this one by one. So print schema will give you the schema of your table. So title is a string data type. IMDb rating is a

[13:23] is a string data type. IMDb rating is a double data type and so on. You can then double data type and so on. You can then say DF count and also print DF dot columns. Okay. So let me just print the count first. So number of rows I have is

[13:38] count first. So number of rows I have is 37. Now you can verify it in your SQL editor. You can say select count star from your table. So let's do select from your table. So let's do select count star from this table.

[13:53] saying 37 rows. Then you can do something like df dot columns which will give you the list of columns. Okay, it's a plain python

[14:05] of columns. Okay, it's a plain python list with all the columns. Then you can run another useful function df.escribe. This is similar to uh if you use again pandas it has this describe function. So let me use this display method so that

[14:19] let me use this display method so that you can see the nicely formatted output. See here it is showing you the count okay release year count okay per column statistics. So what is min max? So for IMDb rating my minimum rating is 1.9

[14:35] maximum rating is 9.3 and my average rating in my movies table is 7.8. We have another similar function similar to this df.escribe. It is called summary. And when you print it, it will show you uh similar columns

[14:51] but it will show you additional data such as all these percentiles. Now how want to select only three columns in my data. So first just like Jupyter notebook you can add markdown cells

[15:05] notebook you can add markdown cells here. So I will just say column here. So I will just say column filtering. You can give it headers etc. filtering. You can give it headers etc. And here you can say df dot select and

[15:17] want to select. Let's say these are the columns and uh I can return this into another data frame called dft trimmed. See here these data frames will be

[15:30] unmutable which means you can't modify them. So I'm selecting three columns and returning it to a new data frame. Now in this new data frame once again you can say show three. They have this another parameter called truncate which is by

[15:46] default false. So let me just execute it and show it to you. So see here I'm seeing only three columns. Okay. And when you set truncate equal to false it will show you this entire text. If my truncate is true this text will be trim.

[16:02] See so that you can see the columns in a compact manner. Now let's do row compact manner. Now let's do row filtering. So I clicked on text and here I am adding a code for row filtering. And let's say I want to see movies

[16:15] And let's say I want to see movies between year 2000 and 2010. Okay. Now I can write queries but I noticed this option of using generate with AI. So I'm curious in trying that. Okay. So let's do Ctrl I and then type in your

[16:31] criteria. Show me all the movies released between 2000 to 2010. Hit enter and the AI assistant will write query for you. See this query looks good. So

[16:45] you will say accept. And here as you can see uh you will use this method where you will give your criteria. Okay. Release years should be greater than equal to 2000 and less than equal to

[16:58] equal to 2000 and less than equal to 2010. I will call this DF 2010. I will call this DF filtered. Okay. So let's call this DF filter. Control enter. And see now you're seeing all the movies between

[17:10] you're seeing all the movies between 2000 and 2010. Now the same thing you can do it using other code as well where you are importing this call function from pispark.sql.function and you can just say column releaser

[17:24] greater than 2000 less than equal to 2010. Okay. So it will do exactly same thing. It's just there are two different ways of doing it. There is third way by ways of doing it. There is third way by the way. So you can say df dot filter

[17:37] and you can say my column release year release year dot between see 2000 to 2010 dot between see 2000 to 2010 and that you can store it in filter and

[17:51] you can say display df filter. Okay. So again this is the third way of doing the same thing. And how do you print all the movies which are released by Marvel. Okay. I love Marvel movies and I want to

[18:06] see all the Marvel movies. Same syntax. Okay. Here in column you will use studio and you will say Marvel studio and here are all your Marvel movies. Now I want to know how many distinct movie industries are there. See here I'm

[18:21] seeing Hollywood but what other movie industries are there? Well, for this you will say DF dot select you will select the column. Okay, industry is a column and then you will say distinct and this you can assign it to a variable called

[18:36] you can assign it to a variable called unique industries and then display function is used to display a data frame in a nice tabular format. Okay. So I have only two industries Bollywood and Hollywood. Similarly you can uh select

[18:51] unique languages. Right. See here if you look at this table there is this language column. So it will show you languages for different movies. So I want to know see here it is saying that my movie data set has movies in Hindi,

[19:07] English, Bengali, Canada and Telugu. Now see my table has the budget and revenue column. Naturally, when I'm doing data analytics, I'm interested in knowing the profitability of each of these movies. And profit is nothing but budget, which

[19:21] means this is the amount of money that was spent in making a movie. Okay? And generated. So if you do revenue minus budget, you will get profit. So here you budget, you will get profit. So here you can create new column by saying DF dot

[19:36] with column and this column will be profit and it is nothing but column profit and it is nothing but column revenue minus column budget. Okay. And I frame. In the same data frame essentially I am adding that extra

[19:50] column. So control enter. And here you will see this new column. See profit. So will see this new column. See profit. So see for this first movie revenue was this much I think 100,000 and 70,000 so profit is 30,000 with column is a

[20:04] function that you will use a lot in pandas if you want to create a new column you will say df then that column and you know if you're creating it using existing column you will say df existing column do apply here the syntax is

[20:19] little different but it's easy to remember right you just say df do with column you specify ify your new column name and here you specify your expression. Now when I look at revenue column I feel like I want to rename it

[20:33] to total revenue. So it shows it's it's a total uh revenue generated by that particular movie. And you can do that easily by saying df do with column

[20:45] easily by saying df do with column rename. See if so here uh I will just change revenue revenue to total revenue assign it back and then

[21:00] total revenue assign it back and then say df dot print schema and see here it is total revenue. In the last lecture we did some basic analysis on the movies data set and by the way movies data set you might have seen in our SQL course if

[21:14] you have taken SQL course from code basics. Now we are going to give some name to this particular notebook. So we'll call it basic operation. Now in this lecture we are going to see how we can read and write

[21:30] from CSV file. So we have given this another file called order dot CSV. So if you open that file, it's a different data set. It has some kind of orders from some e-commerce company etc. Right? Country, order ID etc. And we are going

[21:48] Country, order ID etc. And we are going to load this data in data bricks. Okay. So we'll go to data injection here and then instead of using create or modify table, we'll upload files to volume. Why? because we don't want to create a

[22:03] table out of this CSV file. We want to upload this file as is here in data upload this file as is here in data bricks. Okay. So click here and drag and

[22:15] bricks. Okay. So click here and drag and drop this order files here. You will see what happens actually. Click workspace default and then you have to create a volume. So create a volume. Let's call it raw data. Okay. And it's a manage

[22:30] volume. Okay. And create. Manage volume means like here we are uploading the data to datab bricks and data bricks is taking care of management. So click on upload. And once it is uploaded go to catalog

[22:47] workspace default and here you see in tables you have movies table but in volumes you have raw data and see the CSV file is asis. We did not convert

[22:59] that to a table. Now let's create another notebook. So I think this notebook was basic operation and this notebook I will call it read write CSV.

[23:11] Okay. And we already have spark object. Right? So you can just say spark dot Right? So you can just say spark dot read dot csv. And here you specify the entire path and then df. Okay. It will take some time to run

[23:28] this code. Okay. So, it read the file. But there is a problem. The header is considered as one of the rows in that table. Okay. So, to tackle that problem, you can supply an option. You can just say option

[23:43] an option. You can just say option header is true. And when you do that that it will the first row it will take it as a header. Now you will see another it as a header. Now you will see another problem. See this order ID is integer

[23:57] but here it says the data type is string. See you can verify it by the way string. See you can verify it by the way by also doing df.tprint schema. print schema function. It just prints the schema. Everything is string. Okay.

[24:13] So you can infer the schema as one of the options. Okay. So you can supply another option. So this is the syntax spark read dot option in bracket you provide your option. If you want to supply another option you again say

[24:27] supply another option you again say option right option dot and here you supply the option and the line is getting bigger. So let me just do this. So this way I can supply different options in different lines.

[24:41] different options in different lines. Okay. So here you can just say infer Okay. So here you can just say infer schema is true. You want spark to infer the schema based on the content in that column. So now you can see order id it's

[24:58] column. So now you can see order id it's integer. Then quantity it is integer. integer. Then quantity it is integer. Sometimes instead of letting spark infer the schema you want to specify the gimma. You want to say okay order date

[25:10] is of type date, country is of type integer. Okay. So for doing that you can integer. Okay. So for doing that you can specify the schema by writing this code. Okay. You don't remember need to remember all this code. You have AI

[25:24] assisted coding. So you can generate this code easily. So here from pi sparksql you are importing functions as f and types as t and then you're creating the schema. Okay. where you're saying okay my order date is of date

[25:38] saying okay my order date is of date type my order ID is of integer type and so once you have done that you supply another option called dot schema it's not an option it's it's a method you just say dot schema is this particular

[25:54] just say dot schema is this particular CSV schema and when you run that see it will take that particular schema now sometimes you want specific format for a column let's say this is a date right you want it to be in a mm dd y format.

[26:10] So once again there is an option. Okay. So here you will have option called date So here you will have option called date format and usually date format is like see here I'm saying mm dd y y right so the date format the usually accepted

[26:27] format is y mm and dd and that is what we have mm and dd and that is what we have already but I'm just showing you so see like you have date you have year then month and then date now whatever we

[26:42] just did. You can do the same thing using a little different syntax also where you will say spark read dot format. Right? We were saying read dot format. Right? We were saying read dot CSV previously. See here we are doing

[26:57] read and then CSV. Instead of that you can say read but in format CSV. Okay. So this will do the same thing. It's just a different syntax. And then I want to write data frame to let's say park format. for that I can you know how you

[27:13] have read function you also have write function see and you can specify your format so let's say I just want to write in in a let's say I just want to write in in a park format so I will say df

[27:26] dot write dot mode override means whatever file you're writing to if there is already a file present there just override and here you will say park and you specify the path so in the same path I will write this park file orders. So

[27:42] it has returned it. So now let's go to catalog workspace default volumes raw data. And here you see park. So so you see this thing the data is now returned in the

[27:55] park format. We just worked on some basic data frame operations in data bricks. And you might be wondering how is this different from running a plain Python notebook on my local computer. I can use pandas and do

[28:09] the same thing. Well, the key here is distributed computing. Data bricks and spark provides distributed computing. And just in case if you don't know what this is, let me explain this by simple example of my own wedding. In any Indian

[28:25] wedding, food is a key highlight. So in my wedding, we wanted to prepare 2,000 my wedding, we wanted to prepare 2,000 puris and we initially hired one chef. But then we realized that he's going to take four hours and what if he gets

[28:38] sick? You know there is a dependency. So then instead of one we invited four chefs and we gave them 500 puris each and they worked in parallel and we were able to make 2,000 puris in 1 hour. Now this is a common sense concept right?

[28:53] this is a common sense concept right? You can make your work uh parallel and get it done in a quicker time. Uh similar thing you can do it with computers. Let's say you have this data set where you have ticker, you have

[29:07] set where you have ticker, you have price and EPS and you want to compute PE ratio. You can give it to your computer, right? You can run pandas code and you right? You can run pandas code and you can compute it. But what if you have 40

[29:20] million records? Your computer will probably hang, right? So what you can do to solve that problem is you can divide your data into batches and you can give it to different computers and they will

[29:34] do all this computation and you can aggregate these results. Okay. So this approach is called map and reduce. So this is map step and this is a reduce this is map step and this is a reduce step. So just to make it general, you

[29:47] have a bigger task, you give it to your main computer, your driver computer. It will divide it into multiple task as if you have a team lead and they have four team members and it will divide each of these subtasks to them. Okay. So this

[30:02] entire thing is called cluster. So you have your driver computer and your driver computer, you have worker computer and this is what is distributed computing. And one other benefit uh other than getting efficiency in speed

[30:17] is that let's say something happens to this computer right it goes down then this driver or team lead can assign that task to somebody else. So you get something called fault problems. It's similar to you're working in a team one

[30:31] of your teammate is sick and your team lead will assign the task to some other team member or they will hire a new person. Okay. So let's say going back to my wedding example, what I can do is if I I want to prepare 2,000 puries. See,

[30:45] previously I said I will hire four chef. But instead of hiring four chef, how about I hire caterer and caterer can have multiple chefs. I don't care. I just talk to Kater. This person is a single point of contact for me. And as

[30:58] an output, I get 2,000 puris. Okay. Now let's say if some chef gets sick or assigning that work to some other chef. they will get new shape etc. So distributed computing is the process of splitting the large computing task in a

[31:14] smaller subtask and executing them in parallel. Okay. So that work can be uh done efficiently and the whole thing acts like a single system. So what is Apache Spark and Hadoop then? Well Hadoop is a distributed computing

[31:28] framework folks. It lets you do the same thing but it is very disk heavy and slow. So when Hadoop was going through all these problems at UC Berkeley AM lab this person Mate Zahera along with other people working at UC Berkeley invented

[31:44] Spark. So Spark is a unified engine for large scale data analytics. It is basically a compute. Okay. Uh and it's an open-source distributed compute engine designed to process large scale data quickly and efficiently. So it

[31:58] solved that problem with Hadoop. Hadoop was very disk heavy and spark does everything in memory and due to that it is very efficient. Okay. So if you look at this particular diagram let's say you want to do this computation you can

[32:13] write a simple code in pi spark. So pi spark is a python module that helps you work with spark. You can write a simple code that you read this stocks dot csv

[32:26] file and you simply divide it right. You can say with column and divide these two columns that you have and internally it will assign it to your computers and all that. You don't have to worry about the details of distributed computing. You

[32:41] focus on your business logic uh where to divide the work how many computers like you know all that details will be taken care of by spark. So essentially you give the code to spark uh and you set up a spark cluster and then this driver

[32:56] program will give the work and you will produce the result. So it is very similar right. So spark is similar to a keter. So look at these two diagrams. It's very similar. Okay. So it's a distributed comput engine that abstracts

[33:10] see this is very important. It abstracts the complexity of parallelism letting the complexity of parallelism letting developers focus on a business logic and it is very fast compared to Hadoop in memory and simple API. So what is data

[33:24] bricks then see if you want to run Apache Spark you have two option one is Apache Spark you have two option one is self-hosting. So you basically go to the spark website, you download the docker image or you download the uh zip file

[33:39] and you set things up uh on your computer. Okay. So this is like me talking to caterer, photographer, decorator individually and even I will

[33:51] go and uh you know if decorator says okay I need this much stuff I will go and get it from the market. So I'm also involved in this management. Okay. And this management means uh when you're doing self-hosting you have to provision

[34:03] physical and VM nodes you are installing and configuring spark Hadoop yan etc. So all this management of setting up spark cluster and making it functional is involved in self-hosting. Uh instead of that just imagine I hire an event

[34:19] all these people and she will take care of all of those things. Okay. So there is less headache for me. So this is called manage service. So in manage service you take away the cluster life management burden. You just say give me

[34:34] cluster of X workers and it will do that. Okay. So this manage service is nothing but data bricks. So when you go to data bricks you just create a cluster you create a compute and that's it. Everything else all of these things are

[34:47] taken care of by that. Okay. So this is just a diagram highlighting the just a diagram highlighting the difference between the two approaches. Now we are going to run SQL queries in Spark which is something you will do a

[35:00] lot either as a data engineer or as a data analyst. We already have movies data set and if you want to run SQL query on top of that you can do it in query on top of that you can do it in two ways. The first way is let me just

[35:13] two ways. The first way is let me just create a markdown cell SQL using Python API. Okay. So there is a Python API through which you can run this SQL and we have spark object already correct. So on that

[35:29] you can simply run spark.sql select star from select star from workspace dodefault dot movies where studio is equal to

[35:43] is equal to marwell studios. Okay. And this is it. marwell studios. Okay. And this is it. Now it will return a data frame. So you can say df marvel and here df marwell

[36:02] I got this error because I have quote here and code here. So you can just use okay you can use a single code here. Okay. Let me do that. So single code have to take care of these quotes in Python. You can also use triple quotes

[36:17] Python. You can also use triple quotes if you have a longer SQL query. See it's wonderful, right? So we used Python function spark.sql and you can specify function spark.sql and you can specify your query here. The second way is

[36:32] SQL using percentage SQL. Okay. So in the code when you say percentage SQL percentage SQL you can now freely write

[36:45] percentage SQL you can now freely write your query you can say select star from your query you can say select star from whatever let me just copy it this particular thing here and it will execute it. This sounds more

[36:59] like you're having your database tool your whatever Oracle or uh MySQL workbench and you are directly running your query. See and this result will be

[37:11] stored in underscore SQL DF so that if you want to use it later on you can use it. Now let me create a temporary data frame. So I'm not going to use any movies which is table actually in data bricks. I'm going to create a data frame

[37:27] onfly for my weather data. So I have this Python array. Okay. And in spark you can say spark.create dataf frame data. So you specify your data. Then you specify your schema and that will create your data frame. Okay. And here I'm just

[37:42] doing some uh formatting for my date column. So when you run this it will column. So when you run this it will create a data frame for my weather data. Now I want to run SQL query on top of it. How do I do that? Well, you can do

[37:55] that by creating a temporary view. So you can say df create or replace. If you already have a view with name weather in your session, this will replace it. You

[38:08] can also say create view. But we always use create or replace temp view. Okay. Now when you run this, let me just show you what happens. Now you can say you what happens. Now you can say percentage SQL and you can run a query

[38:22] on this view. So let me run this query. Select event and you know average temperature whatever from weather. See from weather. So this weather is this view that you have created and group by event etc. Okay. So now remember that

[38:36] weather is not a table in my data bricks. It's a data frame from which I have created this uh view and the name is weather. And see I'm able to run the query. So this is very cool. Now if you look at other options for the function

[38:53] look at other options for the function see it has create global temp view also. see it has create global temp view also. So see the scope. So let me say global So see the scope. So let me say global weather. Okay. So this is session scope.

[39:06] What it means is in my session in my current pi spark session in this notebook this is valid. So if I go to a different notebook and if I say if I run this query it's not going to work. But if I done create global temp view then

[39:22] on global weather I can create a another notebook it should be in the same cluster but another notebook on which I can run my query. Okay so that's the purpose. Now you can run the same query using Python API also. Right? So here we

[39:36] are using percent SQL and for Python API folks it's super easy. Just say spark.sql SQL and your query and that's it. So what we learned in this session is there are two ways to run SQL queries in Spark. One is using Python API which

[39:51] is Spark.SQL and the other one is percentage SQL. As a data engineer or data analyst, you will be running all these queries a lot either for ad hoc analysis or for your pipelining purpose. Let's now look at how you can perform

[40:06] joins in Spark. You can perform joins using SQL query or using Python APIs. So I have created a new notebook and I have two data frames that I have created on fly. First one is customers. Second one is orders. Okay. And here I have

[40:22] specified schemas and then I use spark.create data frame. When I display spark.create data frame. When I display them they both looks like this. So in customers data frame we have one record where customer ID is null. In orders we

[40:38] have reference to some customer ids which are let's say null and look at this five customer ID it does not exist here so this kind of inconsistencies will help us demonstrate the concept of joins better the APIs look pretty

[40:54] similar to panda's API so here I can just do df orders dot join df customers just do df orders dot join df customers on some column so how does these two

[41:06] column relate to each other well through customer ID. Okay. So in orders you have customer ID and see customer ID is a primary key in this table and you can join uh both of them using this customer ID to be explicit you can specify the

[41:21] name of the parameter. So you will say on customer ID and how do you want to do it? Well there's a parameter how where you will say left join right join inner

[41:33] join. By default it's inner join. Okay. So, df inner So, df inner and then display df inner. See it and then display df inner. See it performed a join. Now, inner join means

[41:48] you should have that customer ID in the second table which is your customer table. Otherwise, it you will not see the row. Okay. So if you look at both the row. Okay. So if you look at both the tables here uh you will notice that

[42:02] in the orders table this particular row right see this customer ID is not right see this customer ID is not present here also this customer ID is and you can't say null is matching null here okay null means nothing so this is

[42:16] also not matching so only matching rows are 1 2 3 4 5 and six so you have only six rows so when you perform from inner join these rows with order ID 104 and

[42:29] join these rows with order ID 104 and 106 will not be displayed. See, so here you don't see 104 and 106. Okay, so that's what is inner join. If you do left join, okay, so let me do left join and see what happens. From your SQL

[42:44] concepts, you know that left join will keep all the rows on the left table. So left table is order table. Okay. So order table have order ID 1 0 1 to 108.

[42:57] So it will keep all of that. See 1 0 1 2 3 4 5 6 7 8 it will keep all of that and in a customer table if it doesn't find something right see for this null these are the these three are the columns from customer table and put null

[43:13] columns from customer table and put null here. So similarly you also know like how write join performs and so on. Now one thing that will help us write our queries better or perform our join better is this concept of alias. So when

[43:28] better is this concept of alias. So when you have this data frame and when you do you have this data frame and when you do alias O so this will now this O is nothing but it's just another data frame that you have created it's it's exactly

[43:40] same as this one but this alias O is similar to the alias that we use in SQL also when you're performing join with the same table this kind of alias will the same table this kind of alias will be useful. So let me create alias for

[43:55] both our customer and order. Okay. And now with that you can perform the same join that we did here, right? The same one. But you can use now the alias. one. But you can use now the alias. Okay. So this is pretty kind of simple.

[44:09] But let me show you another query where this is particularly useful. See here when you look at all the columns, the country column is kind of common in both

[44:22] of the tables and this is creating ambiguity. You don't know which table this country column refer to versus this right. So you you want to say okay this

[44:34] is my orders country or shipment country you know from where you are shipping it do I do that? So the first thing I will do is this. See let me do this and let do is this. See let me do this and let me create a new data frame called df

[44:48] inner clean. And whenever you want to write multi-line code, you can use this kind of bracket. Okay. So that will help you. of bracket. Okay. So that will help you. Now you can say dot select. And here you

[45:02] can specify the the columns that you want to select. So so I want to have see want to select. So so I want to have see customer ID, order ID, amount. Okay. See customer ID. Okay. Let me just remove this. What I want to have is order ID

[45:15] then customer ID, amount, country name and VIP. Right? Now these two country columns are are something we need to dismbig. So let me just pull them in the dismbig. So let me just pull them in the new line. Now I'm going to say f dot

[45:29] call. See it is doing autocomplete. So f dot call order dot country is order country or you can just say ship country. Okay. And do the same thing.

[45:41] Okay. Let me remove it. Do the same thing for this but this time it is customer country right C dot country is cust dot country and then you will say

[45:55] display df inner clean let's run this control enter is the shortcut that you see now you clearly know that this particular column

[46:07] belongs to my order table and this column belongs to my customer table. The column belongs to my customer table. The value of how is either left join, right value of how is either left join, right join, inner join or full join. So let me

[46:21] just show you the full join here. So full join will show you everything. So full join will show you everything. It's like a union between two sets. It's like a union between two sets. Okay. So you can say display this and

[46:35] Okay. So you can say display this and here I will have full join. So let's see what we get. Okay, we got 10 rows. Why 10 rows? Because it will give me all the eight rows from the order table, right? All these eight

[46:50] rows. And then on the right hand side, if you check if you check there is this row with customer ID four and we don't have any order here, but it will also show that and there is another

[47:04] row with customer ID null. So eight and these two, right? So all eight rows it will show and it will show also these two. Therefore you have 10. So see last

[47:16] two is ghost and Diana. You see Diana with four customer ID and this is ghost. So these these values are null. See null null null. And for this particular row

[47:29] So you see null. See you you see these three null. And for customer ID five we don't have any customer ID in that table. So here also you are saying this three null values. So that is the behavior you get by performing the full

[47:44] join. Now there is a another kind of join where you say left semi and what it will do is it will show you orders with a known customer. What it means is

[47:59] known customers are only these right 1 2 3 4. So one okay so only these right 1 2 3 4. So one okay so one okay one then two then three so how

[48:11] one okay one then two then three so how many rows get this many right six 1 2 3 4 5 6 so you will get six rows see 1 2 3 4 this is different than left right because if it was a left join you would have also got this five five row but

[48:25] here you don't get it so that's why it's called left semi which is orders with called left semi which is orders with known customers and exactly opposite of known customers and exactly opposite of This is left and die which means exactly

[48:38] opposite. Basically you get all the records from the right hand side table. records from the right hand side table. Okay. Uh or or rather see customer five

[48:50] is not present in customer ID table. So it will show you that see customer five you see and then it will also show you null this null. So whatever customers it will show you that. So it's an exactly opposite of left semi. You can

[49:05] exactly opposite of left semi. You can also perform join using a composite key or multi key. So let's say I want to perform join using customer ID and country. Okay. So when I do that I get six rows. Okay. So here see customer ID

[49:20] is one and country is this in right. So one and in. So it will perform join with this particular record Asha. Then you have two and for two you have US US. So two US so it will perform that then three you have Canada so three can CN

[49:38] okay Canada or China I don't know what it is but this is using multiple keys to perform the join all right that's all I have you can do much more but this is have you can do much more but this is good enough for the basic tutorial

[49:53] spark is popular because it has lot of internal optimization in this chapter we are going to understand the internals of spark so that we can understand why spark is so widely used in the industry and this

[50:08] interview questions as well. Okay. So this entire chapter is very important from the interview standpoint. In this particular lecture we are going to talk particular lecture we are going to talk about reading spark plans with dot

[50:23] explain. If you have used SQL, you know that in SQL, if you want to look at the that in SQL, if you want to look at the query plan, you use explain. Similarly, when you are writing queries in Spark, there is this dot explain function that

[50:39] will give you the internal execution plan. I will directly execute the explain method and we'll read that plan. So, I have read the movies table. You know, we have used this table before. And here I'm running a query to get all

[50:54] the movies which has release year greater than 2010. And then I am getting only three columns out of it. And I'm calling that df narrow column. Okay. Now

[51:07] see our data set is small but let's say this data set had millions and millions of records. It's going to execute them on multiple nodes or multiple computers.

[51:19] Okay. So it needs to do some kind of optimization. So here when you run df optimization. So here when you run df narrow.explain

[51:31] parameters. See if you don't supply anything it is going to print something called a physical plan. I'll tell you what physical plan means. But if you say extended it will print both logical and physical plan. So that sounds

[51:47] interesting. So let me let me print that and then I will explain what it means. So extended okay and control enter to execute it. So see it printed this particular thing which sounds interesting. It has passed logical plan

[52:03] analyze logical plan optimize logical plan and a physical plan. Okay. So let plan and a physical plan. Okay. So let me explain what this thing is actually. So when you write any query right see we use Python API to write that query but

[52:18] you can also do percent is SQL and write that query. So when you say select ABC that query. So when you say select ABC from table X what you have is unresolved logical plan and then you will use catalog. So catalog has information on

[52:34] your databases table etc which will create your resolved logical plan. Okay. create your resolved logical plan. Okay. So here let's say if I say that uh

[52:46] select you know in un unresolved logical plan if I have select a b c columns from table x that is my unresolved

[52:58] logical plan but when I convert to resolve logical plan it will look into catalog to see okay do we have actually x table and if we have that table do we have ABC column if they are not there then it will throw for the error. If

[53:11] then it will throw for the error. If they are there then it will uh modify that query plan. I I'll show you what it will do. It will put like ids and stuff to make it more concrete. After that it will create optimize logical plan using

[53:26] logical optimizer. So similar to how you will optimize your SQL queries, you will optimize your result logical plan and I'll show you exactly what it will do underneath. Then you convert that to a physical plan. Physical plan

[53:41] is basically how do I take this query and distribute it to my multiple nodes and distribute it to my multiple nodes so that I can get the best results in a minimum amount of time. Okay. So it will come up with two three strategies or or

[53:56] n number of strategies. Okay. That number can be anything. And then you number can be anything. And then you will use cost model to figure out which plan is going to be the best in terms of your performance, your compute cost and

[54:09] so on. And then you get the best physical plan and that best physical plan is something you will send it to your cluster for execution. Now here I your cluster for execution. Now here I have mentioned AQE which is adaptive

[54:22] query execution. We will talk about it later on in this chapter. But just consider this green box as an evaluator which can figure out okay which plan is the best and then it will send that plan to cluster for execution. Now from

[54:37] unresolved logical plan to optimize logical plan there is a component called catalyst query optimizer which will come in play and it will help you create the optimized logical plan. Okay. And when it comes to execution see here we are

[54:53] executing it. The execution happens here in the last block. At that time we will have photon query engine which is a high performance vectorzed query engine

[55:05] written in C++. It is relatively modern and that will make sure that your query gets executed really fast. If I have to summarize things differently, you have SQL query. So the role of catalyst optimizer is to create optimize plan and

[55:21] the actual execution. See, remember catalyst optimizer doesn't execute. It is just doing you know like optimization. It's like you're sitting out okay it's like going to Google maps and figuring out different paths. Okay,

[55:35] from city A to city B these are the three different paths. So catalyst optimizer will just optimize you know it will create the best uh route from A to B. And photon executor is actually you're taking a car and you're going

[55:50] from a A to B. Okay. So I hope you're getting this point. So let's look at past logical plan. Past logical plan is nothing but your unresolved logical plan. And with the help of catalog you are getting resolved logical plan which

[56:03] is this analyze logical plan. Okay. So see here do you are doing filter on release year 2010. Okay. And project means select. Project simply means see select. You are selecting only three columns. Okay. Our movies table has many

[56:16] more columns but we are selecting only these three columns. Okay. an unresolved these three columns. Okay. an unresolved relation which means you are saying workspace default and movies that is unresolved. Okay. So here we don't know

[56:31] whether you have this table or not. Okay. Because we are in a unresolved logical plan. Now with the help of catalog we are creating resolve logical plan which is this. So now we looked into catalog and we said oh yeah there

[56:44] into catalog and we said oh yeah there is a table called workspace.default.mov. See, so it found a table and then it assigned all these ids to the columns. See, these are the ids which will identify the actual physical columns and

[56:58] it is in a park format. And then out of all these columns, you are interested in only these three columns, right? See title, studio and IMDb rating. Why do I have the fourth column? Just pause the video and tell me.

[57:11] Well, we have fourth column because we need it for filter purpose. Okay, so column. And then you are doing a filter. Okay, release year should be greater than 20110.

[57:27] And after doing the filter you are selecting only see again folks project means select. So you are selecting only three columns right? So we had four columns but in the final output we are selecting these three columns. So that

[57:40] is your analyze uh resolve logical plan. Okay analyze logical plan and resolve logical plan is the same thing. Now you're applying optimizations and you create optimized logical plan. So let's see what kind of optimizations we

[57:52] see what kind of optimizations we applied. See here what we did is we had applied. See here what we did is we had this table right. So in this table u we this table right. So in this table u we had all these columns we applied filter.

[58:04] So first thing we will do is we will apply filter. Why? Because that way you apply filter. Why? Because that way you are reading less data. See if I read all the data and have it in memory and then filter then see eventually I care about

[58:21] filter data. So the extra rows which were movies less than which were released before 2010 I read it in vain right it was not useful. So here we are applying a filter but notice this we are also checking is not null because if it

[58:37] is null then it's not going to be greater than 2010. So we can ignore it. So this is one condition that is added by our optimizer and then you create the best physical plan. So the best physical plan is

[58:52] nothing but this physical plan. Okay. So let me just scroll. Okay. So I hope you can see it. Now let's check this. So you have to always read from bottom. So when when we are looking at this particular thing, read from the bottom. So see it

[59:05] is doing photo scan. Again photon is your actual exeutor. you know it's a vectorzed query executor. So it went to your table where the data is stored in your table where the data is stored in park format and it what did it do? Okay

[59:21] park format and it what did it do? Okay so it will scan all these columns. See only four columns. See it is scanning only four columns. It's not scanning all the columns and then it is applying all these

[59:35] filters. Okay. Format is spark location is see this s3 actual location of the table that you're reading. Okay, it can be table it can be file anything and then then there are bunch of parameters here. All right, so I hope you're

[59:50] getting some idea folks. It might be overwhelming. You get some understanding. Don't worry as you progress as you work with this more you will get better understanding. So I'll encourage you to write some different

[1:00:04] queries here then print the plan and talk to chat GP if you don't understand what it means then give your code and your plan and say hey ch can you explain this step by step and it will explain you if you don't want to print the

[1:00:20] entire plan then you can print something called formatted so formatted so this will print only the physical so this will print only the physical plan. Okay. So, let me just copy this

[1:00:34] plan. Okay. So, let me just copy this or we can just read it here. Okay. So, see when you have formatted plan is much readable. So, let me go over physical readable. So, let me go over physical plan once more with the with the

[1:00:47] So, see first thing it is doing is scanning your tables. Then it's doing project means it's selecting and then there is a result stage and column null to row because see when you're printing these results right see when I say uh

[1:01:03] these results right see when I say uh let me just show you here let me just show you here when I just say display this here you need the results in a row format right but in a parket it is

[1:01:16] stored in a columner format see here is rows so you have to convert the column columns to row. So that is the final stage. Okay. So see the first operation stage. Okay. So see the first operation was reading those four columns. So right

[1:01:30] here it applied that optimization and this is the beauty of spark. Okay. It will only read those columns which are required. Then it applied filters. Release year should be greater than 10. Okay. Now see it applied filter even

[1:01:44] before select. See in our code we have select first and then we are applying a filter. Right? But here it optimized it and it is applying the filter first. Okay. And then out of these four columns it knows that it needs only three

[1:01:59] it knows that it needs only three columns. So it will read only three columns in in the next stage. So in a photon scan it will just read these four columns and in project which is select right. See in project it will get four

[1:02:15] columns but in the select we have only three right here we are using only for the filtering. So it will read only three column. See input is four columns output is three columns. I I hope you are getting it. And in the result stage

[1:02:28] it's the same thing and then column to row which is converting from columnner row which is converting from columnner park format to rows. That's all for this lecture. In the subsequent lectures we will further dig down into this.

[1:02:43] We will now understand spark architecture. We just saw this code. In this code, when you call df. select and filter, it will build that plan. You know that optimize plan and then it will

[1:02:57] convert it to a physical plan. And when you call count, it will actually execute you call count, it will actually execute that plan. Okay. So what happens is in spark you we all know it's a distributed computing. So there will be multiple

[1:03:13] computers working on this. In our case, movies data set was small. So maybe it let's assume that movies data set is very big, you know, like 10 GB of data.

[1:03:25] So in that case, wherever you are running this notebook, okay, your notebook kernel, wherever it is running, that computer is called driver. So it's a main uh computer orchestrating the whole thing. Okay. So let's call it node

[1:03:40] day. Node means a computer in cloud. Okay. It will build a physical plan. So it will take you know whatever we discussed in the last lecture. Take the logical plan, optimize plan, physical plan and so on. Then it takes the data

[1:03:56] set and the data set will be divided into partitions. So let's say for simplicity let's assume it this has this data set has 100 records. Then P1 will

[1:04:08] data set has 100 records. Then P1 will be let's say 40 records. P2 is 40 be let's say 40 records. P2 is 40 records. This is 20 records and so on because it's a distributed computing. So you are going to use multiple computers

[1:04:21] to execute this code and then there will be executor nodes or worker nodes. Let's assume we have two nodes. Okay, there can be many. I'm just taking example. can be many. I'm just taking example. Node B and C. And now node A will give

[1:04:36] one partition to this and it will also give a task. Okay. So let's say this give a task. Okay. So let's say this task is T1. So the task here is to filter you using release year. Okay. Give me all the movies greater than

[1:04:48] 2010. Who has released year greater than 2010 and then pick these three columns. Okay. So that is your task. Okay. That code is your task. So when you run T1 on P1, you will get another data frame, right? And that data frame, let's say

[1:05:05] this guy had 40 records. Let's say this has 15 records because you're doing filtering, right? So you'll get less records. Then it will give P2 to here and eventually it can give P3 to either this node or that node, right? One of

[1:05:18] the node it will give. And let's say on this it runs task T2 which is again release year greater than 2010 and pick these three. And let's say it gets another this narrow data frame which has only let's say 10 records and then T3

[1:05:35] okay 20 records so after the filtering let's say you get three records. So now all these partial results it will send back to driver when this this is called back to driver when this this is called okay when df.t narrow count is called

[1:05:49] that is when this whole execution begins. So spark has this concept of begins. So spark has this concept of lazy evolution which we'll talk about in detail in the next lecture. But it will until this point when you say dfn or

[1:06:01] until this point second line it will just create a plan and when you say dot count then it will actually execute this plan all these results are sent back. Okay. So let's say 15. So this guy sends back 15. This guy sends 10 and three. 10

[1:06:18] and three. So 25 and three. So this df.net narrow count. So this driver node df.net narrow count. So this driver node will now display 28. Okay. So talking in general terms, you have a driver program where spark context or spark session is

[1:06:35] created. We also in datab bricks we get this object spark, right? Remember we created spark.t movies whatever. So the spark variable is a spark session. So driver program create spark context or spark session. Then it communicates with

[1:06:52] all these worker nodes. Okay. And these worker nodes can be any. It can be 10, 20, whatever. And then to create the worker nodes to kind of manage those, it uses cluster manager. Now this cluster manager can be data bricks managed yan

[1:07:08] in orchestration cluster management you might know. So cluster manager will take care of creating new node. Let's say if one node creating new node. Let's say if one node fails, you know what to do with it. So

[1:07:23] that node uh life cycle management is done by cluster manager. But the actual task assignment is done by driver program. So I hope you got a good understanding of spark architecture. It is a very commonly asked question. The

[1:07:37] interviewer may ask this question that okay explain spark architecture and you can just draw this on a on a whiteboard and just explain the whole flow. Let us now discuss transformations

[1:07:51] versus actions and lazy evolution. We have looked at this notebook before. have looked at this notebook before. Okay. Here when you create DF narrow transformation. You are doing filter then you are doing project which is just

[1:08:05] selecting three columns out of many. Now when you execute this cell it doesn't actually create df narrow data frame. It will only create a plan. it will execute

[1:08:17] this code and create a df narrow data frame when you call display or when you frame when you call display or when you call df narrow dot show so spark has call df narrow dot show so spark has this lazy evaluation where when let's

[1:08:32] say if you're running this code in pandas python pandas it will execute this code right away and it will create a df narrow data frame but in spark it will not create df narrow data frame with all its data okay it will create

[1:08:45] that variable but this code will not be executed. It will only create a plan and it will follow this lazy evaluation and delay follow this lazy evaluation and delay the actual execution of code until this

[1:08:59] display is called. So essentially whenever you're doing transformation whenever you're doing transformation such as filter, select etc. uh all these such as filter, select etc. uh all these methods are called transformation

[1:09:11] and when you actually execute the code right things like dot show display these are called actions the example of transformation functions are select with transformation functions are select with column filter where etc so whenever you

[1:09:26] call all these methods okay there are more actually I'm just showing you a couple of them it will not trigger the code actually it will just build a plan transformation. But when you say dot show, see at that time you're showing

[1:09:40] the results. So it has to be executed. When you say count right two pandas when you're writing uh to disk let's say you are writing data frame to disk in a park are writing data frame to disk in a park format or CSV format whatever you have

[1:09:55] to execute the code. Okay. So it is like transformation is a recipe that chef is preparing. So let's say they are preparing recipe for ras gula or they are preparing the recipe for hyderi biryani they are just noting it down

[1:10:11] okay but action is like cooking that recipe you know making that rasagula so I hope you understand like with this analogy transformation is just building a recipe actions is actually cooking the food right so here all these functions

[1:10:26] will only build the plant right the physical plant and all these action will actually exist execute the plan. So the definition here is transformation is an operation that defines a new data set from existing one but does not execute

[1:10:39] it immediately. Whereas action is an operation that triggers actual execution okay and returns a result. So action will either return a result okay to the will either return a result okay to the driver program or it will write data to

[1:10:52] data storage. Now let's understand why do we do lazy evaluation. There are couple of benefits. The first one is query optimization. Just look at this query. If you have eager execution which is if you're executing at every step.

[1:11:08] See right now I'm showing one line but many times you can have df1 is equal to df do.filter then df2 is equal to df1 dot select. You know people do that. So if you don't have lazy execution then what will happen is it will first filter

[1:11:22] on age greater than 18. Then let's say this data frame has five columns. Now it will select only one column select name and then it will filter on column name not equal to blank. But if you have lazy execution you will build a plan okay you

[1:11:37] will not execute until you call df.count or df.t show. So when you create the plan okay of this entire transformation then instead of running three separate transformation you can combine both filters into one right. So

[1:11:52] you have two filters right like this one filter and this you can combine them into one and you can push down filters to the data source. So let's say when you are creating this data frame right at that time you can apply this filter.

[1:12:07] records you are reading maybe 200 records from the disk then only read name and age columns. Let's say this data frame when you're reading from disk data frame when you're reading from disk it has 40 columns but in my code I'm

[1:12:20] using only two columns age and name. So in that case I can optimize on this columns. You can also avoid unnecessary work. See here I'm showing only first five rows right. But let's say if you're running this in pandas and if let's say

[1:12:35] this is a huge data frame this might contain million records right? But in pi spark what will happen is it will not execute the code here. It will execute code only here. So now it knows that okay user wants only five rows. So it

[1:12:50] will execute and it will just show you five rows. It is also memory efficient. Uh the opposite of lazy execution is eager execution, right? You are just executing it every time. So eager execution if you're doing uh let's I'm

[1:13:06] just giving the this example where you have a file or of 1 GB and let's say you are reading it into a data frame. So that's your 1 GB data. Then you are applying a filter. Let's say this is DF1. Now you have DF2 right this is DF1

[1:13:21] this is DF2 DF3 so you have three data frames and it will store 1.7 GB but with lazy execution it will create a pipeline okay 1GB filter select and

[1:13:33] result so with that it will occupy much less memory it is also fall tolerant see this is the beauty of spark it provides you fall tolerance we saw this example

[1:13:47] you fall tolerance we saw this example before And in that case let's say node C okay execute fails due to whatever reason so node C executor two let's say it executes this task two with P2 fine okay there's no

[1:14:03] problem but after some time when it executes task three with P3 let's say there is a problem node C is down let's say it goes down crashes something now the driver knows you know driver is orchestrating the whole thing it knows

[1:14:19] that this is failing. So what it will do is it will assign this task to another node. See either it will assign the task to this node or it will spin off the new node based on how you have configured things. Okay. But since it has the

[1:14:34] entire plan or entire recipe. See this is important. When you have entire data set and give it to someone. It's not like you have half cooked recipe, not like you have half cooked recipe, right? It's like you have a chef, right?

[1:14:49] Let's say this is a chef. So remember the chef example that we discussed before. This is the main chef, master chef. And this is these two are chef. And this is these two are different chefs. Now let's say this chef

[1:15:01] has cooked some recipe. Okay, they are creating a vegetable biryani. They have cooked vegetables and they have not mixed rice yet. And let's say this chef falls ill and he has a problem. Now you don't want to give

[1:15:16] this half cooked recipe here. Since you have the entire recipe, this master chef will give that entire recipe to a new chef and say that okay this is the recipe, these are my row ingredients, prepare it rather than

[1:15:30] giving this halfcooked recipe to someone, right? It becomes a problem. So that is the beauty of creating this physical plan. It gives you a fault benefits. There are more benefits as well but uh you have to just remember

[1:15:45] well but uh you have to just remember that with lazy execution you get all remember the difference between transformation and action. is something that will just create a transformation. It will not actually

[1:15:59] trigger the action. Action is something that will actually execute the code and give you the new data set or new result set or it will write it to a disk. Let us now discuss narrow versus wide transformations. The example that we saw

[1:16:14] before where you are just filtering on data and selecting bunch of columns is narrow transformation. Narrow transformation is a transformation where you don't have to exchange data between different nodes. So here let's say you

[1:16:30] are getting in partition one you're getting total 40 records right and these 40 records have title studio IMDb rating release year etc now what you'll do is release year etc now what you'll do is when you run task t1 you will first

[1:16:45] filter these records right so now to filter the record here you are looking at release here so let's say form 40 you get 15 records so you are working on this P1 set only you don't have to check P2 and then from that 15 records let's

[1:17:01] say these 15 records originally movies table has 10 columns so this 15 records will also have 10 columns out of that you're picking only three columns and same thing for P2 and P3 you don't have to go to different node to execute your

[1:17:15] task but what if you have operation like this where you are first filtering but then you are grouping by studio and then you're calculating average revenue of every studio let's say Marvel studio is this much average revenue

[1:17:31] this much average revenue um you know your studio is this much revenue and so on now in that case you'll have to look at data on different nodes so let's say first you filter down data using 2010 so let's say you have

[1:17:47] filtered it okay so you filter here here and here and you got all this new data and here and you got all this new data set but now to perform group by you have to look at all these three data set okay so maybe some different node or let's

[1:18:02] so maybe some different node or let's say node B is doing group by now it has to get this filter data okay this data and this data needs to go to this node

[1:18:14] and this node can perform group by operation same thing with sorting right if you're doing sorting etc you have to go to different node to get the data and this is expensive okay this is going to slow down performance but but we don't

[1:18:27] have any other option because there will be data transfer between different machines. So narrow transformation is each output partition depends on a single input partition. Okay. So there is no suffer essentially. Whereas Y

[1:18:40] transformation output partition depends on multiple input partitions and requires suffer or exchange between the stages. Okay. So these are the examples of narrow transformation. You want to filter on s certain column, you want to

[1:18:56] create a new column, you know map type of operation. These are all narrow transformation. Y transformation on the other hand is group by order by join. You know for all these you need all the records with a subset of records. You

[1:19:12] can't perform this operation. So these are the some of the common operations for narrow and wide transformation. Now in my notebook I performed this particular transformation and since this is running on serverless

[1:19:28] and since this is running on serverless compute on datab bricks I had to set explicit partition and also I had to call repartition. Okay but this code you can ignore this is just for serverless compute on datab bricks. Here we are

[1:19:41] getting average revenue based on the studio and in the plan right when I run studio and in the plan right when I run plan explain the physical plan will have things like see suff exchange you see suff exchange so it is doing

[1:19:55] suffing and exchange because we are running y transformation let's now discuss very important topic of partitions and parallelism we have seen this particular diagram before And we all know that spark works

[1:20:11] on a distributed computing principles. And here this P1, P2, P3 are the And here this P1, P2, P3 are the partitions. So partitions is a unit of parallelism. It is not just a data partition. Here you will have a

[1:20:24] corresponding task. So P1 will be processed by task T1. P2 will be processed by task T1. P2 will be processed by task T2, T3 and so on. So processed by task T2, T3 and so on. So partition can be considered as a unit

[1:20:37] partition can be considered as a unit for compute parallel compute. Now we have seen this example before right where if you want to prepare 2,000 puris you can have four chefs each preparing 500 puris. What if someone decides to

[1:20:51] 500 puris. What if someone decides to have 2,000 chefs? 2,000. Is it good? No. Right? Because then one chef is preparing one puri and it's not cost efficient and also you have to do lot of coordination also. What if you have only

[1:21:04] one chef that is also not good correct? So whenever you are doing parallel processing number of partitions that you have or number of parallel tasks that you are running is a number that you have to decide with a lot of care. You

[1:21:19] know you need to keep a right balance. You don't want to have too many workers. you don't want to have two less workers. Okay. So when you have this kind of architecture where you have let's say three computers and each computer is

[1:21:33] way every computer can have multiple partitions. Okay you want to keep a right balance and to maintain this balance we have couple of functions. The balance we have couple of functions. The first function that can help is

[1:21:48] repartition. Let's assume this scenario. You are grouping by studio for a movies data set and you're calculating the average revenue per studio, right? So average revenue per studio, right? So you are calculating let's say for

[1:22:01] you are calculating let's say for example my Marvel studio has average revenue of 20.5 billion. My Dharma production has average revenue of 6.7 billion. Right? That is what this line will do. And here you have all these

[1:22:15] partitions. But what happens is when data frame is created right like see this is very important we need to visualize this where is my DF right this visualize this where is my DF right this DF okay tell me where is my DF this data

[1:22:28] frame that I have where is it well this entire thing is my DF see this entire thing right let me just I wish I can draw a good

[1:22:40] diagram so this entire thing this thing is my DF because it is distributed right If it has thousand records, it will be distributed across all the machines. And Spark provides you abstractions so that you don't have to worry about all these

[1:22:54] details, right? Uh you can write a code df.group by. But internally it will be df.group by. But internally it will be uh divided between multiple nodes. Now uh divided between multiple nodes. Now when you run group by what may happen is

[1:23:09] see let's say you are running group by you want to find Marvel's revenue. Marvel can be present in all these records because when you partition, you did not partition by studio, right? The partition was done maybe in a roundrobin

[1:23:21] fashion or maybe by using some other strategy. So now see this is my DF, right? Now you're creating a new DF called revenue DF. So what will happen is let's say I have two records of Marvel here. Okay? Or let me let me just

[1:23:35] draw them with a different color. So let's say um this green is my marvel, right? So let's say I have two records here, one record here, this doesn't have a record and this has three records. This

[1:23:50] has four records, right? And this doesn't have it and this is one record. Now it will have to do data movement because to calculate revenue average revenue of Marvel it needs to access all these records. So to generate revenue DF

[1:24:06] it will have to do all the data movement. Correct? So it might let's say if it is running this operation on node A it will you know these data all these

[1:24:20] records will be moved here and data movement is always expensive right so that that's going to hit performance folks okay now I get all the Marvel records and I can now create a partition for revenue DF by

[1:24:35] the way so similarly you will calculate revenue for dharma production all other studios, right? And how you had DF between P1 to P6 partition, you will between P1 to P6 partition, you will have your revenue DF also in multiple

[1:24:50] partitions. Let's say you you have a huge data frame, right? Revenue data frame is also huge. Then we can call this partitions let's say PA. So you might have PA here,

[1:25:05] PB here and maybe PC here. Let's say only these many partitions are needed. So in this case your revenue DF is this. [snorts] See this whole thing. Sorry for the bad

[1:25:22] diagram but this whole thing is a but this whole thing is a revenue_df.

[1:25:36] repartition helps you optimize this. Okay. So Okay. So when you do dfred repartition, see here. when you do dfred repartition, see here. So again my df this P1 to P6 is my DF.

[1:25:52] Okay. So this is your original DF. Now when you do DF.Partition Partition six when you do DF.Partition Partition six studio. So what I'm saying is create six partitions. Create six partitions. Okay. Uh I think

[1:26:07] Create six partitions. Okay. Uh I think okay. P3 P4 actually seven. I mean the original one has seven actually. See P this is P4. The numbering is wrong. This is six. Seven. There are total seven partitions. So when I say DF.Repartition

[1:26:21] partitions. So when I say DF.Repartition 6 studio what it will do is it is like a hashing operation. it will do uh partitions so that studio is present on a single node basically in a single partition right so here so let's call

[1:26:38] partition right so here so let's call this partitions as x1 x2 etc okay so here let's say by studio so every records for one studio will be in one partition so if I have one partition here x1

[1:26:54] this will have all all the Marvel records let's say and partitions are big so it can have multiple studio also let's say it has Z studios let's say it has Z studios then X2 let's say it has a Colombia

[1:27:07] then X2 let's say it has a Colombia pictures right then X3 has I think your pictures right then X3 has I think your Sharma productions right so now again one partition can have multiple studios right so what happens is now

[1:27:24] when you group by studio and when you run this aggregate operation for revenue it doesn't have to do data movement because the records for single studio are present in one partition on the same

[1:27:36] are present in one partition on the same node. So now it can it can quickly uh do that aggregation and by the way this x1 x2 x3 etc will form uh new data frame. So that is base right. So let me just show you that. So this right this data

[1:27:54] frame is your base. Base is just a name of a data frame right it can be anything of a data frame right it can be anything df1 df2 etc. So in spark data frames are immutable. So once you create it you can't change it. You can only create new

[1:28:07] one. So I had df my original data frame for movies records. I created base when I did repartition into six. Okay. So six partition. So it will it will actually partition. So it will it will actually create six total partitions. So x4

[1:28:22] and number of nodes doesn't matter it can be x5 x6 okay so it will create six partition but it will make sure that the records from same studio so will be

[1:28:35] available only in a single partition so all the records of Marvel studio will be in x1 you will not file any marvel record in x4 x5 x3 or any other partition okay so that's a key thing that we have to remember this is also

[1:28:47] useful when you're doing multiple operations So let's say see I'm doing group by studio then I'm running window operation on studio. So here I am ordering movies within the studio based on the revenue right. So if I know that

[1:29:04] I'm going to be doing multiple operations based on studio as a key then it's better that you create this data frame which is optimized for studio by

[1:29:17] key operations. Okay. So this is where the skill of data engineering comes in play right like you are sensing that you are doing operations based on studio key therefore let me repartition first and then do it see if you don't do

[1:29:31] directly is going to work but it will be slow. All right. So this is where the skill comes in play and this is where you can perform in the interview right if you know all these internal details during the interview you'll be able to

[1:29:44] answer those questions. So let's see this entire thing in action. I'm going to go to my workspace and I have this folder spark internals. So let me create a new notebook here and I'm going to call it uh repartition.

[1:29:59] call it uh repartition. Okay, let me hide this. The data frame I'll create in the same manner that we have done last time. And then I will also do I just want to know how many

[1:30:14] uh unique studios I have. So you can run SQL query by writing uh this right. So SQL query by writing uh this right. So just say select studio from or actually just say select studio from or actually distinct select distinct studio from

[1:30:28] distinct select distinct studio from workspace dodefault dot movies. Okay. workspace dodefault dot movies. Okay. And

[1:30:40] total 22 unique studios. Number of records are 37. So this is a very small uh data set that we have. But let's run repartition function. You are specifying number of partitions and as an option you're

[1:30:56] specifying studio right. So this is repartition by key. And when you look at this see it's folks it's another data frame. It will look similar to this one right? Maybe number of the record order might change but it's the same data

[1:31:11] frame. But what I want to do is I want to run explain. And in explain what you're seeing is see here first it is doing the scan okay then it is doing

[1:31:25] this hash partitioning for so this is important studio is a key and if you have studied hash functions it will do hashing and it will try to put the records for the same studio in a same partition. Okay, so that's how this

[1:31:41] partition. Okay, so that's how this hashing function will work. Also, hashing function will work. Also, if you don't specify the key, it will do partitioning repartitioning in a roundroin fashion. So, okay. So, this is

[1:31:56] repartitioning by roundroin. That's why I have RR I have RR and let's say you are doing explain, right? So see here if you look at it the

[1:32:09] repartitioning let's see where it is see round robin repartitioning and it it is dividing it into six different partitions and if you want to verify you can write it to park file so I will write it to park file okay and I'll

[1:32:24] verify it and for this uh I will create a new volume called this so let me create a new volume by going to data injection so data data injection

[1:32:36] remember we we have created volumes before so right now we have workspace volume just for the separation I'm doing this so repartition demo is my volume okay

[1:32:52] so when you create it what happens is you go to catalog refresh default and here you find this repartition right so this is the volume

[1:33:04] and in that volume I'm going to write the records. So it will write it in a the records. So it will write it in a park format. I think the name should be repartition. So let me just do repartition.

[1:33:19] Okay. And run this. Whatever is the name here common sense. Okay. I don't have to explain that. Okay. Now let's refresh explain that. Okay. Now let's refresh this and let's see what we get here.

[1:33:35] See, watch this. There are six files. See 1 2 watch this. There are six files. See 1 2 3 4 5 and six. Right? So that shows that this repartitioning worked. All right? That's all I have for repartitioning.

[1:33:48] Just to summarize, you have to repartition based on your use case. repartition based on your use case. Okay? and repartitioning will allow you to perform your operations in an optimized way. There is another function

[1:34:02] we are going to discuss that in our next lecture. lecture. The second function is colesque. So let's say you have this kind of scenario. Okay. Now usually

[1:34:17] you have one task associated with one partition, right? So here P1 so there will be task T1 associated with P1 T2 T3 and so on right and then uh

[1:34:32] there will be let's say this machine has four cores so each CPU core can work on this processes and this will work okay but let's assume you have only single

[1:34:44] core machine I mean that's not the case nowadays nowadays you have four core or more but let's assume you have only single core Okay. So in that case you are unnecessarily creating this partition. Okay. Maybe there is a chance

[1:34:56] for some optimization and that's what cos will help us with. and that's what cos will help us with. So when you say df coles see this df this is the df right this entire seven partition is a df. But when you do df

[1:35:10] partition is a df. But when you do df dot coals it will reduce the partitions. Okay it will it will reduce the partitions. So total partitions that it will reduce to will be three. So it will figure out the best strategy making sure

[1:35:24] that there is a minimum data movement. So if I want to create three partitions out of this seven partitions without moving data, what is the best strategy? You tell me. Well, it's easy, right? You you just merge this. You merge this and

[1:35:38] you create this new partition. You merge these two and you create this new these two and you create this new partition. you merge these two create this new partition right that's the best strategy okay so let's say you

[1:35:53] the best strategy okay so let's say you call this x1 x2 x3 and let's say this call this x1 x2 x3 and let's say this data frame is x so x is equal to 3 this data frame is x so x is equal to 3 this so this whole thing right this whole

[1:36:06] thing becomes my x data frame I hope you're getting this point what happens you're getting this point what happens when you do cos 2 So remember coales is used to reduce the number of partitions and when you have seven partitions and

[1:36:21] you want to create only two what will happen? How do you do it with minimum data movement? Well see one way if if I ask you to use your common sense is okay you you you merge all this and you create one partition x1 right

[1:36:36] then the second option is either you move this here or you move this here. move this here or you move this here. See uh cos minimizes data movement. It doesn't prevent it completely. So maybe I'll move p5 p6 here. Okay. And

[1:36:51] I kind of merge it. Okay. So I merge all this and create another big partition called x2. All right. So two means now my resulting partitions are only two. What if I do six? See this numbering is wrong. This

[1:37:07] should be P4, P5, P6, P7. I have seven partition I want to create six. How do you do it with minimum data movement? Well, so you merge any two partition on

[1:37:19] a single node. So you can merge these two, these two or these two. Maybe I'll merge these two. Right? So now this is x1, x2, x1, x2, x3, x4, x5 and x6. So see I got six

[1:37:35] partitions. What if I do 10? Well, cos can only increase it. So in this case, it will do nothing. Okay. So remember coales is

[1:37:47] used to only reduce partitions. It will never increase it. Now what are the cases. Coles will eliminate task overhead from too many small partitions.

[1:37:59] overhead from too many small partitions. So let's say I have four cores okay four CPU cores on each of these machines and I created for example 50 partitions and I created for example 50 partitions each right like I created 50 5050 and

[1:38:15] with each partition you will have a single task so it will have to do lot of task distribution it's like hiring thousand chefs for preparing 2,000 puris thousand chefs for preparing 2,000 puris you know So a better strategy might be

[1:38:31] let's say see if I if there are four cores I will not do three I will do four four so I will do coilless 12 so I will create 12 partitions so I will create

[1:38:44] create 12 partitions so I will create x1 x2 x3 x4 x5 6 x1 x2 x3 x4 x5 6 7 8 9 10 11 and 12. This way I'm utilizing all my CPU cores. Okay, in parallel I can run everything and get

[1:39:00] the efficient results. The second benefit is it will optimize file output. So let's say you have a data frame with thousand partitions and when you say it will actually write thousand tiny files. It's not efficient. It will write

[1:39:17] one file per partition. But you can do df cos 10 write and it will create only 10 files. Third benefit is it is minimizing data movement. So as you work on the projects as you work on spark more you will realize that sometime you

[1:39:34] have to use coales function sometime you have to use repartition. Okay so you have to use it wisely based on the use case and that is where your skill will come in play. Let's quickly go over the concept of

[1:39:47] managed versus external table. when we created our movies table and if you go to details you will see that the type is managed. Now I know you are not seeing

[1:39:59] this company stocks or support logs table. Don't worry uh as we move ahead we are going to create this company stocks table and if you look at this table it is an external table. So what is the difference between the two? See

[1:40:15] when you look at any table in data bricks it has two components. One is data and the other one is metadata. So what you see in the details here is metadata. Okay created it who created it. What is my storage location all this

[1:40:33] information see properties this is called metadata. And another way of looking at metadata is you go to SQL editor and you type this command describe extended uh this. And when you run this command

[1:40:47] you will see all this metadata once again see created by command is manage again see created by command is manage location. So for manage table data

[1:40:59] bricks will manage both the content and metadata. All right. So to summarize any table will have two components content and metadata and if it is a managed

[1:41:11] table data bricks will manage both. But for external table so this is the external table. I know you're not seeing it right now when we look into the delta format and delta lake concept little later on in this video I will show you

[1:41:26] how to create it but you can just watch right now. So this table right now. So this table has its actual data in S3. Okay. So has its actual data in S3. Okay. So Amazon S3 has a bucket called CB company

[1:41:39] stocks and the actual storage location is in S3. Whereas for movies the actual storage location is default storage which means it's stored in datab bricks which means it's stored in datab bricks itself. Okay. So for external table data

[1:41:54] can be stored in S3 or any other external location but metadata is inside datab bricks. So for external table once again I'm repeating metadata is inside again I'm repeating metadata is inside data bricks but actual data can be

[1:42:09] outside datab bricks when you have external table multi-tool access is easy. So in the industry you will see all these companies who will have this huge data lake in S3. So in that case when they use datab bricks all they do

[1:42:23] is they use it as an external table. So that metadata is still managed by datab bricks. And then for manage tables data governance is easy because unity catalog governance is easy because unity catalog unity catalog is a unique feature of uh

[1:42:39] data bricks. We will look into that a little later but that controls everything. Whereas in external table well there is data governance because metadata is still inside data bricks but it will require discipline. Let's say if

[1:42:53] metadata but it will not delete the actual content which might be stored in S3 or any other location. So the use case just in case if you're wondering when to use manage table versus external tables when you're doing quick analytics

[1:43:06] and fully govern data use manage table. But if you already have uh years of data But if you already have uh years of data stored external either in ADLS or S3 uh then uh it's better to use external table right. So the use case here is

[1:43:20] data shared with other tools or pre-existing data set and when you have data in S3 or ADLS you can access it from data bricks and you can access it from other tools also that part becomes easier.

[1:43:34] architecture and this is one of the popular questions that gets asked in data engineering interviews. This term sounds like a jargon but concept wise

[1:43:46] it's a very very simple concept and the way they define medallion architecture is it's a data design pattern used to logically organize data in a lakehouse.

[1:43:59] Now we have studied lakehouse before. Lakehouse is nothing but a combination of see so lakehouse is nothing but a combination of data

[1:44:11] lake data lake plus data warehouse data warehouse. plus data warehouse data warehouse. When you combine these two it makes a lakehouse. And when you're doing this transformation

[1:44:25] uh you want to uh do this transformation incrementally and progressively to improve the structure and quality of data. So there are three layers bronze, data. So there are three layers bronze, silver and gold. Okay. Now this is the

[1:44:40] getting your raw data. So this is let's say your OOLTP system. Okay. Let's say this system here, this entire system is your OLTP system and you're getting some batch data and streaming data and this

[1:44:57] raw data gets into your OLAP and in your OLAP let's say this entire block that lake house. So it's a combination of data lake and data warehouse you will

[1:45:09] have three layers. So bronze is very raw data. Whatever data you extracted from OLTP, you store it here. So that is raw. Then you do data cleaning, right? You Then you do data cleaning, right? You filter null values, you will fix any

[1:45:25] you will clean the data. So that is your silver layer and gold layer is business level aggregate. So you generate let's say some new measures etc. So let me just show you the example here. So I have this uh website analytics data

[1:45:42] where uh if you look at any popular websites they will be tracking their users uh interactions with the website. So let's say user 001 visited this So let's say user 001 visited this website homepage especially on this day

[1:45:56] and time and they spent 15 seconds. Then user 001 once again visited that website and uh now they they visited product page and spend 25 seconds. Right? So

[1:46:09] this is just a general website analytics data. This is a raw data. You can see some null values. See empty values some invalid timestamp. It's a very raw messy data. This is what you will store in bronze layer. You see this bronze tab.

[1:46:24] Now when you go to silver layer in medallion architecture you will do data cleaning. So maybe you will remove this row because it has a null value. You will also remove this row because it has invalid time stamp. And after doing data

[1:46:38] cleaning see this two rows you will delete. So you will have three rows left. And that is your silver layer. So in silver layer you have done some data cleaning some augmentation you have furnished the data. Now this data is not

[1:46:52] fully ready for your analytics yet because one of the KPI that you want to because one of the KPI that you want to know is average span time spent by any know is average span time spent by any user on your website. So maybe for user

[1:47:06] 001 average time is 15 and 25. So two sessions right? Two session they spend 40 second total. So average time spent is 20 second. So maybe in a gold layer

[1:47:18] you want to have some kind of KPI like this. Okay, user ID, total visit is two, average duration in seconds is 20 and so on, right? So

[1:47:30] it's very simple, right? Bronze has all the raw messy data, silver has some clean data and gold will have BI ready data. BI ready data means you would have created some additional measures, some

[1:47:44] additional KPIs since this concept was introduced by data bricks. This is a popular page folks. I'm going to link this page and you can read through it. this page and you can read through it. As I already explained, bronze is all

[1:47:57] raw data. Okay? You have load time, process ID and so on. Silver is cleans and confirmed data. Okay? You have removed your duplicates. You have your

[1:48:09] master customer stores, non-duplicated transactions and cross reference tables. And then gold is a curated business level tables. So the benefits of lakehouse architecture is simple data model. It's very simple, right? You have

[1:48:24] raw data in your bronze layer, clean data in your silver layer and you have data in your silver layer and you have your business ready data in gold layer. When I was at Bloomberg, we used to have n number of layers, not just three

[1:48:38] layers. And we used to call it product one, product two, product three. At every level, see, product zero is raw data. It's not a product that you can sell to customers. But then product one is little bit clean, product two is more

[1:48:51] is little bit clean, product two is more clean. You are enriching at every stage. And you are adding additional measures, additional KPIs. And based on the business needs, you can have this end number of layers. But when it comes to

[1:49:03] Medeline architecture specifically, they try to keep things simple. Okay, bronze, silver and gold and these are all the benefits. Okay, so just read through this article. It's a very simple concept and this is something that gets asked a

[1:49:16] lot during data engineering interview. So I hope concept wise it's at least is So I hope concept wise it's at least is clear what exactly is mine architecture. As promised, now we will talk about unity catalog. Unity catalog is a

[1:49:31] unified governance solution for data and AI assets on datab bricks platform. AI assets on datab bricks platform. Okay. Now when I say data assets I mean Okay. Now when I say data assets I mean tables, views etc. AI assets means

[1:49:45] models etc. Right? You can have trained AI model which you have stored on data AI model which you have stored on data bricks. And this unity catalog provides bricks. And this unity catalog provides governance. So governance means access

[1:49:57] control and how you want your data to be governed. You might have an organization where let's say there are two teams right. So there is data analyst team right. So there is data analyst team analyst and data engineers okay and

[1:50:11] there might be AI engineering team as well. Now in datab bricks you will have well. Now in datab bricks you will have uh your databases your AI models and so on. So let's say I'm in datab bricks here. Okay. And let's say I have some

[1:50:27] database. Now using Unity catalog you can give all the access. So let's say for data analyst group you want to give only read access. Okay. And to data

[1:50:39] engineering group you want to give right access. So you can give all of that. And access. So you can give all of that. And these teams they are called groups in data bricks. Okay. They're like role based privileging. uh when you use

[1:50:54] role-based privileging you have roles right so these are let's say roles and then you can have users tied to it okay so let's say Tom Peter Mohan these are all the users tied to this group so when you give read access to a group or to a

[1:51:09] role automatically these folks get that read access and let's say you can have different people basically these are the name of the people and when you give right access so these people will be able to write data to it and then you

[1:51:23] can have trained AI models right so let's say I have these models and I have another group called AI engineer group so this group will have write access and read access both right they train the model etc and then you can have another

[1:51:39] software developers group let's say these software developers are integrating these models into the rest of the ecosystem then they are going to of the ecosystem then they are going to have read access in data bricks you have

[1:51:52] three levels of hierarchy at outer level what you see is default catalog right this workspace is a default catalog but you can create additional cataloges too so I have created two cataloges dev and prod so you just go here you say create

[1:52:06] a catalog right so you can have cataloges based on your environment dev cataloges based on your environment dev prod etc you can also have catalog names prod etc you can also have catalog names based on your three layers of medelian

[1:52:18] architecture bronze gold silver etc or you have it based on team. So you can have one catalog for sales team then you have one catalog for sales team then you can have another catalog for let's say

[1:52:31] marketing team etc. And within catalog the second layer is your schema. So if you hover your mouse cursor over you get schema right.

[1:52:43] So it is always catalog then you have schema and then you have tables volumes views and so on. All right. So just understand Unity catalog provides these three levels of naming convention. Now it is essentially a unified governance

[1:52:57] solution for data and AI assets. Okay. It provides centralized access control. So the access control is centralized. Previously what used to happen is let's way I have worked in such organizations where there are databases which are

[1:53:12] owned by different teams and there is different type of access control. So different type of access control. So let's say team A is owning customer let's say team A is owning customer database right let's say you have some

[1:53:25] customers data and then team A has this customer table then team B also will have customers table they will have duplications and they will have different kind of access so let's say this database can be accessed by two

[1:53:38] different groups right like G1 and G2 but here the access control might be different here it might be G1 and G3 control. So it's like teams are working in silos and they don't have unified

[1:53:53] governance. But with unity catalog what you get it right. So with unity catalog what you get is centralized governance. So everything is in data bricks. Let's say all your data is in datab bricks and then you have catalog right. So let's

[1:54:08] say you have catalog one, catalog 2 and so on. Under that you have schema, right? Schema 1, schema 2 and these will have tables. Now even let's say if you have customers table since data is in central place you will make sure you are

[1:54:23] let's say either this or this table goes here. So let's say here I have customers table and then through unity catalog I can give uh the the access control to

[1:54:35] can give uh the the access control to various groups. So if you go here um and look at this catalog right. So if I if you click on the catalog see here you can provide the permissions. So you can say grant permissions to your users and

[1:54:49] by the way you can create users etc from here. So if you go to settings identity and access and group right I have created a new group here let's say data analyst and data engineers and then I can go to catalog and I can give that

[1:55:05] permission. So let's say in dev dev catalog okay let's look at dev catalog now in permissions I can give all these permissions. So I can say okay data permissions. So I can say okay data analyst so data analyst folks have read

[1:55:21] here you can control what kind of access you want to give and then data engineers have right access and so on okay so have right access and so on okay so under catalog then you have schema

[1:55:34] schema also have permissions see schema also have you can just go to grant and permissions and then table also has permissions permissions Okay, so you can assign permissions at a

[1:55:47] fine grain level at different levels using this Unity catalog. So it provides governance on tables and views, functions, AI models, notebooks and dashboards. And this is the diagram I took from their GitHub. So essentially

[1:56:02] see when you look at your data, what is your data? You can have tables, right? Manage external views and so on. You can have objects which is volumes like your files, audio files and so on. Or you can have models right ML models, vector

[1:56:16] databases. You can have functions you know like AWS Lambda type of functions. know like AWS Lambda type of functions. And then Unity catalog provides unified And then Unity catalog provides unified governance on all this you know data

[1:56:29] that I have. It will provide governance on who can access what what kind of privileges each of the groups or each of the individual users have etc. And then unity catalog also provides unity rest API. So let's say if you want to access

[1:56:45] it through AWS, Google cloud etc. then you can access it. Remember that data bricks is available right in in different platforms, different cloud providers. So using all these tools you can access unity catalog and access all

[1:56:59] can access unity catalog and access all these objects uh through a centralized governance. All right. So unified centralized governance is an obvious benefit of unity catalog. Then fine grain access control remember catalog

[1:57:12] schema table etc. Automated lineage and data discovery. By lineage I mean this particular tab. So lineage means when you have any data what kind of systems

[1:57:24] it has gone through. Okay. So let's say you have this particular table. Okay. So let me just show you here. So let's say you have some customers table. Okay. Or let's say you have some transaction

[1:57:37] let's say you have some transaction table transaction table. Now that table is being updated by set of system. Let's say A B C and D. Right? So let's say this transaction table first let's say someone makes a transaction on their

[1:57:52] someone makes a transaction on their mobile phone and that that mobile app is what is updating that table and then it goes to downstream system let's say that the system let's say it's doing some data cleaning then it's goes through C

[1:58:08] let's say it's doing further adding new columns right so typically your data will go through multiple system and when you have something wrong In this table you want to backtrack. Okay, it's like you are a spy and you want to figure out

[1:58:22] what are all the places where data has traveled. Okay, so it traveled from A to B to C and that's how I got into this stage. So this is called data lineage. stage. So this is called data lineage. Okay, data lineage and unity catalog by

[1:58:37] default provides that kind of feature. Right now it's just a simple table. So you are not able to see much of the things here but uh let me just look at the movies table. I think movies table will also not have uh anything

[1:58:51] interesting. Okay. At least here it is showing you who all updated uh that particular table. But anyways it provides data lineage by default and another thing it provides is data discovery. So if you want to search

[1:59:06] through this data there is this search thing right? You can add the tags and everything. So if you have a movies table, let's say you want to add some tags so that your data is easily searchable, right? You can add all of

[1:59:18] that and through the discovery feature when you have a huge organization and when you're trying to locate the data, this unity catalog will help you in that. Let's discuss another exotic topic

[1:59:32] Let's discuss another exotic topic called acid atomicity, consistency, isolation, and durability. And often during data engineering interviews, interviewer will touch base on this point. Let's say you are doing a

[1:59:45] transaction. You're transferring $100 from account A to account B. Now transaction in a database, it will perform set of steps. It will read

[1:59:57] account A's balance then subtract $100 from A. So let's say A has $500. It will first subtract it, right? So this becomes 400. And then it will add let's say this this account has 1 1200. So after transaction

[2:00:13] account has 1 1200. So after transaction it becomes 1,300. Right? Now let's say your computer crashes after step two. At this step computer crashes. So what will happen now? If you are doing this step by step this will have 400 because it

[2:00:27] by step this will have 400 because it subtracted but it did not add $100 here. So where did $100 go in the air? It is inconsistent. Right? So either all these steps should succeed or they all should fail. So that is called atomicity.

[2:00:44] Atomicity means else all steps in a transaction should success together or they fail together. There shouldn't be partial completion. So if you work with transaction concept right? So you begin a

[2:00:59] transaction you update account A then you update account B and then you commit. So the entire block will get executed. So if something fails in between it will roll back and it will undo all the partial steps. So this is

[2:01:15] called atomicity. Then there is a consistency. So let's say your account A consistency. So let's say your account A has a balance of let's say $50. Now when you subtract $100 it will become negative. Unless you have overdraft rule

[2:01:31] this is considered to be an invalid situation. your account should not have situation. your account should not have a negative balance and if you use again MySQL you know that you can enforce this using constraint. So databases have this

[2:01:45] concept called constraint where you can say that positive balance see this positive balance right this check it should be balance should be greater than should be balance should be greater than zero. So this balance column it should

[2:01:59] be greater than zero. So consistency means transactions move the database means transactions move the database from one valid state to another valid state maintaining the defined rules and constraint. Okay. So that is called

[2:02:13] consistency. Then comes isolation. Let's say this account is a joint account between me and my wife. We have only $500, you know, we are very poor people. And let's say she transfers, you know. So let's say my wife transfers

[2:02:29] $400 from this account and at the same time let's say I'm in a different time let's say I'm in a different location and I'm also right me I am also transferring $400 to another account will this work ideally it should not

[2:02:43] will this work ideally it should not work right so if your database is following the principle of isolation then this will not work because when my wife initiates this transaction right and Let's say if she commits it, if she

[2:02:58] commits it, then my transaction will fail. Okay? It's like you will have a lock. So both the transactions will not go successful. Okay? So that is called isolation. So concurrent transactions don't interfere with each other. Each

[2:03:12] transaction feels like it's running alone. Okay? Then comes durability, committed, it stays committed even if the system crashes immediately after. So let's say you committed the transaction

[2:03:26] and after it even if the system cashes it will stay committed. So it's called durability. So when you transfer $100 between accounts in acid compliant database. Okay. So this is called acid principle in databases and the databases

[2:03:42] who adle is called acid compliant database. So atomicity ensures both the debit and credit happen or neither does. Consistency ensures that account balances stay valid and rules aren't

[2:03:56] broken. Okay. And isolation ensures other simultaneous transactions don't interfere and durability ensures your transfer is permanent once confirmed. on acid. I hope you got a good understanding of this topic. Now I will

[2:04:11] take you to the movie back to the future and discuss this topic of time travel. Time travel is an essential topic in data engineering. Let's say Shivaji Investment is an investment firm. Now a regulatory bodies such as SEBI in India

[2:04:29] in US it is SEC, FINRA, there are various government operated regulatory bodies that will monitor you know your activity. So let's say CIA investment is

[2:04:41] an investment firm. SEBI can come and say hey can you show me account balance say hey can you show me account balance of some customer as of Jan 2022 or whether certain trading restrictions were applied during a historical period.

[2:04:53] So they can do audit of the past. Okay they can say okay this customer how much money did they have as of this date or what kind of data they saw. So it is as

[2:05:05] if they want you to do time travel in the past and they want you to tell them what kind of screen did they see as of 2 years back or what kind of status they

[2:05:18] had you know so it's not just checking a balance as if you're going back in time and at that time what was customers experience so there are a lot of rules for example in Europe there is GDPR there are a lot of rules and regulations

[2:05:33] across the world where they have this requirement of time travel. Basically, they want you to maintain version history as if you are doing git commit. You know, if you use git, you know how you commit. So, they want you to have

[2:05:48] that kind of versioning for your database. Now, we can do versioning of a database in couple of ways. The first one is copy on write. So, let's say you one is copy on write. So, let's say you have this record as of October 6, 2010.

[2:06:01] The record looks something like this. Now if you're doing copy on write when someone updates address let's say this person moved to Paris right from Delhi what will happen is it will not update this particular record it will insert a

[2:06:16] new record along with that new information okay let's say then balance got updated so now you are inserting new records you are not overwriting that field in that particular record this is called copy on write I understand due to

[2:06:32] this your data storage will increase. But nowadays data storage is actually very cheap and there are solutions in the world of databases which will the world of databases which will provide you this kind of version history

[2:06:46] at a very low cost. Then the second way of maintaining version history is transaction logs. So instead of inserting a new record, you will only record what got updated. So your log as of October 29 7 a.m. will be okay my

[2:07:03] address updated to this. Okay then okay my balance updated to this. Now if someone wants a record right someone wants this particular record as of this what you can do is you can replay all this. So you take this base record and

[2:07:18] you replay all these transactions and then that way you can get this entire record. Okay. As I said this is similar to git where in g you see this version

[2:07:30] history whenever you're writing code your code file will have version history. So it is a very similar concept. There are a lot of benefits folks. First one is audit and compliance. It is absolutely needed.

[2:07:42] When I was at Bloomberg we had our own data store and we used to do the same thing because Bloomberg is a finance company and they had this kind of audit error recovery. Let's say you update some transaction and let's say you

[2:07:57] figure out that that there is an error and I want to roll back to a state 4 days ago. You can do that just like git. You know how you do get revert if your

[2:08:09] code changes have a problem. You will do revert to a previous commit or a commit 10 commit before whatever. So that kind of error recovery you can get in the case of databases as well and you can do historical analysis. Now in spark there

[2:08:26] is this delta format and when you're using uh delta lake or delta format for a table then you can do this kind of versioning. So all the tables that we have seen right the manage tables like u movies etc those are in data format. So

[2:08:43] see if I am on movies and if I see data source it is saying it is delta. So we are going to talk about delta uh in a bit but due to delta format in spark you

[2:08:55] can get this kind of version. So okay select star from table version as of fi or you can query using time stamp select star from table time stamp as of this. So this way you can get just like git you can get record in the history. Okay

[2:09:10] you can get record in the history. Okay or restore table to version as of 10. In the next two sections which is AWS account setup and data leg and data format you will learn about external tables using S3. Now in our end to end

[2:09:24] project we are not going to use any external table. So it is completely okay if you skip next two sections but if you want to strengthen your knowledge on this concept then please go ahead. Folks, we will be using AWS in the

[2:09:38] upcoming chapters. So let's first set up the account. You can Google create AWS account and click on this link here. Click on create a free account link. If

[2:09:50] you already have an account then you can skip this video. You can log into your skip this video. You can log into your account but if you don't then sign up for AWS and here provide your email address. Okay. And then you can provide

[2:10:04] some AWS account name. I'm going to say code basics double whatever account name doesn't matter that much but here you can provide your Gmail id once you do that it will send an email to your email id to verify so I have done the

[2:10:20] id to verify so I have done the verification and now I can uh provide my password so just provide a password and proceed further okay at some point uh it will ask you for selecting your plan so I'm

[2:10:35] going to say okay free 6 month plan okay so let's select that and uh you can so let's select that and uh you can provide all the details here

[2:10:53] me for my credit card it is not going to charge actually they just do this credit card verification so you will get some free credits okay so you can provide your credit card details etc and Then it will um ask you for this SMS just for

[2:11:09] another verification. So here it send a code on my phone. So I'm just confirming my identity. See it will require multiple step of verification. Okay. So multiple step of verification. Okay. So now see my AWS account is set up. At any

[2:11:24] step if you're feeling stuck folks use common sense. You have to use common sense. Take help of chat GPT. Setting up science. You should be able to figure it out folks. Okay? So, please do that. And

[2:11:37] out folks. Okay? So, please do that. And now I can go to AWS console where I'll be able to kind of see different offerings and different options that AWS has. Folks, in this lecture, we will learn

[2:11:51] another important topic called Delta Lake or Delta format. For this, I need to set up another S3 bucket. So, let me show you that first and then I will explain step by step. So here I'm in Amazon S3. You can use any S3 account

[2:12:06] Amazon S3. You can use any S3 account and just create a bucket and the bucket name has to be globally unique. Okay. So I'm going to just say CB company stocks

[2:12:18] I'm going to just say CB company stocks 001. Okay. Now I'm going to use a sample company stocks file which I'm going to share with you so that you can use the

[2:12:30] same file. Okay. So see these are the columns I have and I have some simple uh stocks data. So let me upload that data. Okay. So here this is the bucket name

[2:12:43] Okay. So here this is the bucket name and then everything else is default and you will create this particular bucket. Okay. So now let me

[2:12:55] be able to use this name. So you can say CB company stocks 0023. whatever or just give your name initial. Okay, make it unique. So here I'm going to upload a

[2:13:08] file. So drag and drop. I'm going to give you this CSV file. Okay, so just drag and drop that particular file and just say upload. So my S3 bucket is

[2:13:21] set up. Now the next step is to set up an external location. So go to catalog an external location. So go to catalog external data create external location AWS quick start and here type in this bucket right CB company stocks whatever

[2:13:37] bucket right CB company stocks whatever so I will say S3 this is the bucket generate a new token copy launch in quick start this is just doing the connection okay so here

[2:13:52] so here uh let's see yes this is the token So I'm copy pasting the token. Everything else else is same. I acknowledge I never say no to this kind of thing. Okay. And now

[2:14:07] I think it has established that connection. So let's go back to will check back for your new location. Okay. So just say external location. It

[2:14:20] took some time but eventually you will see create complete here. And then when you go to external data you will see this particular uh location right S3 CB company stocks. Now you can go to your SQL editor. So

[2:14:36] click on that and you can run this command select start from CSV dot this header equal to true and you'll be able to see the entire table. Okay. Now let's

[2:14:48] to see the entire table. Okay. Now let's create something called a delta table. create something called a delta table. Okay. So this delta format is actually a Okay. So this delta format is actually a storage layer on top of your data lake.

[2:15:01] So let's say my data lake is in S3. Okay. So in S3 this bucket you know I I have one CSV file but let's say you have a bunch of CSV files or park files and this is my data lake. Let's assume. So what delta format will give you is it

[2:15:18] will give you storage layer on top of it because see here the data is scattered right there is no versioning you cannot do time travel there is no acid properties someone can override this file right this this is like a folder on

[2:15:33] the cloud anything can happen here so this delta format or delta lake provides this delta format or delta lake provides asset properties it provides this kind of time travel version control version history and so on. So I'm going to

[2:15:47] create a delta table. So see create table this using delta and this is the format. So now in location what I'm saying is here u this is my bucket and

[2:16:01] then what it will do is see I'm having this bronze or let's say I I can just call it delta. Okay. So this is a delta folder as this. So now see when I do select star it will select all the columns. On

[2:16:17] top of that I'm having these three additional columns. So basically I want to use the same schema as what I whatever I have in this uh CSV file. On

[2:16:29] top of that I want to add these three columns. So first one is ingested at so when did I create this table input file name and the UU ID. Okay UU ID as bronze name and the UU ID. Okay UU ID as bronze ID. So usually when you ingest the data

[2:16:43] into Delta Lake under Medeline architecture you will have bronze layer then you have silver and then you have gold. Okay. So you can call this bronze also. Okay. So let's say I'll just call it bronze.

[2:16:57] when you do so let me run this query separately. So when you do this separately. So when you do this new query

[2:17:10] able to run it. But I hope you're getting the point. And then when you say using delta see it will go to this particular location, it will

[2:17:22] get all these records and whatever records we have in this file and it will create that table. So let me run it so that you get an idea. So I'm just simply running this particular command. Okay. So, it ran that command and now I'll go

[2:17:37] to catalog and workspace default tables company stock. See, I get this table company stocks. So, sample data. Hooray. I got

[2:17:50] all this data and then if you look at details, it is actually an external table. Okay, this is external table and I have this

[2:18:02] particular history. Okay. So this is an external table and I have all this history. So I'm going to now modify some records. Okay. So in the version history

[2:18:14] you will see more records. So let me just modify some record here. So here just modify some record here. So here and I will say update company stock. and I will say update company stock. Okay.

[2:18:33] want to set let's say stock price stock price. So set price. So set stock price is equal to let's say 70. stock price is equal to let's say 70. Okay. So this is okay. So this 6267

[2:18:48] Okay. So this is okay. So this 6267 I want to set it as a 70 and where company is equal to

[2:19:00] what is company Apple right so company is Apple so let's run this command and now see it will update the data table it will not update the actual CSV

[2:19:13] table it will not update the actual CSV file okay so if If you go here and okay let me just do sample data. So now when I run this query select star from workspace whatever where company is equal to apple. You see the price I'm

[2:19:27] equal to apple. You see the price I'm getting is now 70. But when you look at the history of this particular thing you see now you're getting a history. So as of this date now you are having this record. Okay. Now same thing if you want

[2:19:41] record. Okay. Now same thing if you want to get the version as of this right so if you want to get zero version where Apple stock price was I think 67 uh Apple stock price was I think 67 uh something then you can say version as of

[2:19:56] zero I think you have to move version as of zero before the wear clause so let me run it so now when I do it see 6267 so now when I do it see 6267 and if you do version one and run it,

[2:20:12] you get 70. So that's the beauty of this you get 70. So that's the beauty of this delta format that you can um have a version history, you can have a time travel, you also have all the asset

[2:20:24] properties that we discussed. So see when I updated that the company stocks CSV file is not changed but it created this bronze. You see this bronze and in

[2:20:36] this bronze. You see this bronze and in bronze I have this delta log. So I have the data stored as park. You see this park and you have like different versions and you have this delta log. To define it formally data lake is an

[2:20:50] open-source storage layer that brings asset transactions time travel schema enforcements to your data lake. So basically Delta Lake solves issues with

[2:21:03] data lake. you have your raw files in your data lake right so let's say this is your data lake so I have my data lake which will have my raw files right let's which will have my raw files right let's say my CSV files park files

[2:21:17] whatever and the issues that I have here is first and the issues that I have here is first of all no audit trail right no audit trail because I don't have versioning then concurrent rewrites right so that

[2:21:33] can cause data corruption. If I am on a S3 bucket, someone can write my CSV file easily. There is no schema enforcement, right? No schema enforcement

[2:21:45] enforcement. So these are all the issues with data lake. So what people do is with data lake. So what people do is they use delta lake which is just a layer on top of your data lake. See your data lake you're not replacing your data

[2:21:59] data lake you're not replacing your data lake will remain but you hook your data bricks like how we did it right you hook your data bricks with this and from CSV file you saw from CSV file which was customers we created a delta table so

[2:22:16] from this CSV file let's say you create delta table from park file whatever you create this delta tables and it will solve all these issues because delta tables have version history so it solves this problem. They are acid compliant so

[2:22:32] it solves data corruption. They are just like relational database so they will solve the issues with schema related issues as well. Okay. So they will prevent the uh data corruption. Okay. So it's a open-source

[2:22:47] storage layer that brings all these benefits and it is built on top of park files and adds a transaction log to track all the changes. In short, delta blake is equal to park files transaction logs plus metadata. And what problem

[2:23:04] does it solve? All these three. And we already saw the transaction log is stored in something like this where you have your actual park files, right? So your data is stored in form of park files and you have this transaction

[2:23:19] logs right we initially discussed about transaction logs. So in the log just like git log you will say okay this field updated that field updated and this is what we saw right like when this bronze was created see this is a delta

[2:23:33] log. So these are actual park files. See this is park this is park and when you this is park this is park and when you go to these logs you will see the actual log file. So the typical architecture that people use is let's say they have

[2:23:50] their data lake data lake which has CSV files park a CSV CSV park relational table and so on. And

[2:24:04] in data bricks what they do is they set up a catalog and they have this raw location. So raw will be an external location. So it will be pointing to that. Then from raw they

[2:24:21] be pointing to that. Then from raw they create this bronze layer. Okay. And this bronze is in delta format. So it gets all this benefit. Then they have silver. This is also delta. Okay. So here they do transformations, data cleaning and so

[2:24:35] on and they get BI ready columns aggregation and new columns in gold layer. This is also delta. Okay. So this is the uh typical architecture that people follow and in our spark project we are going to use the same

[2:24:50] architecture. Okay. So raw is just pointing to this location and then you have bronze, silver, gold which are in data format. Okay. So this is essentially your data lake and it is a core component of

[2:25:05] your data lakehouse. Remember lakehouse is a combination of data lake and data is a combination of data lake and data warehouse and delta lake or delta format is a core component of this lakehouse architecture.

[2:25:19] We will now begin the work on the project. Please check the video description. We have provided all the useful resources along with the code. We have also included a summary document. So please do a quick recap before we

[2:25:31] dive into the project. We will start by discussing the technical architecture of the project. Here is a diagram of a legacy architecture. legacy architecture. We have an OLTP system here where uh

[2:25:45] there is a company's application server and you have a relational database that stores all your transactional data. Okay. Now just in case if you don't know Okay. Now just in case if you don't know OLTP, OLAP, ETL, etc. then you can refer

[2:25:57] to this video. I have explained those concepts uh by going over data warehouse, data lake and data lakehouse. And the second component in the legacy system is once you have all your transactional data in your OLTP system,

[2:26:11] you will do a data pull using some Python script and store that into S3 which is your data lake essentially. Okay. So here S3 is an object store Okay. So here S3 is an object store which is your data lake and then you are

[2:26:26] using variety of AWS services like Lambda, Glue, EC2 for doing your ETL, Lambda, Glue, EC2 for doing your ETL, compute and data processing and then you store your data in your data warehouse. So here you're using Amazon Redshift as

[2:26:41] your data warehouse and you are then uh plugging your BI tool either PowerBI, Tableau, whatever tool you're using to red shift for doing your BI reports. Now

[2:26:53] while this technical architecture is functional, it has couple of issues. The first issue is you have fragmented ETL and maintenance overhead. You are using and maintenance overhead. You are using so many different uh AWS services. your

[2:27:06] ETL logic is scattered throughout you know all these different pieces and you know all these different pieces and you have uh this uh AWS billing system right you don't have much transparency on how you are being build the logic is

[2:27:21] fragmented and you have to do a lot of maintenance right setting up things in AWS might not be easy especially if you're a beginner uh there is a learning curve so that is the first disadvantage the second issue is limited scalability

[2:27:37] and cost inefficiency. The cost of AWS can easily go high with this kind of architecture and we are not using any native uh spark cloud platform.

[2:27:49] Therefore there is limited scalability and there is l of unified platform as well. Now datab bricks solves all this problem. So we are going to have this new architecture where we will do data pool using Python store it in S3 and

[2:28:03] then after everything will happen in data bricks. So data bricks here is a single unified platform where you will do your uh data analytics data engineering even if you want to do AI you can do all of it in this single

[2:28:18] platform and even the BI report and the analytics that we are going to generate is also part of the same datab bricks platform. Okay. So, datab bricks will solve all those three issues and we will work with this new architecture.

[2:28:34] One thing I want to point out about new architecture is that this is based on data lakehouse architecture. So, previously you had separate data warehouse, data lake etc. Here although we are using S3 to store our uh objects

[2:28:49] we are using S3 along with data bricks and then we are doing everything like data analytics, data engineering in the future if you want to do AI everything on a single platform also we are using the data format of data bricks okay

[2:29:04] therefore this is a data lakehouse architecture all right now I'm in my datab bricks login and we have looked at datab bricks platform and learned the foundations in the foundation sections and here in the project we are going to

[2:29:19] and here in the project we are going to begin by creating the catalog. Okay. So let's go to catalog here and by default you see workspace and system and you can you see workspace and system and you can click plus and create a catalog or you

[2:29:32] can do it by code. So we going to do it by code. So let's go to workspace and create a new folder here. If you want to attach it to git, you can create a git folder and and you know link it to your git repository. So create a folder and

[2:29:47] new folder name will be let's say project e-commerce. You can give it whatever project name you want to give and this will have another folder called setup. So all our setup code we will store here. And let's create a new

[2:30:04] notebook where we will attach it to a serverless compute. Okay. Okay, so while this is attaching, let's write our code. We all know that if you want to run SQL query, you will write percentage SQL and then you can run this command create

[2:30:20] then you can run this command create catalog if not exist e-commerce. Okay, control enter and this will create that catalog. So while this is running I that catalog. So while this is running I will open this catalog in the new tab

[2:30:35] and then whenever it comes up I will uh show you. [snorts] So if you go here see now we have this new catalog. Now from the foundation section you know the difference between catalog schema and tables. So it's just three level

[2:30:49] hierarchy folks. Okay. So here you have catalog then you will have schema and then you will have tables. Okay. So let's now do another thing which is let's now do another thing which is percentage SQL and use catalog

[2:31:04] percentage SQL and use catalog e-commerce. So whatever uh code you run e-commerce. So whatever uh code you run after this line that code will use this e-commerce catalog as a default. So we are going to now create three schemas.

[2:31:19] We are using Medelian architecture folks. So we will create bronze schema, silver and gold schema. Okay, let's hit control enter and this says okay. If you refresh it here and you will see bronze, gold and silver

[2:31:35] schemas. You can run this command show databases from e-commerce and whatever structure you are seeing here, you will see the same structure here as well. See bronze, gold and silver. while running the code let's say due to whatever

[2:31:49] want to drop the catalog then you can just say drop catalog this cascade and underneath e-commerce as well all right so our catalog setup is done

[2:32:02] after the catalog setup let's work on uploading the data if you're working for any company the way it will work is it will pull the data from OLTP system via Python into S3 now since this is a learning project we are not going to

[2:32:16] cover this OLTP part right we are mainly interested in data engineering so we will have bunch of CSV files which we will directly ingest into datab bricks okay so we will start from this point and I'm going to provide you all those

[2:32:31] CSV files in this folder so once you look at video description and download that information you will see this folder which will have different uh subfolders so here my fact table is order items and remaining tables are my

[2:32:47] dimension tables. So if you look at brands, brands has this CSV file which looks something like this. So you have brand code, brand name and category code. Okay. So we will identify these brand codes with this unique category

[2:33:01] code CE app etc. Then you have categories. For example, for electronics you will use this code CE. For home and kitchen you will use HNK. Then you have products. So remember HNK. HNK is home and kitchen. So that's how you uh refer

[2:33:15] to this code in your products table. Okay. So you have product ID, SKU, color, size, material. You can look into this data. It also has this uh brand code. Okay. Uh so category and brand will point to those two other tables

[2:33:31] which we just looked at. Then you have customers table. Pretty straightforward. Customer ID, phone, country, state, etc. And date dimension table has date, year, um quarter, etc. Let's say in our BI dashboard if you want to do analytics

[2:33:46] based on quarter okay what was my revenue in Q3 Q4 etc then having this kind of dimension table will be helpful and finally we have our fact table which is orders okay so it will have date order time stamp customer ID how much

[2:34:02] quantity you order right the price because this company has stores in multiple countries so you know great Britain's INR Australian dollar and so on unit price, discount amount, the coupon code and so on. Now if you check

[2:34:17] order items table, it will have this landing subfolder which has files specific to a date. What we saw is a file for just one day. But for every day you will get a separate CSV file and we have three months data. Okay, for the

[2:34:30] month of 8, 9 and 10, C 10:31. All right. Now all of this data we will now ingest into datab bricks. So if you go to cataloges here and if you click on

[2:34:42] your e-commerce catalog and click on create schema. Now schema is something we can create here through code as well but we are going to just I I will show you the manual way of doing it. We will call this source data. Now here you can

[2:34:58] call this source data. Now here you can use external location. So let's say if you have data stored in some outside uh place you can point it to that that place but we're going to use a manage location here. So just say create schema

[2:35:12] click on create and now you see source data here. So just click on this create volume and this volume we will call it raw. Okay. And this is going to be a

[2:35:24] manage volume which means the data files are stored in location managed by unity catalog. Okay. So let's hit create button and then click on upload to this button and then click on upload to this volume. So now I will go to my folder

[2:35:38] and I will click on all the subfolders. Okay. So and drag and drop them here. So it is creating this volume. See volume/ ecommerce/source data/ row. Hit upload button. All right. So see my source data is ready. I have raw. Under raw I have

[2:35:54] is ready. I have raw. Under raw I have all these subfolders. And when you go to files as well. Now you may be wondering why do we need raw? Can't we directly why do we need raw? Can't we directly store it in bronze this raw volume is

[2:36:07] kind of our dump ground. We just dump data as is without any changes. Whereas in bronze we might make minor update. It is still raw data but see it is in delta acid transactions, time travel properties etc. Therefore, it is a

[2:36:23] common practice to have raw as a dumb ground and bronze as a place where you have structured raw data from raw. Now, we will transfer data to bronze and we will begin with the dimension tables. So, let's go to

[2:36:38] workspace here and under project e-commerce let's create a folder we will e-commerce let's create a folder we will call it medallion processing dim. Under call it medallion processing dim. Under that, let's create a new notebook. I

[2:36:52] that, let's create a new notebook. I will name it one dim bronze. In this notebook, what we will do is whatever dimension data we have, we will

[2:37:04] create a table in bronze layer and we will uh ingest that data. So let's will uh ingest that data. So let's import some necessary Python functions first. Then the catalog name is e-commerce and we will define the schema

[2:37:17] for our uh brand dimension table. We already saw brand table has brand code, name and category code. These three see they all are strings. So they are all string type. Let's execute this now. See you don't see any table under bronze. So

[2:37:32] our goal is to create those tables. And also in source we have data at this particular path and we'll create a variable called raw data path using that

[2:37:44] path and in that whatever CSV you have you will use it for branch. Then create a data frame using this and the way you do it is you specify raw data path uh in

[2:37:56] this parameter. The schema is that schema right uh it is comma separated file. So that's why you're specifying this parameter and header equal to true which means that this particular file has this header. Okay. So let's uh

[2:38:10] create a data frame and in that data frame we will add the metadata columns. Right. So metadata columns are the source file ingested it. So whenever there's a problem you want to do any audit these columns will be useful. So

[2:38:25] let's create this data frame and see how it looks. All right our data frame looks good. See these are the three main columns. These are the two metadata columns. So the idea here is you load data from CSV into the spark data frame

[2:38:39] and then from spark data frame you will write to a table in datab bricks. Okay. And that's a delta table. So you will say df do.right format is delta and you there is an existing table you want to override it merge schema is true. So

[2:38:55] let's say tomorrow if you get two new columns then it will merge those those columns. Okay this is for schema evolution and you will call your table brz brand. So let's execute this. This is

[2:39:08] brand. So let's execute this. This is done. And now let me go here refresh and under bronze now I see BRZ branch. And if you look at the sample data you have to select a compute of course. So start and close. See my table is created

[2:39:23] successfully. Now you'll repeat the same process for other dimension tables. So I'm not going to write that code because it will take too long. But is it is essentially the same logic. Okay. You go to your category dimension table, your

[2:39:37] products and so on. See for category you define the schema exactly same process your raw data path see category star dot csv and then you do df do write to brz csv and then you do df do write to brz category you will do that same to brz

[2:39:52] category you will do that same to brz products uh customers then date now in date you might be like okay why date is a string type well we want to store the data in a same format as a string and when we do silver processing at that

[2:40:05] time we will create the date uh data type. Let's go ahead and create a new type. Let's go ahead and create a new notebook for the silver processing and I will call it dim silver. Here as usual I will import some

[2:40:21] silver. Here as usual I will import some useful functions and then we will create useful functions and then we will create a data frame called df bronze with spark dot table and let's use the python format string

[2:40:34] here to say catalog name dot bronze is the schema and then brz dot branch is the table name and then df

[2:40:49] [snorts] bronze dot show. So see this is my bronze table. Now immediately I noticed some issues. For example, there is an extra space after brand name. So why don't we remove the leading and trailing spaces

[2:41:04] remove the leading and trailing spaces from the brand name. So here you will say DF bronze and the function that you use is with column. Okay, brand name.

[2:41:16] use is with column. Okay, brand name. Brand name is the name of the column and Brand name is the name of the column and you will use f dot trim. Okay. So you are going to trim uh this from the brand name function. So

[2:41:30] f dot column brand name and let's assign that to a new data frame called silver because silver is the clean data frame. because silver is the clean data frame. Okay. So show 10. And now see you have

[2:41:45] space here in after Noah wave but here that space is gone. The other issue I observe is these brand codes have this extra character. Now when you when I talked to my business manager he said that the brand code should have only

[2:42:00] alpha numeric characters. Okay. And if you want to detect only those characters you can use regular expression. So if you go to regular expression101.com website you can test your regular expression. So here this expression says

[2:42:15] that include the characters which are between capital A to Z, small A to Z or 0 to 9. But if there is any other character so this is a negation. Okay, this sign is a negation sign and that will say that detect those characters

[2:42:28] and when you detect those you can replace them with space. So you can do all of that with this code. You will say in brand code if you have any character which is not alpha numeric replace. Let's see here user using rag x replace

[2:42:43] function replace those characters with space. So it will replace it with this particular space. Okay. So let's execute this. And now you notice that vault right there was this this character. So that particular character is gone. Now

[2:42:58] it's a common practice that you will try to identify all the unique category codes, brand codes and so on. So let's do that. So I will run this uh distinct function on the category code and when I do that I find that there are some

[2:43:14] discrepancies. See books and BKS then you have toy and toys grocery and grcy. So once again you can talk to domain experts your business managers and they can say that okay these are same. So let's replace books with BKS code

[2:43:29] grocery with this code and so on. All right. So these are the anomalies that you found and you would like to replace these with the actual code. The way you these with the actual code. The way you do that is is you will say df silver

[2:43:42] dotreplace. So there is a replace function replace that you can use and function replace that you can use and here anomalies and then there is a here anomalies and then there is a subset right. So subset is category code

[2:43:55] and you can say uh show now instead of seeing the whole data frame I will just say this. Okay. And you can assign this back to DF silver.

[2:44:07] So when you run this, okay, I think I should not have this show here. That's why I was getting that error. All right. Now you see you don't see this books, this grocery and so on. You have just the valid unique codes. Once that is

[2:44:22] done, you will write to a table. So it'll write to us SLV branch table. it'll write to us SLV branch table. Okay. And when you go to your uh silver schema and refresh it, you will see that particular table. Now I

[2:44:36] have done data cleaning for other tables too. I'm not going to write each and every code in front of you because this is not EDA tutorial. This is datab bricks tutorial. So let me just quickly go over it. I'm going to provide you all

[2:44:48] this notebook. So you'll have all the code. Okay. So now for the category table, we will do some other type of data cleaning. So first we will find out the duplicates based on the category code. For example, app category code has

[2:45:02] two count, grocery has two count. You see app has this record and this grocery has this and this and these are clearly duplicates. Okay. So you can just simply drop them by uh calling this drop duplicates. So whenever you have spark

[2:45:19] data frame and you say drop duplicates and provide a column so on that column it will whatever is the duplicate it will just drop it and then when you display it see those duplicates are gone here. The other thing you will do is you

[2:45:33] will convert category code into uppercase. So right now it's small case uppercase. So right now it's small case here when you do f dot upper function see it has converted this entire column into uppercase. All right. What's next

[2:45:47] into uppercase. All right. What's next is writing that table to SLV category. So you'll do that. Uh make sure you are running all this code on your computer. Then for products table you will find out the raw and column count. Okay. So

[2:46:02] out the raw and column count. Okay. So you have 50,000 products folks with 14 columns. Okay. So we got that into this DF bronze data frame. This is how the product table looks with the metadata columns. Okay. And when you look at the

[2:46:19] weight in grams, see you have all this right G. So you can remove this G character and convert this into a double uh column. So here uh see all this G's you have you will just say G replace that with string and then wait you can

[2:46:37] use integer type. Okay. So fine integer double. So you cast it to the numeric type and then when you look at it see that G is gone and it is looking good. Same thing you will do for length column. So for length you have this

[2:46:52] comma. So you will replace that comma with dot by just saying okay replace comma with dot rag x replace and then casting it to a float type. Okay so this

[2:47:04] is your length column. Now then category and brand code are in lower case. So we need to make it upper. Okay. So in the products table basically they are lower case. So you will make it upper by calling this. So you can do cascading

[2:47:18] with column with column. You can call it in a cascading fashion. And then for material columns you have some distinct categories. Okay. Cotton steel wood

[2:47:30] all of this. And there are spelling mistakes like cotton. See the spelling is wrong. So it should be cotton double T. Aluminum should be this. rubber should be this. So you will just uh use this f.van function. Folks, I know I'm

[2:47:44] going in fast but if I do each and every transformation, this course is going to be like 10 hours. Uh for EDA, I have some other videos too. So you can do so many things when it comes to data cleaning. Okay. So I'm just showing you

[2:47:57] the code once again. Check video description. All the code is provided. Then for rating count, you see it's negative. rating count should be a negative. rating count should be a positive number. So you will make it

[2:48:11] positive by calling this fabs function. Okay. So on this column make it absolute otherwise fit is zero. If it is null then rating count is zero. All right.

[2:48:24] Then check the final clean data. So your products table looks pretty good now. products table looks pretty good now. And then write that to SLV products. So by calling this method we are creating a new table. I mean the the table schema

[2:48:38] is created but we are filling the data okay for this table uh into your silver schema. The table is in bronze. Now we are creating table in a silver schema transformation for your customer. For example customer ID column is null and

[2:48:55] you have some 300 null records. So what you will do is you'll just uh filter it Okay, let let's display and see what's going on. Okay, so these are the columns having customer ID as null and then you will drop them. Now, how do you handle

[2:49:12] null depends on situation your business logic. If you have too many records with null values, maybe you want to replace a null values with some valid values. But null values with some valid values. But here 300 and again while discussing with

[2:49:25] business manager, they're like okay it's okay to drop that data. So you dropped it. Same way number of nulls in phone. Okay, this many uh and these are the numbers for that you don't want to drop it because not having phone number is

[2:49:41] okay. Some customers do not provide phone number. So there instead of having null you will say not available. Okay. So you will say in phone it is not available and then you write that to silver customers table. And the last one

[2:49:57] silver customers table. And the last one is our calendar table. Now in calendar table if you look at the schema see date is a string you want to convert it to a proper date type. So you will first format it in ddmm y format. Okay. And

[2:50:12] you convert that to date by calling this two date function. Now when you check the data type see date has a date proper date data type. You'll also remove

[2:50:24] duplicates. Okay. So see for certain dates you have higher than one count. So in our date table there should be only one record for a given date but for 29th date there are two records. So you will use our

[2:50:40] usual drop duplicates function. Okay. And then you will normalize the day names. So if you look at our day names see there is no consistency. One is small case one is entire upper case. So you want to normalize in a way that the

[2:50:56] first letter later should be capitalized. See capitalize first letter in day name. So see now it looks consistent and then convert negative week of the year to positive. So some of the I think see all these week of the

[2:51:10] year are negative they should be positive. So once again you call f.tabs function to convert them to positive and then uh enhance quarter and week of the year column. So for week of the year you are saying week 31 2025. See here is

[2:51:26] just 31. Right? And also quarter is just three. So instead of three you will say three. So instead of three you will say Q3 2025. Q3. So this is useful. So you're using all these functions. Please take a look to make those enhancements.

[2:51:40] And then week of the year you will rename it to week. Okay. And then you will write that to this silver schema. So now when you go and refresh your tables and go to silver see you will see all these wonderful tables and you can

[2:51:57] just do some spot check by looking at the sample data. So this is how it looks. Let's check our calendar table. Go to sample data and see here you see

[2:52:09] clean enhanced data. See quarter is Q3 2025. Week is this. uh day names are normalized and then you have some duplicates which are removed as well. Let's now work on gold layer. I have created this three_d gold notebook and

[2:52:26] we will be creating business ready uh columns in our uh tables. So if you look at our products table, it has category code, brand code etc. But when we plot

[2:52:38] our BI dashboard, we will have descriptive name. So instead of H and K we will have home and kitchen and so on. So what if in gold layer we have just one table products table and we can get rid of this category and brand table and

[2:52:54] we can put that descriptive name here. So just imagine you have gold products table where you have category code and for HNK you have category name which is home and kitchen. Same way for brand code you have brand code and brand name

[2:53:08] right? So that way we can get rid of this brands and category tables in our this brands and category tables in our gold layer. Okay. So here we are just loading all these three tables into spark data frame. And then

[2:53:24] if you want to pull the category code from the category table, right? So H and K, if you look at the silver category and go to sample data, HNK is home and kitchen. So you have to do a join using this H and K. Okay. And for doing join

[2:53:41] views are useful. So what I have done here is I have created three temporary views. So the purpose of view is uh that your data frame will become more like a

[2:53:53] your data frame will become more like a table. Okay. And then uh on these views table. Okay. And then uh on these views you can just run all these queries. So when you say select star from v products limit five see it shows this select star

[2:54:08] limit five see it shows this select star from v category whatever limit five it from v category whatever limit five it shows this and then uh brands will show it I mean this way you'll understand views better and then we are using this

[2:54:22] e-commerce catalog now look at this query what you're doing here is you are creating a new table called gold dim products in gold layer. And the way you

[2:54:35] products in gold layer. And the way you do that is first you join brand and category table, right? How do you join brand and category table? So check this. Category has category name and category code. But if you look at brands and if

[2:54:49] you go to sample data, brand has brand name and brand code. But it also has category code. So if you join it, you get single table which has brand code, brand name, category code, category name. Okay. And that is something you

[2:55:02] can do with this query. So this is CTE and you are saying that give me brands and category table which has those four columns. Which four columns? Brand name, brand code, category name, category code. Once you get that you feel like

[2:55:16] you have got this one table with those four columns. Now you run this another four columns. Now you run this another query. Okay. where see this brand category is BC and you will do a join between

[2:55:31] that and products table using brand code so it's a simple left join between product table and this new four column table that we created you're doing a join and we are selecting bunch of columns okay so let's uh execute this

[2:55:45] and in goal now you have GLD dim products table and see this is what we got now so along Along with category code you have category name, brand code you have brand name. Then for customers table it will be useful to have region.

[2:56:01] table it will be useful to have region. Okay. So see our customer table in our uh silver schema if you look at it let me just show you here it has the state. So let's say for country India it has st MH is Maharashtra then TN is Tamil Nadu.

[2:56:17] But in BI dashboard our business managers wants region level analytics. They are like okay what were my revenues in my west region east regions and we don't have that in customers table. So how about we map all these states with

[2:56:32] regions. So Maharashtra, Gujarat will be west region, Assam will be east region. Okay. So you can get this kind of mapping from business. So see in south you have Karnataka, Tamil Nadu, AP, Kerala these are all souths. Then

[2:56:48] you have west, you have north and so on. So you can get this kind of mapping from domain expert from your business managers for not just India country but all other country right Australia, UK. So it's a simple dictionary. Okay. So

[2:57:03] our country state map dictionary is a nested dictionary and if you execute this you will get this kind of dictionary. See for India you have state and the region. For Australia you have the state and the region and so on. And

[2:57:18] then you can create a flat list basically. So flat list is like bunch of rows. Every row has country, state, [snorts] region, these three columns. Okay. And then you can create a data frame. And

[2:57:32] this DF region mapping data frame is created based on those uh flat rows. See it has country, state and region. And then you can do a join. Okay. So now we are going to create a silver table first and silver customer table. Say this

[2:57:48] doesn't have a region. But now when you do a join with region mapping table on do a join with region mapping table on country and state and for any uh state if you don't have a region you will say other region. So let's execute this. And

[2:58:02] other region. So let's execute this. And now see here you are getting India uh Maharashtra is a state and then customer ID. These columns are not in a right order but let's look at the data at least let's verify the accuracy. So,

[2:58:17] India, Maharashtra, country code is iron. And see, look at this. This is amazing, right? The region is west and your customer ID, phone, country code your customer ID, phone, country code and so on. Okay, so you can write that

[2:58:31] and so on. Okay, so you can write that to your customers table. Now, see, gold has only dim products tables, but when you refresh, it is refreshing right now. You got this customers table. And if you look at the sample data, see your

[2:58:43] customer ID, phone, country code is this, state is this and then um you have this regions. Now let's look at the date table. So here we are going to download

[2:58:55] SLV calendar not download but create a data frame out of your silver calendar table and then we will create bunch of new columns for example is weekend. Okay, for this date uh let's say you want to know whether it's a weekend 1

[2:59:09] and zero in BI dashboard imagine you have a filter okay what are my revenues on weekend what are my revenues on weekdays so this column will help you do that correct so the entire purpose of gold layer is to create columns which

[2:59:23] will help you in your analytics so is weekend is one column the second one we created is date ID see date ID is nothing but you take a date remove the hyphen see if you remove to the hyphens what do you get? See this is the date ID

[2:59:38] what do you get? See this is the date ID 2025 0907 then you get month name because in BI dashboard imagine you have filter okay what are my revenues in January February you might have seasons right in summer months there might be

[2:59:50] revenue decline or revenue increase in winter months in fall season so for all those month name might be useful okay so we created these three columns date ID month name and is weekend these three columns that you see at the And and then

[3:00:07] uh you might want to reorder certain columns. So you can say desired column columns. So you can say desired column order. Then you say dot select goal. And see now you have all these uh columns. You can by the way rename that for

[3:00:21] customers table two. Okay. All right. Let's write it to gold dim. And when I go here I see dim date and sample data.

[3:00:36] And here I see this see is weekend. This is a new column. Then the month name is another column and date ID is the another column that we generated. All another column that we generated. All right. So our goal layer now has three

[3:00:50] tables and these three tables contains additional columns which will help us in our BI dashboarding. we will move on to the next step which is processing the fact table. So here I'm going to create a folder called

[3:01:06] Medeline Processing Fact and in this folder I'm going to import some uh notebooks right these three notebooks I will just drag and drop here and we'll

[3:01:19] go over these three one by one. So let's look at first the bronze notebook. Okay. So here we want to take the data from our volume. So if you look at uh the

[3:01:32] our volume. So if you look at uh the data here let's say our source data data here let's say our source data volume has raw folder then it has order volume has raw folder then it has order items landing and this is a place where

[3:01:44] we have our CSV files. So we are going to copy this and from [snorts] this volume we will take all these CSV files and create a table inside our bronze which will say bronze orders. Okay. So let's import some necessary libraries.

[3:01:59] Then catalog name is e-commerce. And here is the schema of our orders. And as string because there is some data quality issues. And to tackle that we

[3:02:12] need to have everything in string. So let's create the order schema. And then this is that same path. See we have taken that same path here and all the CSV files in that particular path and created a data frame. So this data frame

[3:02:28] is created from this raw data path you see here. Then you have delimter as comma header equal true and two metadata columns. So let's execute this and then

[3:02:40] let's display the data frame. So see my data frame looks good. It has around data frame looks good. It has around 183,000 records from all those CSV files. See, we have many CSV files, 3 months data. So the total records in 3

[3:02:54] months data is 183,000. And then we will write that to bronze order items table. So when you say dfright dot format, it will write it in data format due to this will write it in data format due to this argument. And once this is done, you can

[3:03:10] argument. And once this is done, you can go here and you can go to gold, not gold actually, but bronze and you will see this order items table. And if

[3:03:22] you look at some sample data, then here are my records. Now when you look at this particular table, so let me just pull it from CSV file because

[3:03:34] bronze and CSV is same. It's raw data. I clearly notice some data quality issues. So in the quantity column, right? So let's look at this order, right? So this is one order where the product ID is

[3:03:48] this, quantity is this, right? The currency is this. But in the quantity column, you see some uh quantities are in text, some quantities are in numbers. So you want to convert all these two to actual

[3:04:03] to convert all these two to actual number two. Then for unit price column you see this dollar symbol here. Dollar symbol dollar there is extra space. So you want to convert that to a number. The next thing you want to do is you

[3:04:16] want to remove this percentage column and get like something like for 10% you and get like something like for 10% you want to get 0.1 because eventually after want to get 0.1 because eventually after your silver processing you want to have

[3:04:29] new columns. For example, let me insert a new column here. You want to have a column such as gross amount, right? So the gross amount will be equal to quantity. So let's say this is the quantity

[3:04:44] into the unit price. I mean here is just one quantity. But for example here if you look at it right quantity into the unit price and of

[3:04:56] course this is not a percentage column. So you can convert that to a number. or go to format cells and uh you know you want to have it as

[3:05:11] regular. So you want to have gross amount which So you want to have gross amount which is multiplication of these two. Then you want to know the discount amount right? So let's say what will be your discount

[3:05:23] amount? Well discount will be this number into this. Right? So now this is in percentage. So you have to internally like you have to

[3:05:36] convert it to a number. But here is showing the right number. So on $11 gross amount you got the discount as 1.11. And then you want to have sales amount, right? So sales amount will be how much? Well, it's very simple logic

[3:05:52] folks, right? It's a gross amount minus you subtract the discount. So this is the amount that customer is paying. So you want to generate these kind of So you want to generate these kind of columns when you do your gold processing

[3:06:07] also based on the suggestion from our business manager. Uh we will convert this channel name to the full name. So web means this sales was generated through website. So maybe we'll generate convert this to a complete uh

[3:06:23] descriptive name. app means this this sales was generated through mobile app. Customer went to the mobile app and order it. Okay. So we'll do all this transformations in silver layer and then we'll generate these new columns in gold

[3:06:37] layer. So let's open the notebook for our silver layer. So here I created a data frame from the bronze table. And if you look at the schema everything is string. Okay. So now for transformations we will convert two to two. See, so we

[3:06:53] are doing for quantity when quantity is two, convert that to number two otherwise cast it to integer. Okay, so overall you will get entire column as integer. Second thing you want to do is in the

[3:07:07] unit price there is this dollar symbol. So you will say df do with column. So you always use this with column function whenever you want to do transformation on a specific column. So on unit price column what kind of transformation I

[3:07:20] want to do? I want to replace this dollar symbol with space. And this is a the format for regular expression. And then cast it to double. The third thing

[3:07:32] I want to do is from my percentage column, right? Like I have this uh discount. I want to remove this percentage and I want to convert it to a percentage and I want to convert it to a number, right? So this will be double.

[3:07:46] Then you want to do some processing with coupon code. You want to convert that to lower. So what is my coupon code? Well, coupon code is this. So I want to convert from capital to lower. This all depends on the business requirement.

[3:08:02] Okay? It may vary uh what kind of transformation you want to do may vary based on the situation. Then you want to change web to website, app to mobile, right? We we discussed that. Okay? So let's run all of these transformations.

[3:08:17] So when I display the rows you will see some of these things getting uh in effect. Okay. So see discount percentage that percentage thing is gone and u text

[3:08:31] that percentage thing is gone and u text amount this web is website mobile is mobile app and this is in the lower case. We also got these two metadata columns. Okay. So the next thing we will do is conversion of date because this DT

[3:08:47] column is string. You want to convert it to a date format. So when you say to date, it will actually change the data type and it will still keep it in Y mmd,

[3:08:59] right? Y mmd. See, right now it's ABC string. This will convert it to date format. Second thing you want to do is for order timestamp you want to convert

[3:09:12] now everything is string we just saw right? See order time stamp everything is a string so you want to convert it into proper uh timestamp. Then the item sequence you want to convert that to integer. text amount you want to remove

[3:09:27] some invalid characters from the text amount just in case if there are any characters there might be leading and uh lagging spaces you want to uh then convert it to double and then you are adding the process time. So this will

[3:09:41] add a current timestamp at which that particular record was created. Okay. So particular record was created. Okay. So let's execute this code and then let's display this. All right. So now when you look at dt see this data type is date

[3:09:56] previously it was here it was string okay this data type is time stamp previously it was string then item sequence is integer all right so you can do some quick check and if you look at print schema you have the right data

[3:10:11] types okay at the end as always you write it to a silver delta table so see now in silver schema I got order items And here you can quickly spot check. You

[3:10:25] And here you can quickly spot check. You can uh do some quick uh quality checks here and you will find that now this silver table is in a good shape. And the third step in our Medeline architecture is processing a gold layer. Okay. And in

[3:10:38] the gold layer we are going to create bunch of new columns. Okay. So this is the table that we have. The first thing we'll do is create gross amount. Gross amount is simple folks. Very simple business logic, quantity into unit.

[3:10:53] Let's say you go to a store, you bought, let's say you went to a store and you bought two cans of beans. Okay, so one can of bean is $5. So two cans of bean is uh $10, right? So that will be your gross amount. So quantity into unit

[3:11:08] price. And then discount amount is gross amount into discount amount divided by this. Okay, so that's your discount amount column. Sales amount column is simple gross minus discount plus tax. Yeah, you have to pay tax too. Okay,

[3:11:25] don't forget government. And date ID is just a unique identifier for your date. And then you also have this coupon flag whether you have coupon code or not. Okay, so if you have coupon code then flag will be one. No coupon code it will

[3:11:38] be zero. So let's execute this and look at this. Now you have and then you have this gross amount. See gross amount, discount amount, sales amount. These are

[3:11:50] the new columns, data ID and coupon flag. Now we will also normalize the are in different currencies. And when you are doing holistic review of your everything into single currency. Let's say our executive lives in India and

[3:12:06] they want to see everything in INR currency. So you have to convert all these numbers into common INR currency and for that you have provided the spot rates you know currency conversion rates. So for USD for example is 88.29.

[3:12:21] So right now we are hard coding but uh if you're working in the on the real project you will use some currency API. See if you Google currency APIs you will

[3:12:33] find bunch of APIs. So you can maybe buy a subscription to this and uh by using HTTP call by using that API you can get the real rates as of that particular

[3:12:46] transaction date. Okay. So let's uh initialize this uh data frame. So rates df is just a data frame with two columns currency and the conversion rate. And then you are going to do a join. You have your main data frame. You will join

[3:13:02] based on that currency. Okay. So let's do a join and after doing join what you do a join and after doing join what you get is in your uh orders fact table see this is my orders fact table towards the end you will get conversion rate right

[3:13:18] like the conversion rate and the sales amount in INR okay all right so if you look at this code you will uh get an idea then you want to create the data frame for your final goal table so you will select bunch of columns columns you

[3:13:34] are renaming some columns instead of DT you are saying transaction date order TS you are saying transaction TS so this way when data analysts are using this gold table for their BI needs or let's say when AI engineers or data scientists

[3:13:49] are using this for their AI needs these columns are more descriptive all right so once you do that you are going to display this and here you see this display this and here you see this nicely processed table which you will

[3:14:04] write to your gold table. Okay? And let's do a quick spot check. So go to let's do a quick spot check. So go to gold and in gold you will see fact table order items and look at this. My gold data my gold is ready for my BI. See

[3:14:21] here you are finding you can do some again spot check. We have this net amount INR all of this and it's now ready for our BI. And just a quick sanity check, I just want to make a count and see my number of records are

[3:14:35] 183,000. Before we start building our BI dashboard, I want to highlight this amazing AI feature called Genie. Now, AI is integrated throughout this product. And you already see this kind of

[3:14:50] suggestions uh at the top of these tables. So let's say for the fact order item table, I want to know how many transactions used coupons each month. So when you click on it, it will actually generate a SQL query. See, it generated

[3:15:05] a SQL query. You can review it and then you can run the query. Isn't this amazing? So in August, September, October, these are the transactions which used coupon. Now let me go to Gene. This feature that I just showed

[3:15:19] you is integrated into catalog. uh section but you can go uh click on genie section but you can go uh click on genie and click on new and here you can go to and click on new and here you can go to e-commerce always use gold for your

[3:15:34] analytics purpose right because bronze is raw silver may not have bi ready is raw silver may not have bi ready columns etc so in the gold let's select or for tables hit create button and then give some name to this right so this is

[3:15:48] e-commerce genie space and on the right hand side You are seeing this tables. You can also give instructions to LLM. Let's say if you want answer to be generated in a specific format. Here is where you will do your prompt

[3:16:01] engineering. And then there are settings like what kind of warehouse you want to like what kind of warehouse you want to use, space ID and so on. So let's close this and let's start asking these questions. So the first question I have

[3:16:14] questions. So the first question I have is how many transactions were made in US currency? And by the way, I'm going to give you this text file with some sample questions. Okay, so I'm just uh using the questions from that file. So see

[3:16:27] total number of transactions in USD are these. And if you want to verify your code, see it will generate SQL query, right? So select count distinct transactions ids from this gold table where the currency is USD and

[3:16:41] transaction ID is not null. See, it's so detailed. Then the second question I detailed. Then the second question I have is what is the total revenue in INR and number of order lines by month and show me the monthly trend chart. So see

[3:16:56] I got the number of orders per month August, September, October and the revenues in each of these months and I got this chart right? So one is total revenue and order line count. Now you can click on this button and you can

[3:17:11] modify your visualization. So let's say I want bar chart instead of line chart. Okay. So then you get this. You can add labels and so on. So you can customize your uh chart here. And the next question I have is what is the average

[3:17:26] revenue per region. Remember we created region column like east, west and so on. And look at this in the England there are these regions. So it is showing you that. Then in India these are northeast etc. You can also modify your question

[3:17:40] and you can say what is the average revenue per region. Show it in table along with country. So see country was missing initially. Now you have region as well as you have country. And once

[3:17:56] again you can verify your SQL query uh here by clicking on show code. I personally love this genie feature. It's making your life so easy as a business question and get your answers. Okay. So we are going to attach this txt file

[3:18:10] which has bunch of questions and you can also come up with your own questions. Now before we create the BI dashboard, let's analyze our needs. See we we want to do uh some visualization such as okay, what is my net sales per quarter?

[3:18:27] Now when you look at our gold fact order items table, it has net uh amount. Okay, net sales amount and it has date but it doesn't have quarter data. So naturally doesn't have quarter data. So naturally we are going to do a join based on this

[3:18:42] date ID. So let's say this is a data ID. We will have same data ID here in dim date table and this one has this quarter data Q3 Q4 and so on. So doing join

[3:18:56] between these tables is one option. But in the industry uh we have seen this practice where people join all these tables and they create one flat table one highly denormalized table which has all the columns. So what we will do here

[3:19:12] is create a view instead of table we will create a view which will join all these tables and we get one view where we have all the columns. So for this I

[3:19:24] will click on SQL editor. So I'm in the SQL editor here and I will create a view SQL editor here and I will create a view here. So you will say create or replace here. So you will say create or replace view and this is my view. Okay. And this

[3:19:39] view and this is my view. Okay. And this view here you will write a query that is joining all those tables. So let's see how that query looks like. So essentially you are selecting data from your main goal table which is your fact

[3:19:53] your main goal table which is your fact table. You're joining it with dim date on data ID. Then you're joining it with products on product ID and then you're having all these bunch of columns. Okay. So let me just run this and uh show you

[3:20:09] So let me just run this and uh show you the result. So it says okay. Now I can go to catalog and in gold see I am seeing a view. So when you click on this seeing a view. So when you click on this view now I have this highly denormalized

[3:20:23] view or table where I have everything. So let's say you have a transaction right this transaction okay which region it belongs to which category it belongs it belongs to which category it belongs to then um hour of the day see rating

[3:20:37] count quarter right I don't think we have region here because we did not do a join with the customers table but that's okay at least we did a join with our uh date calendar table so see we have quarterly data what are my revenues on

[3:20:52] weekend versus week days so we have all that information here. Now we of course that information here. Now we of course did a join with order items and products but uh you will see this practice where they even do join with customers let's

[3:21:06] say they join every single table and they create this one huge table with so many columns and now your data analyst when they're building a dashboard all they have to do is refer to that table. Let's now create a dashboard. You will

[3:21:20] click on dashboard in the left hand side menu option. Click on create dashboard and let's hide this. So see here you will see different options. So the first

[3:21:32] option at the top is pages. So you can create different pages in your dashboard. So let's say you have a view for sales, you have another view for marketing, third view for supply chain. Based on different business functions,

[3:21:47] you can have different views. I will uh close this because this is for filters. We'll look into it later. And here we will begin adding a text box. So here we will begin adding a text box. So add a text box here. And we will uh call

[3:22:03] this uh sales sales insights, right? Sales insights for your e-commerce business. And let's select heading three. Okay? Or

[3:22:15] And let's select heading three. Okay? Or let's say heading two. and you can let's say heading two. and you can resize it. Then you can click here and add a visual here. Now you need to connect this with a data source. Here I

[3:22:29] don't see any data set. So let's go to data and add a data source. So go to e-commerce gold and the view that we created. Okay. So let's confirm it. So it connected with it and you are seeing the data which means my dashboard is

[3:22:44] connected with my data source. So now when you click it here you can select that particular view in your data set. The moment you do that here AI assistant will give you some suggestion okay what kind of dashboard you might want to

[3:22:59] have. So let's say you want monthly sales trend okay you want to know monthly monthly sales trend and see it will create this nice visual for you. So here I see in August I got 621 million of

[3:23:17] I see in August I got 621 million of sales. In September I got 571. In October I got 618 millions of sales. Okay. Uh you can just say accept. You you you can change things by the way. So see here what it did is for this

[3:23:31] particular visualization. Let let me create this visualization manually so you get an idea. So I will create the same thing. So here you will select uh same thing. So here you will select uh line chart. Okay. On x-axis you will

[3:23:45] have month name. Okay. So month name is on x-axis and on the yaxis you will have net sales amount right like net amount

[3:24:00] let's say net amount and that to a sum of it. Now it's the same data but here the order is not correct. August, October and then September, right? So, October and then September, right? So, August 621. So, August 621, right?

[3:24:13] October 618, 618. Okay. So, in the AI generation suggestion, the order was correct because they use this uh monthly transaction date. Okay. So, let me just show you monthly transaction date. So,

[3:24:29] here uh transaction date and then monthly, right? So you can see say monthly, yearly, whatever. And you say monthly, yearly, whatever. And you can give it some title. So title is yes.

[3:24:43] So monthly uh sales trend, you can change this label. So on the y-axis, how do I change label? So by will show you this annotation, right? This is useful, right? See all these uh

[3:24:59] numbers. And let me go ahead and delete this. U so here let's say I want to change this particular x and y-axis uh label so here the display name is sum of

[3:25:11] label so here the display name is sum of net amount so you can just say net amount and then on the xaxis

[3:25:30] okay you can Then go ahead and create another visual. So let me add another visual here. And let's say you want to know net amount by category. See or net

[3:25:44] let's let's do net amount by category. So you see you have all these suggestion. And this particular category has 1.38 billion uh sales, right? Electronics is a highest uh uh selling category. Then

[3:26:01] home and kitchen is this much. You know you can change the visualization type. If from bar chart you want to change it to something else you can easily do it. So using this uh drop-down. Then let me add a heat map actually. So here I'm

[3:26:15] add a heat map actually. So here I'm going to add a heat map. So let me just going to add a heat map. So let me just make this really big and I will uh make this really big and I will uh select a heat map here. And x-axis is

[3:26:27] select a heat map here. And x-axis is hour of the day. So hour of the day and yaxis is net amount, right? Sum of net amount. So here let's select this. So this will be actually net amount will be in the color. So the yaxis will be day

[3:26:45] in the color. So the yaxis will be day name. Okay. So let's call day name in the y-axis. And then in the color And then in the color you can have net amount. So now see what

[3:26:58] you are seeing here is you have all these days right and the regions which are darker has more sales. So you can say that first of all Saturday Sunday I

[3:27:10] have uh good sales. See you have more darker blocks in Saturday and Sunday and Saturday on this hour right at 2:00 or let's say at 5:00 I have a more sales. Okay. So you can do this kind of heat map

[3:27:25] analysis as well. You can also click on filter and add the filters here. So filter and add the filters here. So let's say uh you want only particular category. Okay. So you will say category name here and see it will show you

[3:27:39] different categories. So let's say you want to do analysis only and only for electronics. So when you select electronics here, see now data is refreshed to only electronics. I mean in this bar you'll obviously see only one

[3:27:52] bar chart. But these numbers this sales number this this particular thing is very useful are only for electronics. Then let's say for books you want to analyze the sales number. So see now it is refreshing. for books the sales are

[3:28:05] less but see you using this particular option you can add all kind of filters all right folks so that's it for the dashboard as I said uh before you can create another page let's say for marketing just in case if you're

[3:28:19] marketing data and these kind of dashboards can be used by business users to get the insights on the overall status of your business in the end let's quickly talk about orchestration see in our project

[3:28:34] whatever we did is a historical backfill. When you start working on a data engineering project, you might have some historical data. So we had this backfilled by running those notebooks

[3:28:48] manually. But the second phase once your data engineering infrastructure is set data engineering infrastructure is set up is daily incremental updates. Now we did not cover this in this project but usually what happens is now let's say

[3:29:01] sales transactions are happening on a day-to-day basis those transactions will keep on coming remember our order items uh table so that table will have new and new CSV files coming in every day and we

[3:29:16] need to set up some automated processing so whenever new data comes in it will go through this entire pipeline all the way till your dashboard and for that you till your dashboard and for that you will use the feature of these job runs.

[3:29:29] Okay. So the way you will do it is you go to job runs, you create a job and here you will run your notebook. Now once again folks, this whatever I'm showing you right now is valid for daily incremental update. What we did in this

[3:29:43] project so far is historical backfill. So we have notebooks only for historical backfill. Okay. So let's assume that we have done coding for daily incremental Let's assume that. Okay, we did not do it but we are assuming it. In that case,

[3:29:57] you will create this different task. So let's say this particular task is uh of let's say this particular task is uh of type notebook. Then the source is going to be workspace and the path is going to be in project e-commerce. Let's say you

[3:30:12] have dim processing, right? Dimension processing for bronze. And this is the notebook one dim bronze is a notebook that will do dimension data processing for bronze layer. Okay. You will select your compute uh you will

[3:30:28] add any parameters that you want to add and then you will say create a task. and then you will say create a task. Okay. And this task will be let's say dimension uh bronze processing.

[3:30:41] uh bronze processing. Okay. And create a task. So when you do that, see in your diagram here, let me just make the whole thing like smaller. So now you got this then you can say add a new task of type notebook here. So

[3:30:57] after this step you will do what? Well, you will do a silver processing right? So you will say select silver and the name here is dim silver processing. Create a task. Then you add another task

[3:31:14] Create a task. Then you add another task which is again a notebook and it is dim gold processing. And here uh this is dependent on this. See this task depends on this. You can have

[3:31:27] branching logic as well. And here you select dim gold and you will say create task. Okay. So now this whole thing is the processing for your dimension data.

[3:31:39] the processing for your dimension data. Okay. And this job will be called a dimension processing. Right? And then similarly processing. Right? And then similarly you will create another job for your

[3:31:53] fact processing. And then you can have a parent job which will run both of these. And then you can set a trigger. So see in the schedule and trigger you can say in the schedule and trigger you can say add trigger and you can say uh schedule

[3:32:05] schedule every day. Let's say you want to run it, every day. Let's say you want to run it, you want to run it every day at uh for example 11 p.m. in the night once all my uh business transactions are done

[3:32:22] 11 or 12:00 whatever right and you can show the chrome syntax as well active and say save. So now what will happen is this pipeline will run at 11:00 every

[3:32:34] day. So what people do is even when I was working at Bloomberg we'll have stock market data and stock market will close at 4:00 all the data stops coming then after some buffer let's say 5 or 6:00 you will run this kind of pipeline

[3:32:50] every day so right now we have set up a schedule every day at 11:00 this pipeline will run it will do processing for bronze silver and gold okay ideally what you do is this is the pipeline for dimension processing you'll create

[3:33:04] another pipeline for fact processing and you will have a parent job which will process both dimension and fact and you will douling on that you will not do on this one okay but [snorts] I I just want to just show you this quickly and this

[3:33:17] is something valid for daily processing for historical we ran it one time and that's it okay so this is how things work in the industry big companies which infrastructure they will have this kind of pipelines set up and these pipelines

[3:33:31] will automatically run you can even run it right Now see this is a manual way of running it. Otherwise you can run it on a trigger. So Peter what have you found? >> So Tony uh here is my quick report. I'm

[3:33:46] sharing it here on my screen and I would like to mention my findings. So the first one is it is scalable. It is easy to upgrade or downgrade capacities and it is fully managed. That means you

[3:33:58] infrastructure. You can just focus on your business logic. Second point was about adoption. So on that I spent about 2 weeks and I was able to get this project done and the adoption won't be difficult for our team. That's what I

[3:34:14] can say from my experience. The third thing uh it was about unification. So for that it provides almost everything in one place and of late they are doing a lot to bring these new features to make it all in one place and I really

[3:34:31] like some of the features like workflow dashboards genie and there is a lot more dashboards genie and there is a lot more as I stated in the report. Okay so Peter to me this looks positive and I've uh you know during this time I kind of

[3:34:44] tested it myself too. So I'm quite positive. Let's give it to Bruce and positive. Let's give it to Bruce and wait for his decision.

[3:35:05] miniourse. If you want to get certified from code basics on this miniourse, then provided in the video description. You can take the exam there and get certified. Also if you want to showcase your work then feel free to post it on

[3:35:19] LinkedIn tag datab bricks code basics myself and Haman. [music] I wish you all the best and thank you very much for watching.