Semantic layer entity model

Semantic Layer V3 is the authoring model used by the editor, Developer View, revisions, and MCP changes. It separates physical data structure from reusable business meaning while keeping every reference explicit.

Use Semantic layer workflows for task-oriented MCP guidance. This page explains what the model contains and how its parts relate.

Document version

New documents and exports use fileVersion: 3. V2 documents remain readable: loading migrates their supported calculations and result-filter references to canonical V3; subsequent saves write V3. This is not a promise that older editors can read V3. Keep older clients from rewriting migrated documents. See migration and compatibility for ambiguous-filter handling and retained history. The document version is independent of the MCP tool contract.

Named filters live in root filters. Each defines either filters or associativeFilters, with no type. A consumer uses reference: { filter: name } in its regular or associative filter slot. Invalid references remain visible as document errors; selectors prevent incompatible choices.

Business questions have a complete YAML reference.

Draft data-actions contract

The optional dataActions collection is a draft contract, marked in its schema and documentation. New or changed definitions require the data-actions feature flag; existing definitions remain readable and removable when it is disabled.

While this contract remains draft and feature-gated, breaking changes are allowed without automatic migrations. Affected definitions may need manual repair or recreation. This exception applies to data actions; the rest of the V3 document retains its documented compatibility rules. Saving or publishing a layer does not stabilize this contract. No draft-version field is added to saved documents.

Current definitions register a mapped table source’s writable operations and fields, or an existing stored procedure’s ordered warehouse arguments. Registration metadata is editor-owned. Data Actions have no separate access mode or user/group ACL. SQL or procedure code must enforce business access using trusted caller context; a view’s read filter does not automatically restrict writes. Table actions cannot target inline or custom-SQL views. Procedure schema, name, parameter names and warehouse type text must contain a nonwhitespace character; meaningful native identifier spacing is preserved.

Earlier draft definitions containing access are rejected. Remove that property and put the business authorization in the implementation; no automatic migration is provided. Runtime discovery, execution and status lookup for all semantic Data Action kinds are unavailable. Authoring, validation and preparation do not execute warehouse mutations, and publishing does not make these definitions executable.

Table-action references participate in field rename analysis and lineage. Writable tables and virtual procedures show authored field, dimension and measure context, plus mapped reads, writes and row-count checks derived from virtual-procedure SQL. A native stored-procedure workflow links only to its warehouse procedure through Calls; its body effects remain unknown, and parameter context adds no table or field edges. Workflow and warehouse-procedure nodes follow the data-actions feature flag. Missing references remain diagnostics, and the current usage catalog does not report workflow usage.

When the feature is enabled, Business Context → Business Workflows lists the registrations after Lineage. Search by name, description or target, or filter by kind. Select a workflow to inspect its saved properties and version history. Create and edit writable-table registrations in this tab, or use Developer View or an imported document. Table registrations reference tables already included in the semantic map. The table picker offers mapped warehouse tables; an invalid saved target remains visible so you can repair it. Uncommitted name or description input prevents collaboration updates from replacing local work. The property panel has collapsible definition, table, operations and fields sections. Select a native stored procedure from the connection catalog to register its target and ordered signature. Selecting it again refreshes the signature; the catalog supplies native types. Business access belongs to the SQL or procedure implementation.

Stored workflow diagnostics remain available in Issues while the feature is disabled, including warnings that do not block publication. Open the issue link to inspect the definition in Developer View or remove the registration. Adding or changing a workflow still requires the feature flag.

Procedure catalog reads are limited to 2,000 entries. A larger result fails explicitly instead of returning an incomplete list; select a narrower schema before checking registrations. Catalog reads do not execute the stored procedures. Catalog signatures preserve nested native types and quoted argument names, including PostgreSQL array and user-defined types and Databricks precision and nested definitions. Malformed responses fail as unavailable catalogs. Unnamed inputs use a positional $1, $2, … label; Redshift uses these labels for every input because its catalog view reports ordered types without parameter names. Redshift signatures at the catalog’s 512-byte limit are rejected as potentially incomplete.

