Our engine never sees your warehouse password
datahashi

Semantic model YAML

YAML schema for entities, dimensions, measures, metrics, relationships, rollups, and views in model.yaml.

Updated

This page specifies the YAML the datahashi compiler accepts in model.yaml.

The parser rejects any key it does not know. A typo is a parse error and names the line. Enum keywords are lowercase. The file is one YAML document.

A model may also be split across files: model.yaml and policy.yaml at the folder root, plus every *.yaml file in a subfolder, merged by name, where a name declared twice is an error.

There is no version key and no name key at the root. A query pins a published model with the request field version.

Root schema

source: main_warehouse            # Connection name sent to Query Runner (optional)
owner: "Data Platform Team"       # Documentation only
tags: [core, finance]             # Documentation only
meta:                             # Documentation. Depth ≤ 3. Size ≤ 4096 bytes.
  tier: production

entities: []                      # Entity definitions
relationships: []                 # Join edges (optional)
metrics: []                       # Governed Metrics (optional)
segments: []                      # Named filter lists (optional)
views: []                         # Curated projections (optional)
hierarchies: []                   # Drill paths (optional)
rollups: []                       # Pre-aggregations (optional)
saved_queries: []                 # Stored queries (optional)
tests: []                         # Checks you run on demand (optional)

source, owner, tags, and meta do not change the model version. views, rollups, saved_queries, tests, and hierarchies do not either.

Entity schema

- name: Order                     # Business name (required)
  table: public.orders            # Physical table. Defaults to name.
  # sql: SELECT * FROM ...        # Raw SELECT used as FROM. Wins over table.
  primary_key: id                 # Row identity. Set this on every entity.
  extends: BaseEntity             # Optional parent. One level only.
  label: Orders                   # Catalog text (optional)
  description: Sales orders       # Catalog text (optional)
  tags: [sales]
  owner: "Order Ops"

  dimensions: []
  measures: []
  joins: []                       # Relationships whose from is this entity

table and sql are not exclusive. When both are set, sql wins and table is ignored.

primary_key may be empty in the file. A cross-entity query that needs fan-out isolation then fails with reason missing_primary_key. Set it.

extends copies primary_key, sql, table, label, and description when the child leaves them empty. Dimensions and measures merge by name. The child wins a name clash. Joins are not inherited. The parent stays in the model. An extending entity drops tags, owner, and meta.

Dimension schema

- name: order_date                # Unique within the entity (required)
  type: timestamp                 # int | float | decimal | string | bool | date | timestamp
  column: created_at              # Physical column. Defaults to name.
  # expr: "DATE(created_at)"      # Calculated SQL. Wins over column when both are set.
  grains:                         # Time buckets. Non-empty means the dimension is temporal.
    - minute
    - hour
    - day
    - week
    - month
    - quarter
    - year
    - fiscal_quarter
    - fiscal_year
  fiscal_year_start_month: 2      # 0 = calendar. 1–12 required for fiscal grains.
  values: [pending, completed]    # Catalog list. Not enforced.
  synonyms: [created]
  renamed_from: [created_time]    # Old names still resolve. Responses set meta.aliased_refs.
  deprecated: false
  deprecated_note: "..."
  tags: [time]
  meta: {}

type is required. number and boolean are not types. Use int, float, decimal, or bool.

A fiscal grain without fiscal_year_start_month in 1–12 is a load error. A start month on a dimension with no grains is a load error.

Measure schema

- name: revenue                   # Unique within the entity (required)
  expr: amount                    # SQL over this entity's columns (required)
  agg: sum                        # sum | count | count_distinct | min | max |
                                  # count_distinct_approx | median | p90 | p95 | p99
  filters:                        # Optional. Dimension must be on this entity.
    - dimension: status
      op: eq
      value: completed
  rolling_window:                 # Optional. Not with snapshot. Not stored in a rollup.
    trailing: 30 day              # unbounded | N day|week|month|year
  snapshot:                       # Optional semi-additive balance. Not with rolling_window.
    dimension: as_of              # date or timestamp on this entity, not calculated
    pick: last                    # last (default) | first
  unit: USD                       # Free text
  format: currency                # Free text display hint
  renamed_from: [order_total]
  deprecated: false

agg is required. sum, count, min, and max are additive. The other keywords are not. median, p90, p95, and p99 are the 0.5, 0.9, 0.95, and 0.99 percentiles.

A rolling-window measure needs exactly one temporal-grain dimension in the query. It cannot be used with having.

snapshot.agg must be sum, min, or max.

Relationship schema

- name: order_customer            # Unique in the model (required)
  from: Order                     # Entity name (required)
  to: Customer                    # Entity name (required)
  cardinality: many_to_one        # many_to_one | one_to_many | one_to_one
  keys:                           # [column on from, column on to]
    - [customer_id, id]

