1. Which Omni topic do they want to convert? (e.g.,

   orders

   )
2. What is the Unity Catalog destination? (

   catalog.schema

   ) (e.g.,

   main.sales

   )
3. What is the Databricks SQL Warehouse ID? (run

   databricks sql warehouses list

   to find it)
4. Is this a new metric view or does one already exist at

   catalog.schema.[topic_name]_mv

   ?
5. Which Databricks CLI profile to use (optional — only if the user has multiple profiles)?

⚠️ STOP — Confirm all answers before proceeding. The metric view will be named

[topic_name]_mv

by default.

base_view

joins

omni models yaml-get <modelId> --filename <view_name>.view

If a view is prefixed with

omni_dbt_

, fetch the file starting with

omni_dbt_

. Skip any view backed by

derived_table.sql

— it has no physical table.

catalog.schema.table

ecomm__order_items

catalog.ecomm.order_items

omni_dbt_ecomm__order_items

catalog.ecomm.order_items

omni_dbt_

__

joins

joins:
  user_order_facts: {}          # skip — derived CTE
  ecomm__users: {}              # joins to base_view
  ecomm__inventory_items:       # joins to base_view
    ecomm__products:            # joins to inventory_items

primary_key: true

✋ STOP — Confirm the full table list and join hierarchy with the user before continuing.

relationships.yaml

on_sql

on:

name

joins:
  - name: ecomm__users
    source: catalog.ecomm.users
    'on': source.user_id = ecomm__users.id

joins:
  - name: ecomm__inventory_items
    source: catalog.ecomm.inventory_items
    'on': source.inventory_item_id = ecomm__inventory_items.id
    joins:
      - name: ecomm__products
        source: catalog.ecomm.products
        'on': ecomm__inventory_items.product_id = ecomm__products.id

⚠️

on

is a YAML 1.1 reserved word — always single-quote the key as

'on':

. Columns from nested (2+ level) joins cannot be used in

expr

— flatten them through a denormalized direct join instead.

expr: COLUMN

type: time

type: time

timeframes

DATE_TRUNC(...)

groups:

CASE WHEN ... END

bin_boundaries:

CASE WHEN

duration:

DATEDIFF(unit, start, end)

type: yesno

data_type

aggregate_type: sum/avg/max/min

SUM(col)

AVG(col)

aggregate_type: count

COUNT(*)

aggregate_type: count_distinct

COUNT(DISTINCT col)

MEASURE(measure_a) op MEASURE(measure_b)

filters:

AGG(col) FILTER (WHERE condition)

${view.column}

join_name.column

display_name

label

comment

description

synonyms

ai_context

comment

✋ STOP — Review all dimensions, measures, and join definitions with the user before generating the final output.

databricks api post /api/2.0/sql/statements \
  --json "{\"warehouse_id\": \"<WAREHOUSE_ID>\", \"statement\": \"SHOW VIEWS IN <catalog>.<schema> LIKE '%_mv'\", \"wait_timeout\": \"30s\", \"catalog\": \"<CATALOG>\", \"schema\": \"<SCHEMA>\"}"

• View does not exist → use

  CREATE OR REPLACE VIEW ... WITH METRICS

• View already exists → use

  ALTER VIEW ... AS $$ ... $$

-- CREATE (new view)
CREATE OR REPLACE VIEW catalog.schema.orders_mv
WITH METRICS
LANGUAGE YAML
AS $$
version: 1.1
comment: "..."
source: catalog.ecomm.order_items

joins:
  - name: ecomm__users
    source: catalog.ecomm.users
    'on': source.user_id = ecomm__users.id

dimensions:
  - name: id
    expr: id
    display_name: "Order ID"

  - name: status
    expr: status
    display_name: "Order Status"

measures:
  - name: order_count
    expr: COUNT(*)
    display_name: "Order Count"

  - name: total_sale_price
    expr: SUM(sale_price)
    display_name: "Total Sale Price"
    format:
      type: currency
      currency_code: USD
$$

-- ALTER (existing view)
ALTER VIEW catalog.schema.orders_mv AS $$
version: 1.1
...
$$

databricks sql execute

databricks api post /api/2.0/sql/statements \
  --json "{
    \"warehouse_id\": \"<WAREHOUSE_ID>\",
    \"statement\": $(cat /tmp/orders_mv.sql | python3 -c 'import json,sys; print(json.dumps(sys.stdin.read()))'),
    \"wait_timeout\": \"50s\",
    \"catalog\": \"<CATALOG>\",
    \"schema\": \"<SCHEMA>\"
  }"

"state": "SUCCEEDED"

"state": "FAILED"

status.error.message

✋ STOP — Confirm which group or user should receive access before running the GRANT. This is a permission change visible to others.

databricks api post /api/2.0/sql/statements \
  --json "{\"warehouse_id\": \"<WAREHOUSE_ID>\", \"statement\": \"GRANT SELECT ON VIEW catalog.schema.orders_mv TO \`group_name\`\", \"wait_timeout\": \"30s\", \"catalog\": \"<CATALOG>\", \"schema\": \"<SCHEMA>\"}"

1. Naming: Name the metric view

   [topic_name]_mv

   (snake_case, lowercase)
2. CREATE vs ALTER: Check for existence first —

   CREATE OR REPLACE

   for new,

   ALTER VIEW

   for existing
3. Version: Always use

   version: 1.1

   (requires Databricks Runtime 17.2+)
4. Skip derived CTEs: Views with

   derived_table.sql

   have no physical table — skip and warn the user
5. Confirm before executing: Show the full generated SQL to the user before running
6. Boolean fields: Map

   type: yesno

   as BOOLEAN dimensions — not filters.

   data_type

   is not a valid field — omit it
7. Composed measures: Use

   MEASURE()

   syntax; define atomic measures before composed ones
8. YAML quoting:

   on

   is a YAML 1.1 reserved word — always write

   'on':

   (single-quoted)
9. No SELECT *: All fields must be explicitly defined
10. MAP columns: Skip joins to tables containing

    MAP

    type columns — not supported
11. Nested join refs: Only direct star join columns (1 level) can be used in

    expr

    . Flatten snowflake schema joins through a denormalized direct join
12. Warehouse ID required: Always confirm before execution — cannot be inferred
13. Exclusions win:

    -view.field

    always overrides any wildcard inclusion
14. Format type values are lowercase:

    number

    ,

    currency

    ,

    date

    ,

    date_time

    ,

    percentage

    ,

    byte

15. Date format required:

    type: date

    and

    type: date_time

    both require

    date_format

16. Currency format: Use

    currency_code: USD

    not

    iso_code: USD

17. decimal_places

    unsupported: Omit it entirely — causes a parse error
18. CLI execution: Use

    databricks api post /api/2.0/sql/statements

    ;

    wait_timeout

    must be

    5s

    –

    50s

19. Omni CLI flag: Use

    --filename

    (not

    --file-name

    )
20. Field description key: Use

    comment:

    not

    description:

    —

    description

    is not a recognized field and causes a parse error

• Databricks Metric Views overview
• Create a metric view with SQL
• Metric view syntax reference
• Semantic metadata
• Joins
• Composability