Data Engineering Course For Beginners - #2 TRANSFORM

***
My FREE guide on how to become a data engineer:
karolinasowinska.notion.site/...
***
This is the second part of the Free Data Engineering Course for Beginners that I've decided to create for you! Over the course of the four videos, we are going to cover the entire ETL process (extract, transform, load), and at the end we are also going to talk about job scheduling.
In this course you will build your first data feed (or data pipeline) using Spotify API. This feed will run daily, and it will download the data about the songs that you listened to during a day, and save that data in a SQLite database on your local machine.
In this video we are going to cover the Transform stage of the ETL process, which means that we will be learning how to validate the data that we received from a data vendor (Spotify in this case). We'll check for empty files, null values, stale data and duplicates! Along the way I will also explain some basic data engineering concepts such as a primary key constraint, or "garbage in, garbage out" principle.
Follow this link to generate your Spotify API token:
developer.spotify.com/console...
Find the code with this data engineering project on GitHub:
github.com/karolina-sowinska/...
----
If you'd like to learn data engineering, I recommend following the 4 simple steps below to land you the first job interview:
1. Learn Python
I recommend following the Python for Everybody specialization course on Coursera, which is one of the most popular courses there:
imp.i384100.net/x9gVO3
2. Learn SQL
SQL is still the lingua franca of data. I recommend going with Learn SQL Basics for Data Science course, because it contains some chapters which are very releavant to data engineering in partcular, e.g. distributed computing with Spark
imp.i384100.net/QOMZ09
3. Learn Bash scripting/Linux
I wouldn't take a full course on it, but at least read a good article.
if you do prefer to take a course/guided project, I think this one is short and good:
www.coursera.org/projects/com...
4. Learn how to develop on the cloud, e.g. on AWS
There are a few good courses around there, but I think the Coursera one is the most comprehensive
imp.i384100.net/P0MJBM
Discloure: The above contain affiliate links, meaning when you click the links and make a purchase, I receive a commission!
Discloure: The above contain affiliate links, meaning when you click the links and make a purchase, I receive a commission.
----
Music:
What Now - Golden Age Radio
Connect with me on Instagram:
@karo_sowinska
And if you want to make my day with a cup of coffee... :)
ko-fi.com/karolina_sowinska

[@dionysioskokkoris4340]

'some magic creatures....intercept and corrupt json in an inconceivably wicked way' hahahaha amazing

[@lucidlessons5902]

this is an ideal scenario, microservice data is so messy, cleaning it and figuring out the common keys is a task !!!

[@karolinasowinska]

Very true! :)

[@citormussa]

Very cool, I think that watching you professionals actually doing things while explaining are essential to the learning process. Thank you very much, great video!

[@karolinasowinska]

My pleasure! :)

[@ericdasse8174]

Karolina, you sure know how to break down data engineering. Really helpful. Thank you very much!

[@Korudo]

The “validation stage” comment was super helpful. Thanks!

[@lianlx]

Thank you for the explanation with easy understanding, Karolina! In an ocean of Data Science content, it's really hard to find Data Engineering content like this

[@karolinasowinska]

Aw my pleasure! :)

[@EduardoSantos-gh1dk]

Thank you for the great content, really like this "real case scenario" learning.

[@karolinasowinska]

My pleasure, more to come! ;)

[@javiermadriz7834]

You are amazing, I finally understand the concept of etl with practical examples.

[@Alva1326]

I work Pentaho Data Integration but now i want to learn python as an etl tool (and then airflow or luigi), so this video series is exactly what i need in order to get started!

[@karolinasowinska]

I'm super glad to hear that! ;)

[@BrianTanWah]

Very useful video! I love how you can make a concept that is very convoluted and technical and explain it in a fun way that is easy to understand. Will be waiting for the next video in your series!

[@karolinasowinska]

Thank you, I'm glad you liked the video! :) The next one is coming today!

[@karolinasowinska]

My free course is just an introduction to the data engineering world. If you'd like to learn data engineering and be ready to apply for jobs, I recommend following the 4 simple steps: 1. Learn Python I recommend following the Python for Everybody specialization course on Coursera, which is one of the most popular courses there: imp.i384100.net/x9gVO3 2. Learn SQL SQL is still the lingua franca of data. I recommend going with Learn SQL Basics for Data Science course, because it contains some chapters which are very releavant to data engineering in partcular, e.g. distributed computing with Spark imp.i384100.net/QOMZ09 3. Learn Bash scripting/Linux I wouldn't take a full course on it, but at least read a good article. if you do prefer to take a course/guided project, I think this one is short and good: www.coursera.org/projects/command-line-linux 4. Learn how to develop on the cloud, e.g. on AWS There are a few good courses around there, but I think the Coursera one is the most comprehensive imp.i384100.net/P0MJBM

[@thiagoduarte7207]

Some people are born to teach, and you are one of those people.

[@iceguyoo]

I like how you are giving real world examples and you walk through the code. Very impressive and I in learning a lot. Thank you so much

[@karolinasowinska]

I'm glad you're finding this useful! :)

[@Paulo-oy1gs]

I would like to let you know that I truly enjoy your way of teaching. Thanks for this.

[@karolinasowinska]

I appreciate that!

[@paulntalo1425]

Thank you so much for the videos am a data science student but with great enthusiasm about data engineering. You channel is greatly helping shape my career paths

[@karolinasowinska]

I'm super glad to hear that! :) All the best!

[@hloniramakatane7584]

Looooooving this project! Thank you so much

[@MrFrunko]

Thanks for the content!!!! its really hard to find content like the one you do focused on data engineering . subscribed! greetings from Chile!!

[@karolinasowinska]

My pleasure! ;)

[@sebastianalvarez1391]

Thanks Karolina for sharing us your knowledge! , greetings from Ecuador :)

[@leilanidoofy90]

Amazing easy to understand explanations ❤

[@ezekielmauricio5209]

Great Stuff!

[@scornfrost]

You are awesome! you make everything less boring, for that reason thank you so much!

[@karolinasowinska]

My pleasure!! :)

[@bckzilla]

Awesome!

[@gfunkwsc]

Thanks! you are great at teachng!

[@ngee4925]

Great job!

[@nidhithakur561]

@Karolina I really enjoyed it!

[@LoganNinefingers]

Hello Karolina thank you for the video. Regarding the last validation step with the timestamp variable, shouldn’t it be < yesterday rather than !=, to consider “today’s” listened to music as well. Thanks again

[@yrrep27]

Good catch! Saved me a bit of time.

[@haribk5995]

Really great content, please do more informative videos!

[@samoiloff90]

I love you. Thank you!

[@BillyDTourist]

Nice and clean, another great video. First time ever, no other comment, haha!

[@karolinasowinska]

Haha thanks, I tried to take a lot of your previous feedback on board! ;)

[@charliefulton7304]

these are great. you are so funny too!

[@arshadr9073]

Very Informative Keep up the good work👍🏻

[@karolinasowinska]

I'm glad you found it useful, thanks! :)

[@SciHeartJourney]

I do have one comment, we need to see MORE of Eliza! 🤗☺😍 But you're awesome too Ms. Karolina! ☺

[@karolinasowinska]

She left her wig somewhere deep in the wardrobe, but I'll try to convince her to dig it out one day!

[@seemunyum832]

Thank you so much for the content... Amazing

[@karolinasowinska]

My pleasure:)

[@abhishekchakravorty6250]

Epic content as always, would love to learn from you more and more. Are you an actress? Your closeup reactions makes my heart skip a beat. You're amazing, this channel's amazing, your content is amazing. EVERY THING ABOUT YOU IS AMAZING. BIG FAN!!!!❤️❤️

[@karolinasowinska]

I'm really, really glad that you enjoy being here! ♥ P.S. I did acting classes back in school ;)

[@keyo3945]

