metric-views-patterns

name	metric-views-patterns
description	Standard patterns for creating Databricks Metric Views with semantic metadata for Genie and AI/BI. Use when creating metric views, troubleshooting metric view creation errors, validating schema references before deployment, implementing joins (including snowflake schema patterns), or optimizing metric views for Genie natural language queries.
clients	["ide_cli","genie_code"]
bundle_resource	jobs
deploy_verb	bundle_deploy
deploy_note	Metric Views are created by a notebook_task in the semantic-layer job; deploy via `bundle deploy --target dev` (runDatabricksCli on Genie Code).
coverage	full
metadata	{"author":"prashanth subrahmanyam","version":"2.0","domain":"semantic-layer","role":"worker","pipeline_stage":6,"pipeline_stage_name":"semantic-layer","called_by":["semantic-layer-setup"],"standalone":true,"last_verified":"2026-06-06","volatility":"high","upstream_sources":[{"name":"databricks-agent-skills","repo":"databricks/databricks-agent-skills","paths":"[Truncated]","relationship":"extended","last_synced":"2026-08-30","sync_commit":"ca92a6c"},{"name":"databricks-docs-overview","url":"https://docs.databricks.com/aws/en/business-semantics/metric-views/","relationship":"upstream","last_synced":"2026-06-05"},{"name":"databricks-docs-yaml-reference","url":"https://docs.databricks.com/aws/en/business-semantics/metric-views/yaml-reference","relationship":"upstream","last_synced":"2026-06-05"},{"name":"databricks-docs-basic-modeling","url":"https://docs.databricks.com/aws/en/business-semantics/metric-views/basic-modeling","relationship":"upstream","last_synced":"2026-06-05"},{"name":"databricks-docs-advanced-techniques","url":"https://docs.databricks.com/aws/en/business-semantics/metric-views/advanced-techniques","relationship":"upstream","last_synced":"2026-06-05"},{"name":"databricks-docs-create-sql","url":"https://docs.databricks.com/aws/en/metric-views/create/sql","relationship":"upstream","last_synced":"2026-06-05"},{"name":"databricks-docs-manage","url":"https://docs.databricks.com/aws/en/business-semantics/metric-views/manage","relationship":"upstream","last_synced":"2026-06-05"},{"name":"databricks-docs-agent-metadata","url":"https://docs.databricks.com/aws/en/business-semantics/agent-metadata","relationship":"upstream","last_synced":"2026-06-05"}]}

End-to-end semantic layer? If you are creating Metric Views as part of a larger deployment that also includes TVFs and Genie Spaces, read semantic-layer/00-semantic-layer-setup/SKILL.md first — it orchestrates this skill with the others and mandates Gold schema validation before artifact creation.

Metric Views Patterns for Genie & AI/BI

Overview

Metric Views provide a semantic layer for natural language queries via Genie and AI/BI dashboards. This skill standardizes the YAML structure for comprehensive, LLM-friendly metric definitions following Databricks Metric View Specification v1.1.

Predecessor: Gold tables must exist before creating metric views. Use gold-layer-design + gold-layer-setup skills first.

Key Capabilities:

• Create metric views with proper SQL syntax (WITH METRICS LANGUAGE YAML)
• Validate schemas before deployment to prevent 100% of common errors
• Structure joins (direct and snowflake schema patterns)
• Optimize comments for Genie natural language queries
• Handle SCD2 dimensions with proper is_current filtering

When to Use This Skill

Use this skill when:

• Creating new metric views for Genie Spaces
• Troubleshooting metric view creation errors
• Validating schema references before deployment
• Implementing joins (including transitive relationships)
• Optimizing metric views for Genie natural language queries
• Ensuring compliance with v1.1 specification
• Following the requirements gathering template to design metric views

Prerequisites

⚠️ MANDATORY: Complete these before creating metric views:

• Gold layer tables exist in Unity Catalog (use gold-layer-design + gold-layer-setup skills) — required for production deployment.
• Gold layer YAML schemas exist in gold_layer_design/yaml/ (for validation script). When invoked from a workshop-draft plan with selected_layer = gold_design, YAML alone is acceptable as the source of truth and the validation script's live-catalog probe is advisory.
• Serverless SQL warehouse available (for metric view creation and querying)
• SQL warehouse or compute resource on Databricks Runtime 17.3+ (current docs requirement for CAN USE permission to create or edit a metric view). YAML v1.1 features were introduced in DBR 17.2; some experimental features (snowflake schema joins, agent metadata, materialization) require DBR 17.3+.

