Star and Snowflake schema explained with real scenarios

Star and Snowflake schema are basic and vital concept of dataware housing. This video explains what are star and snowflake schema.
Their differences and which should be used when in a very simplified manner with real examples.
Please like this video and subscribe to my channel for more such content

[@shekharshrestha4072]

This is one of the best video I watched. In a practical environment, I already knew how all of these thing works like when to or when not to normalize dimensions. However, I was always blurred in talks when they use words like star schema, snoke flake schema, dimensions (because I slept in my class most of the times :) ). This video clears it all and summarizes everything. And also basically all Master tables are dimension tables. Just Wow, you put everything together

[@TechCoach]

Thanks a lot shekhar for the kind words :), I am glad I could help here

[@babanbiswas9445]

One of the best explanation I have ever come across

[@TechCoach]

Thanks Baban for the kind words. Please consider subscribing to my other channel as well it will really help me. kzbin.info/www/bejne/bHiUqXqfYqlopdk

[@saurabhsingh6493]

This are one of the best videos on data warehousing. Request you to make more videos on data engineering. Thanks

[@TechCoach]

Sure Saurabh Thanks a lot for the kind words, I do have some more videos and I will work on some more soon. Please consider subscribing to my other channel as well it will really help me :) kzbin.info/www/bejne/bHiUqXqfYqlopdk

[@bijetadash]

Best video in data warehousing so far..keep up the good work

[@naveen1000]

Excellent...Continue uploading more

[@prateekdravin]

very nice and informative...looking frwrd for more such informational videos.

[@TechCoach]

Thanks prateek for the kind words. I will upload more videos soon, meanwhile I have a small request, I am working on this new channel please do watch and subscribe to it. Your support is my motivation. kzbin.info/www/bejne/hauXhZWHrNelhbs

[@sonalipradhan4486]

Highly recommended video.

[@TechCoach]

Thanks a lot Sonali :)

[@user-hk4sv1wx6z]

Crystal clear explination,Thanks bro

[@monty1007]

Hi Vivek, this video has really helped in understanding the basic concepts and difference between Star and Snowflake schema, after being jumped through number of videos. I think the examples you have shown are the best to describe it more appropriately and I can now relate it with the data I have worked on. Thanks...

[@TechCoach]

I am glad it helped :)

[@TechCoach]

Thanks for the kind words. I am working on this new channel and would really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/f4GVh6Fsn7Brfsk

[@krishnachaitanyareddy2781]

Good content

[@TechCoach]

Thanks a lot Krishna :)

[@SameerSrinivas]

Probably the best explanation on star and snowflake schema I've found so far. Very intuitive. Great job Vivek!

[@TechCoach]

Thanks a lot Sameer for the kind words, I have posted more videos on DWBI which may interest you

[@SameerSrinivas]

@@TechCoach I've an interview coming up. Do you have any videos where you take a problem and do data modeling? If you don't, do you have any resources where I can look into for practice? Thank you!

[@TechCoach]

So you will given a scenario and you will have to come up with entity and relationships??

[@SameerSrinivas]

@@TechCoach Yeah. Mostly related to systems like Amazon, Netflix. I am not sure about example questions.

[@gpmeneses]

Great and simple. Very clear.

[@Arvnd]

great video

[@TechCoach]

Thanks Aravind :)

[@opiee1999]

Good explanation with example

[@TechCoach]

I am glad I could help :)

[@brajeshpatel2810]

Thanks a lot.. keep sharing knowledge..

[@TechCoach]

Thanks Brajesh for the kind words. I am working on this new channel and would really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/f4GVh6Fsn7Brfsk

[@salmansayyad4522]

thanks for this video, concept cleared!!

[@PS-le6ty]

Awesome explanation!!!!!

[@TechCoach]

Thanks a lot for the kind words buddy :)

[@Ferruccio_Guicciardi]

Thanks for sharing. Very handy.

[@TechCoach]

Thanks Ferruccio for the kind words, I have a small request I am working on this new youtube channel, I Would love it if you watch and subscribe to it as well. kzbin.info/www/bejne/fZ-5n2ajhaeFsJY

[@mahesh97413]

Very well explained with examples. Thank you so much.

[@TechCoach]

@mahesh thanks for the kind words. Please consider subscribing to my other channel as well. It will really help me. kzbin.info/www/bejne/bHiUqXqfYqlopdk

[@RAHUL_YADAV1999]

