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

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

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

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

Hint

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

Practice

1. What is the main purpose of defining metrics in dbt's semantic layer?

easy

A. To standardize how key numbers are calculated and used across reports

B. To create visual dashboards automatically

C. To store raw data from different sources

D. To write SQL queries faster

Solution

1. Step 1: Understand metric definitions

   Metric definitions specify how to calculate important numbers like sales or counts.

2. Step 2: Connect metric definitions to semantic layer purpose

   The semantic layer uses these definitions to ensure everyone uses the same calculations, keeping reports consistent.

3. Final Answer:

   To standardize how key numbers are calculated and used across reports -> Option A

4. Quick Check:

   Metric definitions = Standard calculations [OK]

Hint: Metrics = consistent numbers everyone trusts [OK]

Common Mistakes:

• Thinking metrics create dashboards automatically
• Confusing metrics with raw data storage
• Believing metrics speed up SQL writing

2. Which of the following is the correct syntax to define a simple count metric in a dbt metric YAML file?

easy

A. metrics:
- name: user_count
model: 'users'
label: 'User Count'
type: sum

B. metrics:
- name: user_count
model: ref('users')
label: 'User Count'
type: sum

C. metrics:
- name: user_count
model: ref('users')
label: 'User Count'
type: count

D. metrics:
- name: user_count
model: ref('users')
label: 'User Count'
type: average

Solution

1. Step 1: Identify correct metric type for counting

   The metric type for counting rows is 'count', not 'sum' or 'average'.

2. Step 2: Check model reference syntax

   Using ref('users') is correct to link the metric to the 'users' model.

3. Final Answer:

   metrics: - name: user_count model: ref('users') label: 'User Count' type: count -> Option C

4. Quick Check:

   Count metric uses type 'count' and ref() [OK]

Hint: Count metrics use type 'count' and model with ref() [OK]

Common Mistakes:

• Using 'sum' or 'average' instead of 'count' for counting
• Not using ref() for model reference
• Incorrect indentation or YAML structure

3. Given this metric definition in dbt YAML:

metrics:
  - name: total_revenue
    model: ref('orders')
    label: 'Total Revenue'
    type: sum
    sql: amount

What will be the output of querying this metric if the 'orders' table has amounts: [100, 200, 300]?

medium

A. 600

B. 3

C. 100

D. 900

Solution

1. Step 1: Understand metric type and SQL field

   The metric type is 'sum' on the 'amount' field, so it adds all amounts.

2. Step 2: Calculate sum of amounts

   Sum of [100, 200, 300] = 100 + 200 + 300 = 600.

3. Final Answer:

   600 -> Option A

4. Quick Check:

   Sum amounts = 600 [OK]

Hint: Sum metric adds all values in the specified column [OK]

Common Mistakes:

• Confusing sum with count (3 rows)
• Picking a single value instead of sum
• Adding incorrectly

4. You wrote this metric definition in dbt YAML:

metrics:
  - name: avg_order_value
    model: ref('orders')
    label: 'Average Order Value'
    type: avg
    sql: total_price

But when you run your report, the average is incorrect. What is the likely error?

medium

A. The metric type 'average' is invalid; it should be 'avg'

B. The metric type should be 'average' instead of 'avg'

C. The model reference should not use ref()

D. The 'sql' field should be 'amount' instead of 'total_price'

Solution

1. Step 1: Check valid metric types in dbt

   dbt metric types include 'average' for average, not 'avg'.

2. Step 2: Identify the impact of wrong type

   Using 'avg' is invalid and causes incorrect calculation or errors.

3. Final Answer:

   The metric type should be 'average' instead of 'avg' -> Option B

4. Quick Check:

   Use 'average' for average metric type [OK]

Hint: Use 'average' not 'avg' for average metrics [OK]

Common Mistakes:

• Using 'avg' instead of 'average' as metric type
• Changing model reference incorrectly
• Assuming 'sql' field name is wrong

5. You want to create a metric that calculates the percentage of active users out of total users using dbt's semantic layer. Which approach correctly defines this metric?

hard

A. Create a metric with type 'sum' on a boolean field active

B. Define a single metric with type 'percentage' and sql 'active = true / total users'

C. Define a metric with type 'count' and sql 'active / total' directly in one metric

D. Define two separate metrics: one for active_user_count (type: count with filter active=true) and one for total_user_count (type: count), then create a derived metric dividing active_user_count by total_user_count

Solution

1. Step 1: Understand metric composition in dbt semantic layer

   Complex metrics like percentages are built by combining simpler metrics, not by writing raw SQL in one metric.

2. Step 2: Define filtered and total counts separately

   Define active_user_count with a filter for active=true, and total_user_count without filter.

3. Step 3: Create derived metric for percentage

   Use a derived metric that divides active_user_count by total_user_count to get the percentage.

4. Final Answer:

   Define two separate metrics: one for active_user_count (type: count with filter active=true) and one for total_user_count (type: count), then create a derived metric dividing active_user_count by total_user_count -> Option D

5. Quick Check:

   Build complex metrics by combining simple filtered metrics [OK]

Hint: Build percentages by combining filtered count metrics [OK]

Common Mistakes:

• Trying to write raw SQL for percentage in one metric
• Using invalid metric types like 'percentage'
• Summing boolean fields without filtering