ETL Workflow In Snowflake | Chapter-19 | Snowflake Hands-on Tutorial

Building ETL Workflow in snowflake is not an easy task and if you have to build end to end ETL workflow (or ETL workflow) we need to use pipe, stream and task components along with external stages and orchestrate all the activities. This episode is a comprehensive 60min practical guide with hands-on exercise to demonstrate you how it works.
This guide will help you to answer the following questions
1. How to build ETL workflow pipeline in snowflake?
2. CDC & Incremental load via Snowflake?
3. Delta Data Load via SnowPipe?
4. Data Transformation via Stream & Task?
5. Task & Task Tree with Stream?
🚀🚀 CHEAT SHEET - Snowflake End To End Guide - bit.ly/43t5IPB 🚀🚀
➥ Site: toppertips.com
➥ Snowflake End To End Guide Cheat Sheet - bit.ly/43t5IPB
➥ SQL Scripts toppertips.com/snowflake-etl-...
🚀 🚀 Chapters 🚀 🚀
-----------------------------------------
➥ 00:00 ETL in Snowflake?
➥ 01:46 Episode-19 Introduction
➥ 02:20 Customer Order Use Case for Snowflake.
➥ 07:37 How many objects are needed for this ETL
➥ 07:40 Why to subscribe to this channel
➥ 10:35 Lets practice it together
➥ 10:52 Handson Part-1: Creating Landing Layer DDLs
➥ 16:43 Handson Part-2: Creating Curated Layer DDLs
➥ 20:40 Handson Part-3: Creating Consumption Layer DDLs
➥ 25:43 Handson Part-4: Stages & Pipes for Landing Layers
➥ 30:53 Handson Part-5: Stream & Tasks for Landing Layers
➥ 38:27 Handson Part-6: Stream & Tasks for Curated Layers
➥ 45:45 Handson Part-7: Data Loading via WebUI (Delta Load)
➥ 54:03 Handson Part-8: Delta Load via S3 Bucket
🚀 🚀 Snowflake Tutotorials (Beginners) All Episodes 🚀🚀
---------------------------------------------------------------------------------------------------
➥ Chapter 1-to-25 "Complete Playlist" 🌐 bit.ly/3iNTVGI
➥ Chapter-1 Snowflake Introduction & History 🌐 bit.ly/3xKHrna
➥ Chapter-2 Snowflake Free Trial Registration 🌐 bit.ly/3m6uiCL
➥ Chapter-3 Snowflake Architecture 🌐 bit.ly/3sk2fB2
➥ Chapter-4 Snowflake Classic or Legacy WebUI 🌐 bit.ly/3stSyzS
➥ Chapter-5 Snowflake SnowSight Modern WebUI 🌐 • Snowsight - Snowflake ...
➥ Chapter-6 Snowflake Unique Features 🌐 • Snowflake Unique Featu...
➥ Chapter-7 Snowflake DB/Schema/Table & Data Loading 🌐 • Snowflake Database/Sch...
➥ Chapter-8 Snowflake Must Know Database Objects 🌐 • Snowflake Must Know Ne...
➥ Chapter-9 Snowflake Fast Data Loading/Ingestion 🌐 • Fast Data Loading & Bu...
➥ Chapter-10 Snowflake Continuous Data Loading 🌐 • Continuous Data Loadin...
➥ Chapter-11 Snowflake External Table 🌐 • How to work with exter...
➥ Chapter-12 Snowflake Virtual Warehouse 🌐 • How To Work With Snowf...
➥ Chapter-13 Snowflake Micro Partition 🌐 • #13 | Micro Partitions...
➥ Chapter-14 Snowflake Time Travel 🌐 • What is Snowflake Time...
➥ Chapter-15 Snowflake Clone Feature 🌐 • Snowflake Zero Copy Cl...
➥ Chapter-16 Snowflake Secure Data Sharing 🌐 • Snowflake Data Sharing...
➥ Chapter-17 Snowflake Streams & Change Data Capture 🌐 • Snowflake Stream & Cha...
➥ Chapter-18 Snowflake Task & Task Tree 🌐 • Snowflake Tasks & Task...
➥ Chapter-19 ETL (Data Pipeline) in Snowflake 🌐 • ETL Workflow In Snowfl...
➥ Chapter-20 Role, Grants & Role Hierarchy in Snowflake 🌐 • Role, Grants & Role Hi...
➥ Chapter-21 (Part-1) Stored Procedure in Snowflake 🌐 • Stored Procedure In Sn...
➥ Chapter-21 (Part-2) User Defined Function in Snowflake 🌐 • User Defined Functions...
➥ Chapter-21 (Part-3) Snowflake Views (Standard, Secure & Materialized) 🌐 • What are views in Snow...
➥ Chapter-22 Snowflake Information Schema 🌐 • What Is Information Sc...
➥ Chapter-23 Snowflake Resource Monitor 🌐 • What Is Resource Monit...
➥ Chapter-24 (Part-1) Snowflake JDBC Driver 🌐 • How to Use Snowflake J...
➥ Chapter-24 (Part-2) Snowflake ODBC Driver 🌐 • Microsoft Excel & Snow...
➥ Chapter-24 (Part-3) Snowflake Python Connector 🌐 • Snowflake Python Conne...
➥ Chapter-25 (Part-1) Snowflake & PowerBI Reporting 🌐 coming-soon
➥ Chapter-25 (Part-2) Snowflake & Tableau Reporting 🌐 coming-soon
🚀 🚀 Snowflake Certification Complete Guide & Question Dump 🚀 🚀
-----------------------------------------------------------------------------------------------------------------
➥ Revised Sep 2020 Syllabus: • Video
➥ SnowPro Guide: bit.ly/35S7Rcb
➥ SnowPro Practice Test (60 Questions): bit.ly/2Ubernv
#ETLSnowflake #ELTSnowflake #snowflakecomputing #securedatasharing #deltaloadsnowflake #continousdataload #incrementalload #etlpipeline
Disclaimer: All snowflake-related learning materials and tutorial videos published in this channel are the personal opinions of the data engineering simplified team and they're neither authorized by nor associated with Snowflake, Inc.

