Semantic Model Concepts

The VeloDB MCP Service lets an AI agent query data in VeloDB Cloud through the Model Context Protocol (MCP). It offers two query paths:

• Semantic layer: When a healthy workspace contains a semantic model with a matching metric, the agent prioritizes that definition, and MetricFlow generates the SQL.
• Read-only SQL: When no semantic model or matching metric is available, the agent discovers the metadata and generates a read-only SQL query.

The semantic path is only as accurate as the model definitions it uses. Correct metric expressions, dimensions, relationships, and filters produce consistent governed results. Incorrect definitions can produce incorrect answers even when the query succeeds.

This page explains the concepts and YAML structure of a semantic model. To create, validate, commit, or troubleshoot a model in Semantic Web UI, see Create and manage semantic models.

What is a semantic model?

A semantic model is a business description of a database table. You tell the system:

• What the table's primary key is (an entity)

• Which fields can be used for grouping (dimensions)

• Which fields need aggregation (measures)

Once defined, users can run natural-language-level queries ("show me the total order amount per month"), and the system generates the correct SQL automatically.

Tip:

YAML is the spec and MetricFlow is the translator: it turns "total order amount per month" into SELECT DATE_TRUNC('month', order_date), SUM(amount) FROM orders GROUP BY 1.

Semantic model structure

A complete semantic_model contains the following fields:

Field	Type	Required	Description
name	string	✅	Globally unique model name. Starts with a lowercase letter; may contain digits and underscores
db_table	string	✅	The VeloDB physical table, in database.table format, e.g. dw.orders
defaults	object	✅	Default configuration. Must currently contain agg_time_dimension
entities	list	✅	The table's entity definitions. At least one type: primary main entity
dimensions	list	✅	Dimensions for grouping and filtering
measures	list	Recommended	Aggregation definitions. Once defined, they automatically become queryable metrics
description	string	Optional	Text description of the model
label	string	Optional	Display name
primary_entity	string	Conditional	Required when entities has no type: primary entity

Warning:

Every name (model, entity, dimension, measure) must:

• Start with a lowercase letter
• Contain only lowercase letters, digits, and underscores
• Not contain two consecutive underscores __
• Be at least 2 characters

For example:

• Use order_id, total_amount, or user_count
• Do not use OrderID, order__id, or a

Entities and table identity

Entities define uniqueness and relationships between rows. Every table must have one main entity.

Field	Type	Required	Description
name	string	✅	Entity name, unique within this model
type	enum	✅	Entity type (see the table below)
expr	string	Recommended	The corresponding database column. Can be omitted (defaults to name); SQL expressions are also supported
description	string	Optional	Text description
label	string	Optional	Display name

Entity types

Type	Meaning	When to use
primary	Primary key. Unique per row, covers all records	The table's ID column. Each table must have exactly one primary entity
foreign	Foreign key. May have duplicates and nulls	A column that joins to another table, such as customer_id or product_id
unique	Unique key. Unique per row, may not cover all records	E.g. an email or ID number
natural	Natural key. A real-world unique identifier	E.g. a product barcode or employee number

Example: entity definitions for the orders table

entities:
  - name: order_id               # primary key: the unique ID of each order
    type: primary
    expr: order_id

  - name: customer               # foreign key: joins to the users table
    type: foreign
    expr: user_id

  - name: order_ref              # foreign key + SQL expression
    type: foreign
    expr: substring(trace_id FROM 1 FOR 8)

Note:

If the table has no type: primary entity, use primary_entity: entity_name at the top level of the model.

Dimensions for grouping data

Dimensions define how data is grouped and filtered. There are two types: time dimensions and categorical dimensions.

Field	Type	Required	Description
name	string	✅	Dimension name
type	enum	✅	time or categorical
type_params	object	Required for time	Time-granularity configuration (see below)
expr	string	Recommended	The corresponding column or a SQL expression
is_partition	bool	Optional	Whether it is a partition column. Default false
description	string	Optional	Text description
label	string	Optional	Display name

Time granularity (time_granularity)

Granularity	Meaning	Example
day	By day	2025-01-15
week	By week	2025-W03
month	By month	2025-01
quarter	By quarter	2025-Q1
year	By year	2025
hour	By hour	2025-01-15 14:00
minute	By minute	2025-01-15 14:30

