Semantic Model

How a 0sql project describes tables, fields, expressions and relationships, and what the planner does with them.

Overview

The semantic model is the core of 0sql. It describes warehouse tables as named dimensions and measures with joins between them, so that a query spec naming fields can be planned into one SQL statement for your warehouse.

Critical Rules

Before diving into components, understand these non-negotiable design principles:

One Name, One Concept

A field name identifies a single concept across your entire semantic layer. There are no table-qualified names like table.field_name.

Define the same name on more than one table and 0sql treats every definition as that one concept. “Total Revenue” on store_sales and “Total Revenue” on catalog_sales are both valid ways to answer a query for Total Revenue. At query time the planner picks the table whose reachable dimensions cover what the query asks for, and breaks ties by table cost (lower wins), so you can steer the choice.

The rule therefore cuts the other way: distinct concepts need distinct names. If “Country” means the caller’s country on one table and the ship-to country on another, name them “Caller Country” and “Ship Country”, or 0sql will merge them.

Benefits:

Unambiguous references: “Total Revenue” means exactly one thing, however many tables can produce it
Compound measures: [Total Revenue] - [Total Cost] works without table prefixes
Conformed dimensions by naming: a shared “Date” or “Customer” name is what lets tables blend
Simple specs: a request names fields, never tables, so callers and agents need no disambiguation

Considerations:

Names are scoped per type: “Days To Recontact” can be both a dimension (the bucket) and a measure (the average), since a query asks for one or the other
Naming is where you apply domain knowledge: same thing, same name; different thing, different name

# In tbl.store_sales.yml
- name: Total Revenue    # one concept...

# In tbl.catalog_sales.yml
- name: Total Revenue    # ...defined on a second table: either can serve it,
                         #    chosen by the dimensions requested, then by cost
- name: Catalog Revenue  # a different concept gets a different name

No Many-to-Many Relationships

many_to_many cardinality is not supported. This design constraint prevents double-counting bugs and ensures predictable aggregations.

Workaround: Use junction tables with two separate relationships.

# ✗ NOT SUPPORTED
users_roles:
  cardinality: many_to_many

# ✓ USE JUNCTION TABLE
users_user_roles:
  left: Users
  right: User Roles
  cardinality: one_to_many

user_roles_roles:
  left: User Roles
  right: Roles
  cardinality: many_to_one

Why: This approach makes relationships explicit and prevents common aggregation errors where measures get counted multiple times.

Key Components

Tables

Tables define semantic metadata for physical database tables:

Dimensions: Categorical fields for grouping
Measures: Aggregatable quantities
Cost: Query optimization hints
Partitions: Data availability constraints

Learn about tables →

Fields

Fields are the building blocks of your semantic model:

Dimensions: Attributes for grouping and filtering
Measures: Quantities to aggregate
Data Types: string, integer, decimal, date, etc.
Field metadata: descriptions, synonyms and tags, returned by the fields endpoint; format and display_type, stored for your client

Learn about fields →

Expressions

Expressions define how fields query the database:

SQL: Direct SQL expressions
Lookups: Dimension field indicators
Arrays: Multi-value fields
Primary Keys: Unique identifiers

Learn about expressions →

Relationships

Relationships define how tables join:

Cardinality: one-to-one, one-to-many, many-to-one
Join Types: inner (default), left, right
Join Conditions: SQL expressions

Learn about relationships →

How It Works

Model Definition: you define tables, fields and relationships in YAML
Deployment: zsql deploy validates the project and ships it to 0sql
Universe Formation: the planner precomputes every join path from every table
Query Planning: your application POSTs a query spec and a security context; the planner picks tables and paths and writes the SQL
SQL Returned: the response is one SQL statement in the datasource’s dialect, plus the datasource it targets
Your application runs it: 0sql never connects to the warehouse

Advanced (in-depth and extra)

The Advanced subsection below covers deeper and optional semantic-model features: exclusions, inclusions, semantic routing, and cost optimization. The core basics (tables, fields, expressions, relationships, imports) are above; Advanced is for more in-depth and extra scenarios.

Imports: Reuse field definitions across tables
Partitions: Define data availability constraints
Snapshot Measures: Point-in-time analysis
Exclusions: Filter-based measure exclusions
Inclusions: Multi-level calculations
Decorators: Temporal, window and contribution transforms applied in the query spec; see Projections

Advanced features →

Next Steps

Read about tables
Explore dimensions and measures
Learn about expressions
Understand relationships
Go deeper in Advanced