Filter semantics

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

This document defines how Semantic Hub binds Boolean expressions, represents them in typed compiler IR, combines filters from different scopes, applies metric filter_context, preserves security constraints and null behavior, and proves filter pushdown safe.

Expressions.json, Dataset.json, Metrics.json, and Query.json are authoritative for authored shape. Expression language defines syntax, YAML model validation requirements defines binding order, and Grain and multi-fact query planning defines physical clause planning.

1. One expression contract, several scopes

Authored filters use FilterExpression: a non-empty GENERIC expression string that follows row-expression binding rules and whose complete result must type-check to Boolean.

The same syntax appears in several properties, but scope and lifetime differ:

Target SQL placement follows semantic scope. Two filters with identical text are not interchangeable when their owners differ.

2. FilterExpression binding

2.1 Anchor and namespace

Every filter has an explicit anchor dataset or grouped-result context. In row context an identifier can terminate at:

• a verified column returned by the anchor dataset's table or every applicable query variant;
• an authored row-level field in the anchor dataset; or
• either kind of row value reached through an explicit scalar join path.

Every non-terminal path component is an authored join name. The binder does not substitute dataset names, infer shortest paths, or fall back from a missing authored object to an unrelated same-name value.

filter: >-
  status <> 'CANCELLED'
  AND customer.region IN ('AMERICAS', 'EMEA')

The bound references retain stable field or column identity, source span, anchor and terminal datasets, complete relationship path, type, nullability, and determinant metadata.

2.2 Scalar paths only

Every row-level relationship edge must yield at most one terminal value per anchor row. ONE_TO_ONE and MANY_TO_ONE are eligible from the owning dataset. ONE_TO_MANY, many-to-many, cross, and otherwise multivalued paths are invalid in the current FilterExpression contract.

# Invalid when items is ONE_TO_MANY from orders.
filter: "items.returned = TRUE"

That expression could mean “any item,” “every item,” or “filter item rows.” The current language defines no implicit quantifier. A join plus DISTINCT neither chooses a meaning nor proves grain safety.

Future EXISTS or universal predicates require explicit grammar, correlation, path, and quantifier contracts. Until then, authors model a fanout-safe dataset, field, or metric for the intended many-side behavior.

2.3 Phase and result type

A row-phase filter cannot reference metrics, aggregate calls, cumulative results, conversions, or windows. Its complete result must be BOOLEAN after name and type resolution.

Query.having has the separate post-aggregation contract defined in Post-aggregation filtering.

3. Generated typed filter IR

Authors persist source text. The parser and compiler generate a typed intermediate representation; it is not another authored YAML format.

Each IR node records, as applicable:

• canonical operator or function identity;
• typed child nodes and inferred nullability;
• bound field or source-column identity;
• anchor and complete relationship path;
• literal type and canonical value;
• three-valued Boolean behavior;
• source span and containing property;
• filter owner and semantic scope;
• dependency dimensions used by filter-context projection;
• evaluation phase; and
• target capability requirements.

The root carries a Boolean type and a stable canonical identity used by planning, lineage, caching, diagnostics, and common-predicate detection.

Formatting or algebraic normalization must not change three-valued semantics. In particular, the compiler does not reorder expressions that may differ under null propagation, collation, or target function behavior without an equivalence proof.

4. Three-valued Boolean behavior

Predicates evaluate to TRUE, FALSE, or NULL. A row passes a filter only when the complete predicate is TRUE.

Use IS NULL and IS NOT NULL for null tests. Comparisons such as value = NULL are invalid.

Optional scalar relationships widen nullability. If an order has no related customer, customer.region = 'EMEA' is unknown and excludes the order. customer.region IS NULL can intentionally retain the missing relationship and any related row whose region itself is null; distinguishing those cases requires a separately modeled relationship-presence field or key.

Authors should use care with nullable values in NOT IN, NOT, OR, and CASE. The target compiler must preserve canonical SQL-style three-valued truth tables rather than coercing null to false inside intermediate nodes.