[@aniketmishra1990]

This is such a cruel world. People like technical guruji, just read out the specs of a phone that's already out there on the internet on 1000s of websites and have 20M plus subscriptions. And here is a gem, who is explaining such good info in detail, and has 2k. But I'm sure hard work pays, and you have our backing bro. Please continue with the good work!

[@DataEngineering]

Thank you 🙏 for watching my video and sharing your feedback and your word of appreciation really means a lot to me. The snowflake community is still very small, compare to other tech stacks... so less views... and that's why I am sharing my knowledge, so the snowflake community can learn and grows with this. Thanks again @aniket and feel free to share this channel detail with your community and your circle. Your msg is pinned.

[@sanjayks001]

Army jawan gets paid less but cricketer are highly paid. It's just about viewership sponsorship and market trends. Irrespective of this let's just appreciate this guy for his hard work.

[@pratimab5936]

Really appreciate the level of content you have brought in this tutorial. And awe for the hard work you have taken to prepare this... Can't believe this you are providing for free. Thanks a lot for being generous.

[@pushkarratnaparkhi2205]

Thank You so much. Such high quality content for free, you are truly generous.

[@DataEngineering]

You're very welcome! ------ You can download this summary sheet in PDF version that has detail for all the chapters in one single file. Snowflake End To End Guide Cheat Sheet - bit.ly/43t5IPB

[@ankursingh730]

Couldn’t ask for more! Perfect explanation. Has all industry concepts. Thanks so so much!!!! You got a subscriber:)

[@DataEngineering]

Welcome aboard! you can find the playlist summary in this cheat sheet. (Snowflake End To End Guide Cheat Sheet - bit.ly/43t5IPB)

[@bourbon1860]

This is the best tutorial I've ever seen on Snowflake. Simply exceptional. Thanks sir.

[@rajeshbhosale2008]

Appreciate your meticulousness and enthusiasm in your tutorials! Thanks a lot! 👍🏻

[@DataEngineering]

My pleasure!

[@pratyushrout5823]

This is the best tutorial playlist I have watched on KZbin

[@DataEngineering]

glad you liked it.. and yes, I know many of us are not fully aware of snowpark Python API, if you want to manage snowflake more programatically.. you can watch my paid contents (data + code available) .. many folks don't know the power of snowpark... these 2 videos... will help you to broaden your knowledge.. These contents are available in discounted price for limited time.. (one for JSON and one for CSV).. it can automatically create DDL and DML and also run copy command... 1. www.udemy.com/course/snowpark-python-ingest-json-data-automatically-in-snowflake/?couponCode=NEWYEAR50 2. www.udemy.com/course/automatic-data-ingestion-using-snowflake-snowpark-python-api/?couponCode=NEWYEAR35

[@VenkatBadaofficial]

Finally the wait is over! 😀👍 Thank you!

[@DataEngineering]

More to come! and again thanks a lot for following and providing your feedback on regular basis. Helps me a lot to continue to share my knowledge.

[@snehalujalambe2737]

You have taken so much efforts for making a single concept understand.. I don't think i came across any video on any topic which has explanation as your videos have. I feel lucky that I wanted to study snowflake and I found your videos. Keep doing good work 🥺

[@ayeoh47]

feeling lucky I came across this course on YT, for FREE, and much better than paying for udacity

[@DataEngineering]

Thanks for your note if you would like to get all the video and unable to find what all are covered under different playlist, then you can download following cheatsheets. 🚀🚀 Cheat Sheets 🚀🚀 ➥ Complete Snowflake Tutorial rebrand.ly/maj4l6x ➥ Complete Snowflake JSON Guide rebrand.ly/d52cef

[@srinivaskolisetty5574]

I just went thru part of a video and i was impressed. Thank you for providing this. I need to renew my Snowpro certification. Excellent work.

[@DataEngineering]

Glad it was helpful!

[@AlinaVilkova_EpicSnail]

Very useful for understanding the whole data lifecycle from S3 to data mart in Snowflake. Thank you so much!

[@DataEngineering]

Thank you 🙏 for watching my video and your word of appreciation really means a lot to me.

[@ugandar123]

Amazing explanation without wasting even a second. I could understand the end to end process of ETL workflow in Snowflake. Thanks for such a great video!

[@DataEngineering]

