Skip to content

CLI reference

dosi is the command-line interface to dosi-engine: validate an Apache Ossie (formerly OSI) semantic model, browse its compiled metrics/datasets/dimensions, compile a metric or detail query to dialect SQL, and execute that SQL against a warehouse. Everything the REST API does over HTTP, dosi does from a shell — same compiler, same structured errors, same --format json machine contract.

$ dosi --help
Compile metric queries over OSI semantic models to dialect SQL

Usage: osi [OPTIONS] <COMMAND>

Commands:
  validate  Validate the model: structure, unique names, relationship integrity, and metric compilation
  list      List model objects
  query     Compile a metric query to SQL
  select    Query row-level detail: project fields at one dataset's grain, joining related datasets automatically
  explain   Show the compiled IR shape and logical plan without generating SQL

Installing

dosi ships in two builds: the default carries DuckDB's official libduckdb library beside the executables — files that travel together, nothing else to install — and the lean build drops it, running local DuckDB queries through a duckdb CLI on your PATH instead. Both include all warehouse connectors.

See Install for the choice, the download command, and checksum verification.

Dosi ships for Linux and macOS, and the binaries depend only on standard system libraries (glibc ≥ 2.28 on Linux, macOS 11 or newer); the default build adds libstdc++ through the libduckdb shipped next to it, while the lean build has no C++ dependency at all. TLS in the connectors is rustls, so there is no OpenSSL or database client library to install.

Global options

These apply to every command (clap global = true):

Flag Env Default Meaning
--model <path> DOSI_MODEL required Ossie model file (.yaml / .yml / .json)
--format <fmt> — text Output format: text, json, or arrow (see Output formats)
--connections <path> DOSI_CONNECTIONS discovery chain Connections file for --execute (see Executing against a warehouse)
--osi-datus — on Datus mode: DATUS nonportable_extensions are honored (datus-extensions.md)
--osi-basic — off Basic mode: strict standard Ossie — DATUS extensions ignored with a warning; validate also runs the upstream validator (see below)

--osi-datus and --osi-basic are mutually exclusive; passing both is a usage error. In basic mode every ignored extension prints a ! warning line on stderr (and lands in warnings under --format json); warnings never change the exit code.

--model may be omitted if DOSI_MODEL is set; a command that needs a model and finds neither exits with no model given; pass --model <path> or set DOSI_MODEL.

Exit codes: 0 success, 1 a query/execute the engine rejected (with a structured error), 2 a usage/CLI error (bad flag, missing model, unparseable argument).

Commands

dosi info

Reports the engine, Ossie spec and datus-ext versions, plus every vendor_name: DATUS nonportable_extensions key this engine reads. Needs no model, so it doubles as a version probe.

$ dosi info
dosi       0.2.2
osi spec   0.3.0.dev0
datus-ext  1.14 (accepts 1.0 and up)
mode       datus
build      engine (DuckDB in process)
examples   /home/you/.local/share/dosi/examples

KEY               EXTENSION     CARRIERS         SINCE  IF IGNORED
join_type         D-JOIN        relationship     1.0    documented
fill_nulls_with   D-FILL        metric           1.0    documented
time_dimension    D-TIME        dataset, metric  1.1    degraded
time_granularity  D-GRAIN       field            1.1    documented
window            D-WINDOW      metric           1.2    documented
dataset           D-DATASET     metric           1.1    degraded
derive            D-DERIVE      metric           1.4    degraded
measure           D-MEASURE     metric           1.4    silent
params            D-PARAM       metric, field    1.5    degraded
time              D-FORMAT      field            1.6    silent
is_dimension      D-DIM         field            1.7    documented
cardinality       D-CONFORM     relationship     1.8    silent
lod               D-LOD         field            1.10   degraded
concept_params    D-PARAM       mapping          1.12   degraded
determines        D-DETERMINES  field            1.14   degraded

build says which of the two published builds this is — engine runs DuckDB in process, lean shells local DuckDB out to a duckdb CLI on your PATH (see Which download?). Without this line, only ldd can tell an installed binary's two builds apart.

