Star Schema Pattern

Model data warehouses using the star schema pattern.

Overview

Star schema is a common data warehouse pattern with a central fact table surrounded by dimension tables.

Structure

Fact Table (center)
├── Dimension 1
├── Dimension 2
├── Dimension 3
└── Dimension N

Example

Fact Table

name: Sales
physical_name: sales
datasource: warehouse
cost: 100  # Fact table: higher cost

fields:
  - type: dimension
    name: Sale ID
    data_type: integer
    expression:
      primary_key: true
      sql: sale_id

  - type: dimension
    name: Sale Date
    data_type: date
    expression:
      sql: sale_date

  - type: measure
    name: Total Revenue
    data_type: decimal
    expression:
      sql: sum(amount)

Dimension Tables

# Customer Dimension
name: Customer
cost: 10  # Dimension: lower cost

# Product Dimension
name: Product
cost: 10

# Date Dimension
name: Date
cost: 10

Relationships

datasource: warehouse

# Fact to Dimensions
sales_customer:
  left: Sales
  right: Customer
  sql: left.customer_id = right.id
  cardinality: many_to_one

sales_product:
  left: Sales
  right: Product
  sql: left.product_id = right.id
  cardinality: many_to_one

sales_date:
  left: Sales
  right: Date
  sql: left.sale_date_id = right.date_id
  cardinality: many_to_one

Best Practices

  1. Fact table cost: 100 - Higher cost
  2. Dimension table cost: 10 - Lower cost
  3. Many-to-one relationships - Fact to dimensions
  4. Primary keys on dimensions - Unique identifiers
  5. Organize by domain - Group related tables

Next Steps