Data Modeling

Bill Inmon vs. Ralph Kimball—What They Are, When to Use Them, and How to Build Well

You’ll hear “Inmon” and “Kimball” tossed around like rival teams. In reality, both are time-tested ways to organize enterprise data for analytics. Inmon emphasizes an Enterprise Data Warehouse (EDW) in 3rd Normal Form (3NF) as the single integrated source of truth, with downstream marts as needed. Kimball emphasizes star schemas (dimensional models) as the bus architecture for analytic consumption, conformed across subject areas.

• What each methodology is really about
• How to decide which to use (or combine) for your situation
• Step-by-step recipes to build models using each method
• Checklists, patterns, and pitfalls—so your models stand up to real workloads

---

1) Core ideas in one page

• Inmon (“Corporate Information Factory”)
  • Goal: A subject-oriented, integrated, time-variant, nonvolatile EDW in 3NF.
  • Flow: Source → Staging → EDW (3NF) → Data Marts (optional, 3NF or dimensional) → BI.
  • Strengths: Enterprise integration/lineage, change resilience, semantic precision.
  • Trade-offs: Heavier upfront modeling; performance hinges on downstream marts or semantic layers.
• Kimball (Dimensional Bus)
  • Goal: Dimensional models (star schemas) aligned to business processes, with conformed dimensions to integrate across marts.
  • Flow: Source → Staging → Dimensional marts (stars) → BI; the set of stars is the warehouse.
  • Strengths: Speed to value; easy for BI tools and analysts; predictable performance.
  • Trade-offs: Cross-domain integration must be guarded by conformance discipline; upstream semantics can be looser.

Reality check: Many successful organizations use a hybrid—an integrated core (Inmon-like) feeding dimensional marts (Kimball-like), especially in regulated or complex enterprises.

---

2) When to use which (decision guide)

Use this rule of thumb matrix:

Context	Inmon-leaning	Kimball-leaning
Regulatory/Compliance (traceability, audits, data lineage)	Prefer 3NF EDW; clear provenance and integration	✔ Use, but ensure strong conformance + lineage capture
Enterprise Integration (many systems; master data)	Central integration layer simplifies semantics	✔ Works if you enforce conformed dimensions rigorously
Speed to Insights (pilot analytics, MVP, one domain)	➖ Can be slower to first value	Fast—model process + dimensions and deliver
Query Performance & Self-Service BI	➖ Need marts/semantic layer	Star schemas are BI-friendly by design
Frequent Source Volatility	3NF absorbs change with fewer ripple effects	➖ More refactoring across stars and conformance
Team Skills (DBAs, data modelers vs analytics engineers)	Suits relational modeling strength	Suits BI/analytics engineering skill sets
Modern Lakehouse (Parquet/Delta + ELT)	Use 3NF core as curated “silver/gold”	Stars on top for “gold” reporting zones

A pragmatic hybrid:

• Stage/land raw data.
• Integrate in 3NF for cross-domain truth (Inmon).
• Publish dimensional stars for BI (Kimball).
• Use a semantic layer (dbt metrics, BI semantic graph, or a governed view layer) to unify measures and calculations.

---

3) The Inmon methodology—step by step

3.1 Concepts

• Subject-oriented: group data by broad domains (Customer, Product, Account, Policy, Claim…).
• Integrated: resolve keys, codes, and definitions into a common enterprise meaning.
• Time-variant: preserve history—no destructive updates; use effective/expiration dates.
• Nonvolatile: EDW is read-mostly; changes are new rows or type-2-style updates.

3.2 Process (recipe)

Step A — Strategy & scope

1. Define business drivers (compliance, cross-sell, finance close).
2. Choose first subject areas with high cross-domain value (e.g., Customer + Product + Account).
3. Set non-functional requirements (SLA, data latency, PII controls).

Step B — Inventory & profiling

1. Catalogue sources, keys, volumes, change patterns.
2. Profile data (nulls, cardinality, anomalies, code sets).
3. Document business definitions (what is a “customer”?).

Step C — Enterprise conceptual model

1. Identify core entities and relationships (ERD): Customer, Organization, Account, Product, Transaction, Location, Agreement, etc.
2. Name attributes in business language; avoid source jargon.