Glad it was helpful! and yes, I know many of us are not fully aware of snowpark Python API, if you want to manage snowflake more programatically.. you can watch my paid contents (data + code available) .. many folks don't know the power of snowpark... these 2 videos... will help you to broaden your knowledge.. These contents are available in discounted price for limited time.. (one for JSON and one for CSV).. it can automatically create DDL and DML and also run copy command... 1. www.udemy.com/course/snowpark-python-ingest-json-data-automatically-in-snowflake/?couponCode=NEWYEAR50 2. www.udemy.com/course/automatic-data-ingestion-using-snowflake-snowpark-python-api/?couponCode=NEWYEAR35

[@joa0liveira92]

Hello, i am new at DE world, I am studying for the SnowflakePro Core. Let me tell you: this set of tutorials it is just amazing! (some small details maybe have changed/updated over the last 2 years, so it is good to complement your study with the Snowflake Documentation). This particular video of ETL Flow it is just awesome, thank you very much for show it, i specially love your practical approach. THANK YOU Data Enginnering Simplified! Please keep sharing and doing 'public' service :) P.S. If you want to expand your channel to dbt would be great!

[@jacquelinevo5462]

Very good tutorials! Happy to subscribe.

[@DataEngineering]

Awesome, thank you!

[@shussain44]

undoubtedly best snowflake course. not just teach command

[@DataEngineering]

welcome..and glad that you like my videos.

[@rpafacts5574]

Great video

[@DataEngineering]

Glad you enjoyed it.. and yes.. I know many of us are not fully aware of snowpark Python API, if you want to manage snowflake more programatically.. you can watch my paid contents (data + code available) .. many folks don't know the power of snowpark... these 2 videos... will help you to broaden your knowledge.. These contents are available in discounted price for limited time.. (one for JSON and one for CSV).. it can automatically create DDL and DML and also run copy command... 1. www.udemy.com/course/snowpark-python-ingest-json-data-automatically-in-snowflake/?couponCode=DIWALI50 2. www.udemy.com/course/automatic-data-ingestion-using-snowflake-snowpark-python-api/?couponCode=DIPAWALI35

[@thebiguy5044]

I am just wondering, how come you have less subscribers. I AM SURE ONE DAY YOU WILL HAVE MILLION's. BEST CHANNEL SO FAR...

[@DataEngineering]

Thanks for support ... if you could share this channel to your internal community and circle... rest youtube will do it jobs..

[@SP-db6sh]

Much better than Udemy n all ! Explained core concept, Design pattern, data model everything.... Thank you ...

[@DataEngineering]

You are most welcome

[@VijayNaramalli]

Awesomely explained

[@DataEngineering]

Glad you liked it

[@NTHDATA]

Such a wonderful video with clear cut explanation. Thanks for all your great content. Could you also please create an End to End ETL workflow in snowflake for Azure cloud as well. This will help lot of folks.

[@fahadmahmood7150]

Very useful to see how the continuous data load works under the hood with real time experience.Awesome work.!👏🏻👏🏻👏🏻I thoroughly enjoyed ur video and the way you put so much effort to larger audience is truly commendable.!

[@DataEngineering]

Glad you liked it @Fahad Mahmood, your word of appreciation really means a lot to me.

[@rajeshwarireddy5973]

@@DataEngineering 11aq

[@shailendravernekar2389]

Excellent !!!

[@DataEngineering]

Thank you 🙏 for watching my video and your word of appreciation really means a lot to me. ⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡ I have already published other knowledge series and snowflake certification videos and if you are interested, you can refer them. 🌐 Snowflake Complete Guide Playlist ➥ bit.ly/3iNTVGI 🌐 SnowPro Guide ➥ bit.ly/35S7Rcb 🌐 Snowflake SQL Series Playlist ➥ bit.ly/3AH6kCq 🌐 SnowPro Question Dump (300 questions) ➥ bit.ly/2ZLQm9E ⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡

[@sketchstudios05]

Its very good that you have covered end to end flow in this tutorial, if followed (which I did) step by step, concepts become very clear. Thanks. I would suggest to include the SQS notification part on Amazon S3 side in the Snowpipe related chapter(chapter 10), which will make that chapter complete. I got stuck over there when python script came in picture, as I was not knowing how to execute it and test.

[@jojibabug]

Very useful.. easy explanation and understanding. Much Apprecaite your thrive to share the knowledge to others.. Keep up the Great work

[@DataEngineering]

Glad it was helpful!

[@okaybumi]

hands down best tutorial I found on snowflake. wish I could get access to data files.

[@DataEngineering]

Thanks for your note..and glad you liked the content.... refer the description section of the video, it has link for blog page + data file link (git)

[@maghy_kethychannel]

one of the best snowflake content.plz try to make a complete portfolio video on data analytics on snowflake tables.

[@DataEngineering]

glad to know that you liked the contents here.. and yes, I know many of us are not fully aware of snowpark Python API, if you want to manage snowflake more programatically.. you can watch my paid contents (data + code available) .. many folks don't know the power of snowpark... these 2 videos... will help you to broaden your knowledge.. These contents are available in discounted price for limited time.. (one for JSON and one for CSV).. it can automatically create DDL and DML and also run copy command... 1. www.udemy.com/course/snowpark-python-ingest-json-data-automatically-in-snowflake/?couponCode=DIWALI50 2. www.udemy.com/course/automatic-data-ingestion-using-snowflake-snowpark-python-api/?couponCode=DIPAWALI35

