omni-query

Run queries against Omni's semantic layer via the Omni CLI. Omni translates field selections into optimized SQL — you specify what you want (dimensions, measures, filters), not how to get it.

Tip: Use omni-model-explorer first if you don't know the available topics and fields.

# 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 use <profile-name>

# Confirm the active profile is authenticated and inspect your permissions:
omni whoami whoami

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 command and request-body shapes with --schema.

You also need a model ID and knowledge of available topics and fields.

omni query --help              # List query operations
omni query run --help          # Show flags for running a query
omni query run --schema        # Body schema + example — but the query object renders free-form; for its shape use the examples below
omni ai --help                 # AI-powered query generation

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).

• When handed SQL, default to reproducing its intent on a topic — read what the SQL does (grain, measures, filters, joins), then check whether a topic can express it (omni-model-explorer; omni ai pick-topic / omni ai generate-query --run-query=false). Reach for raw userEditedSQL only when no topic fits, or when the user explicitly asks to run their SQL as-is. See Running Raw SQL. Don't passthrough SQL by reflex (you're not a text-to-SQL generator), and don't force-fit a topic that doesn't match.
• When the user asks for a calculated column, table calculation, running total, moving average, percent change, row total, tier label, VLOOKUP, SUMIF, or date difference, satisfy it with a calculations[] table calc selected in query.fields. Do not substitute an existing model field, userEditedSQL, client-side math, or a narrative-only explanation unless the user explicitly asks for that alternative.
• Keep table-calc answers auditable. Don't call the task complete until the final answer names the calc column and shows that same calc_name in both query.fields and calculations[] (true even for simple operators like OMNI_RUNNING_TOTAL). Include a compact query-JSON excerpt — query.fields, the real calculations[] object with operators/operands intact, relevant pivots[]/limit, and the validation result — not a paraphrase like { "OMNI_OFFSET_MULTI over": "field" } or "computed via OMNI_RUNNING_TOTAL." Long CSV can bury the query shape; show a few rows plus the reusable shape, or say you're omitting the full JSON for brevity.
• If a calc query succeeds but the calc column is blank, treat it as a failed calc until proven otherwise. Re-check operand order, for_calc, date truncation, outside_pivot, and whether the calc_name appears in query.fields.
• Don't swallow calc errors while authoring or validating. Keep swallow_errors: false (the default) so a bad calc fails loudly with the real message. With swallow_errors: true, the column silently shows #ERROR! and the query still returns COMPLETE — easy to misread as data, a blank calc, or an engine bug. If you see #ERROR!, re-run with swallow_errors: false to surface the cause (often a referenced field missing from query.fields). When re-running a calc query you pulled from a document, dashboard tile, or omni ai job, run it verbatim — dropping a field the calc references manufactures an error that isn't the calc's fault. Reserve swallow_errors: true for a finalized tile that needs per-cell resilience, and validate it with false first. See references/table-calculations.md §5.11 & §6.5.
• Prefer the documented Omni calc operators over lower-level raw SQL/window ASTs when a template exists. For example, use Omni.OMNI_RUNNING_TOTAL, Omni.OMNI_PERCENT_CHANGE_FROM_PREVIOUS, and Omni.OMNI_FX_AVERAGE(Omni.OMNI_OFFSET_MULTI(...)) for moving averages instead of hand-authored window_call/LAG when the prompt asks for a table calculation.
• query run requires modelId INSIDE the query object — every time. A standalone body is {"query":{"modelId":"<uuid>", …}}; omit it and the call 400s with query.modelId: Invalid input: expected string, received undefined. This is the exact opposite of a v2 dashboard tile query (omni-content-builder), whose tile query must never carry modelId (the server anchors tiles to the document's workbook model). Don't let the tile rule bleed into standalone queries — tiles omit modelId; query run requires it. When running against a branch or a workbook/draft model, set modelId to that model's id (the branch model id or the draft's workbookModelId), not the shared model — and pass branchId at the top level only when the skill explicitly calls for it (a branch's own model id already resolves branch fields).
• To read result rows, set resultType:"json" (or "csv") at the body's TOP LEVEL — not inside query. Misplacing it inside query is silently ignored: you get the default base64-Arrow streaming envelope back (unreadable rows) and waste a round trip. See Handling and Validating Results.

Prefer building every query on a topic, not a bare base view. Topics carry the governed joins, labels, and access — and a query not built on a topic is not accessible to restricted queriers/viewers (it works for you as a modeler/admin but silently fails for restricted roles). Set the query table to the topic's base view and pass join_paths_from_topic_name: <topic>. And if you are a Restricted Querier (QUERY_TOPICS, no QUERY_FULL_MODEL — check whoami), topic-based isn't just preferred, it's the only option: a bare base-view query or a raw-SQL userEditedSQL query needs QUERY_FULL_MODEL, so every query you author and place into content must be topic-based.

How the join map resolves joined-view fields. table stays the topic's base view; join_paths_from_topic_name lets the topic's join map reach joined-view fields from it — e.g. to select users.state on an order_items-based topic, table stays order_items and the join comes from the topic; you do not set table: users. Omit join_paths_from_topic_name (or point table at a non-base view) and joined-view fields may fail to resolve or join wrong. Confirm the base view and every reachable join with omni models get-topic <modelId> <topic> — its base_view_name and join_via_map show the base view and the join path to each reachable view. (This is the canonical topic-query shape; omni-content-builder tiles and omni-model-builder validation queries use it too.)

Decide where the query should come from:

1. An existing topic answers it (its base view + a join-reachable view) → query that topic.
2. The field is on a join-reachable view but the topic doesn't expose it / lacks the join → propose extending the topic (add the relationship/join), then build it via omni-model-builder.
3. Fundamentally different subject, constraints, or audience → propose a new topic (see the "new topic vs extend" criteria in omni-model-builder). Prompt the requestor first, and build it on a branch.

Fallback — non-topic query pathways. Two pathways run outside any topic: a bare base view (table: + the global relationships file for joins) and raw SQL (userEditedSQL, see "Running Raw SQL"). Both share the same caveat: topic-scoped controls — access filters (row-level) and always_where — are not applied, and in a dashboard the tile is invisible to Viewer / Restricted Querier roles by default (handling a restricted audience is a content-permission concern — see omni-content-builder). The two pathways differ on object-level access grants: a bare-view query still enforces them, but raw SQL bypasses them too (it's the most permissive pathway). Prefer a topic when one fits; reach for a non-topic pathway only when nothing else expresses the query.

When the conclusion is "build or modify a topic," hand off to omni-model-builder to do it right.

Given SQL? Reproduce it through a topic first (see Known Issues). Express the SQL's intent through the semantic layer; reach for raw userEditedSQL only when no topic can express it (SQL-first migration, warehouse-specific SQL, one-off ad-hoc read) or the user asks to run it as-is. If a faithful reproduction would need a field/topic that doesn't exist but should, propose modeling it (omni-model-builder) rather than defaulting to raw SQL.

Reading generate-query output: when the topic lacks a measure the metric needs, generate-query returns a ${}-templated userEditedSQL (e.g. SUM(${view.sale_price}) AS sale_price_sum FROM ${Topic}) and lists its SQL-output aliases (like sale_price_sum) in fields. Those aliases are not model fields — don't strip the SQL and try to run them semantically (they won't resolve). And the ${Topic} token resolves only inside generate-query's own execution — the templated query is not directly runnable via query run or persistable as a dashboard tile (it errors with No such view "Order Items"). So don't reuse it as-is; treat it as a signal that the topic is missing a measure and add that measure (omni-model-builder) so the metric becomes a clean semantic field.

userEditedSQL is a non-topic query pathway — the same family as a bare-view query (see "Fallback — non-topic query pathways"). It's an escape hatch for SQL the semantic layer can't express; prefer a topic or semantic fields when they fit.

omni query run --body '{
  "query": {
    "modelId": "<model-id>",
    "fields": [],
    "userEditedSQL": "select count(*) as cnt from ECOMM.ORDER_ITEMS"
  }
}'

