YT YouTube2Text

YouTube2Text → Transcripts → Code along - build an ELT Pipeline in 1 Hour (dbt, Snowflake, Airflow)

Code along - build an ELT Pipeline in 1 Hour (dbt, Snowflake, Airflow) — Transcript

by jayzern · 4,267 words · 706 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00what's the difference between ETL and
0:03elt they both have extract transformed
0:06load but they're range differently in
0:09the past when ETL was created cloud
0:11storage was very expensive in order to
0:14minimize cost businesses would transform
0:17the data first and load them in a data
0:20warehouse fast forward to today with the
0:22Advent of all these new exciting
0:24Technologies like snowflake storage is
0:27much cheaper which is why elt was cre it
0:30makes more sense to dump your data in a
0:33data warehouse and slice and dice them
0:36later on there are so many tools in the
0:38market for creating elt pipelines for
0:41example we have DBT snowflake prefect
0:45Daxter spark how do I pick the right one
0:49this is going to be a live coding
0:50tutorial where I'll walk you through how
0:52to build an elt pipeline from scratch
0:55I'll show you every step of the way and
0:57we'll talk about the thought process
0:59we'll cover basic data modeling
1:01techniques such as how to build fact
1:03tables data Marts we'll look into
1:05Snowflake rbac and how to deploy our
1:08model on airflow for all tools we will
1:10be using DBT for transformation
1:13snowflake for data warehousing and
1:15airflow for orchestration for
1:17orchestration feel free to use something
1:19else you're more comfortable with I hope
1:21you enjoyed this tutorial we'll use this
1:23snowflake tpch data set which is a free
1:26data set provided by snowflake make sure
1:28to have your snowflake personal account
1:30ready and let's Dive Right
1:33In for this Hands-On tutorial we're
1:36going to be developing locally using DBT
1:39core so the first thing to do is to go
1:42to DBT core which is this website over
1:44here and make sure you install DPT core
1:48using pip so you can run pip install DBT
1:52core on your terminal you can create a
1:54virtual environment if you want to there
1:56are several other ways to install DPT
1:58core you can install on
2:01Homebrew make sure you have your
2:03snowflake account set up so I have my
2:06account right
2:08here let's
2:21see then pip install DPT core the first
2:25thing we're going to do is to set up
2:26environments in Snowflake so we're going
2:29to create a warehouse a database and a
2:32roll in Snowflake and in that database
2:34we're going to create a schema and
2:37that's where we're going to write our
2:39DBT tables into for the role we're going
2:42to make sure this role is assigned to
2:44our user in Snowflake and we're also
2:46going to Grant access for your warehouse
2:48and your databases to that role so let's
2:51do that so let's use roll account admin
2:56account admin is sort of the super user
2:59for snowflake by the way and I'm going
3:01to create a
3:03warehouse I'm going to call it DBT
3:06Warehouse with Warehouse size
3:10equals small X
3:14small okay and I'm going to create a
3:18database let's call it DBT
3:22database and create a role let's call it
3:26DBT
3:28rooll so let's run
3:31that so press command enter to run your
3:35commands in your snowflake
3:37worksheet okay let's check the grants in
3:40your DBT
3:42Warehouse first before we actually
3:46Grant the warehouse usage onto the role
3:50so let's show grants on
3:52Warehouse BBT
3:54warehouse and you can see that there's
3:56only one user that has ownership PR
3:59privileg which is the account admin so
4:03what we're going to do next is to
4:05Grant
4:07usage on
4:10Warehouse DBT to roll DBT
4:16roll and if we run this show grants on
4:19Warehouse command again we can see that
4:22there's another another user that has
4:24usage privilege on this
4:27warehouse and it's the grante name is
4:30DBT roll so that looks good then the
4:33second thing we're going to do is to
4:34Grant
4:36roll we're we're going to Grant this
4:39role to our user so I'm using my own
4:43user this could be make sure to replace
4:47this with your own user so what we're
4:49going to do next is to Grant all on
4:52database
4:54DBT DB to roll DBT roll so so we're
4:59going to make sure this Ro has access to
5:02this database that we've created so
5:05let's run that
5:08again okay now that we've created our
5:11warehouses databases and roles let's
5:14switch into the role we just created so
5:17let's use DBT
5:19rooll and now we are going to create a
5:24schema DBT db. DBT schema
5:30like
5:31this and if we look here in our
5:35databases objects we can let's refresh
5:38the
5:39page you can see DBT schema exists but
5:43no objects are
5:44found so everything looks
5:50good if you have trouble rerunning this
5:53again you can also do something like
5:55create database if not exist otherwise
6:00if you try to create a rle again it's
6:01going to say the r already exist so
6:05create R if not exist you can do that
6:08too if you want to drop your warehouses
6:11later just so you don't incur cost you
6:14can do something like use roll account
6:18admin use account admin again and you
6:21can drop your
6:24Warehouse if
6:27exists DBT Warehouse drop
6:31database if exists DBT DB drop roll if
6:37exist DBT
6:39roll so now we're going to initialize
6:42our DBT project and all you have to do
6:44is run DBT in
6:46it I'm going to call my project
6:50dataor
6:52pipeline for the database I'm going to
6:55set up my profile as
6:57snowflake for account make sure you go
7:00to Snowflake and then go to your locator
7:03type here hover over so now copy that
7:07paste it here for user type in the user
7:10you created for
7:12Snowflake and I'm going to put password
7:14for Simplicity so just type in my
7:18password for roll we're going to add in
7:20the r that we just created so it's DBT
7:23uncore roll for warehouse It's DBT
7:27uncore Warehouse
7:29so Warehouse like this for database it's
7:33DBT
7:35DB schema is
7:37dtore
7:39schema for threats I'm going to use 10
7:42threats now CD into Data
7:47pipeline all right and let's open V
7:54code so let's configure our DBT project
7:58yo file
8:00and this file basically tells DBT that
8:04this is a project folder right this is
8:06the main file that's going to reference
8:08and it contains a bunch of useful
8:10information like where where to find
8:12your models where where you putting your
8:14test where your seats where your Macros
8:17so forth I'm going to create two two
8:20tables one called staging and this
8:24staging is going to be materialized as a
8:27view and I'm going to set the snowflake
8:31Warehouse as DBT
8:34warehouse and for I'm going to create
8:36another table called
8:39Mars and this is going to be
8:43materialized as a
8:45table and I'm also going to use the same
8:49snowflake Warehouse which is
8:51DBT
8:53Warehouse let's delete the example
8:58models and create new folders first one
9:01let's call it
9:03staging and let's call the next
9:06one Mars let's install some third party
9:11libraries this is going to be useful
9:13later when we're creating suret
9:16keys for our models so let's create a
9:19new folder let's call it packages.
9:24yo and in packages. yo
9:30I'm going
9:32to add DBT Labs DBT utils so this is a
9:38really common library in
9:40DBT let's look up what versions they
9:43have on DBT
9:58utils let's use the latest version
10:00actually to install your packages hit
10:03DBT
10:05depths to kind of give you a high level
10:08tour of DBT projects so this DBT project
10:12yamamo file tells DBT where to look for
10:15your models so the models folder is
10:17where we're going to write our SQL logic
10:20our source data sets are going to live
10:22here our staging files it's really good
10:25practice to separate your staging files
10:29staging files are Ono one with your
10:31source files and you should separate
10:33them with Ms folder all these models are
10:36going to materialize in Snowflake and
10:39this bottom piece of code here where we
10:41tell DBT hey I want to materialize all
10:44my models in this staging folder as
10:46views and I want to materialize all my
10:48models in this SMS folders as tables the
10:51macros folder you can write reusable
10:53macros here and I'm going to show you
10:55how to do that later for DBT packages by
10:59running DBT depths this is where third
11:01party libraries are going to live here
11:03seeds is for static file so files that
11:06the data is not going to change very
11:08often maybe you have a data set you have
11:11a CSV file that you need to reference
11:13and you know it doesn't change for every
11:15few months and you can put it in your C
11:17folder here snapshots are useful when
11:19you are trying to create incremental
11:22models and test folder here DBT test
11:27types there's two types of tests in DBT
11:30and the first one is singular tests and
11:32generic tests are parameterized queries
11:35and accepts Arguments for example check
11:38if this model doesn't have no values or
11:40check if this model has values greater
11:42than
11:46zero so that's kind of a high Lev tour
11:49of DBT projects now what we're going to
11:52do next is to set up our source and
11:54staging tables so go to models go to
11:59models and let's create a TP
12:03chore
12:05sources. yo the name of this file
12:08doesn't really matter so I'm going
12:11to create sources so the First Source
12:15I'm going to get it from the tpch data
12:20set I'm going to name it tpch and it's
12:23coming from Snowflake sample
12:26data and that basically
12:29comes
12:32from right here snowfake sample data and
12:36you have these bunch of schemas here I'm
12:37going to reference them so go back to
12:41schema tpch
12:45sf1 for tables pull in the orders
12:49table and this orders table has a bunch
12:52of
12:53columns but I'm going to write some
12:56tests here so there's a order key here
13:02and I want to make sure this
13:04key
13:06is
13:08unique and it's not null so like like
13:11what I said about generic tests so the
13:13second table I want to pull in is line
13:16item
13:19table and this line item table has a
13:22bunch of
13:23colums that's order key L order key
13:29is a foreign key to this table so let's
13:31test this relationship there's a generic
13:33test called
13:35relationships in two
13:38Source oops
13:41tpch
13:43orders and the field is order key so
13:49this test is going to make sure that
13:51this value here is actually a foreign
13:53key of this table so now that we have
13:55our sources table let's create our
13:57staging models
13:59staging models let's call it staging
14:02tpch
14:04orders. SQL so let's try to pull in data
14:08from our sources first to make sure
14:11everything
14:12works so the way you pull data from
14:14source is using this Source function
14:16wrapped around this Ginger curly braces
14:21bracket let's let's pick up data from
14:24orders let's do DBT
14:27run
14:30oo it's not
14:31working let's see what's the problem
14:34here oh
14:36okay so the issue is the yamamo files
14:39have to
14:40be indented correctly so it wasn't
14:44indented as a typo should be name not
14:48names and there should be a indentation
14:51here
14:52relations let's try to run that again
14:55everything looks good staging folder
14:58let's hit DBT run to run all your
15:03models okay see it says that it passed
15:07and if we go back if we go back here to
15:10our worksheet and
15:13refresh we can see our first table
15:16staging tpch ORD is created right here
15:19so that's pretty cool so what we want to
15:21do in our staging folder is I want to
15:24rename some of these values so let's do
15:27o order key as order key and then o cust
15:33key as
15:35customer key o order status
15:40as status
15:43code o total price as total price o
15:49order date as order date so I'm just
15:54going to rename my variables
15:57here and do the same same thing
16:00for let's create staging
16:03tpch line items. SQL so let's do the
16:08same thing let's call get our source and
16:11then the name of the table line
16:14item so this time I'm going to copy
16:17paste a bunch of the columns here and
16:21I'm going to create a surrogate key
16:24using dbts and a surrogate key is it's
16:27useful in dimensional modeling when you
16:29have a bunch of fact tables and
16:31dimensional tables that you want to
16:33connect so let's create our serate key
16:36here DBT utils surrogate
16:40key let's do something like
16:43this so I'm going to use l order
16:49key I'm going to use both the line
16:52number and the order key to create my
16:54circuit
16:55key let's name this as order order item
17:00key think of it as a
17:03hash to run this model only press DBT
17:08run- select
17:10or- s for short staging tpch line
17:17items let's
17:21go okay says that compilation error
17:25warning this function has been replaced
17:28by a new name so let's rename that run
17:35again okay it works
17:42perfect The Next Step we're going to do
17:44is we're going to transform our models
17:46when you work for a company you have to
17:48do some kind of business transformation
17:50on these staging tables staging tables
17:53are one to one with Source table and
17:56we're going to aggregate some data in
17:58this this line items table here and
18:01create a fact table as a result so what
18:04is a fact
18:08table so fact table is a dimensional
18:11modeling technique that stores results
18:14from a business
18:16process so data warehouse
18:22toolkit so let's look at what a fact
18:24table
18:26is so a fact table contains numeric
18:29measures produced by an operational
18:31measurement in the real world so you can
18:33think of a green in effect table as a a
18:37table that represents a bunch of numeric
18:40events and it's connected to other
18:42tables that you call dimensional tables
18:45so effect table always contains foreign
18:47keys for each of its Associated
18:50Dimensions so that's why we created our
18:52serate key so let's go back here and
18:55what I'm going to do
18:57is
18:59I'm going to reference the files I
19:02created just now so let's use the ref
19:05function staging tpch orders and let's
19:09call it
19:11orders
19:14oops and I'm going to join this
19:18table with staging tpch line
19:25items as line
19:28item and we're going to join it on this
19:31key so orders. order key equals line
19:35item line item. order key I'm going to
19:40copy a bunch of columns
19:42here and we're going to order
19:45this by orders. order date let's see if
19:50this works TBT
19:57run
20:01so remove this comma here syntax
20:07error orders order
20:10key make sure you run your orders table
20:14again
20:20oops and then make sure you run your in
20:24order items table
20:27again
20:29[Music]
20:33okay everything works so what I'm going
20:35to do next is to create a macro function
20:38and macro functions are a good way to
20:41reuse business logic across multiple
20:43models so let's create a file called
20:46pricing.
20:48SQL let's create pricing. SQL I'm going
20:52to Google DBT
20:56macros go here
21:00let's copy this example let's copy put
21:03it here I'm going to rename this as
21:07discounted
21:09amount it's going to have two inputs the
21:11first is extended
21:14price and discounted price discount
21:20percentage so over here is where we
21:22typically write our business logic write
21:24some business logic like this and let's
21:27reuse uses macro in this Ms folder so
21:32I'm going to call this function
21:34discounted amount on line item extended
21:38price and line item discount
21:41percentage so I'm trying to get the item
21:43discount amount right here let's see if
21:46this works DBT run in order items okay
21:52it
21:53works good by the way I'm going to add a
21:56new column here
21:59let's add extended
22:02price let's create more intermediate
22:04files I'm going to do this really
22:06quickly in order items summary. SQL just
22:11going to do a simple group bu taking the
22:14intermediate files we created and just
22:16do a group buyer over the extended price
22:18and discount amount and finally let's
22:21create a fact model so
22:24fct orders. SQL and in this fact model
22:28here let's
22:30select star from
22:34ref staging tpch
22:38orders as orders and we're going to join
22:42it with ref int order items
22:49summary as order item
22:55summary on
22:59let's join it with the order
23:06key order by the order
23:13date I'm going to take everything from
23:15the ORD table so
23:18star and I'm going to combine this order
23:21item
23:25summary it DBT run
23:32let's see if this
23:41works everything works so we have our
23:44fact table let's go back to Snowflake
23:48and click
23:52refresh if we click on tables we can see
23:55fact
23:56orders
23:58and this fact orders table is connected
24:00to our orders key right here so the way
24:04it works is this orders item is our
24:07dimensional dimensional model this is a
24:10huge oversimplification and there's a
24:12lot more to it to fact tables and
24:14dimensional tables but we're not going
24:16to talk about it in this
24:19video all
24:24right now let's create some test code
24:27there two types of test in DBT singular
24:29test where you write SQL queries that
24:31returns failing rowes and a generic test
24:34so let's write some generic tests so the
24:36models folder here I'm going to
24:40create generic
24:45test you can name this file whatever you
24:47want I'm going to call it generic test
24:49let's
24:56say
25:00write some generic test so this is what
25:03I mean by that there's a bunch of
25:04inbuilt tests in built generic tests
25:07like
25:08unique notnull
25:12relationships so let's test the
25:14relationship of the foreign keys right
25:18here staging tpch
25:21orders
25:25field and severity
25:28let's put
25:30warning let's add another test for
25:33status
25:39code and let's put accepted
25:53values so I'm saying that for this
25:55column status code I only want to accept
25:58p o and F and for this relationships
26:01generic test I want to test the foreign
26:04keys so make sure to indent these values
26:08here correctly accept the values values
26:10p o and F so this piece of code here is
26:14telling DBT to say check for these
26:17values these are the only acceptable
26:19values for relationships here it's
26:21checking the foreign keys and you're
26:23checking order key must be unique and it
26:25must not be null hit the BT
26:30test nice Works completed successfully
26:33let's build some singular
26:35tests let's create fact
26:39orders discount. SQL now let's write a
26:43test to check if the item discount is
26:47always greater than
26:49zero because you can't have a negative
26:51discount if you have a negative discount
26:54that means you are paying more money
26:56right fact
26:58orders let's do
27:01where item discount amount is greater
27:05than zero so DBT
27:09test so what happens if you
27:13do less than
27:18zero it's going to
27:21fail so it fails because your this query
27:25is returning a non-null value and you
27:27can see here the failure there are one
27:31is it 1 million 1.4 million values that
27:35didn't meet this test so it should be
27:38greater than
27:42zero okay now it's passed I'm going to
27:45write one more singular test let's call
27:47it fact orders date valid. SQL and over
27:53here let's do
27:56select star
27:59from ref fact
28:03orders
28:06where let's check if the date of the
28:10order date I'm going to cast this value
28:13as a date is greater than the current
28:18date or the
28:21date
28:24is older
28:26than
28:281990 which is a long time ago so what
28:31this test is saying is make sure the
28:33values are within an acceptable range so
28:36let's do DBT
28:40test everything is working
28:43again so now that we have our singular
28:45test set up we have a bunch of generic
28:48tests to summarize everything we've
28:50built so far we've created our source
28:53tables here we have a bunch of staging
28:56tables that reference the source tables
29:00for this line item St staging table we
29:02generated a ciruit key so it can
29:04reference later in our Downstream fact
29:07tables we have a bunch of Mars tables
29:11here and they do a bunch of
29:13Transformations these Transformations
29:15use macros that we wrote right
29:19here and finally we have an or fact
29:21table that references some dimensional
29:25models so we've done a lot so far now
29:27let's actually deploy this using
29:32[Music]
29:35airflow so I'm going to use airflow to
29:37deploy this DBT dag but you can choose
29:40your own orchestration platform of
29:42choice whether it's prefect
29:44Daxter you can even try to run this on
29:47AWS if you prefer
29:51to prefect and Daxter are great
29:53Alternatives that build upon airflow but
29:56let's just stick with airow flow and I'm
29:58going to use this Library called
30:00astronomer
30:04Cosmos and this Library here is going to
30:08allow us to run our DBT core projects
30:10using airflow Dax and task groups it's
30:14really simple to install all I have to
30:16do is go to your terminal here and run
30:21Brew install
30:23Astro I already have it installed so
30:26this is probably
30:27not going to work for me or it's going
30:30to update my Astro
30:33okay so we're going to initialize a new
30:35project using airflow so let's make
30:39directory let's call it DBT do CD into
30:43DBT D and run Astro Dev
30:47init so it's going to initialize a new
30:50astro
30:51project let's take a look at the code go
30:55to a Docker file and add this to your
30:57Docker
30:58file at the
31:01bottom so this command is going to
31:05install DBT
31:07snowflake after you add this piece of
31:11code to your Docker file okay after add
31:13this your darker file we're not done yet
31:16we need to add to our requirements file
31:20let's add as stomer
31:23cosmos and add Apache air
31:28flow providers
31:31snowflake so make sure to add these two
31:33lines for requirements txt file Astro
31:36def
31:40start okay air flow starting up
31:45amazing amazing amazing so let's go to
31:48Local Host
31:528080 username is username is admin
31:58password is admin
32:02okay so in order to run DBT on airflow
32:06copy paste data pipeline DBT folder into
32:10Dax folder right here so I've just did
32:12it is Dax DBT data
32:17Pipeline and here's the piece of code to
32:20write your DBT D so I'm basically just
32:23importing a bunch of libraries right
32:24here for datetime OS using the cosmos
32:29Library I am setting my connection to
32:32snowf using snowl con for my profile
32:36arguments I make sure I set that up to
32:38the database we created in Snowflake and
32:40the schema we created in
32:42Snowflake for your DBT dag right here
32:46make sure to point it towards the right
32:49directory and let's call it DBT dag ID
32:52and I'm going to schedule it for a daily
32:54run so everything here looks quite good
32:57if I go back to my airflow right here I
33:01can go to DBT dag there's one more thing
33:05we have to do left so go to admin go to
33:10connections and then click add a
33:14connection let's call it
33:16snowflake
33:18con and let's go to find
33:22snowflake so put in your snowflake
33:25account put
33:27M your Warehouse DBT
33:30Warehouse whoops DBT Warehouse DBT
33:35DB roll is DBT
33:38roll and that's
33:41it just double checking everything looks
33:44good okay everything looks good hit save
33:47let's go back to our DXs go to DBT
33:50D and let's try to run this so before we
33:54run it we can see this is how Cosmos
33:56will actually create a graph using graph
34:00for us to show us how this fact orders
34:02table is constructed we see the two
34:04staging models that we created goes to
34:07this intermediate table goes to another
34:09intermediate table and this table goes
34:11to fact orders you can even see your
34:14test code right here the trigger D let's
34:17see if this
34:22works
34:24fi let's take a look at why it f
34:29go to
34:32loog fail to
34:35execute okay I know so go back to admin
34:42connections I need to type in my
34:43snowflake username password
34:46so going type my password and user right
34:49here I'm going to throw this out and
34:51then click
34:53save let's try right
34:55again
35:01okay it looks like it's
35:06working you can see the user interface
35:09okay perfect it ran
35:12successfully if we go to run and go to
35:15logs you can see DBT code here actually
35:20running you can see your that code XCOM
35:24is pretty difficult to use in my opinion
35:26in there's a limit to how much data you
35:29can pass on XCOM okay awesome if you
35:32made it this far to the tutorial I hope
35:35you learn a lot we've made a lot of
35:37progress in this tutorial let's wrap up
35:39thank you for watching I genuinely hope
35:41that you got a lot of value out of these
35:43Hands-On tutorials and to summarize what
35:46we learned today we learned how to set
35:48up our snowflake environments with our
35:51warehouses our roles our users tables
35:54schemas databases we learned learn how
35:57to connect DBT core with Snowflake and
36:00inside DBT we've built models in our
36:02staging folders and our Mars folders for
36:05different in order to organize our
36:07models better after building the models
36:11we learned how to use macros to templae
36:14code and reuse business logic we know a
36:17difference between generic tests and
36:18singular tests using DBT and finally we
36:22orchestrated this DBT code inside
36:24airflow let me know the comments what
36:27kind of videos you want to see next as
36:29usual thank you so much for watching and
36:31I'll see you next time peace

About this transcript

This page contains the full transcript of Code along - build an ELT Pipeline in 1 Hour (dbt, Snowflake, Airflow) by jayzern, generated from the public captions YouTube serves with the video. The transcript has 4,267 words across 706 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

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

Free YouTube transcript tool

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