The procedure picker groups entries by schema and shows their parameter types. You can open and search it while metadata loads. Reload refreshes procedures and schema or dataset choices, including newly created or granted schemas. BigQuery requires a dataset selection before loading procedures. Other connections can browse all schemas or select one schema, including when the whole catalog exceeds the limit. Procedures with multiple signatures for the same native schema and name cannot be registered by name.

Publishing checks each registered procedure against the current catalog, including its exact schema and name, ordered parameters and return type. A missing, ambiguous, changed or unavailable signature blocks publication. Catalog checks are snapshots and do not prevent later warehouse changes.

The optional writeback.mode setting selects dataSource or dataActions. When the feature is enabled, Settings → Business Workflows offers Business Workflows or Data connection permissions (marked Legacy). Each selection saves an explicit mode. An absent setting means dataSource, preserving connection-governed writes. Both modes are saved authoring and preparation metadata; no semantic Data Action runtime enforces them. Existing explicit table-write and procedure APIs remain governed by their own permissions and do not execute these definitions. This setting is also draft and requires the data-actions feature flag when changed or imported, including version restores.

New layers and imports use dataActions when the feature is enabled. Opening an existing layer does not select a new mode, even if its model is empty. Copies retain their source mode, including an absent setting.

With MCP tool contract 6.66.0 or later and the data-actions feature flag enabled, use astrato_semantic_layer_set_writeback_mode with a layer ID and mode: dataSource or mode: dataActions. It requires sl:write, stores either choice explicitly in the draft, and publishes nothing. Integrated authorization also requires permission to edit the layer. Nash uses the same tool. The tool is omitted from discovery and refused at invocation while the flag is disabled.

Writable fields have two scopes: All fields or an explicit selection. Clearing the selection permits no writable fields. Changing tables or cascading a field deletion preserves an empty restriction instead of changing it to all fields. Cascading a table deletion removes the registrations that target it; keeping broken references retains them for repair.

MCP and Nash author registrations with astrato_semantic_layer_list_data_actions, astrato_semantic_layer_get_data_action, astrato_semantic_layer_prepare_data_action_change and astrato_semantic_layer_apply_data_action_change. Prepare returns a target and current revision; apply atomically creates, replaces, updates, renames or deletes a registration. Native schema/procedure spellings must match the catalog exactly, and BigQuery needs a selected dataset. These tools execute no warehouse mutation. The workflow-policy tool also accepts an optional expected revision and changes registration admission without adding action ACLs.

Version compare lists action changes under Business Workflows and shows their operations, fields, procedure signature and key-field scope. When the feature is disabled, existing actions remain inspectable, but restoring an action addition or modification is unavailable. Removing an action and restoring unrelated changes remain available. Importing edits from the earlier engine format preserves the registered actions and writeback.mode.

MCP copying requires the data-actions feature; ordinary empty-layer creation remains available while it is disabled.

Copying an older layer keeps its valid stored filters, Business Questions, datasets, map layout and authoring settings and skips invalid fields. If the source changes during the copy, or the new layer is opened before the copy completes, the copy fails and cleanup is attempted. A copy that returns no usable layer ID is reported as a failure; check the layer list before retrying. If an HTTP error includes cleanupFailed: true and createdId, delete that incomplete layer before retrying. A document import fails in the same way instead of dropping editor-owned content. MCP errors name the layer when cleanup fails. Otherwise, retry to copy the current definition.

Model at a glance

flowchart LR
  connection[Data connection] -. routing context .-> layer[Semantic layer]
  layer --> views[Views]
  layer --> joins[Joins]
  layer --> dimensions[Dimensions]
  layer --> measures[Measures]
  layer --> udfs[Functions]
  layer --> questions[Business questions]
  layer --> storedFilters[Stored filters]
  views --> fields[Fields]
  views --> filters[View filters]
  joins --> views
  fields --> dimensions[Dimensions]
  fields --> measures[Measures]
  dimensions --> dimensions
  measures --> measures
  dimensions --> questions[Business questions]
  measures --> questions
  fields --> questions
  fields --> storedFilters
  storedFilters --> questions
  udfs[Functions] --> queries[Query expressions]
  acl[Access grants] -. protect .-> layer

