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:
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.
--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.amountisamount,customers.nameiscustomers__name. - A one-to-many traversal is refused, not silently duplicated. Asking
--from customers --fields orders.order_idreturnsdetail_fanoutwith the root to use instead. - When two relationship paths reach a field, name the one you mean by
prefixing it — the
ambiguous_join_patherror'ssuggested_retryprints both spellings ready to paste. The same prefix works inquery --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.
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 asSUM(d.w)overfacts 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,* REPLACEand* RENAME, along withQUALIFY,OFFSETand 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.NULLcells render dimmed. Colors auto-detect the terminal.json— every output (and every error) is machine-readable. Aquerycompile is{dialect, sql};--executeadds{columns, rows: [{col: val}], …};validateis{issues, compile_errors};listis an array of objects. Errors carry a stablecode, the names involved,candidateswhere a bad reference has alternatives, and asuggested_retrywhere 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, forquery --executeonly (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 anyexec-*-arrow/exec-flightsql/exec-duckdbfeature; a--no-default-featuresbuild without one rejects--format arrowat 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:
--executewithout--connectionuses the profile markeddefault: true; if none is marked, it falls back to local DuckDB (--dbor in-memory). Adefault: trueentry that failed to parse (e.g. an unset${VAR}) warns on stderr rather than silently using empty DuckDB.--dialectwith--connectionmust agree with the profile's dialect, or the command errors — drop--dialectand 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.