omni-query

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.

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

Discovering Commands

omni query --help              # List query operations
omni query run --help          # Show flags for running a query
omni query run --schema        # Request body and response shape
omni query run --schema --field query.filters  # One sub-field, e.g. the filter union
omni ai --help                 # AI-powered query generation

Known Issues & Safe Defaults

• 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.
• 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.
• Calculated columns are table calculations. Running totals, moving averages, percent change, row totals, tier labels, VLOOKUP, SUMIF and date differences go in calculations[]. Write swallow_errors: false explicitly — omitting it defaults to true, which can turn a failed calc into #ERROR! cells or plausible wrong values on a COMPLETE query. Treat a blank, #ERROR! or suspiciously uniform calc column as a failed calc, and prefer the documented Omni operators: references/table-calculations.md.
• 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.

Build queries on a topic

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 adding to 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 adding to" 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.

Composite topics. A composite has no base view: send join_paths_from_topic_name: <composite> and no table, and address its fields with @ names: references/query-parameters.md.

Running a Query

Basic Query

omni query run --body '{
  "query": {
    "modelId": "your-model-id",
    "table": "order_items",
    "fields": [
      "order_items.created_at[month]",
      "order_items.total_revenue"
    ],
    "limit": 100,
    "join_paths_from_topic_name": "order_items"
  }
}'

Query Parameters

modelId goes inside query on every query run (for a branch or workbook model, use that model's id), and a date field supports a timeframe in brackets: [date], [week], [month], [quarter], or [year]. Pivoted queries reject limit: null unless the body sets a top-level resultType, so otherwise give them a numeric limit; transposed_measures takes an array of measure names, and true returns an empty result. query-parameters.md has the full parameter list, sorts, pivots, and common query patterns.

Filters

Each query.filters entry maps a field name to a typed object such as { "type": "string", "kind": "EQUALS", "values": ["California"] }, and a boolean filter uses is_negative with no kind or values. An object with the wrong properties for its type is silently ignored, so confirm that the filter bound in summary.display_sql (with cache: "SkipCache") or by a changed row count. Operators by type are in query-parameters.md, and every filter type is in filter-expressions.md.

Table Calculations

Table calculations are AST objects in calculations[], and each calc_name must also be in query.fields, or the column never shows. Copy each request's AST from table-calculations.md; after lifting a calc from an agentic job's generate_query action (result.query), re-run your assembled query and compare its values with the job's csvResult. Query tasks are read-only unless the user explicitly asks to change the model: when a field seems missing, find the right model, topic, or branch or report the missing field, and do not create branches, add measures, or edit YAML to make a query work.

Running Raw SQL (userEditedSQL)

Use userEditedSQL only when no topic can express the query or the user asks to run their SQL as-is; it is the most permissive non-topic pathway, so keep it to ad-hoc reads by privileged users and strip it from any reused or dashboard query. A ${}-templated userEditedSQL from generate-query does not run through query run; treat it as a sign that the topic lacks a measure. An unbounded raw query stops at 50,000 rows whatever the envelope limit, so put LIMIT in the SQL; raw-sql.md has the body shape and options.

Request-level options (outside query)

Request options such as resultType, cache, userId, branchId, and planOnly go at the top level of the body beside query; a key placed inside query is silently dropped, except branchId, which is rejected there. Validate with a JSON-mode run before showing the human-mode table or --chart view, and when workbookUrl or --workbook returns no link, the user lacks the workbooks permission, so do not retry. request-options.md lists every option and how to confirm an aggregate table served a query.

Handling and Validating Results

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 {"job_id":…,"status":…,"summary":{…}} line per job, then a {"remaining_job_ids":[…],"timed_out":"false"} footer. (With a top-level resultType the body is a single JSON/CSV document instead.) A naive json.loads(entire_stdout) throws JSONDecodeError: Extra data. Don't write a single-object parser — iterate lines and pick the ones with job_id, or slurp with jq -s. The last line is the footer, not the result. --compact puts each object on one tidy line.

Without resultType, each job line carries the rows as base64 Arrow in result, unreadable inline. The row count is cache_metadata.num_rows (not summary.row_count); summary has invalid_calculations, missing_fields and display_sql. omni query wait --schema describes the job line and footer.

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.

Check every response before trusting or presenting it, using the checklist below, and flag an empty result to the user. handling-results.md has the commands for each check and for decoding Arrow.

Validation Checklist

Long-Running Queries

If the footer's remaining_job_ids is non-empty (timed_out is "true"), poll those ids:

omni query wait --job-ids job-id-1,job-id-2

JSON mode never polls for you: the stream ends at the wait window, and a non-empty remaining_job_ids is the only sign the result is incomplete. (Human-mode output on CLI ≥ 1.3.0 does poll.)

Streaming large results (CLI ≥ 1.5.0): omni query wait --completion-only true completes a job without its result, or set "completionOnly": true at the body's top level on query run, never with resultType; then omni query results reads the result in slices for about an hour. handling-results.md has the steps.

Running Queries from Dashboards

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" }'

Run a saved query as it is saved (CLI ≥ 1.5.0). omni documents run-query <identifier> <queryId> runs one of the document's saved queries server-side and returns a single JSON / CSV / XLSX document — the query body is read on the server, so the caller can only replace or add filters for the run. A tile on the dashboard (or any query of a document with an app) runs for anyone who can view it; a hidden tile or an off-dashboard tab needs workbook access. <queryId> is the tile's queryIdentifierMapKey or its query presentation id from omni documents get-queries. Per-run filters are keyed by field name; a filter that cannot be applied fails the run with a 400 rather than running without it. With an org-scoped key, --user-id runs as that member, on get-queries too. Prefer this over reconstructing the full query run body when you want the saved behavior intact.

AI-Powered Query Generation

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

omni ai generate-query <modelId> "<question>" generates and runs a query (--run-query false returns only the query JSON), and omni ai pick-topic shows the topic the AI would pick for a question; see ai-query-generation.md for the response shape and flags.

Agentic Queries (async)

Submit with omni ai job-submit, poll omni ai job-status on its state field, and read omni ai job-result (resultSummary is the narrative; actions[] holds each step). The poll loop must stop on every terminal state (COMPLETE, FAILED, CANCELLED) and never be a fixed-count loop that breaks only on success; before presenting the answer, check each actions[] entry, and if a required generate_query action is pending, partial, or failed, the summary is not final, so run or regenerate that query and present only validated results. ai-query-generation.md has the loop code and the follow-up commands (submit feedback once per job).

Using Job Results in a Dashboard

Before a job's query becomes a dashboard queryPresentation, always set userEditedSQL to "" (a tile query requires the key). When calculations[] is empty, rebuild the calc from the action's result.csvResultFields, except aggregates such as SUM, which become filtered measures in the model; job-result-to-presentation.md has the full algorithm.

When to Use Which Approach

Use omni ai job-submit for anything non-trivial, including generating a table calc or reusable query AST (append "as a table calculation" to the prompt when you want one), and omni ai generate-query for simple queries or for shape-only drafts when query execution isn't permitted. The comparison table is in ai-query-generation.md.

Multi-Step Analysis Pattern

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

Known Bugs

• Bare-string filter values are rejected with 400 "Unable to parse data stream". Use the typed filter object: references/filter-expressions.md.

Linking to Results

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}

Docs Reference

Omni docs pages for queries, documents and AI: references/docs-links.md.

Related Skills

• 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