Guide: Datus extensions¶

Two behaviors that Apache Ossie (formerly 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 Ossie field. Your model stays 100% valid Ossie — tools that don't know Datus simply ignore these, and dosi-engine only uses them to refine behavior at points Ossie 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.

Three 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.
time (on a time field): what the column physically holds, when it is not a real date. '{"time": {"format": "%Y%m%d"}}' on a '20260101' VARCHAR is what lets the engine group it at all — and it keeps the time filter a bare dt >= '20260101', so partition pruning survives. See D-FORMAT.
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 Ossie 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          # month-over-month 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-month moving average
    custom_extensions:
      - vendor_name: DATUS
        data: '{"v": 1, "window": {"type": "rolling", "function": "avg", "periods": 3}}'

  - name: revenue_ytd                 # year-to-date total
    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.

Let the query pick the width¶

If you find yourself declaring revenue_1m_avg, revenue_3m_avg and revenue_6m_avg, declare the width as a query-time parameter instead: add a params entry and put {"param": "n"} where the number was.

  - name: revenue_moving_avg          # n-month moving average
    custom_extensions:
      - vendor_name: DATUS
        data: '{"v": "1.5", "params": [{"name": "n", "type": "int", "default": 3, "min": 1, "max": 12}], "window": {"type": "rolling", "function": "avg", "periods": {"param": "n"}}}'

dosi query --metrics revenue,revenue_moving_avg \
  --group-by metric_time:month --param n=1,3,6

One metric, three columns (revenue_moving_avg__n_1 and friends). A query that binds nothing gets the default, so the metric behaves exactly like the fixed one it replaces. The same works for an offset count, an ntile bucket count, an nth_value position, and a literal inside a derive filter — the declaration rules, the domain (which values a query is allowed to ask for), and the output contract are in parameterized-metrics.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
Declare how a time column is stored	time field	`time`	`format` (`date`, `%Y%m%d`, `%Y%m`, …) + `storage`	assumed native `DATE`/`TIMESTAMP`
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
Let the query choose a width / literal	metric	`params`	typed parameters filling `window` or `derive` holes	fixed at modeling time
Record that a metric derives from others	metric	`derive`	`filter` \| `compose` \| `lod` (+ `window.base`)	self-contained metric
Name a metric's measure explicitly	metric	`measure`	`{"name": …}`	sanitized stem
Use a per-entity aggregate as an axis	field	`lod`	`{"expr": "fixed(base, dims…)"}`	the field is a stored column
Align two facts on a shared dimension without joining them	relationship	`cardinality`	`many_to_many`	the relationship is a many→one join

derive and measure have no walkthrough here on purpose: they are written by the modeling tool, not by hand — the metric's expression and its derive payload have to stay in step, and the engine enforces that. See datus-extensions.md for what they carry and what a consumer gets out of them.

lod is the one option that changes what a field is: its value is a per-entity aggregate rather than a stored column, so "how many users were active exactly N days" becomes an ordinary group-by. Because the value depends on the filter context, where each predicate applies is part of the contract — read datus-extensions.md before authoring one.

Good to know¶

Your model stays valid Ossie. custom_extensions is a standard Ossie field; dosi validate and the upstream Ossie 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 Ossie — 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.