Metric definitions and semantic layer in dbt - Step-by-Step Execution

Concept Flow - Metric definitions and semantic layer

Define Metrics

↓

Add Semantic Layer

↓

Use Metrics in Models/Reports

↓

Metrics Calculated Consistently

↓

Results for Analysis/Visualization

Start by defining metrics, then add a semantic layer to organize them. Use these metrics in models or reports to get consistent results for analysis.

Execution Sample

dbt

metrics:
  - name: total_revenue
    label: Total Revenue
    type: sum
    sql: amount

semantic_layer:
  metrics:
    - total_revenue

Defines a metric 'total_revenue' as sum of 'amount' and adds it to the semantic layer for consistent use.

Execution Table

Step	Action	Input	Output	Notes
1	Define metric 'total_revenue'	amount column	Metric object with sum aggregation	Metric ready for use
2	Add metric to semantic layer	Metric object	Semantic layer includes 'total_revenue'	Organizes metrics centrally
3	Use metric in model/report	Semantic layer metric	Query uses sum(amount) as total_revenue	Consistent calculation
4	Run query	SQL with metric	Result with total revenue value	Output for analysis
5	End	N/A	N/A	Process complete

💡 All steps completed, metric defined, added to semantic layer, used in query, and result produced.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
metric_definition	None	{name: total_revenue, type: sum, sql: amount}	{name: total_revenue, type: sum, sql: amount}	{name: total_revenue, type: sum, sql: amount}	{name: total_revenue, type: sum, sql: amount}
semantic_layer	Empty	Empty	Includes total_revenue metric	Includes total_revenue metric	Includes total_revenue metric
query	None	None	None	SQL using sum(amount) as total_revenue	SQL executed, result returned
result	None	None	None	None	Numeric total revenue value

Key Moments - 2 Insights

Why do we add metrics to a semantic layer instead of using raw SQL everywhere?

Adding metrics to a semantic layer centralizes definitions, so all reports use the same calculation. See execution_table step 2 and 3 where the metric is added and then used consistently.

What happens if the metric SQL changes after adding it to the semantic layer?

The semantic layer updates the metric definition, so all queries using it automatically use the new calculation. This is shown in variable_tracker where metric_definition stays consistent and query uses updated SQL.

Visual Quiz - 3 Questions

Look at the execution_table at step 3, what is the query using?

A Raw column 'amount' without aggregation

B Sum of 'amount' as total_revenue

C Count of rows

D Average of 'amount'

💡 Hint

Check the Output column in step 3 of execution_table showing 'Query uses sum(amount) as total_revenue'

According to variable_tracker, what is the state of semantic_layer after step 2?

A Includes total_revenue metric

B Includes multiple metrics

C Empty, no metrics added

D Undefined

💡 Hint

Look at semantic_layer row under After Step 2 in variable_tracker

If the metric SQL changed after step 1, how would the final query output change?

A The semantic layer would ignore the change

B The query output would remain the same

C The query output would reflect the new metric calculation

D The query would fail

💡 Hint

Refer to key_moments explanation about metric SQL changes affecting all queries

Concept Snapshot

Metric definitions specify how to calculate key numbers (e.g., sum of sales).
Semantic layer stores these metrics centrally.
Use metrics from semantic layer in models and reports.
This ensures consistent, reusable calculations.
Changing metric SQL updates all uses automatically.

Full Transcript

This visual execution shows how metric definitions and semantic layers work in dbt. First, a metric like total_revenue is defined as the sum of the amount column. Then, this metric is added to the semantic layer, which organizes metrics centrally. When building models or reports, the metric from the semantic layer is used, ensuring consistent calculations. Running the query produces the total revenue value. Variables like metric_definition and semantic_layer change state as steps progress. Key moments clarify why semantic layers centralize metrics and how changes propagate. The quiz tests understanding of query usage, semantic layer state, and metric updates.

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