Data Modelling — Dimensional, Data Vault, OBT

Why this session exists

A warehouse needs an agreed meaning for one row before it needs a fashionable schema. This lesson designs a sales star, distinguishes Kimball, Inmon, Data Vault and one-big-table (OBT) approaches, implements historical customer attribution, and answers every original modeling exercise. A correct sum can still answer the wrong business question if grain or historical context is ambiguous.

You will declare fact grain, identify dimensions and surrogate keys, choose SCD 0/1/2/3/6, explain hubs/links/satellites, and weigh OBT's read simplicity against refresh cost. The examples use integer cents so numerical representation does not obscure modeling errors.

Prerequisites

SQL joins explains fan-out; query planning explains scan/join economics. S040 is CTEs, not joins. A cloud engine can optimize a physical plan, but it cannot infer the intended business meaning of duplicated shipping charges.

Three ways to organize receipts—and a fourth architecture

Imagine receipts pointing to customer, product, store and date folders. A dimensional star puts comparable measurements in the fact and descriptive attributes in dimensions. “Blue widgets sold Tuesday in Hyderabad” becomes filtering dimensions and summing the matching facts.

An Inmon-style normalized enterprise warehouse integrates enterprise data before deriving marts. Data Vault is distinct: hubs identify business keys, links record relationships, and satellites carry descriptive history with load metadata and record source. A raw vault can preserve source changes while a business-vault/mart layer applies analytical rules. Neither design alone guarantees a perfect audit trail, and Inmon is not another name for Data Vault.

An OBT materializes selected context alongside each fact at a declared grain. The analyst may avoid repeated joins, but updates to historical/current attributes can require rebuilding many rows. A columnar engine can prune unused columns; it does not make every 300-column query cost exactly the same as a five-column table. Referenced filters, nested fields, compression, clustering, partition pruning and refresh/storage costs still matter.

Vocabulary and the grain derivation

• Grain: what one row represents, such as one sold order line identified by (order_id,line_no), not merely “one product.” A product can occur on multiple lines.
• Dimension: descriptive context for filtering/grouping; not every dimension is small or immutable.
• Fact: a measurement/event at the grain. Facts may be transactional, periodic snapshots, accumulating snapshots or factless events; they are not all immutable numeric streams.
• Surrogate key: warehouse identity for a dimension version, distinct from the business identity. Integer keys are common, not a universal storage requirement.

Derive the double-counting problem: an order has lines of 15 and 6 and one shipping charge of 4. If shipping=4 is copied onto both lines, summing it returns 8; the line amounts still correctly sum to 21. Thus “every SUM double-counts” is false—the error depends on the measure's grain. Keep shipping at order grain, allocate it with a declared rule, or expose a metric that aggregates each order once.

A dimension join must select at most one intended version per fact. Two SCD rows for customer C001 joined by business ID alone turn two sales into four rows and double their revenue. An engine can enforce uniqueness and constraints, but a plausible numeric result is not semantic validation. State the grain before choosing columns [Kimball grain].

Historical context and modeling schools

The longstanding Kimball/Inmon discussion contrasts process-oriented dimensional marts with conformed dimensions against integrated normalized enterprise warehousing. Data Vault separates business-key integration from changing descriptions; OBT moves selected joins into a materialization. These can coexist as layers rather than compete as mutually exclusive religions. The original timeline's sweeping release/adoption dates are not needed to claim one superseded another. Modern columnar storage changed some read economics, not the need for consistent definitions, time semantics and lineage.

Connecting sales measurements to business context

A dimension row may describe many facts; a required fact foreign key identifies one dimension row. The original one-to-one crow's-foot diagram was wrong. Dates can have fiscal year, quarter, weekend and holiday attributes; customers have business identity, descriptive fields and version intervals; products have SKU/category/color; stores have location/region. The miniature executable star below includes all four dimensions and preserves the March/August Alice example.

Comparing the schools