Solid arrows show containment or semantic references. Dotted arrows show platform context that is stored outside the V3 entity collections.

Entity inventory

Entity	Identity and ownership	Important properties	References
Semantic layer	An external semanticLayerId identifies the resource. The ID is not embedded in V3.	name, description, datasource, locale, queryFeatures	Routes to one data connection through datasource.
View	Keyed by name under views. A view owns its fields, SQL parameters, and row filter.	source, description, sqlAlias, singleGrain, filter, filterMode	A source names a physical object, contains custom SQL, or contains inline data.
Field	Keyed by name under a non-inline view. Inline columns are the fields of their view and preserve array order.	source, dataType, optional native type, label, description, precision, enabled state	Always belongs to one view. Other entities use { view, field } references.
Join	Ordered entry under joins; its two endpoints provide its semantic identity.	views, cardinality, ordered conditions	Connects exactly two views. Each condition compares expressions, normally fields from the endpoints.
Dimension	Keyed by name under dimensions.	Common metadata plus the properties of its variant	A field or bucket dimension references a field. A drillthrough contains ordered field or dimension levels.
Measure	Keyed by name under measures. Its class is derived, not stored.	resultType, formula expression, m1… operand blocks, format, parameters, metadata	Operands may reference fields, measures, or measure-local parameters.
Function	Keyed by name under udfs. Function signature order is significant.	kind, path, resultType, ordered parameters, supportsDistinct, metadata	Query expressions bind arguments to signature parameters. Stored measures do not publish function calls in the current model.
Business question	Keyed by name under businessQuestions; top-level and independent of datasets.	Meaning and business-value prose, selections, filters, sorting, chart recommendations, defaults	Selects fields or dimensions and measures; follow-ups reference other questions by name.
Dataset	Keyed by name under datasets. Editor-owned; it is not sent to the query engine.	description, categories, ordered dimensions and measures, filters	Lists existing dimensions and measures by reference. Filters are pre-aggregation conditions on fields or dimensions, as on a business question.
Stored filter	Keyed by name under filters, across both types. Editor-owned; it is not sent to the query engine.	description, categories, and exactly one associativeFilters or filters body	A set filter keeps target values; a regular filter holds conditions. Consumers use reference: { filter: name } in the matching existing slot.

The V3 document has no top-level object, relationship, security-rule, or table-limit collection. Unsupported top-level collections are rejected instead of being silently discarded.

View and field variants

A view has exactly one source variant:

Source kind	Stored source	Field ownership
Physical table or view	type: table plus table, and optionally database, schema, and objectType	The view’s name-keyed fields map describes physical or expression fields.
Custom SQL	type: sql, a statement, and optional typed parameter defaults	Result fields are stored in the view’s fields map.
Inline data	type: inline, ordered columns, and header-bearing csv	columns are the fields; an inline view does not have a second fields map.

Physical source identifiers preserve the exact casing supplied by the data source. Source discovery metadata is temporary catalog context, not part of the semantic-layer document. Confirmed missing source objects can be marked on the affected view, field, or function so the draft remains repairable; see Semantic layer issues.

A view’s singleGrain accepts only auto. In the table panel, the Use inner join checkbox under Join type sets it; clearing the checkbox removes the property.

Custom SQL int parameter defaults must be within the exact integer range −9,007,199,254,740,991 to 9,007,199,254,740,991. Values outside that range are rejected instead of rounded. Boolean defaults are stored as true or false and keep that representation when you reopen and save them.

For a list parameter, use {ids:int_list} or {labels:string_list} in your Custom SQL and enter a comma-separated default such as 1,2 or ok,other. Validate SQL checks the statement using those defaults. After validation succeeds, Run SQL preview runs the same statement with the same defaults and shows its first 20 records. Keep the list type in the SQL token; you do not need to change it to int or string to validate or preview.

Dimension variants

Set a dimension’s optional label alongside its kind. The label controls its display text; the dimension’s map key remains its identity for references. Publishing and importing preserve the label across dimension variants.

