Run your first metric query¶

In this tutorial you'll take a ready-made semantic model, explore its metrics, compile one to SQL, and run it against a real database — then watch the numbers come out exactly as expected. By the end you'll understand the whole dosi-engine loop: model → query → SQL → results.

It takes about 10 minutes. No prior OSI knowledge is assumed.

Prerequisites

You've installed dosi-engine and dosi validate --model fixtures/orders/model.yaml prints ✓ 1 semantic model(s) valid. Run the commands below from the repository root. (If dosi isn't on your PATH, read dosi as ./target/release/dosi.)

We'll use the bundled orders model — a tiny e-commerce example with three tables (orders, customers, products) and five metrics. It's deliberately small so you can check every number by hand.

Step 1 — See what the model offers¶

Start by asking the model what metrics it defines:

$ dosi list metrics --model fixtures/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  orders, products  Revenue minus cost (expression over two aggregates)

Each metric has an inferred kind:

aggregate — a single aggregation like SUM(orders.amount).
ratio — one aggregate divided by another (avg_order_value).
expression — arithmetic combining aggregates, possibly across tables (total_margin spans orders and products).

You didn't declare those kinds — dosi-engine inferred them from each metric's SQL.

Step 2 — Confirm the model is valid¶

$ dosi validate --model fixtures/orders/model.yaml
✓ 1 semantic model(s) valid

validate checks structure, unique names, that relationships point at real columns, and that every metric expression compiles. It's what you'd run in CI to catch a broken model before it ships.

Step 3 — See what you can group by¶

Metrics answer "how much"; you break them down by fields. List every field the model exposes, with time fields flagged:

$ dosi list dimensions --model fixtures/orders/model.yaml
NAME                   TIME  DESCRIPTION
orders.order_id
orders.customer_id
orders.product_id
orders.order_date      time
orders.status
orders.amount
customers.customer_id
customers.region
customers.signup_date  time
products.product_id
products.category
products.unit_cost

The time flag marks a time dimension — here orders.order_date and customers.signup_date — which you can bucket by day, week, month, quarter, or year at query time. We'll group by orders.status first, then by month.

Step 4 — Compile your first query to SQL¶

Ask for revenue, broken down by order status. Without --execute, dosi-engine just prints the SQL it would run — add --pretty to format it (default dialect: DuckDB):

$ dosi query --model fixtures/orders/model.yaml \
    --metrics revenue --group-by orders.status --pretty
SELECT
  orders.status AS status,
  SUM(orders.amount) AS revenue
FROM main.orders AS orders
GROUP BY
  orders.status

You wrote a metric name and a dimension; dosi-engine produced the aggregation and the GROUP BY for you.

Step 5 — Switch warehouses without changing the model¶

Here's where a semantic engine earns its keep. Ask for revenue by month — using the :month grain on the time dimension — and compile it for DuckDB:

$ dosi query --model fixtures/orders/model.yaml \
    --metrics revenue --group-by orders.order_date:month --pretty --dialect duckdb
SELECT
  DATE_TRUNC('MONTH', orders.order_date) AS order_date__month,
  SUM(orders.amount) AS revenue
FROM main.orders AS orders
GROUP BY
  DATE_TRUNC('MONTH', orders.order_date)

Now compile the exact same request for MySQL — only --dialect changes:

$ dosi query --model fixtures/orders/model.yaml \
    --metrics revenue --group-by orders.order_date:month --pretty --dialect mysql
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 main.orders AS orders
GROUP BY
  STR_TO_DATE(DATE_FORMAT(orders.order_date, '%Y-%m-01'), '%Y-%m-%d')

MySQL has no DATE_TRUNC, so dosi-engine renders month-bucketing with an idiom MySQL does support — and you never touched the model to switch. One definition, correct SQL for every warehouse. Either way the output column is named order_date__month (that's {field}__{grain}).

Step 6 — Run it for real¶

Now let's execute against an actual database. First, load the sample data into a local DuckDB file using the bundled seed script:

$ duckdb orders.db < fixtures/orders/seed.sql

Then run the revenue-by-status query from Step 4 with --execute, pointing at that file:

$ dosi query --model fixtures/orders/model.yaml \
    --metrics revenue --group-by orders.status \
    --execute --db orders.db
status     revenue
completed  350
cancelled  100
2 rows

Check it by hand: the seed has completed orders of 100 + 50 + 80 + 120 = 350 and cancelled orders of 30 + 70 = 100. The engine's numbers match exactly.

Step 7 — Break it down over time¶

Now run that monthly query — the same :month grain from Step 5 — for real. Add --order order_date__month to sort by the bucket:

$ dosi query --model fixtures/orders/model.yaml \
    --metrics revenue --group-by orders.order_date:month \
    --execute --db orders.db --order order_date__month
order_date__month    revenue
2024-01-01 00:00:00  150
2024-02-01 00:00:00  230
2024-03-01 00:00:00  70
3 rows

January = 100 + 50 = 150, February = 80 + 30 + 120 = 230, March = 70. Correct again — and you got monthly bucketing without writing any date SQL.

Step 8 — Try a ratio metric¶

Finally, a metric that divides one aggregate by another — average order value across all six orders:

$ dosi query --model fixtures/orders/model.yaml \
    --metrics avg_order_value --execute --db orders.db
avg_order_value
75
1 row

Total revenue 450 ÷ 6 orders = 75. Note dosi-engine computed this as a true ratio (guarding against integer division), not by averaging per-row values.

What you learned¶

You ran the full dosi-engine loop end to end:

Explored a model's metrics and dimensions (list).
Validated it (validate).
Compiled a metric to SQL and retargeted it to another warehouse with one flag (query, --dialect).
Executed it and verified the results by hand (--execute).

Every number matched the source data — because the metric was defined once and the engine did the rest.

Where to next¶

Connect a warehouse

Point --execute at Postgres, Snowflake, ClickHouse, StarRocks, and more.
Why dosi-engine

The "never silently double-counts" guarantee, explained.
CLI reference

Every command and flag — --where, time ranges, --format json, Arrow.