Skip to content

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:

export DOSI_EXAMPLES=~/.local/share/dosi/examples
mkdir orders-tutorial && cd orders-tutorial

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

dosi list metrics --model "$DOSI_EXAMPLES/orders/model.yaml"
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:

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
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

dosi validate --model "$DOSI_EXAMPLES/orders/model.yaml"
✓ 1 semantic model(s) 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

dosi list dimensions --model "$DOSI_EXAMPLES/orders/model.yaml"
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
status     revenue   order_count
cancelled  10        1
completed  23825.49  39
pending    0         1
3 rows

--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
revenue   order_count  avg_order_value
23835.49  41           581.3534146341464
1 row

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
category  line_count
books     28
toys      25
2 rows

Where to next