[@melodiesbyravindra]

This is Just Brillant

[@DataEngineering]

Glad you linked it.. you can find this summary sheet which has all the chapter of this playlist. Snowflake End To End Guide Cheat Sheet - bit.ly/43t5IPB

[@subbareddyreddy2427]

Waiting for video finally yehhh

[@DataEngineering]

Thank you @subh Reddy for following me and sharing your valueable feedback. Helps me to continue with my effort and share my knowledge.

[@virendrasisodia1566]

Very nice session and the way ETL process has been explained

[@DataEngineering]

Glad you liked it

[@snehendudey7436]

Very useful, Thank you

[@DataEngineering]

Glad it was helpful! Thank you 🙏 for watching my video @Snehendu Dey and your word of appreciation really means a lot to me. ⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡ I have already published other knowledge series and snowflake certification videos and if you are interested, you can refer them. 🌐 Snowflake Complete Guide Playlist ➥ bit.ly/3iNTVGI 🌐 SnowPro Guide ➥ bit.ly/35S7Rcb 🌐 Snowflake SQL Series Playlist ➥ bit.ly/3AH6kCq 🌐 SnowPro Question Dump (300 questions) ➥ bit.ly/2ZLQm9E ⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡

[@sketchstudios05]

You are doing good job, we are with you. Keep it up. Very nice tutorial. Is it possible to load the data files so that it will be perfect to follow the steps.

[@vk1808]

Thanks that was very useful .

[@DataEngineering]

Glad it was helpful!

[@vivekshirsat9249]

I was so confused like i don’t even know how workflow of pipeline works this videos definitely helped me now i am planning to create my own project thanks to you sir!

[@DataEngineering]

great that you like it.. if you want to learn with lot of data.. you can also check my udemy courses. www.udemy.com/course/automatic-data-ingestion-using-snowflake-snowpark-python-api/?couponCode=NEW-YEAR-2024 www.udemy.com/course/snowpark-python-ingest-json-data-automatically-in-snowflake/?couponCode=NEW-YEAR-2024 www.udemy.com/course/snowflake-dynamic-table-masterclass-e2e-data-pipeline/?couponCode=AC7896594913CDFA6155

[@maestrejoseg]

Great lab regards...

[@DataEngineering]

glad you linked it.. You can find more content in this Snowflake End To End Guide Cheat Sheet - bit.ly/43t5IPB

[@vivekkumar-nw1qv]

Very Good explanations

[@DataEngineering]

glad you liked it..

[@shahidakhter1886]

No doubt one of the best video for learning Snowflake from the scratch. I have gone through all 26 videos so far and enjoying each of the videos. Wanted to know if there is a video on Data Masking policy is also available? Also, it might be too much to ask but I am also curious to know if you are planning to publish any video on Snowflake integration with DBT or DataOps or with any modern ELT/ETL Tools.

[@DataEngineering]

added into my list.. will try in future.

[@perumallashureshh1925]

Thank you very much

[@DataEngineering]

Thank you @Perumala Shureshh for following me, this helps me to continue with my effort and share my knowledge.

[@Share_IT81]

Very Good explanations to understand and implement snowflake features, all chapters are detailed with hands-on examples. Do you have any use case to explain entire snowflake job orchestration to inject on-prem and cloud storage data/files at one given time also can we schedule CLI commands using task?

[@islauddin]

Thank you for all your effort. I am quiet sure it must be very time consuming to do all this. Much apprecaited. Following additional questions: 1. Is this realistic to maintain with streams where data volume and velocity is high? Would a similar approach with a traditional COPY INTO command be a more realistic implementation in real world example? Could you elaborate? 2. Would it be possible to have an alert mechanism that can notify when a data load fails for any reason? Using Snowpipe and with batch COPY INTO approach? 3. How would one track and monitor these objects in a more manageable way on a day to day basis for the entire data mart which might have 10's of dimensions and multiple fact tables?

[@Buzzingfact]

ohh best please make more videos like this

[@DataEngineering]

Thank you @Bright Spark for following me and sharing your valueable feedback. Helps me to continue with my effort.

[@Buzzingfact]

@@DataEngineering could you provide access to the script

[@puneetsharma1431]

Great work, Appreciated for your hard work, How can we get this complete course html Page which you are following for explain each and every topic?

[@datastageable9749]

Thanks for the amazing content! btw still waiting to download the csv files!

[@DataEngineering]

Will load all the file content.. pls give me some time.

[@PTRVasudevan]

Appreciate for posting good videos like this for free and it helps all types of learners. Please keep continuing the great share of knowledge.

[@DataEngineering]

Thanks, will do!

[@mythzing7]

Thanks a lot sharing your knowledge. Great tutorial. I had a doubt; Towards the end, there was a file which has failed. Could you tell us what are the next steps? how to fix the issue? How to trigger the file load again in snowflake( assuming we are not editing the source file in s3 or reuploading to s3) ?

[@user-kc3dj5hx5n]

