Data & analytics

Semantic Layer

A metric defined once and reused everywhere. It exists so that two people asking the same question of the same data cannot get two different answers.

Open Data → Semantic Layer. You define metrics (numbers) and dimensions (ways to slice them) over your tables; the platform compiles a request for them into SQL and runs it.

Why it works this way

Without this layer, "revenue" is whatever SQL the last person wrote. One analyst excludes refunds, another includes tax, an agent invents a third variation — and all three are defended in a meeting. A metric definition makes the choice once, in the open, and every consumer inherits it.

The pieces — exact fields

Metric

Field	Required	Values / notes
`name`	Yes	Stable id matching `^[a-zA-Z_][a-zA-Z0-9_]*$` — it becomes the SQL alias, so no spaces or hyphens.
`label`	No	Human-readable display name
`description`	No	What it means and what it excludes. Agents read this too.
`agg`	Yes	`sum`, `avg`, `count`, `count_distinct`, `min`, `max`, `custom`, `derived`
`sql`	Depends	The column or expression to aggregate. Optional for `count`; REQUIRED for `custom`, where it is the full aggregate expression, e.g. `SUM(revenue) - SUM(cost)`. For `derived` it is a formula over OTHER metrics referenced as `{metric_name}`, e.g. `{revenue} / NULLIF({orders}, 0)` — each token is replaced with that metric's own expression, so a ratio always tracks its parts' current definitions. Derived metrics may reference other derived metrics; circular or unknown references are refused at compile time.
`filters`	No	Boolean SQL fragments ANDed INSIDE the aggregate — a filtered measure, e.g. `status = 'paid'`. Ignored when agg is `custom`.
`format`	No	number \| currency \| percent
`currency`	No	ISO 4217 code when format is currency

Why it works this way

filters lands inside the aggregate rather than in the query's WHERE clause. That distinction is the whole point: net_revenue can exclude refunds while sitting on the same row set as gross_revenue, so both can appear in one result without one of them quietly filtering the other.

Dimension

Field	Required	Values / notes
`name`	Yes	Same identifier rule as a metric — it is the SQL alias.
`label`	No	Display name
`description`	No	What this slice means
`sql`	Yes	A column, or an expression such as `DATE_TRUNC('month', created_at)`.
`type`	No	Field type. A `time` dimension can be rolled up to a grain at query time (day/week/month/quarter/year) — the compiler emits the right truncation per warehouse dialect, so you write the raw column once and get monthly or quarterly buckets on demand. Local datasets support every grain on DuckDB, the default engine; on the `LOCAL_ENGINE=alasql` escape hatch, every grain except week.

These SQL fields are trusted

Both sql fields are inserted into the compiled query as written. Only people you trust to write SQL against your warehouse should be defining metrics — this is an authoring surface, not a user input.

Defining a metric

Pick a source — a local dataset or a warehouse table (any connected Snowflake / BigQuery / Postgres / … connection; the editor browses its tables) — name the metric, choose the aggregation and expression, add the filters that belong to the definition, and declare which dimensions it may be sliced by. The editor previews the compiled SQL and a sample result before you save — read the SQL, it is the definition.

Validate compiles every dimension and metric and runs each one against the real source, reporting failures per field. Use it before saving: a typo'd column otherwise surfaces as an engine error much later, on a dashboard refresh. The query runner below the editor picks metrics and dimensions, adds filters (dimension filters become WHERE, metric filters HAVING) and time rollups, and the result can be sent straight to a dashboard as a governed widget.

Compiled preview

SELECT date_trunc('month', o.created_at) AS month,
       o.region                          AS region,
       SUM(o.amount)                     AS net_revenue
FROM   orders o
WHERE  o.status = 'settled'
  AND  o.is_refund = false
GROUP BY 1, 2

Joins — spanning a star schema

