Dimensional Modelling and the Case for Wide Tables

Kimball-style dimensional modelling, a central fact table surrounded by dimension tables joined on surrogate keys, was designed when storage was expensive and joins were cheap relative to scans. Both halves of that have reversed. Columnar storage compresses repeated values so effectively that duplicating a dimension attribute across a billion fact rows may cost almost nothing, while a distributed join can require a shuffle across the network. The right model today is a live question rather than settled practice.

What the star schema provides

Update locality. A product's category changes in one row of one dimension table. In a denormalised wide table it changes in every fact row that referenced it, which is a large rewrite.

Slowly changing dimension handling. Type 2 SCDs, which keep historical versions with validity ranges, express "what was this product's category at the time of this sale" cleanly. Reproducing that in a wide table means either freezing the attribute at event time, which loses the ability to re-slice by current values, or rewriting history.

Conceptual clarity. Dimensions are the things analysts filter and group by, facts are the things they aggregate. That vocabulary is genuinely useful for communication and for building semantic layers, independent of physical storage.

What denormalisation provides

No joins at query time. A wide table is a scan, and a scan over a well-clustered columnar file is fast and predictable. Joins introduce shuffles, spills and skew, and are where distributed query plans usually go wrong.

Compression absorbs the duplication. A category string repeated across a billion rows dictionary-encodes to a few bits per row plus one dictionary. The storage cost of denormalisation in a columnar format is a small fraction of what the same duplication would cost in a row store, which is precisely the assumption that made normalisation necessary.

Simplicity for consumers. A single table with self-describing columns is easier for analysts and for downstream feature pipelines than a schema requiring five correct joins, and it removes an entire class of wrong-grain errors.

Choosing

The deciding question is how dimension attributes change and how they are used. Attributes that are stable, or where the value at event time is the correct semantics, denormalise well: the customer's country at the time of the order rarely needs restating. Attributes that change often and must be re-queried at their current value belong in a dimension table joined at query time.

The common production answer is a hybrid: a wide fact table carrying the attributes needed for the majority of queries at event-time semantics, with dimension tables retained for the attributes that need current-value lookups and for the analysts who want them. Modern warehouses make this cheap enough that maintaining both is often correct.

When it breaks

Denormalised tables drift. Once an attribute is copied into a billion rows, correcting a historical error means a rewrite that many teams quietly decline. The wide table's values slowly diverge from the dimension table's, and nobody notices until two reports disagree.

Wide tables encourage schema sprawl. Adding a column is easy, so columns accumulate, and a table with 400 columns of which 30 are used carries footer parsing costs and comprehension costs for every consumer. Splitting by access pattern is the fix and is rarely done in time.

Surrogate keys still matter. Even in a denormalised model, keeping the dimension's key alongside its denormalised attributes preserves the ability to join back for anything not copied. Dropping the key to save a column is a decision that is expensive to reverse.

Update cost is asymmetric across engines. In a copy-on-write table format, updating one attribute across a billion rows rewrites every affected file. In merge-on-read it writes delete vectors and defers cost to readers. The right denormalisation depends on which of those the table uses, so a model that is fine on one storage layer can be untenable on another.