Snowflake is a cloud data platform that supports various data models for modeling tables in a
Snowflake environment. The data models can be classified into two categories: dimensional and
normalized. Dimensional data models are designed to optimize query performance and ease of use
for business intelligence and analytics. Normalized data models are designed to reduce data
redundancy and ensure data integrity for transactional and operational systems. The following are
some of the data models that can be used in Snowflake:
Dimensional/Kimball: This is a popular dimensional data model that uses a star or snowflake schema
to organize data into fact and dimension tables. Fact tables store quantitative measures and foreign
keys to dimension tables. Dimension tables store descriptive attributes and hierarchies. A star
schema has a single denormalized dimension table for each dimension, while a snowflake schema
has multiple normalized dimension tables for each dimension. Snowflake supports both star and
snowflake schemas, and allows users to create views and joins to simplify queries.
Inmon/3NF: This is a common normalized data model that uses a third normal form (3NF) schema to
organize data into entities and relationships. 3NF schema eliminates data duplication and ensures
data consistency by applying three rules: 1) every column in a table must depend on the primary key,
2) every column in a table must depend on the whole primary key, not a part of it, and 3) every
column in a table must depend only on the primary key, not on other columns. Snowflake supports
3NF schema and allows users to create referential integrity constraints and foreign key relationships
to enforce data quality.
Data vault: This is a hybrid data model that combines the best practices of dimensional and
normalized data models to create a scalable, flexible, and resilient data warehouse. Data vault
schema consists of three types of tables: hubs, links, and satellites. Hubs store business keys and
metadata for each entity. Links store associations and relationships between entities. Satellites store
descriptive attributes and historical changes for each entity or relationship. Snowflake supports data
vault schema and allows users to leverage its features such as time travel, zero-copy cloning, and
secure data sharing to implement data vault methodology.
Reference: What is Data Modeling? | Snowflake, Snowflake Schema in Data Warehouse Model -
GeeksforGeeks, [Data Vault 2.0 Modeling with Snowflake]
