How to define a dimension from a SQL expression in dbt

Not every useful dimension is a column. An order size band, a “late” flag or a delivery month are small SQL expressions on existing columns. In the legacy format they were dimensions with an expr; in the latest YAML spec they go in a separate derived_semantics block of the model.

Copy and paste

models:
  - name: orders
    semantic_model:
      enabled: true
    agg_time_dimension: order_date
    columns:
      # ... entities and column dimensions
    derived_semantics:
      dimensions:
        - name: order_size
          type: categorical
          expr: "case when amount >= 100 then 'large' else 'small' end"
        - name: is_late
          type: categorical
          expr: "shipped_date - order_date > 3"
        - name: delivery_month
          type: time
          granularity: month
          expr: "date_trunc('month', shipped_date)"

Query them like any other dimension:

mf query --metrics order_count,revenue --group-by order__order_size
mf query --metrics order_count --group-by order__is_late
mf query --metrics order_count --group-by order__delivery_month__quarter

order__order_size      order_count    revenue
-------------------  -------------  ---------
large                           13       1800
small                            6        430

Not inside the column’s dimension block

The column-level dimension: block accepts no expr. Adding one fails the parse with a long message that ends with:

at path ['columns'][4]['dimension']: {'type': 'categorical', 'expr': 'upper(status)'} is not valid under
any of the given schemas

The column form maps one dimension to one column. Anything computed belongs under derived_semantics, which also holds computed entities such as composite keys.

Writing the expression

• It is SQL for your warehouse, pasted into the generated query. shipped_date - order_date > 3 works on DuckDB and Postgres; on Snowflake write datediff('day', order_date, shipped_date) > 3.
• It uses the model’s columns, not other dimensions or metrics. The expression is evaluated on the rows of the table, before aggregation.
• Quote it in YAML when it contains :, # or starts with a quote, as above.
• A derived time dimension needs granularity, the grain of what the expression returns: month for a date_trunc('month', ...). Queries can then ask for that grain or coarser ones (__quarter, __year), not finer: order__delivery_month__day is refused.

Expression or column

If the same logic is needed outside the Semantic Layer, or it is more than a line, compute it in the dbt model instead and expose the column as an ordinary dimension. The value is then tested, documented and visible in the table. Keep derived_semantics for simple labels and flags that only exist to slice metrics.

Tested with dbt Core 1.12 and MetricFlow 0.213 on DuckDB.