Modeling patterns

IntroductionCopy Anchor Link

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 in AML with real examples and practical solutions.

Understanding fact and dimension modelsCopy Anchor Link

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

Dimension modelsCopy Anchor Link

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:

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 modelsCopy Anchor Link

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:

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 patternsCopy Anchor Link

Denormalized and rollup tables (built upstream)Copy Anchor Link

A single table with everything already joined and flattened, or rolled up to a coarser grain, often produced upstream in dbt or another ETL tool.

When to use:

• A fixed, known set of questions that won't change
• You want the fastest path to a first working model

For datasets people explore freely, model fact and dimension tables instead (see below). A rolled-up table also cannot answer non-additive questions such as distinct users, because the raw rows are gone.

Star schema (single fact, multiple dimensions)Copy Anchor Link

The most common pattern where one fact table connects to multiple dimensions, creating a star-like structure. Read the full Star schema guide.

When to use:

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

Visual:

Learn more about Star Schema →

Galaxy schema (multiple facts, shared dimensions)Copy Anchor Link

Multiple fact tables sharing common dimensions, also known as fact constellation schema. Read the full Galaxy schema guide.

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 schema (normalized dimensions)Copy Anchor Link

A schema pattern where dimensions are normalized into multiple related tables, creating hierarchical structures. Read the full Snowflake schema guide.

When to use:

• Source data is already normalized
• Storage efficiency is critical
• Multiple dimension hierarchies exist
• Data normalization is important

Learn more about Snowflake Schema →

Role-playing dimensionsCopy Anchor Link

The same dimension used multiple times in a fact table, each with different meanings. Read the full Role-playing dimensions guide.

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 →

Composite keysCopy Anchor Link

Two models join on more than one column at once, because no single column is unique on its own.

When to use:

• The key is a combination, such as order and warehouse
• You would otherwise build a concatenated key dimension on both sides

Example: Order lines joined to shipments on order_id and warehouse_id together.

Learn more about Composite Keys →

Polymorphic associationsCopy Anchor Link

A foreign key points into different tables depending on a type column. The type filter goes into the relationship, not the report.

When to use:

• A *_type and *_id column pair
• Excluding soft-deleted rows on one side of a join

Example: Comments on posts, photos and videos, where commentable_type says which table commentable_id points to.

Learn more about Polymorphic Associations →

Validity windowsCopy Anchor Link

Each fact row picks up the one dimension row whose date range contains it.

When to use:

• Price lists, tax rates or FX rates with valid_from and valid_to
• SCD Type 2 dimensions, as of the event date or current version only

Example: Each order gets the price band in effect on its order date.

Learn more about Validity Windows →

Interval overlapCopy Anchor Link

A record with a date range counts in every period that range touches, also known as events in progress.

When to use:

• Open orders or tickets per day, week or month
• Active subscriptions or headcount per month

Example: An order placed on 25 Jan and delivered on 5 Feb counts as open in both January and February.

Learn more about Interval Overlap →

Quick referenceCopy Anchor Link

Pattern selection guideCopy Anchor Link

Which relationship do I use?Copy Anchor Link

Additional resourcesCopy Anchor Link

Core concepts:

• Model
• Relationship
• Dataset

Advanced topics:

• Handle path ambiguity
• Dataset relationships
• with_relationships() reference

Troubleshooting:

• Fan-out issues