Example from Cube with SQL Expressions:
1
2
3
4
5
6
7
8
9
10
11
12
13
|
cubes:
- name: orders
sql_table: orders
measures:
- name: statuses
sql: "STRING_AGG(status)"
type: string
dimensions:
- name: status
sql: "UPPER(status)"
type: string
|
Or more specifically designing Metrics:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
views:
- name: orders_view
cubes:
- join_path: orders
includes:
- status
- created_at
- completed_count
- count
- total_amount
- average_order_value
- join_path: orders.users
prefix: true
includes:
- city
- age
- gender
|
Example from
Cube Docs
# dbt semantic layer
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
semantic_models:
- name: the_name_of_the_semantic_model ## Required
description: same as always ## Optional
model: ref('some_model') ## Required
defaults: ## Required
agg_time_dimension: dimension_name ## Required if the model contains measures
entities: ## Required
- see more information in entities
measures: ## Optional
- see more information in the measures section
dimensions: ## Required
- see more information in the dimensions section
primary_entity: >-
if the semantic model has no primary entity, then this property is required. #Optional if a primary entity exists, otherwise Required
|
Find all the properties and it’s use in
Semantic models | dbt Developer Hub.
# YAML vs. SQL comparison
dbt Semantic Layer, we can see tabs, use MetricFlow to define a semantic model that uses order_total as a metric and a sample schema to create consistent and accurate results which eliminating confusion, code duplication, and streamlining your workflow - example from
dbt Developer Hub:
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
|
semantic_models:
- name: orders # The name of the semantic model
description: |
A model containing order data. The grain of the table is the order id.
model: ref('orders') #The name of the dbt model and schema
defaults:
agg_time_dimension: metric_time
entities: # Entities, which usually correspond to keys in the table.
- name: order_id
type: primary
- name: customer
type: foreign
expr: customer_id
measures: # Measures, which are the aggregations on the columns in the table.
- name: order_total
agg: sum
dimensions: # Dimensions are either categorical or time. They add additional context to metrics and the typical querying pattern is Metric by Dimension.
- name: metric_time
expr: cast(ordered_at as date)
type: time
type_params:
time_granularity: day
- name: customers # The name of the second semantic model
description: >
Customer dimension table. The grain of the table is one row per
customer.
model: ref('customers') #The name of the dbt model and schema
defaults:
agg_time_dimension: first_ordered_at
entities: # Entities, which usually correspond to keys in the table.
- name: customer
type: primary
expr: customer_id
dimensions: # Dimensions are either categorical or time. They add additional context to metrics and the typical querying pattern is Metric by Dimension.
- name: is_new_customer
type: categorical
expr: case when first_ordered_at is not null then true else false end
- name: first_ordered_at
type: time
type_params:
time_granularity: day
|
where as the SQL looks something like this
1
2
3
4
5
6
7
8
|
select
date_trunc('day',orders.ordered_at) as day,
case when customers.first_ordered_at is not null then true else false end as is_new_customer,
sum(orders.order_total) as order_total
from orders
left join customers
on orders.customer_id = customers.customer_id
group by 1, 2
|
See also related Metrics SQL.
Origin: Semantic Layer
