Advanced semantic modeling

You put the business logic in the semantic model. You do not put it in BI tools or in agent prompts.

Calculated dimensions

When an attribute needs SQL, not a direct column, use expr::

dimensions:
  - name: order_size_tier
    type: string
    expr: "CASE WHEN amount >= 1000 THEN 'enterprise' WHEN amount >= 100 THEN 'standard' ELSE 'micro' END"
    values: [enterprise, standard, micro]

Calculated dimensions compile inline in the generated query. You can group or filter on them like standard columns.

Filtered measures

A filtered measure applies conditions inside the aggregation with CASE WHEN ...:

measures:
  - name: completed_revenue
    expr: amount
    agg: sum
    filters:
      - dimension: status
        op: eq
        value: completed

  - name: refunded_amount
    expr: amount
    agg: sum
    filters:
      - dimension: status
        op: in
        value: [refunded, chargeback]

Consumers can query these slices next to other metrics in one query.

Rolling window measures

To compute trailing or cumulative windows, declare a rolling_window::

measures:
  - name: revenue_trailing_30d
    expr: amount
    agg: sum
    rolling_window:
      trailing: 30 day

The compiler builds window frames on the selected time dimension.

Derived metrics

A metric can name another metric. The engine inlines the reference. It detects cycles:

metrics:
  - name: gross_profit
    expr: revenue - cost_of_goods_sold

  - name: gross_margin_pct
    expr: (gross_profit / revenue) * 100
    description: Gross profit margin percentage.

Period-over-period and YoY

Use offset(expr, interval) to read one measure at an earlier period. After inlining, expr must be one bare measure.

metrics:
  - name: revenue_prior_year
    expr: offset(revenue, 1 year)

  - name: revenue_yoy_growth
    expr: (revenue - offset(revenue, 1 year)) / offset(revenue, 1 year)

  - name: order_count_prior_month
    expr: offset(order_count, 1 month)

Supported offset units: day, week, month, year.

Multi-stage metrics (avg_per, min_per, max_per)

Some calculations need two passes. First, aggregate per entity. Then aggregate across the cohort. Example: average customer revenue.

metrics:
  - name: avg_revenue_per_customer
    expr: avg_per(revenue, customer_id)
    description: Calculates customer revenue totals first, then averages across all customers.

  - name: max_revenue_per_status
    expr: max_per(revenue, status)

The second name is a plain dimension on that measure’s own entity. Write it as a bare name. Do not qualify it with an entity. The engine isolates the first pass in a subquery. Then it computes the outer metric.

Percentiles and non-additive aggregations

For latency or distribution, use percentile aggregations:

measures:
  - name: median_order_amount
    expr: amount
    agg: median

  - name: p95_latency
    expr: latency_ms
    agg: p95

  - name: p99_latency
    expr: latency_ms
    agg: p99

Supported aggregations: median, p90, p95, p99. Supported on PostgreSQL, Snowflake, BigQuery, and DuckDB.

Fiscal calendars

To align analytics with a fiscal year, set fiscal_year_start_month (1–12) on a time dimension:

dimensions:
  - name: order_date
    column: order_date
    type: timestamp
    fiscal_year_start_month: 2              # Fiscal year begins in February
    grains: [day, month, quarter, year, fiscal_quarter, fiscal_year]

When a query asks for fiscal_quarter or fiscal_year, the compiler shifts calendar quarters and years by that offset.

Custom SQL entities (sql:)

If an entity cannot map to one table, back it with a SQL subquery:

entities:
  - name: ActiveSubscription
    primary_key: id
    sql: |
      SELECT id, user_id, plan_id, started_at, mrr
      FROM billing.subscriptions
      WHERE status = 'active' AND deleted_at IS NULL
    dimensions:
      - name: id
        type: string
      - name: started_at
        type: timestamp
        grains: [day, month, year]
    measures:
      - name: total_mrr
        expr: mrr
        agg: sum

Entity inheritance (extends:)

An entity can inherit dimensions and measures from a parent:

entities:
  - name: BaseItem
    primary_key: id
    dimensions:
      - name: id
        type: string
      - name: created_at
        type: timestamp
        grains: [day, month]

  - name: PhysicalProduct
    extends: BaseItem
    table: warehouse.products
    dimensions:
      - name: weight_kg
        type: decimal

Model drift resilience

When you rename or deprecate a field, keep stored queries working:

dimensions:
  - name: region_code
    column: region
    renamed_from: [region, country_region]   # Resolves legacy query names automatically
    deprecated: false

metrics:
  - name: old_gmv
    expr: revenue
    deprecated: true
    deprecated_note: "Superseded by net_revenue. Scheduled for removal 2027-01-01."

• renamed_from. Legacy queries that use the old name are rewritten to the canonical name.
• deprecated: true. Catalog discovery flags the item. Active queries still run.

Next: Query the REST API · Connect an LLM agent.