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":"ai-dev-kit","repo":"databricks-solutions/ai-dev-kit","paths":["databricks-skills/databricks-metric-views/SKILL.md"],"relationship":"extended","last_synced":"2026-04-27","sync_commit":"latest"},{"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:
            bad.append((str(yf), j["name"], on_clause))
if bad:
    for f, n, o in bad:
        print(f"TRANSITIVE JOIN in {f} :: join '{n}' -> {o}")
    raise RuntimeError(
        "Transitive/flat-sibling joins detected. Restructure as nested "
        "joins (DBR 17.1+) or as subquery-source (preferred)."
    )

Implementation Workflow

Phase 1: Design (30 min)

Read: references/requirements-template.md

• Identify fact table as primary source
• List dimensions to join (2-5 dimension tables)
• Define key measures (5-10 measures with aggregation type)
• List common user questions (guides synonym creation)
• Map synonyms for each dimension and measure (3-5 each)

Phase 2: YAML Creation (1 hour)

Read: references/yaml-reference.md and references/advanced-patterns.md

• Create one YAML file per metric view (filename = view name)
• Define source table (fully qualified with ${catalog} and ${gold_schema} placeholders)
• Add joins with name, source, 'on' (include is_current = true for SCD2)
• Define dimensions with correct prefix (source. or {join_name}.)
  • Business-friendly comments and display names
  • 3-10 synonyms each (max 10 per field, max 255 chars each)
• Define measures with correct aggregation (SUM, AVG, COUNT)
  • Proper formatting (currency, number, percentage)
  • Comprehensive comments for Genie
  • 3-10 synonyms each (max 10 per field, max 255 chars each)

Phase 3: Script & Bundle (30 min)

Read: references/implementation-workflow.md

• Validate YAML with scripts/validate_metric_view.py
• Use scripts/create_metric_views.py for deployment
• Configure Asset Bundle job (see assets/templates/metric-views-job-template.yml)
• Add YAML file sync to databricks.yml

Phase 4: Deploy & Test (30 min)

Read: references/validation-queries.md

• Deploy: databricks bundle deploy -t dev
• Run: databricks bundle run metric_views_job -t dev
• Verify: DESCRIBE EXTENDED shows Type: METRIC_VIEW
• Test: SELECT ... MEASURE(\Total Revenue`) ... GROUP BY ...`
• Test with Genie: Ask natural language questions

Quick Reference

YAML Structure (v1.1)

version: "1.1"
comment: >
  PURPOSE: [One-line description]
  BEST FOR: [Question 1] | [Question 2] | [Question 3]
  NOT FOR: [What to avoid] (use [correct_asset] instead)
  DIMENSIONS: [dim1], [dim2], [dim3]
  MEASURES: [measure1], [measure2], [measure3]
  SOURCE: [fact_table] ([domain] domain)
  JOINS: [dim_table1] ([description])
  NOTE: [Critical caveats]

source: ${catalog}.${gold_schema}.<fact_table>
filter: <sql_boolean_expression>  # Optional: WHERE clause applied to all queries

joins:
  - name: <dim_table_alias>
    source: ${catalog}.${gold_schema}.<dim_table>
    'on': source.<fk> = <dim_table_alias>.<pk> AND <dim_table_alias>.is_current = true
    rely:
      at_most_one_match: true   # Optional. Many-to-one joins only — declares the join does not fan out, so the
                                # analyzer plans more efficiently. NOT validated at runtime: if it fans out,
                                # SUM/COUNT measures return wrong results. Omit if unsure.

# `fields:` is an alias for `dimensions:` (DBR 18.1+); preserve whichever key the YAML already uses.
# A metric view is valid with only dimensions, only measures, or both — measures are NOT mandatory.
# NOTE: the `materialization` block below still requires the `dimensions:` keyword.
fields:
  - name: <dimension_name>
    expr: source.<column>
    comment: <Business description>
    display_name: <User-Friendly Name>
    synonyms: [<alt1>, <alt2>]

measures:
  - name: <measure_name>
    expr: SUM(source.<column>)
    comment: <Business description>
    display_name: <User-Friendly Name>
    format:
      type: currency|number|percentage
      currency_code: USD
      decimal_places:
        type: exact|all
        places: 2
    synonyms: [<alt1>, <alt2>]

Valid format types (exhaustive):

Type	Use For	Common Mistake
byte	Data sizes (storage, memory)	—
currency	Monetary values (revenue, cost)	—
date	Date-only values	—
date_time	Timestamp values	—
number	Counts, averages, decimals, integers	❌ decimal, ❌ integer
percentage	Ratios, rates, percentages	❌ percent

⚠️ percent is NOT valid (use percentage). decimal is NOT valid (use number).

Column References

• Main table columns: Use source. prefix in all expr fields
• Joined table columns: Use join name as prefix (e.g., dim_store.column_name)
• Never reference table names directly: Use source. or {join_name}.

Join Semantics

• Metric view joins use LEFT OUTER JOIN semantics by default — source (the fact) is the left side, joined dimension tables are the right side.
• Joins are many-to-one by design. If a join is actually many-to-many, the engine selects the first matching row from the dimension — silently wrong for aggregations. Keep joins many-to-one.
• The engine only joins tables needed for the query (based on the selected dimensions/measures), so unused joins add no cost.
• The optional rely.at_most_one_match: true hint lets the analyzer optimize filter pushdown for genuine many-to-one joins. It is NOT validated at runtime — if the join fans out, SUM/COUNT inflate. Verify cardinality before setting it (below).

Cardinality verification (gate for rely.at_most_one_match: true and any join):

-- The join key MUST be unique in the dimension table (0 rows = safe many-to-one).
SELECT <join_key>, COUNT(*) AS c
FROM ${catalog}.${gold_schema}.<dim_table>
WHERE is_current = true            -- include for SCD2 dimensions
GROUP BY <join_key>
HAVING c > 1;                       -- must return 0 rows

If this returns rows, the join can fan out: do not set at_most_one_match, filter to is_current, or pre-aggregate the dimension. A quick COUNT(*) before/after adding a join is the fastest fan-out smoke test — if the row count changes, you have a fan-out.

Join Requirements

• Each join MUST have name, source, and either 'on' or using
• ON clause: boolean expression using source. for main table, join name for joined table (quote the key: 'on'). If a reference in an on clause has no prefix, it defaults to the joined table.
• USING clause: array of column names — use only when both tables share identical column names; use on for all other cases
• Each first-level join must reference source (NOT another join alias — that's transitive)
• For transitive relationships, use nested joins: (snowflake schema, DBR 17.1+) or denormalized columns; reference nested columns as parent_join.child_join.column
• SCD2 joins must include AND {dim_table}.is_current = true
• MAP type columns are NOT supported in joined tables

Core Patterns

Composability (MEASURE Function)

Metric views support composability — building complex metrics by referencing simpler measures via the MEASURE() function. Define atomic measures first, then compose derived KPIs:

measures:
  - name: total_revenue
    expr: SUM(source.net_revenue)
  - name: order_count
    expr: COUNT(source.order_id)
  - name: avg_order_value
    expr: MEASURE(total_revenue) / MEASURE(order_count)  # ✅ Composed measure

Measure-level filtering with FILTER clause:

  - name: fulfilled_orders
    expr: COUNT(1) FILTER (WHERE source.order_status = 'F')
  - name: fulfillment_rate
    expr: MEASURE(fulfilled_orders) / MEASURE(order_count)
    format:
      type: percentage

Best practices: Define atomic measures (SUM, COUNT, AVG) first; always use MEASURE() to reference other measures (never repeat the aggregation logic).

See references/composability-patterns.md for full guide including conditional logic, window measures (Experimental, supported in v1.1), and complete examples.

Level of Detail (Percent-of-Total, Cohorts, Peer Averages)

Some metrics need an aggregate at a different grain than the query GROUP BY — percent-of-total, percent-of-category, cohort assignment, peer-average comparisons. A plain measure cannot do this because it always resolves at the query grain. Use a Fixed LOD (a dimension with OVER (PARTITION BY ...), read with ANY_VALUE()) or an Exclude LOD (a window measure with range: all).

⚠️ Do NOT compute percent-of-total with MEASURE() / MEASURE() — both sides resolve at the query grain, so the ratio is always 1.0. Use a Fixed LOD + ANY_VALUE():

# ❌ WRONG — always 1.0 (both operands at the query GROUP BY level)
- name: pct_wrong
  expr: MEASURE(`revenue`) / MEASURE(`total_revenue`)

# ✅ RIGHT — Fixed LOD dimension (global) + ANY_VALUE() in the measure
fields:
  - name: global_revenue
    expr: SUM(source.net_revenue) OVER ()      # ignores query GROUP BY
measures:
  - name: revenue
    expr: SUM(source.net_revenue)
  - name: pct_of_total
    expr: MEASURE(`revenue`) / ANY_VALUE(`global_revenue`)
    format: { type: percentage, decimal_places: { type: exact, places: 1 } }

See references/level-of-detail.md for Fixed vs Exclude LOD, percent-of-category, cohort analysis, and constraints.

Standardized Comment Format (v3.0)

Use structured format for Genie optimization:

comment: >
  PURPOSE: Comprehensive cost analytics for Databricks billing and usage analysis.
  
  BEST FOR: Total spend by workspace | Cost trend over time | SKU cost breakdown
  
  NOT FOR: Commit/contract tracking (use commit_tracking) | Real-time cost alerts
  
  DIMENSIONS: usage_date, workspace_name, sku_name, owner, tag_team
  
  MEASURES: total_cost, total_dbus, cost_7d, cost_30d
  
  SOURCE: fact_usage (billing domain)
  
  JOINS: dim_workspace (workspace details), dim_sku (SKU details)
  
  NOTE: Cost values are list prices. Actual billed amounts may differ.

Dimension & Measure Patterns

See references/advanced-patterns.md for complete dimension patterns (geographic, product, time), measure patterns (revenue, count, percentage), and a full worked retail example.

Common Mistakes to Avoid

Top 5 mistakes (with paired wrong/correct examples): wrong syntax (TBLPROPERTIES), unsupported fields (time_dimension, window_measures), wrong column references, including name in YAML, transitive joins.

See references/advanced-patterns.md for detailed wrong/correct code examples for each mistake.

Python Script Error Handling

Key rules: strip name before yaml.dump(), drop existing VIEW/TABLE before CREATE, track failures and raise RuntimeError, verify METRIC_VIEW type via DESCRIBE EXTENDED.

See scripts/create_metric_views.py for the full working script and references/implementation-workflow.md for the detailed error handling patterns.

Time Estimates

Metric Views	Design	YAML Creation	Deploy & Test	Total
1 view	20 min	30 min	20 min	~1 hour
2-3 views	30 min	1 hour	30 min	~2 hours
5+ views	1 hour	2 hours	30 min	~3.5 hours

Reference Files

• references/yaml-reference.md — Complete YAML fields, syntax, format options
• references/advanced-patterns.md — Dimension/measure patterns, joins, snowflake schema, worked examples
• references/composability-patterns.md — MEASURE() function, FILTER clause, window measures (Experimental, v1.1)
• references/level-of-detail.md — Fixed/Exclude LOD: percent-of-total, percent-of-category, cohort, peer-average
• references/querying-metric-views.md — Query syntax: GROUP BY ALL, MEASURE(), HAVING-on-alias, Top-K, casting, agg()
• references/validation-checklist.md — Pre-creation validation steps
• references/requirements-template.md — Design template for dimensions, measures, joins
• references/implementation-workflow.md — Step-by-step creation workflow
• references/validation-queries.md — SQL queries for deployment verification

Scripts & Assets

• scripts/validate_metric_view.py — Pre-deployment column reference validation
• scripts/create_metric_views.py — YAML loading, parameter substitution, METRIC_VIEW verification
• assets/templates/metric-view-template.yaml — Starter YAML template
• assets/templates/metric-views-job-template.yml — Asset Bundle job template

Materialization (Experimental)

Metric views support optional materialization for pre-computed aggregations. Lakeflow Spark Declarative Pipelines orchestrates materialized views, and the query optimizer automatically routes queries to the best materialized view using aggregate-aware query rewriting.

materialization:
  schedule: every 6 hours
  mode: relaxed
  materialized_views:
    - name: baseline
      type: unaggregated
    - name: revenue_breakdown
      type: aggregated
      dimensions: [category, color]
      measures: [total_revenue]

Materialized view types: unaggregated (full data) and aggregated (pre-computed for specific dimension/measure combinations). Check refresh status with DESCRIBE TABLE EXTENDED.

Requires serverless compute enabled. Currently experimental — use for high-query-volume metric views where pre-computation reduces latency.

Common Issues

Issue	Solution
SELECT * not supported	Must explicitly list dimensions and use MEASURE() for measures
"Cannot resolve column"	Dimension/measure names with spaces need backtick quoting
JOIN at query time fails	Joins must be in the YAML definition, not in the SELECT query
MEASURE() required	All measure references must be wrapped: MEASURE(\name`)(oragg()` on DBR 18.1+)
HAVING on a measure fails	Filter on the measure alias, not the MEASURE() expression
Redeploy dropped UC grants	CREATE OR REPLACE/drop+create deletes permissions — use ALTER VIEW to preserve them (see Critical Rules)
DBR version error	Compute must be on DBR 17.3+ to create or edit metric views (current docs requirement); YAML v1.1 features need 17.2+; legacy v0.1 needs 16.4+
Materialization not working	Requires serverless compute enabled; currently experimental

See references/querying-metric-views.md for the full query syntax guide (GROUP BY ALL, HAVING-on-alias, Top-K, casting, agg()).

External References

Official Documentation

• Metric Views Overview
• Create and Edit Metric Views
• Metric View YAML Syntax Reference
• Basic Modeling (sources, dimensions, measures, joins)
• Advanced Techniques (composability, window measures, materialization)
• Tutorial: Build a complete metric view with joins
• Manage Metric Views
• Query Metric Views
• Agent Metadata in Metric Views

Related Skills

• databricks-table-valued-functions — TVF patterns for Genie
• genie-space-patterns — Genie Space setup
• databricks-aibi-dashboards — AI/BI dashboard patterns

Version History

• v5.2 (Jun 6, 2026) — Reconciled against the authoritative Databricks using-metric-views skill. Corrected window measures: they are supported in v1.1 (Experimental), not "v0.1 only" — added inclusive/exclusive anchor modifiers and MTD example. Added references/level-of-detail.md (Fixed/Exclude LOD, percent-of-total via ANY_VALUE(), cohort) and references/querying-metric-views.md (GROUP BY ALL, HAVING-on-alias, Top-K, casting, agg() alias). Added join semantics (LEFT OUTER, many-to-one, first-matching-row), cardinality-verification query, and ALTER VIEW vs CREATE OR REPLACE governance (grant-preserving ALTER path in create_metric_views.py). Clarified fields is a DBR 18.1+ alias for dimensions and that measures are optional.
• v5.1 (Apr 27, 2026) — Refreshed prerequisites and Common Issues for the current docs requirement (DBR 17.3+ for CAN USE on metric view creation/edit). Migrated External Documentation links and upstream_sources URLs to the current /business-semantics/metric-views/... paths (overview, yaml-reference, basic-modeling, advanced-techniques, manage, agent-metadata).
• v5.0 (Feb 2026) — Expanded transitive joins with inline fixes; exhaustive format type table (6 types); composability (MEASURE function) patterns; FILTER clause; USING join clause; filter top-level field; window measures clarification (Experimental v0.1); materialization expansion; progressive disclosure restructure (Notes to Carry Forward + Next Step); 7 upstream_sources from official docs; new composability-patterns.md reference
• v4.0 (Feb 2026) — Merged prompt content: Quick Start, implementation workflow, requirements template, creation script, validation queries, worked examples, common mistakes with paired examples
• v3.0 (Dec 19, 2025) — Standardized structured comment format
• v2.0 (Dec 16, 2025) — Genie optimization patterns from production post-mortem
• v1.0 (Oct 2025) — Initial rule based on metric view deployment learnings

Metric Views Notes to Carry Forward

After completing metric view creation, carry these notes to the next worker:

• Metric View names and paths: List of all created MVs with YAML file paths
• Grain per view: Which fact table sources each MV
• Measure counts: Number of dimensions and measures per MV
• Validation status: Which MVs passed schema validation, any unresolved issues
• Composability notes: Any composed measures using MEASURE() that downstream workers should know about

Next Step

After metric views are deployed and validated, proceed to: semantic-layer/02-databricks-table-valued-functions/SKILL.md — Create TVFs for Genie Spaces using the Gold tables referenced by your metric views.