Data Modeling for the Modern Stack: Kimball, Data Vault, OBT & Medallion

Cheap storage and fast columnar engines led some teams to a tempting conclusion: modeling is optional now — just land the data and write SQL. A few years later, many of those teams have hundreds of tables with names like orders_final_v3, five definitions of “active customer”, and dashboards that disagree in front of the board.

Modeling was never mainly about performance. It’s about meaning: deciding what a row represents, where each fact and attribute lives, and how history is kept. That matters even more now that AI agents query your data — a language model can’t ask a colleague which of five revenue columns is the right one.

This is Part 5 of the Data Architecture series. Parts 2, 3 and 4 covered where data lives and how it’s governed. This part is about how the tables themselves should be designed. We’ll model one business four ways, compare the approaches honestly, and end with a practical, layered recipe.

The running example

Throughout this article we’ll model the same small business: an online retailer that sells through a website, an app and a few physical stores.

• Sources: a shop database (orders, order lines), a CRM (customers), a product information system (products), and a returns service.
• Questions the business asks: revenue by week, channel and category; customer lifetime value; return rates by product; how revenue would look under last year’s customer segments.
• Complications: customers change segment and country over time; products move between categories; the CRM and shop disagree on some customer IDs; returns arrive weeks after orders.

These are exactly the problems modeling exists to solve.

First principles: grain, keys and history

Three ideas underpin every approach.

Grain is what one row represents: “one row per order line”, “one row per customer per day”, “one row per page view”. Declaring grain is the single most important modeling decision. Most “the numbers don’t match” incidents are grain bugs — a join that silently duplicates rows, or two tables with different grains summed together.

Keys identify things. Natural (business) keys come from sources (customer_id, sku). Surrogate keys are generated by the warehouse (an identity column, a UUID or a hash) and insulate you from source changes, collisions and history versions.

History is how you handle change. Do you overwrite a customer’s country when they move, or keep both versions with validity dates? The answer changes revenue-by-country for every past period, so it must be a deliberate decision per attribute.

Kimball: dimensional modeling

Ralph Kimball’s dimensional modeling — popularized by The Data Warehouse Toolkit (third edition, 2013) — organizes data into facts and dimensions for analysis.

• Fact tables record business events or measurements at a declared grain: numeric, mostly additive measures (quantity, net_amount) plus foreign keys to dimensions.
• Dimension tables hold the descriptive context people filter and group by: customer, product, date, channel.
• Conformed dimensions are shared across fact tables, so “revenue by customer segment” and “returns by customer segment” use exactly the same segment definition.

Three kinds of fact table

Slowly changing dimensions (SCD)

Dimensions change. Kimball catalogued standard ways to handle it; the three you’ll use most:

Type 2 is what lets you answer “revenue by the segment the customer was in at the time”. Here’s the classic pattern with MERGE, which both Delta Lake and Apache Iceberg support in Spark SQL:

-- SCD Type 2 for dim_customer (Spark SQL on Delta or Iceberg)
MERGE INTO gold.dim_customer AS t
USING (
  -- changed or new customers, keyed for the "close old version" match
  SELECT s.customer_id AS merge_key, s.* FROM silver.customer_changes s
  UNION ALL
  -- changed customers again, with a NULL key so they fall through to INSERT
  SELECT NULL AS merge_key, s.*
  FROM silver.customer_changes s
  JOIN gold.dim_customer d
    ON d.customer_id = s.customer_id AND d.is_current AND d.row_hash <> s.row_hash
) AS src
ON t.customer_id = src.merge_key AND t.is_current
WHEN MATCHED AND t.row_hash <> src.row_hash THEN
  UPDATE SET is_current = false, valid_to = src.updated_at
WHEN NOT MATCHED THEN
  INSERT (customer_key, customer_id, segment, country, row_hash, valid_from, valid_to, is_current)
  VALUES (uuid(), src.customer_id, src.segment, src.country, src.row_hash, src.updated_at, NULL, true);

Strengths: intuitive for analysts and BI tools; excellent query performance; conformed dimensions create consistency across the business; well understood for decades. Weaknesses: requires upfront design and discipline; awkward when sources are many, volatile and disagree; not designed to preserve raw source history for audit.

Inmon: the normalized enterprise warehouse

Bill Inmon’s approach builds an integrated, normalized (third normal form) enterprise model first, then publishes dimensional data marts from it. It minimizes redundancy and gives one integrated view of the business.

In modern stacks, a full Inmon EDW is rare — it’s slow to build and hard to change. But its core idea survives in the silver layer: cleaned, deduplicated, integrated entities (customers, orders, products) with conformed keys, from which dimensional models are built.

