Metric definitions and semantic layer in dbt - Mini Project: Build & Apply

Metric definitions and semantic layer

📖 Scenario: You work as a data analyst at a retail company. Your team wants to create clear, reusable metrics for sales and customer data. These metrics will help everyone understand the business performance easily.

🎯 Goal: Build metric definitions in dbt and create a semantic layer to calculate total sales and average order value.

📋 What You'll Learn

Create a metric definition for total sales

Create a metric definition for average order value

Use a semantic layer to combine these metrics

Output the final metrics values

💡 Why This Matters

🌍 Real World

Companies use metric definitions and semantic layers to standardize business metrics so everyone reports consistent numbers.

💼 Career

Data analysts and engineers build these layers to make data trustworthy and easy to use for decision makers.

1

Create initial sales data model

Create a dbt model file called sales.sql with a table named sales that has columns order_id, customer_id, and amount. Insert these exact rows: (1, 101, 100), (2, 102, 150), (3, 101, 200).

dbt

-- Create the sales model with the specified columns and rows
-- Your code here

Need a hint?

Use a CTE with union all to create the rows inside the model.

2

Define total sales metric

Create a metric definition in metrics.yml called total_sales that sums the amount column from the sales model.

dbt

with source_data as (
    select 1 as order_id, 101 as customer_id, 100 as amount union all
    select 2, 102, 150 union all
    select 3, 101, 200
)
select * from source_data

# Add metric definition for total_sales in metrics.yml
# Your code here

Need a hint?

Define the metric with name, model, type, and sql fields.

3

Define average order value metric

Add a metric definition in metrics.yml called average_order_value that calculates the average amount per order_id from the sales model.

dbt

with source_data as (
    select 1 as order_id, 101 as customer_id, 100 as amount union all
    select 2, 102, 150 union all
    select 3, 101, 200
)
select * from source_data

# metrics.yml
metrics:
  - name: total_sales
    model: ref('sales')
    label: Total Sales
    description: Sum of sales amount
    type: sum
    sql: amount

# Add metric definition for average_order_value below
# Your code here

Need a hint?

Use type: average and add group_by: order_id to calculate average per order.

4

Query metrics from semantic layer

Write a dbt model called metrics_summary.sql that selects the total_sales and average_order_value metrics from the semantic layer and prints their values.

dbt

with source_data as (
    select 1 as order_id, 101 as customer_id, 100 as amount union all
    select 2, 102, 150 union all
    select 3, 101, 200
)
select * from source_data

# metrics.yml
metrics:
  - name: total_sales
    model: ref('sales')
    label: Total Sales
    description: Sum of sales amount
    type: sum
    sql: amount
  - name: average_order_value
    model: ref('sales')
    label: Average Order Value
    description: Average amount per order
    type: average
    sql: amount
    group_by: order_id

-- Create metrics_summary.sql to select total_sales and average_order_value
-- Your code here

Need a hint?

Use sum(amount) and avg(amount) in your select statement from the sales model.