Guide: Datus extensions¶

Two behaviors that OSI leaves undefined, which you can control per-model:

Join type — whether rows with no match are kept or dropped when a metric joins to a dimension.
Null fill — show 0 (or any number) instead of a blank for a group with no data.

You set these inside a model's custom_extensions, an official OSI field. Your model stays 100% valid OSI — tools that don't know Datus simply ignore these, and dosi-engine only uses them to refine behavior at points OSI doesn't specify. The precise contract is in datus-extensions.md; this page is the how-to.

All Datus extensions share one shape — a custom_extensions entry whose vendor_name is DATUS and whose data is a small JSON string:

custom_extensions:
  - vendor_name: DATUS
    data: '{"v": "1.1", "<option>": <value>}'

1. Choose how joins handle unmatched rows¶

The problem¶

Say you measure revenue on line_items and want it broken down by orders.status. Some line items point to an order that isn't in your orders table (a late-arriving row, a soft-deleted order, a data-quality gap). What should happen to their revenue?

By default dosi-engine keeps them (a LEFT JOIN): the unmatched revenue lands in a row where status is blank (NULL). That is the right answer for reconciliation — every dollar is accounted for, even the orphaned ones.

But if you're doing attribution — "revenue by status" should only count revenue that actually has a status — you want those orphans dropped instead.

The fix¶

Declare the relationship INNER with the join_type option:

relationships:
  - name: line_items_to_orders
    from: line_items
    to: orders
    from_columns: [order_id]
    to_columns: [order_id]
    custom_extensions:
      - vendor_name: DATUS
        data: '{"v": "1.0", "join_type": "inner"}'

What changes¶

Same query, orphan revenue of 50:

`join_type`	`status`	`revenue`
`left` (default)	paid	300
	(blank)	50 ← orphans kept
`inner`	paid	300
		(orphan row dropped)

Which to pick¶

Use…	When
`left` (default, or omit)	Reconciliation / audit — no row should silently disappear.
`inner`	Attribution — a measure should only count where the joined entity exists.

It's a property of the relationship, so the choice is consistent across every query that follows that join — not something each query has to remember.

2. Show a number instead of a blank for empty groups¶

The problem¶

You ask for order_count and signups by city. A city had signups but no orders this period. Its order_count comes back blank (NULL) instead of 0. On a dashboard that reads as "no data" when the true answer is "zero orders".

dosi-engine already fills a plain count metric with 0 automatically (a count of nothing is zero). But a SUM — say revenue — stays blank by default, because "sum of no rows" is genuinely undefined (is it 0, or unknown?). You decide per metric.

The fix¶

Add fill_nulls_with to the metric:

metrics:
  - name: revenue
    expression:
      dialects:
        - dialect: ANSI_SQL
          expression: SUM(orders.amount)
    custom_extensions:
      - vendor_name: DATUS
        data: '{"v": "1.0", "fill_nulls_with": 0}'

What changes¶

revenue and signups by city, where Denver has signups but no orders:

`city`	`revenue` (default)	`revenue` (`fill_nulls_with: 0`)
Austin	900	900
Denver	(blank)	0

fill_nulls_with works for any metric — SUM, a ratio, an expression — and any number (0 is by far the most common). It also overrides the automatic count fill if you want a count filled with something other than 0.

One thing it won't do¶

It fills a metric's final value, never a piece inside it. A ratio like revenue / order_count is filled as a whole; the order_count in its denominator is never quietly turned into 0 (that would divide by zero). You get a filled ratio, computed safely.

3. Name each table's business time axis¶

The problem¶

A query says "revenue and inventory moves, by month, last quarter" — but which column is "time"? orders has order_date and ship_date; the inventory table has move_date. Without a declaration, the engine refuses to guess: you must spell out --group-by orders.order_date:month and can never align two tables' different time columns in one query.

The fix¶

Declare the primary time dimension — once per dataset, overridable per metric:

datasets:
  - name: orders
    custom_extensions:
      - vendor_name: DATUS
        data: '{"v": "1.1", "time_dimension": "order_date"}'
    fields:
      - name: order_date
        dimension: { is_time: true }
      - name: ship_date
        dimension: { is_time: true }

metrics:
  - name: shipped_revenue          # same SUM, but on the shipping axis
    expression:
      dialects: [{ dialect: ANSI_SQL, expression: SUM(orders.amount) }]
    custom_extensions:
      - vendor_name: DATUS
        data: '{"v": "1.1", "time_dimension": "ship_date"}'

A dataset with exactly one is_time field doesn't even need the extension — that field is the primary time automatically.

What changes¶

The reserved query name metric_time now works:

dosi query --metrics revenue,moves --group-by metric_time:month

Each metric is truncated on its own table's primary time column and the results align on the shared metric_time__month output. A time range with no --time-dimension (and no time column in the group-by) filters each metric's primary time instead of erroring.