Data Vault 2.x: built for integration and audit

Data Vault, created by Dan Linstedt, models the integration layer for environments with many sources, frequent change and strict audit requirements. It splits everything into three table types:

• Hubs — one row per unique business key (customer_id, order_id, sku), with a hash key and load metadata.
• Links — relationships between business keys (order ↔ customer, order ↔ product).
• Satellites — descriptive attributes and their full history, per source system, attached to a hub or link.

Every table is insert-only — nothing is updated or deleted — so loads can run in parallel, any past state can be reconstructed, and every value traces back to its source and load time. Hash keys computed from business keys let hubs, links and satellites load independently without lookups.

The trade-off is that Data Vault is not for end users. Querying it needs many joins, so you add point-in-time (PIT) and bridge tables for performance, and publish dimensional models on top for consumption.

Data Vault 2.1 (the current version of the methodology) adds guidance for semi-structured data such as JSON, makes the separation between logical and physical modeling explicit, and maps the approach onto data mesh, data fabric and lakehouse architectures.

Use Data Vault when: you integrate many (often conflicting) sources, sources change frequently, and auditability or regulatory traceability is a hard requirement — banking, insurance, healthcare, large enterprises. Skip it when: you have a handful of SaaS sources and a small team; the extra tables and tooling will cost more than they return.

One Big Table: wide, denormalized marts

One Big Table (OBT) pre-joins facts and dimension attributes into a single wide table per use case: every order line with the customer’s segment, the product’s category and the channel’s region already attached.

CREATE OR REPLACE TABLE serving.order_lines_wide AS
SELECT f.order_id, f.line_number, d.date, d.fiscal_quarter,
       c.customer_id, c.segment, c.country,
       p.sku, p.brand, p.category,
       ch.channel, ch.region,
       f.quantity, f.net_amount
FROM gold.fct_order_lines f
JOIN gold.dim_date d      ON f.date_key = d.date_key
JOIN gold.dim_customer c  ON f.customer_key = c.customer_key   -- SCD2 version at order time
JOIN gold.dim_product p   ON f.product_key = p.product_key
JOIN gold.dim_channel ch  ON f.channel_key = ch.channel_key;

Columnar engines changed the economics. Queries read only the columns they reference, so width is cheap; repeated values compress extremely well; and analysts (and AI agents) can answer most questions without writing joins.

What columnar engines didn’t change:

• Temporal meaning is frozen at build time. Each attribute in a wide table reflects either “as of the event” or “as of the last rebuild” — and the reader can’t tell which unless you document it.
• There’s no single home for an attribute. Fixing a wrong category means rebuilding every wide table that copied it.
• Grain isn’t enforced. Nothing stops a careless join from changing what a row means.
• Consistency moves into pipelines. Two wide tables only agree if both were derived from the same dimensions.

A related pattern, the activity schema, goes further: a single time-ordered stream of customer activities (ordered, returned, contacted_support) with a few standard columns. It’s well suited to customer-journey analytics, but it’s a specialist tool rather than a general warehouse design.

Putting it together: models by layer

Here’s how the approaches fit the medallion layers introduced in Part 1:

Choosing for your situation

• Startup or small team, few SaaS sources: clean silver entities → a small Kimball core (a few facts, conformed dimensions) → wide tables for the main dashboards.
• Mid-size company, growing sources: the same, with stricter conformed dimensions, SCD Type 2 for key attributes, and a semantic layer.
• Large or regulated enterprise, many conflicting sources: Data Vault in silver (with automation), Kimball marts in gold, wide tables and a semantic layer for serving.
• Event-heavy product analytics: transaction facts at event grain, an activity-style stream for journeys, wide tables for exploration.

Physical design in a lakehouse

Logical models are portable; physical design depends on your engine and table format (see Part 3).

• Keys. Use surrogate keys for dimensions (identity columns, UUIDs, or deterministic hashes of business keys). Hash keys make loads parallel and idempotent; integer identities are compact and join fast. Pick one convention and document it.
• Constraints. Lakehouse engines generally don’t enforce primary and foreign keys. Some (such as Databricks) let you declare them as informational constraints, which BI tools read for relationships and optimizers can use when you assert they hold. Enforce them with tests instead.
• Partitioning and clustering. Partition or cluster large fact tables by date; cluster on frequently filtered or joined keys. Avoid over-partitioning small tables.
• Dimension size. Small dimensions are broadcast to every worker and join almost for free. Very large dimensions (hundreds of millions of customers) are where pre-joining into wide tables pays off.
• Semi-structured data. Keep raw JSON in VARIANT (now available in both Iceberg v3 and Delta) in silver, and promote the fields people actually use to typed columns in gold.
• Late-arriving data. Returns arrive weeks after orders; dimension changes can arrive after facts. Decide how facts pick their dimension version (event time vs load time) and handle “unknown member” rows explicitly.

