A star schema model is a type of dimensional data model that consists of a single fact table and
multiple dimension tables. A 3NF model is a type of relational data model that follows the third
normal form, which eliminates data redundancy and ensures referential integrity. A Snowflake
Architect might use a star schema model rather than a 3NF model when designing a data
architecture to run in Snowflake for the following reasons:
A star schema model is more suitable for analytical queries that require aggregating and slicing data
across different dimensions, such as those performed by a BI tool. A 3NF model is more suitable for
transactional queries that require inserting, updating, and deleting individual records.
A star schema model is simpler and faster to query than a 3NF model, as it involves fewer joins and
less complex SQL statements. A 3NF model is more complex and slower to query, as it involves more
joins and more complex SQL statements.
A star schema model can provide a simple flattened single view of the data to a particular group of
end users, such as business analysts or data scientists, who need to explore and visualize the data. A
3NF model can provide a more detailed and normalized view of the data to a different group of end
users, such as application developers or data engineers, who need to maintain and update the data.
The other options are not valid reasons for choosing a star schema model over a 3NF model in
Snowflake:
Snowflake can handle the joins implied in a 3NF data model, as it supports ANSI SQL and has a
powerful query engine that can optimize and execute complex queries efficiently.
The Architect can use both star schema and 3NF models to remove data duplication from the data
stored in Snowflake, as both models can enforce data integrity and avoid data anomalies. However,
the trade-off is that a star schema model may have more data redundancy than a 3NF model, as it
denormalizes the data for faster query performance, while a 3NF model may have less data
redundancy than a star schema model, as it normalizes the data for easier data maintenance.
The Architect can use both star schema and 3NF models to design a landing zone to receive raw data
into Snowflake, as both models can accommodate different types of data sources and formats.
However, the choice of the model may depend on the purpose and scope of the landing zone, such
as whether it is a temporary or permanent storage, whether it is a staging area or a data lake, and
whether it is a single source or a multi-source integration.
Reference:
Snowflake Architect Training
Data Modeling: Understanding the Star and Snowflake Schemas
Data Vault vs Star Schema vs Third Normal Form: Which Data Model to Use?
Star Schema vs Snowflake Schema: 5 Key Differences
Dimensional Data Modeling - Snowflake schema
Star schema vs Snowflake Schema
