Building ETL Pipelines in Databricks | Data Engineering in Databricks

Key Takeaways

What's going on everybody? Welcome back to another video. Today, we're going to be building an ETL pipeline in Databricks. >> [music] >> Now, in the last lesson, we worked on data ingestion into Databricks. So, we were able to connect to just a local file, just kind of reading that file in. And then we were also able to connect to an AWS S3 bucket. Now that we have that data pulled in and we have it actually sitting in our schema, we need to clean this data up a little bit and so we're going to need to transform this data, which is part of the extract, transform, and load within an ETL process. So, we extracted and we ingested that data and now we need to clean up our data cuz it is messy. So, that's the transformation piece of the ETL process. Once we have this done, we can put it into an ETL pipeline and then it sits there and it does a lot of the heavy lifting for us and we'll talk about that in this lesson. Now, really quickly, before we jump into things, I want to talk about this bronze, silver, and gold medallion architecture that is very popular within Databricks. Now, we actually already covered this bronze level, which is just our raw data. We ingested our data from our S3 bucket and it's just sitting there in this raw format. This is data that we are just never going to touch. What we're going to do is we're going to create transformations on that data and then we're going to put it into a different table or even a different schema or catalog. When it gets to that location and the data is actually changed, that's going to be in our silver. So, this silver layer or architecture is basically just once you clean it up and you have it in a lot better state where there aren't a lot duplicates, there aren't a lot of issues with the data, that's where it's going to sit where you can then transform it into your gold architecture or layer. Gold is just production ready. You already start using this data. You're going to put it into dashboards. You're going to put it into reports. You're going to put it into your apps. Whatever you're using that data for. Back when I was just using Microsoft SQL Server or any other tool, we would call this raw, staging, and production. The raw is bronze, the staging is silver, and of course the production is gold where we actually use that data. So, what we're going to do in this lesson is we're going to actually use this. We already have our bronze. We need to transform my data into silver and then find a business use case to create the gold table. So, now that we have this background information, let's go on to our screen and start building this out. So, in the last lesson, we brought in this dirty data _s3 and this is what our data looks like. We have this user ID, first name, last name, their email, their sign-up, the country, and referral source. Now, this is just our raw data. This is our bronze layer right here. Now, in this video, we're not going to do it exactly how I would do it in the real world. I'm just going to kind of keep it all in one place for us. So, within this video one or within, you know, whatever schema you created, we're going to keep our silver and our gold tables all within this one schema. That is not typically how it is done. Here's what you typically would do. You're going to have a data engineering _bronze catalog, then you have a data engineering _silver catalog, then you have a data engineering _gold catalog, and all these catalogs would hold the different levels. And so, you're not just usually working with one small project like we are in, you know, this lesson, but typically you're working with lots of different projects and you're working with lots of different customers and you want those to be separated out so you don't kind of get them confused and you don't know which data you're supposed to be hitting off of. That typically is how it's done in a real workplace environment. We're just going to do it all right now within this video one schema. So, this is our bronze layer right here. This is the file that we're going to be using. Now, in order to transform our data to get it to silver, here's what we need to do. Let's come up to our new, let's go down to our notebook. So, we have this notebook right here. Let's call this bronze to silver transformation. There we go. And I'm going to give you a little spoiler here. Uh we're going to create another notebook and we're going to call this one silver to gold. And so, we want to uh separate these out. You don't have to separate these out, but for the sake of what we're going to be doing in this lesson, I do want to show you how kind of you set things up and you actually, you know, organize things within an ETL pipeline. And then in the next lesson, when we look at jobs and orchestration and automation, this will also come into play and I'll talk all about that. So, let's create these two different things. Now, I can write all this out because, you know, I know this data set. It's pretty simple and I already know what's wrong with it. So, I can go in and I can just fix it. I can write this out manually, but uh you know, let's get a little creative. Let's take a look at how we can use AI in order to see if it can do most of the heavy lifting for us. Now, in our sample data, I'm going to give you two things that need to be changed cuz there's only really two big issues. The first thing is in this date column, we actually have it as a string and that's a problem, right? We need it to be a date column and the issue is is this right here. We have one date field that is 2.29.24 instead of the uh forward slashes. That's an issue. We also have a usr_1009 as a user ID and if we go down, we have a 1009 right over here. So, we have a duplicate user ID and in a primary key like a user ID typically would be, that's an issue. So, we have two issues we need to solve. I am going to try to get the AI, which is the Agentic AI, which is uh this one up here that we're going to be using, to try to write this out and get it right. So, let's come back to our workspace. Let's come to our bronze to silver transformation and let's bring up our AI assistant. Now, I'm going to describe what I want it to do and then we're going to see if it's able to write it out. I myself could write it out very accurately in probably maybe three to four minutes, but this is not a coding tutorial. I want to show you guys how ETL pipelines work in Databricks, not how to necessarily transform the data. So, let's try this out. So, I'm going to say, "Take my data set and" I can pull it up over here just so we can see it. I'm going to go to data engineering, video one just so I can see the data. "Take my data set in the data engineering catalog in video one schema called dirty_data_s3." Now, I like to be super explicit cuz I don't want there to be confusion, especially as you have like hundreds of tables, you don't want it to read in the wrong tables. I like to be super explicit. We're going to ask it. I'm going to say, "There is something wrong in the date column making it a string. I want you to identify and fix that issue. There's also duplicates in the data set. I want you to remove duplicates on the ID." Now, I'm being a slightly vague, right? I'm not telling it exactly what it needs to do, but I'm going to let this run and we're going to see if it's able to identify the issues and write the code. I do want it in Python. I think that's just the easiest way to transform this data. And so, I'm going to say, "Use Python and Pandas." And let's give it a go. So, let's let this thing for just a little bit and we'll see what it comes up with. So, that took about a minute or so. It did a lot of different things and now it wants to actually run this code. Now, before we do that, you can have it ask every time or you can just allow it to run the code after it's done. I'm going to ask it to ask every time just because uh you know, I want to make sure. Now, it does have a lot of printing just to show the work that it's doing. I myself don't want this in my output, so I will ask it to change that in just a second, but it does identify that there's a period. It replaced it with a forward slash. It converts it to two date time, which looks correct. And it also formats it for us. Uh then comes down here and it's doing just a ton of kind of pretty unnecessary things before it gets to this df.drop_duplicates on the user ID and we're keeping the first one, which is perfectly fine. And then lastly, it's doing a lot of verification. I basically don't want 80% of this code. I just want the simple stuff. So, all I'm going to say is, "I like the transformations you've done, but get rid of all the print statements." All right, looks like it's done and as you can see, it cleaned up the code immensely. Uh this is uh really looking good. I'm going to go ahead and I'm going to accept all. You can see the diffs down here, by the way, for all the code that it's writing or taking away. We're going to accept all and we are going to run this ourselves. We can I'll just click run all here. Uh but we're going to run this ourselves and then we'll verify and make sure that this actually looks good. So, let's open this up. Let's come down here and let's just do a display. We'll do dataframe_clean, which is what it named it. So, now let's look at this new dataframe that it has created. That should be a lot cleaner than before. So, now if we come down here, we have our 1009. Let's go see if our 1009 was removed. It was. And let's come over here to our sign-up date and it looks like that now is converted to a timestamp, which is perfectly fine. Uh we could also do it as just a date column, but honestly, it really doesn't matter. Uh this is a great uh a great change and it cleans it up immensely. So, now it's all standardized. It's actually in a date column or a timestamp column. That works great. Now, all we need to do as the last part of this process is we have to write this table to a new table and that's going to be our silver table. So, I'm going to come down here. I'm going to say and I could put it as the genie code or I can come over here. I tend to like using this side a lot more. I don't know why, but I'm going to say, "Uh write this cleaned table to a new table in the same schema and call it s3_cleaned_silver." And so, let's go ahead and let that run and it should take just a second and we'll have that code for us. Let's go down here really quick. We have this. This looks great. I'm going to allow this to run for us. So, it's going to run this code. Now, it is giving us this warning, and this is a very fair warning. We're using overwrite right here, and basically what we're doing is every time we run this, we're overriding the previous data that's in that table. For now, I'm just going to use that cuz it's not a huge deal. You know, as you start getting more sophisticated with your data pipelines, you are going to want to think about things like adding data to your existing data instead of overriding. But, you know, that can get a little bit more advanced depending on your data and your data need. Now, it's going to run this, and I'm going to accept all, and then let's come right over here to our data engineering video one. And now we have this S3 cleaned silver. So, our bronze to silver transformation is complete. This is all we needed to do in order to transform our data. And now we have our raw data, and let's come actually back to our catalog, and we'll just take a look at this. We can get rid of our Genie code real quick. So, we're going to come over here. So, our raw data is still going to be raw. Let's go ahead and run this. This is our bronze level, right? We still have the raw data. We still have the duplicates. But, when we come over to our silver, this is now going to be our cleaned level. So, now that we have all of our transformations completed, we've taken it from bronze to silver, now we want to create our silver to gold transformations as well. Let's come back to our workspace, and we'll come down here to the silver to gold, which is going to still be right up here for us. Now, let's give it a use case, right? We could use this table just as it raw, and we could hit off of it, and we could build dashboards and all sorts of things. Sometimes you want to track certain KPIs or certain things that you can't just get from the raw data. So, I'm just going to give it a simple use case, let it write it out, and we'll create our silver to gold transformation. So, let's come right down here. I'm going to say that I want to know the best day of the week that people are clicking on certain ads, and we're going to see what it creates for us. So, uh I want to create a new table called insights _gold, and I want it to show me the best days of the week and what ads people clicked on the most. And let's run this and just see what it does. All right. So, it went and did a lot of work for us. It did not take long. This is maybe 15 seconds. It's doing some group buys on uh some different columns, and it's getting some counts for us on different sign-ups and referral sources. Let's go ahead and allow it to run this, and let's see what it does. Now, it's giving us a few things as far as outputs. One, this first one is extracting the day of the week and analyzing sign-up patterns. So, Thursday, Tuesday, Monday, and it's giving us kind of the day of the week when we had the most sign-ups. And then if we come down here, we also have another one where we're getting the referral source, basically social media, organic referral, Google Ads, or partner, the total clicks, and the countries reached. And if we go down here, we have this last table, but it hasn't been run yet cuz this is actually creating our table. And so, this one should be really interesting, but let's actually stop it really quick, and then I'm going to accept and then run this as well. I just want to see what this one is. And so, and then we have uh day name, the referral source, sign-ups, and unique countries. I think this is the one that I, you know, was kind of hoping for when I asked it to run it for us, but it gave us different options, which I like. Now, all we have to do is we have to get rid of this, and we're going to let that run. And so, let's accept that, and let's run this as well. Now, this display is literally just displaying uh right up here, so we aren't actually reading this in, but uh let's come back into our catalog, and let's go see if we have that gold table now. So, now we have our video, we have our insights gold, and let's just look at our sample data, and there we go. And so, this would be like our gold table that we can now use. We now have some insights into our data. Now, all we've done so far, if we come back in here, all we've done so far is we've just written code. We haven't necessarily created any type of pipeline. And so, now this is the part of the video where we're going to get into building an actual pipeline, and I did it this way very specifically. This is how I tend to write my code. I come into a notebook, I write out my code, and then I'm like, "Okay, this is looking good. Let me now go create my pipeline." So, let's come right over here. We're going to come down to our runs, and there's this thing right here that says ETL pipeline. Now, let's get rid of this. We also have this right here, which is kind of what we're going to cover a lot in the next lesson, but I want to talk you through really quickly while we're here the difference. Now, we created two separate notebooks, one from bronze to silver and one from silver to gold. Now, sometimes with simpler pipelines like the one we just created, it could be totally fine to just come in here, create a job, and say, "Do this one and then do this one." Right? That's all we're doing. We can put it on a schedule, or we can create uh you know, a different trigger for that, and we'll look at that in the next lesson. But, if you have a more complex pipeline, you're typically going to want to use this right here, which is our ETL pipeline. Let's go ahead and click in on this ETL pipeline, and let's come down here to start with an empty file. Now, you can start with sample code in SQL, sample code in Python, or if you have ones that you've already done, you can do that. We don't have anything, and I don't really want to kind of explain all of the sample code that they're going to be creating. Let's just start with an empty file. Now, we need to specify the language that we're using, and this is very important because once you create it, that's kind of the one that you're going to stick with. We're going to use Python, and it's just asking for a folder path. And so, we'll keep that, and we'll say, "Yes." And now what we have looks very similar, right? We have these uh kind of some notebooks on the left, and we can write our code right here. It looks very similar. But, there is a big difference between running something in a notebook like we were in our workspace before and running something in an ETL pipeline. When you're just running your code, it's running the code as is. It's pretty simple. And if you did what we said earlier, which is you literally just take that notebook, you put it into a job, and you say, "Run this and then run this." It's literally just going to take your code and run it. The issue with that though is it's not going to have any built-in data quality checks. We're going to have to manage basically all of the logic ourselves, and it's not going to handle any lineage tracking or dependencies within your code. Now, this is where ETL pipelines come into play. An ETL pipeline is going to have things built into it like automatic incremental processing, built-in data quality checks, failure recovery, things like that that are extremely useful when you have really complex pipelines, which we aren't doing in this lesson, of course. It's very simple, but you have to think, you know, if you're creating a real ETL pipeline with a lot of dependencies, a lot of complexities to it, you absolutely going to want to come in here. Now, when we write this out, we can't just write it as our regular code. And we can actually do that. Let's come back, and let's go to all of our files. Let's go to bronze to silver transformation. We're going to move this just so we can visually see it. We're going to put this in our transformations, and then we're also going to take our silver to gold, and we're going to move this to our transformations as well. So, we're going to put this all in one place. And so, now we have the silver to gold, and we have the bronze. We don't actually need this uh file anymore. So, we could just get rid of this. Now, your UI might look slightly different. That's just because Databricks is always updating things, but you should still be able to follow along. But, let's go ahead, and this is our code. It's exactly how we wrote it before. Let's try to run this pipeline. It's going to try to run this, and it should try to run that, too. Let's just go ahead and run it and see what happens. All right. So, we got this error down here that says pipelines are expected to have at least one table defined, but no tables were found in your pipeline, which may seem very counterintuitive because, you know, like we've created different data frames. We've been working with tables. So, it should understand what it's doing. Now, it is actually rewriting the code as we go. I think it's identified uh the issue already, and let me explain this even though uh it's starting to write it out already for us, which is awesome. Thank you, Genie code. But, here's what's happening. When you're running code just in a notebook, it's just going line by line and running the code. But, within this ETL process, and just ignore that for a second cuz I'm just going to let it run. Within this ETL process, what it's using is something called an STP, which is a Spark declarative pipeline. This is just a different construct and a different framework within the ETL pipeline. And so, what it actually needs is something called a materialized view. It needs to kind of look at what the output is going to be or supposed to be. It's not just blindly running your code for you. It's doing a lot of heavy lifting with data quality checks and all these different things. Now, it just went through uh and it fixed it for us. It is basically the same code, and let's come up, but it's creating these materialized views. So, we have DP.materialized_view, and it's kind of naming it and giving a little comment on what it is. It's doing the work for us, and then it's creating another materialized view where we use this insights gold, and it's actually putting it all into one, which is fine if that's what we want to do with uh this pipeline. But, let's go ahead and accept this, and let's try running this pipeline again. So, now we have a little bit more information. We can come right down here, and we can see it was trying to create these different materialized views, and it was working. And so, now this whole thing has run successfully. Let's actually rename this really quick. We're going to do bronze uh I need to spell bronze right. Bronze to silver to gold ETL pipeline, and let's save it like that. And we come back over here, we can go to our jobs and pipelines. We now have this pipeline right here. We, of course, uh it failed, but now it's running, and it's working successfully. But, now we have this pipeline that we have stored, and we can actually start using this in, you know, automations where we can orchestrate these pipelines right here. It says orchestrate notebooks, jobs, queries, and more. And there is a lot to that, and that's what we're covering in the next lesson. But, if we open this up, we can actually see what's happening under the hood. We can see these are connected. We're doing this one and then this one, and we can see how it's running. And so, there's a lot of things that this ETL pipeline is going to handle for us that we don't even have to worry about. That really is one of the biggest advantages of using an ETL pipeline instead of just running your notebooks. Although, again, there are some advantages to just running your notebooks as is if it's a little bit of a simpler pipeline. I really hope you're able to follow along with this lesson because this is really cool stuff. You can also just come into here and we can create an ETL pipeline and you can create a pipeline with AI. So, we can literally just come here and we can type in exactly what we want our code to look like and do within our data and it can build that out. Instead of starting with a notebook and then creating our ETL pipeline, you can just come right in here and start doing that process here. I will say though my personal workflow because I'm usually not doing super complex pipelines that are involving, you know, ton of different dependency chains and all these different things is I tend to like writing my code in notebooks. That's just what I'm used to. Uh but, there are going to be lots of use cases where you're going to need to come in here and you can just start here instead of starting with a notebook. I hope that lesson was super helpful and you understand how to use ETL pipelines quite a bit better now. In the next lesson, we're going to be taking a look at automating this entire process. So, we're going to be looking at jobs and orchestration and triggers and automations and all these really cool stuff. Thank you guys so much for watching. I really appreciate it. If you like this video, be sure to like and subscribe and I'll see you in the next lesson.