Two companions¶

time_granularity (on a time field): the grain the column is stored at. '{"time_granularity": "month"}' on a monthly-snapshot column makes a :day request a clear grain_too_fine error instead of silently wrong numbers.
dataset (on a metric): a COUNT(*) names no column, so in a multi-dataset model the engine cannot attribute it. '{"dataset": "chat_record"}' pins it — only where the SQL itself is silent; aggregates that name columns keep their own datasets.

4. Compare periods and accumulate over time¶

The problem¶

"How does this month compare to last month?" "What's the 3-month moving average?" "Revenue year-to-date?" — none of these is expressible as one OSI aggregate, and writing LAG(...) OVER (...) inside a metric expression is rejected outright (window_in_metric): raw window SQL can't be validated, recomputed across groupings, or re-ranged safely.

The fix¶

Declare the derivation on the metric with the window key; the metric's own expression stays the plain base aggregate:

metrics:
  - name: revenue_mom_growth          # 环比增幅
    expression:
      dialects: [{ dialect: ANSI_SQL, expression: "SUM(orders.amount)" }]
    custom_extensions:
      - vendor_name: DATUS
        data: '{"v": 1, "window": {"type": "pop", "offset": "1 month"}}'

  - name: revenue_3m_avg              # 近3月移动平均
    custom_extensions:
      - vendor_name: DATUS
        data: '{"v": 1, "window": {"type": "rolling", "function": "avg", "periods": 3}}'

  - name: revenue_ytd                 # 年度累计
    custom_extensions:
      - vendor_name: DATUS
        data: '{"v": 1, "window": {"type": "cumulative", "function": "sum", "reset": "year"}}'

Query them like any metric — group by the time axis with a grain (orders.order_date:month or metric_time:month); every other group-by dimension partitions the window (MoM within each region, for example):

dosi query --metrics revenue,revenue_mom_growth,revenue_ytd \
  --group-by orders.region --group-by metric_time:month \
  --start-time 2025-05-01 --end-time 2025-11-01

What changes¶

Period-over-period compiles to a calendar-correct self-join (a month with no prior month reads NULL — never "the previous existing row"), and the engine automatically loads the lookback: a May start fetches April, a YTD start mid-year fetches back to January 1, and the output is trimmed back to what you asked for.

One query can mix all of the above — MoM next to a rolling average next to YTD and QTD — with a single caveat: a never-resetting running total can't share a query with lookback/reset metrics when a start time is set (the widened scan would change what it accumulates; the engine tells you so). Rolling metrics also take "require_full_window": true to return NULL for the first buckets instead of averaging a partial frame.

Two gotchas¶

The time axis must be in the group-by with a grain — forgetting it is a structured error whose retry hint names the exact item to add.
Give offsets a --start-time: without one the first period in your data simply has no predecessor and reads NULL (correctly).

For the full surface — the general offset/frame form the sugars desugar to, value|delta|percent_change|ratio calculations, reset semantics, and the v1 restrictions — see window-extension.md.

Quick reference¶

Goal	Where	Option	Values	Default
Drop vs keep unmatched join rows	relationship	`join_type`	`"left"`, `"inner"`	`"left"`
Fill empty-group metric value	metric	`fill_nulls_with`	any number	count → `0`, else blank
Name the business time axis	dataset / metric	`time_dimension`	field name (metric: `ds.field` too)	the single `is_time` field, else none
Declare a column's stored grain	time field	`time_granularity`	`day`…`year`	unknown, any grain allowed
Pin a `COUNT(*)`'s home table	metric	`dataset`	dataset name	SQL-derived, else error
Derive PoP / rolling / cumulative	metric	`window`	`pop` \| `rolling` \| `cumulative` \| offset/frame	plain aggregate

Good to know¶

Your model stays valid OSI. custom_extensions is a standard OSI field; dosi validate and the upstream OSI validator both pass. Non-Datus tools ignore the DATUS entry.
Absent = today's behavior. Add these only where you want to change the default; everything else is unaffected.
Typos are caught, not ignored. A malformed datus payload (bad JSON, or join_type: "outer", or a non-number fill) is a clear compile error, so a mistake fails loudly instead of silently doing the default.
See the plan. dosi query --explain shows each join's kind (left / inner), so you can confirm the extension took effect.
"v" is optional — but worth stamping. Leave it out and everything behaves exactly as shown above. Include it ("v": "1.1", matching what dosi info reports) and the engine can tell you when the two of you disagree: it warns if your model uses an option newer than the version you declared, and warns naming the options it had to drop if your model is newer than the engine. Run dosi info to see the version an engine implements and every option it reads.
Extensions only apply in datus mode (the default). Running with --osi-basic — strict standard OSI — ignores the DATUS entries and prints a warning naming the default that applies instead; the model still loads and queries. See cli.md.

For the exact JSON schema, versioning, and precedence rules, see datus-extensions.md.