Layer-aware deployment (workshop mode): The patterns in this skill (YAML, dimensions, measures, joins) are layer-neutral. The orchestrator (semantic-layer/00-semantic-layer-setup) decides which schema to deploy against based on planning_source.selected_layer:

• deployed_gold / gold_design → reference Gold tables (production path).
• deployed_silver / deployed_bronze (workshop deployments) → reference Silver or Bronze tables directly. Metric View YAML is identical in shape; the source field points at the workshop layer's schema. The orchestrator prints a quality advisory because raw layers typically lack curated COMMENTs and dimensional joins.
• source_csv → not reached; the orchestrator stops because no live tables exist.

Production Metric Views always reference Gold; workshop builds may reference Silver/Bronze and should be promoted to Gold for production hardening.

MCP Tools (from upstream databricks-metric-views)

The manage_metric_views MCP tool supports all metric view operations:

Action	Description
create	Create a metric view with dimensions and measures
alter	Update a metric view's YAML definition
describe	Get the full definition and metadata
query	Query measures grouped by dimensions
drop	Drop a metric view
grant	Grant SELECT privileges to users/groups

Quick Start (2 hours)

What You'll Create:

1. metric_views/{view_name}.yaml — Semantic definitions (dimensions, measures, joins, formats)
2. create_metric_views.py — Script reads YAML, creates views with WITH METRICS LANGUAGE YAML
3. metric_views_job.yml — Asset Bundle job for deployment

Deploy: databricks bundle deploy -t dev && databricks bundle run metric_views_job -t dev

Critical Rules

⚠️ CRITICAL: Correct SQL Syntax

Metric views MUST be created using WITH METRICS LANGUAGE YAML syntax:

create_sql = f"""
CREATE OR REPLACE VIEW {fully_qualified_name}
WITH METRICS
LANGUAGE YAML
COMMENT '{view_comment_escaped}'
AS $$
{yaml_str}
$$
"""

Key Requirements:

1. WITH METRICS — Identifies the view as a metric view
2. LANGUAGE YAML — Specifies YAML format
3. AS $$ ... $$ — YAML content wrapped in dollar-quote delimiters
4. No SELECT statement — The YAML definition IS the view definition
5. version field — Must be included in each metric view YAML

❌ WRONG: Regular view with TBLPROPERTIES (creates regular VIEW, not METRIC_VIEW)

⚠️ Modifying: prefer ALTER VIEW over CREATE OR REPLACE

When updating an existing metric view, prefer ALTER VIEW — it preserves the view's Unity Catalog permissions (grants) and cascading metadata:

ALTER VIEW ${catalog}.${gold_schema}.<view_name>
AS $$
{updated_yaml}
$$

CREATE OR REPLACE (and the drop+create the deploy script uses by default) deletes the view and therefore its grants and cascading metadata. Only use replace/drop+create when a CI/CD step re-applies permissions on every deploy (e.g. via the grant action). For incremental edits to a live, governed view, always ALTER.

⚠️ CRITICAL: v1.1 Unsupported Fields

These fields will cause errors and MUST NOT be used:

Field	Error	Action
name	Unrecognized field "name"	❌ NEVER include — name is in CREATE VIEW statement
time_dimension	Unrecognized field "time_dimension"	❌ Remove entirely
window_measures	Unrecognized field "window_measures"	❌ Remove the top-level window_measures: array. The per-measure window: property IS supported in v1.1 (Experimental status; offset requires DBR 18.1+). See references/composability-patterns.md.
join_type	Unsupported	❌ Remove — defaults to LEFT OUTER JOIN
table (in joins)	Missing required creator property 'source'	✅ Use source instead

⚠️ MANDATORY: Pre-Creation Schema Validation

ALWAYS validate schemas BEFORE creating metric view YAML. 100% of deployment failures are preventable schema issues.

Schema Validation Checklist:

• Verified source table schema (ran DESCRIBE TABLE or checked YAML)
• Verified all joined table schemas
• Created column reference checklist for all tables
• Validated every dimension expr column exists
• Validated every measure expr column exists
• Validated join key columns exist in both tables
• Verified no transitive joins (all joins are source → table)
• For COUNT measures, verified primary key column exists
• For SCD2 joins, verified is_current column exists