awww, how cute you are girl! 😻😻🤗🤗🤗 and just look at this your channel is growing so fast, I am so happy about it(only 34 more to be 1K) 5:10 mischievous little creatures are everywhere 😅 wonderful as always Dziękuję bardzo

[@karolinasowinska]

I should watch out for some mishevious creatures fiddling with my subscribers data, haha! On a serious note, thanks so much for your support, I really appreciate it :)

[@chetnaringe3061]

Heyy... Your content so awesome and the way u explain it its easy for ur to understand it.. 💃💃u go grlll

[@karolinasowinska]

Thank you! I'm glad you enjoy the videos :)

[@manish0589]

Helpful video . Thanks :).

[@karolinasowinska]

My pleasure:)

[@thelightspring]

That "wicked part " was so funny .I laughed and dropped a tear ...because it is true . You are amazing ! I learned so much Maybe can share later on if you work for some company .How one day as data engineer is from your perspective

[@karolinasowinska]

Haha I'm glad I made you laugh ;) I'll think about the "day in life" video :)

[@salimz.8587]

THANKS!!!!

[@ziad_m_404]

Can't thank you enough 🙏

[@karolinasowinska]

My pleasure!

[@stevie3427]

Muchas gracias por el tutorial, muy bueno. Saludos desde Soledad, Atlántico! Te espero pa que nos comamos unas butifarritas jeje

[@khalidsultani6006]

Great respect, you deserve all the best, (you look great today 😉☺️👍)

[@karolinasowinska]

Thanks a lot, I'm glad you enjoy watching the series! :)

[@nds142]

Ahead to 1k+++ go go gooo

[@karolinasowinska]

Ah yes, it's the final countdown!

[@javid4288]

Many valid points, but there is an important case that is missing, sometimes we enrich the data because we have that information in another source, so when we load data from a different source and we aggregate them and load them to another DB for example.

[@karolinasowinska]

Definitely true, I tried to keep this simple, but will keep your point for the future videos!

[@marissaperozzi602]

Thank you so much for creating these videos! I am starting a position as a data engineer in a few months and your videos have been very helpful in helping me learn the profession!

[@bhavinshah1202]

Hi Karolina, Thanks for your video. It help for beginners like me to understand the basics of DE. One request, can please help to share sample code. Thanks.

[@andrewhting]

You are a frikkin badass😎

[@anubhavkarelia9585]

Hi Karolina, First of all, Its a Brilliant Beginners Course for DE, really appreciate your efforts ! Just a quick query on line 35, from where we are checking if the song information are from last 24hrs, but in the scenario as per code, if let's say I am new user and opened up a new account, and played some songs recently, it will show error, because instead of checking for last 24hrs, we are only considering data for yesterday, Not including data generated today ! Any Suggestions !

[@Snakey360]

Thanks for the video's. One question: isn't your null check too harsh? You are basically passing on an entire day if for example one row has an error. But maybe it's to keep the example simple? Anyway, on to part three of the series 😀

[@sprintwithcarlos]

You passed "after" as a query param, it should be "before" ;-)

[@fulano31231]

Hi, thanks for the video, really nice explained. I have a quick question though, my data seems to gather not only yesterday but also songs from the current day. Is it a problem with the unix timestamp? Have you ever encountered something like that? I am using the same format as in your code. Thanks!

[@karolinasowinska]

Hi! Perhaps change the date in the code!

[@MrSubash35]

Great video. Learned new things. I have a question. Let's say the volume of data is huge and i would like to directly store both the extracted data and transformed data in staging tables in a database , knowing that holding data in data frames will be occupying huge memory and might create issues when the batch is scheduled on the later stage. how do i solve that. also, since the data needs to be refreshed every 24 hours( daily batch) how do we truncate and load staging tables /data frames (extracted data brought from source) and temp tables/ extracted data frames (loaded from stage to temp).

[@karolinasowinska]

Excellent questions! With large volumes of data, you wouldn't use pandas dataframes for the ETL process. Instead, you'd load the data straight to a staging table.