A snowflaked dimension can reduce redundancy and express governed hierarchies, but adds joins and makes analyst navigation more complex. A flat dimension can simplify slicing without flattening unrelated one-to-many relationships into the fact. Measure costs with the intended query mix. A wide feature table also needs point-in-time availability rules; “feature store = OBT” omits those semantics.

Slowly changing dimensions and surrogate identity

Type 0 retains the original value, such as signup date. Type 1 overwrites an attribute: useful for corrections or an explicitly current-state reporting contract. It intentionally loses that attribute's old value unless another history source preserves it.

Type 2 inserts a new dimension row for a tracked change, with a new surrogate key and effective interval. Store half-open intervals [valid_from,valid_to) and ensure they do not overlap for one business identity. A fact uses the version valid at its business event time, whether resolved during loading or through an unambiguous temporal join. “Captured at event time” need not mean the source application already knew the warehouse key. Late arrivals and corrected effective dates may require restating facts under an explicit policy.

Type 3 keeps selected alternate/prior attributes as columns and supports a bounded number of alternative views, not full change history. Type 6, in the cited Kimball definition, adds Type-1 current-value attributes to Type-2 versioned rows, allowing historical and current grouping with the same fact join [Kimball SCD6]. A separate previous-value column is not required by that definition. Updating current_city on all versions changes the current-view lens; it does not destroy city at the version's event time.

Surrogates distinguish two versions of C001. They do not automatically repair an incorrectly resolved business identity, prevent overlap or make every join faster. Date dimensions commonly use a meaningful date key. A stable natural key can be suitable elsewhere; “always integer surrogate, no exceptions” is too broad.

Additive, semi-additive and non-additive measures

Revenue in a common currency and comparable line quantities can be additive across chosen dimensions. Account balance is often additive across accounts but not across time: Monday balance plus Tuesday balance is not an ending balance. Unit price is normally non-additive, not semi-additive; compute a quantity-weighted realized price if that is the requested metric. Margin percentage is recomputed from sum(margin_amount)/sum(revenue) with a zero-denominator policy, not summed or averaged without weights. Distinct counts need set/sketch union semantics, not addition across overlapping populations [Kimball additive].

A conformed dimension standardizes meaning across facts; it does not make a raw fact-to-fact join safe. Aggregate sales and returns separately to compatible dimension grain and drill across the results. A conformed customer alone does not settle whether “booked” means created, confirmed or paid; a metric definition and business filters must also be governed.

Hands-on: full star and historical attribution

This complete Python 3/SQLite lab creates a 2024–2026 calendar, constrained dimensions, a line-grain fact, an SCD2 transition, historical joins and the Type-6 extension. It uses an in-memory database, no schema drops or external services. Timestamps here are ISO dates with day-grain changes; production timestamps require an explicit timezone and greater precision.

import sqlite3
from datetime import date, timedelta
 
c = sqlite3.connect(":memory:")
c.execute("PRAGMA foreign_keys=ON")
c.executescript("""
CREATE TABLE dim_date(date_key INTEGER PRIMARY KEY, full_date TEXT UNIQUE NOT NULL,
 year INT, quarter INT, month INT, day INT, day_of_week TEXT, is_weekend INT);
CREATE TABLE dim_customer(customer_key INTEGER PRIMARY KEY, customer_id TEXT NOT NULL,
 name TEXT NOT NULL, city TEXT NOT NULL, segment TEXT NOT NULL,
 effective_from TEXT NOT NULL, effective_to TEXT NOT NULL, is_current INT NOT NULL,
 CHECK(effective_from<effective_to), CHECK(is_current IN (0,1)),
 UNIQUE(customer_id,effective_from));
CREATE UNIQUE INDEX one_current ON dim_customer(customer_id) WHERE is_current=1;
CREATE TABLE dim_product(product_key INTEGER PRIMARY KEY,sku TEXT UNIQUE NOT NULL,
 name TEXT NOT NULL,category TEXT NOT NULL,color TEXT);
CREATE TABLE dim_store(store_key INTEGER PRIMARY KEY,store_code TEXT UNIQUE,city TEXT,region TEXT);
CREATE TABLE fact_sales(order_id TEXT, line_no INT, sale_date_key INT NOT NULL REFERENCES dim_date,
 customer_key INT NOT NULL REFERENCES dim_customer, product_key INT NOT NULL REFERENCES dim_product,
 store_key INT NOT NULL REFERENCES dim_store, qty INT NOT NULL CHECK(qty>0),
 amount_cents INT NOT NULL, discount_cents INT NOT NULL DEFAULT 0,
 PRIMARY KEY(order_id,line_no));
INSERT INTO dim_customer VALUES(101,'C001','Alice','Hyderabad','gold','2024-01-01','9999-12-31',1);
INSERT INTO dim_product VALUES(1,'SKU-1','LED Bulb','electronics','warm'),(2,'SKU-2','USB Cable','electronics','black');
INSERT INTO dim_store VALUES(1,'S1','Hyderabad','South');
""")
d = date(2024,1,1)
while d <= date(2026,12,31):
    c.execute("INSERT INTO dim_date VALUES(?,?,?,?,?,?,?,?)",(int(d.strftime("%Y%m%d")),d.isoformat(),d.year,(d.month-1)//3+1,d.month,d.day,d.strftime("%A"),int(d.weekday()>=5)))
    d += timedelta(days=1)
c.commit()
with c:
    c.execute("UPDATE dim_customer SET effective_to='2024-06-01',is_current=0 WHERE customer_key=101")
    c.execute("INSERT INTO dim_customer VALUES(102,'C001','Alice','Bangalore','gold','2024-06-01','9999-12-31',1)")
 
def resolve(customer_id, event_day):
    matches = c.execute("SELECT customer_key FROM dim_customer WHERE customer_id=? AND effective_from<=? AND ?<effective_to",(customer_id,event_day,event_day)).fetchall()
    if len(matches)!=1: raise ValueError("missing or overlapping historical version")
    return matches[0][0]
 
assert resolve('C001','2024-05-31') == 101
assert resolve('C001','2024-06-01') == 102
# Inclusive BETWEEN would match both versions at the boundary.
assert c.execute("SELECT count(*) FROM dim_customer WHERE '2024-06-01' BETWEEN effective_from AND effective_to").fetchone()[0] == 2
for oid,day,product,qty,amount in [('O1','2024-03-15',1,3,1500),('O2','2024-08-10',2,2,600)]:
    c.execute("INSERT INTO fact_sales VALUES(?,?,?,?,?,?,?,?,?)",(oid,1,int(day.replace('-','')),resolve('C001',day),product,1,qty,amount,0))
c.commit()
result = c.execute("""SELECT d.full_date,u.city,p.name,f.qty,f.amount_cents
 FROM fact_sales f JOIN dim_date d ON d.date_key=f.sale_date_key
 JOIN dim_customer u ON u.customer_key=f.customer_key
 JOIN dim_product p ON p.product_key=f.product_key ORDER BY d.full_date""").fetchall()
assert result == [('2024-03-15','Hyderabad','LED Bulb',3,1500),('2024-08-10','Bangalore','USB Cable',2,600)]
# Type 6 current lens on all historical rows.
c.execute("ALTER TABLE dim_customer ADD COLUMN current_city TEXT")
c.execute("UPDATE dim_customer AS d SET current_city=(SELECT city FROM dim_customer v WHERE v.customer_id=d.customer_id AND v.is_current=1)")
historical = c.execute("SELECT u.city,sum(f.amount_cents) FROM fact_sales f JOIN dim_customer u ON u.customer_key=f.customer_key GROUP BY u.city ORDER BY u.city").fetchall()
current = c.execute("SELECT u.current_city,sum(f.amount_cents) FROM fact_sales f JOIN dim_customer u ON u.customer_key=f.customer_key GROUP BY u.current_city").fetchall()
assert historical == [('Bangalore',600),('Hyderabad',1500)]
assert current == [('Bangalore',2100)]
fanout = c.execute("SELECT sum(f.amount_cents) FROM fact_sales f JOIN dim_customer old ON old.customer_key=f.customer_key JOIN dim_customer v ON v.customer_id=old.customer_id").fetchone()[0]
assert fanout == 4200
# Constraint negative controls: duplicate current version and duplicate fact grain.
for sql in ["INSERT INTO dim_customer VALUES(103,'C001','Alice','Delhi','gold','2025-01-01','9999-12-31',1,'Delhi')", "INSERT INTO fact_sales SELECT * FROM fact_sales WHERE order_id='O1'"]:
    try: c.execute(sql)
    except sqlite3.IntegrityError: pass
    else: raise AssertionError("constraint failed to reject")
assert c.execute("SELECT count(*) FROM dim_date").fetchone()[0] == 1096
print({"sales":result,"historical":historical,"current":current,"wrong_natural_join":fanout,"calendar_days":1096})
c.close()

What each block does and the missing production constraints

The calendar centralizes date labels and includes leap-day 2024 (1,096 days total). Real fiscal/holiday calendars need the business's locale and maintenance rules. The surrogate separates business ID C001 from versions 101 and 102. The atomic transition closes Hyderabad before adding Bangalore; the partial unique index prevents two current rows, but does not prevent all historical overlaps. The resolver rejects zero/multiple matches; in PostgreSQL a temporal exclusion constraint or a serialized load with explicit overlap validation can enforce the broader invariant.

For the non-null fixture inputs, facts store a unique order/line key and version key, so retrying a source event must use a defined replacement/conflict policy rather than invent another sale ID. The exact output preserves March 15 Hyderabad/15.00 and August 10 Bangalore/6.00. Looking up is_current for both would incorrectly move March to Bangalore. At June 1, inclusive BETWEEN selects two versions; half-open comparison selects Bangalore only.

Type-6 exercise answer: update current_city on every historical C001 row in the same controlled dimension-maintenance transaction. Historical grouping yields Hyderabad 1,500 cents and Bangalore 600; current grouping yields Bangalore 2,100. The fact keys do not change. If another current change occurs, repeat that maintenance while retaining each version's historical city.

Retry and late-change exercise: serialize changes for one business key, compare tracked attributes, reject conflicting reuse of an effective timestamp, and perform close/insert atomically. Replaying an identical change must be a no-op. An out-of-order change may require splitting an interval and correcting fact references; do not simply close today's row at an earlier time. A scheduled nightly classifier should create a version only when the chosen business segment changes, with model/version provenance recorded separately when needed. Floating-point feature drift alone is not a reason to version an unchanged category.

Enforce non-null grain and test historical overlap

The miniature fact_sales declares a composite primary key but does not explicitly mark order_id and line_no NOT NULL. In an ordinary SQLite rowid table, that composite key can contain NULL, and repeated NULL-bearing keys can bypass the intended uniqueness check. The displayed fixture's two non-null orders are valid; it is not a production-ready ingestion constraint. PostgreSQL primary keys imply NOT NULL, so silently transferring that assumption to SQLite would hide a real defect. Declare both components NOT NULL in an ingestible schema [SQLite CREATE TABLE].

import sqlite3
c = sqlite3.connect(':memory:')
c.execute('CREATE TABLE loose(order_id TEXT,line_no INT,PRIMARY KEY(order_id,line_no))')
c.executemany('INSERT INTO loose VALUES(?,?)', [(None,1),(None,1)])
assert c.execute('SELECT count(*) FROM loose').fetchone()[0] == 2
c.execute('CREATE TABLE strict_lines(order_id TEXT NOT NULL,line_no INT NOT NULL,PRIMARY KEY(order_id,line_no))')
c.execute("INSERT INTO strict_lines VALUES('O1',1)")
for pair in [(None,1),('O1',None),('O1',1)]:
    try:
        c.execute('INSERT INTO strict_lines VALUES(?,?)', pair)
    except sqlite3.IntegrityError:
        pass
    else:
        raise AssertionError('invalid or duplicate grain accepted')
c.execute('CREATE TABLE versions(customer TEXT,k INT,lo TEXT,hi TEXT)')
c.executemany('INSERT INTO versions VALUES(?,?,?,?)', [
    ('C001',101,'2024-01-01','2024-06-01'),
    ('C001',102,'2024-06-01','9999-12-31'),
    ('C001',103,'2024-05-01','2024-05-15')])
overlaps = c.execute("""SELECT a.k,b.k FROM versions a JOIN versions b
 ON a.customer=b.customer AND a.k<b.k
 AND a.lo<b.hi AND b.lo<a.hi ORDER BY a.k,b.k""").fetchall()
assert overlaps == [(101,103)]  # adjacency at June 1 is not overlap
assert c.execute("SELECT count(*) FROM versions WHERE customer='C001' AND lo<='2024-05-10' AND '2024-05-10'<hi").fetchone()[0] == 2
assert c.execute("SELECT count(*) FROM versions WHERE customer='C001' AND lo<='2023-12-31' AND '2023-12-31'<hi").fetchone()[0] == 0
print({'overlaps':overlaps,'nullable_grain_negative_control':2})
c.close()

For half-open intervals, overlap requires both a.lo < b.hi and b.lo < a.hi. Equality at a shared endpoint is adjacency, not overlap. The example deliberately lacks production constraints to expose one overlapping historical pair, a two-match lookup and a zero-match lookup. A one-current-row index does not detect that old-old overlap. Validate the interval set and fact-key resolution atomically under a serialized per-business-key load; a read-only validation query alone cannot prevent a concurrent writer from racing it. Missing effective-time coverage needs an explicit unknown-member/quarantine policy, not silent attribution to today's row.

Production reality and design exercises

Metric consistency: two dashboards can count booking creation versus confirmation, or UTC day versus local day, and disagree despite joining the same dimension. Retain one versioned metric definition, measure grain, cancellation rule and timezone; validate representative orders against expected totals. A semantic layer can encode these rules, but an unsupported “200 dashboards” Airbnb figure is not evidence for this design. The business problem remains substantive without inventing an incident outcome.

Auditability: for “which address received the statement on March 14,” retain the address actually used on the statement event or resolve an audited effective-time version. SCD2 or satellites can support this, but neither guarantees perfect compliance; load time versus business effective time, source corrections, access control and legal retention/deletion rules remain. Type 1 is not universally illegal. An append-only raw archive may still need governed erasure of personal data.

OBT materialization: suppose each daily report repeats a fact-to-dimension join. A wide mart can pay that join once, but must avoid one-to-many fan-out and specify whether attributes are current or historical. Benchmark refresh plus read cost against the star on the same query/result set; there is no universal 40% savings. BigQuery's own guidance notes that already optimized star schemas need not improve merely by further denormalization [BQ denormalization].

Vault worked design: hub_customer holds a durable business key plus source/load metadata; hub_order holds an order key; link_customer_order relates them; sat_customer_address records successive descriptive address states with load timestamps and record source. Business-effective time and correction precedence need explicit fields/rules. Build a dimensional mart from these for reporting rather than making each analyst reconstruct source history ad hoc. A link identifies a relationship, not an amount at an arbitrary grain.

Where this connects next

Batch and streaming both feed the same declared grain; orchestration orders dimension resolution and fact publication; dbt can implement staged transformations without mandating Kimball. Quality tests check fact-grain uniqueness, foreign-key existence and SCD interval overlap—not business-key uniqueness across a Type-2 dimension. Feature engineering adds point-in-time availability to historical context. These constraints are why model design precedes a BI or ML platform diagram.

Recall and worked answers

1. Grain and mixing bug? One sold order line versus one order header; copying a 4-unit shipping fee onto two lines makes its sum 8, while valid line revenue still sums to 21.
2. Surrogate versus business ID? A surrogate identifies the historical dimension version; C001 identifies the customer. A surrogate is useful for SCD2, not a magic guarantee of speed or identity correctness.
3. SCD2 in one sentence? For a tracked effective-time change, atomically close the old nonoverlapping interval and insert a new version/key, avoiding duplicate changes and resolving facts at their business time—not wall-clock now.
4. Vault over Kimball? Source integration/change traceability may justify hubs/links/satellites, typically with dimensional marts for users. Regulation alone does not dictate a schema or guarantee audit completeness.
5. What makes OBT viable? Column pruning and compression can avoid reading every stored column; query and refresh cost still depend on referenced fields, layout and workload.
6. Can qty, unit_price and margin_percent all be summed? Only comparable quantities here. Unit price is non-additive; margin ratio uses aggregate numerator/denominator. A balance snapshot is the classic semi-additive example.
7. Hotel star? fact_room_night has grain one confirmed booked room-night, identity (booking_id,room_id,stay_date), booking-date/stay-date roles of dim_date, plus dim_guest, dim_property, dim_room_type. Measures are room_nights=1, recognized rate/tax under a currency policy. Multi-room bookings need the room identity; cancellations need a lifecycle policy. Guest tier may be SCD2; property location corrections need explicit historical/restatement semantics.
8. Conformed dimension? Shared meaning/compatible attributes across processes, enabling drill-across after separately aggregating facts to compatible grain—not joining every sale to every return for a customer.
9. Nightly ML segment trap? Do not insert a new row just because a model ran or features drifted. Compare the tracked segment and effective time; record model provenance without accidentally multiplying identical business versions.

Additional failure answer: an 11:59 purchase arriving at 12:05 uses the pre-noon country under event-time attribution. If the question is what was knowable at processing time, store availability/load time too. A late correction can legitimately restate reports under a documented version policy.

Explain out loud: declare grain, demonstrate the half-open SCD boundary, then defend or reject an OBT using total read/refresh costs and semantics rather than a slogan.

Sources and next step

Checked 2026-10-07: Kimball grain, Type 2, Type 6, measure additivity, AutomateDV modeling, BQ denormalization. Mutable-source snapshots and exact passages are retained in the evidence. SQLite CREATE TABLE governs the nullable-key exception. Kimball and BigQuery pages are mutable documentation snapshots; AutomateDV is the captured repository source, not a pinned released engine/API contract.

Next: Batch versus streaming. The SQL lab is executed SQLite evidence, not a warehouse benchmark or a claim of PostgreSQL temporal-constraint execution.

Explain-out-loud answer guide

1. Grain: one sold order line (order_id,line_no) is distinct from an order header; copying header shipping onto every line duplicates that measure. Enforce non-null identity as well as uniqueness.
2. SCD Type 2: a version key plus effective_from, effective_to and is_current distinguish business identity from historical versions. In one controlled transaction close the old interval and insert a changed version, using business effective time; reject overlap, duplicate replay and missing resolution.
3. When OBT can beat star: materializing repeated joins can save read work in a columnar warehouse that prunes unused columns, but include refresh/storage cost and historical/current semantics in the comparison. An already optimized star need not improve.

Optional archived resources

These links retain the original pre-read route; historical titles are labels, not verified endorsements. Video attribution, duration, playback and external reachability were not revalidated. Use the version-qualified primary references above for correctness; mutable /current or /latest pages may differ.

• Data Warehouse Concepts — Star Schema Explained — archived video/discovery link.
• Inmon vs Kimball Data Warehouse Architectures #datawarehouse — archived video/discovery link.
• Data Vault 2.0 — Hubs, Links, Satellites — archived video/discovery link.
• Kimball Group — Dimensional Modeling Techniques — archived reading link.
• Is Kimball still relevant? — archived reading link.
• Star Schema vs One Big Table — archived reading link.

Series route and study format

Hub: The 6-Month Learning Plan. Previous: S044; next: S046. These are navigation pointers, not certification of every linked body or permission to publish it.

The original 130-session series suggests a 90-minute study cycle: intuition → visual explanation → hands-on practice → production failure analysis → recall. Treat that as a pacing suggestion, not a tested completion time. Work the examples and explain the failure cases before moving on; reference-only service exercises need separate execution authority.