Snowflakes and why not to use them (2/4): Implementation
Petr Podrouzek
?? Global Tech Leader | ?? SVP at Emplifi | ?? Strategy & Engineering Excellence
In the previous text I have defined what snowflake and star schema is. I also explained the principle of slowly changing dimension type 2. In this text I will move from concepts to actual implementation. Imagine the following situation. You have a simple 2 level hierarchy of categories (that means subcategories and categories) and the OLTP table could look like this:
Fig 4: Source system table (recursive hierarchy)
As you can clearly see the table is implemented in a recursive manner. The depth of the category tree can be infinite (although we assume only 2 levels). This approach is not good for reporting tools and you normally “flatten out” such hierarchy. If you choose a snowflake schema with two linked dimensions (DimCategory and DimSubcategory) it would look like this:
Fig 5: Snowflake dimensions
Should you decide to go with the star schema, the “flattened out” hierarchy would most likely look like this:
Fig 6: Star dimension
As you can see, in the star schema, the data is present redundantly. For each subcategory category data is included as well. A category can have N subcategories which means that the category data is redundant N-1 times in the star schema. Now assume that both dimensions utilize slowly changing dimension type 2 concept (SCD2). Also assume that while the categories are fairly stable (do not change too much), the subcategories change often (names, descriptions etc.). This is reasonable assumption because subcategories might have many more attributes (not just business key, name and description as in the example above). In this case, the problem with data redundancy gets even worse. Why? Because SCD2 versions all the records and if there are M subcategory changes over period of time then the data of the category will be replicated N + M – 1 times. Which is not very efficient. This inefficiency does not happen in snowflake schema because the data is partly normalized.
Few last words...
In this text I have implemented both snowflake and star schema SCD2 dimensions. I have also shown that star schema can be inefficient in using storage. So the natural answer would be – let's use snowflake. In the next article I will discuss if this straightforward approach is actually good.
I agree with the comments about the choice of tech mattering a lot here. The Kimball methodology is deeply rooted in the assumption that one fact table can be efficiently inner-joined with several dimension tables in a single pass if the dimensions have been flattened (ie: no snow-flaking). While this was (and still is) true with many traditional RDBMS's like Oracle and MySQL; this assumption doesn't always hold with distributed map-reduce architectures. I'm not saying that everything about the old way is wrong (I'm actually a huge Kimball fan); just that each situation must be considered in more depth than it used to.
I agree with the last comment regarding who the end user is and how the data will be used. Standard, canned reporting use cases can make use of this approach in general. Another factor to consider is the set of tools in the hands of the end user, their sophistication with using them, and the flexibility they require for analyzing the data from a different perspective or mashing it up against a different data model. In other words, if I know the questions to be asked, I have business users with little SQL or technical background and I am needing to produce canned reports this approach is a traditional one. For instances when the business keeps asking new questions and the "grain" and dimensions change, then I need to use an approach and tools that can derive the dimensions and create these associations on the fly. Regarding snowflake or not, I would typically not waste time doing it and it adds a join that isn't needed. I would be more focused on data cleansing and conforming to the enterprise's definition for the dimensions. Storage is cheap.
Big Data & Analytics - Data Solution Architect
8 年Good article, I would say another factor to consider is the End User who supposed to use this Dimensional Model. Non Techinical Users might prefer to keep it denormalized for ease of use.
Data Architect, Saxophonist
8 年Also consider the likelihood that more levels will be needed and whether the hierarchy will be unbalanced/ragged
I make your data ready for AI (and humans, too)
8 年Given that these type of dimensions are small, query performance is not a useful evaluation criterion: regardless of architecture, query performance will be high. SCD2 bridge tables, which link rows in the Category and Subcategory tables, make CRUD simple. Finally, by using metadata to define hierarchies, all CRUD operations for the SCD2 dimension hierarchy pattern can be hydrated automatically, which reduces TCO (Total Cost of Ownership). Bottom line: thinking outside snowy stars lets you build a data warehouse that is fit to purpose, easily maintainable, low in cost, and highly performant.