Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

database
normalization
star
snowflake

Star schema vs. Snowflake schema

Educative Answers Team

The multidimensional schema was specifically designed to model data warehouse systems. These schemas were created to address the unique needs of huge databases that are designed for Online Analytical Processing (OLAP) purposes.

The database schema, of a database system, is the database’s structure described in a formal language supported by the database management system (DBMS).

The multidimensional schema refers to the structure of multiple interlinked tables.


Most popular schemas

  • Star schema
  • Snowflake schema

These two schemas are a simple way to organize entire data warehouses using relational databases.

Note: A relational database (RDB) is a collective set of multiple data sets organized by tables, records, and columns.


Learning the models

1. Star schema

The star schema is the simplest type of data warehouse schema. Named after its structural resemblance to a star, this schema places each of its logical dimensions (denormalized) into one table.

The center of the star is a fact table, and the points of the star are dimension tables.

  • Fact table: a data table that maps lookup IDs together. It is usually one of the main tables central to your application.

  • Dimension tables: a data table that provides descriptive information for all the measurements recorded in the fact table.

In the star schema, the center of the star can have one fact table and a number of associated dimension tables. This schema is optimized for querying large data sets; it is also known as the star join schema.


Example

A star schema is best suited when the dimension tables store a relatively small number of rows, and space is not an issue. This type of schema is simpler than others, and the query is very easy to understand.

svg viewer

2. Snowflake schema

The snowflake schema is very similar to the star schema, but it contains more dimensions. The schema is called snowflake because its diagram resembles a snowflake.

While the dimension tables of the star schema are normalized, and its data is split into additional tables, the snowflake schema only affects the dimension tables, not the fact tables.


Example

A snowflake schema is best suited when dimension tables store a large number of rows with redundant data, and space is an issue.

However, the snowflake schema has a more complex query due to the multiple foreign keys joins between dimension tables.

svg viewer

Difference between the two models

  • A snowflake design can be slightly more efficient in terms of database space; this is especially true if the dimensions have many large text fields. However, in general, the size of fact tables outweigh the size of the dimension by a large enough factor.

  • Star schema’s simplicity has the advantage of requiring fewer joins to build and maintain the database. However, there are instances that will call for a snowflake design.

  • Some OLAP reporting tools work more efficiently with a snowflake design.

  • The multiple tier joins available in a snowflake design can make aggregation simpler as well.


RELATED TAGS

database
normalization
star
snowflake
Copyright ©2022 Educative, Inc. All rights reserved
RELATED COURSES

View all Courses

Keep Exploring