Expression language

This guide explains the authoring format and semantics. Target support varies; see Support and limitations.

1. Purpose and scope

The Semantic Hub expression language is the default GENERIC dialect: a small, platform-neutral, ANSI-SQL-like language for expressions stored in Semantic Hub YAML. Designers use it for canonical field expression values, dataset and metric filter properties, and aggregate-level DERIVED.expression calculations.

The language is not raw SQL. It has no SELECT, FROM, JOIN, subquery, DDL, DML, comment, or target-specific function syntax. Semantic Hub owns the grammar, name resolution, type system, null behavior, function catalog, validation, and translations to target languages such as Snowflake SQL and Power BI DAX.

Authored YAML stores expression source text. A canonical syntax tree and typed intermediate representation are generated by the Semantic Hub parser and compiler; designers do not author those structures.

The JSON Schemas expose four explicit authored contracts even though they share one grammar: RowExpression binds row-level fields, AggregationExpression binds metrics after aggregation, FilterExpression is a row expression whose complete result must type-check to Boolean, and AggregationFilterExpression is a post-grouping expression whose complete result must type-check to Boolean. JSON Schema validates that each value is a non-empty string; the SDK enforces reference kinds, evaluation phase, result type, grain, and path legality.

fields:
  - name: full_name
    expression: first_name || ' ' || last_name

filter: "status = 'COMPLETED' AND is_test = FALSE"

metrics:
  - name: annualized_profit
    metric_type: DERIVED
    expression: "(total_revenue - total_cost) * 12"

2. Lexical basics

Expression keywords are case-insensitive. The formatter emits uppercase keywords and function names.

2.1 Identifiers and references

An unquoted reference component begins with an ASCII letter or underscore and continues with ASCII letters, digits, or underscores. A dot separates qualified reference components:

status
customer.region
returns.total_return_amount

Use double quotes for a component containing spaces, punctuation, or a reserved word. Double a double quote inside a quoted component. Authored Semantic Hub object names cannot contain dots because dots delimit reference paths.

"Order Status"
"Customer"."Sales Region"
"A name with ""quotes"""

Reference resolution uses the same case-insensitive authored-name comparison and path rules as other Semantic Hub references. The binding context determines whether a reference must resolve to a row-level field or an aggregate metric. A field's preferred_usage never participates in binding.

Choosing a field or a column

Prefer available one-to-one mapped fields, using columns when no suitable field exists. Fields can include business descriptions that make the calculation easier to understand. For example, this field exposes a database column without changing its value:

fields:
  - name: extendedprice
    data_type: DECIMAL
    expression: L_EXTENDEDPRICE

An aggregate metric can use either expression: extendedprice or expression: L_EXTENDEDPRICE; prefer the field form. Use the verified database column when an appropriate field is unavailable, or to define the backing field as shown above. Check that the field maps directly to the intended column in the same scope. A calculated field or a field reached through a different role can have a different meaning.

This choice applies where model expressions permit column inputs. Query members and structural field references still require authored fields.

2.1.1 Explicit physical columns: SOURCE_COLUMN

Agreed contract; parser implementation is pending. SOURCE_COLUMN('c') explicitly binds a physical column in the current expression anchor's source table or source-query output. It bypasses case-insensitive semantic field and metric lookup. Ordinary references keep their existing field-first resolution, metric lookup where permitted, and physical-column fallback. A field's own same-name reference retains its source-column interpretation; other semantic dependency cycles remain errors.

For a source containing column c, the following illustrative fields avoid the collision with semantic field C:

fields:
  - name: C1
    expression: "SOURCE_COLUMN('c')"
  - name: C
    expression: C1

Without the explicit construct, C1 = c resolves to semantic field C and C = C1 closes a dependency cycle. Case differences do not disambiguate semantic names.

• Require exactly one non-empty string literal. Dynamic names, expressions, and additional arguments are invalid. Escape an embedded single quote by doubling it, as in SOURCE_COLUMN('O''Brien').
• Preserve the literal's physical spelling and case for source lookup and target identifier rendering. The argument is a column name, not SQL text. Dots within it do not denote relationships or dataset qualification. This initial form addresses only the local source; qualified/joined-column syntax is not defined here.
• Permit it only where model-authored expressions permit physical row columns, including row inputs inside explicit aggregate calls. It does not permit an unaggregated column in an aggregate-level calculation. Public Query expressions forbid it, including within function arguments.
• Apply the usual source-metadata, type, grain, relationship, and exclusion checks. Catalog-only validation must not invent missing-column errors when physical metadata is unavailable; compilation with source metadata performs the applicable source checks.
• Treat it as a reserved reference construct, not an ordinary scalar function or target-native function. The parser must expose a physical DatabaseColumnReference to consumers. Target compilation renders the correctly qualified and quoted column identifier, never a SOURCE_COLUMN(...) call.
• Formatting, copying, role expansion, and reference rewriting must preserve explicit physical binding. Re-serializing the example as plain c would reintroduce the cycle. When extraction changes the expression anchor, retain the correct source occurrence; do not reinterpret the column against the new root.