Thanks for concise and crisp explanation. Could you please let us know how to get complete history data as only sample records are provided along with scripts.

[@DataEngineering]

Thanks for your note... glad you liked the content. pls check the description for the data set, it is hard to keep the large data, need to check if I still have the large data set with me.

[@NeumsFor9]

Have you done any examples with periodic snapshot or base transaction facts? Are there any examples for handling early arriving facts and/or late arriving dimensions? I suppose those cases can be applied or inferred from these examples?

[@shanmugaraj8931]

pl upload a video doing some ETL transformation with any use case (End 2 end). Thanks for the video, really helpful.

[@DataEngineering]

Help me with a use case that you feel will be good to help larger audience. Drop your detailed requirement in my insta account (instagram.com/learn_dataengineering/)

[@dastagirivangaru752]

1.Why we are creating sequence(eg like customer_pk,order_pk etc) in each table in curated zone and what is the purpose of this . 2. we are using transient tables in landing zone and curated zone. 3.How we are identifying SCD or delta or incremental data in this ETL approach

[@DataEngineering]

The purpose of this video is to show how data flow and the actual data model might differ from project to project. 1. the pk columns are just to desmostrate and it has no specific meaning in the given use case. 2. Transient tables does not incur fail safe cost and that's why we used it. If churn rate is high, permanent table cost a lot. Watch my this video for detail kzbin.info/www/bejne/iWaxqIhopJd4rsk 3. watch the stream chapter kzbin.info/www/bejne/eomsYXp6iNx4lac before watching this video.

[@user-qe5gf3se6c]

Excellent Video! I had a doubt where can i get the dataset used in this tutorial?

[@DataEngineering]

Glad it was helpful!.. check the description of the video

[@rocknur]

Acharya devobhava🙏... You are a great teacher

[@DataEngineering]

Thanks a lot.. and glad you liked the videos and my contents..

[@rocknur]

@@DataEngineering yes.. They are truly good.. Can you please share your LinkedIn profile name/link if you don't mind 🙂...

[@rameshvtech1898]

Could you please share some more details on how to create an event on AWS to capture the Delta from a Source Table? I'm interested in loading only data from My source tables, but not the files from the S3 bucket, How to automate the load process?

[@NeumsFor9]

This is just as possible with Azure and Event Grid trigged storage events with ADF from an SFTP server as a source.......to ADLS for initial ingestion.....and then passed to snowpipe through event hub/storage queue/svc bus and snowpipe and integration object, correct? You should do an Azure version of this if you haven't already. I know Mohit Batra put one out on Pluralsight.....

[@MrZoomok]

Thanks for your lesson and where are the test csv files?

[@DataEngineering]

In the description

[@MsABCIndia]

Hi.. what is the alternative of stream if delta records are more than 10k.. as stream has limitation of 10k

[@suneelkumar-qu3xk]

This session really helps with ETL workflow. Thanks for providing such an amazing session. Actually, I tried to do the same practice which is shown in the session, but unfortunately, my snowflake has been hosted on Azure cloud. Due to this not getting the ARN number and auto ingest is not working. Could you please suggest how can I change the cloud from Azure to AWS? This will be very helpful for me. I tried to search from google, but could not get the correct solution.

[@DataEngineering]

Dont worry, you can watch my my ch-10 where you can also load continous data without any external stage kzbin.info/www/bejne/hn-uZWyJf9uLoKc toppertips.com/continuous-data-loading-ch10

[@satijena5790]

Excellent. I have subscribed and started going over the play list. Just wondering whether you have similar training videos of Snowflake in Azure as well please? These days...Data engineers without knowledge of Snowflake is not recognised that well in UK job market.

[@DataEngineering]

was not aware if UK Job market is really looking for snowflake aware developer, but love to know why? is it limited to data engineering domain or all other application development domains... I have not really concentrated on Azure, but it looks Azure+Snowflake partnership is growing and recently they published a video on it.. will focus on Azure also.. and will soon release videos on Snowflake+Azure.

[@satijena5790]

@@DataEngineering almost every data engineering and data architect job in UK is asking for Snowflake experience. Do you provide virtual training too ?

[@trumper9298]

First viewer

[@DataEngineering]

Thanks @trumper, really appreciate this and happy to hear that my content is being followed and helping community.

[@sujatasbhosale]

Great video sir. Really appreciate your efforts. Just one thing need to understand if delta data is added into same csv file from which full load is done, how it will handle ,will it process only changed data?

[@DataEngineering]

Snowflake will not handle that.. it need to be taken care with manual logic.

[@CamilaChavesNaranjo]

Great videos! I have been following along. However, when I go to your website to download the data, I only find the sample files (around 20 rows). When loading to the consumption_zone.order_fact I get 0 rows inserted as the sample files don't probably have the IDs in common. I've checked my code (which I just copied from the website) and it is definitely the data. Can you send a link to the actual data (or at least larger sample)?

[@ashokraj9741]

Wonderful explanation!! thank you so much.. What is a good way to bring historic data from Oracle to Snowflake? Is AWS Snowball the option? any good recommendation please?

[@DataEngineering]

