Data Modeling medium
In Data Warehousing (OLAP), what is the difference between a Star Schema and a Snowflake Schema?
- A Star Schemas utilize a schemaless document-oriented format to store facts, while Snowflake Schemas rely on a strict, pre-defined relational blueprint.
- B Star Schemas are specifically optimized for open-source databases like MySQL, whereas Snowflake Schemas are required for enterprise-grade Oracle systems.
- C Star Schemas implement a hierarchical, recursive tree structure for dimensional data, while Snowflake Schemas utilize a decentralized flat-file mapping.
- D Star Schemas use denormalized dimension tables for simplicity, while Snowflake Schemas normalize dimensions to reduce storage and improve maintenance.
Answer: D. Star Schemas use denormalized dimension tables for simplicity, while Snowflake Schemas normalize dimensions to reduce storage and improve maintenance.
In Dimensional Modeling (OLAP), the Star Schema is prioritized for analyst usability: because dimensions are denormalized, queries require fewer JOINs and are easier to understand. The Snowflake Schema is a more 'disciplined' approach that normalizes these dimensions, reducing data redundancy and making structural updates easier, but at the cost of more complex, multi-join queries that can be slower on some hardware architectures.
One system design question a day, with spaced repetition so the ones you miss come back.
Get the appRelated questions
- When modeling a Distributed Transaction using the Saga Pattern (Choreography or Orchestration), what type of consistency do you achieve?
- Which practice prevents SQL Injection attacks?
- What is 'Bi-Temporal' data modeling (e.g., in SQL:2011 standard)?
- Adding an Index to a database column generally improves Read performance. What is the negative trade-off?
- Log Systems Merge (LSM) Trees (used in RocksDB, Cassandra) optimize for fast writes. What is their primary downside regarding Disk I/O?
- What is the primary objective of normalizing a relational database schema (e.g., to 3NF)?