5. Effective branch filters

For one metric branch, the compiler constructs filters in semantic layers.

source eligibility
  dataset intrinsic filter
  AND mandatory security constraints

metric query context
  projected inherited Query.where
  AND filter_context.include

metric-local population
  AggregateMetric.filter or MetricInput.filter

The logical populations intersect, but their SQL placement can differ. Dataset and security predicates constrain source eligibility. Query-context predicates constrain the branch. Aggregate-local predicates commonly become conditional aggregate inputs so sibling metrics retain their rows.

Query.having runs only after grouped metric branches have been aligned and projected.

5.1 Dataset filters

Dataset.filter defines which rows belong to the dataset before fields, keys, relationships, or metrics consume it. It is included in source-contract validation and cannot be removed by filter_context.

If a dataset filter follows an optional scalar relationship, generated joins and predicate placement must preserve its authored row-membership meaning. A target rewrite that accidentally turns a required test into a different outer-join condition is invalid.

5.2 Query where

Query.where describes the query-requested row population. By default it is inherited by every compatible metric branch.

For multi-fact queries, the compiler binds or translates the predicate independently for each required branch. Similar field names do not establish shared identity. If a query filter cannot be resolved for a branch and that metric does not explicitly exclude it through filter_context, the query fails.

Time-aware metrics may expand source rows outside a visible query time bound. Time intelligence defines when a time predicate limits output periods versus intrinsic source eligibility.

5.3 Aggregate metric filters

AggregateMetric.filter is always part of one aggregate's business definition:

- name: completed_revenue
  metric_type: AGGREGATE
  expression: revenue
  aggregation: SUM
  filter: "status = 'COMPLETED'"

Conceptually:

SUM(CASE WHEN status = 'COMPLETED' THEN revenue ELSE NULL END)

It must not become a shared WHERE predicate when sibling metrics use other populations. Counts, distinct aggregates, ordered-set functions, and paired statistics require the function-specific conditioning defined in Aggregations and additivity.

The excluded-row null is an intermediate non-contribution marker. It is never replaced by the metric's final null_result before aggregation.

5.4 Metric-input filters

A MetricInput.filter applies only while evaluating that named or inline input:

- name: completed_revenue_share
  metric_type: RATIO
  numerator:
    metric_ref: total_revenue
    filter: "status = 'COMPLETED'"
  denominator:
    metric_ref: total_revenue

The numerator and denominator remain separate dependency nodes even when a target later fuses their physical scans. The filter participates in the numerator's canonical identity, lineage, cache eligibility, time expansion, and null state.

For metric_ref, the filter binds from the referenced metric's evaluation dataset and relationship graph. For inline_metric, it binds from the containing metric's dataset, which is also the anonymous aggregate's anchor. A compiler must not resolve a cross-dataset named-input filter from the consumer's namespace.

5.5 Static filter-context inclusion

filter_context.include is added after query-filter inheritance has been projected. It binds from the metric's containing dataset context. It is not removable by the current query and cannot grant access to rows excluded by dataset or security constraints.

6. Filter context

Metric.filter_context controls only inherited Query.where predicates. It never changes dataset filters, security constraints, an aggregate's own filter, or a metric-input filter.

6.1 Modes

exclude_dimensions is legal only with RELATIVE. keep_only_dimensions and exclude_dimensions are mutually exclusive.

filter_context:
  mode: RELATIVE
  exclude_dimensions: [product.color]

6.2 Dependency sets

The typed IR computes the set of bound authored field identities used by every Boolean subexpression. Context lists are structural FieldReference values and also bind authored identity. Matching is by bound identity and path role, not by text or physical column name.

A predicate on billing_customer.region is not the same filter dependency as shipping_customer.region even when both terminate at the same dataset field.

6.3 Safe conjunct projection

