Ralph Kimball's approach builds a data warehouse incrementally, one business process at a time, as a set of star schemas joined by shared dimensions. This page summarises its principles and components and adds an architecture diagram, a bus matrix, example DDL and a comparison with the Inmon and Data Vault approaches.
Kimball Data Architecture is a bottom-up data warehouse design approach focused on delivering business-friendly analytics. It organizes data using dimensional modeling, making it optimized for reporting, dashboards, and BI tools.
The core of Kimball architecture is the dimensional warehouse, built using star schemas.
In Kimball architecture, data marts are not silos. They are logical subsets of the dimensional warehouse.
Kimball Data Architecture organizes enterprise data into fact and dimension tables to deliver fast, consistent, and business-friendly analytics.
Data flows from operational sources through an ETL or ELT layer into dimensional data marts, and from there to business intelligence tools. Unlike a top-down design, there is no separate normalised enterprise warehouse layer that users never query: the dimensional models are the warehouse. The staging area is a back-room work space for cleaning and conforming and is not exposed to users. The key to integration is that marts built at different times reuse the same conformed dimension tables and agree on their keys and attribute definitions.
The enterprise bus matrix is Kimball's planning tool. Rows are business processes, each of which becomes one or more fact tables; columns are dimensions. A mark means the process uses that dimension. Reading down a column shows which dimensions must be conformed across teams (date and product here are used everywhere), and the matrix doubles as a delivery roadmap: a team can build the retail sales mart first, then inventory, confident that both will drill across on shared dimensions.
CREATE TABLE dim_date (
date_key INT PRIMARY KEY, -- e.g. 20261001
full_date DATE NOT NULL,
month_name VARCHAR(10),
quarter SMALLINT,
year SMALLINT,
is_holiday BOOLEAN
);
CREATE TABLE dim_product (
product_key INT PRIMARY KEY, -- surrogate key
product_id VARCHAR(20) NOT NULL, -- natural key from source
product_name VARCHAR(200),
category VARCHAR(100),
brand VARCHAR(100)
);
CREATE TABLE fact_inventory_snapshot ( -- periodic snapshot fact
date_key INT REFERENCES dim_date(date_key),
product_key INT REFERENCES dim_product(product_key),
store_key INT,
quantity_on_hand INT, -- semi-additive: do not sum across dates
PRIMARY KEY (date_key, product_key, store_key)
);
Kimball distinguishes three fact table types: transaction facts (one row per event), periodic snapshots (one row per entity per period, like inventory above) and accumulating snapshots (one row per process instance, updated as milestones such as order, ship and deliver occur).
| Aspect | Kimball | Inmon | Data Vault 2.0 |
|---|---|---|---|
| Direction | Bottom-up, process by process | Top-down, enterprise model first | Hub-and-link raw vault, marts on top |
| Core storage | Star schemas | 3NF enterprise warehouse | Hubs, links, satellites |
| Time to first value | Short | Long | Medium |
| Change handling | SCD types in dimensions | Normalised history | Insert-only satellites, highly auditable |
| Best for | Analytics-focused teams, BI performance | Large, stable enterprises needing one integrated model | Many changing sources, audit requirements |
In modern lakehouses these are often combined: raw and cleaned layers (Bronze and Silver) play the role of staging or a Data Vault, and the Gold layer is a set of Kimball star schemas.