Expression

When describing a data source, there are often instances where we may need to rename the original columns or perform certain preprocessing tasks based on the original column. This could involve creating a common metric by applying a standardized formula or establishing a composite key that serves as a link to other models. These practices allow for greater flexibility and adaptability when working with the data, enabling us to effectively organize and analyze the information at hand.

Example

There’re a case for Lineitem. We did something here like:

• finalprice is a representation of extendedprice.
• shipmonth is an extracting of shipdate.
• order_totalprice is an associated column of Orders Object.

Model Lineitem @sql(select * from tpch_tiny.Lineitem) {
  orderkey: INTEGER
  linenumber: INTEGER
    order_field: Orders

  finalprice: DOUBLE @expr("extendedprice") -- extendedprice is the original name
  shipmonth: DATE @expr("date_trunc('month' , shipdate)")
  order_totalprice: INTEGER @expr("order_field.totalprice")
}

According the TPCH spec, Lineitem doesn’t have a unique key. To get a unique for Lineitem, we should concat orderkey and linenumber. So we can use expression to generate a composite key to be the primary key of Lineitem.

Model Lineitem @sql(select * from tpch_tiny.Lineitem) {
  orderkey: INTEGER
  linenumber: INTEGER
  orderkey_linenumber: VARCHAR @expr("concat(orderkey, linenumber") @primaryKey
}

Furthermore, an expression can be exposed to outside. Other associated models can access the defined expression through a relation field.

Model Orders @sql(select * from tpch_tiny.orders) {
  orderkey: INTEGER! @primaryKey
  custkey: INTEGER! 
  orderstatus: OrderStatus 
  totalprice: REAL
    customer: Customer
    customer_name expr("customer.name")
}

Model Lineitem @sql(select * from tpch_tiny.Lineitem) {
  orderkey: INTEGER
  linenumber: INTEGER
    order_field: Orders
    customer_name: VARCHAR @expr("order_field.customer_name")
}