The Consumption Layer Should Generate Itself

• The wrong argument
• What a Unified Star Schema is
• Cascading inheritance
• Why one model answers the vast majority
• Why generated, not hand-built
• One model, any shape
• The honest trade-offs
• Model once, generate the rest

Every analytics team pays the same tax, and almost none of them have it on a line item.

It works like this. The business asks a question. Someone builds a star schema to answer it: pick the fact, pick the grain, pick the dimensions, wire up the keys, test the measures. The next question arrives, and it doesn't quite fit the first star, so someone builds another. Then a third. Before long you have a galaxy of star schemas, each one a small hand-built artifact, each one slightly out of step with its neighbors, all of them drifting apart as the business changes underneath them.

We have learned to call this normal. It is not. It is the cost of building the consumption layer by hand, one question at a time.

And the parts that hurt are the parts nobody warns you about. The fan trap, where one join quietly doubles your revenue. The "which fact table do I even query" conversation that every new analyst has to relearn. The moment a measure means one thing in the sales mart and something subtly different in the finance mart, and a meeting gets spent reconciling two numbers that were never modeled the same way.

The wrong argument

So the usual debate is which star schema to build. Snowflake it or keep it flat. One fact table or several. Conformed dimensions or not.

That is the wrong argument. The question is not which star schema to build by hand. The question is why we are building them by hand at all.

Because we already model the business. If you run a proper business layer (DAB, in the Daana stack: the layer where you model entities, keys, relationships, and history the way the business actually sees them) then everything a consumption model needs is already declared. The grain, the relationships, the measures, they're all sitting right there. Building a star schema by hand is typing out something the model already knows.

So let the model generate it. An earlier piece on this blog, The Design of Analytical Data Storage Systems, made the case that star schemas belong in the consumption layer (DAR, Data According to Requirements: the layer shaped to what the consumer needs), not in the business layer. This is the other half of that argument: what the consumption layer should actually be, and where it should come from.

What a Unified Star Schema is

The pattern that makes this work is the Unified Star Schema, and credit where it is due: it is Francesco Puppini's, from his book of the same name with Bill Inmon. I'm not going to reinvent it here, just show what happens when you generate it on top of a business layer.

The idea is a single table, the bridge, that connects every entity to every other entity it can legitimately reach. When I talked it through with Puppini, he suggested calling it a "super fact" rather than a bridge, to break free of what "bridge" means in the Kimball world. The name fits. It holds all the relations and all the measures in one place.

In a USS you stop sorting tables into facts and dimensions. Every entity can play both roles. An order is a thing you measure and a thing you slice by. So is a customer. So is a product. You connect each one to the bridge, and the end user joins whatever they care about to that single structure. No fan traps, because the bridge is built to never fan out. No "which fact table", because there is one.

There is one more move that earns the "answers almost everything" claim, and it is about time. Instead of one row per entity instance, you make the bridge event-based: one row per measurement event, each carrying its own date. An order placed sets the date to the order date. An order shipped sets it to the shipped date. An order due sets it to the required date. Now every measure, across every process, lands on one canonical calendar. You can ask "what happened in Q1" across orders, shipments, and returns on a single timeline, without stitching three star schemas together. One calendar, every process.

Cascading inheritance

If you do not care how the bridge gets built, skip to the next section. All you need is that the generator works out the safe paths for you. For everyone else, the mechanism is simpler than it sounds.

You walk the business model as a graph. For each entity, you follow the relationships that go from many to one and inherit the keys and measures of everything you reach. An order line points to one order, the order points to one customer, so the order line inherits both. The rule is short: follow any path that never fans out, and inherit everything along it.

That "never fans out" part is the whole game. Many order lines rolling up to one order cannot double-count the order, so a many-to-one step is safe. A many-to-many step has no safe direction at all, so the generator skips it and tells you. What you are left with, for every entity, is the exact set of dimensions its measures are additive along. Not additive if you remember to be careful. Additive by construction, with the safe paths worked out for you.

Why one model answers the vast majority

This is where the decision-maker payoff lives.

