Metric View Patterns

Skill: databricks-metric-views

What You Can Build

Metric views define reusable business metrics as code — dimensions, measures, joins, and filters in YAML attached to a SQL view. Once defined, anyone can query a metric view with MEASURE() syntax and get consistent, governed results regardless of how they slice the data. You get a single source of truth for metrics like revenue, fulfillment rate, and customer count.

In Action

“Write SQL that creates a metric view for sales analytics with customer and product dimensions from a star schema.”

CREATE OR REPLACE VIEW catalog.schema.sales_analytics
WITH METRICS LANGUAGE YAML
AS $$
  version: 1.1
  source: catalog.schema.fact_sales
  joins:
    - name: customer
      source: catalog.schema.dim_customer
      on: source.customer_id = customer.customer_id
    - name: product
      source: catalog.schema.dim_product
      on: source.product_id = product.product_id
  dimensions:
    - name: Customer Segment
      expr: customer.segment
    - name: Product Category
      expr: product.category
    - name: Sale Month
      expr: DATE_TRUNC('MONTH', source.sale_date)
  measures:
    - name: Total Revenue
      expr: SUM(source.amount)
    - name: Unique Customers
      expr: COUNT(DISTINCT source.customer_id)
$$

Key decisions:

• version: 1.1 is the current spec for DBR 17.2+ (use 0.1 for DBR 16.4-17.1)
• source points to the fact table; joins bring in dimension tables using the join name as a namespace (e.g., customer.segment)
• Dimensions cannot use aggregate functions; measures must contain at least one aggregate
• Measures are queried with MEASURE(\Total Revenue`)-- never withSELECT *`
• Dimension expressions can reference SQL functions, CASE statements, and columns from joined tables

More Patterns

Build filtered measures for status-based reporting

“Write YAML for a metric view with filtered measures that track open orders, fulfilled orders, and fulfillment rate.”

CREATE OR REPLACE VIEW catalog.schema.order_status_metrics
WITH METRICS LANGUAGE YAML
AS $$
  version: 1.1
  source: catalog.schema.orders
  dimensions:
    - name: Order Month
      expr: DATE_TRUNC('MONTH', order_date)
    - name: Region
      expr: region
  measures:
    - name: Total Orders
      expr: COUNT(1)
    - name: Open Orders
      expr: COUNT(1) FILTER (WHERE status = 'OPEN')
    - name: Fulfillment Rate
      expr: COUNT(1) FILTER (WHERE status = 'FULFILLED') * 1.0 / COUNT(1)
$$

FILTER (WHERE ...) on a measure counts only the rows matching that condition. The denominator in Fulfillment Rate uses an unfiltered COUNT(1), giving you a correct ratio that recalculates correctly at any dimension level.

Navigate multi-level hierarchies with snowflake joins

“Write SQL for a metric view that traverses customer, nation, and region dimensions using nested joins.”

CREATE OR REPLACE VIEW catalog.schema.geo_sales
WITH METRICS LANGUAGE YAML
AS $$
  version: 1.1
  source: catalog.schema.orders
  joins:
    - name: customer
      source: catalog.schema.customer
      on: source.customer_key = customer.customer_key
      joins:
        - name: nation
          source: catalog.schema.nation
          on: customer.nation_key = nation.nation_key
          joins:
            - name: region
              source: catalog.schema.region
              on: nation.region_key = region.region_key
  dimensions:
    - name: Nation
      expr: nation.name
    - name: Region
      expr: region.name
  measures:
    - name: Total Revenue
      expr: SUM(source.total_price)
    - name: Order Count
      expr: COUNT(1)
$$

Nested joins create a snowflake schema traversal. Query Region to roll up across nations, or drill into Nation with a WHERE clause. Snowflake joins require DBR 17.1+.

Calculate trailing windows and period-over-period growth

“Write YAML for window measures that compute 7-day trailing customers and day-over-day sales growth.”

CREATE OR REPLACE VIEW catalog.schema.daily_growth
WITH METRICS LANGUAGE YAML
AS $$
  version: 1.1
  source: catalog.schema.orders
  dimensions:
    - name: date
      expr: order_date
  measures:
    - name: previous_day_sales
      expr: SUM(total_price)
      window:
        - order: date
          range: trailing 1 day
          semiadditive: last
    - name: current_day_sales
      expr: SUM(total_price)
      window:
        - order: date
          range: current
          semiadditive: last
    - name: day_over_day_growth
      expr: (MEASURE(current_day_sales) - MEASURE(previous_day_sales)) / MEASURE(previous_day_sales) * 100
$$

Window measures enable trailing aggregations, running totals, and period-over-period calculations. semiadditive: last controls what happens when the window dimension isn’t in the GROUP BY — it returns the last value instead of summing across time. Derived measures reference window measures via MEASURE().

Pre-compute common aggregations with materialization

“Write YAML that materializes daily revenue by category on an hourly schedule.”

CREATE OR REPLACE VIEW catalog.schema.ecommerce_metrics
WITH METRICS LANGUAGE YAML
AS $$
  version: 1.1
  source: catalog.schema.transactions
  dimensions:
    - name: Category
      expr: product_category
    - name: Day
      expr: DATE_TRUNC('DAY', transaction_date)
  measures:
    - name: Revenue
      expr: SUM(amount)
    - name: Transactions
      expr: COUNT(1)
  materialization:
    schedule: every 1 hour
    mode: relaxed
    materialized_views:
      - name: daily_category
        type: aggregated
        dimensions: [Category, Day]
        measures: [Revenue, Transactions]
      - name: full_model
        type: unaggregated
$$

Materialization pre-computes specific dimension/measure combinations as SDP-managed materialized views. aggregated type pre-computes specific combos; unaggregated materializes the full data model (source + joins + filter). Requires serverless compute and DBR 17.2+.

Watch Out For

• Using SELECT * on a metric view — it’s not supported. You must use MEASURE(\name`)` to query measures and reference dimensions by their backtick-quoted names.
• Putting aggregate functions in dimension expressions — dimensions define grouping categories. Aggregates belong in measures only. The YAML parser rejects dimensions with aggregate functions.
• Forgetting semiadditive: last on window measures — without it, the system doesn’t know how to handle the window dimension when it’s absent from the GROUP BY. Every window block requires the semiadditive field.
• Using MAP columns in joined tables — joins don’t support MAP type columns in the joined source. Use only scalar and STRUCT columns in dimension and measure expressions from joined tables.