Query schema provides one Boolean expression rather than a list of filters. The compiler can project independent top-level conjunctions:

order_date >= DATE '2026-01-01'
AND product.color = 'BLUE'
AND customer.region = 'EMEA'

A metric excluding product.color retains the time and region conjuncts and replaces the color conjunct with Boolean identity TRUE in that metric's context.

Projection through OR, NOT, searched CASE, or another mixed Boolean subexpression is not generally semantics-preserving. For example:

product.color = 'BLUE' OR customer.region = 'EMEA'

Removing only the color leaf changes the expression to TRUE OR ..., while removing the whole expression also discards the region condition. When a mixed subexpression contains both retained and excluded dependencies and cannot be factored equivalently, filter-context application fails with a structured non-separable-filter diagnostic. It must not choose a convenient rewrite.

6.4 Branch isolation

Metrics with different effective query contexts normally require separate source or aggregate branches. A compiler may combine them only when conditional evaluation or another target construct is proven equivalent for every metric, join, null, time, and security rule.

7. Security constraints

The current public model schemas do not define an authored security-filter property. Security constraints are execution inputs supplied by an authenticated policy layer and bound before planning. This is the required architecture contract, not a claim that the current Java QuerySqlCompiler API enforces authorization: that API does not yet accept security constraints. Until an explicit security-input API is implemented, callers that require row security must use a protected source or an equivalent fail-closed policy layer and must not present generated SQL as compiler-enforced authorization.

This separation prevents a model author or query consumer from weakening authorization. A security constraint:

• is mandatory for its principal and policy version;
• is combined with applicable dataset source eligibility;
• cannot be removed by filter_context, query text, a target override, or time expansion;
• applies to every branch and dependency that reads the protected dataset;
• is enforced before data enters a shared cache or materialization; and
• participates in plan and cache identity unless the cache is itself secured by an equivalent target policy.

Security binding uses the same typed reference and Boolean rules where the policy is expressible as a row predicate, but policy parameters and principal values are typed runtime parameters rather than interpolated SQL text.

An explain result identifies the policy and protected dataset without exposing secret values or unauthorized row examples. Diagnostics are similarly redacted according to governance policy.

7.1 Outer joins and protected dimensions

Moving a security predicate between a join ON clause and a branch WHERE clause can change whether an unmatched anchor row remains visible. The policy contract decides the intended protected population; the optimizer cannot make that choice. If the policy cannot be preserved with the requested relationship semantics, the query fails closed.

7.2 Caches

A cache populated without an applicable security predicate cannot satisfy a secured query unless an equivalent row-level target security mechanism is proven. Cache keys include policy identity and version, principal or shareable security partition, bound filter identity, and relevant runtime parameters.

8. Pushdown

Filter pushdown is an optimization after binding and semantic planning. It is never a name-resolution or fanout-repair strategy.

8.1 Pushdown proof

A predicate may move closer to a source only when the compiler proves that the move preserves:

• filter owner and scope;
• three-valued Boolean results;
• outer-join row preservation;
• relationship cardinality and dataset grain;
• sibling metric populations;
• time-frame lookback and lookahead rows;
• aggregate distinctness, ordering, and pair populations;
• security policy; and
• target collation, type coercion, and function semantics.

8.2 Common placements

8.3 Optional relationships

For a left relationship, placing a terminal predicate in WHERE can remove unmatched anchor rows; placing it in ON can retain them with null related values. These are different meanings. The compiler chooses placement from the bound filter's owner and intended anchor population, not merely from SQL cost.

8.4 Common-factor sharing

Two branches may share a pushed scan only for the conjunction of predicates that are semantically identical in both branches. Branch-specific residual predicates remain isolated. Text equality is insufficient when anchors, path roles, parameters, collations, time contexts, or security owners differ.

9. Post-aggregation filtering

Query.having is evaluated after grouping and metric-branch alignment. Under the current schema:

