Skip to main content

Calculated Field

A Calculated Field is a model column whose value is computed from an expression at query time instead of being read directly from the underlying source.

Calculated fields are one of the main ways Wren Engine moves business logic into the modeling layer. Instead of repeating the same SQL expressions across queries, you define them once in the model and reuse them everywhere.

When to use a calculated field​

Use a calculated field when you need to:

  • derive a value from other columns in the same model
  • normalize or reshape source fields into business-facing outputs
  • reference fields from a related model
  • centralize reusable analytical logic

If the output should behave like a regular column on a model, a calculated field is usually the right tool.

Structure​

In JSON MDL, a calculated field is a column with isCalculated: true and an expression.

{
  "name": "customer_name",
  "type": "VARCHAR",
  "isCalculated": true,
  "expression": "customer.first_name || ' ' || customer.last_name"
}

Example: calculated field within a single model​

The following model exposes source columns from orders and adds a calculated field called order_year:

{
  "name": "orders",
  "tableReference": {
    "catalog": "memory",
    "schema": "main",
    "table": "orders"
  },
  "columns": [
    {
      "name": "orderkey",
      "type": "INTEGER"
    },
    {
      "name": "custkey",
      "type": "INTEGER"
    },
    {
      "name": "price",
      "type": "INTEGER"
    },
    {
      "name": "purchasetimestamp",
      "type": "TIMESTAMP"
    },
    {
      "name": "order_year",
      "type": "TIMESTAMP",
      "isCalculated": true,
      "expression": "date_trunc('year', purchasetimestamp)"
    }
  ],
  "primaryKey": "orderkey"
}

You can then query the calculated field like any other model column:

SELECT orderkey, order_year
FROM orders;

Example: calculated field using a relationship​

Calculated fields can also reference columns from a related model. First define a relationship:

{
  "name": "orders_customers",
  "models": ["orders", "customers"],
  "joinType": "MANY_TO_ONE",
  "condition": "orders.custkey = customers.custkey"
}

Then expose that relationship in the orders model and use it in a calculated field:

{
  "name": "orders",
  "tableReference": {
    "catalog": "memory",
    "schema": "main",
    "table": "orders"
  },
  "columns": [
    {
      "name": "orderkey",
      "type": "INTEGER"
    },
    {
      "name": "custkey",
      "type": "INTEGER"
    },
    {
      "name": "customer",
      "type": "customers",
      "relationship": "orders_customers"
    },
    {
      "name": "customer_name",
      "type": "VARCHAR",
      "isCalculated": true,
      "expression": "customer.name"
    }
  ],
  "primaryKey": "orderkey"
}

With that definition in place:

SELECT orderkey, customer_name
FROM orders;

Wren Engine resolves the relationship automatically and compiles the generated SQL with the required join.

Scope rules​

Calculated field expressions do not operate on arbitrary SQL context. They are evaluated inside the model definition and follow the model's symbol scope.

In practice, a calculated field can reference:

  • normal source columns in the same model
  • relationship fields declared in the same model
  • columns on related models reached through those relationship fields

A calculated field should not be treated as a general-purpose place to embed unrelated query logic. It is best used for column-level business logic that belongs to the model.

TO_ONE vs TO_MANY relationships​

Calculated fields behave differently depending on relationship cardinality.

TO_ONE​

For ONE_TO_ONE and MANY_TO_ONE relationships, calculated fields can usually reference related columns directly:

{
  "name": "customer_name",
  "type": "VARCHAR",
  "isCalculated": true,
  "expression": "customer.name"
}

This works because each row in the base model resolves to at most one related row.

TO_MANY​

For ONE_TO_MANY relationships, the expression must use aggregation to avoid row multiplication.

{
  "name": "total_order_value",
  "type": "BIGINT",
  "isCalculated": true,
  "expression": "sum(orders.price)"
}

In these cases, Wren Engine wraps the relationship traversal in an aggregate subquery keyed by the base model's primary key.

How Wren Engine evaluates calculated fields​

At a high level, Wren Engine builds calculated fields in a separate scope above the source-column projection layer.

That means:

  • source columns are resolved first
  • calculated expressions are applied on top of that projection
  • relationship-based calculated fields may introduce additional join or aggregate subqueries

This execution model is why calculated fields are reusable and composable from the model's point of view, while still compiling down to executable SQL for the target engine.

Design guidance​

Calculated fields work best when they are:

  • stable business logic rather than ad hoc analysis
  • understandable from the model alone
  • named as reusable business concepts
  • scoped narrowly enough to stay reviewable

If the logic is query-shaped rather than column-shaped, consider using a View instead.