examples is where the bundled example models were found — $DOSI_EXAMPLES if you set it, else $XDG_DATA_HOME/dosi/examples, i.e. ~/.local/share/dosi/examples. The line is absent when neither is there. The examples on this page use $DOSI_EXAMPLES; export it once and every command below is copy-pasteable:

$ export DOSI_EXAMPLES=~/.local/share/dosi/examples

SINCE is the datus-ext version that introduced the key; IF IGNORED is what it costs a consumer that does not honor it — see Extension versions. --format json emits the same content as a machine contract, which is what a model-generating agent should read before choosing which keys to emit.

dosi validate

Loads the model and runs the full validation pipeline — structure, unique names, relationship reference integrity, and metric-expression compilation — then reports every issue.

$ dosi validate --model $DOSI_EXAMPLES/tpcds/model.yaml
✓ 1 semantic model(s) valid

--format json emits {issues, compile_errors, warnings} for CI gating: each issue carries a severity and message, each compile error a stable code and optional hint, each warning a stable code, location, and message. A model with errors exits non-zero; warnings alone do not.

Field-role advice (datus mode). In --osi-datus — the default — validation also reports fields whose role the model leaves undeclared and structure cannot infer: a measurement no metric aggregates, a business key that is neither a declared key nor a relationship column, a timestamp with no dimension.is_time. Each is a warning that names the field and what to write (usually is_dimension); left alone, those fields are offered as grouping dimensions and an agent will group by them. The check always runs — there is no flag to turn it off — and --strict makes it count, for CI:

$ dosi validate --strict --model model.yaml
! underdeclared_field in field 'routes.distance_miles': `routes.distance_miles` looks like a
  measurement no metric aggregates, so it is offered as a grouping dimension; write the
  metric it belongs to, or declare `is_dimension: false`
✗ the authoring lint reported findings and --strict is set

Upstream validation (--osi-basic). In basic mode validate additionally shells out to the upstream apache/ossie reference validator when a checkout is found — OSSIE_DIR (default ~/src/ossie) containing validation/validate.py — holding the model to the published spec. That validator declares its dependencies as inline script metadata, so it runs through uv run --script when uv is on PATH and through python3 otherwise. An upstream failure fails the command. Anything that stops the validator from running — no checkout, no interpreter, or an interpreter without pyyaml / jsonschema / sqlglot — prints a note and falls back to built-in validation only, because "we could not ask" must not read as "the model is invalid". --format json adds "upstream": {ran, passed, output, note}.

Use the same Apache Ossie commit as Dosi's CI. It provides the schema and validator for 0.3.0.dev0. Dosi upgrades supported historical files before passing them to the validator. See One model per document.

$ git clone https://github.com/apache/ossie ~/src/ossie
$ git -C ~/src/ossie checkout a0d15d29e9c90f65e0978b8bb32619fd4ef42c5c
$ OSSIE_DIR=~/src/ossie dosi validate --osi-basic --model model.yaml
✓ upstream OSI validation passed
✓ 1 semantic model(s) valid

dosi list <what>

Browse the compiled semantic layer. Three subcommands, each a table in text mode or an array of objects under --format json:

Subcommand Columns
dosi list datasets name, source, primary key, field count, time dimensions
dosi list metrics name, inferred kind (aggregate / ratio / expression), datasets, description
dosi list dimensions [--metric <name>] dataset.field, time flag, whether it is worth grouping by (DIM), who decided (SOURCE), description

list dimensions without --metric lists every field of the model. With --metric it lists only what that metric can be grouped by, recommended first. DIM is no for a field the model declares (or the engine infers) is not a dimension — a measure column, a key, a relationship column — and SOURCE says which: declared for a D-DIM declaration, inferred:<rule> otherwise. A no row is still a legal --group-by; the flag is a recommendation, not a restriction.

