Skip to main content

Advanced Tutorial

Overview​

This tutorial walks through core Modeling Definition Language (MDL) concepts in Wren Engine, using a progressively richer example. You will start with basic models, add relationships, and then define calculated fields on top of those relationships.

NOTE: This tutorial assumes you have access to a running Wren Engine. If not, check out the manual MCP setup guide. This guide focuses on MDL concepts and raw file structure. If you are following the Docker-based MCP quickstart, your workspace layout will be different.

MDL is the primary way to define models, relationships, and reusable analytical logic in Wren Engine. For a broader introduction, see Modeling Overview.

Prepare the MDL file​

Create an mdl.json file under etc/mdl.

├── etc
│  ├── mdl
│  │  └── mdl.json
│  ├── config.properties

Set wren.directory=etc/mdl in etc/config.properties.

wren.directory=etc/mdl

All MDL definitions in this tutorial will be stored in etc/mdl/mdl.json.

What is Modeling Definition Language (MDL)​

What is Modeling Definition Language (MDL)?

At minimum, the MDL examples in this tutorial use these top-level fields:

  • catalog: A logical catalog name that abstracts the original catalog.
  • schema: A logical schema name that abstracts the original schema.
  • models: The models exposed by Wren Engine.
{
  "catalog": "my_catalog",
  "schema": "my_schema",
  "models": [
    {
      "name": "Orders",
      "properties": {},
      "refSql": "select * from tpch.orders",
      "columns": [
        {
          "name": "orderkey",
          "expression": "o_orderkey",
          "type": "integer"
        },
        {
          "name": "custkey",
          "type": "integer",
          "expression": "o_custkey"
        }
      ],
      "primaryKey": "orderkey"
    },
    {
      "name": "Customer",
      "refSql": "select * from tpch.customer",
      "columns": [
        {
          "name": "custkey",
          "expression": "c_custkey",
          "type": "integer"
        },
        {
          "name": "name",
          "expression": "c_name",
          "type": "varchar"
        }
      ],
      "primaryKey": "custkey"
    }
  ]
}

What is a Model?​

A model is a logical representation of a physical table or query result. It lets you expose business-facing structure without changing the underlying data source.

In practice, a model defines which fields are available, how they should be named, and how downstream queries should interpret them.

For the full model definition, see Model.

Query a model​

Once a model is defined, you can query it through Wren Engine.

All datasets in the MDL, including models, metrics, and views, are placed into the search path my_catalog.my_schema. For example:

SELECT orderkey FROM my_catalog.my_schema.Orders

You can query modeled datasets much like tables in a traditional RDBMS. Wren Engine then compiles the query into executable SQL for the configured data source.

Add more dataset types​

Wren Engine supports several dataset types:

  • Model
  • Relationship
  • Metric
  • View

In this tutorial, the next step is to add a Relationship to the MDL. For a broader overview, see Data Modeling - Overview.

What is a Relationship?​

A relationship defines how two models are connected. Wren Engine uses the relationship metadata, including joinType and condition, to plan joins and support relationship-aware calculations. For a detailed reference, see Relationship.

"relationships": [
  {
    "name": "CustomerOrders",
    "models": [ "Orders", "Customer" ],
    "joinType": "MANY_TO_ONE",
    "condition": "Orders.custkey = Customer.custkey"
  }
]

After defining the relationship, add a relationship field to the model so it can reference the related model.

{
  "name": "Orders",
  "refSql": "select * from tpch.orders",
  "columns": [
    {
      "name": "orderkey",
      "type": "integer",
      "expression": "o_orderkey"
    },
    {
      "name": "custkey",
      "type": "integer",
      "expression": "o_custkey"
    },
    {
      "name": "customer",
      "type": "Customer",
      // add relationship field here
      "relationship": "CustomerOrders"
    }
  ],
  "primaryKey": "orderkey"
}

Calculated field​

You can then add a calculated field by setting isCalculated: true. In this example, the calculated field references a column from the related Customer model.

{
  "name": "Orders",
  "refSql": "select * from tpch.orders",
  "columns": [
    {
      "name": "orderkey",
      "type": "integer",
      "expression": "o_orderkey"
    },
    {
      "name": "custkey",
      "type": "integer",
      "expression": "o_custkey"
    },
    {
      "name": "customer",
      "type": "Customer",
      "relationship": "CustomerOrders"
    },
    {
      "name": "customer_name",
      "type": "varchar",
      // add `isCalculated` field
      "isCalculated": true,
      // reference to `customer.name` column
      "expression": "customer.name"
    }
  ],
  "primaryKey": "orderkey"
}

Putting these pieces together, the complete MDL looks like this:

{
  "catalog": "my_catalog",
  "schema": "my_schema",
  "models": [
    {
      "name": "Orders",
      "refSql": "select * from tpch.orders",
      "columns": [
        {
          "name": "orderkey",
          "type": "integer",
          "expression": "o_orderkey"
        },
        {
          "name": "custkey",
          "type": "integer",
          "expression": "o_custkey"
        },
        {
          "name": "customer",
          "type": "Customer",
          "relationship": "CustomerOrders"
        },
        {
          "name": "customer_name",
          "type": "varchar",
          "isCalculated": true,
          "expression": "customer.name"
        }
      ],
      "primaryKey": "orderkey"
    },
    {
      "name": "Customer",
      "refSql": "select * from tpch.customer",
      "columns": [
        {
          "name": "custkey",
          "expression": "c_custkey",
          "type": "integer"
        },
        {
          "name": "name",
          "expression": "c_name",
          "type": "varchar"
        }
      ],
      "primaryKey": "custkey"
    }
  ],
  "relationships": [
    {
      "name": "CustomerOrders",
      "models": [ "Orders", "Customer" ],
      "joinType": "MANY_TO_ONE",
      "condition": "Orders.custkey = Customer.custkey"
    }
  ]
}

With that MDL in place, you can query the customer name directly from Orders:

SELECT customer_name FROM Orders;
   customer_name
--------------------
 Customer#000000370
 Customer#000000781
 Customer#000001234
 Customer#000001369
 Customer#000000557
 Customer#000000392
 ...

Without the modeled relationship and calculated field, you would need to write the join manually in the source database:

SELECT
    Customer.c_name
FROM
    tpch.orders AS Orders
JOIN
    tpch.customer AS Customer ON Orders.o_custkey = Customer.c_custkey;

This is the core benefit of modeling in Wren Engine: once relationships and calculated fields are defined centrally, downstream queries become simpler, more consistent, and easier to reuse across metrics and KPIs.

What’s Next?​