2.2 Literals

The initial language supports:

Strings use single quotes. A single quote inside a string is written twice. Typed date and timestamp literals must use ISO representations; target compilers render the appropriate target form.

YAML block scalars are useful for longer expressions:

filter: >-
  status <> 'CANCELLED'
  AND order_date >= DATE '2026-01-01'

3. Operators and precedence

From highest to lowest precedence:

Designers should use parentheses when the intended grouping is not immediately obvious. The formatter may add parentheses but must not change meaning.

!= is accepted as an input alias for <>; the formatter emits <>.

4. Predicates

4.1 Comparisons and null checks

status = 'COMPLETED'
revenue > 0
ship_date IS NULL
customer.region <> 'INTERNAL'

Use IS NULL or IS NOT NULL for null testing. Comparisons such as value = NULL are invalid.

4.2 Sets and ranges

customer.region IN ('AMERICAS', 'EMEA')
status NOT IN ('CANCELLED', 'TEST')
order_total BETWEEN 10 AND 100

An IN list must contain at least one expression. BETWEEN is inclusive at both bounds.

4.3 Pattern and string predicates

customer_name LIKE 'Acme%'
customer_name ILIKE 'acme%'
CONTAINS(customer_name, 'Retail')
STARTS_WITH(order_code, 'WEB-')
ENDS_WITH(email_address, '.org')

LIKE requests the language's case-sensitive match semantics. ILIKE requests a case-insensitive match. Target compilers must preserve the configured Semantic Hub collation behavior or report that an equivalent mapping is unavailable.

4.4 Boolean composition

status <> 'CANCELLED'
AND (customer.segment = 'ENTERPRISE' OR customer.segment = 'MID_MARKET')

Filters must bind to a Boolean result. Semantic Hub uses SQL-style three-valued Boolean logic: a predicate can evaluate to TRUE, FALSE, or NULL. A source row passes a filter only when the complete predicate evaluates to TRUE.

5. Scalar functions

The core functions below are supplemented by the scalar-language expansion. The parser and shared type checker recognize the expanded catalog. Target support depends on the argument types and whether the target can preserve the required conversion, precision, text and clock semantics.

Functions are part of the Semantic Hub language, not pass-through target functions. Adding a function requires defined arity, input types, result type, null propagation, deterministic behavior, and target mappings. Unknown functions are validation errors.

5.1 CASE expressions

Searched and simple CASE expressions are supported in RowExpression, AggregationExpression, FilterExpression, and AggregationFilterExpression when their complete result satisfies the containing contract:

CASE WHEN status = 'COMPLETED' THEN revenue ELSE 0 END

CASE status WHEN 'COMPLETED' THEN revenue WHEN 'REFUNDED' THEN -revenue ELSE NULL END

Whole-number literals infer INTEGER. Therefore CASE WHEN STATUS = 'Open' THEN 1 ELSE 0 END can be declared INTEGER. Decimal-point or exponent literals infer DECIMAL; introducing a decimal result branch widens the CASE result to DECIMAL.

Every result branch must have a compatible type. An omitted ELSE is equivalent to ELSE NULL. Conditions in searched CASE must type-check to Boolean. Authors normally use AggregateMetric.filter for one intrinsic metric predicate; the compiler turns that property into a function-specific conditional aggregate input. Authored CASE remains necessary for multiple branches, non-null else values, and imported row formulas whose conditional value is itself part of the business definition.

5.2 Aggregate calls

Aggregate calls are legal only inside AggregationExpression. Their function names and parameters correspond to the canonical aggregation definitions in Metrics.json; examples include SUM(line_amount), COUNT(*), and CORRELATION(unit_price, quantity). Each row argument is bound and validated as RowExpression, and the compiler normalizes the complete call into a grain-anchored aggregate dependency before aligning it with other metrics.

The parser accepts standard Ossie/SQL forms and normalizes them to the explicit structural aggregation values:

