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 ⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡
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.
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
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.
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 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.
@@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.
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 ⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡
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.
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. 🙏
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.
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.
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
Awesome course, best pubic Snowflake course in youtube. @Data Engineering Simplified - could you please advise when the chapter -14 time travel will be published.
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 ⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡
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
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 ....
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
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...
@@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
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.
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
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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-IocdgUB94KQ.html), 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.
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?
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..
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?
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 (ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-PNK49SJvXjE.html)
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 🌐 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-DXI0GDSwE_E.html ➥ Chapter-19 ETL (Data Pipeline) in Snowflake 🌐 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-9FejjGVZrPg.html
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.
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.
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. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-QMUIW8OtI6c.html if you have any further query, drop a note again. Wish you all the best for your exam
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?
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
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
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.
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
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.
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.
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..
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 ?
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).
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..
@@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
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 ⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡
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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Pi3z1NyBd-Y.html
