Snowflake Data Modeling

Inmon Approach

  • Build Data Marts separate from Enterprise Data Warehouse.

  • High start-up cost

  • Good for enterprise-wide integration


Kimball Approach

  • Use Dimensional modeling using star schema.

  • Low start-up cost.

  • Good for tactical business requirements

  • Kimball's Approach is good if you are starting from scratch due to its simplicity.

Data Vault Approach

  • Hybrid of Third Normal form and star schema.

  • Good for Audit and historical tracking purposes

  • Agile Approach of the building data model.

  • Third normal form for Improved write speed and Star schema for Improved read speed.

  • Stores all the raw data and then use it for the specific business need

  • Adaptable to changing business requirements without having to re-engineer effort

  • Every Hub Table (Entity) is connected to Satellite Tables using a Link table

  • Only inserts are allowed as data is kept for the historical purpose

  • Hash keys are used instead of composite keys or surrogate keys.

  • Data marts are built on top of the data vault as views to enable reporting


HUB

  • Represents business entity (customer, product, employee)

  • Contains unique business keys (Hash)

  • Business keys that tend not to change

LINK

  • Establish a relationship between different business keys

  • Many-to-many relationship (3NF)

  • Link makes it easier to add new sources

SATELLITE

  • Contains attributes

  • Similar to dimensions

  • Subject to change over time

  • Stores changes like SCD Type 2

Pros

  • Allows historical data tracking

  • Incremental builds that handle multiple sources

  • Able to handle changing business requirements

  • Easy to track and audit the data

Cons

  • Complex set up

  • Has learning curve

  • Not suitable for small data sources