[@AthanasiouApostolos]

Thank you for the video :) I have a question regarding the null values check. We correctly throw an exception if there are any null values, however, what happens if we have one or just a few null values in a huge dataframe? I was taught if there are only a few null values we replace them with the mean of the column if it's numerical data or the most common occurrence if it's categorical data. Do I have to sue my professor for malpractice?

[@karolinasowinska]

Haha you're right, null values happen and imputing them is a valid way of dealing with them. This is more of a judgement situation - do you ever except to see nulls from the vendor, and how many? If expect some, then letting them pass, and imputing later might a reasonable thing to do :)

[@user-xd4cv2ix7f]

a quick question, aren't we extracting the songs that we recently played? regardless of whether we downloaded them or not? so aren't we supposed to have data whether we're premium subscribers or not?

[@unamattina6023]

what happened at the end? i did same with you but i got this exception: raise Exception("Null value found") Exception: Null value found but i can see the songs that i listened today. how can i solve this?

[@1111111yeah]

Hello, I'm liking the project and I have some experience with Python, but I'm finding it hard to follow what's going on with things like the pandas commands and data frames etc. Is there some videos you could recommend for the basics on those things?

[@karolinasowinska]

I havent seen any particular video. Maybe search for a simple pandas tutorial in google? I'm sure there will be some good ones! :)

[@121586168542154]

Columns being Replaced with Periods~ easy Thank you for the great project, I'm learning so much! After this video, I can't figure out why the table is replacing columns with "..." I've triple checked spelling, functions, and imports. Using Pycharm, it might be an easy fix that I'm just too new to know. Below shows how the table displays; removing the artist and played at. "18 Epiphany ... 2020-09-18"

[@karolinasowinska]

I'm glad you're enjoying the course! I'm not sure why you're getting the "...", but my guess would be that this is just a visualisation of your table by your console. How many rows have you got?

[@user-of5px6dl5h]

Why did u used Pandas against Spark? Thank u for this course!

[@karolinasowinska]

Pandas is simple and it's my favourite tool for quick data analysis, especially when you're not dealing with real-time data.

[@rjorgeish]

Small nitpick at 05:06 if : pass else: This is a very bad design pattern. You're adding unneeded complexity to the code on the form of conditional branches On this kind of situations, you should consider simply: if not : Much more elegant and compliant with coding conventions :) . Most of the times that you have to use the "pass" reserved keyword, it means the code is not properly structured. The same would apply if you had something like: if : break else: continue You can simplify by just doing: If not : continue break Also, you duplicate the date/time constants inside the function when you could use the already existing ones under __main__ if you move them to an higher scope. On the other hand, shout out for defining the function signature with explicit parameter and return types. Just because you can omit them doesn't mean you should. I am very curious to see if ChatGPT cares about this kind of code improvements. If not, we are going to have debugging nightmares on years to come. 🥶

[@hloniramakatane7584]

Do we need to build our own function that will remove any songs which are not from yesterday's date?

[@karolinasowinska]

No, that should be handled by the dates specified!

[@constancewilliams5120]

My new BFF 🤓🤓

[@karolinasowinska]

High five, fellow data engineer girl! ;)

[@anesmekid798]

honestly i couldn't focusing on the course due to your charming eyes

[@tushard9067]

How can i learn all that python coding part. As a fresher I only know basics of python, seeing this code I feel nervous. Any suggestions?

[@karolinasowinska]

Don't feel nervous! Everything comes with experience. Try doing your own projects, perhaps try to get someone to review them for you so that you can learn from your own mistakes :)

[@ranniex1054]

Hi! I follow your videos and also try cloning your repo but I get the Exception, I suppose because GMT zones, can you help me with that validation please? Great content!

[@karolinasowinska]

Hi! Can you let me know which line of code gives the exception and what exception it is? :)

[@ranniex1054]

