Data Modelling Patterns in Big Data
Big data modelling is not just traditional modelling stretched across more machines.
It’s modelling under very different physics.
You are working with:
Distributed storage spread across nodes
Immutable files instead of updatable rows
No traditional B-tree indexes
Expensive shuffles across the network
Evolving schemas
Append-heavy ingestion patterns
Design something the way you would in an OLTP database, and it might look elegant. Deploy it in Spark at scale, and it may collapse under shuffle pressure and file scans.
In distributed systems, modelling decisions directly influence:
How files are written
How much data gets shuffled
How joins execute
How much storage grows
How long queries take
Below are modelling patterns that consistently survive real-world Spark workloads — with examples that show where they fit.
Star Schema — Still the Workhorse
The star schema has not disappeared in the big data world. It just needs to be applied carefully.
Scenario:
An e-commerce platform tracks billions of order items. Business users want daily revenue by category, region, and customer segment.
You model:
Fact table: fact_order_items
order_id
customer_id
product_id
order_date
quantity
revenue
Dimension tables:
dim_customerdim_productdim_date
Partition the fact table by order_date.
Now a query like:
SELECT p.category,
SUM(f.revenue)
FROM fact_order_items f
JOIN dim_product p
ON f.product_id = p.product_id
WHERE f.order_date BETWEEN '2026-01-01' AND '2026-01-31'
GROUP BY p.category;
Only January partitions are scanned.dim_product is small enough to broadcast.
Shuffle stays manageable.
The star schema works because it keeps large data in one place (fact) and small descriptive data in dimensions.
Adjustment for Spark:
Avoid over-normalizing dimensions. Keep them compact enough for broadcast joins. Excessive joins are expensive in distributed systems.
At terabyte scale, star schema remains one of the safest consumption-layer designs.
Slowly Changing Dimensions (SCD Type 2) — With Discipline
Historical tracking is unavoidable.
Scenario:
A customer moves from “Standard” to “Premium” membership. Finance needs to know what tier the customer had at the time of each purchase.
You implement SCD Type 2 in dim_customer:
effective_start_date
effective_end_date
current_flag
With Delta Lake, MERGE INTO makes this manageable.
But here’s where many teams stumble: frequent updates cause file rewrites.
If you update dimensions every few minutes, small files explode and metadata grows.
Better approach:
Batch SCD updates daily.
Compact regularly.
Avoid continuous micro-merges unless absolutely necessary.
SCD works well in Spark — but operational hygiene matters more than syntax.
Data Vault — Strong in Raw Integration
When source systems are unstable, Data Vault can be a lifesaver.
Scenario:
Marketplace sellers send inconsistent product feeds. Payment providers change schemas quarterly. Regulatory audits require raw traceability.
Instead of forcing everything into a star schema immediately, you model:
Hubs (business keys like customer_id, order_id)
Links (relationships between entities)
Satellites (descriptive attributes with history)
This design is append-friendly. It tolerates change.
Spark handles append-heavy structures efficiently, and Delta handles ACID guarantees.
But querying Vault directly for BI becomes join-heavy and slow.
The pattern that works:
Vault in raw layer
Star or wide tables in curated layer
Data Vault is an ingestion strategy, not a reporting strategy.
Medallion Architecture — Organizing Complexity
Layering is less about schema and more about containment.
Scenario:
Clickstream data arrives unclean. Product feeds have schema drift. Order events occasionally duplicate.
You structure:
Bronze: Raw append-only ingestion
Silver: Cleaned, deduplicated, standardized data
Gold: Business-ready models (star schema or aggregates)
When a source breaks, you fix Silver without touching Gold.
When business logic changes, you adjust Gold without corrupting Bronze.
This layered approach prevents upstream chaos from propagating downstream.
In large Spark environments, this separation reduces operational stress more than any specific schema pattern.
Wide Tables — Reducing Shuffle at Scale
Normalization is elegant. Shuffle is expensive.
Scenario:
A recommendation engine joins:
Orders
Clickstream events
Customer demographics
Product metadata
Campaign interactions
Repeated joins slow training pipelines.
Solution: build a wide feature table.
customer_id
150 engineered features
timestamp
Yes, storage increases.
But training pipelines become dramatically faster because the joins are precomputed.
In distributed systems, storage is cheap compared to shuffle.
Wide tables are often the right trade-off for ML and BI-heavy workloads.
Time-Based Partitioning — The Default Choice
Time is the most predictable filter dimension.
Scenario:
Daily sales dashboard always filters on a date range.
Partition fact_orders by order_date.
Now queries automatically prune partitions.
Avoid partitioning by high-cardinality fields like customer_id. That creates millions of small directories and cripples metadata performance.
Combine time partitioning with clustering (like Z-Ordering) on high-cardinality keys.
Time-based partitioning is stable, predictable, and operationally manageable.
Feature Tables — Designed for Reproducibility
Machine learning workloads demand consistency.
Scenario:
A churn model needs features exactly as they existed on 2026-01-01.
Feature table design:
entity_id
feature columns
feature_timestamp
Instead of recomputing joins every time, materialize features periodically.
Immutable snapshots ensure:
Point-in-time correctness
Reproducibility
Faster training
In ML modelling, reproducibility outweighs normalization purity.
Event-Driven Append Model — Scaling Streaming Systems
Streaming systems struggle with row-level updates.
Scenario:
Inventory updates stream in every second.
Instead of updating rows, record state transitions:
product_id
quantity_change
event_timestamp
Downstream jobs compute current inventory.
Append-only fits Spark’s distributed write model better than frequent updates.
This reduces concurrency conflicts and improves write scalability.
Snapshot vs Incremental Tables
Both approaches have a place.
Snapshot model example:
Daily snapshot of customer balances.
Simple queries.
Higher storage.
Incremental model example:
Store only transaction deltas.
Lower storage.
Queries must reconstruct state.
In practice, many systems combine both:
Incrementals for detailed analysis
Periodic snapshots for BI
The decision depends on SLA sensitivity versus storage cost.
Aggregated Summary Tables — Serving Low-Latency BI
BI dashboards often repeat the same aggregations.
Scenario:
Marketing dashboard calculates monthly revenue by category and region.
Instead of scanning billions of rows every time, create:
fact_monthly_revenue
year_month
category
region
total_revenue
Refresh nightly.
Dashboard latency drops dramatically. Cluster load stabilizes.
Materialized aggregates reduce both compute waste and cost.
Anti-Patterns That Cause Pain
Certain designs repeatedly fail in Spark environments:
Over-normalization causing excessive joins
Partitioning by high-cardinality keys
Designing schemas without reviewing query history
Continuous row-level updates on massive tables
Expecting index-like behavior
Distributed systems punish unnecessary data movement.
Choosing the Right Pattern
There is no universal template.
Ask instead:
What queries dominate workload?
Are joins frequent and heavy?
How large will fact tables grow?
Are updates frequent?
Is cost more constrained than latency?
Modelling in Spark is workload-driven.
What Usually Works in Large Systems
In mature Spark platforms, a common structure emerges:
Raw ingestion (often Vault-style or append-only)
Standardized Silver layer
Gold star schema or wide tables
Time-partitioned fact tables
Clustering on selective keys
Materialized summary tables
This hybrid approach balances governance, performance, and cost.
Final Thought
Big data modelling is not about textbook normal forms.
It’s about:
Reducing shuffle
Controlling file growth
Enabling effective pruning
Aligning layout with access patterns
In distributed systems, data placement and join strategy matter more than theoretical elegance.
At scale, modelling becomes performance engineering.
The systems that survive growth are not the most academically pure.
They are the ones designed with real workloads, real file behavior, and real compute economics in mind.