Metric definitions and semantic layer in dbt - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Metric Definitions in dbt Semantic Layer

Which statement best describes the purpose of metric definitions in a dbt semantic layer?

A They define reusable calculations that can be applied consistently across reports and dashboards.

B They store raw data tables before transformation.

C They are used to schedule dbt runs automatically.

D They replace the need for SQL in data transformations.

💡 Hint

Think about how metrics help maintain consistency in analytics.

Metric definitions in dbt semantic layer specify calculations like sums, averages, or counts that can be reused. This ensures consistent results across different reports.

❓ Predict Output

intermediate

Output of a Metric Definition Query

Given the following dbt metric definition for total_sales:

metrics:
  - name: total_sales
    label: Total Sales
    model: sales
    expression: sum(amount)
    timestamp: order_date
    dimensions:
      - product_category
      - region

What would be the output of a query that calculates total_sales grouped by region for orders in 2023?

dbt

SELECT region, SUM(amount) AS total_sales
FROM sales
WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01'
GROUP BY region
ORDER BY region;

A A list of all sales transactions without aggregation.

B An error because the metric definition cannot be used in SQL queries.

C A table showing total sales by product_category instead of region.

D A table with regions as rows and their corresponding total sales amounts for 2023.

💡 Hint

Consider what the SQL query is doing with the GROUP BY clause.

The query sums the amount for each region in 2023, matching the metric definition's aggregation and grouping.

🔧 Debug

advanced

Identifying the Error in a Metric Definition

Review this metric definition snippet in a dbt semantic model:

metrics:
  - name: average_order_value
    label: Average Order Value
    model: orders
    expression: avg(total)
    timestamp: order_date
    dimensions:
      - customer_id
      - order_status

What is the likely cause of an error when running this metric?

A The timestamp field <code>order_date</code> is missing from the metric definition.

B The expression uses <code>avg(total)</code> but the column <code>total</code> does not exist in the <code>orders</code> model.

C Dimensions cannot include <code>customer_id</code> in metric definitions.

D The metric name <code>average_order_value</code> is too long and invalid.

💡 Hint

Check if the column used in the expression exists in the source model.

If the orders model does not have a total column, the aggregation avg(total) will fail.

🚀 Application

advanced

Creating a Metric for Monthly Active Users

You want to define a metric in dbt semantic layer to count unique active users per month from a user_activity model with columns user_id and activity_date. Which metric definition is correct?

A

metrics:
  - name: monthly_active_users
    label: Monthly Active Users
    model: user_activity
    expression: count_distinct(user_id)
    timestamp: activity_date
    dimensions:
      - month

B

metrics:
  - name: monthly_active_users
    label: Monthly Active Users
    model: user_activity
    expression: count(user_id)
    timestamp: activity_date
    dimensions:
      - activity_date

C

metrics:
  - name: monthly_active_users
    label: Monthly Active Users
    model: user_activity
    expression: sum(user_id)
    timestamp: activity_date
    dimensions:
      - month

D

metrics:
  - name: monthly_active_users
    label: Monthly Active Users
    model: user_activity
    expression: count(distinct user_id)
    timestamp: user_id
    dimensions:
      - month

💡 Hint

Think about counting unique users and grouping by month.

Counting distinct user_id grouped by month using activity_date as timestamp is correct. Using sum(user_id) or wrong timestamp is incorrect.

❓ data_output

expert

Result of Combining Metrics with Different Time Dimensions

Consider two metrics defined in dbt semantic layer:

metrics:
  - name: daily_revenue
    model: sales
    expression: sum(amount)
    timestamp: sale_date
    dimensions:
      - product_id

  - name: monthly_revenue
    model: sales
    expression: sum(amount)
    timestamp: sale_date
    dimensions:
      - product_id
      - month

If you query both metrics together grouped by product_id and month, what will be the expected output?

A The output will show daily_revenue broken down by day and monthly_revenue by month in separate rows.

B The query will fail because daily_revenue cannot be grouped by month dimension.

C The daily_revenue will be aggregated by month, matching monthly_revenue, showing total revenue per product per month.

D The monthly_revenue will be duplicated for each day, inflating totals.

💡 Hint

Think about how time dimensions affect aggregation levels.

When grouping by month, daily_revenue sums daily amounts into monthly totals, aligning with monthly_revenue. This allows comparison at the month level.

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