Example

dimensions:
  - name: order_date               # order date (by day)
    type: time
    type_params:
      time_granularity: day
    expr: order_date

  - name: order_month              # order month (by month)
    type: time
    type_params:
      time_granularity: month
    expr: order_date               # same column, different granularity

  - name: channel                   # channel (categorical)
    type: categorical
    expr: channel

  - name: status_label             # with a SQL expression
    type: categorical
    expr: concat(status, '_', channel)

Measures for aggregation

Measures define aggregations over data columns. Each measure automatically generates a queryable metric of the same name.

Field	Type	Required	Description
name	string	✅	Measure name (also becomes the metric name)
agg	enum	✅	Aggregation type (see the table below)
expr	string	Recommended	The column or SQL expression to aggregate
description	string	Optional	Metric description
label	string	Optional	Display name
create_metric	bool	Optional	Set to false to not auto-generate a metric. Default true
agg_time_dimension	string	Optional	Overrides the model's default time dimension

Aggregation types

Type	Meaning	Typical use
sum	Sum	Total amount, total count
count	Count	Order count, user count
count_distinct	Distinct count	Distinct users, active devices
average	Average	Average amount, average duration
min	Minimum	Lowest price, earliest time
max	Maximum	Highest price, latest time
median	Median	Median order amount
percentile	Percentile	P99 latency, P95 amount (requires agg_params.percentile)
sum_boolean	Boolean sum	Conversion count, pass count

Example

measures:
  - name: total_amount            # total order amount
    description: "Sum of all order amounts"
    agg: sum
    expr: amount
    label: "Total Amount"

  - name: order_count             # order count
    agg: count
    expr: order_id

  - name: unique_customers        # distinct customers
    description: "Number of distinct customers who placed an order"
    agg: count_distinct
    expr: user_id

  - name: p99_amount             # P99 order amount
    agg: percentile
    expr: amount
    agg_params:
      percentile: 0.99

  - name: internal_counter       # not exposed as a metric
    agg: sum
    expr: raw_value
    create_metric: false

Full example

Here is a complete semantic-model definition for an e-commerce scenario — the orders table:

# models/orders.yaml — orders fact table
---
semantic_model:
  name: orders
  description: "E-commerce order fact table; each row is one order"
  db_table: dw.orders
  defaults:
    agg_time_dimension: order_date

  # ── Entities ──
  entities:
    - name: order_id
      description: "Order primary key"
      type: primary
      expr: order_id

    - name: customer
      description: "Associated user"
      type: foreign
      expr: user_id

    - name: product
      description: "Associated product"
      type: foreign
      expr: product_id

  # ── Dimensions ──
  dimensions:
    - name: order_date
      description: "Order date (by day)"
      type: time
      type_params:
        time_granularity: day
      expr: order_date

    - name: order_month
      description: "Order month"
      type: time
      type_params:
        time_granularity: month
      expr: order_date

    - name: channel
      description: "Order channel"
      type: categorical
      expr: channel

    - name: status
      description: "Order status"
      type: categorical
      expr: status

  # ── Measures ──
  measures:
    - name: total_amount
      description: "Total order amount"
      label: "Total Amount"
      agg: sum
      expr: amount

    - name: order_count
      description: "Total order count"
      label: "Order Count"
      agg: count
      expr: order_id

    - name: unique_customers
      description: "Distinct customers who placed an order"
      label: "Distinct Customers"
      agg: count_distinct
      expr: user_id

    - name: avg_amount
      description: "Average order amount"
      label: "Average Order Value"
      agg: average
      expr: amount

Companion: the users and products tables

# models/users.yaml — users dimension table
---
semantic_model:
  name: users
  description: "User dimension table"
  db_table: dw.users
  defaults:
    agg_time_dimension: register_date

  entities:
    - name: user_id
      type: primary
      expr: user_id

  dimensions:
    - name: register_date
      type: time
      type_params:
        time_granularity: day
    - name: city
      type: categorical
    - name: level
      type: categorical

  measures:
    - name: user_count
      agg: count
      expr: user_id

