Metric definitions and semantic layer in dbt

Introduction

Metric definitions help you clearly describe how to measure things in your data. A semantic layer makes these definitions easy to use and understand for everyone.

When you want to track sales numbers like total revenue or average order value.

When different teams need to use the same definitions for metrics to avoid confusion.

When you want to build dashboards that update automatically with correct numbers.

When you want to make your data easier to explore without writing complex code.

When you want to keep your business rules consistent across reports.

Syntax

dbt

metrics:
  - name: total_revenue
    label: "Total Revenue"
    model: ref('orders')
    calculation_method: sum
    expression: amount
    timestamp: order_date
    dimensions:
      - product_category
      - region

The metrics block defines one or more metrics.

model points to the data source, calculation_method tells how to calculate, and expression is the field used.

Examples

This metric counts the number of orders.

dbt

metrics:
  - name: total_orders
    label: "Total Orders"
    model: ref('orders')
    calculation_method: count
    expression: order_id
    timestamp: order_date

This metric calculates the average amount per order.

dbt

metrics:
  - name: average_order_value
    label: "Average Order Value"
    model: ref('orders')
    calculation_method: average
    expression: amount
    timestamp: order_date

This metric sums revenue grouped by product category.

dbt

metrics:
  - name: total_revenue_by_category
    label: "Total Revenue by Category"
    model: ref('orders')
    calculation_method: sum
    expression: amount
    timestamp: order_date
    dimensions:
      - product_category

Sample Program

This example defines a metric for total revenue by product category. Then it shows SQL to calculate it from the orders table.

dbt

metrics:
  - name: total_revenue
    label: "Total Revenue"
    model: ref('orders')
    calculation_method: sum
    expression: amount
    timestamp: order_date
    dimensions:
      - product_category


-- Example SQL to use the metric in a dbt model
select
  product_category,
  sum(amount) as total_revenue
from {{ ref('orders') }}
group by product_category
order by total_revenue desc;

Important Notes

Always use clear names and labels for metrics so everyone understands them.

Dimensions let you break down metrics by categories like region or product type.

The semantic layer helps keep metric logic in one place, avoiding mistakes in reports.

Summary

Metric definitions describe how to measure key numbers in your data.

The semantic layer makes metrics easy to find and use across your team.

Using metrics consistently helps keep reports accurate and trustworthy.

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