Schema Types
Ch.02: DWH | DWH Components | Data Modeling
Lesson Notes
Fact Tables Lesson Notes:
Video
Schema Types
Star Schema.
Snowflake Schema.
Star Schema
Star Schema Main Characteristics
Simplicity: It is the simplest type of DWH schemas.
Query effectiveness: Because of simplicity, It needs less join to query the data (It is optimized to query large dataset).
Data Redundancy and Large Table Size: Due to de-normalization, it has a data redundancy, and the table size is huge.
Most used and widely supported.
Star Schema Characteristics
Dimensions represented by one one-dimension table.
The dimension table are not joined to each other
The fact table would contain key and measure.
Data integrity is not enforced due to the de-normalized structure.
Schema Types: Star Schema Example
What is Snowflake?
Real Snowflake Photo taken from [https://earthsky.org]
Snowflake Simple Design
Snowflake Final (Complex) Design
Snowflake Schema Characteristics
Extension: Snowflake is an extension of the Star Schema.
Normalized: Dimension tables are normalized; this means every dimension may expand into additional tables.
Disk Space Efficiency: Due to its normalization methodology, it uses less desk space, which enhances the query as we scan less data size.
Complicated: Due to the normalization query needs to join more table in some cases to get the data which reduces the performance.
Schema Types: Snowflake schema (Example)
Star Vs Snowflake Schema
| Star | Snowflake |
|---|---|
| Dimension represented by one-table | Dimension tables are expanded into multi-tables |
| Fact table surrounded by dimension tables | Fact table surrounded by Hierarchy of dimension tables |
| Less join | Requires many joins |
| Simple Design | Very Complex Design |
| De-normalized Data structure | Normalized Data Structure |
| High level of Data redundancy | Very low-level data redundancy |
| Maintenance is difficult | Maintenance is easier |
| Good for datamarts with simple relationships (1:1 or 1:many) | Good for core to simplify (many:many) |