Tutorial: build a metric view with joins and data modeling

In this tutorial, you build a sales analytics metric view on the TPC-H dataset. By the end, you'll have a metric view that:

• Joins orders and customers across multiple tables using a snowflake schema.
• Defines fields (also called dimensions) for time, geography, and order attributes.
• Calculates simple and complex measures including ratios, filtered aggregations, and window measures.
• Uses composability to build complex metrics from simpler measures.
• Defines a parameter to apply a discount rate at query time.
• Includes agent metadata for dashboards and AI tools.

If you're new to metric views, start with Create a metric view to learn the basics. This tutorial extends that foundation with real-world complexity.

Requirements

To complete this tutorial, you must have:

• A workspace enabled for Unity Catalog.
• A SQL warehouse or compute resource running Databricks Runtime 17.3 or above.

For the full list of privileges required to create a metric view, see Prerequisites.

The data model

The TPC-H dataset models a wholesale supply chain. This tutorial uses three tables joined in a snowflake schema:

• orders joins to customer on o_custkey = c_custkey
• customer joins to nation on c_nationkey = n_nationkey

Table	Role	Key columns
orders	Fact table (order transactions)	o_orderkey, o_custkey, o_totalprice, o_orderdate, o_orderstatus
customer	Dimension table (customer details)	c_custkey, c_name, c_mktsegment, c_nationkey
nation	Dimension table (country or region reference)	n_nationkey, n_name, n_regionkey

Step 1: Create the metric view and open the editor

You can build this metric view in the Catalog Explorer UI, generate it with Genie Code, or write the full YAML definition directly. All three methods resolve to a single YAML definition that models the metric view. In each step that follows, select the Catalog Explorer UI or YAML editor tab to follow your preferred method. If you use the YAML editor, the example code in each step is the portion of the YAML definition that corresponds to what you build in that step.

Note

The YAML examples in this tutorial use the fields keyword. When you build a metric view in the low-code editor, the YAML it generates uses the equivalent dimensions keyword instead. See Fields.

If you're unfamiliar with the UI for creating metric views, see Create a metric view.

To create the metric view, in Catalog Explorer:

1. Search for samples.tpch.orders.
2. Click the table name.
3. Click Create > Metric view and name the view.

For the detailed create steps, see Create a metric view. When the editor opens, use the UI tab to build interactively, or click the <> button to edit the YAML definition directly.

Step 2: Set up the metric view

Set a version and a description for the metric view. The version determines the YAML specification version, and the comment documents the metric view's purpose, which appears in Catalog Explorer. Azure Databricks manages the version for you.

Catalog Explorer UI

The version is defined for you. To add or edit the description after you save the metric view:

1. In Catalog Explorer, search for the metric view and click its name.
2. Click Description, then enter a description of the metric view. You can use the sample description shown in the YAML editor tab.

This text corresponds to the comment field in the YAML definition. For more ways to edit a metric view, see Edit a metric view.

YAML editor

version: 1.1

comment: |-
  Sales analytics metric view for order performance analysis.
  Joins orders with customers and geography.
  Owner: Analytics Team
  Last updated: 2025-01-15

Step 3: Define the source and joins

Define the primary source table and join related tables:

• source sets the fact table (orders) as the grain.
• joins brings in customer data using a many-to-one relationship.
• The nested nation join demonstrates a snowflake schema pattern, joining through customer to reach geographic data, where nation is a subdimension of customer.

Catalog Explorer UI

This example adds two joins, both Many-to-one, to model the snowflake schema.

To add the customer join:

1. In the editor, click Join in the upper-right corner to open the Add join dialog.
2. Search for samples.tpch.customer, click the table name, then click Add.
3. Set the join condition to o_custkey = c_custkey.
4. Under Join Cardinality, select Many-to-one. For guidance on choosing a cardinality, see Join cardinality.

Then add the nested nation join. Repeat the steps from the customer join, joining samples.tpch.nation on c_nationkey = n_nationkey. Nesting the join under customer models nation as a subdimension of customer.

For the full join dialog steps, see Step 2: Add a join.

YAML editor

source: SELECT * FROM samples.tpch.orders

joins:
  - name: customer
    source: samples.tpch.customer
    'on': o_custkey = c_custkey
    joins:
      - name: nation
        source: samples.tpch.nation
        'on': c_nationkey = n_nationkey

Step 4: Define a filter

A filter limits the source data, and it applies to all queries on the metric view. This tutorial limits the metric view to recent data.

Catalog Explorer UI

To define the filter:

