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.