The older explicit call spellings AVERAGE(expr), COUNT_NON_NULL(expr), and COUNT_ROWS() remain accepted input aliases, but the formatter emits the standard forms above. STDDEV_SAMP and VAR_SAMP remain available when authors want the sample semantics to be explicit.

SUM_DISTINCT and AVERAGE_DISTINCT deduplicate equal expression values. They do not perform entity-key deduplication. Key-based deduplication is modeled structurally with aggregation_parameters.deduplicate_by_dimensions on SUM or AVERAGE.

Ordered-set percentiles accept standard syntax such as PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY revenue). Binding normalizes the ordered expression to AggregateDefinition.expression and the percentile argument to aggregation_parameters.percentile. APPROX_PERCENTILE(expr, p) normalizes the same two semantic inputs without WITHIN GROUP.

Names such as MIN and MAX can denote scalar or aggregate operations. The parser retains the call syntax, and binding resolves the operation from its argument phases and the containing expression contract. Metric or scalar arguments use a defined scalar overload when one exists; row-field arguments in AggregationExpression introduce an aggregate call. Mixing row and metric arguments within one call is invalid unless a separately specified canonical operation defines that signature.

6. Binding contexts

The same parser serves different expression properties, but each property supplies a binding context.

6.1 RowExpression and FilterExpression

A field expression uses RowExpression and may bind verified source columns or authored row-level fields in its dataset's row-expression namespace. AggregateMetric.expression binds its sole canonical row-level input, including an identifier that resolves to either kind, and AggregationParameters.expression binds the canonical second input of a two-input statistical aggregation. This field-or-column binding exists only inside expression text; structural FieldReference properties resolve authored field objects only. These row-level expressions cannot reference aggregate metrics. Metrics and aggregation parameters accept only canonical GENERIC expressions; target-specific metric text is permitted only under MetricOverride.dialect_expressions as a last-resort replacement. Field export variants remain in the field's dialect_expressions, while imported native text retained for history and future forward-engineering belongs in imported_expression.

Dataset filter, AggregateMetric.filter, MetricInput.filter, and static filter-context include properties use FilterExpression. They bind row-level fields in their applicable scope and must return Boolean.

The public Query contract specializes these expression contexts: every reference in where or having must use an unqualified local name or dataset.member, resolving an authored field or a named metric where the clause permits metrics. The dataset qualifier identifies an authored dataset, never a join alias, and must resolve to an unambiguous reachable occurrence. Public query expressions cannot traverse explicit relationship paths or access physical columns directly. These restrictions also apply inside function arguments; they do not change model-authored expressions. See the query-specific definitions in Query schema.

An identifier in a model-authored FilterExpression may terminate at a verified source/query output column or an authored row-level field, either locally or through explicit authored joins. Every traversed relationship must be scalar from the expression's anchor: ONE_TO_ONE or MANY_TO_ONE. An ordinary filter cannot cross a ONE_TO_MANY, many-to-many, cross, or otherwise multivalued path because the terminal path would yield a set rather than one value per anchor row. The current language defines no implicit EXISTS, universal quantifier, or row-deduplication interpretation for such a set; validators reject it. See Dataset filters for examples and rationale.

An AggregateMetric.filter predicate is scoped to that metric. Compilation wraps the metric's aggregate input in a function-specific CASE rather than pushing the predicate into the query-level WHERE. This preserves rows required by sibling metrics. For a two-input statistic, the same predicate conditions both row inputs so their population and pairing grain remain aligned. A MetricInput.filter is scoped to that input's isolated evaluation branch.

Query having uses AggregationFilterExpression. It may reference aligned metrics and selected grouping fields, and its complete result must be Boolean. A direct row-level field that is neither grouped nor proven functionally dependent on the grouping keys is invalid.

6.2 AggregationExpression

DERIVED.expression uses AggregationExpression and may combine metric references, scalar literals, and explicit aggregate calls at the aggregate evaluation stage:

expression: "total_revenue - total_cost"

A row-level field is invalid as a direct operand but may occur inside an aggregate call, for example existing_margin + SUM(line_amount). The compiler normalizes each aggregate call into a grain-anchored aggregate dependency equivalent to an AggregateMetric, then evaluates and aligns that dependency with referenced metrics before applying the enclosing arithmetic. Metric references can be qualified, but qualification does not prove compatible grain or fanout-safe alignment.

6.3 AggregationFilterExpression

