Core Concepts

The fundamental concepts of 0sql’s semantic layer: tables, relationships, datasources, the query spec, and how a spec becomes one SQL statement.

What is a Semantic Layer?

A semantic layer is an abstraction that sits between your raw database tables and the applications that ask questions of them. It translates business-friendly field names (like “Total Revenue”) into one SQL statement against your physical database.

Benefits:

  • One request shape: Applications ask for fields by name and never write joins
  • Consistency: Single source of truth for measures and definitions
  • Routing: The planner picks the cheapest table and datasource that can answer
  • Security: Row-level policies are applied inside the generated SQL

Key Components

Tables

Tables (tbl.*.yml) represent physical database tables with semantic metadata. They define:

  • Dimensions: Fields to group data or generally provides descriptive information (e.g., Customer Name, Product Category)
  • Measures: Aggregatable quantities, typically numeric (e.g., Total Sales, Average Order Value)
  • Cost: Query optimization hint
  • Partitions: Data availability constraints

Example:

name: Store Sales
physical_name: store_sales
datasource: tpcds
cost: 100

# Partition logic helps the engine choose alternative tables when this one
# can't satisfy the query. A partition is a predicate on a dimension: a date
# or number range, or in_list on any dimension.
partitions:
  - dimension: Date
    predicate: between
    filter_value: 24m   # 24 months ago
    filter_value_end: 1d # yesterday

fields:
  - type: dimension
    name: Store Name
    data_type: string
    expression:
      sql: s_store_name

  - type: measure
    name: Total Revenue
    data_type: decimal
    expression:
      sql: sum(ss_sales_price)

Relationships

Relations (rel.*.yml) define how tables join together. They specify:

  • Left and right tables: Which tables to join
  • Join condition: SQL expression for the join
  • Cardinality: Relationship type (one-to-one, one-to-many, many-to-one)

Example:

datasource: tpcds

store_sales_store:
  left: Store Sales
  right: Store
  sql: left.ss_store_sk = right.s_store_sk
  cardinality: many_to_one

Datasources

Datasources (datasources.yml) describe the warehouses your tables live in. 0sql never connects to them; the planner needs two things:

  • Adapter: the SQL dialect to write. One of athena, bigquery, clickhouse, databricks, druid, duckdb, mysql, postgres, redshift, snowflake, sqlite, sqlserver, trino
  • Tier: hot, warm or cold. When several datasources can answer a spec, the planner prefers the hotter one

Connection details may sit beside them as metadata for your own application; 0sql never reads them, and secrets are stripped from every deploy.

Segments

A segment is a population: a set of members and the criteria that decide who is in it. “Customers who spent over 10,000 last year”, “stores opened after 2020”, “sessions that reached checkout”. Segments are not modeled in YAML. They are part of the query spec your application sends, under the segments key, so nobody changes the model to ask a cohort question.

A segment is made of two things:

  • Keys: the dimensions that identify a member (for instance Customer ID). They set the grain of the population and are what the planner joins on. Every segment needs at least one.
  • Criteria: filters in the same shape as the query’s filters (dimension filters, measure conditions, a top-n ranking), and/or measures whose fact table defines membership.

A segment constrains the whole query, or with apply_to only the named measure projections, so a cohort can sit beside its baseline in one statement: new-customer revenue next to total revenue, without a correlated subquery. mode is include (keep members, the default) or exclude (drop them, an anti-join).

{
  "name": "High spenders",
  "keys": ["Customer ID"],
  "filters": [{"field": "Total Revenue", "predicate": "greater_than", "value": "10000"}],
  "mode": "include",
  "apply_to": ["Total Revenue"]
}

The planner emits each segment once as a CTE and joins every consuming node to it on the keys, so a segmented measure keeps the grain safety and row-level security of the model: policies apply inside the segment’s CTE too. Populations compose by intersection: “top 5 products” plus a segment means the global top 5 intersected with the segment.

Expanding segments answer “same X” questions such as what else is bought in the same basket as sub-commodity A? Besides its keys, an expanding segment carries a companion dimension of the other members sharing the key into the query, so each row multiplies into one row per companion value. Pair them with distinct counts of the key, since sums would overcount the fan-out. See Segments.

Calculations

A calculation is a derived column your application adds to a spec: a rate, a ratio, a share, a margin, a difference, a bucket. It is a SQL expression written over the fields of the semantic layer, not over warehouse columns, so it inherits every definition, join and security rule the model already enforces. Calculations are not modeled in YAML; they live under the calculations key of the spec.

Calculations reference fields by name:

  • [Total Revenue]@m: a measure
  • [Item Category]@d: a dimension
  • [Margin]: another column of the same query, including other calculations

The formula decides what kind of column it is. With an aggregate function it is a measure calculation, computed over the aggregated result:

sum([Store Quantity]@m) / nullif(sum([Store Quantity]@m) + sum([Inventory Quantity On Hand]@m), 0)