superb explanation, Thanks for this video, I appreciate the way you explained all the things in the star and snowflake schema. so thankyou, thankyou, and thankyou😊😊

[@New_me14]

Informational video and very relevant with the real time examples.Thanks!

[@TechCoach]

Thanks Shradha for the kind words. I have a small request I am working on this new channel please do watch and subscribe to it. Your support is my motivation. kzbin.info/www/bejne/hauXhZWHrNelhbs

[@jeyapraveena5140]

Very informative. thank you

[@TechCoach]

Thanks a lot for the kind words :)

[@davidwiggs9148]

These videos are very helpful; please keep them coming - thank you

[@TechCoach]

Thanks a lot david for the kind words, Your support is my motivation :)

[@surajpatil3579]

Awesome 👍

[@bezerra238]

Thank you a lot , great video. Very well explained. Best regards!

[@TechCoach]

Thanks for the kind words, I have a small request I am working on this new youtube channel "An Indian Abroad" ,it will really help me if you watch and subscribe to it. kzbin.info/www/bejne/fnO0eZihgpaIaMU

[@vishal2327]

Thanks for the Video. Very well explained.

[@anshulaggarwal9601]

Such a descriptive and meaningful Video Vivek. Please keep posting such stuff...

[@shahnewazahmed9629]

Several times you have mentioned 'denormalize' when you meant 'normalize'; specially while explaining snowflake.

[@snehaltarwade1291]

Very nice videos..keep uploading more 👍🏻good work

[@TechCoach]

Thanks for the kind words Snehal. I have a small request I am working on this new youtube channel "An Indian Abroad" ,it will really help me if you watch and subscribe to it. kzbin.info/www/bejne/fnO0eZihgpaIaMU

[@narikivar2008]

This video makes sense really ! Quite illustrative and simple to understand. Thanks Vivek !

[@TechCoach]

Thanks for the kind words :)

[@kaavi2020]

Excellent explanation

[@rajpaul1501]

Very good and simple explanation. Thank you!!

[@TechCoach]

Thanks Raj for the kind words :)

[@Someonner]

Nice

[@sindhus1574]

Very well explained.Thank u☺️

[@TechCoach]

Thanks Buddy, apologies for the delayed reply

[@akshathaprabhuk]

Very well explained.

[@TechCoach]

Thanks Akshatha for the kind words, It means a lot to me. I have a small request, I am working on this new channel and would really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/f4GVh6Fsn7Brfsk

[@yarramneediravindraswamy6804]

Now I am following your video lectures, are they any concept beyond this in this concepts, sir?

[@TechCoach]

This video covers star and snowflake schema buddy I have more videos around it you can watch them as well

[@nuthankolusu1199]

Very good, easy to understand

[@TechCoach]

Thanks a lot Nuthan for the kind words:)

[@gauravsinghal4948]

Good explanation and channel also👌 You are doing a great job and putting so much effort to make it simple. Keep it. but here i want to add one thing as per knowledge that in SNOWFLAKE, Data redundency is reduced but data storage is increased in terms of memory/Tbl space of DW. Please pardon me if i am on wrong track

[@TechCoach]

@ gaurav in most cases the DB size would get reduced. In one of my previous project we were capturing finanical instruments ina dimension table, the table was changing everyday and we were maintaining history which lead to it becoming several million row table we normalized it to reduce redundancy the data size reduced signifacntly for that dataset. I hope it helps ;)

[@rajeshs8846]

@ Tech coach. You've mentioned to go with the snowflake schema to avoid redundancy and when dimensions are huge in size. Snowflakes are normally normalized rite? But at the end of the video, you've told to denormalize the dimensions when they are large in size. It contradicts the explanation of the snowflake schema. Could you kindly clarify?

[@jojappakoyyuru8301]

thanks for doing better job for us

[@TechCoach]

You are welcome buddy,I am glad I could help.

[@PS-le6ty]

This is really helpful. I was confused but this explaination has cleared my doubts.Thanks alot..😊😊

[@TechCoach]

Thanks a lot for the kind words, I am glad I could help :)

[@coloradokid8321]

Good demo... But just.a thought... A City can have multiple zip-codes (in the U.S.). So I think your foreign key for your normalized Customer Dimension table should be Zip Code instead of City. Correct?

[@kartikmehta5881]

Thank you.

[@TechCoach]

I am glad I could help :)

[@aravindhrajaratnam3681]