$ dosi list dimensions --metric revenue --model $DOSI_EXAMPLES/orders/model.yaml
NAME                   TIME  DIM  SOURCE                DESCRIPTION
metric_time            time  yes  inferred:time
orders.status                yes  inferred
customers.region             yes  inferred
products.category            yes  inferred
orders.amount                no   inferred:measure
orders.customer_id           no   inferred:foreign_key
orders.order_id              no   inferred:primary_key

With --metric, the metric's own time column is reported once, as metric_time, and a conformed dimension once, under the metric's spelling.

$ dosi list metrics --model $DOSI_EXAMPLES/tpcds/model.yaml
NAME                     KIND        DATASETS               DESCRIPTION
total_sales              aggregate   store_sales            Total sales revenue across all transactions
total_profit             aggregate   store_sales            Total net profit from store sales
customer_lifetime_value  ratio       customer, store_sales  Average lifetime sales value per customer
sales_by_brand           aggregate   store_sales            Total sales by brand (requires grouping by item.i_brand)
store_productivity       expression  store, store_sales     Sales per employee across stores

dosi show <metric|dataset> <name>

One object in full — what list leaves out. --format json prints the same object MCP's describe_metric / describe_dataset and REST's /v1/metrics/{name} / /v1/datasets/{name} return.

