Star Schema vs Normalized

Data warehousing is a technology-driven approach for collecting, storing, and managing large volumes of data from various sources in a centralized repository. The goal is to support business intelligence activities, enabling organizations to perform complex queries, generate comprehensive reports, and obtain insights on their data to drive strategic decision-making.

The best practice for designing a data warehouse involves a multi-layered architecture. This approach simplifies maintenance and solves various distinct problems independently. The layers typically include three key types: an initial layer for data acquisition, a middle layer for data integration, and a presentation layer for ease of querying. This layout is often known collectively as a medallion data architecture.

The three layers each employ different types of data models tailored to their specific functions. In a medallion data architecture, a normalized data model is utilized in the middle (silver) layer to handle complex data integrations, ensuring reduced data redundancy and increased data consistency. The presentation (gold) layer adopts a star schema for simplified and efficient querying, enabling end-users to perform analytics without complex joins.

This article dives deeper into the characteristics, similarities and differences between Star Schema and Normalized data models in the context of these data architecture layers.

Understanding Star Schema data models

A Star Schema is a type of data model typically used in the presentation (gold) tier of a multi-tier data warehouse. It is designed for ultimate consumability and is easily understandable, distinguishing it from more technical models such as Normalized. Entities in a Star Schema are categorized into dimensions and facts:

• Facts represent measurable quantities
• Dimensions provide contextual attributes by which facts are measured.

When visualized, the model appears star-shaped, with a central fact table connected directly to surrounding dimension tables through one level of joins.

Star Schema vs. Snowflake Schema

The primary difference between a Star Schema and a Snowflake Schema lies in their hierarchical design. A Star Schema always uses exactly one depth of join between the fact and dimension tables, producing a simplified structure. Snowflake Schemas have dimensions that can cascade into sub-dimensions, requiring multiple joins. This makes Snowflake Schemas more normalized but potentially more complex to query.

Star Schemas in a Multi-Tier Architecture

In a multi-tier data architecture, the Star Schema fits into the presentation or gold tier. It is designed for end-user querying and reporting, simplifying data retrieval by users through highly targeted and easily navigable structures. This level typically follows after layers like Normalized or Data Vault models, which perform data integration and serve as the single source of truth.

ETL pipelines for Star Schemas

ETL (Extract, Transform, Load) pipelines for populating a Star Schema typically source data from Normalized or Data Vault models. These pipelines often entail two major stages: first, maintaining the dimension tables, followed by updating the fact tables. This sequence ensures the fact tables can always reference the latest dimension data.

Common transformations include using surrogate keys or sequences, dealing with time variance (such as slowly changing dimensions), and setting up the left joins for the dimension lookups.

Pros and cons of Star Schema

The key advantage of a well-designed Star Schema is the ease of cross-referencing and gaining insights through commonly used dimensions across multiple fact tables. This simplifies query processing, keeps it highly performant, and opens up the possibility of cross-domain data insights.

However, its reduced flexibility compared to Normalized models can be a downside. In particular, Star and Snowflake schemas are bad choices for data integration because deliberate denormalization means the same piece of data often needs to be maintained in multiple places.

Additionally, the need for separate staging processes to maintain dimensions and facts can complicate the ETL pipelines.

In summary, Star Schemas are intended for streamlined, user-friendly querying and reporting, making them well-suited for the presentation layer of a data architecture. However, Star and Snowflake schemas also have specific drawbacks that need careful handling during data pipeline design and implementation.

Normalized Data Models in Data Warehousing

Normalization is a data modeling technique aimed at minimizing redundancy and ensuring data integrity. To organize data most efficiently, normalization breaks down source information into small, related tables and defines relationships between them using unique identifiers called primary keys and foreign keys.

The core principles of normalization include:

• Atomicity (storing simple, indivisible values)
• Consistency (maintaining consistent granularity within every table).

These techniques ensure that the data accurately reflects real-world entities and their relationships.

Data Normalization in a multi-tier data architecture

In a multi-tier data architecture, normalized models typically reside in the middle, often referred to as the "silver" tier. This tier acts as an intermediary between raw data (captured in the bronze tier) and aggregated, user-friendly information (stored in the gold tier).

The silver tier serves as the integration stage, where data from multiple source systems converges and is harmonized. This is where normalization plays a pivotal role, aligning all the incoming data into a single, consistent, subject-oriented structure to prepare it for further analysis and reporting.

ETL pipelines for populating Normalized Data

ETL (Extract, Transform, Load) pipelines for normalized data models must be sophisticated enough to incorporate complex transformations to ensure data coherence and alignment.

