What is the difference between star schema and snow flake schema? When we use those schema's?

Showing Answers 1 - 33 of 33 Answers

Rahul verma

  • Nov 28th, 2006
 

Star Schema : Star Schema is a relational database schema for representing multimensional data. It is the simplest form of data warehouse schema that contains one or more dimensions and fact tables. It is called a star schema because the entity-relationship diagram between dimensions and fact tables resembles a star where one fact table is connected to multiple dimensions. The center of the star schema consists of a large fact table and it points towards the dimension tables. The advantage of star schema are slicing down, performance increase and easy understanding of data.

Snowflake Schema : A snowflake schema is a term that describes a star schema structure normalized through the use of outrigger tables. i.e dimension table hierachies are broken into simpler tables.

In a star schema every dimension will have a primary key.

  • In a star schema, a dimension table will not have any parent table.
  • Whereas in a snow flake schema, a dimension table will have one or more parent tables.
  • Hierarchies for the dimensions are stored in the dimensional table itself in star schema.
  • Whereas hierachies are broken into separate tables in snow flake schema. These hierachies helps to drill down the data from topmost hierachies to the lowermost hierarchies.
  • Star schema : In this star schema fact table in normalized format and dimension table is in de normalized format.  It also known as basic star schema.

    Snow flake schema: In this both dimension and fact table is in normalized format only. It is also knwon as Extended star schema.

    If u r taking the snow flake it requires more dimensions, more foreign keys, and it will reduse the query performance, but it normalizes the records.,

    depends on the requirement we can choose the schema

      Was this answer useful?  Yes

    Guest

    • Jan 4th, 2007
     

    Both these schemas are generally used in Datawarehousing.

    Star schema as the name indicates resembles the form of star as there is only one fact table which is associated with numerous dimension tables (with the help of foreign keys).This schema depicts a high level of denormalized view of data.

    However in Snowflake schema the dimension tables are further normalized into different tables (fact table is single in this schema also).This schema stores data in a more normalized form .

    It depends on scenario as how much data is generally there in the datawarehouse,generally star schema is preferred.

      Was this answer useful?  Yes

    Ravikumar

    • Jan 17th, 2007
     

    star schema:  it is a highy de-normilised techinque... in this one fact table is associated with n number of dimensions table... .. it looks like a star..
    snow Flake Schema:  If We apply normilised Princples to Star Schema Then It is Known As Snow Flake Schema.. In This Each Demsion Table Associated With Sub Dimenson Table..

    lakshmib

    • Sep 26th, 2008
     

    Star Schema means A centralized fact table and surrounded by different dimensions Snowflake means In the same star schema dimensions split into another dimensions

    Star Schema contains Highly Denormalized Data

    Snow flake contains Partially normalized data

    Star cannot have parent table But snow flake contain parent tables. Need to go for Star: Here


    Simply database

    Support drilling up options



    Need for Snowflake schema: Here some times we used to provide? seperate dimensions from existing dimensions that time we will go to snowflake

    Dis Advantage Of snowflake: Query performance is very low because more joiners are present

    mri_sonam

    • Dec 17th, 2008
     

    Star Schema:
    Definition: The star schema is the simplest data warehouse schema. It is called a star schema because the diagram resembles a star, with points radiating from a center.

    A single Fact table (center of the star) surrounded by multiple dimensional tables(the points of the star).
    Advantages:

    •    Simplest DW schema
    •    Easy to understand
    •    Easy to Navigate between the tables due to less number of joins.
    •    Most suitable for Query processing
      Disadvantages:
    •    Occupies more space
    •    Highly Denormalized


    Snowflake schema:

    Definition: A Snowflake schema is a Data warehouse Schema which consists of a single Fact table and multiple dimensional tables. These Dimensional tables are normalized.
    A variant of the star schema where each dimension can have its own dimensions.

    Advantages:

    •    These tables are easier to maintain 
         Saves the storage space.
    Disadvantages:
    •    Due to large number of joins, it is complex to navigate

    Starflake schema - Hybrid structure that contains a mixture of (denormalized) STAR and (normalized) SNOWFLAKE schemas.

      Was this answer useful?  Yes

    1. Star shema is Demormalised data but in snowflack schema its normalised
    2. when when star schema splited into it become to snowflack schema
    3. Performace will be more on star schema but performance less in snoflack schema due to joins
    4. Star schema is simple but snowflack is complex
    5. Dimention table in star schema having no maste table but Dimention table in snowflack having so many maste table.
    6. Star schema having one fact table sarounded by dimetion table but incase of snowflack schema more than one fact table sarrounded by dimetion table.

      Was this answer useful?  Yes

    erpooja88

    • May 25th, 2009
     

    Accurate information is star schemas have more joins because it has only one dimension table per fact and so to retrieve more queries we have to use joins...

      Was this answer useful?  Yes

    shakilag

    • Jun 23rd, 2009
     

    Star Schema:

    A single fact table associated to N dimension tables.

    This schema is denormalised and hence has good performance as this involves a simple join rather than a complex query.

    Snowflake schema:

    A star schema in which dimensions are further normalised.

    As this schema is normalised and hence will give slower performance as this involves lot of complex joins (involves many dimensions).

      Was this answer useful?  Yes

    sunny

    • Jul 5th, 2013
     

    star schema: When dimension table contains less number of rows, we can go for Star schema.In this Both Dimension and Fact Tables are in De-Normalized form.Good for datamarts with simple relationships (1:1 or 1:many).Less no. of foreign keys and hence lesser query execution time.

    snowflake schema: When dimension table is relatively big in size, snowflaking is better as it reduces space.Dimension Tables are in Normalized form but Fact Table is still in De-Normalized form.Good to use for datawarehouse core to simplify complex relationships (many:many).More foreign keys-and hence more query execution time.

      Was this answer useful?  Yes

    kamesh

    • Jan 30th, 2018
     

    A join is linked with only only one dim and one fact is called star schema
    A join is linked with more than one dimension is called Snow flake schema.

      Was this answer useful?  Yes

    Give your answer:

    If you think the above answer is not correct, Please select a reason and add your answer below.

     

    Related Answered Questions

     

    Related Open Questions