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.

Put plainly: an order has exactly one customer, so pulling customers.name in adds a column to the row — it never adds a row to the result. It is a lookup, the way VLOOKUP fills a column in a spreadsheet without changing how many rows the sheet has.

The other direction is where hand-written SQL quietly goes wrong. One customer has many orders, so joining from customers down to orders splits each customer into several rows — and a SUM over any customer-level column then counts that customer once per order. That is the classic double-count, and nothing errors: the query succeeds and the number is simply wrong. Dosi will not emit it. Ask for an order field from --from customers and the query is refused with detail_fanout, which hands back the grain that does answer the question (--from orders) instead of a duplicated result.

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.

unknown_query_key — the query carries a key it does not define, usually a misspelling. It is refused rather than ignored, because an ignored distinct or filter returns plausible rows that answer another question. candidates lists the keys the query defines.

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.

Level-of-detail fields¶

A D-LOD fixed field of the root is a value per key — active days per user, payments per player — and a detail query reads it like any other field: project it, filter on it, order by it. Each root row is joined with its key's row of the rollup, which has one row per key, so the value attaches to the row and the row count stays the root's.

$ dosi select --from sessions --fields sessions.user_id \
    --where "sessions.active_day >= 2" \
    --context-filter "sessions.channel_id = 2" \
    --start-time 2025-01-01 --end-time 2025-02-01 --distinct

The layers are the metric plane's (filter placement):

--start-time / --end-time and --context-filter are context. They filter the rows and also scope the rollup: above, the days are counted on channel 2 in January.
--where is a dimension filter. It picks rows once the value is attached and never reaches the rollup. Moving channel_id = 2 into --where counts January days on every channel, then keeps the channel-2 rows — a different list. A --where condition may mix a stored column and a value (active_day > 2 OR user_id = 'E').
--context-filter cannot read a level-of-detail field: it feeds the rollup that computes it (unsupported_filter).

A cross-dataset field works the same way — --where "flights.open_alert_count = 0" lists the flights with no OPEN alert — and a context condition reaches the other dataset only where it can read it. When a field has a window of its own on the root's dataset, keep the query's main window in --where, not the time range, or the two windows intersect inside the rollup. In this version the field must live on the --from dataset; one on a joined dataset is refused with the root to use.

A field's own window is its base's parameters, and --param NAME=VALUE ("params" elsewhere) binds them — January actives with no February login:

$ dosi select --from logins --fields logins.player_id --distinct \
    --where "logins.login_day BETWEEN 20250101 AND 20250131 AND logins.window_days = 0" \
    --param window_start=20250201 --param window_end=20250228

Each parameter takes one value, since a field is one column, and a parameter that no field the query reads declares is unknown_metric_param.

Duplicate rows¶

A row is one row of the root, so a list of ids read off a fact table repeats each id once per fact row: one line per login, not one per player. --distinct ("distinct": true on every other surface) drops the rows whose projected values coincide — SELECT DISTINCT:

$ dosi select --from logins --fields logins.player_id --distinct

It collapses rows; it does not change which rows exist, so a fan-out is still refused rather than deduplicated after the fact.

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. A per-entity value is the exception: declare it as a level-of-detail field and read it here.
A level-of-detail field must live on the --from dataset.