There is no many_to_many. Model a bridge entity.

Two entities may have more than one named relationship. A query then sets relationship on the dimension, or it fails with ambiguous_join.

Entity joins are the same edges with from implied. joins and relationships share one name space.

Metric schema

- name: avg_order_value           # Unique in the model (required)
  expr: revenue / order_count     # Arithmetic over measures and metrics (required)
  default_time_dimension: order_date  # Must be a dimension that declares grains
  description: "Average amount"
  direction: higher_is_better     # Free text. Not an enum.
  unit: USD                       # Free text
  format: currency                # Free text. Not an enum.
  synonyms: [aov]
  tags: [executive]
  renamed_from: [aov]
  deprecated: false

Metric functions

  • offset(expr, N unit) reads one measure shifted back N units. After inlining, expr must be exactly one bare measure. N is a positive whole number. Units are day, week, month, and year.
  • avg_per(expr, dim), min_per, and max_per aggregate expr per value of dim, then take the avg, min, or max. expr must inline to one bare measure that is not a rolling window. dim is a plain dimension on that measure’s own entity, written as a bare name.

A bare name resolves to a measure or another metric. Entity.measure is always a measure. A reference cycle is an error. Metric expressions contain no SQL and no filters.

Segment schema

- name: completed_orders          # Unique in the model (required)
  description: "Orders completed"
  filters:                        # At least one filter (required)
    - dimension: status
      op: eq
      value: completed

Each filter dimension must exist on some entity. A segment with zero filters is a load error.

View schema

- name: finance_overview          # Unique in the model (required)
  description: "Executive view"
  metrics: [revenue, profit]      # Names, or exactly ["*"]
  dimensions: [Customer.region]
  measures: []
  filters:                        # Injected into every query through this view
    - dimension: status
      op: eq
      value: completed

A list of ["*"] requires the matching exclude_metrics, exclude_dimensions, or exclude_measures key. That key may be an empty list. exclude_* without "*" is an error. Mixing "*" with names is an error.

Rollup schema

- name: daily_order_rollup        # Unique among rollups (required)
  entity: Order                   # One entity (required)
  dimensions:                     # Required
    - status
    - { name: order_date, grain: day }
    - customer.region             # Other entity. One many_to_one or one_to_one path.
  measures:                       # Measures of entity (required)
    - revenue
    - order_count
  filters:                        # Optional baked predicate. Dimension on entity.
    - dimension: status
      op: eq
      value: completed
  sketches:                       # count_distinct_approx or a percentile, also listed in measures
    - unique_customers
  refresh:
    every: 1h                     # ≥ 1m. Or cron. Not both.
    when_changed: updated_at      # Needs every or cron. Not with when_changed_sql.
    # cron: "0 4 * * *"
    # manual: true                # Alone. No schedule.
  build_range:
    dimension: order_date         # Stored date or timestamp on entity
    days: 400                     # ≥ 1
  partition:                      # Needs build_range
    by: month                     # day | week | month | quarter | year
    refresh_last: 2               # Default 1. Periods re-read on a scheduled refresh.
    compact: quarter              # Optional coarser file for ended periods
    append: true                  # Optional. Not with live_tail. Additive measures only.
  live_tail: true                 # Needs partition
  sort_by: [order_date]

compact and append belong inside partition. They are not root keys of the rollup.

A rolling-window measure and a snapshot measure cannot be stored.

Filter object

Measures, segments, views, and rollups share one filter shape:

{ dimension: status, op: in, value: [open, paid] }

Operators are eq, ne, lt, le, gt, ge, in, not_in, between, contains, starts_with, ends_with, not_contains, not_starts_with, not_ends_with, is_null, and is_not_null.

String match operators require a string dimension. Model YAML does not accept query-API aliases such as >= or gte.

Hierarchies, saved queries, and tests

hierarchies:
  - name: invoice_time
    levels:                       # At least two. Coarse grain before fine grain.
      - { dimension: order_date, entity: Order, grain: year }
      - { dimension: order_date, entity: Order, grain: month }

saved_queries:
  - name: revenue_by_region
    query:                        # Same shape as POST /v1/query
      measures: [revenue]
      dimensions: [{ name: region }]
      limit: 100

tests:
  - name: revenue_is_positive
    query:
      measures: [revenue]
    expect:                       # At least one of rows, row_count, no_nulls, unique
      row_count: { min: 1, max: 1 }
      no_nulls: [revenue]

entity on a hierarchy level is required when the dimension name exists on more than one entity. grain is required for a temporal dimension and forbidden otherwise.

Tests do not run at load. Run them with POST /v1/model-tests.

Next: Semantic query syntax · Environment variables.