Skip to content

The dosi-engine semantics contract

The OSI core spec deliberately leaves execution semantics implicit: a metric is a name plus a raw SQL string, a relationship is a column mapping, and a field is at most "is a time dimension". This document is the normative contract for how dosi-engine fills those gaps. It is versioned with the engine; behavioral changes here are breaking changes.

Rules are labeled S-<area>-<n> for reference from issues and tests.

1. Expressions

  • S-EXPR-1 Every Expression is compiled from its ANSI_SQL dialect entry when present, else the first SQL-family entry (SNOWFLAKE, DATABRICKS). MDX/TABLEAU/MAQL entries are ignored; an expression with only non-SQL entries is a compile error for metrics and a warning for fields.
  • S-EXPR-2 Expression fragments are parsed as a single scalar expression. Anything else — multiple projections, trailing aliases, statements — is a parse error (this is also the injection barrier: user text never reaches SQL by string concatenation anywhere in the engine).

2. Metric inference

  • S-METRIC-1 A metric expression must contain at least one aggregate call. Supported aggregates: SUM, COUNT, COUNT(DISTINCT x), AVG, MIN, MAX. Any other aggregate function is unsupported_aggregate.
  • S-METRIC-2 Classification, applied to the parenthesis-stripped root:
  • a single aggregate call → aggregate metric;
  • agg / agg (both sides bare aggregates) → ratio; the division is wrapped CAST(numerator AS DOUBLE) / denominator to avoid integer division on truncating engines;
  • anything else → expression; each aggregate subtree becomes a measure, the surrounding arithmetic is preserved verbatim (including NULLIF, CASE, …). Note SUM(a)/NULLIF(SUM(b),0) is an expression, not a ratio, and is not double-cast — the authored SQL wins.
  • S-METRIC-3 Rejected in metric expressions, as structured errors: window functions (window_in_metric), subqueries (subquery_in_metric), nested aggregates (nested_aggregate), column references outside any aggregate (bare_column_in_metric), aggregates over no column (SUM(1) → bare_column_in_metric), multi-column COUNT(DISTINCT a, b), and references to other metrics — metric references live exclusively in the DATUS derive extension key (docs/datus-extensions.md#d-derive), never in expression SQL; the expression of a derived metric stays a self-contained flattened form.

3. Measures (synthesized)

  • S-MEASURE-1 Each distinct aggregate call becomes a measure named {dataset}_{stem}_{suffix}: stem = the aggregate argument rendered and sanitized (lowercase, non-alphanumeric runs → _); suffix = sum, count, count_distinct, average, min, max; COUNT(*) → {dataset}_rows_count.
  • S-MEASURE-2 Measures dedupe by signature (dataset + aggregate kind + distinct + normalized argument), within and across metrics. Two different aggregates that sanitize to the same name are a compile error (measure_name_collision) — never silently merged.

4. Column → dataset attribution

  • S-ATTR-1 dataset.column resolves exactly; the qualifier must be a dataset name and the column one of its declared fields. Metric and filter columns reference fields (which may themselves be expressions), not raw physical columns.
  • S-ATTR-2 A bare column resolves iff exactly one dataset declares a field with that name; zero → unknown_column, several → ambiguous_column (with candidates).
  • S-ATTR-3 All columns inside one aggregate must belong to one dataset (cross_dataset_aggregate otherwise).
  • S-ATTR-4 COUNT(*) attributes to the unique dataset of the metric's other measures, or to the model's only dataset; otherwise count_star_needs_dataset.

5. Joins

  • S-JOIN-1 Relationships are the only join source. Edges are followed strictly many→one (from → to), so walking a path never multiplies the origin's rows. Joins are on the AND-ed equality of the relationship's column pairs, and are LEFT JOIN by default (orphan many-side rows survive into a NULL dimension bucket). A relationship may declare INNER via the Datus join_type extension (drop orphans — attribution semantics; docs/datus-extensions.md#d-join).
  • S-JOIN-2 A target dataset is reachable iff exactly one simple path exists (edge-level: parallel relationships are distinct paths). Zero paths → no_join_path; several → ambiguous_join_path with each candidate path spelled out. Maximum path length: 6 hops.
  • S-JOIN-3 Self-relationships and cyclic walks are never followed.

6. Fan-out protection & branch assignment

The correctness core. Terms: a measure's home is the dataset its columns live on; the required set of a query is every dataset referenced by group-by dims and filters.

  • S-FAN-1 Candidate evaluation bases for a measure: datasets that reach (S-JOIN-2) both the measure's home and the entire required set. Duplicate-sensitive aggregates (SUM, AVG, COUNT) are additionally restricted to their home — evaluating them over a fanned-out join would double-count. COUNT DISTINCT, MIN, MAX may evaluate anywhere.
  • S-FAN-2 Within one metric, if a common candidate hosts all its measures, the whole metric evaluates there (preferring a base that is home to one of the measures). Consequence: a cross-dataset ratio like SUM(fact.x) / COUNT(DISTINCT dim.k) always evaluates over the fact join — it counts entities observed in the fact — with or without a group-by.
  • S-FAN-3 Otherwise each measure aggregates at its own home's grain in its own branch. Branches all produce the requested dim columns and merge FULL OUTER JOIN on them; the k-th branch joins on the null-safe equality of COALESCE(m0.key … m(k-1).key) and mk.key (see S-FAN-5), and final keys project as COALESCE across branches. Zero group-by keys → CROSS JOIN. Where a dialect cannot express that join — the Postgres family requires an equi condition in a FULL JOIN, MySQL/TiDB have no FULL JOIN, and ClickHouse cannot use a COALESCE as a join key — the same merge is emitted as a keys CTE (UNION of every branch's key tuples) that each branch LEFT JOINs back. Both shapes yield identical rows; the choice is invisible in the result and visible in --explain/generated SQL only.
  • S-FAN-4 A duplicate-sensitive measure whose home has no candidate base is a fan_out_risk error, with a retry hint. The engine never silently emits a double-counting query.
  • S-FAN-5 A group-key value that is NULL merges as one row across branches, not one per branch: the merge join is null-safe, rendered as the portable (a = b OR (a IS NULL AND b IS NULL)) (a bare = would leave each branch's NULL bucket unmatched — SQL NULL = NULL is unknown). ClickHouse rejects that OR-expansion as a join key, so there — and only there — it is emitted as the equivalent single predicate IS NOT DISTINCT FROM.
  • S-FAN-6 A bare count metric (its value is exactly one COUNT / COUNT DISTINCT measure) reads 0, not NULL, for a group present only in other branches — a count over no rows is 0. This applies only when the whole metric is the count; a count embedded in a ratio or expression keeps NULL so it propagates (and a count denominator never becomes a literal 0 divisor). A metric's Datus fill_nulls_with extension overrides this default and applies to any metric kind (docs/datus-extensions.md#d-fill).

7. Dimensions, grains, time

  • S-TIME-1 Group-by items are dataset.field or a bare unique field name; output columns are named {field}, or {field}__{grain} when a query-time grain is applied. Duplicate output names are an error.
  • S-TIME-2 Grains (day, week, month, quarter, year) apply only to fields declared dimension.is_time: true, lowering to DATE_TRUNC (per-dialect idioms where the dialect lacks it: MySQL/TiDB use DATE() / STR_TO_DATE(DATE_FORMAT(...)) / YEARWEEK rewrites, ISO weeks starting Monday).
  • S-TIME-3 A time range is half-open [start, end) over ISO dates, compiled as field >= DATE start AND field < DATE end before aggregation, against: the explicitly named time dimension, else the single time item in the group-by (metric_time counts as one), else — with no time item at all — each metric's primary time dimension (S-TIME-5). Only a group-by holding several time items still needs an explicit name (time_range_needs_dimension).
  • S-TIME-4 OSI core carries no granularity metadata; native grain is whatever the field expression yields, unless the field declares one via the Datus time_granularity extension (docs/datus-extensions.md#d-grain) — then requesting a strictly finer grain is a grain_too_fine error. Window metrics (period-over-period / rolling / cumulative) are the Datus D-WINDOW extension consuming the S-TIME-5 axis — see window-extension.md; a time spine remains proposed upstream — see rfc-time-semantics.md.
  • S-TIME-5 Every metric may have a primary (aggregation) time dimension, resolved as: the metric-level Datus time_dimension (docs/datus-extensions.md#d-time), else the unique primary time among the metric's datasets — a dataset's being its explicit time_dimension extension, else its single is_time field (this last inference reads no extension and applies in basic mode too). The reserved query name metric_time groups/filters by it: in a multi-branch plan each branch substitutes its own primary time column under the shared output name (metric_time / metric_time__{grain}) and branches merge on that name, so unrelated facts align on their respective business time axes. A metric with no resolvable primary time is no_primary_time_dimension; two metrics sharing one aggregation branch with different primary times are metric_time_conflict. A model field literally named metric_time is shadowed by the reserved name — qualify it as dataset.metric_time.

8. Filters

  • S-FILTER-1 --where filters are scalar boolean SQL over dimension fields, applied before aggregation in every branch. Columns resolve per S-ATTR rules; filter datasets join into each branch like dims.
  • S-FILTER-2 Rejected in filters: subqueries, window functions (unsupported_filter), aggregates (aggregate_in_where; HAVING-style metric filters are a later phase).

9. Datasets

  • S-DATA-1 A source containing whitespace is an inline query (compiled as a derived table); otherwise it is a table reference split on . into up to catalog.schema.table. Identifier quoting is not yet supported.
  • S-DATA-2 Within a branch, each dataset appears at most once and is aliased by its dataset name (no self-joins in v1).

10. Errors

  • S-ERR-1 Every compile/query error carries a stable snake_case code, a human message, the names involved, candidates where a bad reference has alternatives, and suggested_retry where a rewrite would succeed. --format json emits the full structure. Error text is not a stable API; codes are.