#13 | Micro Partitions & Data Clustering In Snowflake | Snowflake Hands-on Tutorial

[@jacksonjulian5968]

Thankyou for your selfless service, surely these video series will always be the master guide for all those who are going to learn Snowflake in future

[@DataEngineering]

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

[@saltydog996]

I have difficulty understanding the concept of the documentation. This tutorial saved me. Thank you.

[@DataEngineering]

Glad it was helpful!

[@pkphuloria]

Best Data Engineering channel in You tube for Snowflake. Excellent !

[@DataEngineering]

Thank you 🙏 @ pradeep phuloria 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 ⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡

[@wernerzirkel2753]

Thanks for sharing these videos. I have been searching for a while for Snowflake training videos and event considered buying a training. But this series here is perfect, condensed, sales-free information. 100 % what I need to understand this technoogy. Will recommend this to other colleagues.

[@DataEngineering]

Glad you liked it... and thanks for sharing it with your colleagues and your community.. 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

[@sujathapullareddy7726]

Thank you very much, I could understand micro-partitioning very well now., so well explained.!

[@DataEngineering]

Glad it helped!

[@SowmyaAS-x7t]

Excellent explanation.

[@DataEngineering]

Glad you think so!

[@alexsanders7803]

Thank you for the detailed explanation

[@DataEngineering]

Glad it was helpful!

[@maheshbabu6925]

Nice explanation towards real time examples

[@DataEngineering]

Glad you liked it. Thank you 🙏 @Mahesh Babu, 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 ⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡

[@karthikeyanudayakumar9553]

Excellent explanation 🎇

[@gchillam]

Excellent presentation of Micro-Partitions. However, one thing to note is that the max size on a micro-partition is 16Mb, not 500Mb. This is easy to confirm. If you take the micro-partitions count from the SYSTEM$CLUSTERING_INFORMATION for a given table and then pull the size of the table from the tables view in Information_schema. Then divide the table size by the micro-partitions, and you get an avg. Micro-Partition size. If you look at lots of tables, you will find that most tables' average micro-partitions are usually smaller than 16Mb.

[@DataEngineering]

Excellent observation.... never thought in that direction. As per snowflake documentation.. "Each micro-partition contains between 50 MB and 500 MB of uncompressed data" and I am not sure if compress & un-compressed playing a role here.. Thanks again for your feedback and your word of appreciation really means a lot to me.

[@pratikparbhane8677]

for compressed partition its 16MB and uncompressed its 50-500MB

[@DataEngineering]

@@pratikparbhane8677 Snowflake does not say if compressed data will be 16Mb or more or less. For exam point of view, it should be between 50-500Mb as per documentation. We can perform some trial and errror and infer if it would be 8Mb or 16Mb etc.. I hope this clarifies.

[@gchillam]

@@DataEngineering Well from working with Snowflake since their beginning the MAX size of a micro-partition is 16mb. That used to be on the exam. Regardless if it still is on the exam it is very important to understand how micro-partitions really work. I specialize in optimizing Snowflake for very large implementations and understanding how well the micro-partitions are getting packed is a very big deal for performance. I bring this up as it is so often overlooked and misunderstood, but is the foundation for performance.

[@s4dgo4t]

@@gchillam It's clearly stated in the SF doc that it is 16 Mb "compressed" and 50-500Mb in their uncompressed form.

[@sun15220]

Thank you for the videos. Very well structured curriculum.

[@DataEngineering]

Glad you like them!

[@thamilkadavarayar6707]

Another good introduction about micro partitioning... keep up with your hard work putting all these information for us. THANK YOU.

[@DataEngineering]

My pleasure!

[@brajamajumder4325]

Thanks for Sharing , great inside

[@amitjaiswal781]

Amazing session Thank you

[@DataEngineering]

Thank you so much!

[@mdwasim-cv2ig]

Thanks for the such an easy explanation, watching all your videos as i am going to appear Certification exam. Please guide

[@abdullahsiddique7787]

if cluster key is not defined explicitly , snowflake creates micro partitions as per the order in which data comes for loading. i dont think it automatically orders as per DOB in this example. It will create micro partition as the per order of data loaded . You can do a quick insert with that dataset and check once.

[@DataEngineering]

You are right. I have just taken a simple demo data and what you say is right.. meanwhile let me check the actual behaviour as you explained.

[@abdullahsiddique7787]

@@DataEngineering sure

[@davidsun5648]

Thanks for the exceptional presentation. One quick question: where are micro partitions physically stored? I think it should be cloud storage(e.g., Azure Blob), right? Thanks in advance.

[@RajanieshKaushikk]

YOU are TOOOOO Good...Thanks a lot for this wonderful video!!

[@DataEngineering]

You are welcome

[@raghuram6264]

