Key Terminologies

Definitions of 0sql-specific and related terms, plus guidance on choosing the right modeling approach.

Key Terms

These terms are used throughout the docs. If you are new to 0sql (or to semantic layers in general), this is a good reference.

Fact table grain: The level of detail of a fact table (e.g. one row per order line, per day, per invoice). 0sql assumes safe cardinality; incompatible grains are handled via blending or by restricting measures to safe join paths.

Universe: The complete, cardinality-safe join tree reachable from a chosen root (fact) table. Conceptually, the “virtual table” formed by joining that fact to all dimension tables it can safely reach. The query planner uses universes for query routing and to decide which dimensions can group a given measure.

Automatic data blending: 0sql’s way of combining measures from multiple fact tables (often at different grains) in one query without you writing the joins. The engine aggregates each fact in its own universe and merges results on common dimensions. When dimensions from different tables should count as the same for that merge, you assign them an extended blend group. Industry terms you may hear: data blending, multipass SQL.

Extended blend group: A named group (e.g. activity_date) that you assign to dimensions so they are treated as one logical dimension during automatic data blending. Dimensions in the same group are semantically equivalent for the merge, so the planner can combine queries across tables that share that concept.

Snapshot measure: A measure that captures a value at a point in time (beginning or end of a period) rather than summing over the period. Used for stateful quantities: inventory, balances, membership counts. Not for transactional flows.

Compound measure: A measure whose expression references other measures and dimensions by name (e.g. [Total Revenue] - [Total Cost]). Resolved at query time: each referenced measure is planned on its own fact and the results are blended, so a formula can span fact domains (within one datasource). A referenced dimension is resolved within each measure’s own universe, so it may sit anywhere along that fact’s join path; it must be reachable from every fact involved. No shared universe is required. See also auto-leveled compound measure.

Auto-leveled compound measure: What a cross-domain compound measure becomes when the query groups by a dimension only some of its components can reach. 0sql excludes that dimension from the components that can’t reach it, aggregating them at the coarser grain, and joins the results on the dimensions all components share, so the leveled component repeats as a fixed total across the dimension it can’t see (Tickets per 1000 Orders by Contact Channel: tickets per channel, orders for the month). See: Compound measures

Complex dimension: A dimension whose expression references other dimensions (e.g. CASE WHEN [Status Code] = 'A' THEN 'Active' ...). Only dimensions can be referenced; not measures. Same universe required.

Inclusion measure: A measure that temporarily includes extra dimensions in an intermediate aggregation to compute correct results (like medians or averages), then re-aggregates at the query grain and drops those dimensions in the final result. This solves the problem of computing accurate statistics when underlying data has uneven counts per group (e.g., computing daily median account view hours from per-title data in a streaming company). Industry analogs for this pattern: Tableau’s INCLUDE Level of Detail, MicroStrategy level metrics. Looker does not support this pattern. See: Inclusions

Exclusion: Advanced control that removes dimensions from a measure’s grouping or filtering behavior (each can be set independently). Used for ”% of total” measures and other cases where you want a measure to ignore certain dimensions’ grouping or filters. Industry analogs: Tableau’s EXCLUDE Level of Detail , Power BI DAX filter-context functions (e.g. REMOVEFILTERS, ALLEXCEPT). See: Exclusions

Segment: A population: key dimensions that identify its members plus the criteria (dimension filters, measure conditions, top-n) that decide membership. Declared in the query spec under segments, never in YAML. Constrains the whole query, or with apply_to a single measure so a cohort can sit beside its baseline in one statement. include keeps members; exclude drops them. Planned once as a CTE and joined on the keys, so grain safety and row-level security carry through. See: Segments

Expanding segment: A segment with an expanding list that, besides its keys, carries companion dimensions into the query, so each row multiplies into one row per companion value sharing the key (“Sub Commodity (same Basket ID)”). Used for “same basket / same session” analysis. Applies to the whole query; pair it with distinct counts, since sums would overcount the fan-out.

Calculation: A derived column your application adds to a spec under calculations, as a SQL expression over semantic fields ([Name]@m, [Name]@d, [Alias]). With an aggregate it is a measure calculation; without one, a dimension calculation. Never modeled in YAML, never a raw column, validated when the spec is planned, and planned with the same guarantees as a modeled field. Contrast with a compound measure, which is modeled and deployed. See: Calculations

Decorated measure: A measure projection that carries a decorators entry in the spec: a time comparison (temporalize: year-over-year, month-over-month, as value or percent change), percent of total (contribute), or a window (window: moving average, running total, rank, lag/lead). Never in the model; planned as a final pass over the aggregated result, so the measure’s definition and security are unchanged. Date dimensions take truncate and extract the same way. See: Projections and decorators

When to Use What

Snapshot measure vs normal aggregation

  • Use a snapshot measure when the measure is stateful: inventory on hand, account balance, headcount. Summing across days is meaningless; you want the value at the boundary of the period (beginning or ending).
  • Use a normal measure (e.g. sum(amount)) for flows: revenue, transactions, units sold. These are additive over time.

Extended blend groups vs remodeling tables

  • Use extended blend groups when the same logical dimension (e.g. “activity date”) exists in multiple tables with different column names or grains, and you want one query to group or filter by that concept across those tables. No need to change table structure.
  • Remodel or pre-aggregate when you need a different grain or a dedicated bridge table to fix cardinality; blend groups do not replace proper relationships or grain alignment.

Inclusion, exclusion, or separate measure?

  • Use an inclusion when you need multi-level aggregation: aggregate at an intermediate grain (e.g., sum per account), then compute a statistic over those results (e.g., median across accounts). Essential for correct medians, averages, and percentiles when underlying data has uneven counts per group.
  • Use an exclusion when one measure should behave differently depending on which dimensions are in the query (e.g., “revenue ignoring category” for a %-of-total denominator, or “revenue only by product”). Exclusions control how dimensions affect grouping and filtering.
  • Define a separate measure when the formula or aggregation is genuinely different (e.g., a distinct measure with its own name and definition). Inclusions and exclusions tune aggregation behavior of the same underlying expression; they do not change the core formula.

Validating your model

  • zsql check validates the project on the service without deploying: syntax, references, joins, policies, tests.
  • zsql explain --expr "item category, store net paid" returns the SQL plus the datasource, the table route and the node tree the planner built, so you can inspect the generated SQL (CTEs, joins, grouping) before your application sends the spec.
  • zsql explore --expr "store net paid" lists the dimensions and measures that can still join the query, the quickest way to see that a relationship or blend group took effect.

If the SQL does not match what you expect, check relationships, grains, extended blend groups, and exclusion/inclusion settings against the docs.