# Star Schema

## Purpose
The Star Schema is the standard pattern for the Gold (consumption) layer. It optimizes data
for analytical queries, BI tools, and multidimensional analysis. Apply this skill when
designing or implementing Gold-layer dimension and fact tables.

## Core Structure

A Star Schema consists of one Fact table at the center connected to multiple Dimension tables:

```
              Store_DimDate
                    |
Store_DimProduct — Store_FactSalesOrderLine — Store_DimCustomer
                    |
              Store_DimSalesPerson
```

## Dimension Tables

**Purpose**: Describe the "who, what, where, when" context of a business event.

**Rules**:
- One surrogate key as primary key — system-generated at the Gold layer (independent of Silver keys).
- Business key preserved alongside the surrogate key for traceability.
- All descriptive attributes flat in one table (denormalized — no sub-tables).
- No measures — only descriptive and categorical attributes.
- SCD Type 1 (overwrite) by default unless history is required (SCD Type 2).

**Naming**: `Store_Dim{Entity}`
Examples: `Store_DimCustomer`, `Store_DimProduct`, `Store_DimDate`, `Store_DimSalesPerson`

**Typical columns**:
- `{Entity}Key` — surrogate key (PK, Gold-generated)
- `{Entity}Id` or `{Entity}Nr` — business key
- Descriptive attributes (name, category, region, etc.)

## Fact Tables

**Purpose**: Record business events at a defined grain (level of detail).

**Rules**:
- Define the grain explicitly before designing any columns.
  One row represents exactly one occurrence of the measured event.
- Contains foreign keys to all related dimensions.
- Contains only measures — no descriptive attributes.
- Every fact table must reference at least one Date dimension.

**Naming**: `Store_Fact{Subject}`
Examples: `Store_FactSalesOrderLine`, `Store_FactInventorySnapshot`

**Typical columns**:
- FK columns for each dimension: `{Dimension}Key`
- Measure columns (see measure types below)

## Measure Types

| Type | Description | Example | Can sum? |
|---|---|---|---|
| Additive | Can be summed across all dimensions | Revenue, Quantity | Yes, always |
| Semi-additive | Can be summed across some dimensions | Account Balance | Not across time |
| Non-additive | Cannot be summed at all | Unit Price, Rate, Percentage | No — document explicitly |

Non-additive measures must be documented as such in the DOCUMENTATION block of the DnAML node.

## Date Dimension

Every fact table must reference a Date dimension.
If multiple date roles exist (order date, ship date, expected delivery date), use
role-playing dimensions: reference the same `Store_DimDate` multiple times with
distinct FK column names.

**Role-playing FK naming**: `OrderDateKey`, `ShipDateKey`, `ExpectedDeliveryDateKey`

## Grain Definition

**Always define the grain before designing any columns.**
The grain answers: "One row in this fact table represents one ___."

Examples:
- One sales order line item
- One daily inventory snapshot per product and warehouse
- One customer service interaction

A fact table with an undefined or mixed grain is the most common source of incorrect aggregations.

## Surrogate Keys in Gold

Gold-layer surrogate keys are independent from Silver surrogate keys.
Do not reuse or pass through Silver Key table surrogate keys into Gold dimensions.
Generate new surrogate keys at the Gold layer.

## Anti-Patterns
- **Descriptive attributes in Fact tables**: Violates Star Schema, breaks BI aggregations.
- **Multiple grains in one Fact table**: Makes measures incomparable — split into separate facts.
- **Business keys as FK columns in facts**: Fragile when business keys change.
- **Snowflaking dimensions**: Normalizing a dimension into sub-tables defeats the Star Schema.
- **Undefined grain**: The most common cause of aggregation errors.
- **Reading Silver Key/Hist tables from Gold**: Use Silver Access views only.

## Naming Conventions

| Object | Pattern | Example |
|---|---|---|
| Dimension | `Store_Dim{Entity}` | `Store_DimCustomer` |
| Fact | `Store_Fact{Subject}` | `Store_FactSalesOrderLine` |
| Dim surrogate key (PK) | `{Entity}Key` | `CustomerKey` |
| Fact FK to dimension | `{Dimension}Key` | `CustomerKey`, `ProductKey` |
| Role-playing date FK | `{Role}DateKey` | `OrderDateKey`, `ShipDateKey` |
