Write your first model

You write a semantic model in YAML. This procedure shows how you make two warehouse tables into metrics. The tables are customer and order. Consumers can query the metrics.

Warehouse tables

Assume your warehouse has these two tables:

erDiagram
    CUSTOMERS ||--o{ ORDERS : "customer_id"
    CUSTOMERS {
        text id PK
        varchar region
        date signup_date
        numeric ltv
    }
    ORDERS {
        text id PK
        text customer_id FK
        varchar status
        timestamp order_date
        numeric amount
    }

The relationship is:

• An order belongs to exactly one customer (many_to_one).
• A customer can have many orders.

Create the model YAML

Create a file named model.yaml. This single-file layout is for the headless engine. Studio stores one file per entity. The schema for this file is Semantic model YAML.

source: main_warehouse

entities:
  - name: Customer
    table: public.customers
    primary_key: id
    dimensions:
      - name: id
        column: id
        type: string
      - name: region
        column: region
        type: string
        values: [na, emea, apac]
      - name: signup_date
        column: signup_date
        type: date
        grains: [day, month, quarter, year]
    measures:
      - name: customer_count
        expr: id
        agg: count
      - name: total_ltv
        expr: ltv
        agg: sum

  - name: Order
    table: public.orders
    primary_key: id
    dimensions:
      - name: id
        column: id
        type: string
      - name: status
        column: status
        type: string
        values: [pending, completed, refunded]
      - name: order_date
        column: order_date
        type: timestamp
        grains: [day, week, month, quarter, year]
    measures:
      - name: revenue
        expr: amount
        agg: sum
      - 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
    description: Average order value across completed transactions.
  - name: orders_per_customer
    expr: order_count / customer_count
    description: Average order count per customer.

Compiler rules

1. Set primary_key on every entity. A cross-entity query fails with reason missing_primary_key when that key is empty. The compiler uses it to stop measure double-counting on fan-out joins.
2. Do not use an avg measure. Average order value is a Metric: revenue / order_count after aggregation. It is not avg(amount) on raw unjoined rows.
3. count_distinct is non-additive. Unlike sum or count, you cannot re-aggregate distinct counts across dimensions without raw rows or a rollup sketch.
4. Relationship names are unique. If two entities connect on several foreign keys (billing_address_id and shipping_address_id), give each relationship a distinct name.

Apply and publish

Apply and publish run in the console only.

1. Apply draft. The engine validates entity names, formula syntax, and Join Graph cycles. Errors appear before runtime queries change.
2. Publish. The engine freezes the draft into an immutable version (current). Consumers query the new version. The service does not go down.

In the headless engine, save model.yaml and restart the process.

Next: Advanced semantic modeling · Query the REST API.