Kind	Meaning	Relationship
field	Reusable semantic name for one physical or calculated field	References one { view, field }. Date level, date part, and formatting are optional.
bucket	Evenly spaced numeric groups	References one field and owns minimum, maximum, bucket-count, and outer-bucket settings.
mapping	Custom groups evaluated in order; the first matching rule wins	References a base field and owns filters, typed group values, and an optional default.
drillthrough	Ordered hierarchy for moving from summary to detail	Contains one or more field or dimension references. Level order is semantic.
periodAxis	Reusable date window for period-aware calculations	Owns periodAxis.sources, period, and level.

A drillthrough may reference ordinary field dimensions. Cycles and drillthrough-to-drillthrough nesting are invalid because the ordered field sequence must be deterministic.

A period axis uses the same block globally and inside a named business question. See Period axes and comparisons for the full YAML contract, relative dates, multiple sources, compatibility rules and examples.

Custom-group values

Custom-group rules preserve empty text and null as distinct values. Equality with null checks for a missing value. A list containing null and text matches either, including an explicitly authored empty string. Exclusions follow ordinary filter behavior: excluding non-null values retains null rows; excluding only null matches non-null rows.

Integer group values and defaults use the same exact integer range as SQL parameter defaults. Out-of-range values remain available for correction in the editor and block publication.

Date and time comparisons carry their type with their text, for example { value: '2026-09-23', dataType: date }. Simple and advanced editing preserve that type. Rules containing null comparisons or empty-string list members open in advanced mode so those values remain distinguishable. Legacy millisecond literals cannot be compiled into custom-group rules; replace them with explicit date or time text and its matching type. Validation reports this before publication.

Date grouping

Choose date grouping by the question you want to answer:

Setting	Meaning	Across years
dateLevel: month	A date-valued monthly time bucket	January 2025 and January 2026 remain separate.
datePart: month	The numeric month of the year	Both Januaries share the same value, useful for seasonal comparisons.

A chronological monthly trend can span years without changing its date level. Use date parts when you intend recurring calendar categories, rather than choosing them from the number of years in the data. Date grouping and period comparisons serve different purposes.

Measure variants

Set a measure’s optional label alongside its expression and resultType. The label controls its display text; the measure’s map key remains its identity for references. Publishing and importing preserve the display label, including for derived, conditional and period measures.

measures:
  Order Count:
    label: Distinct orders
    resultType: int
    expression: '{m1}'
    m1:
      aggregate: COUNT
      distinct: true
      arguments:
        - reference:
            view: Orders
            field: Order ID

Every measure uses the same readable formula plus operand-block representation. Its compatibility class is derived from the formula and references:

Derived class	Rule
Aggregate	Contains no named-measure reference.
Derived	References named aggregate measures and contains no conditional if expression.
Conditional	Contains an if expression and at least one named-measure reference.

Measure formulas refer to sibling blocks such as {m1} and {m2}. Nested definitions may use qualified names such as {m2m1}; these names resolve only inside their own calculation. An operand can be a reference, scalar or date value, operator, aggregate, or function call. Aggregates may own a filter and options such as distinct, total scope, fanout behavior, ordering, and a filtered-out value. Measure-local parameters are name-keyed typed defaults referenced from operands or aggregate filters.

The aggregate’s fanout option is omitted by default. fanout: allow enables fanout; fanout: reduceToRelation enables fanout with reduction to the relation’s dimensionality. Publishing and YAML import preserve both parts of the reduced-fanout setting. Check results at each intended grouping: a correct ungrouped total does not establish that results across a many-to-many join are correct.

Structured comparison operators use textual names such as notEquals. Measure expression strings use formula symbols: =, !=, >, >=, <, and <=. For example, if({m1} != 'External Calculation', 'Not required', 'Required') compares the referenced measure to a string. Each textual comparison has one formula spelling; notEquals renders as !=. Legacy <> comparisons remain readable and normalize to != when rendered for execution.

Timestamp parameter defaults are independent of your device timezone. Use a full ISO date and time with seconds and at most three fractional digits. A timestamp without an offset keeps its wall-clock value; an explicit offset is normalized to UTC. A timestamp_tz must include Z or an explicit UTC offset, for example 2026-01-02T03:04:05+13:00. The editor does not guess an offset from the model timezone. Existing numeric defaults remain epoch milliseconds. Invalid calendar dates and values finer than millisecond precision are rejected rather than silently changed.