Query having uses AggregationFilterExpression after grouped metric branches have been aligned. It accepts metric references, selected grouping fields, and explicit aggregate calls using the same normalization rules as AggregationExpression, but requires a Boolean result. This is a separate binding context from row-level FilterExpression; the compiler must not push it into WHERE unless equivalence is proven.

6.4 Type checking

Semantic Hub resolves every reference and assigns types before target compilation. Validation rejects, among other errors:

• unresolved or ambiguous references;
• a non-Boolean filter;
• arithmetic on incompatible types;
• invalid function arity or argument types;
• row-level references outside an explicit aggregate call in an aggregation expression;
• aggregate metric references in a row-level filter;
• comparisons with NULL instead of IS NULL;
• operations unsupported by every selected deployment target when no valid mapping exists.

7. Null and numeric behavior

Arithmetic generally propagates NULL. Boolean operations use three-valued logic. COALESCE and NULLIF provide explicit value control inside an expression. Metric null_handling.null_result applies only after a non-empty metric calculation has produced its final value; it never rewrites an intermediate NULL.

The / operator represents strict numeric division. A zero denominator is an error. When an authored row or aggregation expression requires a portable zero-denominator fallback, use the canonical function:

DIVIDE(numerator, denominator)
DIVIDE(numerator, denominator, alternate)

Both arguments are numeric and are evaluated once. A NULL denominator returns NULL. A zero denominator returns NULL in the two-argument form or the type-compatible alternate value in the three-argument form. The result otherwise equals numerator / denominator. The compiler lowers DIVIDE to target SQL that preserves these rules, commonly with a CASE or target-native safe-division operation.

This operator-local policy is required for RowExpression and AggregationExpression because one expression can contain several divisions with different intended fallbacks. Structural ratio-like metric types instead expose null_handling.divide_by_zero on the operation itself.

AggregateMetric.filter lowers excluded rows to NULL inside the aggregate input. These NULLs retain their aggregate-input meaning and are not subject to the metric's final null_result policy. Similarly, empty_result is evaluated only after the compiler determines that an aggregate, frame, or event calculation had no contributing rows.

Numeric type inference must preserve declared metric precision and scale where possible. Target compilers may widen numeric types to avoid loss, but they must report a validation or deployment error rather than silently narrowing a result.

8. Validation and formatting

Expression validation occurs in stages:

1. Lex and parse the source string.
2. Resolve names in the property's binding context.
3. Type-check operators and functions.
4. Validate evaluation phase, grain, and dependency rules.
5. Check target capabilities and mappings.

Diagnostics include a stable code, severity, message, source span, containing YAML property, and remediation when available.

The complete validation order, namespace construction, path algorithm, and conformance requirements are defined in YAML model validation requirements.

The canonical formatter normalizes whitespace and keyword spelling while preserving semantics. Formatting does not rename references, reorder operands, or apply algebraic simplifications.

9. Portability boundary

The canonical language includes only behavior Semantic Hub can define consistently and compile correctly. Target-native syntax belongs in documented override properties such as a metric compiler override, not in dataset or metric filter properties or DERIVED.expression.

The language and its typed intermediate representation are separately versioned. A language-version change may add syntax or functions; an IR-version change may alter generated compiler structures without changing authored YAML.

See the expression schema reference for the expression data model.

10. Planned scalar-language expansion

Phase-one language profile, 2026-10-23. The parser and shared type checker recognize this catalog, and compilers lower supported overloads or report a target capability limitation. Support is not uniform across targets. These examples describe the intended semantics; check Support and limitations and your selected target before deploying a model.

Convert values explicitly

A field's data_type describes its result. To change a source value's type, use an explicit expression:

CAST(customer_id AS VARCHAR)
TO_DATE(event_timestamp)
TO_INTEGER(amount)

The convenience functions TO_STRING, TO_INTEGER, TO_DECIMAL and TO_DOUBLE follow the corresponding casts. TO_DATE/DATE share one date-conversion behavior; TO_TIMESTAMP/TIMESTAMP share one datetime-conversion behavior. TO_DOUBLE produces an approximate FLOAT value, not an exact DECIMAL.

Conversions preserve NULL. Numeric text uses a decimal point and may include a sign and exponent, with no grouping, currency or surrounding spaces. Date text uses YYYY-MM-DD; time uses HH:MI:SS; datetime uses YYYY-MM-DD HH:MI:SS, with up to six fractional digits. The phase-one profile uses signed 64-bit integers and DECIMAL(38,9) for explicit decimal casts. Invalid input and overflow produce errors. Empty text does not become NULL automatically, and a numeric value does not become a date using an assumed epoch or spreadsheet date origin.

