semantic model in dbt
-- models/sales_aggregated.sql
WITH sales_data AS (
SELECT
s.order_id,
s.customer_id,
c.customer_name,
c.customer_region,
s.product_id,
p.product_name,
p.product_category,
cast(s.order_date as date) as order_date,
s.quantity,
s.price,
s.total_sales
FROM {{ ref('sales_m') }} s
JOIN {{ ref('customer1_m') }} c ON s.customer_id = c.customer_id
JOIN {{ ref('products1_m') }} p ON s.product_id = p.product_id
)
SELECT * FROM sales_data
----------------------------------------------------------------------
--metricflow_time_spine
{{
config(
materialized = 'table',
)
}}
with days as (
{{
dbt_utils.date_spine(
'day',
"to_date('01/01/2000','mm/dd/yyyy')",
"to_date('01/01/2027','mm/dd/yyyy')"
)
}}
),
final as (
select cast(date_day as date) as date_day
from days
)
select * from final
-------------------------------------------------------------
#schema3.yml
version: 2
models:
name: sales_aggregated3
description: "Aggregated sales data with customer and product information"
columns:
name: order_id
description: "Unique identifier for the order"
name: customer_id
description: "Unique identifier for the customer"
name: customer_name
description: "Name of the customer"
name: customer_region
description: "Region of the customer"
name: product_id
description: "Unique identifier for the product"
name: product_name
description: "Name of the product"
name: product_category
description: "Category of the product"
name: order_date
description: "Date when the order was placed"
name: quantity
description: "Quantity of products ordered"
name: price
description: "Price of the product"
name: total_sales
description: "Total sales amount"
semantic_models:
name: sales_semantic_model3
description: "Semantic model for sales data"
model: ref('sales_aggregated3')
defaults:
agg_time_dimension: order_date
entities:
name: sales_m
description: "The dimension entity representing orders"
type: primary
expr: order_id
name: customer1_m
description: "The dimension entity representing customers"
type: foreign
expr: customer_id
name: products1_m
description: "The dimension entity representing products"
type: foreign
expr: product_id
measures:
name: total_sales
label: "Total Sales"
description: "The total sales amount"
agg: sum
name: total_orders
label: "Total Orders"
description: "The total number of orders"
agg: count
name: xyz
label: "Total Orders"
description: "The total number of orders"
agg: count
dimensions:
name: order_date
description: "The date when the order was placed"
type: time
label: "Date of transaction"
is_partition: true
type_params:
time_granularity: day
name: product_category
description: "The category of the product"
type: categorical
name: customer_region
description: "The region of the customer"
type: categorical
-------------------------------------------------------------------------