Thank you 🙏 for watching my video and your word of appreciation really means a lot to me. Loading data from Oracle to Snowflake, need data to be moved to S3 first and then it has to be moved to Snowflake, this can be done via different tool available based on data volume. There are other cloud tools like fivetran which can do history load as well as delta/cdc load and it is pipeline/data-ingesting as service platform.

[@ashokraj9741]

@@DataEngineering thank you, if we are leveraging existing Azure setup, do you recommend ADF (Azure Data Factory) to stage the data in ADLS (Data lake store), if the data volume is ~200 TB from Oracle? or any other recommendations plz?

[@RajeshKuruguntla]

at 50:44 when you added new record and existing record how does it work in snowflakes that it identifies the existing records . we have not defined any constraint and the constraint does not work in snowflakes AFAIK. Could you please clarify ?

[@vinothkannaramsingh8224]

31:57 on the landing zone streams you are mentioning append_only = TRUE (cdc captures only inserted rows) 33:15 what's the purpose of update query ? cdc captures only inserted rows do we really need update query in 33:15 ?

[@ranjeethrikkala6344]

Hi Sir , how do i download the source data to follow along in this tutorial ? you provided only sample data in the SQL scripts link

[@wailcareer]

that is a really amazing effort really appreciated, I have a question, and perhaps I've misconstrued something. When establishing the data flow from the landing zone to the curated zone, a parameter for "append only" is set. This implies that any updates made to the landing table won't be integrated into the curated table (only new inserts). Specifically, the logic task created to run every minute involves two checks: one for matched updates and the other for unmatched entries as new records. With the "append only" setting, only new inserts will be present in the stream, ensuring that no matched data appears in the created stream. So, I'm curious about the rationale behind adding the "append only" parameter in this scenario. -

[@DataEngineering]

in real life world, your data will not land to snowflake directly, it will come as new file into external stage location ... and that external stage location will be S3 or ADLS Gen2, that is also immutable storage.. so you need to place a new file.. even if it is an update.. so append only option is the right configuration... and if you would like to know more about it.. prefer to watch my end to end project.. that is more on Snowflake Snowpark.. but will help you to answer this question.. kzbin.info/www/bejne/Z5umamuOhtx1kNk ------ and yes, I know many of us are not fully aware of snowpark Python API, if you want to manage snowflake more programatically.. you can watch my paid contents (data + code available) .. many folks don't know the power of snowpark... these 2 videos... will help you to broaden your knowledge.. These contents are available in discounted price for limited time.. (one for JSON and one for CSV).. it can automatically create DDL and DML and also run copy command... 1. www.udemy.com/course/snowpark-python-ingest-json-data-automatically-in-snowflake/?couponCode=DIWALI50 2. www.udemy.com/course/automatic-data-ingestion-using-snowflake-snowpark-python-api/?couponCode=DIPAWALI35

[@CuongDao-fn6ok]

Thank you, i cleared the exam.

[@DataEngineering]

Well done!

[@rayudusadanala6433]

Can you please provide csv files that we need to import into landing zone

[@sureshdarla5540]

and the syntax is => AFTER task_name right ? , and not AFTER = task_name (timestamp : 35:18 part6)

[@alanchala]

Very useful video. I have couple of questions. How can we get data from other external source systems(Oracle, Excel, CSV, Teradata, MYSQL, DB2) like following ETL tools informatica, streamsets, or Datastage ..etc.. Snowflake may useful to read data from S3 backet to load into Cloud snowflake DB and from there data may be available for any other purpose. But initial step we need to use other ETL tool to get data into S3 bucket right? in this case we need to pay the money for other ETL tool as well right? Can you please suggest how snowflake is better than other cloud DB's and other ETL tools?

[@DataEngineering]

To collect data (extraction) and make it available to snowflake's external stage need similar tools like StreamSet or Fivetran or Matillion. The extraction or data collection part is missing. I would suggest to watch my data loading playlist kzbin.info/aero/PLba2xJ7yxHB6NPEv8pp7j3zWibLZzwjvO

[@ViralSPatel-xr2bj]

All the sessions are really helpful. Thank you so much. need sample data files for history load? I don't see any link or dataset or file .

[@DataEngineering]

Working on them and will soon will make it part of video description.

[@sahishnutaatosh2245]

Could you please provide the data set for this project?

[@user-tv4gn5ss7u]

Where you keep the code?Could you please let me know

[@prasa1]

Hi.. Thanks for your excellent tutorials on Snowflake .. I have a question on this video .. When you are creating landing_order_stream as append only stream , you'll only see new orders in the stream . Subsequently why are you trying to do a merge operation that looks for both inserts and updates .. You only need to worry about inserts .. Please correct me if my understanding is wrong

[@DataEngineering]

They are just examples for demo purpose, the real life scenario and implementation depends on use case.

[@haridatta5495]

in the landing_order_stream it's only inserted, but when creating a task on this table, he created a merge statement in which we handle inserts and updates. So in the next phase, we need to handle updates...note that Snowflake treats metadata$update True only when data is loaded using update syntax.

[@sureshdarla5540]

In part5, we are loading data from stream object of landing zone to curated zone . Q. We are wring Update command in task, but our stream was defined with APPEND_ONLY MODE , how does it tracks updates/deletes ?

[@sureshdarla5540]