• fields must be present (an array; may be empty []); table is not needed. The SQL is authoritative — populated fields/table are ignored when userEditedSQL is set.
• Runs against the model's connection — write warehouse-dialect SQL with fully-qualified names, not field references.
• "rewriteSql": false runs your SQL verbatim (the default parses and re-emits it — re-quoting identifiers, aliasing projections into the view.field namespace). "dbtMode": true allows Jinja/dbt templating. (Both are camelCase; the query object is permissive, so a misspelled/snake_case key is silently dropped.)
• Permission-gated: the querier's role must permit manually-written SQL. Without it the job fails — error_type: "FORBIDDEN", "queries based on manually written SQL are restricted" — returned as HTTP 200 with the error in the job body, not a 4xx.
• Access behavior: raw SQL bypasses all model controls — object-level access grants, row-level access filters, and always_where — and is invisible to Viewer/Restricted Querier roles in a dashboard. It's the most permissive pathway; use only for ad-hoc reads by privileged users, and strip it from any reused/dashboard query (see "Using Job Results in a Dashboard").
• Row cap: an unbounded raw query is capped at 50,000 rows (the response returns 50,001 — the cap plus one truncation sentinel). The envelope limit is not applied to raw SQL; put LIMIT in the SQL itself to bound results.

These keys sit at the top level of the body, beside query, not inside it. The query object is permissive — a key you misplace inside query (e.g. resultType, branchId, cache) is silently dropped, not rejected, so the call "succeeds" while ignoring your option. The tell-tale for a misplaced resultType is getting the base64-Arrow envelope back when you asked for JSON/CSV.

