Evolving a Dimensional Model Without Rewriting Its History

Adding a column to a dimension is a five-minute change. Changing what a column means is a change to every number that has ever been reported from it, and the two are performed with the same ALTER TABLE statement. The distinction between them is the whole of model evolution: one class of change is additive and safe, and the other class is a semantic change that has to be planned as a migration because the history it invalidates cannot be recovered from the table.

Additive changes and their hidden coupling

A new dimension attribute that is populated going forward is additive. Existing rows keep their values, existing facts join as before, and no report changes. Adding a nullable column and backfilling it over time is the cheapest evolution available.

A new measure on a fact table is nearly as cheap, but only if it is computed from data the loader already reads. A measure that requires a new source join changes the load, and the load’s history cannot be re-derived unless the source retains it. The measure will then be null for all history, which is correct and which every consumer will eventually ask about.

The hidden coupling is in the grain. Adding a column to a fact table whose grain does not determine that column forces a choice: repeat the value on every row at the coarser grain, which makes it non-additive, or move it to a table at its own grain. The grain declaration is what makes this visible; without one, the new column is simply appended and the fan-out appears later as an inflated sum.

A rename is additive in the schema and destructive in the contract. Consumers reference column names, and a rename breaks every query that uses the old name. The compatible sequence is to add the new name, populate both, migrate consumers, then drop the old one — three releases, not one. The same caution applies to the versioned attributes that type 2 dimensions expose, where a renamed column changes the meaning of every historical version at once. Where the warehouse exposes a view layer, the view can carry both names for a period, which moves the coordination into the view definition rather than into every consumer.

Changes that alter meaning

Some changes preserve every byte and change every answer. Three of them account for most incidents.

Widening a type. A NUMERIC(12,2) amount becoming NUMERIC(18,4) adds precision. Historical rows are unchanged in value, but a report that rounds to cents now rounds a slightly different underlying number, and totals can differ in the last place. Usually harmless; it must be announced, because a reconciliation against a source that stores two decimals will show small differences that are not errors.

Narrowing a type. The reverse loses information silently. A string column shortened, or a numeric precision reduced, truncates or rounds existing values, and the truncation happens at ALTER time in engines that rewrite the table and at read time in engines that do not. Neither outcome is recoverable without a backup.

Redefining an attribute’s derivation. A active_customer flag that changes from “ordered in the last 90 days” to “logged in in the last 30 days” is the same column with a new meaning. Historical rows now describe a rule that was not in force when they were written, and any trend line across the change is a comparison of two different definitions. The only correct treatment is to add a new column with the new definition, leave the old one frozen at the changeover date, and document the boundary. A column whose values are frozen looks broken to a consumer who does not know why; the frozen range has to be visible in the data, usually as a null after the boundary rather than a stale value.

Changing a dimension’s grain is the largest of the semantic changes. A product dimension keyed per SKU becoming keyed per SKU per warehouse cannot be done by adding a column: every fact row’s foreign key must be re-resolved, because one SKU now maps to several keys. The mechanics are a full rebuild of the dimension with new surrogate keys, a translation table from old key to the new keys plus the attribute that disambiguates them, and a backfill of the fact table resolving each row through the translation. The fact table’s rows do not change in number, but every key changes, which means every index and every materialized aggregate over that key is invalidated.

Refining a grain without a flag day

The grain refinement is worth treating in detail because it is the change most often attempted as an in-place edit and most often abandoned halfway.

The safe sequence builds the new structure beside the old one. A new fact table at the finer grain is created and loaded from the source for new data, while the old table continues to receive data at the coarse grain. Both share the conformed dimensions, so a report can move from one to the other without changing its dimension joins. Consumers migrate one at a time. The old table is retired when its last consumer moves.

The history problem decides whether this works. The finer grain requires source rows the coarse table aggregated away — the individual lines behind an order total. If the source still has them, the new table can be backfilled and the old one retired on a date. If it does not, the new table has history only from its start date, and the two tables must coexist for the length of the reporting window. A report that spans the boundary has to union the two at a common grain, which means aggregating the fine table up to the coarse one for the overlapping period. That union is correct only if both tables use the same dimension keys and the same measure definitions, which is another argument for conforming dimensions before refining grain.

A translation table makes the coexistence explicit. It maps each coarse row to the fine rows it decomposed into, so a query can check that the fine table’s aggregate over the translation equals the coarse row exactly. That check is the acceptance test for the backfill: any difference is a row the fine loader dropped or duplicated, and finding it after consumers have migrated is much more expensive than finding it during the backfill.

Retiring a column

Dropping a column is the one change that has no compatible form, because a consumer that selects it gets an error rather than a null. The sequence that works has four steps and takes at least two releases.

The column is first marked deprecated in its documentation and, where the platform supports it, in the column’s comment. Its usage is then measured — query logs, view dependencies, lineage metadata — to find consumers the documentation did not reach. Once usage is zero, the column is dropped from the physical table but retained in a compatibility view that computes it, if the computation is still possible. The view is what lets a forgotten consumer fail late and loudly rather than at the same moment as everything else.

The computation is the part that often cannot be done. A column whose value was derived from a source the warehouse no longer reads cannot be reconstructed in a view, and the choice is between keeping the physical column indefinitely and dropping it with a warning that some queries will break. There is no third option, and pretending there is produces a view that returns nulls where the column used to have values — which is worse than an error, because it silently changes results.

Recording what the model is

A model that changes needs a record of which version of itself it is. Two artefacts carry that record in practice: a per-table model version or a changelog table listing the semantic changes with dates, and the physical metadata — column comments, nullability, and the presence or absence of constraints — that a consumer can inspect.

The changelog matters most for the changes that leave no physical trace. A widened type is visible in the catalog; a redefined flag is not. A consumer comparing a metric across the boundary has no way to know the definition moved unless something records it, and the natural place is beside the table rather than in a document that drifts.

The final constraint on evolution is the same one that governs the initial design. Every change is evaluated against the grain and the additivity of each measure, because those are the properties a consumer relies on without checking. A change that preserves the schema and breaks a measure’s additivity is more damaging than a change that breaks the schema, since the schema break is discovered at parse time and the additivity break is discovered by an accountant.