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.