The model’s locale.timezone accepts named IANA timezones such as Pacific/Auckland, including supported aliases such as US/Pacific. Names are case-sensitive: US/pacific is invalid. Timezone-aware timestamp grouping and relative date boundaries use the model timezone, including its daylight-saving rules. Date-only and unzoned timestamp values retain their own type-specific behavior.

When the entire locale is omitted, query compilation uses the engine’s Europe/London default. If locale is present, set timezone to a string: use UTC or an empty string for UTC, or a named zone. A missing timezone inside a present locale, invalid names, standalone offsets and the machine-dependent Local value are reported as settings errors. An existing invalid setting remains visible in a draft so you can repair it before publishing.

An aggregate measure can add a stored period comparison while keeping the same formula and operand blocks:

dimensions:
  Order Timeline:
    kind: periodAxis
    resultType: date
    periodAxis:
      sources:
        - reference: { view: Orders, field: Order Date }
      period: currentYear
      level: month
measures:
  Sales growth:
    resultType: float
    expression: '{m1}'
    m1:
      aggregate: SUM
      arguments:
        - reference: { view: Orders, field: Amount }
    periodAxis:
      reference: { dimension: Order Timeline }
      comparisonPeriod: year
    periodComparison:
      type: growth
      reference: { dimension: Order Timeline }

Stored period comparisons accept current, value, diff, ratio, and growth. They reference a period-axis dimension. An explicit comparison offset belongs to the measure-local periodAxis binding. Named periods can retain their built-in previous interval by omitting the offset; relative periods require an explicit offset. Explicit reduction belongs to the sibling nestedCalculation property.

Filters, table limits, and row-level security

A filter is embedded where it applies, unless it is stored under filters as a named entity for questions to reuse. It stores a Boolean formula over sibling condition blocks such as {f1} AND ({f2} OR {f3}). Each condition owns an operator and ordered expression arguments.

Owner	Effect
View filter	Restricts rows before queries use the view. filterMode controls whether the filter is active and strict.
Aggregate operand filter	Restricts rows used by that aggregate.
Business-question filters	Applies before aggregation. Subjects may be fields or dimensions.
Business-question resultFilters	Applies after aggregation, before or after advanced calculations depending on its subject (details); with keyed measures it addresses completed selection keys.
Stored regular filter	Reusable conditions, referenced from a compatible regular filter slot; the consuming slot determines when they apply.

Table limits and row-level security are authoring views over a view’s existing filter and filterMode; they do not create another collection. A view-filter comparison against the reserved user-context parameters $userEmail or $userGroupNames is resolved per requesting user. Other view-filter comparisons are ordinary static pre-filters.

When opening legacy layers or drafts saved by an older importer, view and aggregate filters recognize untyped strings as dates only when the comparison expects a date and the value is a real calendar date in exact YYYY-MM-DD form, such as 2026-08-14. The text stays unchanged; the literal gains dataType: date. This does not infer timestamps or numeric date parts, change explicit types, or repair invalid dates. Values outside this rule keep their existing validation diagnostics so you can correct them before publishing. New document authoring still requires explicit date types.

Access control is outside the document

Semantic-layer access grants protect the whole semantic-layer resource and are not V3 entities. See Manage resource access for granting permissions to people, groups, workspace roles or everyone in your workspace. With explicit resource access enabled, creating a layer does not provide irrevocable access: the creator’s grants can be changed or removed. Workspace owners and administrators retain full access. Row-level security controls which source rows a permitted query can return.

Keeping access grants outside the V3 document prevents copying or exporting semantic definitions from accidentally copying resource permissions.

Business questions are terminal consumers

A business question combines governed selections with the prose needed to understand the answer. Its dimension selections may reference fields directly or reference named dimensions; its measure selections reference named measures. Selections are ordered lists, and list order is the presentation order. Give every entry a key when you need local aliases, inline periodAxis dimensions, or local calculations; keys then identify the question’s output columns, and advanced calculations refer to the dimension keys. Filters, result filters, prioritized sorting, Top N, chart recommendations, categories, and follow-up questions refine how consumers use it.

Questions are stored and versioned with the semantic layer but are not emitted to the query engine’s legacy composite. They are still validated: a missing selected entity or a question with no measure blocks publication because an agent could not execute it. Renderer recommendations are advisory; omitting output configuration means table output. See Business questions.

Settings and derived state

The following are document settings or derived state, not independently addressable semantic entities:

• locale, queryFeatures, and opaque engine compatibility properties apply to the document;
• layout stores editor positions and display choices;
• SQL and filter parameters belong to their owning view, measure, or filter;
• revisions, publication state, validation issues, source-catalog data, and query capabilities are computed or stored outside the V3 document.

Developer View exposes lossless V3 JSON and YAML. Apache Ossie export is a read-only projection of the current valid draft, not another authoring model.

Large YAML imports retain change history for every changed entity. Saving the imported draft is atomic: if saving fails, the previous saved draft remains intact. Creating a version also retains the complete change history for the revisions it covers.

Join comparison operators

New and updated joins store these canonical textual values:

Stored value	Editor symbol	Accepted V3 compatibility aliases
equals	=	equal, =, ==
notEquals	≠	notEqual, !=, <>, ≠
greaterThan	>	>
greaterThanOrEqual	≥	greaterThanOrEqualTo, >=, ≥
lessThan	<	<
lessThanOrEqual	≤	lessThanOrEqualTo, <=, ≤

Loading normalizes a compatibility alias to its canonical value. Applying or exporting the document writes the canonical textual operator. Unsupported values are rejected with the affected condition path.

Rename and removal behavior

Names are entity identity in V3. Reference-aware rename operations can update supported dependent references atomically. Field data-type changes list definitions that read the field so you can review their compatibility before applying the change.

The editor asks for confirmation before removing a view, field, measure, dimension, join, or function. This includes entities with no known dependencies inside the semantic-layer document because a connected Workbook can still use them. When dependencies are known, the confirmation lists the affected entities and lets you either remove dependent definitions or preserve them for manual repair. Preserved broken references remain visible as draft issues and block publication until repaired.

Removal confirmations block interaction with the editor until you confirm or cancel, so changing the current selection cannot retarget an open destructive action.

Measure selectors and explicit nested aggregation

Use periodAxis: { reference: { dimension: Order Timeline }, period: yearToDate, level: month } for measure-local overrides. Use nestedCalculation: { aggregate: SUM, dimensions: [{ reference: { dimension: Order Timeline } }] } for an explicit reduction. An optional inner nestedCalculation.periodAxis preserves its own settings separately from the outer binding.

See Measure overrides and reduction for full examples and editor instructions.

Calculations inside calculations

A measure operand can contain a complete calculation definition. Use this to calculate a result per City, reduce it with SUM or AVG, and compare the reduced results without creating helper measures. The calculation editor and agent measure authoring use the same document shape.

resultType: float
expression: '{m1}'
m1:
  calculation:
    resultType: float
    expression: 'if({m1m1} > 20000, {m1m1}, 0)'
    m1m1:
      aggregate: SUM
      arguments: [{ reference: { view: Sales, field: Amount } }]
    nestedCalculation:
      aggregate: AVG
      dimensions: [{ reference: { dimension: City } }]

Here City is an existing stored dimension. The calculation first sums Amount per City, evaluates the IF for each City, then averages those results. At Country query grain, each Country gets its own average. Change the outer aggregate to SUM to sum those results.

New nested operands receive readable names such as m1m1. Existing m1 names remain valid and are preserved when editing. Names do not navigate into other calculations. Each calculation owns its parameters. Reductions support one ordinary stored dimension, or the existing Time Axis settings, and require Business Questions to be enabled.

Within one IF, local aggregates cannot be mixed with embedded calculations or stored-measure references. Wrap each local aggregate in a calculation, or save it as a measure and reference it. To nest reductions, put the inner reduction inside a calculation operand. Import rejects the engine’s unsupported direct reduction-within-reduction shape.

This extends fileVersion: 3; older readers may reject it.

A layer that contains data actions does not support embedded calculations or qualified operand names. Validation reports this combination as an error.