Write your first model

This walks through modeling two ordinary warehouse tables. If a term here is unfamiliar, the glossary has the precise definition.

The problem

You have two tables: customer and order. Product wants numbers like “average order value by region” without every dashboard writing its own SQL join and its own average — which will eventually disagree with each other.

customer                          order
┌─────────────┬──────────┐        ┌──────────────┬──────────────────┐
│ column      │ type     │        │ column       │ type             │
├─────────────┼──────────┤        ├──────────────┼──────────────────┤
│ id          │ pk       │        │ id           │ pk               │
│ region      │ text     │        │ customer_id  │ fk → customer.id │
│ signup_date │ date     │        │ status       │ text             │
│ ltv         │ numeric  │        │ order_date   │ timestamp        │
└─────────────┴──────────┘        │ amount       │ numeric          │
                                   └──────────────┴──────────────────┘

Six rules

1. Entity ≈ business noun / table
2. Dimension = slice attribute (a type, not an aggregation)
3. Measure = one aggregation (expr + agg)
4. Metric = named formula over measures only, computed after aggregation
5. Relationship = a named edge in the join graph (from the many/FK side to the one side)
6. Filters = always { dimension, op, value }

The model

entities:
  - name: customer
    primary_key: id
    label: Customer
    description: A person or company that places orders.

    dimensions:
      - name: region
        type: string
        label: Region
        values: [na, emea, apac]

      - name: signup_date
        type: date
        grains: [month, quarter, year]

    measures:
      - name: customer_count
        expr: id
        agg: count

      - name: lifetime_value
        expr: ltv
        agg: sum
        unit: USD
        format: currency

  - name: order
    primary_key: id
    label: Order
    dimensions:
      - name: status
        type: string
        values: [pending, completed, cancelled, refunded]

      - name: order_date
        type: timestamp
        grains: [hour, day, week, month, quarter, year]

    measures:
      - name: revenue
        expr: amount
        agg: sum
        unit: USD
        format: currency

      - name: order_count
        expr: id
        agg: count

      - name: unique_customers
        expr: customer_id
        agg: count_distinct

relationships:
  - name: order_customer
    from: order
    to: customer
    cardinality: many_to_one
    keys:
      - [customer_id, id]

metrics:
  - name: avg_order_value
    expr: revenue / order_count
    label: Average Order Value
    unit: USD
    format: currency
    direction: higher_is_better

  - name: orders_per_customer
    expr: order_count / customer_count
    direction: higher_is_better

Here’s the same model as a picture — the two metrics on top, drawing from measures on the entities below them:

orders_per_customer draws on a measure from each entity; avg_order_value draws on two measures already on order. The order_customer relationship is what lets a later query ask for revenue grouped by region, even though region lives on customer, not order.

A few things worth calling out:

• primary_key is required, not a flag set on a dimension — the compiler needs it to keep measures correct when a query joins across a one-to-many relationship (so lifetime_value isn’t multiplied once per order).
• There is no avg aggregation. avg_order_value is a metric — revenue / order_count, computed after aggregation — not avg(amount) computed per row. That distinction is what keeps the number correct once you group by a dimension on the “one” side of a join.
• unique_customers is non-additive (count_distinct) — the engine tracks that for you, and it’s part of why re-aggregating measures across time buckets or rollups needs care.
• Relationship names are unique model-wide. If two entities connect more than one way — a bill_to address and a ship_to address on the same order, say — you’d give each edge its own name so a query can pin which one it means.

Next

Once a model is published, query it — see Query the API for REST, or Use with an LLM agent for MCP.