STAR SCHEMA : When one FACT table is connected to multiple DIMENSION table (containing de-normalized data ) and are linked through a primary key (dimension table) / foreign key (fact table) relation ship. SNOW FLAKE SCHEMA: If you normalize any one of the DIMENSION table in a STAR schema , then it becomes a SNOW FLAKE SCHEMA

[@marksaunders8430]

nice!

[@TechCoach]

Thanks Mark :)

[@mokshadakothari1265]

Very nice video and well explained !!

[@TechCoach]

Thanks a lot Mokshada :)

[@bhaskarsharma3338]

I have never seen a time dimension is normalized in dimension modeling, only OLPT designers can to such mistakes. I have not found a single video why we normalize dim tables but as of my experience, normalization only helps in case 1. mapping design easy to develop and maintain 2.when we have a dimension that goes huge numbers SCD to get the better performance. let me know if anyone wants to add their experience.

[@pawangadhave1548]

Clear understanding

[@TechCoach]

Thanks Pawan for the kind words, Your support is my motivation

[@TheVamos101]

Another great video as always. Thanks, Vivek! I learned a lot from the video. And I truly appreciate your responses to critics. I request you to make more series on database concepts.

[@AnIndianAbroadd]

Thanks for the kind words. I am working on some new videos. I will publish them soon.

[@TechCoach]

you may like the partitioning series as well. kzbin.info/aero/PLUWdEQb_2yqX9rJ9CtahlZvo0AD3A63JA

[@Happymoon789]

Thanks, I like it, learned.

[@TechCoach]

I am glad I could help :)

[@sindhus1574]

Nice explanation☺️

[@TechCoach]

Thanks a lot Sindhu S for the kind words. This Video on data mart may interest you as well. kzbin.info/www/bejne/noianKF4jZh-d5I

[@learnwithamjad7412]

please use city id instead of city value as key in customer table

[@arpitkumar6700]

Hi Vivek, It is a very nice video. However, I have questions? 1) Can we have multiple fact tables in both the schema and if it is possible then how can we handle it?

[@alanye7542]

Thanks!!!

[@abhilashgaddam7836]

Good videos...

[@TechCoach]

Thanks Abhilash. I have a small request I am working on this new channel "An Indian abroad" and will really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/Z2KnfGClrrekopI

[@ashokdasari450]

in snowflake, do we need vendor key or not. plz advise

[@TechCoach]

Hi Ashok, I may need more details here to understand the question better

[@anubhavchauhan1377]

Good explanation. Thank you!!!

[@TechCoach]

Thanks Anubhav chauhan for the kind words :). Your words are my motivation:)

[@ShaluChaudhary]

nice

[@TechCoach]

Thanks a lot shalu for the kind words :)

[@vishwas22khare]

Which one is used in industry more

[@TechCoach]

Both , if the dimensions grow in size we denormalize them often.

[@farhanakhan1348]

Great video! Thanks.

[@TechCoach]

Thanks Farhana for the kind words. I have a small request, I am working on this new channel please do watch and subscribe to it. Your support is my motivation :) kzbin.info/www/bejne/gKDFgIGfi7x0b7M

[@KartikShenoySK]

Great content! Great Delivery!

[@TechCoach]

Kartik Shenoy Thanks buddy for the kind words. I really appreciate it.

[@mukeshnuvvula9782]

Thank you Vivek, Its awesome the way you explained both Star and SnowFlakes are better than anything, especially the Time Dimension. I have a question here about Snowflakes schema, Which is How many , 1. if only , How do we maintain relationship between and using PK and FK), can we call them as a Dimension tables? because generally in DWH we use facts in Fact Table not in Dimension table. This would be helpful if you reply me.

[@TechCoach]

I am glad I could help mukesh:) We are just splitting dimension tables( normalizing them) to reduce ghe size and data redundancy. They do share a primary key foriegn key relationship. One dimension key from these splitted tables will be present in fact table using which we will be able to compute the required details.

[@mukeshnuvvula9782]

@@TechCoach Thanks Vivek for the response, Unfortunately some words from my question are deleted, This is my Question: How many Fact tables does a Snow Flake schema contains, if only 1 Fact table, How do we maintain relationship between Product_Vendor and Vendor tables(using PK and FK? (or) SK and FK? ), can we call them as a Dimension tables? because generally in DWH we use facts in Fact Table not in Dimension table. This would be helpful if you reply me.

[@meditation_in_nature]

Awesome , Please make some lecture on types of dimensions and facts .

[@TechCoach]

Thanks Sudhir, Apologies for the drlayed reply

[@casadespider1256]