1. In the editor, click Filter in the upper-right corner.
2. Use the drop-down menus to set the Column to o_orderdate, the Operator to >=, and the Value to 1995-01-01.

For more about filters, see Step 3: Define a filter.

YAML editor

filter: o_orderdate >= '1995-01-01'

Step 5: Define fields

Fields are the attributes users group and filter by. A field can be a categorical column (such as region or status) or an unaggregated numeric column (such as age or quantity) that users aggregate at query time.

Agent metadata

Each field and measure in this tutorial includes agent metadata properties that improve how your metric view works with dashboards and AI tools:

• display_name: A readable label that appears in visualizations instead of the technical column name.
• synonyms: Alternate names that help AI tools like Genie discover fields and measures through natural language queries.
• format: How values display in downstream surfaces such as dashboards, notebooks, and SQL query results, for example as currency, number, or percentage.

These properties are optional but recommended. The field and measure definitions in the following steps include them inline.

Field definitions

This tutorial adds:

• Time fields: order_date, order_month, and order_year at multiple granularities to support different analysis needs.
• Transformed fields: order_status and order_priority, which use CASE and SPLIT to convert source codes into readable labels.
• Joined fields: customer_name, market_segment, and customer_nation, which reference joined tables using the join name. Nested join columns use chained dot notation, such as customer.nation.n_name, to traverse the snowflake schema.

Catalog Explorer UI

The editor adds all source columns to the Fields tab automatically. Edit, rename, remove, and add fields so that the metric view defines exactly the following. For each field, click its name to edit it or click Add to create it, then set the expression in Builder or Custom mode. Set the Display name and Synonyms for each field as shown.

1. order_date: In Builder mode, select the o_orderdate column. Set display name to Order Date.

2. order_month: In Custom mode, enter DATE_TRUNC('MONTH', order_date). Set display name to Order Month.

3. order_year: In Custom mode, enter YEAR(order_date). Set display name to Order Year.

4. order_status: In Custom mode, enter the following expression. Set display name to Order Status and synonyms to status, fulfillment status.

   CASE o_orderstatus
     WHEN 'O' THEN 'Open'
     WHEN 'P' THEN 'Processing'
     WHEN 'F' THEN 'Fulfilled'
   END
   

5. order_priority: In Custom mode, enter SPLIT(o_orderpriority, '-')[0]. Set display name to Priority.

6. customer_name: In Builder mode, select the c_name column from the joined customer table. Set display name to Customer Name.

7. market_segment: In Builder mode, select the c_mktsegment column from the joined customer table. Set display name to Market Segment and synonyms to segment, industry.

8. customer_nation: In Custom mode, enter customer.nation.n_name to reference the nested nation join. Set display name to Country and synonyms to nation, country.

For the full field steps, see Step 4: Add fields.

YAML editor

fields:
  - name: order_date
    expr: o_orderdate
    display_name: Order Date

  - name: order_month
    expr: "DATE_TRUNC('MONTH', order_date)"
    display_name: Order Month

  - name: order_year
    expr: YEAR(order_date)
    display_name: Order Year

  - name: order_status
    expr: |-
      CASE o_orderstatus
        WHEN 'O' THEN 'Open'
        WHEN 'P' THEN 'Processing'
        WHEN 'F' THEN 'Fulfilled'
      END
    display_name: Order Status
    synonyms:
      - status
      - fulfillment status

  - name: order_priority
    expr: "SPLIT(o_orderpriority, '-')[0]"
    display_name: Priority

  - name: customer_name
    expr: customer.c_name
    display_name: Customer Name

  - name: market_segment
    expr: customer.c_mktsegment
    display_name: Market Segment
    synonyms:
      - segment
      - industry

  - name: customer_nation
    expr: customer.nation.n_name
    display_name: Country
    synonyms:
      - nation
      - country

Step 6: Define parameters

Parameters let you pass values into the metric view when you query it, so a single definition can serve many query variants. This tutorial adds a discount parameter that a later measure uses to calculate discounted revenue. The parameter has a default of 0, so queries that don't pass a value return undiscounted revenue. For more about parameters, see Use parameters with metric views.

Catalog Explorer UI

In the editor heading, click Add parameter. Enter discount as the name, then enter a default value of 0 and select the double data type.

YAML editor

parameters:
  - name: discount
    data_type: double
    default: 0

Step 7: Define measures

Measures are the calculations users want to analyze. Define atomic measures first, then use composability to build complex metrics that reference earlier-defined measures with the MEASURE() function. Set the display_name, format, and synonyms for each measure as described in Agent metadata. This tutorial adds:

