Unity Catalog Metric Views

Define reusable, governed business metrics in YAML that separate measure definitions from dimension groupings for flexible querying.

When to Use

Use this skill when:

• Defining standardized business metrics (revenue, order counts, conversion rates)
• Building KPI layers shared across dashboards, Genie, and SQL queries
• Creating metrics with complex aggregations (ratios, distinct counts, filtered measures)
• Defining window measures (moving averages, running totals, period-over-period, YTD)
• Modeling star or snowflake schemas with joins in metric definitions
• Enabling materialization for pre-computed metric aggregations

First: can this even be a metric view? (decision gate)

Before authoring, confirm the measure re-aggregates at query time. Additive SUM/COUNT, a COUNT(DISTINCT) that recomputes per cell, MEASURE() ratios (divide last), and supported window measures (trailing / cumulative / period-over-period — Pattern 8) all re-aggregate correctly and are valid metric-view measures; this list is not exhaustive. The gate below is about disqualifying shapes — if any of the following hold, it is not a metric view: author a governed Unity Catalog SQL function instead (build it with databricks-dbsql) and expose it to Genie as a trusted registered SQL function (Design Priorities surface #11), rather than forcing it into a view or a pre-aggregated helper view:

• Non-additive at the queried grain — summing it across a dimension double-counts. Distinct-entity counts are the classic trap: an entity appearing under two attribute values is one entity, not two, so a per-cell distinct count must not be summed across an arbitrary multi-attribute cross.
• Computed per-entity, then aggregated, where the per-entity step isn't a sum/count/ratio-of-sums — e.g. a per-entity min/max across two periods, or a threshold on a per-entity distinct count.
• Selection-dependent — the result redistributes with the user's filter (an "all others" bucket that shifts every cell).
• Iterative — repeats until convergence (e.g. panel reweighting).
• Returns a bundle of tables, not a single measure (e.g. a source×destination matrix plus per-row nets).

A measure can be mostly a metric view with one part hoisted into a function (penetration = additive buyers ÷ base, divide last — but a short-window correction, or an arbitrary multi-attribute cross / per-entity threshold, goes in a function). Pre-computing the awkward step into a base/helper view is only a fix for a fixed definition — a performance choice, settled once for all slices. If the measure must respond to the user's runtime selection in Genie (arbitrary cross, a parameter, or a shifting denominator), it is computed on the fly and cannot be pre-computed — it needs a parameterized governed SQL function, not a pre-aggregated view. Full rule + two worked examples (penetration, brand-switching gains & loss): metric-view-advisor.md §When a metric view is not the right tool. Before benchmarking Genie's routing to a function that reproduces an external methodology, validate it to N-decimal parity against its reference — see the databricks-genie-agents skill.

Prerequisites

• Databricks Runtime 17.2+ (for YAML version 1.1); 17.3+ for semantic metadata (synonyms / display_name / format)
• SQL warehouse with CAN USE permissions
• SELECT on source tables, CREATE TABLE + USE SCHEMA in the target schema

Metric View Lifecycle

Typical flow: advisor → create → query/validate → Genie integration (if adding to a Genie Agent).

Source-controlled deployment with Declarative Automation Bundles

To source-control a metric view, commit its complete SQL definition and execute it through a bundle-managed SQL job. DABs do not have a native metric-view resource, but a bundle-managed SQL job can apply a committed definition:

# databricks.yml
bundle:
  name: orders_metrics

variables:
  catalog: { default: main }
  schema:  { default: default }
  warehouse_id: { default: "" }

resources:
  jobs:
    deploy_orders_metrics:
      name: deploy_orders_metrics
      parameters:
        - name: catalog
          default: ${var.catalog}
        - name: schema
          default: ${var.schema}
      tasks:
        - task_key: create_metric_view
          sql_task:
            warehouse_id: ${var.warehouse_id}
            file:
              path: ../src/orders_metrics.metric_view.sql

Deploy and run:

databricks bundle deploy --target <TARGET> --profile <PROFILE>
databricks bundle run deploy_orders_metrics --target <TARGET> --profile <PROFILE>

See the official metric view bundle example.

Related Skills

• databricks-genie-agents — create, manage, and validate Genie Agents that consume the metric views built here. Metric-view design rules for Genie are in the advisor §Genie Design Rules; query rules are in query-patterns.md.
• databricks-aibi-dashboards — build AI/BI dashboards on top of metric views.
• databricks-data-discovery — explore data before creating metric views; answer questions across your workspace.
• databricks-dabs — source-control and deploy metric view SQL definitions via bundle-managed jobs.

Resources

• Metric Views Documentation
• YAML Syntax Reference
• Joins
• Window Measures
• Materialization
• MEASURE() Function