# models/products.yaml — products dimension table (pure dimension table, no measures, so defaults is omitted)
---
semantic_model:
  name: products
  description: "Product dimension table"
  db_table: dw.products

  entities:
    - name: product
      type: primary
      expr: product_id

  dimensions:
    - name: product_name
      type: categorical
      expr: name
    - name: category
      type: categorical
      expr: category
    - name: brand
      type: categorical
      expr: brand

Advanced metric definitions

Beyond the simple metrics auto-generated from measures, you can define advanced metrics with a metric: document. Advanced metrics combine existing measures or metrics to implement more complex logic. Four types are supported:

Type	Meaning	Typical scenario
ratio	Ratio metric: numerator ÷ denominator	Conversion rate, profit margin, share
derived	Derived metric: an expression over existing metrics	Period-over-period growth, year-over-year change, weighted calculations
cumulative	Cumulative metric: accumulates over a time window	Sales over the last 7 days, monthly cumulative registrations
conversion	Conversion metric: conversion analysis between two events	Order conversion rate, registration conversion rate

Ratio metrics

Compute the ratio of two metrics, e.g. orders per user = order count / user count.

# models/orders_per_user.yaml
---
metric:
  name: orders_per_user
  description: "Orders per user: order count / user count"
  type: ratio
  type_params:
    numerator: order_count       # numerator: references an existing metric
    denominator: user_count      # denominator: references an existing metric

Both the numerator and the denominator must be already-defined metric names (either simple metrics or other advanced metrics).

Derived metrics

Computed from one or more existing metrics via an expression. Most often used for period-over-period and year-over-year calculations.

# models/revenue_growth.yaml — period-over-period growth
---
metric:
  name: revenue_growth
  description: "Revenue period-over-period growth rate"
  type: derived
  type_params:
    expr: (current_revenue - prev_revenue) / prev_revenue
    metrics:
      - name: total_amount         # current-period revenue
        alias: current_revenue
      - name: total_amount         # prior-period revenue (same metric, with an offset)
        alias: prev_revenue
        offset_window: 1 month     # shift back by one time window

Parameter	Description
expr	The calculation expression; reference each input metric by its alias
metrics	The list of input metrics
name	The referenced metric name
alias	The alias used in expr
offset_window	Time offset, e.g. 1 month, 7 days, 1 year
offset_to_grain	The granularity to offset to, e.g. month, year

Cumulative metrics

Accumulate a metric over a time window, e.g. "sales over the last 7 days".

# models/weekly_sales.yaml
---
metric:
  name: weekly_sales
  description: "Sales over the last 7 days"
  type: cumulative
  type_params:
    measure:
      name: total_amount
    window: 7 days          # time window: the past 7 days