• Atomic measures: order_count, total_revenue, and unique_customers, the simple aggregations that form the building blocks.
• Composed measures: avg_order_value and revenue_per_customer, which reference earlier-defined measures with MEASURE() instead of duplicating aggregation logic. If total_revenue changes, these measures automatically use the updated definition. See Composability.
• Filtered measures: open_order_revenue and fulfilled_order_revenue, which use FILTER (WHERE ...) to create conditional metrics without separate fields.
• Parameterized measure: discounted_revenue, which references the discount parameter to apply a discount rate. See Use parameters with metric views.
• Window measure: t7d_customers, which calculates a rolling 7-day count of unique customers. See Window measures for more window measure patterns.

Catalog Explorer UI

The editor adds a sample COUNT(*) measure automatically. Edit or remove it and add measures so that the metric view defines exactly the following. For each measure, click Add, then set the expression in Builder or Custom mode. Set the Display name, Format, and Synonyms as shown. Use 2 decimal places for currency formats and 0 decimal places for number formats.

1. order_count: In Builder mode, select the Count distinct aggregation on o_orderkey. Set display name to Order Count, format to Number.
2. total_revenue: In Builder mode, select the Sum aggregation on o_totalprice. Set display name to Total Revenue, format to Currency (USD), synonyms to revenue, sales.
3. discounted_revenue: In Custom mode, enter SUM(o_totalprice * (1 - discount)). Set display name to Discounted Revenue, format to Currency (USD).
4. unique_customers: In Builder mode, select the Count distinct aggregation on o_custkey. Set display name to Unique Customers, format to Number.
5. avg_order_value: In Custom mode, enter MEASURE(total_revenue) / MEASURE(order_count). Set display name to Avg Order Value, format to Currency (USD), synonyms to AOV.
6. revenue_per_customer: In Custom mode, enter MEASURE(total_revenue) / MEASURE(unique_customers). Set display name to Revenue per Customer, format to Currency (USD).
7. open_order_revenue: In Custom mode, enter SUM(o_totalprice) FILTER (WHERE o_orderstatus = 'O'). Set display name to Open Order Revenue, format to Currency (USD), synonyms to backlog.
8. fulfilled_order_revenue: In Custom mode, enter SUM(o_totalprice) FILTER (WHERE o_orderstatus = 'F'). Set display name to Fulfilled Revenue, format to Currency (USD).
9. t7d_customers: In Custom mode, enter COUNT(DISTINCT o_custkey). Then click + Window and configure a window ordered by order_date with range trailing 7 day and last semiadditive aggregation. Set display name to 7-Day Rolling Customers, format to Number.

For the full measure steps, see Step 5: Add measures.

YAML editor

measures:
  - name: order_count
    expr: COUNT(DISTINCT o_orderkey)
    display_name: Order Count
    format:
      type: number
      decimal_places:
        type: exact
        places: 0
      abbreviation: compact

  - name: total_revenue
    expr: SUM(o_totalprice)
    display_name: Total Revenue
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
    synonyms:
      - revenue
      - sales

  - name: discounted_revenue
    expr: SUM(o_totalprice * (1 - discount))
    display_name: Discounted Revenue
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact

  - name: unique_customers
    expr: COUNT(DISTINCT o_custkey)
    display_name: Unique Customers
    format:
      type: number
      decimal_places:
        type: exact
        places: 0
      abbreviation: compact

  - name: avg_order_value
    expr: MEASURE(total_revenue) / MEASURE(order_count)
    display_name: Avg Order Value
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
    synonyms:
      - AOV

  - name: revenue_per_customer
    expr: MEASURE(total_revenue) / MEASURE(unique_customers)
    display_name: Revenue per Customer
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact

  - name: open_order_revenue
    expr: SUM(o_totalprice) FILTER (WHERE o_orderstatus = 'O')
    display_name: Open Order Revenue
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
    synonyms:
      - backlog

  - name: fulfilled_order_revenue
    expr: SUM(o_totalprice) FILTER (WHERE o_orderstatus = 'F')
    display_name: Fulfilled Revenue
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact

  - name: t7d_customers
    expr: COUNT(DISTINCT o_custkey)
    window:
      - order: order_date
        semiadditive: last
        range: trailing 7 day
    display_name: 7-Day Rolling Customers
    format:
      type: number
      decimal_places:
        type: exact
        places: 0

Review the complete definition

After completing the steps above, your metric view has the following complete definition:

View the complete YAML definition

version: 1.1

parameters:
  - name: discount
    data_type: double
    default: 0

