Schema Types

Lesson Notes

Fact Tables Lesson Notes:

1. Presentation Mode
2. Static Mode

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