- 6 days ago
Databricks ETL pipelines take center stage in this data engineering tutorial focused on building and managing end-to-end workflows. The topic covers how ETL processes fit into modern analytics environments, with attention to data ingestion, transformation, and preparation inside the Databricks platform. Designed for viewers interested in data engineering, cloud data workflows, and lakehouse-style processing, the content supports a practical understanding of how pipelines are structured and maintained. The tutorial format makes it useful for learners who want a concise introduction to Databricks and the role it plays in ETL development.
Category
📚
LearningTranscript
00:00:00What's going on, everybody?
00:00:01Welcome back to another video.
00:00:02Today, we're gonna build ETL pipelines
00:00:04in Databricks in under one hour.
00:00:12Now, in this video,
00:00:13we're gonna cover several different things.
00:00:15First, we're gonna work on data ingestion,
00:00:17just getting data in.
00:00:18Second, we're gonna actually build out our ETL pipelines.
00:00:21And then third, we're gonna work on data orchestration
00:00:23or creating jobs in Databricks.
00:00:25At the very end, we're gonna have a full end-to-end project
00:00:28where we pull data in from a folder in an AWS S3 bucket,
00:00:32and then we automate it with an ETL pipeline
00:00:34to clean that data.
00:00:35Now, this video is made from several shorter videos
00:00:38that we've done on Databricks in previous lessons,
00:00:40but we're putting them all into one long video
00:00:43so you can watch it all at one time.
00:00:45Let's not waste any more time.
00:00:46Let's jump into the first part, which is data ingestion.
00:00:49Before we jump into this data engineering series
00:00:51and start doing all the things,
00:00:53I wanna slow down for just a second
00:00:54to take a look at what ELT is in Databricks.
00:00:57This is the process that we're gonna be walking through
00:00:59for this entire series.
00:01:01We're extracting data or getting our data into Databricks.
00:01:04That involves loading the data into different schemas
00:01:06and having that data available,
00:01:08and then we transform that data.
00:01:10Now, ELT might sound odd
00:01:12because most people are used to ETL,
00:01:14where you extract data, you transform it,
00:01:16and then you load it into the database.
00:01:18With a lot of modern data workflows,
00:01:20it doesn't actually make much sense
00:01:21to transform your data before,
00:01:23because compute is quite cheap these days.
00:01:26And so you can just load your data into Databricks
00:01:28and then transform it after.
00:01:30Now, there's something called the Medallion architecture.
00:01:31We're gonna take a look more at that in the next lesson
00:01:34when we take a look at bronze, silver, and gold architecture.
00:01:37Now, this is a really great way to kind of stage your data,
00:01:39and it's been like this for a long time,
00:01:41even before Databricks.
00:01:43But we'll be going into why and how we actually do that
00:01:46within Databricks.
00:01:47Our data, when we actually get it into Databricks,
00:01:49is being stored in a Delta table.
00:01:51It's kind of like a Delta file type,
00:01:52which is basically just a parquet file that has this log system
00:01:56where you can kind of revert back
00:01:57and see previous changes to the actual document.
00:01:59And so we store our data in these Delta tables,
00:02:02and then we can do all of our transformations on it
00:02:04within a notebook or within SQL queries.
00:02:06Now that we've got that out of the way,
00:02:08let's actually jump into Databricks
00:02:09and see how we can do this.
00:02:11All right, here we are on Databricks,
00:02:12and we're gonna be doing two things.
00:02:14One, we're just gonna upload a CSV file.
00:02:16It's probably the simplest way to get data into Databricks.
00:02:19But then we're also gonna connect to an S3 bucket.
00:02:21And so I'm gonna show you how you can do that really easily.
00:02:24And we're gonna get all of our data into Databricks.
00:02:27Now, we are just working with sample data for this lesson,
00:02:30but at the last one, when we start doing our full ETL process
00:02:33and automating this entire thing,
00:02:35then we'll be using real data.
00:02:37And so it'll be a lot more, a little bit more complex.
00:02:40There's a few ways to ingest data.
00:02:42One, you can just click on this, bring in data,
00:02:44and it's gonna take you right down here,
00:02:46but we can also just go to our data ingestion.
00:02:48And so when we click on this,
00:02:50we're gonna upload files to a volume,
00:02:53or we're gonna create and modify a table.
00:02:56Now, these are two separate things,
00:02:57and these are important things to understand.
00:02:59Let's actually come over here to catalog for a second.
00:03:01And what we're gonna do is we're gonna come over here,
00:03:03and we're gonna create a new catalog.
00:03:05And we're just gonna call this one our data engineering.
00:03:09That's all we're gonna call it.
00:03:10I was gonna keep going,
00:03:12but we'll call it the data engineering one.
00:03:13And let's go ahead and view this catalog.
00:03:16Now, when we create a schema,
00:03:19we're gonna say this is a video one.
00:03:22Let's go ahead and create this.
00:03:25We have within our data engineering,
00:03:27we have our default,
00:03:28and then we have this information schema,
00:03:30but we also have this video one.
00:03:32Now, we don't have any data in this schema,
00:03:35but what we can do is we can create different ways
00:03:38to store our data.
00:03:39We can store it in a volume,
00:03:41or we can store it in a table.
00:03:43Now, in a previous series,
00:03:44I kind of dove into these
00:03:45and how you can store your data,
00:03:46as well as how to access the data once you put it in.
00:03:49We're gonna be putting all of our data into tables,
00:03:52so I'm just gonna set up one table.
00:03:54Now that we're here, though,
00:03:55we can do the same thing that we would do
00:03:57if we came over to our data ingestion,
00:03:59which is basically just drop a file in here
00:04:02like you would on any platform.
00:04:04Let's just go over to the data ingestion
00:04:05just so we get the full experience.
00:04:08We're going to create or modify a table.
00:04:10We're gonna select our CSV file,
00:04:12so it's just our users underscore dirty,
00:04:14and we're gonna go ahead and upload this.
00:04:16So now we have this preview of our data,
00:04:18and we're gonna specify what we wanna do with it.
00:04:20We could create a table,
00:04:21we could overwrite an existing table,
00:04:23and we want to put this in our data engineering video one,
00:04:26so if it doesn't automatically populate,
00:04:28you can always just specify where you wanna place it,
00:04:31and we're gonna call this underscore CSV,
00:04:33because we're gonna be bringing in the same file
00:04:35from an S3 bucket,
00:04:37so I just wanna specify where we got this data.
00:04:39Let's come down here, and we're gonna create our table.
00:04:43So now we have our data sitting in our video one schema,
00:04:47so this is our users underscore dirty underscore CSV.
00:04:50This is the simplest way to get data into Databricks,
00:04:53but I also have this exact same data
00:04:56sitting right over here in an S3 bucket,
00:04:59and I wanna use it.
00:05:00I want to connect to this data.
00:05:02I want to pull it in automatically,
00:05:04and that's gonna really help us later on down the line
00:05:06when we start automating this whole process,
00:05:08because we're gonna create a connection to this data source,
00:05:11so we can automatically pull this data in,
00:05:13and that's a big part of just data engineering in general,
00:05:17which is creating systems that can automatically ingest,
00:05:20transform, and load your data.
00:05:22So what we're gonna do is we're gonna come right over here.
00:05:24Now, I just wanna show you this.
00:05:26I'm gonna have a link down below,
00:05:27so that you can see this as well,
00:05:29but this is basically just how you're gonna create
00:05:32the connection, and I'm gonna show it to you in a second.
00:05:33It is very, very simple.
00:05:35So let's come right over here,
00:05:37and what we're gonna do is we're gonna come down
00:05:39to data ingestion.
00:05:40Now, we want to go to the Databricks connectors,
00:05:43and we wanna go to our Amazon S3 bucket right here,
00:05:47and we need to create an external location.
00:05:50So we're basically connecting our Databricks account
00:05:52to our Amazon S3 bucket,
00:05:55and then we can bring that data in very easily.
00:05:58What we're gonna be using is this AWS Quickstart.
00:06:00Let's go ahead and select next.
00:06:02We need to put in our bucket name.
00:06:04So I'm gonna come over here.
00:06:06Let's click in here.
00:06:07We can actually get it right here.
00:06:09There's other places to get it,
00:06:11but I'm just going to copy from here.
00:06:14We're gonna go back to our Catalog Explorer,
00:06:17and there's our bucket name.
00:06:19So now, what we're gonna do
00:06:21is we're gonna generate this new token,
00:06:22and we're going to copy this.
00:06:24Now, we're gonna come over here to launch in Quickstart,
00:06:27and all we have to do is it's gonna connect to our accounts.
00:06:30That makes it pretty easy if you're already logged in.
00:06:32Then we're gonna come down here,
00:06:34and we're gonna say I acknowledge,
00:06:37and we're gonna say Create Stack.
00:06:38It's gonna come right here,
00:06:40and it's gonna say Create in Progress.
00:06:41It's just gonna validate it for a second.
00:06:43I had already done this before
00:06:45when I was making this video earlier,
00:06:47just to confirm that everything was working smoothly.
00:06:49And what it's gonna do
00:06:50is it's just gonna say Create Complete,
00:06:52and then you're gonna be good to go.
00:06:54All right, so that took about two minutes,
00:06:56and it says it is complete.
00:06:58So all we're gonna do is come back here,
00:07:00and we're going to refresh this page.
00:07:02So I'm gonna go ahead and refresh.
00:07:05And now that that connection is active,
00:07:07we now have access to our users underscore dirty.
00:07:11Let's come under here.
00:07:12We're gonna click on this,
00:07:13and we're gonna go to Preview Table.
00:07:15So now we get this preview of the exact same data set.
00:07:18There's nothing changed.
00:07:18I'm not trying to trick you.
00:07:19All we have to do is we're gonna come up here
00:07:21to Data Engineering, and we're gonna go to Video 1,
00:07:24and then we need to name this.
00:07:26So I'm gonna call this one Dirty underscore Data underscore S3.
00:07:33So I'm just naming it this purely
00:07:35so that we know which one came from which.
00:07:37Let's come down here to Create Table.
00:07:39And now we can see over here,
00:07:41we have our Dirty Data underscore S3,
00:07:44and our Users underscore Dirty underscore CSV.
00:07:46I named it completely wrong.
00:07:47But these are the exact same data sets,
00:07:50and now we have them in from two separate locations.
00:07:53Now this is really important,
00:07:55especially as we start automating a lot of this.
00:07:57If you have data that's sitting in an S3 bucket,
00:07:59and you have other systems that then upload it
00:08:02into that bucket,
00:08:03we're gonna be able to ingest that data automatically,
00:08:05whether it's updated or if it's a new file,
00:08:07and we'll set all sorts of triggers and schedules,
00:08:10and all sorts of really cool things in later lessons.
00:08:13So that's how we ingest data within Databricks.
00:08:15In our next lesson,
00:08:16we're gonna be transforming data
00:08:18within an actual data pipeline.
00:08:19So we're gonna have the entire ingestion process,
00:08:21as well as the transformation process,
00:08:24all in one place.
00:08:25Now in the last lesson,
00:08:26we worked on data ingestion into Databricks.
00:08:28So we were able to connect to just a local file,
00:08:31just kind of reading that file in.
00:08:32And then we were also able to connect to an AWS S3 bucket.
00:08:36Now that we have that data pulled in,
00:08:37and we have it actually sitting in our schema,
00:08:39we need to clean this data up a little bit.
00:08:41And so we're gonna need to transform this data,
00:08:43which is part of the extract, transform, and load
00:08:46within an ETL process.
00:08:47So we extracted and we ingested that data,
00:08:50and now we need to clean up our data,
00:08:51because it is messy.
00:08:52So that's the transformation piece of the ETL process.
00:08:56Once we have this done,
00:08:56we can put it into an ETL pipeline,
00:08:58and then it sits there,
00:08:59and it does a lot of the heavy lifting for us,
00:09:01and we'll talk about that in this lesson.
00:09:03Now really quickly, before we jump into things,
00:09:05I wanna talk about this bronze, silver,
00:09:07and gold medallion architecture
00:09:08that is very popular within Databricks.
00:09:11Now we've actually already covered this bronze level,
00:09:14which is just our raw data.
00:09:15We ingested our data from our S3 bucket,
00:09:18and it's just sitting there in this raw format.
00:09:21This is data that we are just never going to touch.
00:09:23What we're gonna do is we're gonna create transformations
00:09:25on that data,
00:09:26and then we're gonna put it into a different table,
00:09:28or even a different schema or catalog.
00:09:30When it gets to that location,
00:09:32and the data's actually changed,
00:09:33that's gonna be in our silver.
00:09:35So this silver layer or architecture
00:09:37is basically just once you clean it up,
00:09:39and you have it in a lot better state,
00:09:41where there aren't a lot of duplicates,
00:09:43there aren't a lot of issues with the data,
00:09:44that's where it's gonna sit,
00:09:46where you can then transform it
00:09:47into your gold architecture or layer.
00:09:49Gold is just production ready.
00:09:51You are ready to start using this data,
00:09:52you're gonna put it into dashboards,
00:09:54you're gonna put it into reports,
00:09:55you're gonna put it into your apps,
00:09:57whatever you're using that data for.
00:09:59Back when I was just using Microsoft SQL Server,
00:10:01or any other tool,
00:10:02we would call this raw staging and production.
00:10:05The raw is bronze, the staging is silver,
00:10:08and of course the production is gold,
00:10:10where we actually use that data.
00:10:12So what we're gonna do in this lesson,
00:10:13is we're gonna actually use this.
00:10:14We already have our bronze,
00:10:15we need to transform my data into silver,
00:10:17and then find a business use case
00:10:19to create the gold table.
00:10:21So now that we have this background information,
00:10:22let's go onto our screen and start building this out.
00:10:25So in the last lesson,
00:10:26we brought in this dirty data underscore S3,
00:10:29and this is what our data looks like.
00:10:30We have this user ID, first name, last name,
00:10:33their email, their signup, the country,
00:10:36and referral source.
00:10:37Now this is just our raw data.
00:10:40This is our bronze layer right here.
00:10:43Now in this video,
00:10:44we're not gonna do it exactly how I would do it
00:10:46in the real world.
00:10:47I'm just gonna kinda keep it all in one place for us.
00:10:50So within this video one,
00:10:52or within whatever schema you created,
00:10:54we're gonna keep our silver and our gold tables
00:10:57all within this one schema.
00:11:00That is not typically how it is done.
00:11:02Here's what you typically would do.
00:11:03You're gonna have a data engineering underscore bronze catalog,
00:11:06then you have a data engineering underscore silver catalog,
00:11:10then you have a data engineering underscore gold catalog,
00:11:12and all these catalogs would hold the different levels.
00:11:16And so you're not just usually working
00:11:18with one small project like we are in this lesson,
00:11:22but typically you're working with lots of different projects
00:11:24and you're working with lots of different customers
00:11:25and you want those to be separated out
00:11:27so you don't kind of get them confused
00:11:29and you don't know which data
00:11:30you're supposed to be hitting off of.
00:11:32That typically is how it's done
00:11:33in a real workplace environment.
00:11:35We're just gonna do it all right now
00:11:37within this video one schema.
00:11:39So this is our bronze layer right here.
00:11:42This is the file that we are going to be using.
00:11:44Now in order to transform our data to get it to silver,
00:11:48here's what we need to do.
00:11:49Let's come up to our new, let's go down to our notebook.
00:11:53So we have this notebook right here.
00:11:55Let's call this bronze to silver transformation.
00:12:00There we go.
00:12:00And I'm gonna give you a little spoiler here.
00:12:03We're gonna create another notebook
00:12:05and we're gonna call this one silver to gold.
00:12:08And so we want to separate these out.
00:12:11You don't have to separate these out,
00:12:13but for the sake of what we're gonna be doing
00:12:15in this lesson, I do want to show you
00:12:17how kind of you set things up
00:12:19and you actually organize things within an ETL pipeline.
00:12:22And then in the next lesson,
00:12:23when we look at jobs and orchestration and automation,
00:12:26this will also come into play
00:12:28and I'll talk all about that.
00:12:29So let's create these two different things.
00:12:32Now, I can write all this out because, you know,
00:12:35I know this data set, it's pretty simple,
00:12:37and I already know what's wrong with it.
00:12:39So I can go in and I can just fix it.
00:12:41I can write this out manually,
00:12:42but, you know, let's get a little creative.
00:12:46Let's take a look at how we can use AI
00:12:48in order to see if it can do most of the heavy lifting for us.
00:12:52Now, in our sample data,
00:12:53I'm gonna give you two things that need to be changed,
00:12:55because there's only really two big issues.
00:12:57The first thing is in this date column,
00:12:59we actually have it as a string and that's a problem, right?
00:13:02We need it to be a date column.
00:13:04And the issue is, is this right here.
00:13:06We have one date field that is 2.29.24
00:13:10instead of the forward slashes, that's an issue.
00:13:13We also have a USR underscore 1009 as a user ID.
00:13:18And if we go down, we have a 1009 right over here.
00:13:22So we have a duplicate user ID and in a primary key,
00:13:27like a user ID typically would be, that's an issue.
00:13:30So we have two issues we need to solve.
00:13:31I am going to try to get the AI, which is the agentic AI,
00:13:36which is this one up here that we're gonna be using,
00:13:38to try to write this out and get it right.
00:13:40So let's come back to our workspace.
00:13:43Let's come to our bronze to silver transformation.
00:13:47And let's bring up our AI assistant.
00:13:50Now I'm going to describe what I want it to do.
00:13:53And then we're gonna see if it's able to write it out.
00:13:56I myself could write it out very accurately
00:13:58in probably maybe three to four minutes,
00:14:00but this is not a coding tutorial.
00:14:03I want to show you guys how ETL pipelines work in Databricks,
00:14:06not how to necessarily transform the data.
00:14:09So let's try this out.
00:14:10So I'm gonna say, take my data set
00:14:12and I can pull it up over here just so we can see it.
00:14:15I'm gonna go to data engineering, video one,
00:14:18just so I can see the data.
00:14:19Take my data set in the data engineering catalog
00:14:24in video one schema called dirty underscore data underscore S3.
00:14:31Now I like to be super explicit
00:14:33because I don't want there to be confusion,
00:14:34especially as you have like hundreds of tables,
00:14:36you don't want it to read in the wrong tables.
00:14:38I like to be super explicit.
00:14:40We're gonna ask it.
00:14:40I'm gonna say there is something wrong in the date column,
00:14:45making it a string.
00:14:48I want you to identify and fix that issue.
00:14:52There's also duplicates in the data set.
00:14:55I want you to remove duplicates on the ID.
00:14:59Now I'm being slightly vague, right?
00:15:01I'm not telling it exactly what it needs to do,
00:15:03but I'm gonna let this run
00:15:04and we're gonna see if it's able to identify the issues
00:15:07and write the code.
00:15:08I do want it in Python.
00:15:10I think that's just the easiest way to transform this data.
00:15:13And so I'm gonna say use Python and pandas
00:15:17and let's give it a go.
00:15:18So let's let this thing for just a little bit
00:15:20and we'll see what it comes up with.
00:15:22So it took about a minute or so.
00:15:23It did a lot of different things
00:15:25and now it wants to actually run this code.
00:15:28Now, before we do that,
00:15:29you can have it ask every time
00:15:30or you can just allow it to run the code after it's done.
00:15:33I'm gonna ask it to ask every time just because,
00:15:36you know, I wanna make sure.
00:15:38Now it does have a lot of printing
00:15:40just to show the work that it's doing.
00:15:43I myself don't want this in my output,
00:15:45so I will ask it to change that in just a second.
00:15:47But it does identify that there's a period,
00:15:50it replaced it with a forward slash,
00:15:51it converts it to to date time, which looks correct.
00:15:54And it also formats it for us.
00:15:56Then it comes down here
00:15:58and it's doing just a ton of kind of pretty unnecessary things
00:16:01before it gets to this df.dropduplicates on the user ID.
00:16:06And we're keeping the first one, which is perfectly fine.
00:16:08And then lastly, it's doing a lot of verification.
00:16:11I basically don't want 80% of this code.
00:16:14I just want the simple stuff.
00:16:15So all I'm gonna say is,
00:16:16I like the transformations you've done,
00:16:19but get rid of all the print statements.
00:16:23All right, it looks like it's done.
00:16:25As you can see, it cleaned up the code immensely.
00:16:28This is really looking good.
00:16:30I'm gonna go ahead and I'm going to accept all.
00:16:32You can see the diffs down here, by the way,
00:16:34for all the code that it's writing or taking away.
00:16:36We're gonna accept all.
00:16:37And we are going to run this ourselves.
00:16:40We can, I'll just click run all here.
00:16:42But we're gonna run this ourselves.
00:16:43And then we'll verify and make sure
00:16:45that this actually looks good.
00:16:47So let's open this up.
00:16:48Let's come down here and let's just do a display.
00:16:52We'll do data frame underscore clean,
00:16:54which is what it named it.
00:16:56So now let's look at this new data frame
00:16:58that it has created that should be a lot cleaner than before.
00:17:01So now if we come down here, we have our 1009.
00:17:04Let's go see if our 1009 was removed.
00:17:07It was.
00:17:08And let's come over here to our signup date.
00:17:11And it looks like that now is converted to a timestamp,
00:17:14which is perfectly fine.
00:17:15We could also do it as just a date column,
00:17:18but honestly, it really doesn't matter.
00:17:20This is a great change and it cleans it up immensely.
00:17:24So now it's all standardized, it's actually in a date column
00:17:27or a timestamp column and that works great.
00:17:30Now, all we need to do as the last part of this process
00:17:33is we have to write this table to a new table.
00:17:36And that's gonna be our silver table.
00:17:38So I'm gonna come down here.
00:17:39I'm gonna say, and I could put it as the genie code,
00:17:42or I can come over here.
00:17:44I tend to like using this side a lot more.
00:17:46I don't know why, but I'm gonna say,
00:17:48write this cleaned table to a new table in the same schema
00:17:54and call it S3 underscore cleaned silver.
00:17:59And so let's go ahead and let that run.
00:18:01And it should take just a second
00:18:03and we'll have that code for us.
00:18:05Let's go down here really quick.
00:18:06We have this, this looks great.
00:18:08I'm gonna allow this to run for us.
00:18:11So it's gonna run this code.
00:18:12Now it is giving us this warning,
00:18:14and this is a very fair warning.
00:18:15We're using overwrite right here.
00:18:17And basically what we're doing is every time we run this,
00:18:19we're overwriting the previous data that's in that table.
00:18:22For now, I'm just gonna use that
00:18:24because it's not a huge deal.
00:18:26You know, as you start getting more sophisticated
00:18:28with your data pipelines,
00:18:29you are gonna want to think about things like adding data
00:18:32to your existing data instead of overwriting.
00:18:35But you know, that can get a little bit more advanced
00:18:37depending on your data and your data need.
00:18:39Now it's going to run this and I'm gonna accept all.
00:18:43And then let's come right over here
00:18:44to our data engineering video one.
00:18:47And now we have this S3 cleaned silver.
00:18:49So our bronze to silver transformation is complete.
00:18:53This is all we needed to do in order to transform our data.
00:18:57And now we have our raw data.
00:18:59And let's come actually back to our catalog
00:19:01and we'll just take a look at this.
00:19:03We can get rid of our genie code real quick.
00:19:05So we're gonna come over here.
00:19:06So our raw data is still going to be raw.
00:19:10Let's go ahead and run this.
00:19:10This is our bronze level, right?
00:19:13We still have the raw data.
00:19:15We still have the duplicates.
00:19:16But when we come over to our silver,
00:19:18this is now going to be our cleaned level.
00:19:21So now that we have all of our transformations completed,
00:19:23we've taken it from bronze to silver.
00:19:25Now we want to create our silver to gold transformations as well.
00:19:30Let's come back to our workspace.
00:19:31And we'll come down here to the silver to gold,
00:19:34which is gonna still be right up here for us.
00:19:36Now let's give it a use case, right?
00:19:38We could use this table just as it raw and we could hit off of it
00:19:42and we could build dashboards and all sorts of things.
00:19:44Sometimes we want to track certain KPIs or certain things
00:19:46that you can't just get from the raw data.
00:19:48So I'm just going to give it a simple use case,
00:19:51let it write it out and we'll create our silver to gold transformation.
00:19:54So let's come right down here.
00:19:55I'm going to say that I want to know the best day of the week
00:19:57that people are clicking on certain ads,
00:19:59and we're going to see what it creates for us.
00:20:02So I want to create a new table called insights underscore gold,
00:20:10and I want it to show me the best days of the week
00:20:14and what ads people clicked on the most.
00:20:18And let's run this and just see what it does.
00:20:20All right, so it went and did a lot of work for us.
00:20:22It did not take long.
00:20:23This is maybe 15 seconds.
00:20:25It's doing some group buys on some different columns,
00:20:29and then it's getting some counts for us on different signups and referral sources.
00:20:34Let's go ahead and allow it to run this and let's see what it does.
00:20:38Now, it's given us a few things as far as outputs.
00:20:41One, this first one is extracting the day of the week and analyzing signup patterns.
00:20:45So Thursday, Tuesday, Monday, and it's giving us kind of the day of the week
00:20:48when we had the most signups.
00:20:50And then if we come down here, we also have another one where we're getting the referral source,
00:20:55basically social media, organic referral, Google Ads or partner,
00:20:58the total clicks and the countries reached.
00:21:01And if we go down here, we have this last table, but it hasn't been run yet
00:21:04because this is actually creating our table.
00:21:06And so this one should be really interesting, but let's actually stop it really quick.
00:21:11And then I'm going to accept and then run this as well.
00:21:15I just want to see what this one is.
00:21:17And so, and then we have a day name, the referral source, signups and unique countries.
00:21:22I think this is the one that I, you know, was kind of hoping for
00:21:26when I asked it to run it for us, but it gave us different options, which I like.
00:21:30Now, all we have to do is we have to get rid of this and we're going to let that
00:21:34run.
00:21:35And so let's accept that and let's run this as well.
00:21:38Now, this display is literally just displaying, uh, right up here.
00:21:42So we aren't actually reading this in, but, uh, let's come back into our catalog
00:21:47and let's go see if we have that gold table now.
00:21:49So now we have our video, we have our insights gold, and let's just look at our sample data.
00:21:55And there we go.
00:21:56And so this would be like our gold table that we can now use.
00:22:00We now have some insights into our data.
00:22:02Now all we've done so far, if we come back in here, all we've done so far is we've just
00:22:08written code.
00:22:09We haven't necessarily created any type of pipeline.
00:22:12And so now this is the part of the video.
00:22:15We're going to get into building an actual pipeline.
00:22:17And I did it this way very specifically.
00:22:19This is how I tend to write my code.
00:22:21I come into a notebook.
00:22:22I write out my code and then I'm like, okay, this is looking good.
00:22:25Let me now go create my pipeline.
00:22:27So let's come right over here.
00:22:29We're going to come down to our runs and there's this thing right here that says ETL pipeline.
00:22:34Now let's get rid of this.
00:22:35We also have this right here, which is kind of what we're going to cover a lot in the next
00:22:40lesson.
00:22:41But I want to talk you through really quickly while we're here, the difference.
00:22:44Now we created two separate notebooks, one from bronze to silver, one from silver to gold.
00:22:49Now, sometimes with simpler pipelines, like the one we just created,
00:22:52it could be totally fine to just come in here, create a job and say,
00:22:56do this one and then do this one, right?
00:22:58That's all we're doing.
00:22:59We can put it on a schedule or we can create a different trigger for that.
00:23:02And we'll look at that in the next lesson.
00:23:04But if you have a more complex pipeline, you're typically going to want to use this right here,
00:23:09which is our ETL pipeline.
00:23:11Let's go ahead and click in on this ETL pipeline.
00:23:13And let's come down here to start with an empty file.
00:23:16Now you can start with sample code in SQL, sample code in Python,
00:23:20or if you have ones that you've already done, you can do that.
00:23:23We don't have anything and I don't really want to kind of explain
00:23:27all of the sample code that they're going to be creating.
00:23:29Let's just start with an empty file.
00:23:31Now we need to specify the language that we're using.
00:23:33And this is very important because once you create it,
00:23:37that's kind of the one that you're going to stick with.
00:23:38We're going to use Python and that's just asking for a folder path.
00:23:42And so we'll keep that and we'll say yes.
00:23:45And now what we have looks very similar, right?
00:23:48We have these kind of some notebooks on the left.
00:23:50Then we can write our code right here.
00:23:52It looks very similar, but there is a big difference between running something in a notebook
00:23:56like we were in our workspace before and running something in an ETL pipeline.
00:24:01When you're just running your code, it's running the code as is.
00:24:04It's pretty simple.
00:24:06And if you did what we said earlier, which is you literally just take that notebook,
00:24:09you put it into a job and you say, run this and then run this.
00:24:11It's literally just going to take your code and run it.
00:24:14The issue with that though, is it's not going to have any built-in data quality checks.
00:24:17We're going to have to manage basically all of the logic ourselves,
00:24:20and it's not going to handle any lineage tracking or dependencies within your code.
00:24:24Now, this is where ETL pipelines come into play.
00:24:27An ETL pipeline is going to have things built into it like automatic incremental processing,
00:24:31built-in data quality checks, failure recovery, things like that, that are extremely useful
00:24:36when you have really complex pipelines, which we aren't doing in this lesson.
00:24:40Of course, it's very simple, but you have to think, you know,
00:24:43if you're creating a real ETL pipeline with a lot of dependencies, a lot of complexities to it,
00:24:47you absolutely are going to want to come in here.
00:24:49Now, when we write this out, we can't just write it as our regular code and we can actually do
00:24:54that.
00:24:55Let's come back and let's go to all of our files. Let's go to bronze to silver transformation.
00:25:01We're going to move this just so we can visually see it.
00:25:04We're going to put this in our transformations,
00:25:05and then we're also going to take our silver to gold,
00:25:08and we're going to move this to our transformations as well.
00:25:10So we're going to put this all in one place.
00:25:12And so now we have this silver to gold and we have the bronze.
00:25:15We don't actually need this file anymore, so we could just get rid of this.
00:25:20Now, your UI might look slightly different.
00:25:22That's just because Databricks is always updating things,
00:25:25but you should still be able to follow along.
00:25:27But let's go ahead and this is our code.
00:25:30It's exactly how we wrote it before.
00:25:32Let's try to run this pipeline.
00:25:34It's going to try to run this and it should try to run that too.
00:25:36Let's just go ahead and run it and see what happens.
00:25:39All right.
00:25:39So we got this error down here that says pipelines are expected to have at least one table to find,
00:25:44but no tables were found in your pipeline,
00:25:46which might seem very counterintuitive because you're like,
00:25:50we've created different data frames.
00:25:51We've been working with tables, so it should understand what it's doing.
00:25:55Now, it is actually rewriting the code as we go.
00:25:57I think it's identified the issue already.
00:25:59And let me explain this, even though it's starting to write it out already for us,
00:26:04which is awesome.
00:26:05Thank you, genie code.
00:26:06But here's what's happening.
00:26:07When you're running code just in a notebook, it's just going line by line and running the code.
00:26:13But within this ETL process, and just ignore that for a second,
00:26:16because I'm just going to let it run.
00:26:17Within this ETL process, what it's using is something called an SDP,
00:26:21which is a Spark declarative pipeline.
00:26:23This is just a different construct and a different framework within the ETL pipeline.
00:26:28And so what it actually needs is something called a materialized view.
00:26:31It needs to kind of look at what the output is going to be or supposed to be.
00:26:35It's not just blindly running your code for you.
00:26:37It's doing a lot of heavy lifting with data quality checks and all these different things.
00:26:41Now, it just went through and it fixed it for us.
00:26:45It is basically the same code.
00:26:47And let's come up.
00:26:48But it's creating these materialized views.
00:26:50So we have dp.materializedView.
00:26:52And it's kind of naming it and giving a little comment on what it is.
00:26:55It's doing the work for us.
00:26:57And it's creating another materialized view where we use this insights goal.
00:27:01And that's actually putting it all into one, which is fine.
00:27:05That's what we want to do with this pipeline.
00:27:07But let's go ahead and accept this.
00:27:08And let's try running this pipeline again.
00:27:11So now we have a little bit more information.
00:27:13We can come right down here.
00:27:14And we can see it was trying to create these different materialized views.
00:27:17And it was working.
00:27:19And so now this whole thing has run successfully.
00:27:22Let's actually rename this really quick.
00:27:23We're going to do bronze.
00:27:25I need to spell bronze, right?
00:27:26Bronze to silver to gold ETL pipeline.
00:27:31And let's save it like that.
00:27:32And we come back over here.
00:27:34We can go to our jobs and pipelines.
00:27:36We now have this pipeline right here.
00:27:38Of course, it failed.
00:27:40But now it's running and it's working successfully.
00:27:42But now we have this pipeline that we have stored.
00:27:45And we can actually start using this in, you know, automations where we can orchestrate
00:27:49these pipelines right here.
00:27:51It says orchestrate notebooks, jobs, queries, and more.
00:27:53And there is a lot to that.
00:27:54And that's what we're covering in the next lesson.
00:27:56But if we open this up, we can actually see what's happening under the hood.
00:28:00We can see these are connected.
00:28:02We're doing this one and then this one.
00:28:03And we can see how it's running.
00:28:05And so there's a lot of things that this ETL pipeline is going to handle for us that we
00:28:10don't even have to worry about.
00:28:11That really is one of the biggest advantages of using an ETL pipeline instead of just running
00:28:15your notebooks.
00:28:16Although, again, there are some advantages to just running your notebooks as is if it's
00:28:22a little bit of a simpler pipeline.
00:28:23I really hope you're able to follow along with this lesson because this is really cool stuff.
00:28:27You can also just come into here and we can create an ETL pipeline.
00:28:31And you can create a pipeline with AI.
00:28:33So we can literally just come here and we can type in exactly what we want our code to look
00:28:38like and do within our data.
00:28:39And it can build that out instead of starting with a notebook and then creating our ETL pipeline.
00:28:43You can just come right in here and start doing that process here.
00:28:47I will say though, my personal workflow, because I'm usually not doing super complex pipelines
00:28:51that are involving, you know, a ton of different dependency chains and all these different things
00:28:55is I tend to like writing my code in notebooks.
00:28:58That's just what I'm used to, but there are going to be lots of use cases where you're going
00:29:03to need to come in here and you can just start here instead of starting with a notebook.
00:29:06Now, in the last two lessons, we've been building out our ETL pipeline.
00:29:09We've been writing all of our code and getting everything set up.
00:29:12But once we actually have everything set up, then we need to automate this process so that
00:29:16we don't have to manually go in and run the code ourselves.
00:29:19Luckily, Databricks has this already built out for us.
00:29:22It is called a job.
00:29:23And so we're going to jump into Databricks.
00:29:25We're going to create our own custom job and we're going to see all the small things
00:29:28that you need to do in order to create this automation.
00:29:31Now, in our last lesson, we built out this bronze to silver to gold ETL pipeline.
00:29:35And we're basically creating two separate tables, this S3 underscore clean silver,
00:29:39and then this insights gold.
00:29:41And that is our silver and our gold tables after they're transformed.
00:29:45And we find our business insights.
00:29:47Now, just for demonstration purposes, I also just kept our regular code in here as well.
00:29:52We have this bronze to silver, then we have another notebook for silver to gold.
00:29:56Now, these are just regular notebooks in Databricks,
00:29:59but I do want to show you how you can use this within a job as well.
00:30:03But we have this bronze to silver transformation.
00:30:05You can see it in a pipeline.
00:30:07And then if we just go to our bronze to silver, this is just a regular notebook.
00:30:11Now, in order to create our job, let's come right down here.
00:30:14We're going to go to runs.
00:30:15We're going to come over to job.
00:30:16And this is orchestrate notebooks, pipelines, queries, and more.
00:30:20So, let's come in here.
00:30:21Now, this is a new UI for us.
00:30:23And what you can do here is you can orchestrate the different steps that you want within your job.
00:30:29If we click right down here, we can see all the things that we can do.
00:30:33We can create ingestion pipelines, or we can use existing ones.
00:30:36We can come down here and we can run notebooks, Python files, SQL queries, SQL files.
00:30:41And we have some more advanced things right down here, like if-else conditions,
00:30:46or you can create triggers from another job.
00:30:50And then we also have this ingestion and transformation.
00:30:52And these are really useful because if you have an ingestion pipeline, an ETL pipeline,
00:30:57or a database table sync, then you can just use those that you've already created.
00:31:01Now, we've created an ETL pipeline.
00:31:04Let's go ahead and click on this ETL pipeline.
00:31:06We're going to come down here and we're going to click on this bronze to silver gold ETL pipeline.
00:31:11Now, I'm just going to call this bronze to silver to gold, keep it simple.
00:31:18And all we would need to do is create this task.
00:31:21Now, of course, that would be a little too simple, right?
00:31:24But this is as simple as it can get for any type of pipeline or orchestration that you're trying to
00:31:29do.
00:31:29Oftentimes, when I'm creating entire pipelines and there's a lot of different steps to it,
00:31:34I package everything into an ETL pipeline and then I just place it in here.
00:31:38And then what I'll do is I'll come over here to schedules and triggers.
00:31:43Now, we'll look at that in just a second, really quick.
00:31:45We can also trigger a full refresh on this pipeline so we can click on this.
00:31:49We can also add notifications if you want to send this notification when it kicks off or when it finishes.
00:31:54We can also look at retries.
00:31:57Now, this is really important because sometimes you are going to have things that fail just for a various number
00:32:02of reasons.
00:32:03Maybe you're trying to run this, but the data hasn't all imported yet.
00:32:05And so you're trying to run this transformation.
00:32:07But there's some connection issue and that caused it to fail.
00:32:11You'd want to retry maybe an hour later or on a different day.
00:32:15You would want to attempt to try this.
00:32:18And so you can come in here and you can say, okay, I want to try this a ton of
00:32:22times.
00:32:22Let's try it 30 total times.
00:32:24And every single time, we're going to wait maybe 30 or 40 minutes between each try.
00:32:30And then you'll keep trying until it is successful.
00:32:32Again, with this, you can notify yourself and make sure that you know what's happening, especially if this is a
00:32:37really important pipeline within your company.
00:32:40It is important to have these things set up so you don't have to manually go in there and see
00:32:44it failed, you know, last night and just never got a notification.
00:32:47It never tried again.
00:32:49So this would absolutely be something that you'd want to do.
00:32:51And then you have metric thresholds.
00:32:53You can set these, especially for something like a run duration.
00:32:57If you know this should take five minutes at most, you can set a timeout threshold or a warning threshold.
00:33:02It may be 30 minutes so that it isn't just going to keep running because sometimes it gets stuck in
00:33:06these loops and it keeps trying and it's going to run forever.
00:33:09And it's going to cost a lot of money and you don't want that to happen.
00:33:12So these are all really important things to think about when you are actually creating these jobs.
00:33:16Now, let's come back here to schedules and triggers for something like this when you've done almost all the work
00:33:23in an ETL pipeline, you are going to want to schedule or trigger this most of the time.
00:33:28Now, for something like this pipeline, what we've done is we've extracted data out of an S3 bucket.
00:33:33What we would want to do is probably set a trigger for this.
00:33:35Now, what we need to do is we need to create this task first so that it is saved in
00:33:40there.
00:33:40And then let's say this is our entire job.
00:33:43It's a very simple one.
00:33:44But now we can come in here and we can add a trigger.
00:33:47There are several different types of triggers.
00:33:49One, we have a schedule, which is as simple as it sounds.
00:33:52We are just going to schedule this.
00:33:55Right now, it'll be active.
00:33:56You can pause it.
00:33:57We're just going to schedule this and we'll say every one week.
00:34:00And so every one week, we're going to save this and this is going to run every week.
00:34:05So that's super simple.
00:34:07Now, let's delete this and let's add another trigger.
00:34:10We can also schedule it and we can go a little bit more advanced.
00:34:13And we can schedule it at a very specific day and time.
00:34:16Now, this is what I usually do because there are certain cadences and timing to things that
00:34:21I really like.
00:34:22For example, at a previous job that I used to work at, we wanted the data to be as fresh
00:34:26as possible because we actually had it refresh often, like every 10 minutes.
00:34:30And so what we were doing was we were trying to run it as soon as we could in the
00:34:34morning to where
00:34:35it would still run, but it would give us the freshest set of data by about 830 in the morning.
00:34:39So we would kick off this job at like 745 so that the freshest data would be available by 830.
00:34:45This is more advanced.
00:34:46You don't have to do this, but this is a really useful thing to do.
00:34:51The next thing that you can do or the next type of trigger is a file arrival.
00:34:55So if we click on file arrival, we're going to say when a file arrives at this location,
00:35:01kick off this job and run everything within it.
00:35:03Now for our process, this would be like our S3 bucket. If we can go and look at our S3
00:35:08bucket,
00:35:09if a new file gets dropped in here or this gets updated, then we may trigger this job and it
00:35:14will run.
00:35:15And of course we have advanced settings as well, where we can wait a minimum time between triggers
00:35:21because what if you're uploading a lot of documents at the same time, you don't want it to trigger 20
00:35:25times because you just dropped 20 different files in there one at a time. You'd want to wait for all
00:35:30these
00:35:30files to get in there. So that is absolutely an option. And if we go back, we also have a
00:35:36table
00:35:36update. So this would trigger when new data is updated on a table. Now for our use case,
00:35:42this may work because we have S3 data. We're bringing it into our bronze table. So I can come
00:35:48in here and I can say, when this table, and I would just specify that table name that we've been
00:35:52using.
00:35:53When this bronze table gets updated from that S3 bucket, then kick off this job, which of course,
00:36:00this ETL pipeline takes that bronze data, we transform all the data, we create our gold tables,
00:36:05and then we have all that data sitting there. So this might be a really good use case. We have
00:36:09some advanced options down here, minimum time between triggers and wait after last change,
00:36:14just like we did before, because sometimes data gets updated continuously. And so it might trigger
00:36:19it many times. These are things that you should test and try out within your pipelines,
00:36:23just to make sure you get them right. Now let's cancel out of this and let's actually get rid of
00:36:28this
00:36:28entirely. Let's actually come here and we're going to go back to our runs, or sorry, back to our jobs.
00:36:36And I want to show you one more thing within here that might be really useful. Now we just kind
00:36:41of
00:36:41came down here and we pulled in this ETL pipeline, but let's actually pull in and run a notebook.
00:36:47So we're going to specify our notebook. We're just going to do this as our bronze to silver,
00:36:52and this is a notebook. It's within our workspace, not a Git provider. And let's select our notebook.
00:36:57So we're going to come in here. We're going to do bronze to silver. Let's confirm this.
00:37:01And you'll notice we have a lot of different options in here, some similar, right? We have
00:37:06retries, we have notifications and we have metric thresholds, but we also have parameters. These are
00:37:11parameters that you can pass down to the task because this is just a notebook. It doesn't have
00:37:15all that built-in stuff that we were talking about in the last lesson within the ETL pipeline.
00:37:20So you do need to configure this a little bit more within a job. So we can add these parameters
00:37:25where
00:37:26we create these kind of key value pairs that we pass into a notebook, but let's come in here,
00:37:31let's create this task. And now we're going to add in another task. So let's come here. We're going to
00:37:36add in another notebook, and this is going to be our silver to gold. Now these two tasks,
00:37:45and let's actually name this, these two tasks that we've created, these two notebooks,
00:37:50do the exact same thing as our pipeline. But I wanted to show you this because it does give us
00:37:56some more information when we're actually building out these jobs. So we specified our path, we have
00:38:00our computer serverless, but now we have something called a dependency or a dependency chain. This right
00:38:06here, this line is a dependency. With what we have right now, this silver to gold is completely dependent
00:38:13on this bronze to silver, which means if we get this data in and this bronze to silver does not
00:38:19run
00:38:19correctly, then this silver to gold is never going to run. And in this use case, that's perfectly fine
00:38:26because this relies heavily on this bronze to silver. But there are going to be use cases where that is
00:38:31not the case, where we would not want that to be, you know, a dependency. We wouldn't have to rely
00:38:36on it.
00:38:37Or we also have an option right down here to run if dependencies. And we have a lot of different
00:38:43options. So right now, all succeeded means this has to run properly in order for this to run. But there
00:38:50are going to be cases when you create these chains or these dependency chains where you're like,
00:38:55it doesn't matter if this one runs, we just want it to run after this one runs, whether it fails
00:39:01or not.
00:39:01And so for that one, you can come in here and say, at least one succeeded, none failed,
00:39:06all are done, at least one failed or all failed. It doesn't matter. You can specify whichever option
00:39:12you need. For us, we would want to keep this all succeeded because if this one runs, we don't
00:39:18actually create the silver tables that are needed in order to run this one. So that is pretty important.
00:39:24We can come down here and we can create this task. And now we have this job that we've created
00:39:30and we
00:39:30run it now. Or of course we could add in our trigger. Now, typically with something like this,
00:39:36it could go either way. You could have it on file arrival, table update, or a schedule. It really is
00:39:41just very dependent on your workflow and how you want this to trigger. For most of these, you're going
00:39:46to have some type of trigger. Let's just set it on a schedule and let's go to advanced. And we're
00:39:51going
00:39:51to set this for every week. And let's do this on a Monday. And let's do it at 745,
00:39:57because that's when I used to do some ones at a previous job. So I'm going to do it at
00:40:02745
00:40:02every morning. Let's go ahead and schedule this. And now we've updated this job. And now we can also
00:40:08rename this. I'm going to call this our silver to gold job. So now if we go back to our
00:40:16jobs and
00:40:17pipelines, we have our silver to gold job right here. This was the pipeline that we built out in the
00:40:23last
00:40:23lesson. And this is going to be orchestrated and scheduled to run this pipeline. Well, actually,
00:40:29we used the notebooks instead of the pipeline for that last example, but we're going to be running
00:40:33that code to actually create and update those tables. So that is how we create a job in Databricks.
00:40:39This is extremely, extremely useful. Again, like we did just a little bit ago for our silver to
00:40:45gold job, and let's go into the tasks. If it's a really small transformation, and maybe it's just for
00:40:50me, I'll just do it like this where I just have the notebooks. But if it's a larger transformation,
00:40:56especially if there's a lot of dependencies, if there's a lot of complexity, I will use an ETL
00:41:01pipeline. So get in here, mess around with this, try this out because this is super fun to play around
00:41:06with and kind of get all those dependency chains going and getting the ETL pipelines where they're
00:41:10triggering off of each other or when a file is updated. This is really cool stuff to mess around
00:41:15with and is awesome to use within Databricks. Now, if you haven't been following along in the past
00:41:19three videos in the series, we've covered several things. One, we've just learned about ingesting
00:41:25data. Then after that, we looked at ETL pipelines, and then we looked at creating a job to orchestrate
00:41:30all these things and to kind of automate the process. In this video, we're going to be putting
00:41:34all of that together into one. We're going to add some things that we didn't cover in previous lessons
00:41:39to make it a little bit more advanced, but it's going to cover a lot of the same concepts.
00:41:43Let's not waste any time. Let's jump right onto my screen and get started. Now, before we actually
00:41:47jump into Databricks, what we're going to be working with is that same S3 bucket that we created earlier,
00:41:53but I created this transactions folder, and that is going to be an important piece of this process.
00:41:59It's something that we touched on in a previous video, but we are actually going to be doing it
00:42:04in this lesson. We use this users-dirty.csv in this bucket, but inside of this transactions,
00:42:11we have three separate transaction files, and we'll actually be adding another one later on
00:42:16to show how the entire process works. I'm going to have these and the other file down in the
00:42:23description. You can just download those from GitHub, but we will need those. We're just going
00:42:27to start off with these three, the one underscore six, one underscore 13, and one underscore 20.
00:42:33Now, really quick, just to show you what data we're working with, this is our data. Let me actually
00:42:39zoom in just a little bit. The data itself is not as important for this specific project just because
00:42:46we're more focused on the process of building the pipeline within Databricks, but within the project,
00:42:52we will be cleaning this data a little bit because this is just a horrible column. I think whoever,
00:42:57you know, was collecting this data just left this free text or something for people to just put
00:43:02whatever they wanted in there. Not a good system, but that is the kind of data that we're going to
00:43:09be working with. So, let's come up here. Let's get out of this. We don't need to save it. Now,
00:43:14let's come up to our Databricks. Now, in our previous lesson, this is what we built. We built this
00:43:21pipeline right here, Bronzes Silver to Gold Etail Pipeline, and then in the very last lesson,
00:43:26we created this silver to gold job, which basically scheduled this and automated this,
00:43:31and it ran successfully and everything was great. Now, what we're going to be doing is we're going
00:43:36to be doing it in a similar fashion, but covering some new things. All you need to do, and I
00:43:41actually
00:43:41have another tab for this because I don't want to have to keep going back and forth when we're
00:43:45building this out, but I created this end-to-end schema within our data engineering catalog. You don't
00:43:51have to do this. You can put this wherever you want. I just did this as kind of where we'll
00:43:55be
00:43:55building things out. So, I'll just come back to this as we start adding in new tables. As we start
00:44:00creating this stuff, I'm going to come back to that. Now, this is where we'll be doing a lot of
00:44:06our work on this tab right here. So, let's come over to data ingestion. Let's go over to our Amazon
00:44:13S3.
00:44:14Now, if you haven't already, in a previous lesson, I think the second video, we connected to an S3 bucket.
00:44:21So, if you don't know how to do that, then come over here and do this.
00:44:24Now, we used it for the one time because all we used was this usersdirty.csv,
00:44:30but in order to schedule this data ingestion, we're going to use a folder. So, we have this
00:44:35transactions folder right here. So, we're going to click on this. We're going to click on transactions,
00:44:41and we have those three separate files in there, and we can schedule when we want to bring those in.
00:44:46Now, we can be very specific or pretty laid back. So, for example, if we want to do, you know,
00:44:53once a day, we can specify what time of day we want that, and that's similar to a job. So,
00:44:58it's not that
00:44:58crazy. Now, what we're going to be actually doing is we're going to schedule this for basically every
00:45:0230 minutes. And what we're going to do is we're going to build this entire thing out,
00:45:05and what our trigger is going to be inside of our job is when a table gets updated. So,
00:45:11then we're going to drop a file in our S3 bucket, and when this brings it in at that 30
00:45:16minute point,
00:45:17it's then going to refresh, kick off the job, which runs our ETL pipeline. We should be able to do
00:45:22all
00:45:22this within 30 minutes for sure. So, I'm going to say every 30 minutes, and we'll just set it at
00:45:30zero minutes past the hour, which means basically the top of the hour. Now, this is my time zone,
00:45:36but you can set it to whatever time zone you want. Now, let's go ahead and preview this table. It's
00:45:41going to start up our compute, and then it's going to give us basically what we need in order to
00:45:46create
00:45:47this table, which is our preview, and then where we want to place it along with the table name.
00:45:53Now, an important thing to note from just those three files is there's only 50 rows of data in
00:45:57each one. So, if we come down here, we got all the way up to 100, so we at least
00:46:02know two of those
00:46:03files are coming in just from this preview. We're going to keep this as the transactions, but for the
00:46:09schema, we're going to add the end-to-end, which is the custom one that we created for this project.
00:46:14So, we have transactions right here. Let's go ahead and create the streaming table.
00:46:19So, now this table has been created. Let's just look at a sample of this data. It should show us
00:46:25enough to be confident all three got in, but then we can just also run a query, and that's probably
00:46:29fine. In fact, instead of waiting, uh, never mind, we got them all in. I was going to say,
00:46:35we don't have to wait on this. We could just run a query in like a notebook or a SQL
00:46:39editor,
00:46:39but we have all 150. So, that's all three files. So, now that we know we have all three of
00:46:45our files
00:46:46in because it's 50 each, it's going to be 150 rows. Now that we know those are in, we can
00:46:50start building
00:46:51things out. Now that we know that it's all in there, what we can do is let's come over here
00:46:56to
00:46:57our jobs and pipelines. Now, this is where we were before. We only had these two things. We had a
00:47:02pipeline,
00:47:03and now we had a job. And now we have another pipeline, and we didn't build this ourselves.
00:47:08This was built automatically. And if we come in here, we can get a little bit of information on
00:47:14this. This is our streaming pipeline that we created to put into this end-to-end transaction.
00:47:20So, this is that streaming table that we created. And so, we don't have to technically manage this.
00:47:26It's going to be managed by Databricks itself. And so, this is just something to note that when we did
00:47:31that, we did create its own pipeline for this. Now what we need to do is we need to create
00:47:38an ETL
00:47:39pipeline. So, let's come in here. We're going to click on the ETL pipeline. This new UI pops up right
00:47:45away. We don't have the options that we had before in previous lessons. But now what we're going to do
00:47:50is we're going to start building this out with Genie code. Now, I could absolutely just write all this
00:47:56out, and this would be like an hour and a half video, or we can have Genie code write it
00:48:00out,
00:48:00which I highly recommend trying it out and starting to use these tools because they really
00:48:05speed up your work. And if you already know how to program, if you know how to code, this is
00:48:09going
00:48:09to be a huge boost to your productivity. And so, what we're now going to do is use Genie code,
00:48:14write down here, basically tell it what we want to build. And we're going to do a few things. One,
00:48:19we want to build that bronze to silver, which is basically our raw data, which is that transactions
00:48:25table, to a silver table, which is where the data is cleaned, to then a gold table, which is what
00:48:31we
00:48:31would use for like a production level product or production level analysis or whatever that might
00:48:37be. So, we can come in here and we can use that app, and it is prompting us to do
00:48:42that. And if we
00:48:43come in, we can say data engineering dot end to end, and I'll just put it like that so it's
00:48:51looking
00:48:52kind of at that schema. I'm just going to say for the transactions table, I want to create a bronze
00:49:02to silver transformation on this raw data. I want you to clean this data set. I'm just going to leave
00:49:12it
00:49:13really open-ended just to see what it does. Maybe it catches something outside of that column.
00:49:18I don't think it will, but let's just see what it does. Then we are going to create a silver
00:49:25to gold
00:49:27transformation. And you can do this in the same notebook or separate notebooks. It may also do that
00:49:34for you with Genie code, but you can be really specific and it's honestly pretty great at what it
00:49:39does. And I want to track daily transactions in that gold table. So, I'm going to give it just this
00:49:48to work on. It's going to take that. It's going to kind of create its logic. It's going to start
00:49:53writing everything out. I have found, this is not just me saying this, I genuinely love working in
00:49:59this system because Genie code is very good at understanding context and what you're trying to do
00:50:03and working with tables and just everything. And so, we're going to let this run for just a little
00:50:09bit. I'm going to come back. We'll take a look at what it said and then we'll commit some code
00:50:13to
00:50:14start going on this ETL pipeline. All right. So, it just finished. I haven't even really reviewed this
00:50:18because it only took, you know, 30 seconds, but it took a look at the data. Then it came down
00:50:24here
00:50:24and gave a proposed pipeline architecture. So, here's what we have. We have our bronze layer,
00:50:30which is just going to be our transactions.py. And this is just going to read in the data as
00:50:35is.
00:50:35So, it's going to recreate basically the raw data, which I'm totally fine with. It's not a big deal.
00:50:41Then for our silver layer, we have the silver transactions underscore clean. It's going to
00:50:45trim the white space, standardize capitalization, remove duplicate spaces, filter out null transaction
00:50:50IDs or negative quantities and amounts, and add data quality expectations. I think these are all
00:50:57perfectly reasonable things to do. Then we have our gold layer. There's going to be
00:51:01transformations gold daily transactions summary.py. So, there's three different files that it's going
00:51:07to create. And it's going to aggregate some of this data into kind of these metrics right here.
00:51:12I think this all looks great. If there was something I wanted to change, I would just tell it,
00:51:16hey, let's do this instead. So, let's just say, go for it, start writing the code,
00:51:23my friend. It really is my friend at this point. I've been using it a lot. So, let's let this
00:51:28run.
00:51:29Let's watch the code. And then we will commit everything. And then it probably will prompt us
00:51:33to do some type of dry run to make sure that there aren't any errors that were just missing. And
00:51:38then
00:51:39we will run the entire thing and start automating this with a job as well. It's still writing. I was
00:51:44like 10 seconds, I stopped talking, but it's still writing everything. It's going to start organizing
00:51:49this. It's going to start creating our .py files or just our Python files. I just am reading it as
00:51:55is. But it's creating our Python files. And then it's going to start writing the code in which we
00:52:00are then going to review, approve, and then run. You can see these things starting to pop up. So,
00:52:06we have our code, we have our diff, or, you know, if we had code that it took out, it
00:52:11would also say
00:52:11the minus. But we're just creating code right now. And so, right here, it's saying, all right,
00:52:18do we want to try dry running this pipeline? Do we want to just see if it works? And of
00:52:24course,
00:52:24we're going to do that in a second, but I'm going to go to each one just to kind of
00:52:27see what it's
00:52:28doing. It looks like this is our gold and we're just using a group by for this. Let's just see
00:52:36what
00:52:36it did for the data cleaning. So, it looks like it is going to drop some stuff in here, but
00:52:41we are
00:52:41looking at some regex replace, which is great. Some trimming and proper case for a few other stuff.
00:52:48And this looks perfectly good to me. I have no problem with what it's doing. Again, this is all
00:52:54subject to be altered. If you want to change this or have it do other things or fix the code
00:52:58yourself,
00:52:59you absolutely can do that. Now, all we're going to do is we're just going to accept this. And so,
00:53:04we're going to allow this and it's going to run a dry. So, we'll accept, review next, we'll accept,
00:53:09review next, and accept. I didn't have, I could have done that in a different way. But now,
00:53:13we're going to try dry running this pipeline. Now, what this does is it is not going to actually run
00:53:19through and run your code. It's doing a dry run. It's basically testing, are there any big errors
00:53:23that we need to fix before you actually implement this into, you know, whatever process you're doing,
00:53:28so that we don't have issues right off the bat. It's going to run for just a little bit,
00:53:32and then it'll tell us if there's any big issues. Oftentimes, if you've never done this before,
00:53:38you shouldn't have any big issues, but you could get issues like, oh, this table,
00:53:42you don't have the permissions for this table. Maybe you wrote something incorrectly, or in this
00:53:47case, you know, Genie Code wrote something incorrectly that is not going to create the
00:53:51materialized view properly, or you're pulling from a table that doesn't exist anymore. So,
00:53:55there's lots of issues that could arise, but let's let this run. It shouldn't take very long.
00:54:00And just like that, we did encounter a small issue. It's actually going to run,
00:54:05it'll probably fix this very easily. I am not exactly sure what the issue is here.
00:54:11I'm just glancing at it. But it looks like it's fixing that code for all of it. And let's go
00:54:17ahead
00:54:17and just accept that, and let's try dry running this one more time. Now it looks like everything
00:54:23is running properly, and this is really good. So, what we can now do is we can rename this. So,
00:54:31it's going to give us some feedback on that, but I'm going to rename this, and I'm going to say
00:54:35this
00:54:35is our end-to-end ETL pipeline. And that's what we're going to name it. So, we have our end
00:54:45-to-end ETL pipeline.
00:54:47And with this, if we come back here, obviously nothing has changed, right? This is just a dry
00:54:53run that we did. Now, what we can do is we can actually run this pipeline, and it will run
00:55:00everything. It's going to do all the transformations, all the things that we would want it to do.
00:55:05And we should, and we will do that in a little bit. Now, what we want to do is we
00:55:09want to automate this
00:55:10process. All we have to do is we're going to come back here to, not data ingestion, into runs,
00:55:17and let's get rid of this. Now we're going to create a job for this. So, we're going to come
00:55:23in
00:55:23here and we're going to say we want our pipeline. And if we come in here, we have our end
00:55:28-to-end ETL
00:55:29pipeline. That's the one we want. We're just going to call this end-to-end ETL pipeline. Keep it simple.
00:55:35Now, what we're going to do, and you can always come in here and add notifications and retries and
00:55:40metric thresholds, which we covered in the last lesson. Now, we're going to create this task,
00:55:44but now we're going to add this trigger right here. Now, this trigger is going to be a table update.
00:55:50So, what we want it to do is when new data is actually updated and brought into that table,
00:55:56we want this job to kick off so that it runs our entire ETL process to clean the data and
00:56:01put that
00:56:02new data into our new tables that we're creating. Now, what we want to do is we want to say
00:56:08this table.
00:56:09When this table gets updated. So, let's come up here and we're just going to copy this name to
00:56:14the clipboard and we're going to put it right down in here. We could also have typed it out.
00:56:18Either one's fine, but I just wanted to copy it. So, when this table gets updated by our S3 process,
00:56:25which we're running every 30 minutes, this is going to kick off the ETL pipeline, right? It's going to kick
00:56:32off this right here. So, now that we have that job updated and created, let's go back to our jobs
00:56:39and
00:56:39pipelines. And now we have a few new things in here. So, right here, we have our end-to-end
00:56:44ETL pipeline.
00:56:46I should have named this job. Let's actually come in here really quick. I'm just going to come up here.
00:56:50I'm going to rename this. I'm going to say job to run end-to-end pipeline. And let's rename this.
00:56:59So, we have our pipeline. We have our job to run the ETL pipeline. And we have our transactions that's
00:57:06going to run 30 minutes on the minute. It looks like it may have already run before. No, I think
00:57:13we're good.
00:57:14No, it did. It's already run twice. I think that's just because of when I set it to the zero
00:57:19time.
00:57:20Perfectly fine. But what we're going to do now is we are going to just check that this end-to
00:57:28-end ETL pipeline
00:57:28is working properly. It's going to create all of our tables. We're going to then write a query
00:57:33just to show that the data looks good. And then we'll go drop our extra file in there. And then
00:57:39we'll wait to have it update and the ETL pipeline bring in the data. Then our job is going to
00:57:44trigger.
00:57:45And then it'll run our ETL pipeline to bring in and clean that new data as well.
00:57:49So, let us run this pipeline. This is going to take just a little bit to actually run. And then
00:57:57we'll
00:57:57go check the data in just a little bit. All right. So, this looks like it worked properly. We have
00:58:03completed, completed, and completed. Let us come up here and let's go back and let's refresh this.
00:58:12And it is possible that I put it in the wrong place. And it totally is. I absolutely forgot to
00:58:20change that in the ETL pipeline. It is pointed at the workspace default. Let's actually go back.
00:58:26And, you know, this happens. We're going to edit this pipeline. So, it is our default catalog that
00:58:32caused this issue. We have our default catalog and default schema as workspace and then default.
00:58:38Um, you can change this. You don't have to, but you absolutely can. You also, if I'm being honest,
00:58:44I should have fixed this myself or caught this right away. I like to be explicit when I'm, you know,
00:58:50writing to places. I don't like to have defaults like this. So, I should have had it specified right
00:58:55here. Where we're writing it should have been like, uh, you know, data engineering dot end to end dot
00:59:00and then the table name just to be more explicit. And we should have done this in basically all the
00:59:06Python files within the ETL pipeline. Totally fine though. Not a massive deal. Just, you know,
00:59:12something to think about. Now, if we come back to this catalog and we look at this,
00:59:19we can go to, let's go to the silver transactions clean. This is going to be our cleaned data.
00:59:26Let's just go ahead and run this real quick so we can look at that sample data.
00:59:29So, now this is our clean data. This product name looks much better. Uh, looks really good.
00:59:36It did a few other really small things in here, but this is the main one that we're looking at.
00:59:41If
00:59:41we go back to the bronze transactions, uh, and look at our sample data. So, this is our bronze table.
00:59:49This looks terrible. So, obviously it did a really good job data cleaning it. Uh, and then we'll look at
00:59:54the gold daily transactions summary. And this is looking at the transactions, just grouping by,
01:00:03and then looking at a lot of our data. And this is great for like a gold table, uh, that
01:00:08we're going
01:00:08to be using for, you know, some metrics or whatever we want to use it for. So, all of this
01:00:13looks really
01:00:13good. Now, in our silver transactions clean, in our sample data, again, we, at least in the sample,
01:00:21we only have a hundred, let's go and run this. So, let's actually create a notebook with this,
01:00:27and let's run this query. So, now we can see we have 150 rows, and let's add code,
01:00:35and let's copy this. And instead of the transactions clean, we'll say, uh, let's go see what that table's
01:00:44called. It's not gold customers. I should've kept it up over here. Let's go back to our catalog. See,
01:00:50when I start, when I start messing with my systems, I start getting messed up. It's gold
01:00:55daily transactions summary. I could've gotten copied at somewhere else. Uh, but I'm gonna put it right
01:01:00here so that we can look at this. And what we're gonna do now is we're gonna go drop in
01:01:06that other
01:01:07file into the S3 bucket so that we can see when it gets updated and to make sure that the
01:01:13new data
01:01:14gets in there and gets cleaned. So, let's come over here. We're going to upload, and let's click
01:01:20on add files. Now, we're gonna come here. We have that 127. That's the new one that we didn't have
01:01:25before. Let's upload this and put this into our S3 bucket. And now we have 06, 13, 20, and 27,
01:01:33all in this S3 bucket. So, now what we're gonna do is we're just going to, I'm gonna literally just
01:01:39let this wait. Let's come over here. And right here, this is going to kick off in probably like
01:01:46five minutes or so. I'm just gonna let it run. This is going to kick off. And then you will
01:01:51see
01:01:51that this job to run the end time pipeline will automatically kick off as well once that table is
01:01:58updated. So, let's just be patient. Let's just wait. I'm gonna skip you ahead. And you will see
01:02:03this running in just a little bit. All right, now you can see that this is kicked off. Looks like
01:02:09it
01:02:10is running. When this process is finished, it is going to update that table with the new data,
01:02:15which is going to trigger this job right here based on this table update on data engineering dot end to
01:02:21end
01:02:22dot transactions. And that should start any second here. And it looks like that is working. We can see
01:02:28this one running. And since it is literally running this pipeline, we can also see that this one is
01:02:33going to start running as well. It's just spinning up the compute so that it can run properly. Let's go
01:02:38ahead and let this run. And then we're going to see and check in our queries if everything actually
01:02:44went through properly. All right, it looks like this job is still spinning, but the pipeline was kicked
01:02:50off successfully. It looks like it ran with no issues, which is exactly what we want. And now this job
01:02:56is done. So now our entire process is complete and it is going to keep doing that every 30 minutes,
01:03:04every single 30 minutes from now until I stop the job or I stop this pipeline from running.
01:03:10It is going to kick this off. It's going to kick off the job. It's going to kick off the
01:03:14pipeline
01:03:15every 30 minutes. Of course, I'm going to stop that because that's nuts to keep running,
01:03:19but let's come back. Let's go to our workspace. And I think it's this one. Let's go take a look.
01:03:26Yeah, so now we have 150 rows. Let's go ahead and run this. We should see 200 rows of data.
01:03:33And there we go. And let's just make sure it's all cleaned properly in that product name.
01:03:40Looks great. And let's come down here and let's just make sure that this gets updated. We have 21 rows,
01:03:45but more than that, it's about the data because this is aggregated. So let's go ahead and run this
01:03:51as well. And we have 28 rows. That's just another week's worth of data. And these numbers are actually
01:03:57look basically the same, but we have this new week's worth of data in here that we didn't have
01:04:03before. So that is the entire end to end project. It really brings everything together that we've
01:04:08been working with in the past several lessons into one final project. And I hope you were able to
01:04:13follow along. If you didn't follow along, you just watch this video to the end. I highly recommend
01:04:18using the free edition. I will have a link in the description. You can try all this out completely
01:04:23for free. You don't even have to enter a debit card or credit card, which I love. So you can
01:04:28just
01:04:28use this and it is an amazing platform to try out. I highly recommend it. But with that being said,
01:04:34thank you guys so much for watching. I hope you liked this video. I hope you learned something in
01:04:38this entire series. If you did, be sure to like and subscribe. I'll see you in the next episode.
Comments