source: SELECT * FROM samples.tpch.orders

joins:
  - name: customer
    source: samples.tpch.customer
    'on': o_custkey = c_custkey
    joins:
      - name: nation
        source: samples.tpch.nation
        'on': c_nationkey = n_nationkey

filter: o_orderdate >= '1995-01-01'

comment: |-
  Sales analytics metric view for order performance analysis.
  Joins orders with customers and geography.
  Owner: Analytics Team
  Last updated: 2025-01-15

fields:
  - name: order_date
    expr: o_orderdate
    display_name: Order Date
  - name: order_month
    expr: "DATE_TRUNC('MONTH', order_date)"
    display_name: Order Month
  - name: order_year
    expr: YEAR(order_date)
    display_name: Order Year
  - name: order_status
    expr: |-
      CASE o_orderstatus
        WHEN 'O' THEN 'Open'
        WHEN 'P' THEN 'Processing'
        WHEN 'F' THEN 'Fulfilled'
      END
    display_name: Order Status
    synonyms:
      - status
      - fulfillment status
  - name: order_priority
    expr: "SPLIT(o_orderpriority, '-')[0]"
    display_name: Priority
  - name: customer_name
    expr: customer.c_name
    display_name: Customer Name
  - name: market_segment
    expr: customer.c_mktsegment
    display_name: Market Segment
    synonyms:
      - segment
      - industry
  - name: customer_nation
    expr: customer.nation.n_name
    display_name: Country
    synonyms:
      - nation
      - country

measures:
  - name: order_count
    expr: COUNT(DISTINCT o_orderkey)
    display_name: Order Count
    format:
      type: number
      decimal_places:
        type: exact
        places: 0
      abbreviation: compact
  - name: total_revenue
    expr: SUM(o_totalprice)
    display_name: Total Revenue
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
    synonyms:
      - revenue
      - sales
  - name: discounted_revenue
    expr: SUM(o_totalprice * (1 - discount))
    display_name: Discounted Revenue
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
  - name: unique_customers
    expr: COUNT(DISTINCT o_custkey)
    display_name: Unique Customers
    format:
      type: number
      decimal_places:
        type: exact
        places: 0
      abbreviation: compact
  - name: avg_order_value
    expr: MEASURE(total_revenue) / MEASURE(order_count)
    display_name: Avg Order Value
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
    synonyms:
      - AOV
  - name: revenue_per_customer
    expr: MEASURE(total_revenue) / MEASURE(unique_customers)
    display_name: Revenue per Customer
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
  - name: open_order_revenue
    expr: SUM(o_totalprice) FILTER (WHERE o_orderstatus = 'O')
    display_name: Open Order Revenue
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
    synonyms:
      - backlog
  - name: fulfilled_order_revenue
    expr: SUM(o_totalprice) FILTER (WHERE o_orderstatus = 'F')
    display_name: Fulfilled Revenue
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
  - name: t7d_customers
    expr: COUNT(DISTINCT o_custkey)
    window:
      - order: order_date
        semiadditive: last
        range: trailing 7 day
    display_name: 7-Day Rolling Customers
    format:
      type: number
      decimal_places:
        type: exact
        places: 0

Create the metric view using SQL

If you're building this definition outside Catalog Explorer, run the following SQL to create the metric view:

CREATE OR REPLACE VIEW catalog.schema.tpch_sales_analytics
WITH METRICS
LANGUAGE YAML
AS $$
version: 1.1

parameters:
  - name: discount
    data_type: double
    default: 0

source: SELECT * FROM samples.tpch.orders

joins:
  - name: customer
    source: samples.tpch.customer
    'on': o_custkey = c_custkey
    joins:
      - name: nation
        source: samples.tpch.nation
        'on': c_nationkey = n_nationkey

filter: o_orderdate >= '1995-01-01'

comment: |-
  Sales analytics metric view for order performance analysis.
  Joins orders with customers and geography.
  Owner: Analytics Team
  Last updated: 2025-01-15

fields:
  - name: order_date
    expr: o_orderdate
    display_name: Order Date
  - name: order_month
    expr: "DATE_TRUNC('MONTH', order_date)"
    display_name: Order Month
  - name: order_year
    expr: YEAR(order_date)
    display_name: Order Year
  - name: order_status
    expr: |-
      CASE o_orderstatus
        WHEN 'O' THEN 'Open'
        WHEN 'P' THEN 'Processing'
        WHEN 'F' THEN 'Fulfilled'
      END
    display_name: Order Status
    synonyms:
      - status
      - fulfillment status
  - name: order_priority
    expr: "SPLIT(o_orderpriority, '-')[0]"
    display_name: Priority
  - name: customer_name
    expr: customer.c_name
    display_name: Customer Name
  - name: market_segment
    expr: customer.c_mktsegment
    display_name: Market Segment
    synonyms:
      - segment
      - industry
  - name: customer_nation
    expr: customer.nation.n_name
    display_name: Country
    synonyms:
      - nation
      - country

