# Data Architecture Design Patterns from a Data Modelling Perspective

Technology changes. Storage engines evolve. Processing frameworks get faster.

But data modelling principles remain remarkably stable.

When platforms struggle, it’s rarely because the compute engine is weak. It’s usually because the underlying data model wasn’t designed for scale, change, or clarity.

This article focuses purely on data modelling design patterns — independent of tools — and how they shape sustainable data architecture.

* * *

## 1\. Normalized Model (OLTP-Oriented Pattern)

The normalized model is designed for transactional systems.

It emphasizes:

*   Minimal redundancy
    
*   Strict referential integrity
    
*   Clear entity relationships
    
*   Third normal form (3NF) or higher
    

Typical use cases:

*   Order management systems
    
*   Banking systems
    
*   ERP databases
    

This model optimizes for write efficiency and consistency.

However, heavy joins make it less ideal for analytical workloads. That’s why most analytical platforms avoid deeply normalized structures.

* * *

## 2\. Dimensional Modelling (Star Schema Pattern)

Dimensional modelling is built for analytics.

Core components:

*   Fact tables (measurable events)
    
*   Dimension tables (descriptive context)
    

A simple example:

*   Fact\_Sales
    
*   Dim\_Customer
    
*   Dim\_Product
    
*   Dim\_Date
    

This pattern reduces join complexity and improves query performance.

### Why it works

*   Clear business logic
    
*   Easier reporting
    
*   Strong alignment with BI tools
    
*   Intuitive structure for analysts
    

Star schemas prioritize readability and performance over strict normalization.

* * *

## 3\. Snowflake Schema Pattern

A snowflake schema extends dimensional modelling by normalizing dimensions.

Instead of one flat dimension:

*   Dim\_Product
    
    *   links to Dim\_Category
        
    *   links to Dim\_Brand
        

This reduces redundancy but increases join complexity.

Snowflake models are useful when:

*   Dimensions are large
    
*   Hierarchies are complex
    
*   Storage optimization matters
    

However, over-snowflaking can reduce query performance and clarity.

* * *

## 4\. Data Vault Modelling

Data Vault is designed for scalability and historical tracking.

Core components:

*   Hubs (business keys)
    
*   Links (relationships)
    
*   Satellites (descriptive attributes with history)
    

Data Vault excels when:

*   Source systems change frequently
    
*   Historical traceability is critical
    
*   Multiple systems feed the warehouse
    

It separates structural keys from descriptive changes, making schema evolution easier.

However, it introduces more tables and complexity compared to dimensional models.

* * *

## 5\. Slowly Changing Dimensions (SCD Pattern)

Real-world data changes.

Customers move cities. Products change prices. Employees change roles.

SCD patterns define how these changes are handled.

Common types:

*   Type 1 – Overwrite old value
    
*   Type 2 – Maintain historical versions
    
*   Type 3 – Store limited history
    

Choosing the wrong SCD strategy leads to inconsistent reporting.

For audit-heavy domains like finance or compliance, Type 2 is often necessary.

For simpler reporting use cases, Type 1 may suffice.

* * *

## 6\. Event-Driven (Append-Only) Model

Modern architectures increasingly adopt append-only designs.

Instead of updating rows:

*   Every change becomes a new event
    
*   History is preserved
    
*   Current state is derived
    

This pattern is common in event sourcing systems.

Benefits:

*   Full traceability
    
*   Easier replay and debugging
    
*   Clear timeline of changes
    

It requires careful handling of storage growth and query design.

* * *

## 7\. Data Mart Pattern

As organizations grow, centralized warehouses become hard to navigate.

Data marts provide domain-focused subsets:

*   Finance mart
    
*   Marketing mart
    
*   Operations mart
    

Each mart is optimized for a specific business function.

This pattern improves usability but must be governed carefully to avoid duplication and inconsistent metrics.

* * *

## 8\. Canonical Data Model Pattern

In multi-system environments, each source has its own schema.

A canonical model defines:

*   Standardized entities
    
*   Unified naming conventions
    
*   Agreed business definitions
    

All sources are mapped to this canonical structure before downstream consumption.

This reduces integration complexity and improves cross-system consistency.

Without a canonical layer, organizations often suffer from conflicting definitions of “customer,” “revenue,” or “active user.”

* * *

## 9\. Wide Table vs Narrow Table Design

Two common modelling approaches in analytics:

### Wide Tables

*   Many columns
    
*   Flattened structure
    
*   Simpler querying
    

Pros:

*   Fewer joins
    
*   Faster reporting
    

Cons:

*   Harder schema evolution
    
*   More null-heavy columns
    

* * *

### Narrow Tables

*   Fewer columns
    
*   More relational structure
    
*   More joins
    

Pros:

*   Cleaner structure
    
*   Easier maintenance
    

Cons:

*   Query complexity
    

The choice depends on reporting needs and data volume.

* * *

## 10\. Semantic Layer Pattern

A semantic layer abstracts technical schemas into business-friendly models.

Instead of exposing raw tables:

*   Business metrics are defined centrally
    
*   Calculations are standardized
    
*   Definitions are governed
    

This prevents metric inconsistency across dashboards.

Without a semantic layer, every analyst calculates KPIs differently.

* * *

## Choosing the Right Pattern

There is no universal best model.

Transactional systems benefit from normalization.  
Analytical systems benefit from dimensional models.  
Highly dynamic environments may require Data Vault.  
Event-driven systems benefit from append-only structures.

The key is alignment between:

*   Business requirements
    
*   Change frequency
    
*   Query patterns
    
*   Governance expectations
    

Good data modelling is less about theory and more about anticipating scale and change.

* * *

## Final Thoughts

Data modelling decisions outlive tools and platforms.

Poor modelling creates:

*   Confusing dashboards
    
*   Performance bottlenecks
    
*   Integration conflicts
    
*   Governance headaches
    

Strong modelling creates:

*   Clear business definitions
    
*   Stable reporting layers
    
*   Easier evolution
    
*   Predictable performance
    

In the long run, architecture strength is determined not by processing speed, but by how thoughtfully data is structured.