See references/validation-checklist.md for detailed validation steps.

⚠️ CRITICAL: Source Table Selection

Rule: Revenue/bookings/transactions → FACT table. Property/host counts → DIMENSION table.

❌ WRONG: Revenue from dimension table (under-reports by 4x)

source: ${catalog}.${schema}.dim_property  # ❌ Wrong for revenue!

✅ CORRECT: Revenue from fact table

source: ${catalog}.${schema}.fact_booking_daily  # ✅ Correct for revenue!

⚠️ CRITICAL: Transitive Join Limitations

Metric Views DO NOT support transitive/chained joins (where join B's on clause references join A instead of source).

How to detect: If ANY join's on clause references a join alias (not source), it is transitive and will fail.

❌ WRONG: Transitive join (join B references join A)

joins:
  - name: dim_property                              # Join A
    source: catalog.schema.dim_property
    'on': source.property_id = dim_property.property_id
  - name: dim_destination                            # Join B
    source: catalog.schema.dim_destination
    'on': dim_property.destination_id = dim_destination.destination_id  # ❌ References dim_property!

This fails at plan-time with UNRESOLVED_COLUMN because dim_property is not visible in dim_destination's on scope.

✅ FIX 1 (Preferred — simplest): Use denormalized columns from existing dimension

If dim_property already has destination_name and destination_country, reference them directly — no second join needed:

# `fields:` is an alias for `dimensions:` introduced in DBR 18.1+; preserve whichever key the YAML already uses.
fields:
  - name: destination_name
    expr: dim_property.destination_name  # ✅ Already in dim_property
  - name: destination_country
    expr: dim_property.destination_country

✅ FIX 2: Snowflake schema (nested joins) — requires DBR 17.1+

joins:
  - name: dim_property
    source: catalog.schema.dim_property
    'on': source.property_id = dim_property.property_id
    joins:  # ✅ Nested under dim_property — snowflake schema
      - name: dim_destination
        source: catalog.schema.dim_destination
        'on': dim_property.destination_id = dim_destination.destination_id

Validation gate: Before generating YAML, inspect all join on clauses. If the left side of any on references a join name (not source), restructure as nested joins or use denormalized columns.

Pre-check for Fix 2 (nested joins): Verify the workspace runtime supports nested joins:

dbr = spark.sql("SELECT current_version()").first()[0]
assert float('.'.join(dbr.split('.')[:2])) >= 17.1, \
    f"Nested joins require DBR 17.1+, got {dbr}. Use Fix 1 or restructure."

If Fix 1 is not feasible (the intermediate dimension lacks the needed column, e.g., dim_property does not have destination_name), do NOT silently use Fix 2. Flag the constraint to the user and offer:

• (a) Add the column to the intermediate dimension in the Gold layer design
• (b) Confirm DBR 17.1+ and use nested joins
• (c) Omit the dimension from the Metric View and handle it via TVFs instead

See references/advanced-patterns.md for additional snowflake schema examples.

⚠️ CRITICAL: Multi-Hop / Snowflake Joins (subquery-source pattern)

Transitive joins aren't the only multi-hop trap. Two other shapes silently produce wrong numbers instead of a clean planner error — and both are indistinguishable from valid Metric Views on casual inspection. Treat this section as the canonical reference for any join that must traverse more than one dimension hop.

Failure mode A (silent row-drop — DBR < 17.1): Nested joins emitted under joins: are ignored by older runtimes. Aggregations run against the un-joined base, so dimensions from the nested table quietly disappear from the GROUP BY. No error surfaces — the Metric View just returns the wrong grain.

Failure mode B (fan-out cartesian): Nested joins work on DBR 17.1+ but the join key on the intermediate table (dim_property.destination_id) is not unique. Every row from source fans out over every matching dim_destination row, inflating measures. Again: no error — just a wrong total.

❌ Anti-pattern 1 — flat "sibling" join relying on an earlier join's alias

# Wrong: dim_destination is a sibling, not nested — and references dim_property.
joins:
  - name: dim_property
    source: catalog.schema.dim_property
    'on': source.property_id = dim_property.property_id
  - name: dim_destination
    source: catalog.schema.dim_destination
    'on': dim_property.destination_id = dim_destination.destination_id  # ❌ transitive

Planner behaviour:

• DBR ≥ 17.1: raises UNRESOLVED_COLUMN at creation time.
• DBR < 17.1: may silently accept and produce wrong results. Never rely on the error.

❌ Anti-pattern 2 — nested join without verifying intermediate uniqueness

joins:
  - name: dim_property
    source: catalog.schema.dim_property
    'on': source.property_id = dim_property.property_id
    joins:
      - name: dim_destination
        source: catalog.schema.dim_destination
        'on': dim_property.destination_id = dim_destination.destination_id
# ❌ Wrong if dim_property rows can share a destination_id with dim_destination M:1 ambiguity,
#    OR if dim_destination has multiple rows per destination_id (e.g. SCD2 without is_current filter).

Always verify uniqueness before shipping a nested join:

-- Intermediate table must be 1:1 on the OUTER-facing key:
SELECT property_id, COUNT(*) AS c
FROM catalog.schema.dim_property
GROUP BY property_id HAVING c > 1;  -- must return 0 rows

-- Inner dimension must be 1:1 on the join key (or filtered to is_current):
SELECT destination_id, COUNT(*) AS c
FROM catalog.schema.dim_destination
WHERE is_current = true              -- if SCD2
GROUP BY destination_id HAVING c > 1;  -- must return 0 rows

✅ Correct pattern — subquery source (pre-join, then treat as one dim)

When nested joins are not available (DBR < 17.1) OR the intermediate table is not uniquely keyed, fold the multi-hop into a subquery source that pre-resolves the join off-Metric-View. The Metric View then joins source to a single, clean, uniquely-keyed dimension.

source: catalog.schema.fact_booking_daily
joins:
  - name: dim_property_enriched
    # Subquery as source — pre-joins dim_property → dim_destination and enforces uniqueness.
    source: |
      (
        SELECT
          p.property_key,
          p.property_id,
          p.property_name,
          d.destination_id,
          d.destination_name,
          d.country
        FROM catalog.schema.dim_property p
        LEFT JOIN catalog.schema.dim_destination d
          ON p.destination_id = d.destination_id
         AND d.is_current = true        -- SCD2 guard
        WHERE p.is_current = true
      )
    'on': source.property_key = dim_property_enriched.property_key
fields:
  - name: property_name
    expr: dim_property_enriched.property_name
  - name: destination_name
    expr: dim_property_enriched.destination_name
  - name: country
    expr: dim_property_enriched.country

Why this is the robust FALLBACK (use only when nested joins are unavailable — DBR < 17.1 — or the intermediate key is not unique):

• Works on every DBR version (no 17.1 dependency).
• The subquery makes uniqueness guarantees explicit — reviewers can see and test them.
• Re-usable: wrap the subquery in a Gold-layer VIEW (dim_property_enriched_v) and reference it in every Metric View that needs the same enrichment — this also anchors the Gold dependency manifest (see planning/00-project-planning/SKILL.md).

Default preference (do not over-engineer): on the workshop runtime (serverless, DBR ≥ 17.1) with 1:1 intermediate keys, nested joins (Fix 2) are the PREFERRED multi-hop solution — simpler, no extra Gold view, native to the Metric View. Reach for the subquery-source pattern only as the fallback. Do NOT spend planning cycles designing a subquery workaround when nested joins resolve the path directly.

Decision ladder for any multi-hop requirement:

1. Can the intermediate dimension carry the needed attribute directly (denormalize)? → Use Fix 1 (flat join, no second hop).
2. PREFERRED on the workshop runtime. Is every intermediate key uniquely 1:1 and is DBR ≥ 17.1? → Use nested joins (Fix 2) and document the uniqueness check in a comment.
3. Only if DBR < 17.1 OR an intermediate key is non-unique → use the subquery-source fallback above. Do NOT ship a transitive join and hope for an error.

Anti-pattern detector — run before deploy:

import yaml, re
bad = []
for yf in Path("src/semantic/metric_views").rglob("*.yaml"):
    mv = yaml.safe_load(yf.read_text())
    joins = (mv.get("joins") or []) if isinstance(mv, dict) else []
    alias_names = {j["name"] for j in joins}
    for j in joins:
        on_clause = j.get("on", "")
        # Left side of `=` must start with `source.` or be a nested-join alias.
        m = re.match(r"\s*([A-Za-z_][\w.]*)", on_clause)
        left_head = (m.group(1).split(".", 1)[0] if m else "")
        if left_head in alias_names: