A Field Guide to Data Modelling Decisions

Data modelling debates are religious wars fought over what is actually a procurement decision: you are buying future query patterns with present-day complexity, and every style pays a different price. After refereeing this argument at four companies, here’s the field guide I hand to teams before the shouting starts.

The three contenders

Star schema — facts in the middle, dimensions around them. Kimball’s fifty-year-old gift to analysts who need consistent answers. One Big Table (OBT) — denormalise everything into wide tables; let the columnar engine cope. Data Vault — hubs, links, and satellites; an audit-first style built for organisations where lineage is a regulatory noun.

The mistake teams make is treating these as ideologies. They’re insurance products. The question is never “which is correct” — it’s “which failure can you least afford”: inconsistent metrics, unqueryable history, or a six-month modelling phase before anyone sees a dashboard.

The scoring rubric

Four questions, scored honestly with the people who will do the work. Here’s the aggregate scoring from my last three warehouse engagements — how well each style served the thing teams actually needed:

Reading that back: OBT wins the sprint, star schema wins the marathon, and Data Vault wins the audit. The bar none of them wins is the last one — no modelling style survives requirements nobody has agreed on. That’s not a data problem; stop trying to solve it with joins.

What the argument actually sounds like

A composite design review, condensed:

Concern	Star	OBT	Vault
“Finance and Ops report different revenue”	✅ conformed dims	⚠️ hope	✅ but slower
“We need a dashboard by Friday”	⚠️ next Friday	✅ this Friday	❌ which quarter?
“The regulator wants row-level history”	⚠️ SCD2 gymnastics	❌ good luck	✅ born for this
“We have two data engineers”	✅ fine	✅ fine	❌ genuinely no

That last row does more work than the rest of the table combined. Data Vault below a certain team size isn’t a methodology, it’s a hostage situation.

The move that usually wins

In practice I keep landing on the same hybrid, and I’ve stopped apologising for it: star schema core, OBT serving layer, vault-ish raw history.

-- The raw layer keeps receipts (vault instinct, without the ceremony)
create table raw.orders_history (
  order_id      bigint,
  payload       variant,
  loaded_at     timestamp_ntz default current_timestamp(),
  source_system varchar
);

-- The core layer agrees on truth (star instinct)
create table core.fct_orders (
  order_key     bigint references core.dim_order_details,
  customer_key  bigint references core.dim_customer,
  order_date    date,
  net_amount    number(18,2)   -- ONE definition, argued about ONCE
);

-- The serving layer goes fast (OBT instinct)
create table marts.orders_wide as
select f.*, c.segment, c.region, d.channel
from core.fct_orders f
join core.dim_customer c using (customer_key)
join core.dim_order_details d using (order_key);

Raw history means you can re-decide later — the most underrated architectural property there is. The star core is where definitions get argued into agreement exactly once. The wide marts are rebuildable from core by design, so denormalisation stops being risky and starts being cheap.

The coaching note

When I run this decision with a team, the rubric scoring is done by the engineers, not by me — I only referee the honesty of the scores. The first time through, someone always scores their favourite style generously. That’s fine. We write the scores down, ship the first mart, and re-score two months later against reality.

The re-scoring session is the actual lesson. Not “which model won”, but: we made an architectural decision with explicit criteria, and reality graded our homework. Engineers who’ve been through that loop twice start doing it unprompted, on everything. Which means the modelling decisions after this one won’t need me in the room.

That’s the pattern hiding under every post on this site, so I’ll stop pretending it’s a coincidence.

---

No actual Data Vault practitioners were harmed in this post. Two were mildly annoyed.