jaffle-sl-testing/models/marts/orders.yml at 4a94d88653942ca9b94562d41fa0efecb6fa6b79 · dbt-labs/jaffle-sl-testing · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
models:
  - name: orders
    description: Order overview data mart, offering key details for each order inlcluding if it's a customer's first order and a food vs. drink item breakdown. One row per order.
    columns:
      - name: order_id
        description: The unique key of the orders mart.
        tests:
          - not_null
          - unique
      - name: customer_id
        description: The foreign key relating to the customer who placed the order.
        tests:
          - relationships:
              to: ref('stg_customers')
              field: customer_id
      - name: location_id
        description: The foreign key relating to the location the order was placed at.
      - name: order_total
        description: The total amount of the order in USD including tax.
      - name: ordered_at
        description: The timestamp the order was placed at.
      - name: count_food_items
        description: The number of individual food items ordered.
      - name: count_drink_items
        description: The number of individual drink items ordered.
      - name: count_items
        description: The total number of both food and drink items ordered.
      - name: subtotal_food_items
        description: The sum of all the food item prices without tax.
      - name: subtotal_drink_items
        description: The sum of all the drink item prices without tax.
      - name: subtotal
        description: The sum total of both food and drink item prices without tax.
      - name: order_cost
        description: The sum of supply expenses to fulfill the order.
      - name: location_name
        description: The full location name of where this order was placed. Denormalized from `stg_locations`.
      - name: is_first_order
        description: A boolean indicating if this order is from a new customer placing their first order.
      - name: is_food_order
        description: A boolean indicating if this order included any food items.
      - name: is_drink_order
        description: A boolean indicating if this order included any drink items.
semantic_models:
  #The name of the semantic model.
  - name: orders
    defaults:
      agg_time_dimension: ordered_at
    description: |
      Order fact table. This table is at the order grain with one row per order.
    #The name of the dbt model and schema
    model: ref('orders')
    #Entities. These usually corespond to keys in the table.
    entities:
      - name: order_id
        type: primary
      - name: location
        type: foreign
        expr: location_id
      - name: customer
        type: foreign
        expr: customer_id
    #Measures. These are the aggregations on the columns in the table.
    measures:
      - name: order_total
        description: The total revenue for each order.
        agg: sum
      - name: order_count
        expr: 1
        agg: sum
      - name: tax_paid
        description: The toal tax paid on each order.
        agg: sum
      - name: customers_with_orders
        description: Distinct count of customers placing orders
        agg: count_distinct
        expr: customer_id
      - name: locations_with_orders
        description: Distinct count of locations with order
        expr: location_id
        agg: count_distinct
      - name: order_cost
        description: The cost for each order item. Cost is calculated as a sum of the supply cost for each order item.
        agg: sum
    #Dimensions. Either categorical or time. These add additonal context to metrics. The typical querying pattern is Metric by Dimension.
    dimensions:
      - name: ordered_at
        type: time
        type_params:
          time_granularity: day
      - name: ordered_at_date
        type: time
        expr: date_trunc('day', ordered_at)::DATE
        type_params:
          time_granularity: day
      - name: order_total_dim
        type: categorical
        expr: order_total
      - name: is_food_order
        type: categorical
      - name: is_drink_order
        type: categorical
metrics:
  - name: order_total
    description: Sum of total order amonunt. Includes tax + revenue.
    type: simple
    label: Order Total
    type_params:
      measure: order_total