Very good video! Clear, concise, and very informative. You mentioned at the beginning of the video that you have another video explaining Data Warehousing. Can you please provide a link to that? Thank you!!

[@TechCoach]

Thanks a lot for the kind words , These videos may interest you as well. Data mart : kzbin.info/www/bejne/noianKF4jZh-d5I ODS :kzbin.info/www/bejne/a5_JeaCebbOmftk DWBI : kzbin.info/www/bejne/h2iueYpopKiCi6s SCD : kzbin.info/www/bejne/jqLHi3lmea-ji9U

[@fabriciovargas6164]

Nice!, good explanation

[@TechCoach]

Thanks a lot for the kind words buddy :)

[@preethiag1972]

Thanks a lot. Can you please post a video for the datawarehouse developer roles , interview questions and skillset required to become a datawarehouse developer

[@ejapuk]

around 17 mins de-normalized and normalized are used interchanged. please check and correct.

[@TechCoach]

Thanks for the input sir, I will review and make the required change. I have a small request I am working on this new channel and would really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/Z2KnfGClrrekopI

[@kalyanirane03]

I thought it was just me who got confused due to confusion between normalization and denormalization. The change has still not being made. I wonder if other were even paying attention?! Your comment solves my doubt. Thanks!

[@rajk5032]

Hi Vivek, It was a nice video . Can you please answer these questions? 1). What would be the average time it will take to implement a model (consider snowflake & Star with 10gb of rawdata). 2)what are the perquisites which we need to consider before opt a data model?

[@TechCoach]

Hi Raj, I am not sure if I understand your question completely.Implementing a data model has more to do with your business requirements and its scope and complexity. Usually Mart's are Implemented using Star schema but if some of the dimensions are expected to grow in size overtime they are normalized further which results in snowflake Implementation.Please let me know if you were looking for something else

[@prashantmhatre9225]

awesome :)

[@TechCoach]

Thanks a lot Prashant :)

[@00EagleEye00]

I got a question regarding date dimension. Is it self-generated? Why is the date doesn't reside on the fact table? Should be the date dimension join will be use for aggregation type (e.g. weekly, monthly, quarterly, etc.)

[@bhushangosavi435]

Very Nice explanation Vivek..! :)

[@TechCoach]

+Bhushan Gosavi Thanks Bhusan.Your words means a lot to me :)

[@virupangouda9341]

Very helpful....👌👍

[@TechCoach]

Hi Virupan, Thanks for the kind words. I have a small request, I am working on this new channel "An Indian abroad" and will really appreciate if you watch and subscribe to it.If you enjoy the videos please share with your friends as well. kzbin.info/www/bejne/f4GVh6Fsn7Brfsk

[@chiho7311]

great video!!!

[@TechCoach]

Thanks Chi ho for the kind words:)

[@kshitishdas8131]

More topics on data warehouse please 🙏

[@TechCoach]

Hi Buddy, I have a lot of videos on datawarehousing concepts buddy. I will post the link here for you.

[@pedrohenriquecardoso1076]

Great video, helped a lot :D

[@TechCoach]

Thanks buddy for the kind words. I have a small request I am working on this new youtube channel "An Indian Abroad" and will really appreciate if you watch and subscribe to it :) kzbin.info/www/bejne/p2XIiayFhdVggtU

[@abdullahyahya2471]

How to Populate Fact Table, Especially when we want to populate date_id, Do we have to query and find the specific Date_id for a specific timestamp?

[@ChiragPatel-mg4mm]

Nice job done Vivek

[@TechCoach]

Chirag Patel Thanks a lot :)

[@crhanks5400]

great job!

[@TechCoach]

Thanks a lot for the kind words :)

[@sweetietoronto7090]

Hi Vivek very nice video and explanation. I have a question - If i have a scenario dimension (covid, pre covid, post, covid with value and start and end time frame), how would I model this scenario into my dim model?

[@TechCoach]

Hi Sweetie thanks for the kind words can you please explain your question a little more?

[@mlvkkapoor]

Hey Vivek, Your videos have good content and you explain every bit so nicely.I was wondering if you could suggest some websites for case studies on data mart and likes. Thanks !

[@TechCoach]

Malvika Kapoor Thanks Malvika for the kind words.If you are looking for case studies for specific domains like insurance, healthcare,banking let me know. I will try to find some good ones

[@mlvkkapoor]

I am looking for Collections data mart in banking and KYC case studies.I really appreciate your help.Thanks.

