Detail queries¶

Dosi answers two kinds of question over one semantic model.

Question	Command	Returns
What was revenue last month, by region?	`dosi query`	aggregated metrics
Show me the 100 largest orders from Enterprise customers	`dosi select`	rows

Both compile through the same planner, the same relationship graph and the same dialect backends. They differ in one operator: query aggregates, select projects.

$ dosi select \
    --from orders \
    --fields orders.order_id,orders.amount,customers.name \
    --where "customers.tier = 'Enterprise'" \
    --order -amount \
    --limit 100

You never write a JOIN. You name the fields you want; Dosi works out which datasets they live on, which relationships connect them, and whether that connection is safe.

`--from` is the grain, not the FROM clause¶

--from orders means one output row per order. It does not mean the SQL may only mention orders.

Fields, filters, ordering and the time range may all reference other datasets. Dosi collects every dataset the query needs, finds the relationship path to each, and emits the joins:

$ dosi select --from orders --fields orders.order_id --where "customers.region = 'US'"

customers is never projected, but the filter needs it, so it is joined. The result is still one row per order.

This works because Dosi follows relationships in one direction only: from the many side to the one side. Each hop attaches at most one row of the target to each row you already have, so the grain you asked for survives every join in the path.

Three ways to name a field¶

Spelling	Example	When
bare field	`amount`	the name is unique across the model
dataset-qualified	`orders.amount`	the usual case
relationship-prefixed	`orders_to_buyer.name`	you want to choose the join path

The third form matters when a model reaches the same dataset more than one way. Suppose orders links to regions both directly (where it shipped) and through customers (where the buyer lives). Asking for regions.region_name is genuinely ambiguous, and Dosi says so rather than picking:

$ dosi select --from orders --fields regions.region_name --format json
{
  "code": "ambiguous_join_path",
  "candidates": [
    "orders -[orders_to_customers]-> customers, customers -[customers_to_regions]-> regions",
    "orders -[orders_to_ship_region]-> regions"
  ],
  "suggested_retry": "name the path you mean by prefixing the field with its relationships, one of: orders_to_customers.customers_to_regions.<field> | orders_to_ship_region.<field>"
}

Paste one of those prefixes and the query runs:

$ dosi select --from orders --fields orders.order_id,orders_to_ship_region.region_name

Relationship prefixes work in --where too, at any path length, and on the metric plane's --group-by — the spelling is the same everywhere.

Output column names¶

An output column is the reference with the root prefix removed and each remaining . turned into __:

Reference (root `orders`)	Column
`orders.order_id`	`order_id`
`customers.name`	`customers__name`
`orders_to_ship_region.region_name`	`orders_to_ship_region__region_name`
`orders.created_at:month`	`created_at__month`

Root fields stay bare because that is what you asked about; everything else carries where it came from, so two name columns from two datasets cannot collide silently. Two fields that would produce the same column name are a duplicate_output_name error.

When Dosi refuses¶

The engine never returns rows it cannot vouch for. Three refusals are worth recognizing.

detail_fanout — you asked for a dataset that is only reachable against the join direction. One customer has many orders, so projecting an order field from --from customers would turn one customer into several rows:

$ dosi select --from customers --fields customers.name,orders.order_id --format json
{
  "code": "detail_fanout",
  "suggested_retry": "query at the finer grain instead: --from orders and project the customers fields you need"
}

The fix is in the message: ask the question at the finer grain. --from orders --fields orders.order_id,customers.name gives you the same information, one row per order, with no duplication.

ambiguous_join_path — several paths reach the field. Name one, as above.

metric_in_detail_query — a metric needs an aggregation, a time range and a grouping, none of which a detail query has. The message names the command that does have them.

Every refusal carries a machine-readable code, the names involved, and a suggested_retry. See Errors.

Time ranges¶

--start-time / --end-time apply a half-open [start, end) window. With no --time-dimension, the range routes to the root dataset's primary time dimension:

$ dosi select \
    --from orders \
    --fields orders.order_id,orders.amount \
    --start-time 2026-01-01 --end-time 2026-02-01

If the root dataset has no primary time dimension and the projection has no single time field, Dosi asks you to name one rather than guessing.

Row caps¶

--limit defaults to 100 and is clamped to 10000. This applies on every surface, so an agent that forgets a limit gets a sample, not a table scan.

Beyond the CLI¶

The same DetailQuery reaches every surface, with identical semantics and identical errors.

RESTMCPPython

$ curl -s localhost:8080/v1/select/execute \
    -H 'content-type: application/json' \
    -d '{"from":"orders","fields":["orders.order_id","customers.name"],"limit":10}'

Also POST /v1/select/compile and POST /v1/select/explain. See the REST API.

Tools select, compile_select and explain_select. See the MCP server.

engine.select({"from": "orders", "fields": ["orders.order_id"], "limit": 10})

Checking the join before trusting the rows¶

--explain prints the logical plan: the root, every join it chose, and the projection.

$ dosi select --from orders --fields orders.order_id,customers.name --explain
logical plan
Project[order_id, customers__name]
  JoinRelated[orders_to_customers -> customers, left]
    ReadDataset[orders]

Joins are LEFT by default, so a row with no match survives with NULLs rather than disappearing. A relationship may ask for INNER instead — see Control joins & null-fill.

Limits in this version¶

A query may reach each dataset by one path. Two named paths to the same dataset need two table aliases, which do not exist yet, so the engine refuses rather than quietly reading one join twice.
Order keys must name projected columns. Project the field you want to order by.
Metrics and fields cannot be mixed in one query. Run both planes and join the results yourself.