Snowflake

A Snowflake Schema is a Star Schema that has additional tables related to the dimensions as parents of the dimension. Legacy systems are loaded with codes. Our customers will tell us that they must have their codes. The snowflake is not the answer.

DO NOTdesign, build or use these structures. They will raise hell with the performance of your business models. Over time I learned that my customers absolutely must have their codes -- after all, they've been using them since the 50s, 60s, or 70s -- I've never been able to convince a customer there's a better way.

You want to collapse those snowflake tables down into the dimension. Those tables may, themselves, be dimensions. I would be very suspicious of "Vehicles" and "Brands" in the above model.

The name of this game is I/O and you can see from this simple snowflake -- there's gonna be a lot of I/O hitting against that structure. It is not uncommon to have several million rows in a fact table.