Very good efforts sir. So helpful in real time projects. Are you providing any training classes.

[@DataEngineering]

No, I don't provide any training and I share my knowledge and my experience via this channel to help snowflake community. 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 ⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡

[@MexiqueInc]

Hello , currently on a mission to finish all the videos on 2024. Please include all the data use for the procedures. Been following along all the videos with the new UI is a challenge by itself. Would appreciate it I could find the files on the description. Thanks for your awesome job.

[@chaitanyakrishna5873]

Thank You for all of your Great Efforts. Extraordinary Explanation & Nowhere i found such in-depth explanation..Very soon i am going to take SnowPro Certification

[@DataEngineering]

You are most welcome

[@shayankabasi160]

Awesome course, best pubic Snowflake course in youtube. @Data Engineering Simplified - could you please advise when the chapter -14 time travel will be published.

[@DataEngineering]

Thank you 🙏 for watching my video and your word of appreciation really means a lot to me. It is coming anything in next 7 days @Shayan.

[@DataEngineering]

@Shayan Kabasi, time travel video is out now (kzbin.info/www/bejne/b5ybcneOqtObf80)

[@subodhagrawal4087]

Amazing you are teaching like paid course.

[@DataEngineering]

feel free to review my Udemy courses www.udemy.com/user/data-engineering-simplified/

[@RAMANKUMARKHARCHE]

Thank you so much. I got the idea about the micro partitioning. And I have subscribed your channel as well.😀 Just one other thing can you share your sample database structure with data which is used in video. That would be super helpful. 🙏

[@YDENTERMAINT]

Hi excellent tutorial thank you . I am trying to access the SQL scripts , but get a 401. Is this as intended ?

[@krist17860]

Hi excellent tutorial thank you . I am trying to access the SQL scripts , but get a 401. Is this as intended ?

[@vinothkannaramsingh8224]

So, external/internal stage data will have micro partition (or) when loading into table it will have micro partition ?

[@shivamrai162]

Not able to download any note from your site. Kindly update it. I need to revisit every time coz i keep forgetting concept. I need something to refer to. Thank you. Great content

[@Niteshkumar-pq5on]

Thanks for the very informative and useful videos on Snowflake. I am unable to access the SQL codes for the hands on. Could you please help?

[@varunjain2645]

dhanywad bhaiii..its been very help help in understanding fine concepts ...WILL CONNECT ONCE I LL CRACK THE INTEVIEW..

[@DataEngineering]

All the best for your exam.

[@auravivianaduartehernandez820]

Hello @DataEngineering, excellent content however, it would be good if you specify the updated links, they are already broken

[@kothandans8]

Very clearly explained and very useful . Can you please explain about lambda functions used in snowflake batch data ingestion process?

[@DataEngineering]

Thanks for watching my video. Snowflake does not have any lamda function, it is available in AWS and not in snowflake. Snowflake has task and that will be available in ch-18 and will be published soon.

[@kothandans8]

@@DataEngineering Thank you!

[@kumarsingh1741]

Can you please post the site from where you inserted the data in the tables

[@vedantshirodkar]

Great video sir. I have one doubt. How much time Snowflake would take to implement manual/custom clustering since it involves reshuffling of data ? Is it created instantly? Any other way to get the status of manually clustered table?

[@DataEngineering]

there is no such concept of manual re-shuffling of data.

[@raviagarwal9834]

Hello...I am looking for online training in Snowflake along with AWS....let me know if you conduct these

[@saraneegupta7426]

What is the difference of scanning data by row or by column..what advantage columnar db has

[@PriyaSharma-ci2kh]

could you pls explain this line Micro-partitions can overlap in their range of values, which, combined with their uniformly small size, helps prevent skew?How it prevent skew

[@DataEngineering]

Snowflake does not reveal how micro partition works internally and how does it perform all management stuff to optimize the storage.. If you have you gone through my cloud server layer chapter (ch-3 kzbin.info/www/bejne/f6DGlZqLd55ngbM), it has all the statistics and it decides how to organize the micro partition with overlap or without overlaop and make sure that data is not skewed.... But thanks for the note and I will do some additional research to add more detail in a separate video in future.

[@ameyabapat9090]

In case of cdc/delta, 10:00 to 13:00 once they create new partition, they would need to update the older partition which might contain data which was updated during CDC and become stale. Is it correct?

[@DataEngineering]

Micro-partitions are immutable and once it is created, it can not be changed... and if there are lot of updated or delete happening, snowflake query (the existing one which was running fast) gets slower..

[@vvkwadhwa]

too gud tutorial and your knowledge and the explaination way is awesome. Request you to club all of your videos at single path because still few more concepts details are needed. Please email or share details ASAP. Thanks a lot ....

