Understanding the Snowflake Schema.

The snowflake schema is a variant of the star schema model, where some dimension tables are normalized, thereby further splitting the data into additional tables. The resulting schema graph forms a shape similar to a snowflake.

The major difference between the snowflake and star schema models is that the dimension tables of the snowflake model may be kept in normalized from to reduce redundancies. Such a table is easy to maintain and saves storage space because a large dimension table can become enormous when the dimensional structure is included as columns. However, this saving of space is negligible in comparison to the typical magnitude of the fact table. Furthermore, the snowflake structure can reduce the effectiveness of browsing since more joins will be needed to execute a query. Consequently, the system performance may be adversely impacted. Hence, the snowflake schema is not as popular as the star schema in data warehouse design.

An example of a snowflake schema for AllElectronics sales is shown below.

Note: - The item dimension table now contains the attributes item_key, item_name,brand, type, supplier_key, where supplier_key linked to the supplier dimension table, containing supplier_key, and supplier_type information. Similarly, the single dimension table for location in the star schema can be normalized into two new tables:location and city. The city _key in the new location table links to the city dimension table.