Universe Formation

Understanding how 0sql builds query paths through your semantic model.

Overview

When you run zsql deploy, the Formation Engine on the 0sql service generates all possible paths from every table to every reachable field. These paths form universes: complete, cardinality-safe data access routes that the planner uses to turn a spec into SQL.

Understanding universe formation helps you:

• Design efficient semantic models – keep join graphs sane and avoid double-counting
• Understand query routing – why a particular table/universe was chosen for a query
• Reason about dimensionality – which dimensions are allowed to group a given measure
• Validate measure eligibility – why some combinations of measures/dimensions are invalid

If you are familiar with other tools, a 0sql universe plays a role similar to a BusinessObjects Universe or a Looker Explore, but it is derived automatically from your YAML model and enforced with strict cardinality and routing rules.

What is a Universe?

A universe is a set of paths from a single root table to all fields reachable via joins. Each table in your model has its own universe.

flowchart TD
    subgraph StoreSalesUniverse["Store Sales Universe"]
        SS["Store Sales (Root Table)"]
        SS --> D["Date Dimension"]
        SS --> C["Customer Dimension"]
        SS --> S["Store Dimension"]
        C --> CA["Customer Address"]
        CA --> ST["State Lookup"]
    end

The Store Sales universe contains paths to:

• All fields directly on Store Sales
• All fields on Date, Customer, Store (via direct joins)
• All fields on Customer Address (via Customer)
• All fields on State Lookup (via Customer → Customer Address)

How Universes Are Built

On deploy, the Formation Engine runs on the service and discovers all paths from each table to every reachable field by following your join definitions. It applies cardinality and measure-expansion rules so that only valid, cardinality-safe paths are available to the planner. You don’t configure paths directly: they are derived from your semantic model. zsql deploy reports the path count (N paths) and any formation warnings, such as two routes of equal cost.

What You Need to Know: Measure Expansion

Not all paths allow measure aggregation. When you design joins, these rules determine whether measures can flow through a path (and thus whether a measure can be combined with dimensions on the other side of that join):

# This path allows measures to flow through:
customer_address:
  cardinality: many_to_one
  allow_measure_expansion: true

# This path blocks measures (dimensions only):
customer_orders:
  cardinality: one_to_many
  # Measures from Customer cannot aggregate across Orders

Getting cardinality and measure expansion right is what matters when you design your semantic model; the rest is handled by the engine.

Universe Selection

When a spec arrives with specific measures and dimensions, the planner selects which universe(s) can answer it: a universe must contain paths to all requested dimensions and any requested measures. When several universes qualify, the planner ranks them (e.g. by tier, cost, partition fit) and picks the best fit. Details are in Semantic Routing.

Example: For a query like Total Revenue by Customer State, the Store Sales universe is used because it has the measure (Total Revenue) and can reach the dimension (Customer State via Customer → Customer Address). The Customer universe is not used because it doesn’t contain that measure. If you see errors like “dimension not in universe,” it means no single universe has a path to all requested fields, often a join or cardinality issue in your model.

Path Cost Calculation

Path cost influences table selection when multiple tables can answer the same query:

A join’s cost is the sum of the two tables’ cost values, and a path’s cost is the sum of the joins it takes. Cardinality does not change the cost; it decides whether measures may travel the path at all.

# Table costs (set in tbl.*.yml)
Store Sales: cost: 100    # Fact table - higher
Customer: cost: 10        # Dimension - lower

# The Store Sales -> Customer join therefore costs 110

Lower total cost = preferred path.

Blended Dimensions

Dimensions with the same extended_blend_group can be used interchangeably:

# In tbl.store_sales.yml
- name: Sale Date
  extended_blend_group: date_dimension

# In tbl.catalog_sales.yml  
- name: Order Date
  extended_blend_group: date_dimension

The engine creates blend paths that allow queries to:

1. Use “Sale Date” when querying Store Sales measures
2. Use “Order Date” when querying Catalog Sales measures
3. Automatically blend when both measure types are requested

Best Practices

1. Keep join graphs simple: Star schema is faster than complex snowflake
2. Use appropriate cardinality: Match actual data relationships
3. Be careful with measure expansion: Only enable when truly safe
4. Use cost hints: Lower cost on dimension tables, higher on facts
5. Leverage tiers: hot datasources are preferred when several can answer the spec

Next Steps

• Semantic Routing: how a spec selects a table and datasource
• Cost Optimization: tuning table and join costs
• Explain: see the universe and paths a spec resolved to