Examples of required results are TO_INTEGER(-9.7) = -9, conversion of a date to midnight datetime, and conversion of a datetime to its date without shifting the timezone. A time alone cannot become a datetime without a date. Parameterized casts, timezone-bearing conversions and locale-dependent parsing need further specification. TRY_CAST, which would return NULL for conversion failures, is a separate follow-up capability.

Work with dates and times

Weeks start on Monday and week numbering follows ISO rules. Month arithmetic clips to the last valid day: adding one month to January 31 reaches February's last day. DATEDIFF counts boundaries, so crossing midnight counts as one day even when less than 24 hours elapsed. These scalar operations use Gregorian calendar rules; they do not inherit a model's fiscal calendar or perform DST conversion. Date parts must be recognized literal units, not field names.

Build and search text

The required string expansion covers CONCAT and ||, LENGTH, LOWER, UPPER, TRIM/LTRIM/RTRIM, LEFT/RIGHT, SUBSTRING/SUBSTR, REPLACE, SPLIT_PART, POSITION/CHARINDEX, CONTAINS, STARTSWITH/STARTS_WITH, ENDSWITH/ENDS_WITH, LIKE/ILIKE and REGEXP_LIKE.

CONCAT(first_name, ' ', last_name)
SUBSTRING(account_code, 1, 3)
POSITION('-' IN account_code)
SPLIT_PART(account_code, '-', 2)

Positions and split-part numbers start at 1. A search with no match returns 0; a substring or split part beyond the available text returns empty text. Negative lengths and nonpositive positions are invalid. CONCAT propagates NULL rather than skipping missing values. Use an explicit conversion for numeric inputs. Positions count Unicode code points. Trimming removes ASCII space, tab and line whitespace. Regex matching searches case-sensitively using a supported constant pattern; dynamic patterns and nonportable constructs are rejected. Empty text and NULL must remain distinct, which can limit target support. REGEXP_LIKE is in this required phase; regex extraction, replacement and counting are follow-up functions.

Calculate with numbers

The required numeric catalog comprises ABS, ROUND, FLOOR, CEIL/CEILING, TRUNC/TRUNCATE, MOD and %, SIGN, POWER, SQRT, EXP, LN, LOG(base,value), LOG10, GREATEST and LEAST. Some already have limited support; the expansion requires consistent typing, NULL behavior and target execution for the whole set.

Rounding ties go away from zero; truncation goes toward zero. FLOOR moves toward negative infinity. Thus ROUND(-2.5) is -3, TRUNC(-2.5) is -2 and FLOOR(-2.5) is -3. MOD follows the dividend's sign, so MOD(-7,3) is -1. Zero divisors, invalid logarithms, negative square roots and overflow cause evaluation failures.

ROUND(net_amount, 2)
FLOOR(quantity / package_size)
LOG(10, sales_amount)

POWER produces an approximate FLOAT result, including 1 for zero raised to zero. GREATEST and LEAST return NULL if any argument is NULL. Existing scalar MIN/MAX retain their own behavior. Floating-point-to-text conversion needs an exact target serialization rule and may be unsupported. This expansion does not imply automatic numeric narrowing.

Availability and interoperability

These requirements follow the required scalar families in the Ossie expression proposal. They are the first phase of language parity. Conditional aliases, aggregate FILTER syntax and general window expressions are separate work; format-driven date parsing and formatting are experimental upstream.

An importer must preserve an actual type change in the expression, including Tableau fields customized from integer to string or datetime to date. Matching function names alone cannot preserve format-dependent text or numeric date origins. A target needs an equivalent native expression or source projection; otherwise compilation must explain the unsupported case. Accepting a formula in an editor does not by itself verify its deployed results.

Specific limits include Oracle's inability to distinguish empty native text from NULL, native TIME limitations on Oracle and Databricks, and BI restrictions on decimal precision, dynamic Unicode operations and statement-stable clocks. FLOAT-to-text serialization and Oracle text-to-decimal/float parsing require additional target qualification. Constant folding can support some BI formulas whose equivalent dynamic form remains unsupported. Native case conversion also varies between engines for Unicode characters. SQL LOWER/UPPER therefore support qualified constant inputs and reject dynamic inputs when invariant conversion cannot be guaranteed. Literal text searches use explicit binary comparison rules rather than inheriting source collation.