Subcommand Shows
dosi show metric <name> description, kind, expr (how it is computed: an aggregate, <base> FILTER (WHERE …), a compose's arithmetic, a level-of-detail declaration), datasets, the datasets it can be grouped by, every parameter a query can bind for it (with the metric that declares one it does not), required dimensions, time grains
dosi show dataset <name> source, description, time axis, primary key, each field with its expression (a fixed field with its fixed(base, keys…) declaration, the datasets it reads and the time axis the period lands on), relationships seen from this dataset
$ dosi show metric revenue --model $DOSI_EXAMPLES/orders/model.yaml
metric: revenue
description: Total order amount
kind: aggregate
expr: SUM(orders.amount)
datasets: orders
group_by: orders, customers, products
time: metric_time (day, week, month, quarter, year)

An unknown name exits non-zero with unknown_metric / unknown_dataset and the names that do exist.

dosi query

The core command: compile a metric query into dialect SQL, and optionally run it. The query is described by the shared query spec flags below; on top of those, query takes:

Flag Meaning
--dialect <name> Target SQL dialect (default: the --connection profile's; else the default: true profile's; else duckdb)
--pretty Pretty-print the generated SQL
--explain Also print the logical plan above the SQL
--execute Run the compiled SQL against a warehouse and print the rows
--connection <name> Named profile from the connections file (implies its dialect)
--db <path> DuckDB file for --execute without --connection (default: in-memory)

Compile only (no --execute) prints the SQL (text) or {dialect, sql} (json). Without --dialect, --connection or --db it compiles for the connection marked default: true (reading only its type: no secret is resolved and nothing is connected), so query and query --execute emit the same SQL; stderr names the dialect it picked. A DWS default still needs --dws-mode. With no default connection it compiles for DuckDB:

$ dosi query --model $DOSI_EXAMPLES/tpcds/model.yaml \
    --metrics total_sales \
    --group-by store.s_state,date_dim.d_date:month \
    --where "item.i_category = 'Books'" \
    --start-time 2024-01-01 --end-time 2025-01-01 \
    --order -total_sales --limit 100 \
    --dialect starrocks
SELECT store.s_state AS s_state, DATE_TRUNC('MONTH', date_dim.d_date) AS d_date__month, ...

(Combining total_sales with a store-based metric like store_productivity here would be a fan_out_risk error — a per-store measure can't be grouped by a date the store doesn't reach. That protection is the point; see semantics.md §6.)

A condition some part of the query cannot read is not dropped silently: with --format json the result lists it under warnings, and text output prints each warning on stderr. See filters (S-FILTER-5).

Query spec flags (shared with explain)

Flag Meaning
--metrics <a,b,…> Required. Comma-separated metric names
--group-by <items> Comma-separated dataset.field or dataset.field:grain (grain: day\|week\|month\|quarter\|year); metric_time[:grain] groups by each metric's primary time dimension
--where <sql> Scalar boolean SQL, placed per AND conjunct: a condition on a selected metric filters the result rows (before --order / --limit); in a window query so does one on group-by fields only, so ranks and partition counts stay those of the whole population; anything else filters rows before aggregation. See filters.
--context-filter <sql> Row-level SQL that scopes the calculation population before aggregation, including each fixed LOD input that can read its columns. See LOD filters for its difference from ordinary --where.
--start-time <YYYY-MM-DD> Inclusive lower bound of a time range
--end-time <YYYY-MM-DD> Exclusive upper bound
--time-dimension <field> Which time dimension the range applies to (default: the only time dimension in the group-by, else each metric's primary time; metric_time says so explicitly). Every level-of-detail rollup reads the period on its own dataset's time field.
--order <keys> Comma-separated order keys; a - prefix means descending. NULLs sort last on every warehouse; a trailing nulls first puts them first: --order "-revenue nulls first". See Ordering
--limit <n> Row limit
--param <name=value[,value…]> Bind a D-PARAM parameter (repeatable). Values are typed by the metric's declaration; a comma list expands the metric into one column per value; quote a string value that contains a comma. For a multiple parameter the comma list is one set, and each repeated --param adds a set (one column each). See Parameterized metrics

How grains name their output columns, what an order key may name, how time ranges and metric_time behave, and where each --where condition lands are covered with examples in Query metrics.

dosi select

Compiles a detail query: row-level records at one dataset's grain, rather than aggregated metrics. Same planner, same relationship graph, same structured errors — see Query detail rows for the full guide.

$ dosi select --model $DOSI_EXAMPLES/orders/model.yaml \
    --from orders \
    --fields orders.order_id,orders.amount,customers.region \
    --where "customers.region = 'east'" \
    --order -amount --limit 20 --execute --db orders.duckdb

It takes the same --dialect / --pretty / --explain / --execute / --connection / --db flags as query, plus its own spec:

Flag Meaning
--from <dataset> Required. The result grain: one row per row of this dataset. Not a SQL FROM clause — other datasets are joined automatically.
--fields <a,b,...> Required. field, dataset.field, or relationship[.relationship…].field, each with an optional :grain on a time field.
--where <sql> Row-level filter. May reference datasets the projection does not, which joins them. Aggregates are rejected; a level-of-detail field of the root is allowed.
--context-filter <sql> Filters the rows like --where and also scopes every level-of-detail rollup the query reads. See level-of-detail fields.
--start-time / --end-time Half-open [start, end) window.
--time-dimension <field> Which time field the window applies to; defaults to the root dataset's primary time dimension.
--order <k,-k,...> Order keys, naming projected columns (- prefix = descending; NULLs last unless the key ends in nulls first).
--limit <n> Default 100, clamped to 10000.
--distinct Drop rows whose projected values coincide (SELECT DISTINCT) — an id list read off a fact table.
--param NAME=VALUE Bind a parameter of the base of a level-of-detail field the query reads (repeatable, one value each, or one set NAME=V1,V2 for a multiple parameter). See level-of-detail fields.

Three behaviors worth internalizing:

  • Output columns drop the root prefix and join the rest with __: orders.amount is amount, customers.name is customers__name.
  • A one-to-many traversal is refused, not silently duplicated. Asking --from customers --fields orders.order_id returns detail_fanout with the root to use instead.
  • When two relationship paths reach a field, name the one you mean by prefixing it — the ambiguous_join_path error's suggested_retry prints both spellings ready to paste. The same prefix works in query --group-by.

dosi explain

Takes the same query spec flags as query but stops at the logical plan — no SQL is generated. Useful for understanding join paths, fan-out branch assignment, and grain handling before you pick a dialect.

$ dosi explain --model $DOSI_EXAMPLES/tpcds/model.yaml \
    --metrics customer_lifetime_value --group-by store.s_state

The plan renders as text (including each join's kind, left / inner, so you can confirm a Datus join_type extension took effect). --format json applies only to the error path here — a successful plan is text-only.

dosi attribute-metrics and dosi attribute-dimensions

Explain a metric's change between two [start, end) periods. attribute-metrics splits it over the metric's formula; attribute-dimensions splits it by the groups of the fields in --dimensions. Each runs every query it needs itself. See Metric attribution for what comes back and how to read it.

$ dosi attribute-metrics --model model.yaml \
    --metric avg_order_value \
    --baseline 2025-01-01..2025-02-01 --current 2025-02-01..2025-03-01 \
    --db warehouse.duckdb
$ dosi attribute-dimensions --model model.yaml \
    --metric avg_order_value --dimensions customers.region,orders.status \
    --baseline 2025-01-01..2025-02-01 --current 2025-02-01..2025-03-01 \
    --db warehouse.duckdb

Periods are START..END ISO ranges on the metric's time axis (--time-dimension names another). --where, --context-filter, --param and --group-by are a metric query's own flags; --group-by is the grain the metric is computed at, not what the change is split by. Several --dimensions are one drill-down, level by level; --top-groups sets how many groups the result lists (default 20). --request FILE (or - for stdin) takes the whole request as JSON instead of the flags. --connection/--db pick the warehouse exactly like query --execute, and --dry-run compiles every statement without sending any. The result is JSON; a request that is not one prints its code and field_path on stderr and exits 1.

The earlier dosi attribute command still runs, with a deprecation warning, until it is removed; see Moving from attribute.

dosi lineage

The metric lineage graph — physical tables → datasets → atomic metrics → derived metrics, and the ontology's concepts when one is loaded — as JSON or as a page you can open. See Metric lineage for what the graph contains.

$ dosi lineage dump --model model.yaml > graph.json
$ dosi lineage view --mapping mapping.yaml

dump prints the graph JSON (--out <path> writes it to a file; the contract is JSON, so --format does not apply). view writes a single self-contained HTML page and opens it in your default browser; --out <path> picks where the page goes, and --no-open — or a machine with no browser — prints the path instead and still exits 0. The page needs no network access and opens over file://.

Both take --redact-sql, which replaces every SQL text and opaque extension payload with "<redacted>" while keeping the shape, for sharing a model's structure without its expressions. --osi-basic compiles under strict OSI and stamps "mode": "basic" on the output.

dosi migrate conf

Turns the warehouse connections you already keep for dbt (and MetricFlow, which reads the same profiles.yml) or Cube (.env) into dosi-connections.yaml. Needs no model.

$ dosi migrate conf
found: dbt  /home/me/.dbt/profiles.yml
  ✓ analytics.dev                snowflake   default
  ⚠ analytics.prod               snowflake   needs SNOWFLAKE_PROD_KEY_PASS
  ✗ analytics.sso                snowflake   unsupported_auth: target analytics.sso authenticates with browser SSO (authenticator), ...
wrote dosi-connections.yaml (gitignored)
Flag Meaning
--from dbt\|cube Read one tool (metricflow is an alias of dbt). Default: every one present
--detect Report what would be migrated; write nothing
--project-dir <dir> Where dbt_project.yml, profiles.yml and .env live (default: the current directory; parents are never searched)
--out <path> File to write or merge into (default dosi-connections.yaml)

The report never prints a field value — not a password, not even a host — only connection names, types, statuses, the names of unset environment variables, and reasons. What happens to the values is described in Connectors → From dbt or Cube. With --format json:

{
  "sources": [{"kind": "dbt", "path": "/home/me/.dbt/profiles.yml"}],
  "connections": [
    {"name": "analytics.dev", "dialect": "snowflake", "source": "dbt", "status": "ok",
     "missing_env": [], "code": null, "reason": null, "default": true}
  ],
  "written": "dosi-connections.yaml",
  "gitignored": true
}

status is ok, missing_env (written; set the listed variables) or unsupported (not written; code is unsupported_auth, unsupported_dialect or unsupported_config). A source carries a note when part of it cannot be migrated (a Cube driverFactory written in code). Under --detect, written is null. Naming a source with --from that is not there exits 1 with source_not_found.

dosi connection

The connections in the connections file. Neither subcommand needs a model or prints a field value.

$ dosi connection list
file: dosi-connections.yaml
  ✓ analytics.dev                snowflake   default
  ⚠ analytics.prod               snowflake   needs SNOWFLAKE_PROD_KEY_PASS
$ dosi connection test analytics.dev
✓ connected analytics.dev (snowflake, 812 ms)

list --format json prints one object per entry: name, dialect, source_file, default, available, missing_env and code (missing_env, invalid_config or null).

test <name> connects and runs SELECT 1; with --format json it prints {"name", "dialect", "ok": true, "latency_ms"}. A failure exits 1 with a JSON error on stderr — {code, message, hint, fields?, missing_env?, candidates?}. The message and hint are dosi's own words: a driver or parser message is never forwarded, because those quote values (a uri with a password in it, a host); fields names the settings involved instead. The code says what to do next:

code Meaning
missing_env An environment variable the entry references is unset; missing_env lists them
auth_failed The warehouse rejected the credentials
network_blocked The process could not resolve or open a connection at all — typically an agent sandbox without network access
connection_failed The warehouse could not be reached (refused, wrong host, missing DuckDB file)
timeout No answer in time
unsupported_dialect This build has no connector for the entry's type
invalid_config The entry does not parse
connection_not_found No entry by that name; candidates lists the closest

dosi workload check

Experimental: the JSON may gain fields.

Reads SQL you already have against the model. For each statement it says whether the same rows can be asked through dosi query or dosi select, and when they cannot, what the model lacks.

$ dosi --model model.yaml workload check queries/ --dialect duckdb
answerable  queries/sales_by_region_month.sql
  dosi query --metrics sales --group-by stores.region,order_items.order_date:month --where 'stores.store_id IS NOT NULL'
needs_metric  queries/region_pivot_by_month.sql
  add metric east_sales, over sales
  dosi query --metrics east_sales,sales --group-by order_items.order_date:month
unsupported  queries/aov_avg_of_orders.sql
  note aggregate_over_aggregate: the block aggregates an aggregated input

additions to the model
# metric east_sales, used by 1 statement(s)
- name: east_sales
  expression:
    dialects:
    - dialect: ANSI_SQL
      expression: SUM(CASE WHEN stores.region = 'East' THEN order_items.amount END)

3 statements: 1 answerable, 1 needs_metric, 0 needs_model, 1 unsupported
Verdict When What comes back
answerable every aggregate is a metric of the model, and the grouping, filters, order and limit fit a query the query
needs_metric the tables, columns and joins are in the model; an aggregate over them is not the metric as model YAML, and the query that uses it
needs_model a column, a grouping expression, a join or a table is not declared the field, relationship or dataset as model YAML, and the query once they are added
unsupported the statement is outside what is read today a reason code

The arguments are SQL files, or directories searched for .sql files. A file with several statements is checked statement by statement (script.sql#2). INSERT … SELECT, CREATE TABLE … AS and CREATE VIEW … AS are read as the query they write.

The comparison is on what a statement computes, not on how it is written. A join spelled with ON, USING, a comma and a WHERE, a derived table, a CTE, IN (SELECT …) or a correlated EXISTS is the same relationship; a metric is found whether the model wrote it as one expression or as a derive over other metrics. STRFTIME(d, '%Y-%m') and DATE_TRUNC('month', d) are a time field at the month grain. A GROUP BY with no aggregate returns its distinct keys, and lands on dosi select with distinct.

A join is only read as a relationship when the joined side is unique on the join columns: the model says so with a primary_key, a unique_keys entry or an existing relationship. An inner join also drops the rows that have no match, which a relationship does not, so the query gets the condition that says so (stores.store_id IS NOT NULL above). --facts takes it away.

Option Meaning
--dialect The dialect the SQL is written in. Default: the --connection's, else the default: true connection's, else duckdb
--facts <file> Facts about the tables that a model cannot state (below)
--execute Run each statement and its query on the connection and compare the rows. Exits 1 when a pair differs
--connection, --db Where --execute runs, as for dosi query

A facts file lists what the warehouse guarantees and the model has no place for. With a foreign key and a NOT NULL key column, an inner join needs no extra condition; with a nullable key the condition narrows to the key being present.

not_null:
  - {table: fact_order_item, columns: [store_id, amount, discount]}
foreign_keys:
  - {table: fact_order_item, columns: [store_id], references: dim_store, ref_columns: [store_id]}

--format json prints {summary, additions, statements}. Each statement has:

Field Content
statement The file, with #n for the n-th statement of a script
verdict answerable, needs_metric, needs_model or unsupported
plane, query metrics with the JSON of a metric query, or select with the JSON of a detail query: what the REST API, the Python Engine and the MCP tools take as is. With additions, the query assumes they are in the model
columns How each output column of the SQL reads off the query's result: {sql, query}, with format when the SQL spells a period as text (%Y-%m) and the query returns its first day, and constant for a column that reads no row
additions {kind, name, dataset?, yaml, based_on?, used_by}: one list entry of model YAML each. The top-level additions holds every one once, with all the statements that need it
equivalence keys: same, empty_groups or subset; values: same or null_zero (equal once NULL and 0 are read as one, as with SUM(CASE … ELSE 0 END) against a filtered metric). Inside a division, a group whose denominator is 0 reads NULL on one side and NaN or a division error on the other; detail says so when that can happen
proof The rewrite laws applied and the facts they used, each with where it came from
needs_facts Facts that would simplify the query, each with a probe_sql that returns a row exactly when the fact does not hold
executed rows_equal, rows_differ, error or not_run
reason, detail Why a statement is unsupported, as a code and in words

Two things hold for every result, and are what the engine's own tests check: adding the additions to the model and checking again gives answerable; and the query returns the statement's rows at the stated equivalence.

Not read yet, each with its reason: an aggregate over an aggregated subquery (aggregate_over_aggregate), aggregated subqueries joined to each other or to detail rows (join_of_aggregates), window functions (window_function), UNION (set_operation), a join that can multiply rows (fanout_join), NOT IN / NOT EXISTS and tests against rows with no known key (subquery_test), scalar subqueries (scalar_subquery), and deduplication (dedupe).

Refused because the query would return other rows:

  • aggregate_off_main: an aggregate of a joined table's column over the main table's rows, such as SUM(d.w) over facts f JOIN dims d. A metric aggregates at its own dataset, once per row of it.
  • integer_division: / between aggregates on a dialect where it truncates integers (PostgreSQL, Trino, Redshift and others), with neither side cast to a fractional type. A metric's ratio keeps the fraction.
  • unread_clause: * EXCLUDE, * REPLACE and * RENAME, along with QUALIFY, OFFSET and the other clauses a query has no place for.
  • query_surface: a query the engine does not take, such as one with no metric and no field. Every printed query is compiled before it is reported.

Output formats

--format takes one of three values:

  • text (default) — human-readable: an aligned table for rows/listings, the raw SQL for a compile, an indented tree for a plan. NULL cells render dimmed. Colors auto-detect the terminal.
  • json — every output (and every error) is machine-readable. A query compile is {dialect, sql}; --execute adds {columns, rows: [{col: val}], …}; validate is {issues, compile_errors}; list is an array of objects. Errors carry a stable code, the names involved, candidates where a bad reference has alternatives, and a suggested_retry where a rewrite would succeed — so agentic callers self-correct without parsing prose. Error codes are the stable API; error text is not.
  • arrow — an Arrow IPC stream on stdout, for query --execute only (any other command errors: --format arrow only applies to 'query --execute'). Result batches pass straight from the warehouse adapter to stdout with no row materialization — pipe them into DuckDB, Polars, or pyarrow with zero JSON parsing. Requires an Arrow-capable build (the default build, or any exec-*-arrow / exec-flightsql / exec-duckdb feature; a --no-default-features build without one rejects --format arrow at runtime).
# Stream results into DuckDB for further analysis
dosi query --model model.yaml --metrics revenue --group-by orders.status \
    --execute --connection prod-ch --format arrow \
    | duckdb -c "SELECT * FROM read_arrow('/dev/stdin')"

# Or into Polars
dosi query ... --execute --format arrow \
    | python -c "import polars as pl,sys; print(pl.read_ipc_stream(sys.stdin.buffer))"

Executing against a warehouse

--execute runs the compiled SQL and prints the rows. Without a connection it uses local DuckDB — in-process and Arrow-native by default (bundled exec-duckdb); --db <file> points at a DuckDB file, otherwise it is in-memory. With --connection <name> it targets that profile's warehouse and dialect.

$ dosi query --model model.yaml --metrics revenue --group-by orders.status \
    --execute --connection prod-sr

Connection profiles use the Datus agent.yml datasources: vocabulary. Point --connections at a full agent.yml (reads services.datasources) or a standalone datasources: file. Without the flag, the file is discovered in order: DOSI_CONNECTIONS env → ./dosi-connections.yaml → ./osi-connections.yaml → ~/.config/dosi/connections.yaml → ~/.config/osi/connections.yaml; the pre-rename osi- paths still resolve. A Datus agent.yml is read only when --connections names it. Secrets interpolate from the environment as ${VAR}.

datasources:
  prod-sr:
    type: starrocks
    host: sr.internal
    port: 9030
    arrow_flight_port: 9408      # opt into Arrow Flight SQL (SR ≥3.5.1)
    username: osi
    password: ${SR_PASSWORD}
    database: analytics
    default: true                # used by --execute without --connection
  prod-ch:
    type: clickhouse
    uri: http://ch.internal:8123
    username: default
    database: analytics

Resolution rules:

  • --execute without --connection uses the profile marked default: true; if none is marked, it falls back to local DuckDB (--db or in-memory). A default: true entry that failed to parse (e.g. an unset ${VAR}) warns on stderr rather than silently using empty DuckDB.
  • --dialect with --connection must agree with the profile's dialect, or the command errors — drop --dialect and let the profile decide.

Warehouse drivers are feature-gated if you build from source, so a hand-rolled binary only carries what you ask for (published builds carry them all). Build with the features you need (or exec-all):

Feature Engines Result path
exec-duckdb (default) DuckDB (in-process) Arrow-native
exec-mysql MySQL, TiDB, StarRocks, Doris (MySQL wire) rows
exec-postgres Postgres rows
exec-hologres Hologres (Postgres wire; implies exec-postgres) rows
exec-gaussdb GaussDB / openGauss (native SHA256 auth driver) rows
exec-oracle Oracle Database (ODPI-C; Instant Client at runtime) rows
exec-http ClickHouse, Trino rows
exec-http-arrow ClickHouse FORMAT ArrowStream Arrow-native
exec-flightsql StarRocks / Doris Arrow Flight SQL (arrow_flight_port:) Arrow-native
exec-snowflake Snowflake (SQL API v2 + key-pair JWT) rows
exec-bigquery BigQuery (jobs.query REST + service-account JWT) rows

Per-engine setup and the Arrow result-path status live in connectors.md and arrow.md.

See also

  • semantics.md — the normative behavior contract (metric inference, joins, fan-out protection, time handling) the CLI compiles to.
  • rest-api.md — the same capabilities over HTTP.
  • datus-extensions.md — the optional Datus model extensions (join_type, fill_nulls_with, time_dimension, time_granularity, time, dataset, and the larger ones), and extension versions for their versioning.
  • connectors.md — warehouse connector setup.
  • arrow.md — enabling Arrow result transfer and what gets faster.