Dimensional Model Specification Template (Star Schema)

One star schema: business process, grain, dimensions and keys, measures with additivity, conformed dimensions, bus matrix, SCD types, load rules, tests

Інженерія та врядування данихСтандартKimball dimensional modelling concepts (The Data Warehouse Toolkit, 3rd ed., 2013, name only)dimensional model specОновлено 29 жовт. 2026 р.

Відкрити в редакторіЗавантажити .md

Як використовувати: Use it when you design or document one fact table and its dimensions. Fill the business process and the one-sentence grain first, then dimensions, then measures. The main pitfall is mixing grains in one fact table or creating private copies of conformed dimensions.

Перегляд

Dimensional Model Specification: [Business process] star schema

Specification for one star schema: the business process, the declared grain, the fact table and its measures with additivity, the dimensions and their keys, conformed dimensions shared with other stars, slowly changing dimension handling per attribute, special cases (unknown members, late-arriving data, degenerate and junk dimensions), load rules, tests and the bus matrix row. Modelling vocabulary follows the dimensional modelling concepts published by Ralph Kimball and Margy Ross (The Data Warehouse Toolkit, 3rd edition, Wiley, 2013), cited by name only. Replace every [bracketed] value and every row marked Example. Worked examples use the order-line star of Corvid Commerce, a fictional SaaS retailer.

Document control

Field	Value
Document ID	[DMS-SALES-001]
Version	[0.1]
Status	[Draft / In review / Approved / Superseded]
Technical owner	[@analytics-engineer]
Business owner	[@process-owner]
Approvers/reviewers	[@data-architect], [@data-steward]
Physical location	[Warehouse, schema]
Last updated	YYYY-MM-DD

Revision history

Version	Date	Author	Change summary
0.1	YYYY-MM-DD	[@name]	First draft
[x.y]	YYYY-MM-DD	[@name]	[Change summary]

1. Business process and questions

Field	Value
Business process	[Process]
Source systems	[Systems and tables]
Questions answered	[List]
Questions not answered here	[List, with the star that answers them]

Example: Business process: a customer places an order on a Corvid storefront. Source: commerce platform order and order-line tables. Questions: units and net sales by product, category, channel, country and day; basket composition; discount depth. Not answered here: fulfilment lead times (see the fulfilment accumulating snapshot star).

2. Grain

Grain: [one row per ...]

Example: One row per order line (one product on one customer order) at the time the order is placed. Cancellations after placement overwrite order_status on the same row (no history kept on the fact) and are not separate rows; cancellation history, if needed, belongs in a separate star.

Fact table type	Choose one
Transaction	One row per event at a point in time
Periodic snapshot	One row per entity per period (day, month)
Accumulating snapshot	One row per process instance, updated at milestones
Factless	One row records that an event or coverage occurred, with no numeric measure

Example: Transaction.

3. Dimensions

ID	Dimension	Role in this star	Surrogate key	Natural key	Conformed?	Owner
DIM-DATE (Example)	dim_date	Order date	`date_key` (YYYYMMDD integer)	Calendar date	Yes	Data platform
DIM-CUST (Example)	dim_customer	Ordering customer	`customer_key`	`customer_id`	Yes	Customer domain
DIM-PROD (Example)	dim_product	Product ordered	`product_key`	`sku`	Yes	Catalogue domain
DIM-CHAN (Example)	dim_channel	Storefront and channel	`channel_key`	`storefront_code`	Yes	Marketing domain
DIM-GEO (Example)	dim_geography	Shipping region	`geography_key`	Country and region code	Yes	Data platform
DIM-OFLAG (Example)	dim_order_flags	Junk dimension: payment method, gift flag, first-order flag	`order_flags_key`	Combination of flags	No	Sales domain
[DIM-nnn]	[Name]	[Role]	[Key]	[Natural key]	[Yes / No]	[@owner]

Degenerate dimensions: order_number and line_number stay on the fact table with no dimension table, so that lines of the same order can be grouped and each line identified. order_status is also kept on the fact as a degenerate attribute, with is_deleted and updated_at as load audit columns (section 7).

4. Facts and measures