measures:
  - name: order_count
    expr: COUNT(DISTINCT o_orderkey)
    display_name: Order Count
    format:
      type: number
      decimal_places:
        type: exact
        places: 0
      abbreviation: compact
  - name: total_revenue
    expr: SUM(o_totalprice)
    display_name: Total Revenue
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
    synonyms:
      - revenue
      - sales
  - name: discounted_revenue
    expr: SUM(o_totalprice * (1 - discount))
    display_name: Discounted Revenue
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
  - name: unique_customers
    expr: COUNT(DISTINCT o_custkey)
    display_name: Unique Customers
    format:
      type: number
      decimal_places:
        type: exact
        places: 0
      abbreviation: compact
  - name: avg_order_value
    expr: MEASURE(total_revenue) / MEASURE(order_count)
    display_name: Avg Order Value
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
    synonyms:
      - AOV
  - name: revenue_per_customer
    expr: MEASURE(total_revenue) / MEASURE(unique_customers)
    display_name: Revenue per Customer
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
  - name: open_order_revenue
    expr: SUM(o_totalprice) FILTER (WHERE o_orderstatus = 'O')
    display_name: Open Order Revenue
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
    synonyms:
      - backlog
  - name: fulfilled_order_revenue
    expr: SUM(o_totalprice) FILTER (WHERE o_orderstatus = 'F')
    display_name: Fulfilled Revenue
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
  - name: t7d_customers
    expr: COUNT(DISTINCT o_custkey)
    window:
      - order: order_date
        semiadditive: last
        range: trailing 7 day
    display_name: 7-Day Rolling Customers
    format:
      type: number
      decimal_places:
        type: exact
        places: 0
$$;

For other ways to create a metric view, see Create a metric view.

Step 8: Query your metric view

Query the metric view using business-friendly syntax. The MEASURE() function aggregates a measure at the grain of the fields you select.

Aggregate measures by dimension

This example aggregates measures across multiple fields. It returns total revenue, order count, and average order value by customer nation and market segment, ranked by highest revenue first:

SELECT
  customer_nation,
  market_segment,
  MEASURE(total_revenue) AS total_revenue,
  MEASURE(order_count) AS order_count,
  MEASURE(avg_order_value) AS avg_order_value
FROM catalog.schema.tpch_sales_analytics
GROUP BY customer_nation, market_segment
ORDER BY total_revenue DESC;

Analyze a monthly trend

This example combines a time field with measures to track a trend. It returns total revenue and open order revenue (backlog) by month and order status:

SELECT
  order_month,
  order_status,
  MEASURE(total_revenue) AS total_revenue,
  MEASURE(open_order_revenue) AS open_order_revenue
FROM catalog.schema.tpch_sales_analytics
GROUP BY order_month, order_status
ORDER BY order_month;

Pass a parameter value

Because the metric view defines a parameter, you can call it as a table-valued function and pass a value at query time. The following query applies a 10% discount. Because discount has a default of 0, queries that omit the argument return undiscounted revenue:

SELECT
  customer_nation,
  MEASURE(total_revenue) AS total_revenue,
  MEASURE(discounted_revenue) AS discounted_revenue
FROM catalog.schema.tpch_sales_analytics(discount => 0.1)
GROUP BY customer_nation
ORDER BY discounted_revenue DESC;

What you learned

You built a metric view that demonstrates:

Feature	Example
Snowflake schema joins	Orders to customer to nation (nested many-to-one joins)
Time fields	Date, month, year granularity
Transformed fields	CASE statements, SPLIT functions
Simple measures	COUNT, SUM
Composability	avg_order_value and revenue_per_customer reference earlier-defined measures using MEASURE()
Filtered measures	FILTER (WHERE ...) for conditional aggregations
Window measures	Rolling 7-day customer count using trailing 7 day
Parameters	discount parameter applied in the discounted_revenue measure
Agent metadata	display_name, format, synonyms on fields and measures

Additional resources

• Window measures to calculate rolling averages and year-to-date totals.
• Materialization for metric views to improve query performance for large datasets.
• Use metric views to use your metric view in AI/BI dashboards.