Skip to main content

Creating the stg_order_summaries Model

This model creates order summaries by customer_id. It features 3 aggregations: first_order, last_order, and number_of_orders.

Specifying model dependencies​

This model is a dependent of the dev_orders model. To create a new model, we'll right-click on the no-code folder in the left sidebar and select New Model. For Heirarchy, we'll select Child Of, rather than Root model and specify this mode's dependencies: dev_orders.

img stg_order_summaries New Model

Setting up the model​

Let's set up the following to create the new model:

  • Source Schema: dev
  • Source Table: dev_orders
  • Destination Schema: stg
  • Destination Table: stg_order_summaries

Clicking on Apply will create the model. We're now ready to specify the transformations.

Setting up the transformations​

For this model, we'll add 3 aggregation columns and also sort one column.

Setting up the aggregation columns​

To get started, click on the Aggregator button of the data model. This will bring up the aggregation dialog.

img stg_order_summaries Aggregation

Specify the customer_id in the Group By option and then click on Add Aggregator. Now, let's add the three aggregation columns:

Setting up the first_order column​

Click on Add Aggregator and enter the following values:

  • Function: MIN
  • Column: order_date
  • Alias: first_order

Setting up the last_order column​

Click on Add Aggregator and enter the following values:

  • Function: MAX
  • Column: order_date
  • Alias: last_order

Setting up the number_of_orders column​

Click on Add Aggregator and enter the following values:

  • Function: COUNT
  • Column: order_id
  • Alias: number_of_orders

Now, click on Save to save the aggregation column. You should now see the aggregated data in the data grid.

Setting up the sort column​

Let's sort the data by customer_id.

Click on the Sort button of the data model. This will bring up the sort dialog.

img stg_order_summaries Sort Dialog

Now, enter the following values:

  • Sort By: customer_id
  • Order: Ascending

Now, click on Sort to save the sort column. You should now see that the aggregated data is sorted by customer_id.

The final model​

Let's look at the final model.

img stg_order_summaries Final