Star-Schema Decisions That Age Badly

A star schema does not fail the way a pipeline fails. Nothing throws. Reports keep rendering, refreshes keep completing, and the numbers keep looking plausible. What degrades is the distance between the questions the model was shaped to answer and the questions people now ask it. The model becomes a place where every new requirement costs a flag, a bridge table, or a second copy of a dimension — and each of those is a small payment on a design decision made when the business looked different.

The useful skill is not avoiding bad decisions. It is knowing which decisions are structural — expensive to reverse once measures, reports and downstream extracts have been built on them — and putting a review habit around exactly those.

Which decisions are the structural ones?

Five: the declared grain of each fact, which dimensions are conformed, the key strategy, what history you retain, and the column you partition on. Everything else in a star schema is comparatively cheap to change. Rename a column, add an attribute, split a measure — those ripple, but they ripple predictably. The five below change the shape of every query written against the model.

Why does grain drift instead of breaking?

Because changing grain invalidates every measure written against the fact, and nobody wants to do that in the middle of a reporting cycle. So the grain does not change. Exceptions get absorbed instead.

The pattern is recognisable. A fact is declared at order-line. Then returns arrive, and a return is not an order-line, so it becomes a negative row with a type flag. Then partial shipments arrive, and a shipment is neither, so a second flag appears. Then a commercial arrangement lands where revenue is recognised across periods, and a proration row is inserted with a null product key. Each addition is defensible in isolation. Collectively they mean the fact no longer has one grain — it has four, distinguished by flags that every measure must now filter on, and that new report authors will not know about.

The symptom is measures that only work when a particular slicer is set. The cause is that the grain statement lives in a comment or a wiki page rather than in a test. A grain declaration that is not asserted in code is a description of the past.

-- fct_order_line: one row per order, line, and revenue event type
-- Fails the build if the declared grain stops holding.

select
  order_id,
  order_line_id,
  revenue_event_type,
  count(*) as row_count
from {{ ref('fct_order_line') }}
group by 1, 2, 3
having count(*) > 1

When that test starts failing, you have a decision to make deliberately rather than a flag to add quietly. That is the whole value of it.

What makes a conformed dimension stop conforming?

A new source system, and the absence of anyone empowered to renegotiate the definition.

Conformance is a social agreement enforced in code. Billing has a customer. The CRM has a customer. The support platform has an account that is sometimes a customer and sometimes a site. When the model was built, two of these were in scope and somebody reconciled them. When the third lands, the reconciliation work is large, the deadline is not, and a new dimension appears beside the old one with a suffix. Nothing is broken. Reports built on the new dimension simply count a different population from reports built on the old one, and both are labelled “customers”.

The defensive move is not to forbid new dimensions. It is to make the suffix visible. A dimension named for its source rather than its concept is honest; a second dimension named for the concept is a trap.

Do surrogate keys still earn their place?

Yes, and the reason is less about performance than about surviving source-system behaviour you did not agree to.

Natural keys fail in specific ways. A source reassigns an identifier after a record is deleted. Two sources use the same identifier space for different entities. A merger introduces a second tenant whose identifiers collide with the first. Each of these turns a join into a silent fan-out, and a fan-out inflates additive measures without producing an error.

The surrogate key is not free — it costs a lookup and a layer of indirection when debugging. It buys the ability to absorb a source change without rewriting facts. That trade improves as the number of sources grows, which is the direction models move.

Which history decision is hardest to reverse?

Overwriting an attribute you later need to see historically. Type 2 versioning can be added to a dimension at any time; what cannot be added is the history you never kept. If sales territory has been overwritten on change for two years, retrospective territory analysis is not a modelling task, it is an archaeology task, and it may simply be impossible.

The cheap insurance is to separate two questions that get answered together. Question one: does the dimension expose versioned attributes to report authors? Question two: does the platform retain immutable snapshots of the source records? You can answer no to the first and yes to the second. Retaining raw, timestamped source extracts preserves the option to construct history later, without paying the modelling and query complexity of versioning attributes nobody has asked about.

How does partitioning become a modelling decision?

Through the choice of which date governs the partition. Incremental refresh processes only changed partitions rather than a full dataset, which means the model’s refresh economics are set by how reliably you can answer “which partitions changed?”

Partition on a business date — order date, service date, posting date — and you inherit that date’s amendment behaviour. If back-dating is permitted upstream, a row that lands today can belong to a partition closed months ago. If the refresh only looks at recent partitions, that correction never appears. If the refresh compensates by widening the window, the incremental strategy quietly becomes a full reload with extra steps.

The modelling consequence: a fact that carries both an event date and a system-modified date can be partitioned on the first and change-detected on the second. A fact that carries only the business date has foreclosed that option, and reversing it means reloading history from a source that may no longer hold it.

Where does row-level security interact with model ageing?

At the point where a secured dimension gets duplicated. Row-level security filters a model’s data by who is asking, and that filter is anchored to a specific dimension and a specific relationship path. Snowflake that dimension, add a second copy for a new source, or introduce a bridge table to resolve a many-to-many, and the filter path can change without the security definition changing with it.

The failure modes are asymmetric. Over-filtering is reported immediately, because somebody cannot see their own data. Under-filtering is reported by nobody, because the person seeing too much has no way to know. That asymmetry is an argument for testing security as data — a fixture of principals and expected row counts per principal, run on every model change — rather than treating it as configuration that is checked once at go-live.

How do you review a model without rebuilding it?

By running a small, repeatable inventory rather than waiting for a redesign to be funded. Three lists, produced on a cadence you actually keep:

Measures that no report references. These are maintenance surface with no consumer. Deprecate them loudly before deleting them, because absence of references in the tooling is not the same as absence of use.

Dimension attributes that nothing filters or groups by. Wide dimensions accrete columns because adding one is easy. Each unused column is a refresh cost and a thing a report author has to scroll past when looking for the right field.

Facts whose declared grain no longer passes its own test. This is the list that matters most, because a failing grain assertion is the precursor to every flag-driven exception described above.

What ages well?

Narrow facts with grain asserted in code. Surrogate keys with an inspectable mapping. Raw source snapshots retained independently of whether the dimension versions anything. A partition column chosen alongside a reliable change signal, not just a business meaning. A semantic layer holding metric names, because a semantic layer centralises metric definitions so separate reports agree — which means renaming a physical column is a model change, not a contract change, and the blast radius stays inside the warehouse.

What ages badly is any decision that was correct for the sources in scope on the day it was made, recorded in prose, and never revisited when the scope changed. The schema is not the artefact that decays. The assumptions underneath it are, and they decay silently unless something in the build is configured to notice.