Step D — Logical 3NF design

1. Normalize to 3NF: remove repeating groups; separate entities; use surrogate keys where integration needs them.
2. Add history attributes (effective_from, effective_to, is_current).
3. Model reference data (statuses, types); avoid embedding free text where enums are intended.
4. Define keys: natural + surrogate; indexes for lookups and history.

Step E — Integration rules

1. Key mapping/XREF tables to relate source IDs → EDW surrogate keys.
2. Business rules for harmonization: e.g., currency normalization, unit standards, code translations.
3. Data quality rules: completeness, validity, uniqueness (fail, fix, or flag policies).

Step F — Physical design

1. Choose distribution/partitioning (by date, business key).
2. Cluster/index strategy for 3NF joins; consider materialized views for common traversals.
3. Security: row/column-level rules for PII/PCI.

Step G — Load (ELT)

1. Staging (raw, append-only) → Integration transforms (keys, codes, de-dupe) → EDW tables.
2. SCD-like history management at 3NF level (often via hash/equality change detection).
3. Audit columns (source_system, load_ts, batch_id, created_by).

Step H — Serve

1. Create data marts/views over EDW for common queries.
2. Provide semantic models (BI layer, dbt metrics, or curated views).
3. Publish data contracts for downstream teams.

3.3 Example (Customer/Account)

• customer(customer_sk, legal_name, birth_date,…, effective_from, effective_to, is_current)
• customer_identifier_xref(source_system, source_customer_id, customer_sk, active_flag)
• account(account_sk, product_sk, open_date, …)
• customer_account_role(customer_sk, account_sk, role_type, effective_from, effective_to)

This structure captures roles/time cleanly and feeds any number of marts.

3.4 Inmon pitfalls & safeguards

• Pitfall: Analysis paralysis (months of modeling).
  Guard: Time-box subject areas; demo early with thin vertical slices.
• Pitfall: Slow BI due to 3NF joins.
  Guard: Publish marts or views tailored to consumption.
• Pitfall: One “true” definition blocks delivery.
  Guard: Permit alternative views with explicit lineage; iterate definitions under governance.

---

4) The Kimball methodology—step by step

4.1 Concepts

• Business process orientation: model around events (orders, payments, trades, claims).
• Grain first: define the atomic level of the fact (e.g., one row per transaction line).
• Dimensions for context: who/what/when/where/why; rich, descriptive, often Type 2 SCD.
• Conformed dimensions: shared across marts (Customer, Product, Calendar, Geography) to integrate the enterprise.
• Bus matrix: a blueprint mapping business processes to the conformed dimensions they use.

4.2 Process (recipe)

Step 1 — Identify business processes

• Interview by process (“Tell me how you book a loan”, “How does a claim flow?”).
• Prioritize processes with clear KPIs.

Step 2 — Define grain

• One fact row per transaction (e.g., policy premium line), snapshot (e.g., monthly account balance), or accumulating (e.g., loan lifecycle milestones).

Step 3 — Identify dimensions

• Determine who/what/when/where/how the process references. Typical: Customer, Product, Channel, Organization, Calendar, Geography, Currency, Advisor, Policy.

Step 4 — Design dimensions

• Surrogate keys; business keys retained.
• SCD Type 2 for attributes that change and must be historized (address, segment).
• Type 1 for corrections (name standardization).
• Add junk dimensions for low-cardinality flags (yes/no fields).
• Include audit attributes (effective_from/to, is_current).

Step 5 — Design fact tables

• Foreign keys to all relevant dimensions.
• Numeric, additive/semi/non-additive measures (premium, balance, rate).
• Degenerate dimensions (e.g., invoice number) kept in fact when they have no additional attributes.

Step 6 — Build the bus matrix

• Rows = business processes (facts).
• Columns = conformed dimensions.
• Check shared dimensions stay consistent across marts (data types, values, hierarchies).

Step 7 — Physical design

• Partition by date/grain; indexes on foreign keys; summary aggregates where useful.
• Consider bridge tables for many-to-many (e.g., customer ↔ household, product bundles).

Step 8 — Load (ELT)

• Dimension pipeline first (SCD handling), then facts referencing dimension surrogate keys.
• Manage late-arriving dimensions/facts (early-arriving facts strategy using inferred members).

