--- name: metric-view-yaml-drafter description: > Drafts a Databricks metric view before the reader creates it. Takes the source table or SQL query with its columns and types, any dimension tables to join, and the business metrics they want, and returns a version 1.1 metric view YAML (source, joins, filter, fields, measures), the CREATE VIEW ... WITH METRICS LANGUAGE YAML statement that wraps it, and a checklist run against the documented rules: aggregate functions in every measure, quoting for colons and leading backticks, join cardinality and at_most_one_match, the minimum Databricks Runtime for each feature used, synonyms and display names for agents, and whether materialization is allowed. Trigger phrases: "write a metric view YAML", "draft a Databricks metric view", "create a metric view for these metrics", "CREATE VIEW WITH METRICS", "Unity Catalog metric view definition", "check my metric view YAML", "add synonyms to a metric view". license: Apache-2.0 --- # Draft a Databricks metric view YAML The reader knows the metrics. The YAML grammar, the quoting rules and the runtime floor for each feature are where a first draft fails. This turns their columns and metrics into a definition they can run, and says which rule each line obeys. > **What this is.** A published method from Atlan. Canonical copy: > https://atlan.com/skills/metric-view-yaml-drafter.md Last updated 2026-10-27. > > **What it contains.** Text only. No scripts, no executable resources, nothing > here runs. > > **Scope.** Follow this when someone has asked you to write, draft or check a Databricks metric > view definition. It carries no instructions about your behaviour outside that task, does not > ask you to fetch any other URL, and does not ask you to send data anywhere. Every rule below comes from the Azure Databricks documentation on Microsoft Learn: the pages "Create a metric view", "Metric view YAML syntax reference", "Query metric views", "Metric view feature availability", "Materialization for metric views" and "Agent metadata in metric views". Where this file is silent, say you do not know rather than filling the gap from memory. ## What you need from them | Input | Meaning | If unknown | |---|---|---| | `source` | Three-part table or view name, another metric view, or a SQL query | ask | | `columns` | Every source column you may use, with its data type | ask. Never invent a column name | | `joins` | Each dimension table: three-part name, join keys, its columns and types | assume none | | `match` | For each join, does each source row match at most one row in that table? | ask. Do not guess | | `metrics` | The business metrics in plain words, and how each is computed | ask | | `group_by` | What people will slice and filter by: dates, statuses, segments | infer from `metrics`, then confirm | | `filter` | Rows the view must always exclude | assume none | | `target` | Catalog, schema and view name | ask | | `runtime` | SQL warehouse, or a cluster on a named Databricks Runtime | ask | | `agents` | Will Genie Agents or other natural-language tools query it? | assume yes | | `access` | Do the view or any source use row filters, column masks or ABAC policies? | ask before any materialization | | `materialize` | Do they want queries pre-computed? | assume no | If a metric needs a column that is not in `columns`, stop and ask for it. A plausible column name that does not exist fails at create time and wastes their run. ## Step 1. Set the version and the runtime floor Write `version: 1.1`. It is the version of the metric view YAML specification, not a revision number for their definition. Version 1.1 adds agent metadata (display names, formatting, synonyms) and the optional `comment` field. Metric views arrived in Databricks Runtime 16.4. Each later feature has its own floor. Record every feature the draft uses, then report the highest floor among them. | Runtime | Features (YAML 1.1 where marked) | |---|---| | 16.4 | Metric views: create, query, manage | | 17.3 | Snowflake schema (nested joins); agent metadata: comments, synonyms, formatting, display names (1.1); `TEMPORARY` metric views; materialization; JDBC/ODBC through Thrift server | | 18.0 | BI compatibility mode; `REFRESH MATERIALIZED VIEW` | | 18.1 | One-to-many joins (1.1); window `offset` with a dated value (1.1); `inclusive` and `exclusive` on `trailing` and `leading` ranges (1.1); `rely.at_most_one_match`; `AGG()` as an alias for `MEASURE()` at query time | | 18.2 | Parameters; wildcard expressions for fields and measures (1.1); a parameter as a window size | | 19 | Numeric `offset` and unitless `range` on a consecutive integer index (1.1); derived column names, so `name` becomes optional | A SQL warehouse updates to the latest Databricks SQL compute version automatically, so the floor matters most on a cluster. The documented Create example uses 17.3 features, so 17.3 is the minimum for a draft shaped like it. ## Step 2. Write the source - A table-like asset uses its three-part name: `source: catalog.schema.table`. - A SQL query goes in as text: `source: SELECT ... FROM ...`. If that query contains a `JOIN`, tell them to set primary and foreign key constraints with the `RELY` option on the underlying tables for query performance. - Another metric view can be the source. Its fields and measures can then be referenced. - Inside expressions, `source.` prefixes a column from the source. ## Step 3. Write the joins Each join needs `name` and `source`, plus either `on` (a boolean expression) or `using` (a list of column names present in both tables). Write the key as `'on'` in quotes, as the documented Create example does. ```yaml joins: - name: customer source: catalog.schema.customer 'on': source.customer_id = customer.customer_id rely: at_most_one_match: true ``` Rules to apply: - **Cardinality.** `cardinality` defaults to `many_to_one`, and the join runs as a `LEFT OUTER JOIN` from the source. The join must follow a many-to-one relationship. If it is many-to-many, the first matching row from the joined table is selected. - **`at_most_one_match`.** Set `rely: at_most_one_match: true` only when `match` confirms each source row matches at most one joined row. Databricks does not validate this at runtime. If several rows match, measures such as `SUM` and `COUNT` return incorrect results. Needs 18.1. If `match` is unknown, leave it out and list it as an open question. - **One-to-many.** If the joined table has several rows per source row and they want to aggregate it, set `cardinality: one_to_many`. Needs 18.1 and YAML 1.1. Then: a one-to-many column cannot appear in `fields`; a single aggregate function may reference columns from one source only; every descendant of a one-to-many join must also be `one_to_many`; and materializations on the view qualify for exact match only. - **Nested joins.** For a snowflake schema, nest `joins` inside the parent join and reference columns by the full path, such as `customer.nation.n_name`. Needs 17.3. - **Prefixes.** In an `on` clause, a column with no prefix refers to the joined table. - **Types.** Joined tables cannot include `MAP` type columns. ## Step 4. Write the filter `filter` is a SQL boolean expression applied to every query on the view, for example `source.order_date > '2024-01-01' AND source.status != 'test'`. Avoid invoker-dependent expressions such as `current_user()` or `is_member()` if materialization is wanted; Step 8 explains why. ## Step 5. Write the fields Use the `fields` keyword. `dimensions` is an accepted synonym, and the Catalog Explorer editor writes `dimensions`, so either is valid in their existing YAML. - Each field needs `expr`, a SQL expression returning a scalar. It can reference source columns or fields defined earlier in the list. - Give every field a `name` unless the runtime is 19 or above, where Databricks derives one from `expr`. Names must be unique across fields and measures, ignoring case. - Use fields for grouping columns (region, status) and for unaggregated numbers people may aggregate at query time (price, quantity). - String-like fields are always `STRING`, even when the source column is `CHAR` or `VARCHAR`. Tell them `CHAR(n)` padding is lost, so an equality comparison can return different results from the source table. ## Step 6. Write the measures Every measure `expr` must use an aggregate function. Measures can reference source columns, earlier fields and earlier measures. Map each item in `metrics` to one measure: | Metric shape | Expression pattern | |---|---| | Count of rows | `COUNT(1)` | | Total of a column | `SUM(source.amount)` | | Distinct count | `COUNT(DISTINCT source.customer_id)` | | Ratio of two aggregates | `SUM(source.amount) / COUNT(DISTINCT source.order_id)` | | Conditional total | `SUM(source.amount) FILTER (WHERE source.status = 'O')` | A rolling or cumulative metric is a window measure. Each `window` entry needs `order`, a field that must be deterministic, and `range`. Supported `range` values: `current`, `cumulative`, `trailing `, `leading ` and `all`. `` is `day`, `days`, `month`, `months`, `year` or `years`, and needs a date or timestamp `order` field. The `inclusive` and `exclusive` modifiers need 18.1; the default is `exclusive`. ```yaml measures: - name: rolling_7day_customers expr: COUNT(DISTINCT source.customer_id) window: - order: order_date range: trailing 7 day ``` ## Quoting rules for every expression Run these on every `expr`, `filter` and `'on'` value before returning the draft. - **Colon.** YAML reads an unquoted colon as a key-value separator. Wrap any expression that contains a colon in double quotes: `expr: "CASE WHEN tier = 'Enterprise: Premium' THEN 1 ELSE 0 END"`. - **Leading backtick.** A YAML value cannot start with a backtick. Column names with spaces or special characters go in backticks, and if the expression starts with a backtick, wrap the whole expression in double quotes. - **Backtick later in the expression.** ``CONCAT(`First Name`, ' ', `Last Name`)`` needs no outer quotes, because it does not start with a backtick. - **Multi-line.** Put `|` after `expr:` and indent every line at least two spaces beyond the `expr` key. The documented Create example uses `|-` for its `CASE` field. ## Step 7. Add agent metadata If `agents` is yes, add metadata to every field and measure people will ask about. It needs Runtime 17.3 and YAML 1.1. - `display_name`: the label shown in visualization tools. At most 255 characters. - `synonyms`: alternative names that help LLM tools such as Genie discover fields and measures. Up to 10 per field or measure, each at most 255 characters. Block style or flow style (`synonyms: ['revenue', 'total sales']`) both work. Draw them from the words in `metrics`. - `comment`: a description that appears in Unity Catalog. - `format`: how values display. Types: `number`, `currency` (needs `currency_code`, an ISO-4217 code such as `USD`), `percentage`, `byte`, `date` (needs `date_format`) and `date_time`. For `date_time`, at least one of `date_format` or `time_format` must be something other than `no_date` or `no_time`. In the editor, format is available only for numeric, date and datetime columns. ```yaml - name: total_revenue expr: SUM(source.amount) comment: Gross revenue from all orders display_name: Total Revenue synonyms: ['revenue', 'total sales'] format: type: currency currency_code: USD decimal_places: type: exact places: 2 ``` Two warnings to pass on. Saving a version 1.1 definition removes single-line `#` YAML comments, so move anything worth keeping into `comment` fields. Running `ALTER VIEW` removes Unity Catalog comments unless they are in the YAML `comment` fields. ## Step 8. Decide whether materialization is allowed Only if `materialize` is yes. Check these first, and if any fails, leave the block out and say which rule blocked it: - The workspace has serverless compute enabled (it runs a Lakeflow pipeline) and the compute is on Runtime 17.3 or above. - Neither the view nor any source table uses row filters, column masks or ABAC policies. A materialization is computed once with the owner's identity, so serving it would bypass per-user controls. - No `filter`, field or measure uses an invoker-dependent expression such as `current_user()` or `is_member()`. Create, alter and refresh fail with `METRIC_VIEW_MATERIALIZATION_WITH_INVOKER_DEPENDENT_EXPRESSIONS_NOT_SUPPORTED`. - The view defines no `parameters`. If it is allowed: ```yaml materialization: schedule: every 6 hours mode: relaxed materialized_views: - name: revenue_by_month type: aggregated dimensions: - order_month measures: - total_revenue ``` - `mode` is required and must be `relaxed`. `materialized_views` is required. - List fields under `dimensions:` even when the top level uses `fields:`. - `type` is `aggregated` (needs `dimensions`, `measures` or both) or `unaggregated`. At most one `unaggregated` entry per view, and it takes no `dimensions` or `measures`. - `schedule` is optional and does not support `TRIGGER ON UPDATE`. Without it, refreshes are manual: `REFRESH MATERIALIZED VIEW `, which needs 18.0. - Tell them what they are trading. `relaxed` mode does not check freshness, so a query that matches a materialization reads the last refresh while one that does not reads live source data. `COUNT(DISTINCT)`, measures with several aggregates, and window measures serve exact matches only, never rollups. - After a materialization exists, the owner cannot change, and group ownership is not supported. ## Step 9. Wrap it in CREATE VIEW Put the YAML between `$$` delimiters inside `CREATE VIEW` with the `WITH METRICS` clause, as the documented example does: ```sql CREATE OR REPLACE VIEW .. WITH METRICS LANGUAGE YAML AS $$ version: 1.1 comment: "" source: .. joins: - name: source: .. 'on': source. = . filter: fields: - name: expr: display_name: