metric-views-patterns

> **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:** ```python 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: ```sql 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) ```yaml source: ${catalog}.${schema}.dim_property # ❌ Wrong for revenue! ``` **✅ CORRECT:** Revenue from fact table ```yaml 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) ```yaml 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: ```yaml # `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+** ```yaml 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: ```python 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 ```yaml # 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 ```yaml 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: ```sql -- 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. ```yaml 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:** ```python 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: