Semantic Layer

Open Data & BI → 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.

The pieces — exact fields

Metric

Dimension

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 — and then goes further: it measures each join's real cardinality, checks the primary key's uniqueness, and re-computes every pinned assertion (see Trust checks). 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.

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.

Each join declares its cardinality — how many joined rows one source row matches. A lookup (many_to_one, one_to_one) is safe; a fanning join (one_to_many, many_to_many) repeats each source row per match, which silently inflates any sum/avg/count over source columns. With the cardinality declared, the compiler builds a multi-fact plan instead of that wrong number: each metric aggregates in its own branch — the source plus only the fanning join its columns reference — at the requested dimension grain, and the branches are stitched on a dimension spine. Base metrics and fact metrics come back correct side by side, two fanning facts (the classic chasm: orders → items, orders → shipments) each aggregate at their own grain, and a group missing from one fact shows blank for that fact rather than vanishing. Period-over-period composes with the plan — the comparison builds the whole plan twice, current and shifted, and stitches them with the same _prev/_change/_pct_change columns as a single-pass comparison. And a dimension from a fanning table groups base metrics too, when the model declares a primary key: the base branch deduplicates by the key — each source row counts once per distinct dimension combination it relates to, the standard related-table attribution. An INNER fanning join resolves too: INNER both multiplies and filters (a source row with no match vanishes), so the branch that reads the fanning table keeps the real join while every other branch — and the spine — keeps the filter as a correlated EXISTS instead. The scope holds everywhere; the multiplication stays contained. What the plan cannot prove still refuses with the reason: an unqualified column (no branch may guess its table), a bare count, a metric reading two facts at once, dimensions from two different fanning tables, a fanning-table dimension without a declared primary key, a duplicate-sensitive metric reading a different fact than the dimensions group by, and a lookup chained through a fanning join. Models saved before cardinality existed keep compiling unchanged — Validate measures them instead.

Trust checks — grain, measurement, assertions

A model can declare its primary key — the column that identifies one row of the source, i.e. the model's grain. Validate doesn't trust any of these declarations: it measures them. It counts the source rows, re-counts after each join cumulatively, and compares the result to the declared cardinality — an undeclared join that fans out in the data, or a “lookup” that actually multiplies rows, is reported with the real row counts. The primary key is checked for uniqueness (COUNT(*) vs COUNT(DISTINCT key)), and with a key declared the join measurement also catches fan-out an INNER join's dropped rows would hide from a bare row count.

Assertions pin the numbers a model must keep producing: a metric, a set of absolute filters, and the expected value (with an optional tolerance). Every Validate re-computes each one against the live backend and fails if the number moved — the difference between “the SQL still runs” and “revenue still means what the board was told”. Relative date windows are refused in assertion filters, because a pin on “ytd” would go stale by itself; pin an explicit date range instead. The editor's Pin current value button records what the model computes today — confirm the number against a trusted reference before relying on it.

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.

Fiscal calendars

A model can declare the month its fiscal year starts (Source tab). That unlocks two extra rollups — fiscal_year and fiscal_quarter — and five extra windows: this_fiscal_year, last_fiscal_year, this_fiscal_quarter, last_fiscal_quarter and fiscal_ytd. A fiscal year is named by the calendar year it ends in — with a July start, July 2025 opens FY 2026 — and the buckets come back as sortable numbers: 2026 for a year, 20261 for FY2026 Q1, so they order correctly in every engine and chart without per-dialect date formatting. With no fiscal start configured (or January), the fiscal vocabulary still works and simply equals its calendar counterparts.

