Which schema is best (Star or Snowflake)?

[muthukumar77]

Hi,

Can you please tell me which schema is best for Fast loading data?

[Anonymous]

Hi muthukumar,

Please refer to the page 15 from this document Best Practices for Data Modelling, it's a good reference of the advantages and disadvantages of a snowflake vs a star schema. In my personal opinion, I strongly support the Star Schema model, it provides not only better performance but also it's easier to understand for the user. I also recommend to read some of the Kimball techniques for dimensional modeling.

Hope it helps,

-- Karla

[engishfaque · Accepted solution]

Dear Muthukumar,

Kindly check the attached image for Star Schema vs Snowflake Schema. I hope this will help you to understand in better way.

For more information, please have a look into listed below similar thread

Re: Star & snowflake schema

Kind regards,

Ishfaque Ahmed

[ramasaisaksoft]

Hi Muthukumar,

Star Schema is the best loading technique.

	Snowflake Schema	Star Schema
Ease of maintenance / change	No redundancy and hence more easy to maintain and change	Has redundant data and hence less easy to maintain/change
Ease of Use	More complex queries and hence less easy to understand	Less complex queries and easy to understand
Query Performance	More foreign keys-and hence more query execution time	Less no. of foreign keys and hence lesser query execution time
Type of Datawarehouse	Good to use for datawarehouse core to simplify complex relationships (many:many)	Good for datamarts with simple relationships (1:1 or 1:many)
Joins	Higher number of Joins	Fewer Joins
Dimension table	It may have more than one dimension table for each dimension	Contains only single dimension table for each dimension
When to use	When dimension table is relatively big in size, snowflaking is better as it reduces space.	When dimension table contains less number of rows, we can go for Star schema.
Normalization/ De-Normalization	Dimension Tables are in Normalized form but Fact Table is still in De-Normalized form	Both Dimension and Fact Tables are in De-Normalized form
Data model	Bottom up approach	Top down approach

The main difference between star schema and snowflake schema is that

• The star schema is highly denormalized and the snowflake schema is normalized. .
• Performance wise, star schema is good but if we think about memory then snow flake schema is better than star schema.
• A dimension table will not have parent table in star schema, whereas
  snow flake schemas have one or more parent tables.
• Snow Flake Schema has bottom-up appraoch where as Star has Top-down.
• Star Schema has fewer joins and Snow flake has higher

[imsushantjain]

Can anybody explain why Single Table is bad for RAM Consumption and Script Run Time?

[fabian3684386]

OBT (= one big table) / single fact table including all dimensions should be part of this comparison (https://medium.com/dbsql-sme-engineering/one-big-table-vs-dimensional-modeling-on-databricks-sql-755...)