Parameter	Description
measure	The referenced measure name (from a semantic_model's measures)
window	Time-window format number granularity, e.g. 28 days, 4 weeks, 3 months
grain_to_date	Optional. Accumulate to a given grain, e.g. month (month-to-date), year (year-to-date)

Conversion metrics

Measure the rate at which users convert from one event (base) to another (conversion). Commonly used to analyze user funnels.

# models/order_conversion.yaml
---
metric:
  name: register_to_order_conversion
  description: "Registration-to-order conversion rate"
  type: conversion
  type_params:
    conversion_type_params:
      base_measure:                 # base event (registration)
        name: user_count
      conversion_measure:          # conversion event (order)
        name: order_count
      entity: user               # join entity: the dimension to compute conversion by
      calculation: conversion_rate  # calculation method

Parameter	Description
base_measure	The base event's measure name
conversion_measure	The conversion event's measure name
entity	The join entity to compute conversion by (usually user or session)
calculation	conversion_rate (the rate) or conversions (absolute count)
window	Optional. Conversion window, e.g. 7 days

Common scenarios

Scenario 1: the same column as dimensions at different granularities

A single date column can define day, week, and month dimensions at once:

dimensions:
  - name: order_date
    type: time
    type_params:
      time_granularity: day
    expr: order_date

  - name: order_week
    type: time
    type_params:
      time_granularity: week
    expr: order_date        # same column!

  - name: order_month
    type: time
    type_params:
      time_granularity: month
    expr: order_date        # same column!

Scenario 2: using SQL expressions

When column names are not intuitive, or you need computed fields, use a SQL expression:

dimensions:
  - name: user_label
    type: categorical
    expr: concat(level, '_', city)  # concatenated field

entities:
  - name: user_short_id
    type: foreign
    expr: substring(trace_id FROM 1 FOR 8)  # substring

measures:
  - name: net_amount
    agg: sum
    expr: coalesce(amount, 0) - coalesce(discount, 0)  # computed field

Tip:

Standard SQL functions such as substring, concat, coalesce, and cast are supported. These expressions are recognized during physical validation and skip column-name checks.

Scenario 3: partitioned tables

If the table has a partition column, mark it with is_partition: true:

dimensions:
  - name: ds
    type: time
    type_params:
      time_granularity: day
    is_partition: true              # mark as the partition column
    expr: ds

Scenario 4: hiding internal measures

Some measures are only intermediate calculations and should not be exposed to end users; set create_metric: false:

measures:
  - name: total_amount            # ✅ public metric
    agg: sum
    expr: amount

  - name: _raw_count              # ❌ not exposed
    agg: count
    expr: order_id
    create_metric: false

Advanced features

Filters

You can add SQL filter conditions in a measure definition or a metric definition. Filters are applied before aggregation.

Note:

You can add filters in these locations:

• The filter: field of a measures entry limits the data range of a single measure.
• The filter: field of a metric: limits the data range of the whole metric.
• A measure's input_measures[].filter: limits a measure referenced by an advanced metric.

# Example 1: measure-level filter — only the total amount of "completed" orders
measures:
  - name: completed_amount
    description: "Sum of completed order amounts"
    agg: sum
    expr: amount
    filter: {{ render_dimension_template('status') }} = 'completed'

# Example 2: metric-level filter
---
metric:
  name: premium_user_orders
  description: "Order count for premium users"
  type: simple
  type_params:
    measure:
      name: order_count
      filter: {{ render_dimension_template('user_level') }} = 'premium'

Warning:

Follow these filter syntax requirements:

• In YAML, reference a dimension with {{ Dimension('qualified_name') }} or {{ render_dimension_template('dimension_name') }}.
• Reference an entity with {{ Entity('entity_name') }} or {{ render_entity_template('entity_name') }}.
• Follow the reference with a normal SQL condition, such as = 'value' or IN ('a', 'b').
• The where parameter of query_metric accepts raw SQL directly, such as "channel = 'APP'". The compiler converts it to MetricFlow template syntax automatically.

Saved queries

Save a frequently used combination of metrics + grouping + filters as a query template that users can call directly.

# models/weekly_report.yaml
---
saved_query:
  name: weekly_revenue_report
  description: "Weekly revenue report: total order amount and order count grouped by channel"
  label: "Weekly Revenue Report"
  query_params:
    metrics:
      - total_amount
      - order_count
    group_by:
      - order_id__order_week      # group by week
      - order_id__channel         # group by channel
    order_by:
      - "-order_id__order_week"  # descending by week
    limit: 52

Field	Description
metrics	The list of metric names to query
group_by	Grouping dimensions, in entity_name__dimension_name format (joined by a double underscore)
order_by	Sorting; a - prefix means descending
where	Filter condition (same syntax as filters)
limit	Maximum number of rows

Tip:

Use entity_name__dimension_name with a double underscore to reference a dimension. For example, order_id__order_date is the order_date dimension of the orders table.

Non-additive measures and slowly changing dimensions (SCD Type II)

Some measures cannot simply be summed (such as inventory or account balance) and need a snapshot value along a specific dimension.

# Non-additive measure: inventory (month-end snapshot)
measures:
  - name: monthly_inventory
    description: "Month-end inventory"
    agg: sum
    expr: inventory_count
    non_additive_dimension:
      name: snapshot_date       # the non-additive dimension
      window_choice: max        # take the maximum within the time window
      window_groupings:
        - product                # snapshot grouped by product

SCD Type II (slowly changing dimensions): when a dimension table has validity time ranges, mark the start and end dimensions:

# Mark SCD Type II in the dimension table
dimensions:
  - name: valid_from
    description: "Validity start time"
    type: time
    type_params:
      time_granularity: day
      validity_params:
        is_start: true              # mark as the start time

  - name: valid_to
    description: "Validity end time"
    type: time
    type_params:
      time_granularity: day
      validity_params:
        is_end: true                # mark as the end time

Null filling and timeline alignment

# Replace NULL with 0 (useful so count-type metrics show 0 on dates with no data)
metric:
  name: daily_orders
  type: simple
  type_params:
    measure:
      name: order_count
    fill_nulls_with: 0         # show 0 on dates with no data
    join_to_timespine: true   # align to the timeline (fill in missing dates)

Parameter	Description
fill_nulls_with	Replace NULL in the aggregated result with a given value (usually 0)
join_to_timespine	Join the metric result with the timeline table so every day/month/year has a row (missing dates filled with NULL or 0)

Native table reference format

Besides the db_table shorthand, MetricFlow's native node_relation format is also supported, including three-part catalog references:

# Two-part: database.table
node_relation:
  schema_name: dw
  alias: orders

# Three-part: catalog.database.table
node_relation:
  database: catalog
  schema_name: dw
  alias: orders

# db_table also supports the three-part form
db_table: catalog.dw.orders

Aggregation mode for cumulative metrics

Cumulative metrics support three period_agg modes that control aggregation within the time window:

# last: take the value of the last day in the window (default behavior)
metric:
  name: end_of_week_inventory
  type: cumulative
  type_params:
    cumulative_type_params:
      measure:
        name: inventory_count
      window: 7 days
      period_agg: last       # take the last day's value

# average: daily average within the window
      period_agg: average    # 7-day average

# first: the value of the first day in the window
      period_agg: first      # take the first day's value

period_agg	Description
last	The last day's value in the window (default)
average	The daily average within the window
first	The first day's value in the window

Constant properties for conversion metrics

In a conversion metric, use constant_properties to specify properties that must stay constant between the two events:

# Analyze conversion by "traffic source", requiring the source to be the same across both events
metric:
  name: register_to_order_by_source
  type: conversion
  type_params:
    conversion_type_params:
      base_measure:
        name: user_count
      conversion_measure:
        name: order_count
      entity: user
      constant_properties:
        - base_property: order_id__channel    # the base event's property
          conversion_property: order_id__channel # the conversion event's property (must match)

Metric time granularity and offset grain

Metric-level time_granularity: you can set a time granularity directly in the metric definition (overriding the query-time default):

metric:
  name: monthly_revenue
  type: simple
  time_granularity: month     # this metric defaults to monthly aggregation
  type_params:
    measure:
      name: total_amount

offset_to_grain: in a derived metric, align the offset to a given grain (rather than the default day level):

metric:
  name: yoy_growth
  type: derived
  type_params:
    expr: (current - prev) / prev
    metrics:
      - name: total_amount
        alias: current
      - name: total_amount
        alias: prev
        offset_window: 1 year
        offset_to_grain: month    # offset to month grain (rather than day)

Entity roles

The same entity (such as user_id) may play multiple roles in a table. For example, user_id in the orders table is both the "buyer" and the "referrer". Use role to distinguish them:

entities:
  - name: user
    type: foreign
    expr: buyer_id
    role: buyer              # this user entity's role is "buyer"

  - name: user
    type: foreign
    expr: referrer_id
    role: referrer           # this user entity's role is "referrer"

When role is not specified, the default role equals the entity name.

Best practices

1. One file per table. Name files table_name.yaml for a clear, maintainable structure.

   ✅ orders.yaml, users.yaml, products.yaml

2. Define entities first, then dimensions, then measures. Entities are the skeleton of the semantic model, dimensions provide grouping, and measures are the query targets. Writing them in this order helps avoid omissions.

3. Write a description for every measure. End users see the metric name and description. A good description lets them understand a metric without reading the YAML.

4. The same time column can define multiple granularity dimensions. For example, the order_date column can have day, week, month, quarter, and year granularities at once.

5. Use meaningful business names for foreign-key entities. The entity name customer is easier to understand than user_id — it represents the concept of "customer", not just "the user_id column".

6. Validate before you commit. Follow the validation and commit workflow after each YAML change. The service checks table existence, column-name correctness, and naming rules. Commit only after validation passes.

7. Measure names must be unique within a model. Measure names can repeat across models, but that causes metric overrides — so it is best to keep them globally unique.

See also

• Create and manage semantic models to create a workspace, add model files, validate changes, and commit them in Semantic Web UI.
• Get Started to connect the VeloDB MCP Service to an AI agent and complete your first query.