Skip to main content

Scenario 5: Building a Metrics Layer

Business Context: Your Indian e-commerce company has successfully built the fct_orders and dim_customers tables in Snowflake. However, the business analysts in the Mumbai office keep defining the "Average Order Value (AOV)" differently in Tableau, resulting in conflicting reports.

Your Objective: You have been tasked with implementing dbt's Semantic Layer (MetricFlow). You must define a semantic_model on top of your fact table, and define three standard metrics: total_revenue, total_orders, and the ratio metric average_order_value.


1. Setup the Database Environment​

(Execute this in your Snowflake worksheet to prepare the environment. Find your username by running SELECT CURRENT_USER();)

-- 1. Create a dedicated database for this scenario
CREATE DATABASE IF NOT EXISTS BEEMS_SCENARIO_5_METRICS;
USE DATABASE BEEMS_SCENARIO_5_METRICS;
CREATE SCHEMA IF NOT EXISTS MARTS;

-- 2. Create the mock fact table
CREATE OR REPLACE TABLE MARTS.fct_orders (
order_id INT,
customer_id INT,
order_date DATE,
order_amount_inr NUMBER(10,2),
is_successful BOOLEAN
);

-- 3. Insert mock data
INSERT INTO MARTS.fct_orders VALUES
(1, 101, '2023-10-01', 1500.00, TRUE),
(2, 102, '2023-10-01', 2500.00, TRUE),
(3, 101, '2023-10-02', 500.00, FALSE), -- Failed order
(4, 103, '2023-10-03', 4000.00, TRUE);

2. Defining the Semantic Model​

In your dbt project, you need to map the physical fct_orders table into a Semantic Model that MetricFlow can understand.

Create a new file: models/marts/semantic_models.yml

version: 2

semantic_models:
- name: orders
description: "Semantic model representing ecommerce orders."
model: ref('fct_orders')

# Define how to uniquely identify rows and join to other tables
entities:
- name: order_id
type: primary
- name: customer_id
type: foreign

# Define what we group by (e.g., date)
dimensions:
- name: order_date
type: time
type_params:
time_granularity: day
- name: is_successful
type: categorical

# Define the raw numerical values we can aggregate
measures:
- name: revenue
description: "The total INR amount of the order"
agg: sum
expr: order_amount_inr

- name: order_count
description: "Count of individual orders"
agg: count
expr: order_id

3. Defining the Business Metrics​

Now that MetricFlow understands the table, you define the absolute business logic in a metrics.yml file.

Create a new file: models/marts/metrics.yml

version: 2

metrics:
# Metric 1: Total Revenue (Filtered for successful orders only)
- name: total_successful_revenue
description: "Total revenue generated from successful orders."
type: simple
type_params:
measure: revenue
filter: |
{{ Dimension('order_id__is_successful') }} = true

# Metric 2: Total Order Count
- name: total_successful_orders
description: "Total number of successful orders."
type: simple
type_params:
measure: order_count
filter: |
{{ Dimension('order_id__is_successful') }} = true

# Metric 3: Average Order Value (A Ratio Metric!)
- name: average_order_value
description: "Total Successful Revenue divided by Total Successful Orders."
type: ratio
type_params:
numerator: total_successful_revenue
denominator: total_successful_orders

4. Querying the Semantic Layer​

In a real environment, Tableau or Looker would query these metrics via JDBC. But you can test them directly in the dbt CLI using the dbt sl query command!

If you were to run the following in your CLI:

# Ask MetricFlow for the AOV, grouped by day
dbt sl query --metrics average_order_value --group-by order_date__day

MetricFlow would dynamically generate and execute this raw SQL against Snowflake:

SELECT 
order_date,
SUM(CASE WHEN is_successful = TRUE THEN order_amount_inr ELSE 0 END) /
COUNT(CASE WHEN is_successful = TRUE THEN order_id ELSE NULL END) as average_order_value
FROM BEEMS_SCENARIO_5_METRICS.MARTS.fct_orders
GROUP BY 1

Result:

order_dateaverage_order_value
2023-10-012000.00
2023-10-02null
2023-10-034000.00

Summary​

You have successfully centralized business logic using the dbt Semantic Layer. The Analysts in Mumbai no longer have to write complex CASE WHEN statements in Tableau; they just ask for average_order_value and dbt handles the rest!