as stream stores inserted new records with APPEND_ONLY mode then there is no necessity of using UPDATE right ? and we could insert all the new records into curated tables directly through task and Stream combination whenever there is change . please correct me here.

[@sajjanshrestha888]

How can I download the csv files that you used? Please help

[@anjalikp2680]

Hi..if it's a one time load..means right from the source,should we go through all these landing zone and curated zone? Can't we directly load it to the consumption zone?

[@DataEngineering]

Yes, it can be done, if your data is curated and ready to fit into consumption zone... like 1st time migration project..load data to the respective table and then your cdc or incremental moves through left to right as shown in this table.. thanks for your note.. it helps me others to understand different scenarios...

[@gshabareesh3207]

Why we are moving data from landing zone to curated zone and to consumption zone and what are the changes

[@DataEngineering]

In my demo, no specific changes are done.. but in complex and large project, you will see lot of transformation logic, but it is not must to have so many layer..

[@Yassou1985]

Waouh dude, this is really impressive and very clear ! thank you so much ! I have one question : is it possible to replace AWS S3 stage by 'internal stage' for education purpose and test ? I would like to load manually 'delta' csv files' and the pipeline do the work.

[@DataEngineering]

yes you can do it.. watch my latest video that explain how to load large data set into internal storage kzbin.info/www/bejne/Z5umamuOhtx1kNk (End to End ETL Using Snowpark)

[@Yassou1985]

@@DataEngineering i will for sure. Thank you

[@Yassou1985]

@@DataEngineering On last question, on your last schema, why you didn't build foreign key constraint on fact table for your dimensions tables ?

[@satijena5790]

Can a task from one schema call another task from a different schema? Which version or release this videos been created ? Is it still the case now with the latest versions of Snowflake?

[@DataEngineering]

as far as I know, this was one of the limitation and this has been added a new feature, I am not aware of... Will review the docs and will revisit my content and will adapt it if needed. You can download this master cheat sheet, that can help you to identify which all topics and sub-topics are covered under different playlist and different videos. 🚀🚀 Cheat Sheets 🚀🚀 ➥ Complete Snowflake Tutorial rebrand.ly/maj4l6x ➥ Complete Snowflake JSON Guide rebrand.ly/d52cef

[@phanikumar3014]

Could you please share the files that you loaded in the Three Tables

[@mpgaming3254]

So part 1-3 is the extraction, part 4-6 is the transformation and part 7-8 is load

[@abhijitvernekar593]

where is the CSV file for customer,order and item?

[@pranjalinagarkar3176]

All the sessions are really helpful. Thank you so much. Could you please let me know where can I get the sample data files for history load? I don't see any file to download on your site. if you could help here.

[@DataEngineering]

Glad you like them! you can refer the link and end of the blog, you have sample files content, not sure if helps you or not. toppertips.com/snowflake-etl-example-ch19-part01

[@pranjalinagarkar3176]

@@DataEngineering Thank you! I am following the current practice video. while inserting data into consumption_zone.order_fact table, seems like other schema tables are not accessible. I tried several grant queries but nothing worked. could you please help?

[@vishaltalele9402]

@@DataEngineering But How to download them?

[@papaziek6636]

Thanks a lot for the video. I tried loading some sample CSV file from my local drive into a table I created in Snowflake. I get the following. " There was an error while trying to stage "Customer.csv". Reason: Unable to upload the file to Azure Storage". The file I am trying to load is Customer.csv. Any clues as to why this is happening will be greatly appreciated. Cheers

[@DataEngineering]

Strange error.. how are you uploading the csv file? Via WebUI?

[@singh_abhishek1429]

Thank for doing this great session. it helps a lot. however I am getting error "S3 bucket 'toppertips' does not exist or not authorized" executing SQL.

[@DataEngineering]

Hello Abhishek, the S3 bucket created in my video is the S3 bucket I created for this tutorial, you have to replace it with your own S3 bucket where you have access to. If you need to understand how it works, refer ch-10 (kzbin.info/www/bejne/hn-uZWyJf9uLoKc)

[@singh_abhishek1429]

@@DataEngineering -Thank you I will do. is it possible for you to provide you one time data load files?

[@DataEngineering]

@@singh_abhishek1429 can you check the link.. add 1st few lines for history and delta load toppertips.com/snowflake-etl-example-ch19-part01

[@com567]

Great videos and explanation .when inserting data from LANDING_CUSTOMER to CURATED_CUSTOMER, am getting an error "Numeric value 'BIRTH_DAY' is not recognized" ( conversion from varchar to number) . Anyway to avoid it?

[@DataEngineering]

you can try using cast function or double-semi-colon.. not sure why it is causing an issue when the demo is build on the data .. so it should not be an issue

[@user-rn9ru5hw3j]

can you explain the data migration from aws to snowflake and what tools are required for that

[@DataEngineering]

When you say AWS .. what all tool are being used in AWS side?

[@user-rn9ru5hw3j]

@@DataEngineering AWS service like migration MDS, lambda and Glue

[@qiyuef]

Can you please explain why we need extra curated layer?

[@DataEngineering]

Many standard data projects have curated layer and in this layer data is cleaned, curated & reconciled before the data is modelled (dimension modeling etc) for larger use. For example, you are getting employee data from two different sources and you want to make sure that they are reconciled before they can be used by business, in that case you need a middle layer and you can name it as curated layer. Hope this clarifies.