[@DataEngineering]

Thank you 🙏 for watching my video and your word of appreciation really means a lot to me. The playlist has already having all published 14 chapter as on today. You can check it 🌐 Snowflake Complete Guide Playlist ➥ bit.ly/3iNTVGI

[@bidyadharapanda552]

So when we are adding clustering key columns it is taking more time and it is scanning more file size of data.. so how is it a benefit?

[@DataEngineering]

clustering narrow down the scanning... it picks only those partition that has that specific data set.. and avoid full scanning.

[@rameshbaburamachandran2431]

In cluster depth realtime calculation couldnt find how you are getting average depth as 3.69, could you pls give the details?

[@laurent_bocquet]

What will the performance be if you query your table using the city, when it is clustered by date of birth ?

[@ameyabapat9090]

does reclustering recreate the micro partitions as per the latest data ?

[@DataEngineering]

latest data create new micro partitions.. it does not change the existing micro partition..

[@SenthilPrakashM-d7b]

If the micro partitions are immutable, how updating/deleting an existing record in micro partition works.? By doing deletion will the space in micro partition be reused?

[@vedantshirodkar]

Deletion would require recreation of entire partion which contains that record.

[@b123786]

any video that covers bytes sent over the network? or is that a indication that you need a bigger warehouse

[@DataEngineering]

so far I have not touched upon that topic.. made a note of it.. will review and will see if I can make some video around it... Thanks for sharing with me and community... would like to know what challenges you have..so I can suggest more..

[@b123786]

@@DataEngineering so my query was scanning thought a large dataset billions of rows using 2xl cluster and the recommendation was to use 4xl since large amount of data was from remote storage ... would you say that is accurate

[@swarnalathabanala1665]

If CDC doesn't update existing partitions, how will the delta consolidation works? could you explain with clear example

[@DataEngineering]

Micro-partition has not direct link with CDC .. if there is lot of churn be it CDCC or any other update.. you can run a process to re-adjust the partition specially if query performance degrade.. if you would like to understand the CDC behavior.. snowflake has a much nicer way to implement it.. ➥ Chapter-17 Snowflake Streams & Change Data Capture 🌐 kzbin.info/www/bejne/eomsYXp6iNx4lac ➥ Chapter-19 ETL (Data Pipeline) in Snowflake 🌐 kzbin.info/www/bejne/b3fIm519i7-lhsk

[@gokukanishka]

For the auto clustered tables , when we dont provide cluster by clause … how do we know which column snowflake choose to partition it

[@DataEngineering]

There is no supporting document available to answer this question. Will search and try to find it out..

[@wernerzirkel2753]

one question, az 27:18 you are using the command "alter table t2_order_dt" suspend recluster. So I see that Snowflake is reclustering automatically by default but at the same time the micro partitions are immutable. Does it create new micropartitions and delete old ones ?

[@DataEngineering]

yes.. it does

[@majakmasti3592]

Sir if u teach in Hindi. Then I'm sure you are reach shortly 1M subscriber. Bcz your teaching style is good.

[@DataEngineering]

Thanks for your note... this is 3rd request about Hindi videos. I can also make video in Hindi.. I generally explain in very simple English, what is that you find it challenging with my video being in English (share your thought in my insta account instagram.com/learn_dataengineering/) Sabhi kuch to technical words hi hain... Hindi me samajhane me bhi lagabhag vaisa hi lagega... to kya challenge hai audience ko English me? Please help with your thought..so I can start making in Hindi too...

[@majakmasti3592]

@@DataEngineering deeply understanding sirf Hindi mein hi hoga Sir.. most of people in India is highly or deeply understanding in Hindi. Learning ke liye language ka restriction nahi hona chaiye ... Aisa mera manana hai

[@DataEngineering]

@@majakmasti3592 make sense.. started planning ... jaldi hi soch ke video release karunga.. thanks again for your note..

[@dineshkumarnagarajan200]

Wow!! Thanks for the detailed explanation.. Truly appreciate the hard work put behind this... Can you comment why cant i access the website or the sql scripts, it says 404 forbidden.

[@DataEngineering]

Let me check, will fix the website issue 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

[@LucianMoldovanu]

Hi. Wasn't it expected that the query time would be lower when you queried from the third table, the one clustered by order priority? This was not seen, and the time (as well as bytes scanned) were actually the highest ones when using the third table.

[@DataEngineering]

Will check and come back.

[@sgnaneswari2902]

Hi I am loading data from S3 to snowflake using copy command, but for the first time it is giving me like copy executed with 0 files processed.

[@DataEngineering]