Essential ETL tasks include locating and integrating data from varied sources, performing structural and semantic transformations to ensure consistency, and maintaining the correct granularity and business definitions. Semi-structured data needs to be flattened and relationalized for storage in normalized tables.

These transformations embody business rules, capturing and delivering the true essence of data as defined by business needs.

Pros and cons of Normalized Data Models

Pros:

• Data Integrity: Enforced through primary keys and foreign keys, ensuring reliable data relationships.
• Removal of Redundancy: Minimizing duplicate data by storing every piece of information only once.
• Consistency: Atomicity rules guarantee consistent and aligned data across the database.

Cons:

• Rigidity: Normalized databases' highly structured nature makes it challenging to adapt them to data and schema changes over time.
• Complex Queries: Having data spread across numerous tables leads to intricate and slower queries.
• History tracking: The requirement to track changes over time directly conflicts with the principle of storing an item of data only once. This can lead to extra complexity and compromises in design.

A normalized layer is essential in integrating and preparing data for the subsequent stages in a multi-tier data architecture, proving indispensable for businesses aiming for accuracy and integrity in their data warehousing solutions.

Normalized data models offer a structured, reliable, and redundancy-free format, which is perfect for data integration. However, their rigidity and complexity in adapting to changes can pose challenges.

Similarities and differences between Star Schema and Normalized Models

Similarities:

• Multi-Tier Architecture Placement: Both Star Schema and Normalized models are integral components of a multi-tier data architecture, with specific roles in their respective tiers. Star Schema is used in the presentation (gold) tier for end-user querying and reporting, while Normalized models are used in the integration (silver) tier as an intermediary for harmonizing raw data before it proceeds to the presentation layer.
• Requirement for ETL Pipelines: Both data models rely on comprehensive ETL (Extract, Transform, Load) pipelines to populate and maintain their structures. These pipelines are responsible for extracting data from source systems, transforming it according to business rules, and then loading it into the appropriate models.
• Flexibility and Adaptability: Star Schemas are designed with specific reporting or analytic needs in mind. New requirements often mean a new star schema is needed. Consequently star schemas are often deployed as relatively lightweight components in the data architecture, and may be frequently replaced or entirely virtualized. Similarly, normalized models are highly structured and rigid. They provide an excellent framework for ensuring consistent data storage, but have the same difficulty in handling schema drift over time.

Differences:

• Design Approach: Star Schema models focus on simplicity and ease of use. They involve a central fact table linked to dimension tables through one level of joins, creating a star-shaped layout. Star Schemas combine dimension and fact tables designed for high performance in end-user queries. In contrast, Normalized models emphasize minimizing redundancy by organizing data into small, related tables using entity relationship design techniques. They follow strict rules for data integrity and consistency, spreading data across multiple closely related tables connected by primary and foreign keys.
• End-User Focus vs. Data Integration: A Star Schema is ideal for final stage presentation and user-friendly querying. The Star Schema layout makes data retrieval straightforward but at the cost of potential data redundancy - where the same item of data is held in multiple places. Normalized models primarily serve the purpose of data integration and storage in a highly structured way. These models ensure relational integrity and eliminate redundancy, packing the highest information density.
• Complexity: Given its flatter structure, a Star Schema is simpler for query processing. It is suitable for straightforward and high-performance reporting but requires meticulous ETL staging processes to keep dimension and fact tables updated. Normalized Models are much more complex to interpret because data is distributed across numerous tables, leading to intricate queries that are harder to write and might perform slower.

Summary

Star Schema enhances the consumability and performance of end-user queries, making it suitable for the presentation layer in a multi-tier architecture. In contrast, Normalized models deliver a structured and integrity-focused foundation in the integration layer, ensuring data accuracy and minimizing redundancy. This does come at the cost of complexity in querying and adaptability to data changes.

Both types of data models are complementary in data warehousing and serve critical roles in different stages of data processing and utilization.

Further reading

• Data Vault vs Star Schema vs Third Normal Form: Which Data Model to Use?
• Building a Star Schema with Matillion
• Top 10 Tips for Creating Fact Tables Using Matillion
• Top 10 Tips for Creating Dimension Tables Using Matillion
• What to do with a Late Arriving Dimension or Early Arriving Facts
• Building a Type 2 Slowly Changing Dimension in Matillion's Data Productivity Cloud

Matillion is the data pipeline platform that empowers data teams to build and manage star schemas and normalized data models faster for AI and analytics at scale, leveraging its productivity-focused, code-optional UI, first-class Git integration, and versatile data lineage capabilities. Start a 14-day trial for free.