[@deepakbhutekar5450]

sir where can i access the dataset for item,order and customer tables used in ETL flow..???

[@DataEngineering]

check the description.. and yes, I know many of us are not fully aware of snowpark Python API, if you want to manage snowflake more programatically.. you can watch my paid contents (data + code available) .. many folks don't know the power of snowpark... these 2 videos... will help you to broaden your knowledge.. These contents are available in udemy.. (one for JSON and one for CSV).. it can automatically create DDL and DML and also run copy command... 1. www.udemy.com/course/snowpark-python-ingest-json-data-automatically-in-snowflake/ 2. www.udemy.com/course/automatic-data-ingestion-using-snowflake-snowpark-python-api/

[@karthikkalavena9918]

Could you provide the complete data set of orders,customers and items,

[@DataEngineering]

the data link is available in the description section, please check and let me know... it is hard to put very large dataset in git or any other system.... so sample data set is provided. and yes, if you want to manage snowflake more programatically.. you can watch my paid contents .. many folks don't know the power of snowpark... these 2 videos... will help you to broaden your knowledge.. These contents are available in discounted price for limited time.. (one for JSON and one for CSV).. it can automatically create DDL and DML and also run copy command... 1. www.udemy.com/course/snowpark-python-ingest-json-data-automatically-in-snowflake/?couponCode=DIWALI50 2. www.udemy.com/course/automatic-data-ingestion-using-snowflake-snowpark-python-api/?couponCode=DIPAWALI35

[@pranjalinagarkar3176]

which is the best way/tool to move daily load from microsoft sql server to S3. could you please help here.

[@DataEngineering]

there is no one solution to move data to S3 from SQL server. If you have Azure subscription, then ADF is the best way to move data from SQL server to S3. DMS from AWS is another service which helps to move data from RDBMS to S3 to Snowflake. Fivetran is another cloud native tool which can be used to move data to snowflake directly. Streamset is another tool which can move your data via JDBC. If you share how much data set (schema & tables) you have and size of the data. You can also write CLI scripts to move the data by taking the csv dump but that may take time.

[@kirande4700]

Really great video series about snowflake. Deep dive into concepts and clear explanation. Only slight bit of constructive criticism. To reach out to more native English speakers, please avoid influence of your mother tongue. I am non native english speaker myself living abroad but it's so distracting certain times when there is repeated use "E" before most of the sentences. something like "e schemas (vs schemas), " estart date (vs start date) " . no intention of hurting your feelings or confidence , just suggesting little improvement next time. all the best.

[@DataEngineering]

I wish, I had this feedback long back... I will surely pay attention and ensure my future videos will be better to cater large english and native english speaker. Do you have any recommendation (free or paid material), so I can watch them and start working towards it.

[@kirande4700]

@@DataEngineering just follow BBC news or CNN news for good pronunciation. Also many Indian techies are now CEOs of companies like Microsoft, Adobe ..etc . Follow their interviews. You will learn that way .

[@avneet1411]

Can you please share how the solution will change if we have to use internal named stage instead of external?

[@DataEngineering]

Yes, it can be done using Snowpipe APIs with python program which was covered in ch-9 to ch-11

[@a2zhi976]

one confusion ! all the sql's how we will call in production?. it is not possible to have webUI open in Production .. can you please help on this

[@DataEngineering]

You need to call them using task.. or you need to call it using some kind of tools or orchestration like Airflow or ADF... but if you want to use snowflake native objects.. then it has to be task..

[@nchandradeep8220]

Can you give me some csv files of having more data like you mentioned in the video like 10k rows,2.7k rows and 8.9k rows like that

[@shabbiransari7584]

Yes I also need the same. Please let me know if you find one

[@abhijitvernekar593]

@dataengineering I am new to snowflake, can you please share link to all csv files I am unable to find it in description link

[@DataEngineering]

it is available in the description.. pls check once again

[@shabbiransari7584]

@@DataEngineering It is not available in description, on website only 20 to 22 records are given. Please share the csv

[@mianshahmirali1607]

Thanks for the amazing videos but kindly share the Complete Dataset otherwise it's not possible for us to practice this ETL pipeline. The sample data which you have put on your website consists of only 20 rows. Many people requested for this in the past but you didn't share.

[@DataEngineering]

Will check and will do it as soon as possible

[@shashankm2859]

@@DataEngineering Can you please share the link for complete dataset? Can't able to complete the exercise because of unavailability of the dataset.

[@Lo-jn8zw]

where can i find the 3-6 delta file csv its not on gitlab? @Data Engineering

[@DataEngineering]

refer the blog page ... it has git link attached.

[@Lo-jn8zw]

@@DataEngineering its not even on the blog page the gitlab link only has history and delta 1-2

[@SomenPlay]

I am not able to find the complete initial data set for loading In website it just has 20 rows and when copied it has several errors in format . Please advice on getting correct files for testing

[@DataEngineering]

check the desc.. and blog page.. it has link for gitlab location.

[@SomenPlay]

Hi I could not find the gitlab location near sample data

[@guptagurumukh00]

Hi can u pls share the csv files also