Because the model knows which measures are additive along which dimensions, the USS can answer the vast majority of analytical questions without anyone choosing a fact table or second-guessing a grain. And it's self-describing. The same model that builds the bridge also knows, for every measure and every dimension, whether you can slice that measure by that dimension, and how. That's a sliceability contract, and it doesn't have to live in a proprietary corner. Express it as a vendor-neutral semantic contract (e.g. OSI) and any BI tool or AI agent can read it directly. "Can I break revenue down by region" stops being a modeling debate and becomes a lookup.

Two things follow from that. First, coverage is not a matter of faith. Because the list is explicit, you can read off exactly which questions the model answers and which it does not, before you trust it with anything. Second, this does not replace your BI tool, it feeds it. You point Power BI, Tableau, or Qlik at the bridge, or build your semantic model on top of it, instead of hand-building a star for every report. And do not skip the most used analytics tool on the planet just because it is unfashionable to mention it: Excel. It's not going anywhere, and ignoring it is futile. A single bridge is a PivotTable's best friend. Point Excel at the bridge and an analyst can slice the whole model, on one calendar, without waiting on anyone to build them a thing.

That is the real difference between a hand-built star and a generated USS. A hand-built star answers the questions you anticipated when you built it. A generated USS answers the questions the business model makes valid, which is a far larger set, and it tells you which ones those are.

Why generated, not hand-built

The economic argument almost writes itself.

A hand-built consumption layer scales with the number of questions. More questions, more stars, more maintenance, more drift. A generated one scales with the number of changes to the business model, which is a much slower clock. You model a new entity once, regenerate, and every analysis that can now reach it gets it for free. Consistency stops being a discipline you have to enforce and becomes a property of how the thing is built.

This is the same shift my colleagues have written about in The Rise of the Model-Driven Data Engineer and Contract-Driven Data Transformation: stop writing the artifact, declare the model and generate the artifact. In practice I generate the USS from a handful of small YAML files, and it takes seconds.

One model, any shape

Here is the part that usually ends the debate.

Once you've modeled the business, you haven't committed to one consumption format. A USS is one shape you can generate. So is a data mart, One Big Table, a classic star, a feature table. They're all projections of the same model.

Want a data mart for the finance team? It's not a separate build. It's a selection: take the entities finance cares about, generate the shape they need. Want One Big Table, because your tool or your data scientist prefers a single flat table? You can generate it flat, from the model or from the USS. Want a classic star, or a feature table? Same story.

You are no longer choosing between a USS, a star, an OBT, and a mart, and arguing about which is right. You model the business once, and you generate whichever shape the consumer in front of you needs. The shape is a detail. The model is the asset.

The honest trade-offs

None of this is free, and I would rather say where it costs than pretend it does not.

Many-to-many relationships have no fan-out-safe direction, so the generator skips them and warns you. If your domain leans hard on many-to-many, you will model around it, and that is real work. There are genuinely non-additive measures, ratios and distinct counts across the wrong grain, where you still have to think. The USS does not abolish thinking. It removes the busywork so you can spend the thinking where it actually matters.

The bigger cost is upstream, and I would rather name it than hide it. All of this rests on a business layer that is actually modeled well. The USS doesn't model your business for you. It generates the consumption layer from a business model you still have to get right, which means the hard work doesn't disappear, it moves to where it belongs: defining your entities, your keys, and your relationships once, properly. Get that layer right and everything downstream generates. Get it wrong and the generated USS will faithfully reproduce the mess.

And then there is the objection I hear most: a single bridge across everything must be slow to query. So I benchmarked it instead of arguing about it. I put the same questions to a Unified Star Schema and to an equivalent multi-fact star schema, on TPC-DS at scale factor 100, and the query performance held up. There was no meaningful penalty for unifying. The scare story about USS falling apart under load is, in my experience, just a story. It builds fine too: a bridge of roughly a billion rows reloads in about four minutes on an F64, and you can load it incrementally, by key or by updated-at, rather than reloading at all.

Model once, generate the rest

We have spent years getting very good at hand-building consumption models. We got so good at it that we stopped noticing the bill.

But the business layer already knows the grain, the relationships, the measures, and the history. The consumption layer is downstream of all of it. So model the business once, carefully, and let the consumption layer generate itself. The star schema, the Unified Star Schema, the OBT, the mart, the feature table: not things you build, things you select.