Snowflakes and why not to use them (1/4): The Basics
Petr Podrouzek
?? Global Tech Leader | ?? SVP at Emplifi | ?? Strategy & Engineering Excellence
Stars and snowflakes
There are two basic ways how to model data for reporting and analytics. These are star schema and snowflake schema. The only difference is that snowflake schema can contain links between dimensions, while in star schema dimensions can be linked only directly to the fact table. The diagrams below illustrate the differences:
Fig 1.: Snowflake schema (source: Wikipedia, Snowflake.)
Fig 2.: Star schema (source: Wikipedia, Star.)
Slowly changing dimension
Most of the datawarehouses will keep history of changing data. This means that you want to track how dimensional data change. For example, if customer changes address you want to keep both records – before the change and after the change. Although I worked on some datawarehouses where this was not implemented and dimensions were always completely reloaded, most of the implementations I was involved in did contain slowly changing dimensions (SCD) and most often it was SCD2. I will shortly explain SCD2 but will not elaborate on the other types, there is plenty of stuff out there:
- SCD2 versions data using two timestamp columns (ValidFrom and ValidTo)
- Current records has ValidTo = NULL
- Once a record is updated or deleted ValidTo is updated to the time of deletion or update; if this time is not supplied by the source system it is the time of the ETL load (just simple GETDATE()).
- If you load round the clock the precision of this timestamp might need to be up seconds
- I would strongly suggest to implement hash to identify the changes. Hash would be calculated over all the columns except business key (but that can be included as well but it is of no use). The whole data flow can be described using the following diagram:
Fig 3.: Data flow of SCD2 with hashes
Few last words...
In this text I have explained the basic terms I will be using in the next articles. These are star and snowflake schemas and slowly changing dimension type 2. In the next text I will implement those in MS SQL 2012. What about you? Do you use snowflakes or stars? Do you use SCDs?
Sources:
- Wikipedia, Star. (n.d.). Retrieved June 14, 2016, from https://en.wikipedia.org/wiki/Star_schema
- Wikipedia, Snowflake. (n.d.). Retrieved June 14, 2016, from https://en.wikipedia.org/wiki/Snowflake_schema
My previous technical articles:
- Agile BI Series
- FLOGS: Agile SSIS Series
BI Engineer Azure Cloud / Power BI at Rabobank Netherlands
8 年Hai Petr, how do you implement hash in a DB2 environment?
Retiree at University of Life
8 年Nowflake brings the worst features of both Star and Relational... it guarantees the integrity of data structures at the cost of high performance big queries. No one solution is ever perfect in every way?
Data Architect
8 年The problem of the snowflake schema is performance: the more joins, the less performance. This is why we try to avoid them if we can. There is no shame in denormalizing a datawarehouse, quite the contrary. The shame is on slow reports! Also, I like your diagram. I have used this technique not for SCD2 but for Change Data Capture (differential load of big tables). Same principles. But you explain it neatly.
CEO at ieSoft
8 年I prefer Star schema overall. Not sure the last time I used a Snowflake schema. I say about 50% of my projects I have used SCD2, depends on project requirements. Hash approach has always been the best way for me to change data.
Sudar.io - Extreme Automation of AI data foundation and data products A first in many ways! - Enterprise Data Warehouse (DV2.0) Design Deploy & Load complete no-code automation - SQL only Business Vault
8 年Nice article. I agree this is would be a great SCD2 load strategy for many use cases. Most cases I have seen have source as more than one table for the SCD2 dimension. In this case different columns of the dimension come from different source tables. I guess this strategy would count on a staging table with columns matching the target data. The overhead with managing the staging table this way needs to be weighed against the efficiency by this process. Also, when we get delta feeds of the source(s), the 'Delete record' step will need a different route.