Run your first metric query¶
This tutorial uses the dosi CLI directly, with no agent. In about ten
minutes you'll see what the bundled orders model defines, compile a query to
SQL for two different warehouses, then load its sample data into a local
DuckDB file, run the query and check the numbers. Each step shows the output
to expect. To do the same by talking to a coding agent, see the
quickstart.
The orders example has four tables (customers, orders, products and
order_lines), seven metrics and 41 orders.
Before you start¶
- Dosi, installed manually. Steps 1 to 5 need nothing else.
- The DuckDB CLI, to load the sample data in Step 6. Dosi runs the queries itself, with the DuckDB built into it; the lean build runs this CLI instead.
Point DOSI_EXAMPLES at the bundled examples, and work in a new folder:
That path is where the install script puts the examples. If you unpacked a
release archive yourself, use its examples/ directory; dosi info prints
the path either way. If your results differ from the ones below, your
examples come from an older release: re-run the install script.
Step 1 — See what the model offers¶
NAME KIND DATASETS DESCRIPTION
revenue aggregate orders Total order amount
order_count aggregate orders Number of orders
unique_customers aggregate orders Distinct purchasing customers
avg_order_value ratio orders Revenue per order (ratio)
total_margin expression order_lines, orders Revenue minus cost (expression over two aggregates)
line_count aggregate order_lines
line_product_margin expression order_lines, products
Each metric is defined once in model.yaml, as SQL over the model's
datasets. Nobody declared the KIND column: Dosi infers it from each
expression, telling a plain aggregate from a ratio of two aggregates or an
expression that combines them. To see one metric in full:
metric: revenue
description: Total order amount
kind: aggregate
expr: SUM(orders.amount)
datasets: orders
group_by: orders, customers
time: metric_time (day, week, month, quarter, year)
group_by names the datasets whose fields can break revenue down. Step 9
shows what happens when you ask for one that is not listed.
Step 2 — Confirm the model is valid¶
validate checks the model's structure, that names are unique, that every
relationship points at real fields, and that every metric compiles. When you
write a model of your own, run it after every edit.
Step 3 — See what you can group by¶
NAME TIME DIM SOURCE DESCRIPTION
customers.region yes inferred
customers.signup_date time yes inferred:time
orders.order_date time yes inferred:time
orders.status yes inferred
products.category yes inferred
customers.customer_id no inferred:primary_key
order_lines.cost_amount no inferred:measure
order_lines.line_amount no declared
order_lines.line_id no inferred:primary_key
order_lines.order_id no inferred:foreign_key
order_lines.product_id no inferred:foreign_key
order_lines.qty no declared
orders.amount no inferred:measure
orders.customer_id no inferred:foreign_key
orders.order_id no inferred:primary_key
products.product_id no inferred:primary_key
products.unit_cost no declared
The fields marked yes under DIM are the ones meant for grouping and
filtering; keys and amounts are listed too, marked no. Every field is named
dataset.field, and that full name is what --group-by and --where take.
A time field also takes a grain, such as orders.order_date:month.
Step 4 — Compile your first query to SQL¶
dosi query --model "$DOSI_EXAMPLES/orders/model.yaml" \
--metrics revenue --group-by orders.status --pretty
SELECT
orders.status AS status,
SUM(orders.amount) AS revenue
FROM orders AS orders
GROUP BY
orders.status
You asked for a metric by name, and Dosi wrote the SQL. Without --execute
it only compiles, so this step needs no database.
Step 5 — Switch warehouses without changing the model¶
Ask for revenue by month, compiled for MySQL:
dosi query --model "$DOSI_EXAMPLES/orders/model.yaml" \
--metrics revenue --group-by orders.order_date:month --dialect mysql --pretty
SELECT
STR_TO_DATE(DATE_FORMAT(orders.order_date, '%Y-%m-01'), '%Y-%m-%d') AS order_date__month,
SUM(orders.amount) AS revenue
FROM orders AS orders
GROUP BY
STR_TO_DATE(DATE_FORMAT(orders.order_date, '%Y-%m-01'), '%Y-%m-%d')
Run it again with --dialect duckdb, the default, and the month becomes
DATE_TRUNC('MONTH', orders.order_date). The request and the model stay the
same; only the SQL changes. --dialect changes nothing but the SQL text, so
this step does not connect to MySQL. Connect a warehouse
lists every dialect.
Step 6 — Run it for real¶
Load the sample data into a new DuckDB file, then add --execute:
duckdb orders.duckdb < "$DOSI_EXAMPLES/orders/seed.sql"
dosi query --model "$DOSI_EXAMPLES/orders/model.yaml" \
--metrics revenue,order_count --group-by orders.status --order status \
--execute --db orders.duckdb
--execute runs the compiled SQL against the database file that --db
names. On a warehouse you pass --connection <name> instead; see
Connect a warehouse. Add --format json for a
machine-readable result.
Load the seed once, into a new file. A second run reports that the tables
already exist but inserts every row again, doubling every number; if that
happens, delete orders.duckdb and load it again. The sample data is
deliberately untidy, like real data: some orders have no customer, some
customers have no region, and orders come in three statuses.
Step 7 — Break it down over time¶
dosi query --model "$DOSI_EXAMPLES/orders/model.yaml" \
--metrics revenue --group-by orders.order_date:month \
--start-time 2024-01-01 --end-time 2024-04-01 \
--order order_date__month --execute --db orders.duckdb
order_date__month revenue
2024-01-01 00:00:00 2059
2024-02-01 00:00:00 1059
2024-03-01 00:00:00 33
3 rows
--start-time and --end-time bound the metric's time axis, here the order
date. The start is included and the end excluded, so this is January to
March. The month column is named order_date__month, the field followed by
its grain, and --order takes that output column name.
Step 8 — Try a ratio metric¶
dosi query --model "$DOSI_EXAMPLES/orders/model.yaml" \
--metrics revenue,order_count,avg_order_value --execute --db orders.duckdb
avg_order_value is SUM(orders.amount) / COUNT(orders.order_id), and the
two columns beside it let you check it: 23835.49 ÷ 41 = 581.35…. Dosi
computes both aggregates at the query's grain and then divides, so the ratio
stays right whatever you group by.
The total includes the cancelled and pending orders. The model's revenue
has no status filter, so a query only gets one by asking for it: add
--where "orders.status = 'completed'" to count completed orders alone.
Business filters belong in the model or in the query, never in a guess.
Step 9 — Ask for a breakdown that would double-count¶
Break revenue down by product category:
dosi query --model "$DOSI_EXAMPLES/orders/model.yaml" \
--metrics revenue --group-by products.category --execute --db orders.duckdb
error: FanOutRisk: metric "revenue" aggregates Sum over dataset "orders", which has no many→one path to ["products"]; evaluating a duplicate-sensitive aggregate (SUM/AVG/COUNT) across that join would double-count rows
hint: group by dimensions reachable from the metric's dataset, or restate the metric with a duplicate-insensitive aggregate (COUNT DISTINCT/MIN/MAX)
An order can hold several products, so the join from orders through
order_lines to products repeats each order once per line, and
SUM(orders.amount) would count it several times. That is why Step 1 listed
only orders, customers under group_by. Rather than return an inflated
number, Dosi refuses and says why. A metric defined on the order lines can be
broken down by category:
dosi query --model "$DOSI_EXAMPLES/orders/model.yaml" \
--metrics line_count --group-by products.category \
--order category --execute --db orders.duckdb
Where to next¶
- Query metrics: every option of
dosi query, including filters, limits, time windows and output formats. - Query detail rows:
dosi selectreturns rows rather than aggregates, through the same model. - Connect a warehouse: run the same queries on your own database.
- Level of detail and five commerce questions: metrics that fix their own grain.
- Quickstart: let a coding agent build a model for your data and answer through it.