ID	Measure	Definition	Unit	Additivity	Source
MSR-001 (Example)	`quantity`	Units ordered on the line	Units	Additive across all dimensions	`order_lines.qty`
MSR-002 (Example)	`gross_amount_eur`	Unit price times quantity, converted to EUR at the order date reference rate	EUR	Additive	Computed
MSR-003 (Example)	`discount_eur`	Discounts allocated to the line	EUR	Additive	`order_lines.discount`
MSR-004 (Example)	`net_amount_eur`	Gross amount minus discount, excluding tax	EUR	Additive	Computed
MSR-005 (Example)	`unit_price_eur`	Price of one unit	EUR	Non-additive: average with care, never sum	`order_lines.price`, converted to EUR at the order date reference rate
[MSR-nnn]	[Measure]	[Definition]	[Unit]	[Additive / Semi-additive / Non-additive]	[Source]

Example: A line of 3 units at 40.00 EUR with a 12.00 EUR discount has gross_amount_eur 120.00 and net_amount_eur 108.00.

5. Conformed dimensions and bus matrix

Business process (star)	Date	Customer	Product	Channel	Geography
Orders (this star) (Example)	X	X	X	X	X
Payments (Example)	X	X		X	X
Fulfilment (Example)	X	X	X		X
Stock snapshot (Example)	X		X		X

6. Slowly changing dimension handling

Reference for the commonly used types (descriptions in original words):

Type	Behaviour when the source value changes
0	Keep the original value; never change it
1	Overwrite the value; no history
2	Add a new dimension row with validity dates and a current flag; facts keep pointing to the version valid when they occurred
3	Keep the previous value in an extra column next to the current one
4	Move fast-changing attributes into a separate small dimension (mini-dimension) referenced from the fact
5	Mini-dimension (type 4) plus a type 1 reference to its current row from the main dimension
6	Type 2 rows that also carry a type 1 "current value" column for the same attribute
7	Facts reference both the type 2 versioned key and a durable key, so reports can choose history or current values

Dimension	Attribute	Type	Reason
dim_customer (Example)	`customer_segment`	2	Sales must be reported by the segment at the time of the order
dim_customer (Example)	`email_hash`	1	Only the current value is useful
dim_customer (Example)	`first_order_date`	0	Never changes by definition
dim_product (Example)	`category`	2	Category reorganisations must not rewrite past sales
dim_product (Example)	`product_name`	1	Corrections only
[Dimension]	[Attribute]	[Type]	[Reason]

7. Special cases and load rules

Case	Rule
Missing dimension reference	Point to the "unknown" member (key -1) and raise a data quality warning
Late-arriving dimension	Insert an inferred member with the natural key, update its attributes when the source row arrives
Late-arriving facts	Look up the dimension version valid at the event time, not the current version
Corrections in source	Update the fact row in place (status, amounts); keep `updated_at`
Deletes in source	Set `is_deleted` on the fact; excluded by the semantic layer
Load frequency	[Hourly / Daily], incremental on `updated_at` with a [3]-day lookback (Example)

8. Tests and reconciliation

ID	Test	Rule
TST-001	Grain uniqueness	`order_number` plus `line_number` is unique
TST-002	Referential integrity	Every foreign key exists in its dimension (unknown member allowed)
TST-003	Reconciliation	Daily `net_amount_eur` total within [0.1]% of the finance ledger (Example)
TST-004	Type 2 integrity	One current row per natural key; validity ranges do not overlap
TST-005	Measure sanity	`net_amount_eur` equals `gross_amount_eur` minus `discount_eur`

9. References

Reference	Version or date checked	Use
Ralph Kimball and Margy Ross, The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling, Wiley, https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/books/data-warehouse-dw-toolkit/	3rd edition (2013); checked 2026-10-30	Concept names (grain, conformed dimension, bus matrix, slowly changing dimension types 0 to 7)

Standard and licence note

Structure aligned with the dimensional modelling concepts published by Ralph Kimball and Margy Ross in The Data Warehouse Toolkit, 3rd edition (Wiley, 2013). The book is copyrighted and is cited by name only; concept names are used as common vocabulary, and the descriptions, tables and examples here are original. Official text: Wiley, purchase or licensed access: https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/books/data-warehouse-dw-toolkit/. Corvid Commerce is fictional. This template is original text written for this collection. Template licence: MIT.

Схожі шаблони

Інженерія та врядування даних