@@karolinasowinska Line 41 in your repo. It goes into the exception if the user have listen something in this day :(

[@Emily-is3cz]

@@ranniex1054 I'm having the same issue. For some reason the song dates we pull from spotify is 1 day ahead in the columns played_at and timestamp and that's why the error occurs. I'm not sure how to correct for this since the data comes directly from spotify. I'm sure you can subtract a day using python from the data but how do you correct this through spotify? Did you ever figure it out? I really wish Karolina had some data and spot this issue as well and give us some solutions. It would have also been great if she could have given us some solutions to these errors if they ever occured. I know the missing value and date error occured in my data set but not sure how to fix it.

[@christineeee96]

@@karolinasowinska Raise exception ("At least one of the returned songs does not have a yesterday's timestamp")

[@LaraUAE]

@@christineeee96 add the yesterday.replace line to your if name ==main section

[@section9999]

Cool! I only have one question... Is that your actual typing speed?? 😳 My god woman!

[@karolinasowinska]

Let it be a secret, but I sped it up a little bit!

[@section9999]

@@karolinasowinska Ok Shhh I think the comments section can hear us.. 😳🤐

[@Robi2009]

Nagrane w JuKej czy już w PL:)?

[@karolinasowinska]

Nagrane w UK, sklecane w PL :)

[@manjunathm3384]

Ma'am can you please tell me how to learn ETL completely?

[@karolinasowinska]

Yes. Please watch this series!:)

[@jonathan-._.-]

i think we should also check the api response code oO

[@jonathan-._.-]

(and maybe do an explicit check for the data/data.items property to exist otherwise debugging will take longer if something changes)

[@jonathan-._.-]

another tiny problem might be : in the request we spcify the last 24 hours but we validate in the last day - even if the validation were to suceed we would miss out on the data that was provided yesterday but after the last run

[@jonathan-._.-]

hm i would probably skip the unique check in the pipeline and just let the database handle that oO ( less work for me :3 ) ( i may differentiate depending on how busy the database is ^^ - btw i wonder if i could listen to 2 songs at the sime time on multiple devices oO)

[@karolinasowinska]

You're absolutely right, although checking the api response code typically would be done in the extract stage, right where the data is downloaded.

[@karolinasowinska]

@@jonathan-._.- Haha you're right, one can be more minimalistic! :) Btw I think Spotify prevents you from listening on multiple devices, and when you want to play on a second device, it says something like "you're already listening on your laptop, would you like to switch to listening on mobile now?"

[@adammoritz89]

Offtopic: You resemble Jodie Foster

[@karolinasowinska]

Haha yes, I've been told this before ;)

[@muse3324]

i think you've a career in data modeling, i mean data & modeling :D

[@karolinasowinska]

Ahaha how flattering :)

[@jonathan-._.-]

anything that can go wrong will go wrong #MurphysLaw

[@karolinasowinska]

So true!

[@candyfloss184]

You tube: data engineering course While I read dating course. 😂

[@karolinasowinska]

Haha I don't think I'd be qualified enough to run that!

[@candyfloss184]

Karolina Sowinska 🤣

[@dataluchs1288]

these magic creatures who intercepted the json file must be responsible for this one dislike why guys?

[@karolinasowinska]

Hahaha I like this justification! :D

[@sanajaobathongram4110]

That PRIMARY KEY tho

[@karolinasowinska]

Eliza sure knew she was holding something special! :)

[@sanajaobathongram4110]

Yeah, sure she did 😂

[@FerchoGarcia123]

so: never trust in an API!

[@karolinasowinska]

At least not blindly! ;)

[@kacper2626]

Great video! However last validation step doesn't make sense for me, i think that it should be more like this: played_ats = df['played_at'].to_list() for played_at in played_ats: if (datetime.datetime.strptime(played_at, "%Y-%m-%dT%H:%M:%S.%fZ") - yesterday).total_seconds() > 24 * 60**2: print(played_at) raise Exception("At lest one of the returned songs doesn't come from within the last 24 hours")