Databricks Metric Views

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:

yamlCopy code
# 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:

bashCopy code
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