Thanks for your query @S gnaneswari. You can check copy history or pipe history table for any error. There could be two reason, the 1st that the file already loaded and when you try to load the same file, it does not load any duplicate data, 2nd, there might be some error which you can validate by going to information schema tables. Do let me know if this solve the proble or watch Ch-10 (kzbin.info/www/bejne/hn-uZWyJf9uLoKc)

[@spacesolutions9656]

Can you please share SQL scripts

[@DataEngineering]

will release the SQLs soon.. via my website..

[@panrutilife]

It is very nice Sir, Thank you . how did you create this Treeview chart. any software for that. __/\__

[@DataEngineering]

Thank you 🙏 for watching my video and your word of appreciation really means a lot to me. It is done via mind mapping too, generally used for brain storming. ⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡ 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 ⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡

[@livenotlive]

@@DataEngineering Why the question list is private?

[@DataEngineering]

@@livenotlive pls check this new playlist kzbin.info/aero/PLba2xJ7yxHB5X2CMe7qZZu-V4LxNE1HbF

[@livethecolourfulworld]

Hello Sir, I am vikram singh for Delhi. Watch your video for snowflake. I want to be a do certification exam for snowflake and I am working in .Net developer for last 15 years. So please advise me change a profile for right decision or not

[@DataEngineering]

If you don't have any experience on data engineering domain and if you would like to come to data engineering space, along side snowflake, also gain knowledge about data engineering space. You can watch all the playlist and get an idea, if this make sense to you or not. It is hard to say in black & white that changing profile from x tech to Snowflake will make a person successfuly. But, it is true that Snowflake has much more brighter future than .Net

[@livethecolourfulworld]

Thanks

[@vishnuramjatin4898]

How is this different from indexing?

[@DataEngineering]

Indexing is a very different concept and micro-partition is a very difference concept. But if you see that micro-partition file's metadata is stored in cloud service like (similar to indexing).

[@angelnadar1209]

Snowflake automatically selects a cluster key for a table ,so on what basis does snowflake selects the cluster key?

[@DataEngineering]

When data is loaded, snowflake cloud service layer analyze the data and select a best approach to store it.. since it is not an open source tech, I did not find any doc around it.

[@saraneegupta7426]

When a data gets deleted... What happenes with the MP...

[@vinothkannaramsingh8224]

1. In this video you are saying NO clustering works efficiently than manual specifying clustering key ?? what's the takeaway ?

[@abhijeet2218]

unale to download anything please make it available

[@navneetkumar-rr4up]

Sir,please help with this 1. We have micro-partition (automatic by SF) then why we need clustering key.? 2. If we change the clustering key in the table , MP will not change (immutable) then again why are changing clustering key? 3. when you didnot provide the cluster key for the first table, automatic clustering was OFF but still MP was working then again why do we need clustering key? I hope you reply soon, my test is on wednesday.

[@DataEngineering]

Hi Navneet, here is my answer 1. Micropartition is a kind of file format (like parquet or ORC) and whether you have cluster key enabled or not, internal storage in snowflake is via Micropartition. 2. If we change the cluster key, the new micro partitions will be written and old will be destroyed and it is an expensive operation. 3. Clustering key is to how the fields will be organized in rows and columns withing micro-partition and if you would like to understand it better, try to focucs on overlap concept in my video and it will be clear to you. The exam will not go that deep and I will suggest to try my micro-partition question paper. kzbin.info/www/bejne/h364eopuhNl8bMU if you have any further query, drop a note again. Wish you all the best for your exam

[@lakshmikanththopuri5823]

can we load pdf files into snowflake

[@DataEngineering]

The question is "Why would you load PDF in snowflake".. do you want to use snowflake as storage or as database. Technically, if the PDF size is less than 8Mb, then you can store the PDF as binaries. Watch episode-7 for more detail and for data type kzbin.info/www/bejne/hpqWq2SErqeXY7s

[@lakshmiprabha2449]

May i know why the date for first query is changed (2020-01-02) whereas the dates in the other 2 queries are the same (2020-01-01). Will that not retrieve different rows ? Video at 23 min 59 sec.

[@DataEngineering]

it is recording issue.. but when you go to the query history.. the query history shows the correct dates.. but will checkc once again.. and confirm Thanks for your observation and sharing it with all of us..

[@rajeshraxz9571]

SQL script shows error , we can't access this one Pls help on that

[@DataEngineering]

let me check.. there is some issue in my website.. will fix it.

[@mohammedvahid5099]

Voice is very low ...

[@DataEngineering]

Strange, nobody has ever complained about the low voice... can you check in a difference device.

[@nityanandkore]

This is best playlist.. I am eagerly waiting for rest all videos.. kudos to you.. by any chance can you share yours LinkedIn profile to connect

[@DataEngineering]

Will upload soon

[@jayanthkaja]

Thank you it really helped us and understand about snowflake platform. Once again thank you for selfless service...