enterprise-data-model-lab

One retail domain, modelled four ways — a business glossary, a conceptual model, a normalised logical model, and two physical targets built from identical staging tables: Kimball dimensional and Data Vault 2.0. Then the two are measured against each other on the same question.

                                   ┌─ Kimball ────────────┐
glossary ─► conceptual ─► logical ─┤                      ├─► the same answer,
  (who      (what the     (keys,   │  dim_* with SCD2      │   two very different
  owns       business      types,  │  fct_sales, one grain │   costs
  the term)  recognises)   3NF)    ├─ Data Vault 2.0 ─────┤
                                   │  hubs · links · sats  │
                                   └───────────────────────┘

Most modelling portfolios show a star schema. The argument that actually comes up in a data-architect conversation is Kimball or Data Vault, and it is usually conducted as a matter of taste. It does not have to be. Both models here are loaded from the same source in the same run, so the difference can be counted.

→ Start with reports/comparison.md. That is the page to put on a screen. Everything else exists to make it defensible.

make demo      # generate the source, load both models, assert, render, compare — under a minute

What it actually proves

Modelling craft, in the parts that are easy to get wrong. Fourteen assertions run on every build, and each one is a way a historised model fails quietly — producing a plausible number rather than an error:

SCD2-01	No overlapping validity windows. Two rows valid at one instant fans out every join and double-counts every measure.
SCD2-02	Exactly one current row per business key. Zero loses the customer; two duplicates them.
SCD2-04	No no-op versions. A version created when nothing tracked changed is invisible in reports and doubles the table every few years.
GRAIN-01/02	The fact holds its declared grain, and no source line is missing from it.
PIT-01	Facts join the dimension version that was valid at the time, not the current one. This is the entire reason for keeping history and it is one line of SQL that is very easy to leave out.
DV-01/02/03	Satellite primary keys, hashdiff actually changing between versions, and every link resolving to its hubs.
RECON-01/02	Both models agree with each other and with the source. Two models over one source that disagree on the headline number is the most expensive modelling defect there is, because both look correct in isolation.

The trade-off, priced. From the generated comparison — same data, same question, same machine:

	Kimball	Data Vault
Objects	5	11
Rows stored	28,979	91,461
Joins to answer the question	1	3
Best of 5 runs	0.79 ms	302 ms

The question was net sales by the customer segment that was in force at the time of the order — the only kind of question where the two models genuinely differ. The vault stores 3× the rows and answers ~380× slower, because Kimball resolved history once at load time and the vault resolves it per query.

That is not an argument for Kimball. It is the price of what the vault is bought for: a second source system for the same customer is one new satellite and no change to anything that exists, where the dimensional model needs an altered dimension, a history reload, and every fact row re-pointed. If the source landscape is stable, the vault is overhead. If sources arrive and change often, the dimensional model is rework. Deciding which describes the client is the architecture. Picking a modelling technique first is not.

Honesty statement

Authored AI-assisted (agentic workflow; the specs and ADRs under docs/ lead the code).

What is real: the models, the SQL, the assertions, the comparison, the timings. make demo produces all of it on a laptop.

What is not real, and is labelled as such everywhere it appears:

• The retail estate is invented. 400 customers, 120 products, 12 stores and ~180 days of orders are generated by a seeded generator in config/generate.yaml. The change events — how many times a customer's segment moves, how many times a product is recategorised — are configured on purpose, because a dimension whose attributes never change proves nothing about Type 2.
• The timings are DuckDB on one laptop. The direction and the order of magnitude carry over to a real warehouse; the absolute numbers do not. Snowflake's clustering and result cache change the vault side substantially, which is exactly why real vaults grow PIT tables and information marts.
• This is a raw vault plus a thin business vault. A production vault would add PIT and bridge tables, which is the honest answer to the 302 ms — and which is also why most estates that adopt a vault end up running a star schema on top of it.
• No dbt, no orchestrator. Plain SQL files executed in order, so the modelling is readable without learning a tool first. docs/design/decisions/0002-plain-sql-not-dbt.md says why, and what would change in production.

Layout

config/glossary.yaml     terms, owners, metric definitions        → docs/glossary.md
config/model.yaml        conceptual + logical + both physical     → docs/model-diagrams.md (Mermaid)
config/generate.yaml     the seeded source system, incl. changes
sql/10_staging.sql       logical layer — typed, deduplicated
sql/20_kimball.sql       dimensional target (SCD2, stated grain)
sql/30_datavault.sql     raw vault (hubs, links, satellites)
sql/40_business_vault.sql  where the glossary's calculations live for the vault
src/edml/checks.py       the fourteen assertions
src/edml/compare.py      the measured comparison

Commands

make build     # generate the source and load both models
make check     # run the assertions (exit 1 on any violation)
make docs      # regenerate the glossary and the diagrams from config/
make compare   # measure the two models against each other
make test      # the assertions as pytest, plus guards on the harness itself
make demo      # all of it

Where this would go in production

The staging dedupe, the SCD2 window logic and the assertions transfer directly — they are SQL. What changes: the source becomes a real CDC feed rather than a generated one, the transformations move into dbt or SQLMesh so lineage and docs come for free, the assertions become dbt tests or a data quality gate in CI, and the vault grows PIT tables so the 302 ms above stops being the vault's number. docs/modelling-walkthrough.md walks the whole chain in order.

License

MIT — Copyright (c) 2026 Fusion Platform Services – 4 Dimensions of Success.