omni query run streams NDJSON — it is NOT one JSON object. In JSON mode (pass -o json when parsing — a human default from config set-format or OMNI_OUTPUT_FORMAT applies even when piped, and prints a table instead) the CLI prints multiple JSON objects, one per line: first a {"jobs_submitted":{…}} line, then one or more {"job_id":…,"status":"COMPLETE","summary":{…}} job lines (and, with resultType, the result payload). A naive json.loads(entire_stdout) throws JSONDecodeError: Extra data. Don't write a single-object parser — iterate lines and pick the one you need, or slurp with jq -s / read the last non-empty line. --compact puts each object on one tidy line.

Default response: base64-encoded Apache Arrow table. Arrow results are binary — you cannot parse individual row data from the raw response. The row count is at cache_metadata.num_rows (not summary.row_count). The summary object holds validation metadata: invalid_calculations, missing_fields, display_sql (the compiled SQL), omni_sql_parse_failed. (--schema won't show any of this — it describes the request body only; response shape comes from a live response. See the omni-api-conventions rule.)

To read rows yourself, always set resultType: "json" (or "csv") at the body's TOP LEVEL — then stdout is a clean, directly-parseable JSON array (or CSV), with none of the Arrow/NDJSON envelope to unpack. This is the single reliable way to spot-check values; reaching for the default Arrow path and trying to decode rows from it is the common time-waster.

{ "query": { ... }, "resultType": "csv" }

resultType: "xlsx" is also valid, but it returns a binary .xlsx file (zip-based) — like the default Arrow blob, you can't read it inline without a spreadsheet app or a library. Use it only to deliver a file to a person, not to inspect results. For agent-side reading, stick to csv/json.

Extract and re-run queries powering existing dashboards:

# Get all queries from a dashboard
omni documents get-queries <dashboardId>

# Run as a specific user
omni query run --body '{ "query": { ... }, "userId": "user-uuid-here" }'

# Cache policy (valid values: Standard, SkipRequery, SkipCache, SkipCacheAndRebuildExtracts)
omni query run --body '{ "query": { ... }, "cache": "SkipCache" }'

Instead of constructing query JSON manually, you can describe what you want in natural language and let Omni's AI generate the query.

For complex analysis, chain queries:

1. Broad query — understand the shape of the data
2. Inspect results — identify interesting segments or patterns
3. Focused follow-ups — filter based on findings
4. Synthesize — combine results into a narrative

Time Series: fields + date dimension + ascending sort + date filter

Top N: fields + metric + descending sort + limit

Aggregation with Breakdown: multiple dimensions + multiple measures + descending sort by key metric

• Bare-string filter expressions are rejected. "filters": { "field": "complete" | "last 90 days" | "not null" } returns 400 "Unable to parse data stream" as of August 2026 (older builds returned 500 "Cannot use 'in' operator to search for 'query_id' in <value>" — the query-reference/field_name_in_query probe). Use the typed filter object (see Filters above). Reproduced for value, date, null-string, number, and boolean forms.

Queries are ephemeral — there is no persistent URL for a query result. To give the user a shareable link:

• For existing dashboards: {OMNI_BASE_URL}/dashboards/{identifier} (the identifier comes from the document API response)
• For new analysis: Create a document via omni-content-builder with the query as a queryPresentation, then share {OMNI_BASE_URL}/dashboards/{identifier}

• Query API · Running Document Queries · Querying Documentation · Filter Syntax

• omni-model-explorer — discover fields and topics before querying
• omni-content-explorer — find dashboards whose queries you can extract
• omni-content-builder — turn query results into dashboards
• omni-ai-eval — benchmark and test AI query generation accuracy