Calendars month arithmetic cannot express — retail 4-4-5, 13-period, ISO-week years — are declared as a fiscal calendar table instead (Source tab): one row per day, mapping the day to each grain's period via a sequence number (a dense integer that steps by one per period, across year boundaries) and the period's start date. That unlocks two further rollups — fiscal_period and fiscal_week — and two further windows, this_fiscal_period and last_fiscal_period; the year/quarter vocabulary now resolves against the table too, so a 53-week year is honoured exactly. Buckets come back as the period's start date, and comparisons step the sequence — which is what makes “vs previous period” exact when a 4-week period follows a 5-week one, across year boundaries included. yoy is allowed only where the step is provably constant (a year is one year back, a quarter four quarters); a fiscal year holds no fixed number of periods or weeks, so those refuse with the reason rather than guess. Declaring a calendar table replaces the start-month setting — two sources of truth for the same fiscal year would disagree quietly, so the model refuses both at once.

Rollups — aggregate awareness

A model can declare up to five rollups: pre-aggregated tables mapping its dimensions and metrics onto their columns (Source tab). A query is answered by the first rollup that can provably answer it — every requested metric mapped and re-aggregatable (sum of sums, count as a sum of pre-counts, min/max of themselves), every grouped and filtered dimension present, and time grains served by the stored grain (a month store answers month/quarter/year; day answers everything; nothing serves a finer grain). avg never routes — an avg of avgs answers a different question, so store sum and count and derive the ratio — and count_distinct never routes. Anything unprovable falls back to the source table, and a routed query says which table answered: a leading comment in its compiled SQL and a banner on the result. Validate measures every mapped metric's total on the rollup AND the source, so a stale rollup is a reported drift with both numbers, never a quietly different dashboard.

Parameters — governed what-ifs

A model can declare named parameters its SQL fragments reference as {{name}} tokens — a commission rate, a materiality floor, a status filter. Callers (the runner, agents via metric_query, dashboards) may override them per query; every parameter must carry a default, because Validate, assertions and scheduled refreshes compile without a caller and a parameter that breaks unattended compiles is a footgun, not a feature. Values substitute as literals — numbers must parse, strings are escaped like any other literal — and a query naming an undeclared parameter, or a fragment referencing one the model never declared, is refused with the declared list. Join ON conditions cannot use parameters: a parameterised join would change the query graph per caller, and every cardinality declaration and measured probe would be describing a different query. A dashboard widget pins the overrides it was built with: Add to dashboard stores the runner's values on the widget, the scheduled refresh re-runs with exactly those values rather than reverting to the defaults, and the widget's Parameters… menu edits the pinned values — saving re-runs the governed query immediately, so the stored SQL, rows and parameters always tell one story.

-- metric big_sales, with parameter {{min_amount}} (number, default 100)
SUM(CASE WHEN amount >= {{min_amount}} THEN amount ELSE 0 END)

Hierarchies — declared drill paths

A hierarchy is an ordered drill path over existing dimensions — geo: region → subregion → city — declared once on the model. Agents see it in the catalog, so “break that down” has a governed next level instead of a guess, and the answer to “drill into EMEA” is the same path everyone else drills. Levels must name real dimensions on the model (2–6 of them, broadest first); a level that is not a dimension, or appears twice, is refused at save with the list of what exists.

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.

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"

What the agent sees — synonyms, values, honest sizes

The catalog injected into metric_query carries each field's description, each metric's governed formula (revenue = SUM(amount)), its synonyms (add them per field in the editor — aka: turnover, GMV) and sampled values for low-cardinality categorical dimensions (values: AMER|APAC|EMEA, measured by Validate — a dimension with more than 8 distinct values lists none, because a partial list reads as a complete one). Synonyms are also resolved server-side: a query for “turnover” maps to revenue with a disclosed note, and an ambiguous synonym refuses rather than guessing. Unknown names refuse listing what exists, and a result larger than the tool's 50-row cap says “first 50 row(s) of a LARGER result” instead of a silent partial list.

Worked example — one model, start to finish

Every piece above in one place, built in the order you would actually build it. The situation: an orders table, a customers table, and a finance team that has been arguing about what "revenue" means because three dashboards compute it three ways.

1. Start from the fact table and one metric

Resist modelling everything. One metric that replaces a disputed number is worth more than twenty nobody asked for, and it is how you find out whether the grain is what you assumed.

source table:  orders
grain:         one row per order

metric  net_revenue
  agg      sum
  sql      amount
  filters  status = 'settled', is_refund = false
  format   currency
  label    Net revenue

The filters are the whole point. "Revenue" was ambiguous because each dashboard remembered a different subset of them; written here once, every consumer inherits the same definition.

2. Add the dimensions people slice by

dimension  order_month   type time    sql date_trunc('month', created_at)
dimension  region        type string  sql region
dimension  segment       type string  sql customers.segment   -- needs the join below

Add dimensions that answer questions people ask, not every column that exists. Each one is something the agent may group by, and a catalogue full of noise makes it choose worse — the selected model's catalogue goes into the prompt on every call.

3. Join the dimension table

join  customers
  type  LEFT
  on    orders.customer_id = customers.id
  card  many_to_one

4. State the grain, so a wrong join fails loudly

grain assertion:  order_id is unique
measurement:      net_revenue is additive over time and region

These are what turn a modelling mistake into an error message instead of a number that is merely wrong. Without the grain assertion, the duplicated-customer case above produces a confident, doubled figure and nothing to notice.

5. Add the derived metric that caused the argument

metric  average_order_value
  agg      derived
  sql      {net_revenue} / NULLIF({order_count}, 0)
  format   currency

metric  order_count
  agg      count

6. Check what the agent will see

Open the agent catalogue view. It should read like a menu a human could use — clear labels, synonyms for the words your team actually says ("turnover", "sales"), and honest row counts. Then ask the analyst something the model covers and read the compiled SQL: the filters should be there, the join should be there, and the badge should say the answer came from the governed model rather than hand-written SQL.

7. Certify it, then share it

Mark the model certified once the numbers have been checked by someone who owns them, and share it with row-level security if different readers should see different slices — a single grant filtered by {{user.region}} scopes every member of a group to their own region. See user attributes.

Who uses it

Metric or plain SQL?

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 — sharing with row-level security

Metrics inherit access from the tables underneath them — a user who cannot read the source table cannot use a metric built on it. A superadmin can also share a model (grant type Semantic model in Access control): the grantee queries governed numbers computed over the owner's data, without access to the underlying tables. The grant can carry a row filter (dimension ∈ values, e.g. region ∈ [EMEA]) and a field mask — both enforced inside the compiled SQL at the one choke point every consumer uses (agents, the runner, BI refresh). A filter naming a dimension the model no longer has fails closed. And the restriction is disclosed everywhere: the grantee's editor, the runner's results and the agent's tool output all say the numbers are a scoped view — a restricted share must never pass for the global truth.

A row-filter value can also be the attribute token {{user.<key>}} instead of a literal. It resolves at query time to the calling viewer's values for that key, set by an admin under Admin → IAM → Attributes — so one grant on a group, region ∈ [{{user.region}}], scopes every member to their own region. A viewer whose account lacks the attribute is refused with the attribute named — never run unfiltered, never silently empty — and a malformed token is refused when the grant is written, using the same grammar the enforcer applies. The disclosure shows the resolved values, so a viewer always knows the scope they are actually seeing. Tokens resolve on every grant surface — semantic models, BI dashboards (stored results and live direct-query) and shared datasets — through the one shared resolver, so the same grant means the same rows wherever it is enforced.

Certification, history and dependents

A model is draft, certified or deprecated. Certify re-runs the whole validation pipeline against the live backend and refuses if anything fails, then stamps who and when; editing a certified model's definition drops it back to draft (a database trigger, so no write path can carry a stale certificate). Agents see [certified] and [DEPRECATED] markers in their catalog. Every change to a saved model also snapshots the previous definition — the History & usage tab shows a field-level diff per version, a restore (itself undoable), and everything that depends on the model: metric-backed widgets, agents and swarm nodes that allow-list it, and who it is shared with. Deleting a model warns with that list first.