Modeling Patterns in Holistics

Introduction

Good data modeling is the foundation of a successful analytics platform. The way you structure your models determines whether your analytics will be fast, accurate, and easy to maintain - or slow, confusing, and brittle.

This guide walks you through proven modeling patterns used by data teams worldwide, showing you how to implement them effectively in Holistics with real examples and practical solutions.

Understanding Fact and Dimension Models

Before diving into specific patterns, you need to understand the two fundamental building blocks of data modeling:

Dimension Models

Dimension models provide context - the "who, what, where, and when" of your data.

Characteristics:

• Describe business entities (customers, products, locations, dates)
• Contain descriptive attributes for filtering and grouping
• Have a unique primary key
• Usually have fewer rows (lower cardinality)
• The "one" side of relationships

Example:

dim_products.model.aml

Model dim_products {
  type: 'table'
  table_name: 'ecommerce.products'

  dimension id {
    label: 'Product ID'
    type: 'number'
    primary_key: true
    hidden: true
  }

  dimension name {
    label: 'Product Name'
    type: 'text'
  }

  dimension category {
    label: 'Category'
    type: 'text'
  }
}

Fact Models

Fact models store measurable events - the "how many" and "how much" of your data.

Characteristics:

• Store business events or transactions (orders, page views, inventory levels)
• Contain metrics you want to analyze (revenue, quantity, count)
• Have foreign keys linking to dimension models
• Usually have many rows (high cardinality)
• The "many" side of relationships

Example:

fct_orders.model.aml

Model fct_orders {
  type: 'table'
  table_name: 'ecommerce.orders'
  
  dimension id {
    label: 'Order ID'
    type: 'number'
    primary_key: true
    hidden: true
  }

  dimension user_id {
    label: 'User ID'
    type: 'number'
    hidden: true
  }

  dimension created_at {
    label: 'Order Date'
    type: 'datetime'
  }

  dimension amount {
    label: 'Amount'
    type: 'number'
  }

  measure total_orders {
    label: 'Total Orders'
    type: 'number'
    definition: @aql count(fct_orders.id) ;;
  }

  measure total_revenue {
    label: 'Total Revenue'
    type: 'number'
    definition: @aql sum(fct_orders.amount) ;;
  }
}

Quick Classification:

• "What am I measuring?" → Fact models
• "What am I grouping by?" → Dimension models

Common Modeling Patterns

Star Schema (Single Fact, Multiple Dimensions)

The most common pattern where one fact table connects to multiple dimensions, creating a star-like structure.

When to use:

• One primary business process to analyze
• Simple, fast queries

Visual:

Learn more about Star Schema →

Galaxy Schema (Multiple Facts, Shared Dimensions)

Multiple fact tables sharing common dimensions, also known as fact constellation schema.

When to use:

• Analyzing multiple business processes together
• Different facts share common dimensions
• Building metrics that span multiple fact tables

Visual:

Learn more about Galaxy Schema →

Snowflake Dimensions (Dimension Hierarchies)

Dimensions that connect to other dimensions, creating hierarchies and normalized structures.

When to use:

• Natural dimension hierarchies exist
• Reusing dimensions across multiple facts
• Data normalization is important

Learn more about Snowflake Dimensions →

Role-Playing Dimensions

The same dimension used multiple times in a fact table, each with different meanings.

When to use:

• Multiple date fields in a fact (created, shipped, delivered)
• Same dimension referenced in different contexts

Example: Orders with created_at, delivered_at, cancelled_at, refunded_at all linking to the same date dimension.

Learn more about Role-Playing Dimensions →

Quick Reference

Pattern Selection Guide

Pattern	Use When
Star Schema	Single business process, simple queries
Galaxy Schema	Multiple facts, shared dimensions
Snowflake Dimensions	Dimension hierarchies, data normalization
Role-Playing Dimensions	Same dimension, multiple meanings

Additional Resources

Core Concepts:

• Data Models in Holistics
• Relationships Overview
• Datasets Overview

Advanced Topics:

• Handle Path Ambiguity
• Dataset Relationships
• with_relationships() Reference

Troubleshooting:

• Fan-out Issues