• it is authored with AggregationFilterExpression syntax;
• it may bind named metric references in the aggregation namespace;
• every authored field or verified source column must be grouped or functionally determined; and
• it must not introduce a hidden grouping key that changes output grain.

The compiler may render a physical HAVING clause or an outer WHERE over a grouped CTE. The physical clause is irrelevant as long as the post-aggregation phase and result are preserved.

An arbitrary ungrouped field or row-level multivalued path is a validation error. An unresolved, ambiguous, or inaccessible metric reference is also an error.

10. Null and empty behavior

Filter nulls control row eligibility; metric null policies control calculated results. They are separate boundaries.

• A null predicate excludes the row because only TRUE passes.
• A row excluded by an intrinsic aggregate predicate contributes no aggregate input.
• An absent row population can trigger empty_result.
• A non-empty all-null population can produce a null aggregate and then null_result.
• fill_missing_periods: ZERO does not make a null filter predicate true.
• A COALESCE inside a filter is an explicit author choice and is type-checked before target compilation.

Blindly changing predicate nulls to false within subexpressions, or wrapping filter operands in COALESCE, can change NOT, OR, and optional-relationship behavior and is invalid without an equivalence proof.

11. Parameters, literals, and target rendering

Literals are parsed and typed in the canonical expression language. Target dialects render values through parameter binding or the target literal renderer; authored text is never concatenated with untrusted runtime input.

Target capability validation covers:

• Boolean and null semantics;
• string collation, LIKE, and ILIKE behavior;
• date and timestamp comparisons and time zones;
• numeric coercion and precision;
• scalar function equivalence;
• parameter types; and
• safe predicate placement around joins and aggregates.

When no exact target mapping exists, compilation fails. A target override on a metric does not override filters or security constraints.

12. Validation failures

Semantic Hub rejects conditions including:

• parse failure or a non-Boolean root result;
• unresolved, ambiguous, hidden, or inaccessible references;
• a metric or aggregate reference in a row-phase filter;
• a multivalued relationship path without an explicit relational quantifier;
• incompatible comparison or function operand types;
• value = NULL instead of IS NULL;
• a query predicate unavailable to a required metric branch;
• a filter_context dimension that does not resolve to the same bound identity used by the filter;
• a mixed Boolean expression that cannot be projected safely for exclude_dimensions or keep_only_dimensions;
• a security constraint omitted from any protected branch or cache;
• pushdown that changes outer-join, time-frame, aggregate, or sibling-metric populations;
• a having reference that is not grouped or functionally determined; or
• target Boolean, collation, parameter, or function behavior without an exact mapping.

Diagnostics include the filter owner, containing property, source span, anchor dataset, bound dependency and path, evaluation phase, effective context, affected metric branches, target when applicable, and remediation. Security-sensitive values remain redacted.

13. Explainability and conformance

An explain result exposes, for every metric branch:

• dataset and mandatory security predicates;
• the original query predicate and its bound typed dependencies;
• retained and excluded context conjuncts with reasons;
• static include, aggregate, and metric-input predicates;
• physical pushdown and residual placement;
• optional-join effects;
• output-only time bounds and expanded source intervals;
• post-aggregation predicates; and
• target parameterization and capability choices.

A conforming implementation tests:

1. local and scalar related field/column binding;
2. rejection of metrics and multivalued paths at row phase;
3. complete three-valued truth tables and optional relationships;
4. all filter owners and their non-interchangeable scopes;
5. FIXED, RELATIVE, exclude, keep-only, and static-include contexts;
6. safe conjunction projection and rejection of mixed non-separable Boolean expressions;
7. same-fact and multi-fact branch isolation;
8. function-specific intrinsic aggregate filtering;
9. fail-closed security propagation through joins, time expansion, and caches;
10. valid and invalid pushdown around outer joins, frames, and grouped results; and
11. positive and unsupported-capability behavior for Snowflake, PostgreSQL, DuckDB, Oracle, and Databricks.