# 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.

<!--
Guidance: How to use this template
- One specification per business process (orders, payments, shipments, stock snapshots). Write a new document for a new fact table with a different grain.
- Follow the order of the sections: business process, then grain, then dimensions, then facts. Declaring the grain before listing columns prevents most modelling mistakes.
- Conformed dimensions (section 5) are owned centrally and reused. Do not create a second customer or date dimension for your star; extend the conformed one through its owner.
- The solution-level view (all stars, the semantic layer, refresh and security) belongs in Analytics and BI Solution Design (`architecture/analytics-bi-solution-design`). Metrics computed from this star are defined in Metric Definition (`data/metric-definition`) and the Semantic Model Specification (`data/semantic-model-specification`). Implementation conventions belong in the dbt Project Standard (`data/dbt-project-standard`).
-->

## Document control

<!-- Guidance: The owner is usually the analytics engineer responsible for the star; the business owner of the process approves the grain and the measures. -->

| 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

<!-- Guidance: Name one business process (an activity that produces events or measurements) and the questions the star must answer. Pitfall: modelling a department ("sales reporting") instead of a process ("customer places an order"). -->

| 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

<!-- Guidance: State in one sentence what one row of the fact table represents, at the most atomic level the source provides. Every dimension and measure must be true at this 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

<!-- Guidance: List every dimension that is true at the grain, with its key and role. Use surrogate keys; keep the natural key as an attribute. Mark conformed 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

<!-- Guidance: List every measure with its unit, source and additivity. Additivity tells report builders which aggregations are valid. Pitfall: storing ratios or percentages as facts; store the numerator and denominator instead. -->

| 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

<!-- Guidance: Show which conformed dimensions this star shares with other stars. Use the same keys and attribute values so that results can be combined across processes (drill across). -->

| 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 |

```mermaid
erDiagram
  FCT_ORDER_LINE }o--|| DIM_DATE : "order date"
  FCT_ORDER_LINE }o--|| DIM_CUSTOMER : customer
  FCT_ORDER_LINE }o--|| DIM_PRODUCT : product
  FCT_ORDER_LINE }o--|| DIM_CHANNEL : channel
  FCT_ORDER_LINE }o--|| DIM_GEOGRAPHY : "shipping region"
  FCT_ORDER_LINE }o--|| DIM_ORDER_FLAGS : flags
  FCT_ORDER_LINE {
    int date_key
    int customer_key
    int product_key
    int channel_key
    int geography_key
    int order_flags_key
    string order_number
    int line_number
    decimal quantity
    decimal gross_amount_eur
    decimal discount_eur
    decimal net_amount_eur
    decimal unit_price_eur
    string order_status
    boolean is_deleted
    timestamp updated_at
  }
```

## 6. Slowly changing dimension handling

<!-- Guidance: Decide per attribute how changes are handled, because the business question decides whether history matters. Record the type and the reason. -->

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

<!-- Guidance: Write down how the load behaves when data is missing, late or corrected. These rules decide whether totals reconcile. -->

| 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

<!-- Guidance: List the automated tests that protect the grain and the totals. -->

| 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

<!-- Guidance: Name the sources you used with the date checked. -->

| 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.