Without one it is a dimension calculation, evaluated per row before grouping:

case when [Order Total]@d >= 500 then 'Large' when [Order Total]@d >= 100 then 'Medium' else 'Small' end

Raw column and table names are rejected (columns like x are not permitted in calculations), a formula is an expression and never a SELECT, and every reference must resolve to a field of the stated type. In the spec:

{
  "alias": "Sell Through Rate",
  "sql": "sum([Store Quantity]@m) / nullif(sum([Store Quantity]@m) + sum([Inventory Quantity On Hand]@m), 0)",
  "data_type": "decimal"
}

Calculations and compound measures. They look alike but sit at different layers. A compound measure is modeled in YAML, deployed with the project and available to every caller. A calculation lives in one request and needs no deployment. A calculation your applications keep sending is a signal to promote it into the model. See Calculations.

Decorators

A decorator is a transform on a projection in the spec, with no formula and no model change. Put one on a measure and the planner computes a new column from it as a final pass over the aggregated result: last year’s value, its share of the total, its 7-period moving average. The measure’s definition, joins, grain rules and row-level security are unchanged; the decorator only transforms the aggregate.

Three families cover measures:

  • Time transforms (temporalize): year-over-year, quarter-over-quarter, month-over-month, week-over-week, day-over-day, as the prior value or as percent change. The planner reads the query’s date dimension and its grain to work out the comparison period, so a month-grain query with a year-over-year transform compares each month to the same month last year.
  • Percent of total (contribute): a measure divided by its total, partitioned by the dimensions you name in partition_refs.
  • Windows (window): moving and running avg, sum, min, max, count, plus rank, dense_rank, percent_rank, ntile, lag, lead, first_value, last_value. Windows order by the query’s date dimension automatically.

Dimensions have their own: truncate a date to week, month, quarter or year, or extract a part of it (day of week, month name, year-month).

{"field": "Total Revenue", "alias": "Revenue LY", "decorators": [{"type": "temporalize", "transform": "year_over_year"}]}

A decorator is the fast path for a transform that has a name; a calculation is the general path for a formula that doesn’t. See Projections and decorators.

Core Design Principles

Fact table grain and join safety

Every fact table in your model has a grain: the level of detail at which each row is stored.

0sql’s universe formation and routing logic assume that:

  • Joins between tables respect their true cardinality (see Cardinality).
  • Many-to-many joins are modeled explicitly via bridge tables rather than hidden in a single relationship.

If two facts live at incompatible grains (for example, one is “daily inventory” and the other is “per‑transaction sales”), 0sql will:

  • Restrict measures to safe join paths when building universes: measures are only exposed on paths where every join is one-to-one (or explicitly allows measure expansion). On many-to-one or one-to-many paths, only dimensions are exposed, so measures are not double-counted.
  • Use automatic data blending across universes when no single path can safely serve all measures: each fact is aggregated in its own universe and the results are merged on common dimensions, in one statement.

How It Works

  1. Model in YAML. Tables, relationships, datasources and security policies live in a git repository.
  2. Deploy. zsql deploy sends the project to the service under the checked-out git branch. The service validates it, forms universes (every join route from every table) and runs tests/*.yml.
  3. Request. Your application POSTs a spec (projections, filters, calculations, segments) with a security context to POST /projects/{uid}/branches/{branch}/sql, or one line of shorthand as expr.
  4. Plan. The planner resolves field names, picks the universe that reaches every field, chooses join routes by cost, tier and partitions, applies the branch’s policies, and writes one SQL statement in the datasource’s dialect. Planning takes microseconds.
  5. Run. Your application runs the SQL against its own warehouse. 0sql never executes or stores anything.
zsql sql --expr "item category, store net paid" --context user.json

The response names the datasource and holds the statement. See the Query API for a full request and the SQL that comes back.

Project Structure

A typical 0sql project:

my-project/
├── project.yml              # name, uid, production_branch
├── datasources.yml          # one warehouse per key: name, adapter, tier
├── security.yml             # row-level security policies
├── .zsql                    # API key and server (gitignored)
├── models/                  # tbl.*.yml and rel.*.yml, any folder depth
│   ├── sales/
│   │   ├── tbl.orders.yml
│   │   ├── tbl.customers.yml
│   │   └── rel.sales.yml
│   └── inventory/
│       └── tbl.products.yml
└── tests/                   # planner assertions, run on every deploy
    └── revenue_positive.yml

Workflow

  1. Initialize: zsql init my-project
  2. Configure: describe your warehouses in datasources.yml
  3. Model: zsql new table NAME --datasource DS and zsql new relation --datasource DS, then edit the YAML
  4. Validate: zsql check, plus tests/*.yml
  5. Deploy: zsql auth --api-key zsk_... --server https://app.0sql.io once, then zsql deploy
  6. Query: zsql sql --expr "..." to see the SQL, then POST specs from your application

Next Steps