Data Modeling for Big Data: Spark, Hadoop, and the Lakehouse Era

When data outgrows a single database, the rules of modeling don’t disappear — they change shape. There’s no CREATE INDEX to save a slow query over ten billion rows in a data lake. There are no foreign keys to protect integrity, no UPDATE to quietly fix a record (for years there wasn’t even a practical DELETE). What you get instead is a different set of levers — file formats, partition layout, distribution, and table formats — and modeling for Spark and lake architectures means learning to pull them deliberately.

This post covers how the discipline translates: what survived from the Hadoop era, the physical design decisions that dominate performance, the medallion architecture that organizes lakes into layers, and why modern table formats (Delta, Iceberg, Hudi) changed what’s possible.

The Mental Shift: You Are Modeling Files

In a database, you model tables and the engine owns the physical layout. In a lake, the physical layout is yours — a “table” is a directory of files on object storage (S3, GCS, ADLS), and every design choice about those files directly becomes query performance:

Format: columnar or bust. Analytical queries read a few columns from many rows, so columnar formats — Parquet, or ORC in Hive-legacy shops — are the default for anything queried. A query touching 3 of 200 columns reads ~1.5% of the bytes, and per-column compression (plus min/max statistics per file, letting engines skip files entirely) multiplies the win. Row formats (JSON, CSV, Avro) belong only at the ingestion edge — Avro legitimately so for streaming, since appending rows is its strength.

File size: the small-files disease. Object stores and Spark both hate millions of tiny files — every file costs a listing call, an open, a task. A streaming job innocently writing a file per minute per partition key will eventually bring a lake to its knees. Target roughly 128 MB–1 GB per file and schedule compaction as a standing chore, not an emergency response.

Partitioning: the index you actually get. Directory-level partitioning (/events/date=2026-07-06/) is the lake’s substitute for indexing — queries filtering on the partition column skip entire directories (partition pruning). The craft is choosing columns that are (a) filtered in most queries and (b) low-cardinality: date is the classic; region or tenant sometimes earns a second level. Partitioning by user_id — a million directories of tiny files — recreates the small-files disease with extra steps. For high-cardinality columns you filter often, use sorting/clustering within files instead (Z-ordering in Delta, sort orders in Iceberg): min/max statistics then let engines skip most files without any directory explosion.

s3://lake/gold/fct_orders/
  order_date=2026-07-05/  part-0001.parquet  (~256 MB, sorted by customer_id)
  order_date=2026-07-06/  part-0001.parquet

Modeling Trade-offs: Joins Are the Enemy, Wide Tables Are Fine

Distributed engines execute joins by shuffling — repartitioning both datasets across the cluster by join key over the network — and shuffles dominate the cost of most big Spark jobs. That single fact reshapes several classic modeling instincts:

Denormalization gets cheaper, joins get dearer — the opposite of the OLTP calculus. Pre-joining dimensions into wide fact tables during transformation (paying the shuffle once, at write time) is standard practice, because storage is nearly free and repeated read-time shuffles are not.
Star schemas still work, with a twist: small dimensions get broadcast (shipped whole to every node, no shuffle), so classic fact-to-dimension joins stay cheap as long as dimensions stay broadcast-sized (megabytes to low gigabytes). Giant dimension? Consider embedding its hot columns into the fact.
Nested structures are first-class. Parquet and Spark handle structs and arrays natively, so an order with its line items as an embedded array is a legitimate model — one that eliminates the orders-to-lines join entirely. The document-modeling instincts from earlier in this series (bounded arrays, aggregate boundaries) apply verbatim.
Skew is a modeling concern. If 30% of your events share one key (the anonymous user, the null tenant), every join or aggregation on that key funnels through one poor executor. Fixes are physical (salting, adaptive execution) but the detection belongs in modeling: know your keys’ distributions before they know you.

The Medallion Architecture: Layering the Lake

The organizing pattern that emerged for lakes — bronze/silver/gold, popularized under the “medallion” name by Databricks — is best understood as staged modeling, echoing the raw-vs-serving distinction from the enterprise modeling post in this series:

Bronze lands data exactly as received, append-only, schema-on-read. Its job is replayability: when logic changes, you rebuild downstream from bronze instead of begging source systems for a re-extract. Model decision: keep original payloads (yes), partition by ingestion date (yes), clean anything (no).
Silver is where modeling proper happens: types enforced, duplicates resolved, keys standardized, entities conformed — the lake’s equivalent of 3NF-ish integration, one trustworthy table per business entity/event.
Gold is consumption-shaped: dimensional marts, wide denormalized tables, aggregates — the analytical modeling post’s content, materialized at lake scale.

The layers are a contract about guarantees, not just tidiness: bronze promises completeness, silver promises cleanliness, gold promises meaning. Consumers pick their layer accordingly (ML feature pipelines often want silver; dashboards want gold).

Table Formats: The Upgrade That Made Lakes Honest

Classic Hadoop/Hive lakes had a dirty secret: no transactions. A job failing mid-write left readers seeing half a dataset; updating one record meant rewriting whole partitions; concurrent writers corrupted each other; DELETE for a GDPR request was an engineering project. Modern table formats — Delta Lake, Apache Iceberg, Apache Hudi — fixed this by adding a metadata/transaction layer over Parquet files, and they’ve become the default so quickly that new lakes without one are a red flag. What they buy you, modeling-wise:

ACID transactions — readers never see partial writes; concurrent jobs merge safely.
Row-level operations — MERGE INTO makes CDC upserts and late-data corrections routine; deletes make compliance feasible.
Schema evolution — add/rename/widen columns as metadata operations, with enforcement against accidental drift (the schema evolution post’s concerns, handled at the format level).
Time travel — query the table as of last Tuesday; reproduce the numbers a model trained on.
Hidden/evolvable partitioning (Iceberg especially) — change partition strategy without rewriting history or breaking queries, unwinding the old “partitioning is forever” terror.

The practical consequence: the lake now supports warehouse-grade modeling semantics, which is the entire premise of the lakehouse — one storage layer, open formats, both SQL analytics and ML on top. The modeling skills from the warehouse posts in this series transfer directly; only the physical levers differ.

A Design Checklist for Lake-Scale Models

Columnar format (Parquet) everywhere past ingestion; a table format (Delta/Iceberg) on anything mutable or multi-writer.
Partition by date (+ at most one more low-cardinality column); cluster/sort within files for high-cardinality filter columns.
File sizes 128 MB–1 GB, with compaction scheduled.
Medallion layers with explicit guarantees; bronze immutable and replayable.
Joins audited: small dimensions broadcastable, big joins pre-computed at write time, skewed keys identified.
Grain declared per gold table — ten billion rows make mixed grain more expensive to discover, not less.

The through-line: big data didn’t repeal modeling, it physicalized it. The entities, grains, and histories are the same ones this series has been designing all along — what changed is that the modeler now also owns the file layout those designs live in. Get both layers right and “big” stops being the interesting adjective; it’s just data again.