0:00 The demand of analytics engineers is growing rapidly and you know what this data role is more AI proof as compared to some junior data roles. I'm sure I also want to become an analytics engineer but I do not know from where to start and exactly what to cover. What are the tools frameworks I need to know and I do not have any kind of structure. Do not worry that's why I have created this complete master class. And first thing first you first need to understand that analytics engineering is a complete domain in itself and you need to grow step by step in domain and your very first step is actually gaining a very strong foundation around all of the concepts that we use in the analytics engineering world and that's why I have
0:45 created this complete 4 plus hours long master class for this domain including these topics such as what is actually an analytics Engineer role the complete data life cycle ELTL blob storage and data lake open table format such as delta lakehouse versus warehouse configuration based incremental loading back filling of data applying abserts over appends what is Apache Spark and why we need to use pispark relational data modeling and dimensional data modeling Apache airflow and Azure data factory for orchestration slowly changing dimension and its types not just this we also going to learn about a very popular framework within analytics engineering which is DBT and we will perform an end to end example as well with DBT and data bricks. Wow, that is a
1:33 complete course in itself. But this role is only for those people who are already in the data domain and who are ready to learn more because you need to learn a lot of new things if you just want to enter into this domain. Why? Let me tell you. because this is a role which requires much more skills than a data analyst knows and lesser skills than data engineer knows. So this is a sweet spot between a data analyst and data engineer. But yes, the demand is growing and the supply is very less. So if you are ready to learn and upskill yourself and you want to actually enter into this role then this master class will help you in all of the possible ways with amazing hand-drawn doodles to explain
2:18 the critical concepts with notes with demos with everything and obviously for free. So without any delay just take out your notebook and just start taking notes because we going to learn so much in this particular amazing master class. So what's up? What's up? What's up my fam? Happy Sunday and finally analytics engineer masterclass is here. First of all, let me just tell you analytics engineering as a domain is growing rapidly. So if you are a beginner, yes, this video is for everyone who is a beginner because this is a beginner friendly video. So just relax. You do not need any kind of prior knowledge to get started with this thing. Simple. Okay. Obviously, you should know some basic stuff, Python, SQL and all of your beginner level stuff that you do in the
3:04 data domain. Obviously now let me just tell you one thing that this video this master class by the end of this master class you will be able to build a strong foundation for your entire analytics engineering domain which will guide you that what exactly you need to know why you need to know and each concept will be crystal clear in your head so that you can just start learning deeper and deeper and deeper inside those topics. Make sense? We will also talk about popular tools. Yes, your favorite tool DBT and all. We will just talk about most of things and let me just tell you the agenda quickly that what exactly we're going to talk about. But I hope that this a this analytics engineering masterass will help you in every
3:51 possible way if you want to become one. Simple, very simple. Okay. And obviously the resources will be there in the video as well. So it's fine. So let me just talk about the agenda like what topics I am going to cover in this video. First thing first I am going to cover the analytics engineer role because you first need to understand who is actually an analytics engineer. What the person will do after becoming an analytics engineer. So you need to understand that thing and you will get to know that the supply of analytics engineer is less why and why you should just target this role as soon as possible because there will not be like much openings for analytics engineer roles because this is a kind of senior role to data analyst but junior role to
4:37 data engineer. So time is less you have to hurry. Then we're going to talk about data life cycle. The entire data life cycle because when you are targeting such a role which requires most more more expertise than just data analytics, you need to understand the entire data life cycle. Then you will get to know about ELTL which is the modern way of performing all of the things. Then you will get to know about blob storage versus data lake which is the modern data architecture that we do um in storing and retrieving the data. then you will know what exactly is a lakehouse then open table formats and all of the other table formats that you need to work with the modern data engineering because see when you are just working as a beginner or let's say you are a fresher or let's say you are a
5:23 student you would have covered the fundamentals like SQL Python databases queries it's fine but when you actually perform the tasks in the real world it's not the case why because the things that you are reading right now in the books were written like 20 years 15 years back but now the architecture is completely changed. We talk more about the open table formats, abstraction layers, data lakes, ELTL, lakehouses, these things. Make sense? Good. That is why this video is for you. And then we're going to talk about incremental loading and not just the traditional incremental loading, the modern way how to incrementally load the data using configuration files. Then
6:08 we're going to talk about backfilling, which is a very popular step that we all developers perform. Then we're going to talk about absurds. Then we're going to talk about Apache Spark which is the most popular distributed computing framework and pispark as well. Then data modeling. Here this chapter this data modeling chapter is very special for you because here you will learn that data modeling is not exactly what you are thinking right now. Data modeling has different different flavors. Data modeling is also that data modeling where you perform normalization. First normal form, second normal form, third normal form. But in on the data engineering side on the analytics engineering side we do not perform that data modeling not at all that will be done by DBA we perform in the form of
6:55 facts and dimension table. So this will be a very thorough chapter. Then we're going to talk about Apache Airflow and Azure data factory for orchestration. Then yes we going to deeply talked about DBT because DBT is the backbone of the entire um data engineering or analytics engineering domain. Not data engineering but analytics engineering. Then we're going to talk about sedd types basically slowly changing dimension types and we also going to see one demo of delta lake and and and I will also show you end to end demo how you can just work with dbd with data bricks or any other platform. So this is a thorough complete masterass is made for you for all of the um future analytics engineers. Simple expectations are clear agenda is clear everything is clear very well done. Now let's get
7:41 started with our master class and we have to congratulate our newly newly people who have cracked that dream offer. So simply say congratulations, congratulations congratulations congratulations, congratulations. And congratulations. And one more. One more. One more. One more. One more. One more. One more. Wo. Wow. Great list. Big list. I want this list to be like more more more big. So now the thing is even more bigger. So now the thing is first of all congratulations to all of these people. If you if if if you want your name to be here you just need to do one thing. What
8:29 is that one thing? Learn learn and learn. There is no alternative of learning. Simple and especially in the AI era. AI is taking jobs. Yes. Yes. But not of those people who are actually learning who are actually adapting with the technologies simple and you will also get to know why analytics engineering role is actually AI proof because I have mentioned some points which will actually show you the reality. Okay. And this is me Anjel Lamba in front of you. And now let's get started with our master class on HM analytics engineering. Let's get started. So whenever we talk about analytics engineering as a domain or you
9:16 can say analytics engineer as a profile, you first need to understand who actually is analytics engineer. Simple question. So this is a very sweet spot that is obviously growing right now. First thing obviously that's why we need to just learn about this. Second thing when you will look at the roles and responsibilities of this particular profile you will love it. You will literally love it and you will actually feel why I like myself didn't jump into this domain earlier because obviously analytics engineer was engineering was not there but that's not the case people were actually doing the same stuff before as well who understood the importance of growing and learning new and new things to be honest. So let's let me just first of all tell you this
10:03 particular thing who is actually a analytics engineer who is an analytics engineer and obviously this is a kind of selfexplanatory profile with its name but let's talk about it. Let's say in your organization you have a data team. Obviously every organization has a data team. Now in that data team you have some data engineers. Very good. Very good. Let's say let's take a sample of individual person. Okay. So let's say this is one data engineer and this data engineer does the data engineering things like pipelines automation elds data warehouses, lakehouses, all of that stuff. Very good. Now there is a data
10:49 analyst as well. Okay. Obviously now this data analyst let's keep this person here. Okay. because this is the upstream and data analyst is the downstream because data and analytics team is like the stakeholder for data engineering team. I hope you have this basic understanding but do not worry we're going to cover that as well in the data data life cycle. So do not worry at all. So let's say this is the data engineer. Okay. And now we have one data analyst. Let's say this one. Okay. Let's say this is the data analyst. Make sense? He's cute. So now this data engineer is working on the data engineering tasks. This data analyst is actually working on the data analytics task. Now what can be data analytics task? Now I know in each
11:34 organization the roles and responsibilities can differ but most probably this person would work with let's say PowerBI just to build the reports. This person would work with um obviously SQL to retrieve the data to analyze the data simple. This person would also work a little bit with Python if person is using pandas obviously because in all the organization not everyone uses Python a lot but I am assuming that you are using Python. So this is like most of the things that a person use and now someone will say Excel Excel bro Excel is okay okay let me just write Excel. See happy happy use Excel. Excel is everything. Yes use Excel. Excel is just a you can say kind of source of data.
12:23 You do not need to build Excel models, macros and all of these things. If you are doing this, wow. So this is let's say the stack. Okay. This for this data analyst makes sense. Now what will happen? Now we have a new role which is called analytics engineer. Now this analytics engineer, let me just bring that person. Um let's say this is the person this one. So this analytics engineer person which is standing in the middle now this person this analytics engineer why this person is actually sitting in the sweetest spot because this person knows everything that this person is doing
13:08 first of all. Okay. Everything. Yes. Everything. Literally everything. Okay. And this basically this person knows much more than this person basically data analyst. Okay. But this person doesn't know everything that this data engineer knows. So this is a kind of bridge. This is a kind of role which is senior to data analyst but junior to data engineer. Are you getting my point? Because see data engineer is itself a kind of senior role. Um yes you can see some fresher roles for data engineering but it is very less. But whenever someone wants to hire a junior data engineer, they were using the terms let's say junior data engineer. But nowadays they are using the terms let's say analytics engineer. Still they use
13:53 junior data engineer or data engineer with less years of experience. I'm not denying that fact. I'm just simply demonstrating this particular profile why you are seeing this profile even more in your job portals because this is a kind of profile that you can consider as a junior data engineer as well. So this person knows a lot of things almost everything that a data analyst knows but this person knows something about the work that this data engineer is doing not everything not everything just to clear the expectations not everything but some things what some things we're going to discuss about that obviously that's why this is a master class but I hope now you know who is this analytics engineer why this is the sweetest spot see
14:40 these kinds of things can be automated by AI and what can be just open Microsoft fabric write one prompt and your entire dashboard will be ready in just few minutes these are already being um automated obviously um the solutions are not optimized it is just the beginning point but this will be optimized as well within just 1 to two years it's not a big deal but the work that analytics engineer does cannot or I can say will be very hard to automate by AI Because this analytics engineer doesn't just build the reports. This analytics engineer doesn't just write SQL queries to retrieve the data, select testics from this database, group by having, join, blah blah blah blah blah. No, this analytics engineer works with the data
15:26 models, data warehouses, you can say your um schemas, your lakeouses, all of these things. And do you know what? Do you know what I have been like analyzing this particular field a lot like data field like what will be the future of it. Obviously I am not an astrologer but let me just tell you my insights. If you are working more closely on the infrastructure side more on the architecture side more on the ownership of the entire lakehouse side you are less likely to be replaced by AI. If you are working on just PowerBI, SQL, some simple pandas, um um you can say transformations, your favorite Excel, then you know what I what do I mean?
16:13 Okay. So this is the trend that I can see clearly crystal clear crystal clear. So this analytics engineer performs these task. Obviously there we need someone who can take ownership of the reports. Obviously we cannot say hey AI just build the dashboard for me and maintain it for the eternity. Obviously no we need a person but this person doesn't need to spend the entire month to build one dashboard. This person can now build the dashboard in let's say just half of the time or let's say quarter of the time. Let's say in just one week and rest of the three weeks this analytics engineer will focus on the infrastructure that this data engineer alone was doing. Now this person will be an ally for this data
16:59 engineer as well. And whenever we just work with bigger organizations, there are so many stakeholders which just come with some silly silly errors, silly silly requirements that require data infra infrastructure level knowledge in order to answer and debug those things. Who will do that? That role is booming right now. That role is booming. Are you getting my point? And then when you become the analytics engineer then if you want to go more deeper technically you can become a data engineer full-fledged data engineer works with big big data Apache airflow Kafka data snowflake all of these technologies but if you do not want to you can still be analytics engineer and you can lead the team you can lead the projects and not I'm not just talking about the
17:45 management roles not at all technical leads senior leads like technically this is the analytics engineer role and I Hope now you have a crystal clear explanation or understanding of who is an analytics engineer. If that is clear, please do let me know in the comment section. I want to see like who actually understood this role because when you understand the role only then you should go towards that direction and if this if if this role is not for you then obviously you should think about it right. So one thing is clear you need to upskill yourself and that is it. But how much and what exactly you need to follow? I am here to guide you. Simple. So I thought of just putting this section in the very beginning so that you are crystal clear about the
18:31 expectations from this role from this domain which is actually not a new domain. It's just like knowing much more than you actually know and doing much more than what currently you are doing because those things can be done by AI as well. You can use AI to boost your productivity and you can just use rest of the time to work on the infrastructure side. And I think this is a very common sense that every data analyst like someone who has like some knowledge some experience one to two to three years 3 years of experience the person would know that these tasks are not enough because see in just 1 to 1.5 years these tasks can feel like automated like feel like feel like you are just doing the redundant task again and again. You build one dashboard. Okay. Two. Okay. 3 4 5 6 7 8 9 10. Okay.
19:18 Okay. Okay bro. Okay. But what after that? Now you need to take the ownership of those dashboards and now you will do other things as well. Some automations as well. Are you getting my point? Like what exactly I'm trying to just tell you? AI is there to help you. AI is there to boost your productivity. Uh pro productivity. If you are working with this stack and is actually helping you to take ownership of the infrastructure, you have to start taking the ownership. I hope this is clear. Very good. First section is done. Very good. So just do let me know in the comment section if this section was helpful. I
20:03 have specially added this section. Who is an analytics engineer? Because see um you will go on LinkedIn and you will say analytics engineer. You will find actually a lot of roles obviously and you will start reading reading the JD and you will say what is this SED? What is this ELT? What is this DBT? You will say bro what is this? So you need to know what is this thing? Okay. So now because now we know who is an analytics engineer. Now we need to start preparing, start learning for it, right? And the learning starts right from the data life cycle. Data life cycle. Okay. Now what is data life cycle? If you are a data engineer, you would know this thing for sure because see analytics
20:49 engineer role is for everyone who wants to walk on the path to become a data engineer or now yes we have a pre-step or basically pre-roll as well especially analytics engineer obviously so I still remember like when I was just um starting my journey of my data engineering I also went through this data life cycle and I was like ooh I actually didn't Think about this thing like this like obviously everyone knows about data life cycle. Um data is generated, data is loaded, data is reported. Yes, this is true but when you just dig deeper you actually understand what this person and this person needs to do. We will focus on this person because we are talking about this person. Okay. But
21:34 because I said that this person needs to know a lot of things that this person is doing, right? So we will simply take this person as an example. Let's say that this person's name is Rahul. Okay. So, we'll talk about Rahul from now onwards. Very good. And yeah, so if we just talk about data life cycle, we need to know what exactly the data life cycle because I am assuming that you are in the data domain. You are working with data obviously in the organization. But let's say you actually do not know the entire data life cycle might be because you are actually let's say this Rahul this Rahul was like has started preparing for the analytics engineer role obviously okay because it is his or her goal Rahul is obviously a boy let's say anyone okay so this Rahul was most
22:24 probably working on something called as let's say warehouses or let's say database Sometimes databases in small organization they do not build warehouses. They simply build databases and they are happy with that and nothing is wrong because it's their data. They are the owner and they can do whatever they want. So this person was actually directly working with these two things and was building some reports was building let's say some queries SQL queries was building some panda scripts to perform some um analysis and handing over that analysis to their managers or anyone right everyone knows this thing but do you know what how data forget about SQL database how data actually reached till data warehouse
23:09 that is This is the thing that you now need to know bro. Now you need to know how this data warehouse was created and how the complete data life cycle actually moves because you need to take care of that life cycle as well. Yes. So let's try to see that life cycle. Okay. So let's say let's say everything starts with something called as data generation. Okay. Now be with me. Okay. This is a very in-depth section first of all. So it will be called as let's say data generation because we are going to talk about data life cycle. So just listen or basically take notes of these things data generation and all of these things that I'm just going to tell you because these needs to these need to be in your
23:56 notes. Obviously I will provide this particular nodes but just create your own nodes as well. So let's say data generation which is the very first step of our life cycle. We are working with data. How? Because we have data. We are building reports. How? We have data. We are building data models. We have data. We are building lakehouses because we have data. Because we have data. If we do not have data, we would not have like done anything. If we didn't get any kind of data because we had data. Now what can be the data sources? It can be literally anything. Let's say you are using your mobile phone. Do I have any mobile phone image? No. Very good. Let's say you are using your um application. Any application you are using Amazon,
24:41 you are using your favorite website, anything. Okay. You are doing some shopping. You are doing literally anything. Okay. Let's say you are doing some shopping. Shop online shop. You are just scrolling something. You can literally do anything. Everything generates data. Literally everything generates data. everything. Okay. So now when everything generates data, we need to obviously store it somewhere obviously and in most of the cases these data generation will be applications. It can be mobile application, it can be web application, it can be literally some IoT data, it can be anything. But most probably it will be coming from an
25:28 application, a kind of interface. You are watching this video. This is also a kind of interface, right? You are watching this information. This is also a data. So, we are actually getting the data from an interface. Okay? And we need to simply store this data somewhere like anywhere. Okay? So that is why we start our data life cycle. We simply say hey data is being generated. Now we need to do something something how something and what something let me just tell you. In most of the cases because you will feel relatable I want to mention this step. By the way this is not mandatory in all of the solutions but in some solutions. Whenever we talk about data generation we sometimes refer to something called as
26:14 associated SQL database. What does it mean? Let's say your organization has built a website. Okay. Let's say you're working in a product based company and let's say for example Amazon. Now Amazon has some sources for data their um Amazon website application. You are just simply selecting some um products. You are simply liking some products. You are simply um saving some products. You are simply buying some products. So everything click every click is being recorded. every click is being stored somewhere where in most probably most probably in case in it will be stored in the database most probably. So this thing needs to be there. This
27:02 SQL database needs to be there because it needs to store your data. But what type of data? Transactional data. Okay, transactional data. Now what do we mean by transactional data? Basically transactional mean like transactional word is like self-explanatory transaction transactional mean transactions you are scrolling transaction you are buying something transaction you are doing any action transaction you need to record anything transaction everything is a transaction that's why we store the data immediately in the SQL database in the transactional database or you can say RDBMS relational database
27:49 management system. That's why you see the databases like MySQL, Postgress SQL, Microsoft SQL Server, Oracle SQL. If you have some hosted applications, these are the databases which are very popular that we use at the starting point for our organization's data because our organization's data is stored in a database. In some cases it is directly coming from the application. What do I mean? Sometimes it is not let's say attached to your company's database. Let's say there is um a kind of you can say application a kind of web application or any kind of API that you are using to pull the data and in most probably um this will be like HR
28:35 department data. So let's say um you have some application for HR. Okay. And for HR you have hired or basically um considered a kind of um outsider basically a kind of contractor to save all the information for your HR department and now that data is not directly synced to your organization's database not at all not at all this particular you can say your HR department's application will be directly fetched in the form of APIs that's Why you hear that we work with APIs a lot. But do you know what? What are APIs? APIs are nothing just a kind of
29:20 middleman between you and the database. You and the database. So what will happen? Let's say I am this person. Let's say I am this person. Let's say Rahul or no Rahul is Rahul is this one. So let's say this person's name is I don't know. So let's say this is a person. So this person will use this API to fetch some data right but you know what whenever we say that I want to fetch the data from the API there is a database let's say Oracle database any database is actually attached to that API API itself cannot generate any data if that is a kind of pseudo API or um toy API where you are generating data directly in the form of JSON that is a different case but in most of the cases
30:06 there will be a database attached behind this API and you will not have access directly to this database. So in this case you simply make an API call to this particular API and this API goes to this database and this database returns back the data and this API will return data to you. Okay. So in short data is generated and stored immediately. So that is why according to me I should keep database but which database? Relational or transactional database. Okay. At the point of data generation because it immediately captures the data the moment it is generated. It is not running in the fly. It is there. It is attached. Okay. This is my personal you
30:54 can say understanding of it. You might find it in some books. You might not find this in some books. You will find it whenever you're just talking to Anch Lamba. Okay. So this is data generation. Perfect. Data is generated. The moment data is generated everything is good. But now do you know what? After data generation our real process starts. Here till here we are not involved. Yes we are not involved. Then who is involved on schlumba? Because there I can see database and whenever we talk about any data we should involve any data professional. Right? You are right. You are actually right. But still we are not involved. Why? Because we have a special data role which is not very popular in the data domain. Because this role is
31:41 actually a kind of senior role first of all. Second, you will hardly see any openings for this kind of role if you are a fresher. Third thing, this role go hand in hand on the admin side. That is why this person whoever handled this step is actually considered as let's say DBA database administrator. Okay, this is database administrators task. In some organizations if they do not have any database administrator which is a very rare case but still there can be chances. So what will happen the senior software engineer or senior you can say developer who have built this kind of web applications for the organization person will simply configure a database behind the scenes to store the data in some organizations they do this they
32:28 they do this as well but in most of the organization they will have DBA because without DBA it's not possible to manage and maintain their databases if they have very crucial data right so this role is actually called DBA database administrator. Have you ever heard data analyst working as a DBA? In small organization, obviously they can, but usually they do not work as a DBA because DBA is actually a different domain where you just need to learn about database design. You need to know about relational database design. You need to know about normalization, first normal form, second normal form, third normal form. Obviously, you need to know these concepts if you are a data professional. But you use these concept instead of implementing these. What do I mean?
33:14 Let's say you need to know first normal form, second normal form, third normal form and database design, primary keys, foreign keys, referencing tables, constraints, indexes, but you do not create SQL databases. You use SQL databases and you build data warehouses. That is the difference. You are a data professional. You need to know fundamentals because whenever you're working with database, you need to understand it because you still need to write SQL. Writing SQL is a different thing. Writing SQL for creating database is a different thing. Both are different. Both are totally different. One is called DQL, data query language. Second is called DDL or DML.
33:59 And data professional do not touch these things here. You cannot directly write DDL hey drop table in production you will you will not even have access to these database I'm so sure especially for some you will have but not for all you have to ask DBA hey please allow me to make some changes in database because it is required or something are you getting my point so this role is called DBA so we are not entered here in some organization yes yes yes we have exceptions everywhere but I'm just talking about normal generic organizations Okay, because in startups one person can literally do everything obviously but that person would need to know everything but yeah normal I'm just talking about normal world okay so now our role starts but actually our role
34:48 started here as well because database is there but I just want to tell you that this will be maintained by DBA it will be used by us okay this point is clear maintained by DBA we will not maintain it we will use it Okay, very good. Now the very second step of data life cycle is data extraction. Okay data extraction. Let's talk about this thing. Have you ever heard about data fetching instead of data extraction? If yes, this is the same thing. Okay, data extraction. So when we have data generated we need to extract this data
35:36 as well. Simple we need to extract this data as well. Now whenever we just extract this data we simply need to just load everything that we have. Why that is a kind of feature that we follow whenever we are just doing any kind of data extraction. Let's say I have this particular thing RDBMS. I will simply say hey I want to extract my data. Simple, simple, simple, simple. I want to extract my data. Okay. So here we will simply put our data in the asis form. Now where do we put our data in the modern world? We put our data in the data lake. Okay. Let's say it can be ads gen 2, it can be S3.
36:21 If you're using snowflake, it will be snowflake. But yeah, there are like so many so many options. Okay. So here we use data link. Data laks are cheap. Data lakes are inexpensive. Data lakes are actually the go-to solution for the modernday data life cycle. Okay, we simply extract this data. So, whatever data is generated, we need to extract it. Simple. This doesn't need any kind of explanation. Very simple thing. We need to extract it. Very good. Once the data is extracted, then what do we need to do? Then we need to transform this data. This is the actual actual thing that you need to understand data
37:06 transformation. Now we need to transform this data. Whatever data we have loaded obviously right whatever data we have loaded. Now what will happen? Now we simply need to say that hey whatever data that you have loaded please transform it. What do I mean by transformation? Now just to set the realistic approach you would imagine that data transformation is about or data engineering is about dealing with messy data sets. It's not actually the truth. It's not actually the truth. Data engineering is not about handling messy data sets. Data engineering is all about building the entire system, entire data
37:53 ecosystem. You would work on the projects where you will hardly see any kind of garbage in your data and still you would spend 90% of the time building complex data workflows. Why? According to the requirements of the project. Maybe in that project the requirement is just to build such a robust solution that it can automatically trigger the pipelines on failures. They can automatically detect what is happening. They can automatically detect the logs. They can automatically monitor the pipeline. They can automatically transfer the state of all of these things. I do not need to be very very technical here, but I'm simply telling you the truth. Simple data sets can create complex pipelines and reverse is
38:40 also true. Even the messy data sets literally messy data data sets where you will simply spend 90% of the time in just transforming the data that will literally build just a like very simple pipeline. Very simple pipeline. So do not consider data engineering as just about dealing with messy data sets. That is not true. Okay. Very good. So now whenever we want to transform our data we have specialized frameworks we cannot use our um normal thing that we just use let's say pandas and all why because we work with big data here the data that you have just stored that you have just stored is actually big is actually big just think about it there is so much data being generated and you have
39:26 literally um fetched or extracted everything now we need to transform it right so now in order to transform your data You use frameworks let's say Apache Spark Apache Spark like this or you can also use frameworks let's say DBT we will talk about it don't worry at all and why do we use it you will also know it and let's say like this and by the way um see Spark and DBT are not like different tools they go hand in hand so do not feel like DBT is like replacement for Apache Spark But because I need to tell you the frameworks that we use. So I'm simply telling you and do not consider these as tools. These are not tools. These are
40:11 the infrastructure bro. These are the entire framework in itself. Because whenever you need to run Apache Spark, it's not a simple thing. It's actually like in like managing an entire infrastructure. Okay. Apache Spark is there. DBT is there and these are the two very famous frameworks and both go hand in hand. Do not feel like one is the replacement for other. Not at all. Not at all. DBD alone cannot do anything. Nothing. Nothing. So this is data transformation. Let's say you have transformed your data as well. Very good. Then once the data is transformed, then what? Then what? Then comes the real part. Every part is real. But this is the hero part. Data modeling. Once the data is transformed, now we
40:58 need to create the data model. Make sense? Data model. Let's say we just want to create a data model. Now, now you need to know one thing which is very important for you. Now, this is not just a kind of normal data model that you just saw um here in RDBMS. This data model is different and obviously we'll talk about it in detail as well. But for now, you can just imagine that this is a special kind of data model which uses facts and dimensions table. If you are coming from the reporting background, you would have worked with facts and dimension tables in your maybe PowerBI um semantic um data set option as well. But creating facts and dimension table there is not
41:44 equivalent to creating facts and dimension tables here on the server side. That is why whenever you work on the projects we always ask this thing from the owner project owner that hey do you want us to create data models or you want data analyst to build the facts and dimension tables. If the data size is low if the data size is small we simply say you can ask data analyst and they can build facts and dimension tables for you because they can handle that load. But if the data size is big, if the complexity is huge, if they just want to handle concepts like SEDDS and all of these things, then we simply say we will take the ownership, we will build facts and uh dimension tables for you and data analyst can directly use it. So this is a question that who will build it and
42:31 previously this was purely isolated but now because we have analytics engineer role now this data modeling part is the most crucial part that they need to take ownership of and I hope it makes sense. So whatever things we have covered let's say data generation, data extraction, data transformation, data modeling everything is good. Now we simply say data storage. Now we also want to store this data anywhere. And we can create data storage like this. Let's say data storage is everywhere. Why everywhere? Because whenever we just want to extract data, we will simply load this data somewhere.
43:17 We will transform the data. Then we will also stage that data. After data modeling, we will also store this data somewhere. So this is called data storage. Okay, simple. And now at the end we have reporting data reporting which is purely independent of any kind of storage anything. Obviously in the latest features of Microsoft fabric they also work with one lake which is a different story but I'm just talking about a normal data reporting where anyone can use let's say traditional PowerBI or Tableau. So this will be this particular thing. So let's say PowerBI and this will be Tableau. Do I have Tableau? No. So let's say just PowerBI. So this is your entire
44:02 data life cycle that you just need to know that you need to know and I hope it makes sense. Now just look at it. Data generation. Okay. And then after that we have data extraction, data transformation, data modeling and storage everywhere. and then PowerBI. Now, now I want to ask you one simple question and I think now you will be able to answer this. If not, do not worry. I want to ask where where the analytics engineer where does the analytic engineer role fit? Simple
44:52 dust. Now just tell me one thing. Where does the role fit? Where? So earlier earlier this entire thing this entire thing let's say right from till here till here this was purely managed by data engineer. Obviously that is true. That is true. This is still true. And this thing was actually managed by data engineer. I'm data analyst. Right? Let's say this is data engineer. This is data analyst. Perfect. Now data not data analytics engineer. Obvious
45:37 data analytics engineer, analytics engineer. Analytics engineer role starts from in the middle of transformation in the middle because person should know transformation and it goes till here. This is the data analytics engineer. Let me just change the color. Let's say red. This is the analytics engineer. I hope it makes sense and I hope it sets the um your expectation. See if I just zoom it. So now person needs to know how to store the data, transform the data, data modeling for sure. For sure. And do you know what? I do not even need to tell you the importance of data modeling because even if you are a data analyst
46:23 right now, even if you are not a data analytics engineer, you still work a lot with data modeling in PowerBI and not just PowerBI, I would assume that you still know the importance of having a good understanding of facts and dimension tables, surrogate keys and all these things. So this is the thing that you need to know. This is your analytics engineer role which fits in the middle of data engineer and data analyst and you now know from the data life cycle point of view as well. Now your expectation should be crystal clear because I am not here to tell you like hey you just need to know this hey you just you have to just know this tool. Not exactly. I can just give you all of the things. I have to tell you what this domain is about and then you will have a crystal clear understanding like what
47:09 exactly you need to learn where you need to stress more. Maybe your data transformation is already good. You already know these things. You can just stress more on data modeling or vice versa or this thing simple simple makes sense. So this is the complete data life cycle of analytics engineer role. So now I want to talk about a very important topic that any analytics engineer need to know. So now obviously we need to just um talk about the topics as well like what exactly you need to know and how you need to know and now we need to talk about the real topics as well. Obviously this do you know what do you know what see in if you understood this entire data life cycle if you understood I use the word understood if you actually
47:56 understood the meaning of this entire data life cycle you would actually work as a better data professional I am so sure about it because when you know the entire data life cycle you can actually connect the dots but if you are simply working on the reporting side if you do not actually know how this thing these things are happening you cannot feel connected then those things came into the picture like AI is automating the things AI is automating the reports AI can automate the reports but AI cannot automate the entire system AI cannot automate the entire ecosystem AI cannot automate the entire um data life cycle it is next to impossible bro next to impossible
48:41 and it's not just about like being possible or not possible it's out. We still need ownership on these things. We can use AI to boost the productivity but we cannot just replace data professionals. We can reduce the number of professionals. Let's say in an organization 10 people were working now maybe three, two or three or hardly four. We can reduce the numbers but we cannot replace. Reduce. Yes. Replace cannot. Simple. So now you need to be in those top three or top four people so that you have right skill set so that you can be that particular asset which is not replaceable. Just tell me one thing. Let's say you are the owner of the organization and you have 10 people
49:27 working in the data team and you just want to use three or four because now you know that um you have cloud code now you have co-pilot just to do all of the stuff quickly. You have six people who just knows how to build reports. Okay. And who just know how to write SQL queries. And there are three people who know data modeling very well, data transformation very well. DBD, Spark and they understand the data life cycle very well. Forget about AI, forget about big big companies, forget um like stop blaming those big companies firing people. Just tell me about you. Will you keep those six people or will you keep those three people? Just tell me one thing that I I want to hear your thoughts. You will say those three. Why? Why? Because you are the owner, right?
50:14 So, OB, this is like common sense. This is common sense. All about supply and demand. Earlier supply was less. But now they do not need that kind of demand. Why? Because they know earlier 10 people were required to do a task. Now I just need two to three. That's it. That's it. It's all about supply and demand. It was never about you know something and um I do not know this thing. It was never about this thing. It was never about like learning this thing and not learning that thing. It was all about supply and demand and that's it. And it will only about supply and demand. If now the demand is for learning AI, you have to learn AI that then only your demand will be there in the market. I
50:59 hope you get my point. Okay. And now let's talk about a very important concept for you which is the key which is the key of today's data life cycle not life cycle the entire thing that we do it is called um first of all ELTL what is this thing lamba we have heard about ETL we have heard about ELT what is ELTL it is not a kind of term that exist in the books but this is a kind of term that we data engines use on a daily basis what do we mean so earlier when we didn't have much data available on the data generation side on this side and
51:44 you know what I will be referring to this diagram a lot of times now because everything is embedded within this this is the beauty of this so earlier data generation side was less heavy it was like very very weak okay like there was like hardly billions of records and that's it and we didn't have like much data billions like means billions of records for the entire organization and it's not a big number okay so let's say what we were doing we simply had let's say a kind of SQL database okay simple and we have some data and we used to simply extract the data right because this data life cycle needs to be followed right extract the data so I'll simply say I want to extract this data and I want to
52:31 let's say extract it. Simple. So I'll simply say extract. Simple. Very good. Now do you know what? Do you know what? When I extracted this data, I simply put the data nowhere. What do I mean? So I I extracted the data. Okay. And immediately I transform the data as well within the same query. Okay. Okay. So let's say transform and after transforming the data I then loaded it in the data warehouse because earlier we used to work with only warehouses. Okay.
53:18 On-prem data engineering and this is called ETL standard ETL approach. Okay. And I hope you would have heard about this concept. ETL is a like very popular approach, very popular. We sometimes still use this word ETL but this is not something that we do nowadays. So this is called ETL standard approach. This was the heart of on-prem data engineering. Old school data engineering was purely based on this thing. That is why we had tools such as Pentaho data integration. There was one more very popular um oh man this was that was like so popular tool. It has like orang-ish logo. Pentaho data integration. Informatica. Okay. Informatica. So, Informatica and
54:05 Pentaho. These were like very very popular ETL tools that people used to just um integrate with their workflows where you can just literally create these ETL pipelines that you can run recurrently. Okay. And this was a standard approach. This was a standard approach. And this was approach when we didn't have data links when we didn't have those um data warehouses cloud data warehouses simple data SQL. So we used to even load the same data in the database. Yeah. Obviously with different strategy but the storing layer was same storage layer was same database. Oh really? Yeah. So yes we were just building data warehouses in um your MySQL database SQL
54:50 database like this. Okay. So this was a um you can say traditional approach but data started growing. Data started growing. We were not able to literally transform the data with such a volume. And it's not like we were not able to. It was like wastage of resources because we are simply extracting so much of data and then we are not loading it anywhere and we are literally processing all of the data that we do not even want to. Then we have a concept we got a concept called ELT. Okay, ELT or ELTL both are same thing. I will just tell you why and you will also say Anlaba you are right. Okay. So I always
55:36 called as ELTL. So now we have this data obviously but now the data size is like huge. I would say trillions of records will be like too much but let's say 100 billion or let's say 200 billion these these much of records 100 times more. Okay. Now I will yes extract this data for sure. But after extracting the data, I will immediately load this data in a kind of storage layer. And this storage layer is nothing but your data lake. Why? Because data links are cheap. Okay. So I will attach data lake with extract stage. Wow. Then I will transform this data and it will be very
56:22 easy for me. Why? because I just want to transform the data that I want and it is already there in the data lake. I do not need to put pressure on this um poor SQL database. Okay. So this is my transform and this will also have data lake attached. Yes. And finally we will create a kind of um load stage like final load stage you can say load and this will be attached to the data lake as well. Yes. So this becomes my el because I am extracting the data I am loading it immediately. So, E L then I am transforming the data and then I am
57:09 loading the data again. It is not just ELT after T we have to load it again. So that is why it is like ELTL but just for the like you can say common terms because we were using ETL so we simply said ELT just for the easy naming convention that's it but ideally it is ELTL okay makes sense because we need to load the data so this is the same thing that we are doing in the modern data life cycle we have extraction we have storage we have transformation we have storage we have data modding we have storage underneath make sense I hope So, so we cannot simply do the processing directly. Um, you can say while fetching the data, we have to put this data somewhere. There are tons of benefits of it. Why?
57:57 Because this makes sure that you can reuse the data. You can create a staging layer, a kind of persistent staging layer. There are multiple layers of ETL and ELT. Um, the very first is I think staging layer. Okay. And staging layer also has two types persistent and transient. So this extract stage is actually kind of staging layer. Okay. And this has two layers um transient. Transient means your temporary layer where you do not store your data for the longer period. Maybe you will just revoke it in just 24 hours or maybe immediately after loading the data. It depends upon the design. And the second layer is called persistent. Yes, no analytics engineer needs to know
58:43 these things. Okay, because obviously you will say oh we didn't know these things. So this is all about your uh let's use let's say this color perfect persistent and transient simple very good so these are the two things this is the staging layer in the transform layer you can create a staging layer but it doesn't make any sense because you are already creating a staging layer for the raw simple now the beauty of this ETL and ELT is whenever you perform extra ract you simply extract the same data as it is without any kind of transformation. So you can actually compare the data
59:28 between the source and the extract layer because you have literally stored the same data in the data lake. But here you were not able to do this. Obviously obviously make sense. So this is one thing. Then then why did we move to ELL? Why? First reason is obviously data size because now we are just dealing with big data and now we do not have like much resources to work with that big data and now with AI data is even bigger. Now the major reason after data size was compute because data size is big. We need to put more compute to process that data and
1:00:14 not just process that data. We needed a solution where we can efficiently store this data somewhere. When we store this data in the SQL database, it is very expensive. It is very expensive to maintain those databases. Are you getting my point? So here comes the concept of data lake. Now if I would ask you what is a data lake according to you according to you you will simply say data lake um is something where we simply store our data in any file format which is actually true which is actually true in data lake you can literally store
1:00:59 this data in any format but why did we and how did we introduce data lake this particular point of discussion is very important Now let's actually discuss it in detail. So let's try to understand the entire architecture of this. So first of all data lake is not the direct um thing that we got before data lake or I can say the the backbone of data lake is actually blob storage. What is this blob storage? Let me just tell you because if you understand blob storage you can actually understand the data lake because data lake is not actually a kind of component. It's the architecture in itself which is built on top of blob storage. So let's say you have one blob
1:01:44 storage solution. It can be let's say S3 bucket. Let's bring it which is a very popular one or let's say ADLS gen 2. My Azure people will say bro bring ADLS. Okay. So let's say ADLS gen 2. So let's say you have created one ADLS gen 2 instance which is a kind of Azure data lake solution in Azure but forget about data lake I do not know what is this thing but I know there's a solution called ADLS simple okay cool so now let's say you have this particular solution perfect now you want to upload one file on the cloud simple let's say I have a file and let's say I want to upload let's say this file let's say let's say you uploaded this file now do you know What will happen? You see one
1:02:31 file. You see one file. And by the way, that is why we call it as like binary large object. Blob means binary large object. This will be considered as a large object. Okay. But behind the scenes this file will be converted into small small chunks. Wow. Yes. Small small chunks. Let's say one then two then three. And each chunk will be distributed across the disks. Yes. Do I have disks? No. Um let's use let's say any kind of server. Do I have anything? Let's set the disk. So this is let's say disk one. Let's say for example.
1:03:16 So this will be like disk one which will be attached to this particular chunk. And there will be another disk for another chunk. It can be same as well but most probably it will break different. So this way as your manages the blob storage binary large object will be converted into the form of chunks and each chunk will be distributed among different different disk. But anal when I just double click on this file I simply see everything together. I do not see just one chunk or two chunks. That is why I am just saying that this is kind of a solution provided by Azure. That means whenever you open this
1:04:01 particular file, double click on it, you will simply see the entire file because Azure will reassemble all of these chunks that was shared across the disks automatically everything by Azure. Wow. So this was a concept of blob storage which is very similar to block storage. Now what is block storage? So whenever you do the same thing in your local machine happens but in your OS in your operating system let's say you have a file in a folder right double click on it you will see the entire file but you know what the moment you store the file in the folder that file will be converted into small small blocks. Blocks B L O C K blocks. And then each block will be shared across different
1:04:48 regions of your disk the me the uh Rome that you have read only memory um SSD HDD whatever laptop you are using right so each block will be distributed across multiple regions of your just one disk because you just have one disk right and the moment you click on that file it reassembles those blocks and just represent the result and here in the Azure as well they use a same concept they simply distribute the chunks but not on just one disk across multiple disk because they have like literally data centers right and the moment you double click on it they simply see it now this is the backbone of it now they simply said hey we have a lot of data as you can see here so why can't we just create the data in
1:05:35 the form of files so that we can store it here point is right that's why data lake was built on top of blob storage. Now what is the difference between blob storage and data lake? Blob storage is just like storing your blobs, storing your large objects. But when you just want to create data lake for big data analytics or data analytics, you simply need to enable something called as hierarchical naming spaces. What does it mean? For example, let's say you want to store your data in the dedicated folders, right? Let's say you are just creating a orders table. So what you will do? You will simply say hey I want to create one folder and this folder should be named as let's say orders why because I'm creating an orders table. So I'll simply
1:06:21 create this folder and I'll simply call as orders. So all of the files related to orders table will be stored inside this particular folder. That's it. So this will be considered as my orders table. So this was the architecture of the data lake and this is still the architecture. This is still the architecture but we have actually entered into even more advanced era because after data lake we found amazing amazing you can say progress or you can say amazing performance in terms of storage. Why storage means um cost from the cost perspective. Business leaders were happy because there was not much cost associated with the solution. But the performance, the
1:07:10 quality of reports were degrading. Why? Because everyone knows that SQL actually provides amazing reporting capabilities because it can run SQL structured query language actually gives us that particular thing. And it's not just about performance. It's about much more than that. For example, let's say I want to perform asset transactions or any kind of asset property. Let's say atomicity any anything I can do it in SQL database and I hope that you know asset transactional properties right atomicity consistency isolation durability right I hope so. So if I want to perform those things I can do it here but I cannot perform asset properties here obviously because these
1:07:55 are the files. So when we started seeing these errors then we got introduced to the concept called data lakehouse which is the current most popular thing and yes now you analytics engineer needs to maintain plus build this as well I'm so sure. So this is called data lakehouse. Now what is this thing data lakehouse? So now we know that our data is stored in the form of let's say file like in the in the form of data lake in the data lake simple very good now I am happy with this particular thing from the cost perspective very good but I am not happy with the
1:08:42 performance with the quality so what I will do I will simply say I will combine the data lake for cost and I will combine the data warehouse for the quality punch lamba you are cutting the cost or you are increasing the cost because data lake is already here now you're adding data warehouse that is the power of lakehouse we add data warehouse for sure but you know what in a smarter way what does it mean what does it mean data lake is there for sure but this data warehouse is not your this traditional SQL database no not at All not at all. Then what we introduce
1:09:28 something called as open table format. Okay, open table format. What is that? We will just talk about it. Don't worry. For now, you can consider it as alternative to data warehouse. But it does everything that data warehouse needs to perform. Simple. Very good. Now what I will do? I will say I have the data, right? So what I can do? I can convert this particular data into lakehouse. How? First of all, I need to consider or basically write my data in a specific format called park or my file should be in a specific format called park. So I'll simply say park. So let's say this is my file. Simple.
1:10:13 Okay. Perfect. So this is my file. Now along with this file, we need something else because this is still data lake. This is not a kind of um warehouse or anything. This is still like data lake. But now what you need to do I will attach a kind of database this database. This is a very special database and this is not your SQL database. So what is that? This is called transaction log. Okay. Let me just draw it. So this is called transaction log. What is so special here? So let me just tell you. So let's say I have orders table. Okay, I have saved this data in the park format. That is also clear what is parquet. Um park
1:11:00 is a special file format that we use for big data analytics and is it is basically a columnar file format that stores data column by column. For example, let's say you have some data in
…