A model can declare up to eight LEFT/INNER joins from its source table, so dimensions and metrics can reference related tables (customers.segment on an orders fact) without pre-joining in a view or prep flow — the metric definition stays the whole story. Table names and aliases are validated as strict identifiers; the ON condition is authored by the model owner, the same trust as a dimension's SQL. Once a join exists, qualify column names in your fragments.

Relative date filters

Prefer these to hard-coded dates: they resolve against today every time the query runs, so a dashboard never needs editing as time passes. last_n_days (with the number of days as the value), this_month, last_month, this_quarter, last_quarter and ytd. They apply only to a time dimension, and compare the raw date rather than a rollup bucket — so “last 30 days” grouped by month still means 30 days. Windows are half-open and computed in UTC, and the runner shows the exact dates each one resolves to.

Period-over-period

Set compare to yoy, mom or prior_period and every metric gains _prev, _change and _pct_change (a fraction — 0.25 is +25%). It needs exactly one time dimension with a grain: that is the axis being compared. prior_period steps back one unit of that grain, mom one month and yoy one year whatever the grain.

Three behaviours worth knowing. A period with no predecessor — the first in the series, or a gap in the data — shows blank rather than being dropped from the result. _pct_change is blank when the earlier value was zero, because a change from nothing is not a percentage. And any date filter you set moves with the comparison, so filtering to this year still compares against last year rather than against nothing.

Not available on the AlaSQL escape hatch (LOCAL_ENGINE=alasql), which has neither CTEs nor date arithmetic — the compiler refuses with that message rather than emitting SQL it cannot run.

Compiled preview

WITH semantic_cur AS (…), semantic_prev AS (… shifted one year …)
SELECT semantic_cur."month",
       semantic_cur."net_revenue",
       semantic_prev."net_revenue"                                    AS "net_revenue_prev",
       (semantic_cur."net_revenue" - semantic_prev."net_revenue")     AS "net_revenue_change",
       CASE WHEN semantic_prev."net_revenue" = 0 THEN NULL
            ELSE (semantic_cur."net_revenue" - semantic_prev."net_revenue")
                 * 1.0 / semantic_prev."net_revenue" END              AS "net_revenue_pct_change"
FROM   semantic_cur
LEFT JOIN semantic_prev ON semantic_cur."month" IS NOT DISTINCT FROM semantic_prev."month"

Who uses it

Consumer	How
Dashboards	Pick a metric in the builder instead of writing a query. The tile inherits the definition and updates if it changes.
Agents	Enable the `metric_query` tool. The agent asks for a metric by name with dimensions and filters — it never re-derives the number.
Agent Chat	Answers about governed numbers come back consistent with the dashboards showing the same metric.
BI AI analyst	When it writes SQL for a chart or an AI-generated dashboard, the governed definitions for the tables in play are injected into its context with an instruction to compute those metrics with exactly the defined expression — so ad-hoc BI agrees with the metric tiles instead of improvising a different formula.

Note

When an agent answers from a metric, the source shown under the answer names the metric and marks it as coming from the semantic layer — so a reader can tell a governed number from an ad-hoc query at a glance.

Metric or plain SQL?

Use a metric when	Use SQL when
The number appears in more than one place	It's a one-off investigation
People would argue about its definition	The shape is exploratory and changing
An agent might be asked for it	You need a join or window the layer doesn't model
It must stay consistent as the data model changes	You're prototyping and will discard it

Practical advice

Name for the business, not the schema. net_revenue, not sum_amt_filtered. The name is what people and agents select on.
Write the exclusions into the description. "Excludes refunds and internal test accounts" prevents most of the arguments this layer exists to end.
Start with the contested few. Five metrics everyone disputes are worth more than fifty nobody looks at.
Declare dimensions deliberately. Every dimension you expose is a slice someone will screenshot — leave out the ones where the metric doesn't mean anything.
Changing a definition changes history. Everything reading the metric moves with it. Announce it, and note the change in the description.

Access

Metrics inherit access from the tables underneath them — a user who cannot read the source table cannot use a metric built on it. Grants are managed in Access control.

PreviousKnowledge Base Next BI Workspace