[@venkatagadepalli4483]

For snowflake you are saying as denormalised, I guess accidentally you are saying it,instead of normalised

[@TechCoach]

Hi Venkata, thanks for Informing.I will review the video. I have a small request I am working on this new channel "An Indian abroad" and will really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/Z2KnfGClrrekopI

[@claytonpope4631]

In the star schema, it appears that the table Product_Vendor has two primary keys, but both of them have repeating values? How is this possible? Even if denormalized I thought primary keys must not contain duplicate values? 8:46 in the video

[@jvg19701]

The combination of product_id and vendor_id is unique

[@applehotful]

Good video!!

[@TechCoach]

+applehotful Thanks a lot :)

[@venkatbala3201]

Hey Vivek, Good Work. I still have doubt on the way how the date dimension i.e. splitting the Date table into 2 dimensions is resolving/improving performance by 80% as you have mentioned.I understood the City dimension well though. Could you please elaborate. Thanks!

[@TechCoach]

if you have date and time dimension in the same table you have 24 rows for each day with a lot of redundant columns, Visualise this over a 10 year period and you will have lot of redundancy. You can split/normalise this in 2 tables 1. Date - 1 row per day 2. Time - 24 rows per day (but less columns) so you will have less redundancy, improved storage and improved performance (Less traversal of columns ). I doubt I mentioned 80% improvement,80% is usually the size of fact tables in warehouse. we normalise the dimensions to ensure they remain small and effective. Hope this helps

[@sweetysushma4247]

Helpul thank u so much

[@TechCoach]

Thanks a for the kind words Sushma, I have a small request I am working on this new channel please do watch and subscribe to it. Your support is my motivation. kzbin.info/www/bejne/hauXhZWHrNelhbs

[@ankurlodha6098]

where I can find the slides.

[@TechCoach]

I am sorry Ankur , I lost the slides in a laptop crash :(

[@Bruh-jw2ze]

Dude why do u keep saying "denormalized" for snowflake schema????? Confused the hell out of me Otherwise a brilliant video 💯

[@pratimab5936]

Hi can we have more than one fact table in Star schema ? If two or more fact tables in any schema then how they are related? please answer.

[@TechCoach]

We can have multiple fact tables in a star schema , usually they are related directly only to dimensions tables

[@guenthersteiner9252]

Thanks, Hero

[@maryasangeetha8461]

I don't understand why you say "de-normalized" for normalized !!!

[@TechCoach]

My apologies for the confusion

[@jaskaran449]

can you provide me your ppt?

[@TechCoach]

I am sorry I don't have it now

[@jaskaran449]

@@TechCoach no problem

[@shranta]

U made an error saying dim tables r denormalized in snow flake schema. It's the other way around. Star schema has denormalized dimension tables and in snow flake schema dimensions are normalized to reduce redundancy thus increasing join complexity

[@TechCoach]

Thanks Shranata for the comment, I will review it. In Snowflake schema the " dimensions are further normalized " this is what I intended to convey. If there was a slip of tounge. I Apologize for that.

[@shivashankar4202]

Hello !!!!!can any one tell?Is it possible that ,Snowflake schema contains more than one fact table REPLY

[@TechCoach]

shiva shankar I am guessing your question is if a warehouse with snow flake schema can have more than one fact table, Yes it can. I hope it helps

[@roseporras2010]

Hi Tech Coach, do you have samples for such scenario? I am new to Dimensional Modeling and trying to deliver a simple solution but dont have much time to do so. Thanks.

[@abdullahyahya2471]

[@ashishchoudhary26]

Ppt Link please

[@TechCoach]

I am sorry Ashish, I don't have the PPT.

[@MudassirKhan-ko8po]

Pls send me a copy of this

[@TechCoach]

Hey Mudassir Khan, unfortunately i don't have one right now. I would recommend seeing it KZbin

[@yogeshtomarr]

Too Slow

[@TechCoach]

My Apologies, I would work on it for my next videos. I would recommend watching it at 2x speed that may help.

[@yogeshtomarr]

@@TechCoach Thank you for suggestion that works for me, although the content is good.

[@TechCoach]

@@yogeshtomarr Thanks for the kind words. I am working on this new channel and would really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/h6PTgZZjjLOdpbc

[@sindhus1574]

Very well explained.Thank u☺️

[@TechCoach]

Thanks Sindhu for the kind words, It means a lot to me. I have a small request, I am working on this new channel and would really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/f4GVh6Fsn7Brfsk