Testing and contracts: modeling as code

Models are only as good as the guarantees around them. Transformation frameworks such as dbt and SQLMesh let you declare grain, keys and relationships as tests that run in CI:

# dbt: declare the grain and relationships, and enforce the contract
models:
  - name: fct_order_lines
    description: "One row per order line. Grain: order_id + line_number."
    config:
      contract: {enforced: true}
    columns:
      - name: order_line_key
        data_type: string
        data_tests: [unique, not_null]
      - name: customer_key
        data_type: string
        data_tests:
          - not_null
          - relationships:
              arguments: {to: ref('dim_customer'), field: customer_key}
      - name: net_amount
        data_type: decimal(12,2)

A unique test on the grain key catches the most common modeling bug — accidental fan-out from a join — before it reaches a dashboard.

The semantic layer: define metrics once

Even a perfect dimensional model leaves one problem: every dashboard, notebook and agent can still compute “net revenue” or “active customer” differently. A semantic layer defines metrics, dimensions and relationships once, in code, and serves them consistently to every tool.

This space is standardizing. The Open Semantic Interchange (OSI) initiative — led by Snowflake with members including dbt Labs, Databricks, Salesforce and many BI and catalog vendors — published a finalized, vendor-neutral specification for datasets, metrics, dimensions and relationships in January 2026, under the Apache 2.0 licence. dbt Labs also announced MetricFlow, the engine behind its semantic layer, as an open-source project at Coalesce 2025.

For architects, the implication is simple: your gold layer and your semantic layer are designed together. Facts and conformed dimensions provide the clean structure; the semantic layer provides the shared business meaning on top. The Analytics series will cover semantic layers in depth.

Modeling for AI and agents

Everything above makes data easier for machines as well as people. A few habits matter especially when LLMs and agents are consumers:

• Descriptive, consistent names — net_revenue_usd, not amt2. Names are prompts.
• Documented grain and meaning on every table and column, stored in the catalog where agents can read it (see Part 4).
• Metrics in a semantic layer, so agents query governed definitions instead of improvising SQL aggregations.
• Agent-friendly serving tables — curated wide tables with clear grain reduce join mistakes dramatically.
• Entity-centric views (everything about a customer or product) for retrieval and feature generation.

Common pitfalls

• Undeclared grain, and joins that silently multiply rows.
• Business logic in dashboards instead of in the model or semantic layer.
• Overwriting history by accident — treating every attribute as SCD Type 1.
• Data Vault without automation, producing hundreds of hand-written tables nobody can query.
• Wide tables as the system of record, so every correction means rebuilding everything.
• Mixing grains in one table (order-level and line-level amounts side by side).
• No tests on keys and relationships, so model bugs are found by executives.

Key takeaways

• Modeling is about meaning: grain, keys and history — and it matters more, not less, with AI consumers.
• Kimball dimensional models remain the best presentation layer: facts at a declared grain, conformed dimensions, SCD Type 2 where history matters.
• Data Vault 2.x is a powerful integration layer for many changing sources and strict audit — with automation, and with marts on top.
• One Big Table is a great serving format derived from the dimensional core, not a replacement for it.
• Map techniques to layers: raw bronze → integrated silver → dimensional gold → wide tables + semantic layer.
• Enforce models with tests and contracts, and define metrics once in a semantic layer — now standardizing through OSI.

Up next

Part 6 — Batch, Streaming & Real-Time Architectures. When does real-time actually pay off? We’ll cover CDC, the streaming lakehouse, Lambda and Kappa in practice, and the latency–cost–complexity triangle. Follow along on the series page, via RSS or by email.

References and further reading

• Ralph Kimball & Margy Ross — The Data Warehouse Toolkit, 3rd edition (Wiley, 2013), and the Kimball Group dimensional modeling techniques.
• W. H. Inmon — Building the Data Warehouse (Wiley; first edition 1992).
• Dan Linstedt & Michael Olschimke — Building a Scalable Data Warehouse with Data Vault 2.0 (Morgan Kaufmann, 2015); Scalefree — What’s New in Data Vault 2.1 (November 2025).
• One Big Table vs Star Schema: what columnar engines changed and what they didn’t.
• Databricks — Lakehouse Data Modeling: Myths, Truths, and Best Practices.
• Snowflake — Open Semantic Interchange (OSI) specification finalized (January 2026) and dbt Labs — What the OSI spec means for metrics, semantics and AI.
• dbt model contracts and data tests documentation.