Affordable Access

Publisher Website

Why is the snowflake schema a good data warehouse design?

Information Systems
Publication Date
DOI: 10.1016/s0306-4379(02)00021-2
  • Data Warehouse Design
  • Star And Snowflake Schema
  • Independent And Separable Database Schema
  • Acyclic Database Schema
  • Design
  • Engineering


Abstract Database design for data warehouses is based on the notion of the snowflake schema and its important special case, the star schema. The snowflake schema represents a dimensional model which is composed of a central fact table and a set of constituent dimension tables which can be further broken up into subdimension tables. We formalise the concept of a snowflake schema in terms of an acyclic database schema whose join tree satisfies certain structural properties. We then define a normal form for snowflake schemas which captures its intuitive meaning with respect to a set of functional and inclusion dependencies. We show that snowflake schemas in this normal form are independent as well as separable when the relation schemas are pairwise incomparable. This implies that relations in the data warehouse can be updated independently of each other as long as referential integrity is maintained. In addition, we show that a data warehouse in snowflake normal form can be queried by joining the relation over the fact table with the relations over its dimension and subdimension tables. We also examine an information-theoretic interpretation of the snowflake schema and show that the redundancy of the primary key of the fact table is zero.

There are no comments yet on this publication. Be the first to share your thoughts.