omni-to-databricks-metric-view

Omni → Databricks Metric View

Converts an Omni topic into a Databricks Metric View by exploring the Omni model via API, translating its field definitions into the Databricks Metric View embedded YAML format, and executing via the Databricks CLI.

See FIELD-MAPPING.md for full before/after translation examples and YAML-REFERENCE.md for the complete YAML structure, aggregate type, and format mapping tables.

Prerequisites

# Verify the Omni CLI is installed — if not, ask the user to install it
# See: https://github.com/exploreomni/cli#readme
command -v omni >/dev/null || echo "ERROR: Omni CLI is not installed."

# Show available profiles and select the appropriate one
omni config show
# If multiple profiles exist, ask the user which to use, then switch:
omni config

Auth: a profile authenticates with an API key or OAuth. If whoami (or any call) returns 401, hand off — ask the user to run ! omni config login <profile> (OAuth 2.1 browser flow; it blocks ~2 min on the browser). Don't run config login yourself in a headless/CI session (no browser → timeout); on a local interactive machine you may. See the omni-api-conventions rule for profile setup (omni config init --auth oauth) and discovering request-body shapes with --schema.

Tip: Use -o json to force structured output for programmatic parsing, or -o human for readable tables. The default is auto (human in a TTY, JSON when piped).

Workflow

Step 1 — Gather Requirements

Ask the user:

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.

Step 2 — Explore the Omni Model

2a. Find the model ID

Identify the Shared Model and note its id. Always prefer the Shared Model over Schema or Workbook models.

2b. Fetch the topic file

From the topic file extract: base_view, joins, fields, always_filter, ai_context, sample_queries.

2c. Fetch the relationships file

2d. Fetch each view file referenced in the topic

For every view in base_view and joins:

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.

Step 3 — Identify Tables and Joins

Map view names to fully-qualified Databricks table references (catalog.schema.table):

The __ separator maps to schema (left) and table (right). Confirm the catalog prefix with the user.

The joins indentation defines the join chain — a view indented beneath another joins into its parent:

Find the dimension with primary_key: true in each view — list it first among that table's dimensions.

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

Step 4 — Resolve the Field List

Process inclusions first, then apply exclusions. Also remove any field with hidden: true unless explicitly included by name.

Step 5 — Build Join Definitions

Using the hierarchy from Step 3 and relationships.yaml, extract join columns from on_sql and build the on: clause. Use the view name as the join name.

Star schema (single-level):

Snowflake schema (multi-hop):

⚠️ 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.

Step 6 — Map Dimensions and Measures

For each field that survived Step 4, translate it using the rules below. See FIELD-MAPPING.md for full examples.

Dimension quick reference:

Measure quick reference:

Strip Omni's ${view.column} refs to bare column names (or join_name.column for joined fields). Use display_name for the Omni label, comment for description, and carry synonyms directly. See YAML-REFERENCE.md for format and aggregate type mapping tables.

If the topic has ai_context, carry it into the metric view's top-level comment.

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

Step 7 — Check for Existing Metric View

• View does not exist → use CREATE OR REPLACE VIEW ... WITH METRICS
• View already exists → use ALTER VIEW ... AS $$ ... $$

Step 8 — Generate and Execute the SQL

Write the SQL to a temp file:

Execute via the SQL Statements API (databricks sql execute does not exist in CLI v0.295.0+).

Write the request body as a JSON file (with the SQL embedded as a properly-quoted string) and pass it via --json @file. This avoids shell substitution of arbitrary SQL content into the command line:

Check the response for "state": "SUCCEEDED". If "state": "FAILED", read status.error.message and see the Troubleshooting section below.

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

Grant access:

Troubleshooting

When the SQL Statements API returns "state": "FAILED", read status.error.message:

If the error message is truncated, run the same statement with "wait_timeout": "5s" to get the full synchronous error response.

Critical Rules

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

Reference

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