Step 9 — Semantic layer & BI

• Publish star schemas to BI; define metrics centrally (revenue, churn rate) to avoid measure drift.

4.3 Example (Policy Premium fact)

• dim_customer(customer_key, …, effective_from, effective_to, current_flag)
• dim_product(product_key, product_family, plan_code…)
• dim_calendar(date_key, day, week, month, quarter, year, fiscal flags)
• fact_premium(date_key, customer_key, product_key, policy_number (degenerate), premium_amount, currency_key, channel_key)

4.4 Kimball pitfalls & safeguards

• Pitfall: “Mart sprawl” with conflicting dimensions.
  Guard: Strict conformance governance, shared dimension pipelines, a published bus matrix.
• Pitfall: Wrong grain leads to double counting or gaps.
  Guard: Write the grain statement and validate with examples.
• Pitfall: Overusing Type 2 (exploding rows).
  Guard: Be selective; use Type 1 for non-analytic changes; consider mini-dimensions for rapidly changing attributes.

---

5) Hybrid strategy (what most modern teams do)

A sensible, modern pattern for cloud/lakehouse:

1. Raw/bronze: Land source data as-is (immutable).
2. Integrated/silver (Inmon-ish): Curate 3NF-like subject-area tables with harmonized keys and history (the integration backbone).
3. Dimensional/gold (Kimball): Star schemas built on the integrated layer for BI and ML features.
4. Semantic layer: Consistent metrics (e.g., dbt metrics layer, BI semantic model).
5. MDM & reference data: Shared services feeding both layers to keep identities and code sets clean.

Why it works: you get enterprise integration + lineage (Inmon) and analyst-friendly stars with high performance (Kimball).

---

6) Modeling patterns you’ll reuse

6.1 Slowly Changing Dimensions (SCD)

• Type 1: Overwrite; use for corrections (typos).
• Type 2: Add new row with new surrogate key and effective dates; use for history of analytics-relevant attributes.
• Type 3: Previous value stored in additional columns (rare; for limited “before/after” analysis).

6.2 Many-to-many

• Bridge table between dimension and fact (e.g., customer ↔ household).
• For weighted relationships (e.g., multi-beneficiary allocations), include allocation percent in bridge.

6.3 Snapshots vs transactions

• Snapshot facts (balance by month) simplify point-in-time reporting; transaction facts support detailed event analysis. Many warehouses keep both.

6.4 Role-playing dimensions

• Same physical dimension used in multiple roles via views/aliases (e.g., dim_date becomes order_date, ship_date, close_date).

---

7) Quality, governance, and performance—regardless of method

• Data contracts: Each pipeline publishes schema, SLAs, and expected checks (primary keys, FKs, null constraints).
• Tests: Uniqueness, not-nulls, referential integrity, valid value sets; automate in CI (e.g., dbt tests).
• Lineage: Capture column-level lineage so auditors (and teammates) can trace metrics back to source.
• Cost/perf: Partition by time; vacuums/compaction jobs for file formats; aggregates or materialized views for common queries.
• Security: Row/column masking for PII; purpose-based access; tokenization where necessary.
• Documentation: Business definitions for each dimension attribute and fact measure—don’t rely on column names.

---

8) Worked mini-case: Claims analytics

Goal: build trustworthy claims analytics (severity, frequency, cycle times).

Option A (Inmon-first)

• Integrate claim, policy, party, provider in 3NF, resolving IDs and code sets.
• Build dim_claimant, dim_provider, dim_policy, dim_date, fact_claim_event (one row per adjudication event) off the integrated core.
• Benefits: clean enterprise semantics; easier cross-functional use.
• Trade-off: first insights arrive after the integration layer stands up.

Option B (Kimball-first)

• Interview adjusters; define grain (one row per claim transaction) and dimensions (claimant, provider, product, cause, date).
• Build stars directly from source with minimal harmonization; conform dim_date, dim_product to existing marts.
• Benefits: speed; quick dashboards.
• Trade-off: more rework later to unify with other domains if early choices drift.

Hybrid: create a thin integrated layer only for identities and codes, then star up. This is the most common and pragmatic route.

---