Wren Engine - MDL and SQL generation example

mdl.json

{
  "schema": "public",
  "catalog": "wrenai",
  "models": [
    {
      "name": "customers",
      "columns": [
        {
          "name": "City",
          "type": "VARCHAR",
          "isCalculated": 0,
          "notNull": 0,
          "expression": "",
          "properties": {
            "description": "The Customer City, where the customer company is located. Also called \"customer segment\".",
            "displayName": "City"
          }
        },
        {
          "name": "UserId",
          "type": "VARCHAR",
          "isCalculated": 0,
          "notNull": 0,
          "expression": "Id",
          "properties": {
            "description": "A unique identifier for each customer in the data model.",
            "displayName": "Id"
          }
        },
        {
          "name": "State",
          "type": "VARCHAR",
          "isCalculated": 0,
          "notNull": 0,
          "expression": "",
          "properties": {
            "description": "A field indicating the state where the customer is located.",
            "displayName": "State"
          }
        },
        {
          "name": "orders",
          "type": "orders",
          "properties": null,
          "relationship": "CustomersOrders",
          "isCalculated": false,
          "notNull": false
        }
      ],
      "refSql": "select * from main.customers",
      "cached": 0,
      "refreshTime": null,
      "properties": {
        "schema": "main",
        "catalog": "memory",
        "description": "A table of customers who have made purchases, including their city",
        "displayName": "customers"
      },
      "primaryKey": "Id"
    },
    {
      "name": "order_items",
      "columns": [
        {
          "name": "FreightValue",
          "type": "DOUBLE",
          "isCalculated": 0,
          "notNull": 0,
          "expression": "",
          "properties": {
            "description": "A numerical value representing the cost of shipping for an item in an order.",
            "displayName": "FreightValue"
          }
        },
        {
          "name": "ItemNumber",
          "type": "BIGINT",
          "isCalculated": 0,
          "notNull": 0,
          "expression": "",
          "properties": {
            "description": "The sequential number of the order item in this order. Each order item in an order has its unique ItemNumber.",
            "displayName": "ItemNumber"
          }
        },
        {
          "name": "OrderId",
          "type": "VARCHAR",
          "isCalculated": 0,
          "notNull": 0,
          "expression": "",
          "properties": {
            "description": "A VARCHAR value indicating the order that this order_item belongs to. The column is used to map the order_item to Orders model in the OrdersOrder_items relationship.",
            "displayName": "OrderId"
          }
        },
        {
          "name": "Price",
          "type": "DOUBLE",
          "isCalculated": 0,
          "notNull": 0,
          "expression": "",
          "properties": {
            "description": "this represents the sales volume",
            "displayName": "Sales"
          }
        },
        {
          "name": "ProductId",
          "type": "VARCHAR",
          "isCalculated": 0,
          "notNull": 0,
          "expression": "",
          "properties": {
            "description": "A VARCHAR value representing the product of this order_item. The column is used to map the order_item to Products model using ProductsOrder_items relationship.",
            "displayName": "ProductId"
          }
        },
        {
          "name": "ShippingLimitDate",
          "type": "DATE",
          "isCalculated": 0,
          "notNull": 0,
          "expression": "",
          "properties": {
            "description": "A date value indicating the limit by which an item should be shipped according to the order. It helps track the deadline for shipping items in the \"order_items\" model.",
            "displayName": "ShippingLimitDate"
          }
        },
        {
          "name": "orders",
          "type": "orders",
          "properties": null,
          "relationship": "OrdersOrder_items",
          "isCalculated": false,
          "notNull": false
        },
        {
          "name": "products",
          "type": "products",
          "properties": null,
          "relationship": "ProductsOrder_items",
          "isCalculated": false,
          "notNull": false
        }
      ],
      "refSql": "select * from main.order_items",
      "cached": 0,
      "refreshTime": null,
      "properties": {
        "schema": "main",
        "catalog": "memory",
        "description": "The model is used to store information about items in orders, including details like prices, product IDs, shipping limits, and relationships with orders and products tables.",
        "displayName": "order_items"
      },
      "primaryKey": "Id"
    },
    {
      "name": "orders",
      "columns": [
        {
          "name": "ApprovedTimestamp",
          "type": "TIMESTAMP",
          "isCalculated": 0,
          "notNull": 0,
          "expression": "",
          "properties": {
            "description": "A column that represents the timestamp when the order was approved.",
            "displayName": "ApprovedTimestamp"
          }
        },
        {
          "name": "CustomerId",
          "type": "VARCHAR",
          "isCalculated": 0,
          "notNull": 0,
          "expression": "",
          "properties": {
            "description": "A unique identifier representing the customer who purchased this order.",
            "displayName": "CustomerId"
          }
        },
        {
          "name": "DeliveredCarrierDate",
          "type": "DATE",
          "isCalculated": 0,
          "notNull": 0,
          "expression": "",
          "properties": {
            "description": "A column that represents the date when the order was delivered by the carrier.",
            "displayName": "DeliveredCarrierDate"
          }
        },
        {
          "name": "DeliveredCustomerDate",
          "type": "DATE",
          "isCalculated": 0,
          "notNull": 0,
          "expression": "",
          "properties": {
            "description": "A column that represents the date when the order was delivered to the customer.",
            "displayName": "DeliveredCustomerDate"
          }
        },
        {
          "name": "EstimatedDeliveryDate",
          "type": "DATE",
          "isCalculated": 0,
          "notNull": 0,
          "expression": "",
          "properties": {
            "description": "A column that represents the estimated delivery date of the order.",
            "displayName": "EstimatedDeliveryDate"
          }
        },
        {
          "name": "OrderId",
          "type": "VARCHAR",
          "isCalculated": 0,
          "notNull": 0,
          "expression": "",
          "properties": {
            "description": "A column that represents a unique identifier of this order.",
            "displayName": "OrderId"
          }
        },
        {
          "name": "PurchaseTimestamp",
          "type": "TIMESTAMP",
          "isCalculated": 0,
          "notNull": 0,
          "expression": "",
          "properties": {
            "description": "A column that represents the timestamp when the order was purchased.",
            "displayName": "PurchaseTimestamp"
          }
        },
        {
          "name": "Status",
          "type": "VARCHAR",
          "isCalculated": 0,
          "notNull": 0,
          "expression": "",
          "properties": {
            "description": "A column representing the status of the order.",
            "displayName": "Status"
          }
        },
        {
          "name": "customers",
          "type": "customers",
          "properties": null,
          "relationship": "CustomersOrders",
          "isCalculated": false,
          "notNull": false
        },
        {
          "name": "order_items",
          "type": "order_items",
          "properties": null,
          "relationship": "OrdersOrder_items",
          "isCalculated": false,
          "notNull": false
        },
        {
          "name": "reviews",
          "type": "reviews",
          "properties": null,
          "relationship": "OrdersReviews",
          "isCalculated": false,
          "notNull": false
        },
        {
          "name": "payments",
          "type": "payments",
          "properties": null,
          "relationship": "PaymentsOrders",
          "isCalculated": false,
          "notNull": false
        },
        {
          "name": "RevenueA",
          "type": "DOUBLE",
          "isCalculated": true,
          "expression": "SUM(order_items.\"Price\")",
          "notNull": 0,
          "properties": {
            "description": ""
          }
        },
        {
          "name": "Sales",
          "type": "DOUBLE",
          "isCalculated": true,
          "expression": "SUM(payments.\"Value\")",
          "notNull": 0,
          "properties": {
            "description": ""
          }
        }
      ],
      "refSql": "select * from main.orders",
      "cached": 0,
      "refreshTime": null,
      "properties": {
        "schema": "main",
        "catalog": "memory",
        "description": "A model representing the orders data.",
        "displayName": "orders"
      },
      "primaryKey": "OrderId"
    },
    {
      "name": "payments",
      "columns": [
        {
          "name": "Installments",
          "type": "BIGINT",
          "isCalculated": 0,
          "notNull": 0,
          "expression": "",
          "properties": {
            "description": "A column representing the number of installments in the payments data model.",
            "displayName": "Installments"
          }
        },
        {
          "name": "OrderId",
          "type": "VARCHAR",
          "isCalculated": 0,
          "notNull": 0,
          "expression": "",
          "properties": {
            "description": "A column representing the order id associated with this payment. The column is used to map the payment to the order using PaymentsOrders relationship.",
            "displayName": "OrderId"
          }
        },
        {
          "name": "Sequential",
          "type": "BIGINT",
          "isCalculated": 0,
          "notNull": 0,
          "expression": "",
          "properties": {
            "description": "A column representing the sequential number of the payment in its corresponding order. Each payment in the order has its unique sequential number.",
            "displayName": "Sequential"
          }
        },
        {
          "name": "Type",
          "type": "VARCHAR",
          "isCalculated": 0,
          "notNull": 0,
          "expression": "",
          "properties": {
            "description": "this represents the payment method",
            "displayName": "Payment type"
          }
        },
        {
          "name": "Value",
          "type": "DOUBLE",
          "isCalculated": 0,
          "notNull": 0,
          "expression": "",
          "properties": {
            "description": "this represents the actual amount paid by customers",
            "displayName": "Payment amountB"
          }
        },
        {
          "name": "orders",
          "type": "orders",
          "properties": null,
          "relationship": "PaymentsOrders",
          "isCalculated": false,
          "notNull": false
        }
      ],
      "refSql": "select * from main.payments",
      "cached": 0,
      "refreshTime": null,
      "properties": {
        "schema": "main",
        "catalog": "memory",
        "description": "A model representing the payment records, including installments, order IDs, sequential numbers, payment types, values, and relationships with orders.",
        "displayName": "transactions"
      },
      "primaryKey": "Id"
    },
    {
      "name": "products",
      "columns": [
        {
          "name": "Category",
          "type": "VARCHAR",
          "isCalculated": 0,
          "notNull": 0,
          "expression": "",
          "properties": {
            "description": "A category that classifies the products in the data model.",
            "displayName": "Category"
          }
        },
        {
          "name": "Id",
          "type": "VARCHAR",
          "isCalculated": 0,
          "notNull": 0,
          "expression": "",
          "properties": {
            "description": "A unique identifier assigned to each product in the data model.",
            "displayName": "Id"
          }
        },
        {
          "name": "Name",
          "type": "VARCHAR",
          "isCalculated": 0,
          "notNull": 0,
          "expression": "",
          "properties": {
            "description": "A name of the product in the data model.",
            "displayName": "Name"
          }
        },
        {
          "name": "order_items",
          "type": "order_items",
          "properties": null,
          "relationship": "ProductsOrder_items",
          "isCalculated": false,
          "notNull": false
        }
      ],
      "refSql": "select * from main.products",
      "cached": 0,
      "refreshTime": null,
      "properties": {
        "schema": "main",
        "catalog": "memory",
        "description": "A data model containing information about products such as category, ID, and name, with a relationship to order items.",
        "displayName": "products"
      },
      "primaryKey": "Id"
    },
    {
      "name": "reviews",
      "columns": [
        {
          "name": "AnswerTimestamp",
          "type": "TIMESTAMP",
          "isCalculated": 0,
          "notNull": 0,
          "expression": "",
          "properties": {
            "description": "The date when the answer was provided.",
            "displayName": "AnswerTimestamp"
          }
        },
        {
          "name": "CreationTimestamp",
          "type": "TIMESTAMP",
          "isCalculated": 0,
          "notNull": 0,
          "expression": "",
          "properties": {
            "description": "The date when the review was created.",
            "displayName": "CreationTimestamp"
          }
        },
        {
          "name": "Id",
          "type": "VARCHAR",
          "isCalculated": 0,
          "notNull": 0,
          "expression": "",
          "properties": {
            "description": "A unique identifier assigned to each review entry.",
            "displayName": "Id"
          }
        },
        {
          "name": "OrderId",
          "type": "VARCHAR",
          "isCalculated": 0,
          "notNull": 0,
          "expression": "",
          "properties": {
            "description": "The order id of the order which the review belongs to.",
            "displayName": "OrderId"
          }
        },
        {
          "name": "Score",
          "type": "BIGINT",
          "isCalculated": 0,
          "notNull": 0,
          "expression": "",
          "properties": {
            "description": "The score associated with each review entry.",
            "displayName": "Score"
          }
        },
        {
          "name": "orders",
          "type": "orders",
          "properties": null,
          "relationship": "OrdersReviews",
          "isCalculated": false,
          "notNull": false
        }
      ],
      "refSql": "select * from main.reviews",
      "cached": 0,
      "refreshTime": null,
      "properties": {
        "schema": "main",
        "catalog": "memory",
        "description": "A model containing information about review of orders.",
        "displayName": "reviews"
      },
      "primaryKey": "Id"
    }
  ],
  "relationships": [
    {
      "name": "CustomersOrders",
      "models": [
        "customers",
        "orders"
      ],
      "joinType": "ONE_TO_MANY",
      "condition": "\"customers\".Id = \"orders\".CustomerId",
      "properties": {}
    },
    {
      "name": "OrdersOrder_items",
      "models": [
        "orders",
        "order_items"
      ],
      "joinType": "ONE_TO_MANY",
      "condition": "\"orders\".OrderId = \"order_items\".OrderId",
      "properties": {}
    },
    {
      "name": "ProductsOrder_items",
      "models": [
        "products",
        "order_items"
      ],
      "joinType": "ONE_TO_MANY",
      "condition": "\"products\".Id = \"order_items\".ProductId",
      "properties": {}
    },
    {
      "name": "OrdersReviews",
      "models": [
        "orders",
        "reviews"
      ],
      "joinType": "ONE_TO_MANY",
      "condition": "\"orders\".OrderId = \"reviews\".OrderId",
      "properties": {}
    },
    {
      "name": "PaymentsOrders",
      "models": [
        "payments",
        "orders"
      ],
      "joinType": "MANY_TO_ONE",
      "condition": "\"payments\".OrderId = \"orders\".OrderId",
      "properties": {}
    }
  ],
  "views": []
}