# 0sql

> Open source semantic layer that turns a business-terms query spec into one deterministic SQL statement without connecting to your warehouse.

0sql is an open source semantic layer from Strata Business Intelligence, LLC that returns SQL instead of running it. You describe tables, dimensions, measures, joins and row-level security policies in YAML, then send a query spec in business terms and receive one planned SQL statement, or an error explaining why the request cannot be resolved. It ships as a single static binary, `zsql`, under the MIT licence.

## What It Is

0sql is a semantic layer and SQL planner aimed at AI agents, embedded analytics, BI and data apps, and platform teams. A request names fields rather than columns; the planner finds the tables that hold them, the join routes between them and the aggregation each measure needs. The server never connects to the warehouse, executes nothing and stores no queries, so the caller runs the returned statement with its own credentials.

## How the workflow runs

The model lives as YAML in git, and a deployed branch is the checked-out branch. `zsql serve` runs the server locally, validates the model, runs tests and redeploys as files change. An application then POSTs a spec, or a shorthand expression line used from the CLI, and gets back SQL for one of 13 dialects, including Postgres, Snowflake, BigQuery, Databricks and DuckDB. The CLI can separately read a warehouse with a read-only connection to draft models (`zsql catalog`, `zsql generate`, `zsql sample`) and check answers with `zsql run`.

## Governance, determinism and refusals

- Security policies in the model become WHERE filters or CASE masks, driven by a security context sent with each request; a branch with policies refuses to plan without one.
- Measures from different fact tables are aggregated separately and joined on conformed dimensions to avoid fan-out.
- The same spec yields the same SQL, and `assert_sql` tests can pin statements so a model change that alters them fails the deploy.
- Unresolvable requests return typed errors, and near-miss field names are corrected and reported.

## Agent use

The README says `POST /mcp` exposes the same reads as MCP tools, so an agent can search fields and plan queries against the model instead of guessing at the schema. The site demonstrates a chat dashboard example in which an agent writes a spec, 0sql returns SQL, and the application runs it against the warehouse. The vendor's comparison table against Cube and MetricFlow is its own claim, drawn from their public documentation.

## Features
- Resolves business field names to SQL via a governed YAML semantic model
- Safe blending of measures across fact tables without fan-out
- Row-level security policies compiled into WHERE filters or CASE masks
- SQL generation for 13 dialects
- Deterministic output with assert_sql tests pinned on deploy
- Typed errors and field-name corrections instead of wrong results
- MCP endpoint, /fields, /explore and /explain endpoints for agents
- Compound, snapshot, inclusion and exclusion measures
- Period-over-period, running window and top-n via spec decorators
- Single static binary serving as CLI, planner and server
- Partition-aware and aggregate-aware routing
- Accounts, users and read-only query keys for shared servers

## Integrations
Postgres, Snowflake, BigQuery, Databricks, Redshift, Athena, Trino, ClickHouse, Druid, DuckDB, MySQL, SQL Server, SQLite, MCP, Strata

## Platforms
WINDOWS, MACOS, LINUX, WEB, API, CLI

## Pricing
Open Source

## Version
0.6.5

## Links
- Website: https://0sql.io/
- Documentation: https://0sql.io/docs/
- Repository: https://github.com/stratasite/0sql
- EveryDev.ai: https://www.everydev.ai/tools/0sql