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.